Selenium: Excel Read and Write – using java jExcel(jxl)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
package excel.util;
import jxl.*;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import java.io.File;
import java.io.IOException;
public class readWriteExcel {
 
 public static void main(String[] args) throws Exception
 {
  readExcelData("./testdata.xls"); //initial file
  writeToExcel();
  readExcelData("./outputtestdata.xls"); //updated file
 }
 
 //read an existing excel
 public static void readExcelData(String excelPath) throws BiffException, IOException
 {
  Workbook workbook = Workbook.getWorkbook(new File(excelPath));
  Sheet sheet = workbook.getSheet(0); 
  for(int i=0; i<sheet.getRows();i++)
  {
   for(int j=0; j<sheet.getColumns();j++)
   {
    Cell cell = sheet.getCell(j,i);
    System.out.print(cell.getContents());
    //get their types
    CellType type = sheet.getCell(j,i).getType();
    System.out.print("("+type+")" + " ");
   }
   System.out.println(" ");
  } 
  
  CellType type = sheet.getCell(1,1).getType();
  if (type == CellType.LABEL)
  {
   System.out.println("cell(1,1) data is a label");
  }
 }
 
 //Write to an existing excel or add a new row
 public static void writeToExcel() throws BiffException, IOException, RowsExceededException, WriteException
 {
  Workbook workbook = Workbook.getWorkbook(new File("./testdata.xls"));
  //create a new excel and copy from existing
  WritableWorkbook copy = Workbook.createWorkbook(new File("./outputtestdata.xls"), workbook);
  WritableSheet sheet = copy.getSheet(0);
  //Label(colno, rowno, string)
  Label label = new Label(2,1, "class-10");
  sheet.addCell(label);
  copy.write();
  copy.close();
 }
}
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX