import jxl.Sheet;
import jxl.Workbook;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;
import jxl.write.*;

import java.io.*;

/**
 *  Code to update 10 student records into table into Excel file
 */
public class Practical_5 {
    @BeforeClass
    public void f1() {
    }

    @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);
        // Used to store content of inputWorkbook - later used to fetch marks obtained
        String workbookArray[][] = new String[inputWorkbookSheet.getRows()][inputWorkbookSheet.getColumns()];

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


        // Fetch and store input xls data, and populate output xls sheet
        for (int i = 0; i < inputWorkbookSheet.getRows(); i++) {
            for (int j = 0; j < inputWorkbookSheet.getColumns(); j++) {
                workbookArray[i][j] = inputWorkbookSheet.getCell(j, i).getContents();
                Label label = new Label(j, i, workbookArray[i][j]); // parameter of Label(Column, Row,cellFormat)
                outputWorkbookSheet.addCell(label);
            }
        }
        //Creates result column
        Label resultLabel = new Label(6, 0, "Result");
        outputWorkbookSheet.addCell(resultLabel);


        /*  Compute's pass/fail.

            Note: 1) row starts from 1 because 1st row consists of names
                  2) col starts from 2, to exclude rollno(idx=0),name(idx=1), and compute only marks.
        */
        for (int i = 1; i < inputWorkbookSheet.getRows(); i++) {
            for (int j = 2; j < inputWorkbookSheet.getColumns(); j++) {
                workbookArray[i][j] = inputWorkbookSheet.getCell(j, i).getContents();
                int x = Integer.parseInt(workbookArray[i][j]);
                if (x > 35) {
                    Label l1 = new Label(6, i, "pass");
                    outputWorkbookSheet.addCell(l1);
                } else {
                    Label l1 = new Label(6, i, "fail");
                    outputWorkbookSheet.addCell(l1);
                    break;
                }
            }
        }

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