import jxl.*;
import jxl.write.*;

import java.io.*;

import org.testng.annotations.Test;

public class Practical_6 {

    @Test
    public void testImportExport1() throws Exception {
        File xlsInputFile = new File("E:\\Ty\\ST\\input.xls"); // Get Inputs from this xls
        File xlsOutputFile = new File("E:\\Ty\\ST\\output.xls"); // Stream Output to this xls

        FileInputStream fi = new FileInputStream(xlsInputFile);
        FileOutputStream fo = new FileOutputStream(xlsOutputFile);


        Workbook inputWorkbook = Workbook.getWorkbook(fi);
        Sheet inputWorkbookSheet = inputWorkbook.getSheet(0);

        WritableWorkbook outputWorkbook = Workbook.createWorkbook(fo);
        WritableSheet outputWorkbookSheet = outputWorkbook.createSheet("result", 0);

        int tempRow = 0; // Maintains row in result xls file
        for (int i = 1; i < inputWorkbookSheet.getRows(); i++) {
            // Iterate each row, starting from 2nd row (since 1st is just labels)

                    for (int k = 2; k <= 4; k++) {
                        // Iterate through subjects (X,Y,Z), if any subject
                        // has marks greater then 60 then output that in xls
                        String b = inputWorkbookSheet.getCell(k, i).getContents();
                        int x = Integer.parseInt(b);

                        if (x > 60) {
                            for (int j = 0; j < inputWorkbookSheet.getColumns(); j++) {
                                String colLabel = inputWorkbookSheet.getCell(j, i).getContents() ;
                                Label l2 = new Label(j, tempRow, colLabel);
                                outputWorkbookSheet.addCell(l2);
                            }

                            tempRow++;
                            break; // Skip further iterations
                        }
                    }

        }
        outputWorkbook.write();
        outputWorkbook.close();
    }
}