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

-------------------------------------------------------------------------------------------------------------------------
According to http://jexcelapi.sourceforge.net/ - jexcel API is to read and write excel sheet data,

Note:

• Excel should be of .xls format , if it is a .xlxs format, then open the excel and click onFile -> Save As and choose "Excel 97-2003 WorkBook" format.
• We can't edit an existing excel, but can create a new excel(copy existing excel content) and update the content.

Required jar file: jxl.jar, click here to download the zip jexcelapi and get the required jar file.

Steps:
Place the excel file under the project folder (for ease access)
Enter excel data something like this:

testdata.xls

Here, 1st row is the column header / description.

Code to read and write an excel file:

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(); } }

And the output is:

rollno(Label) name(Label) standard(Label)  
1111(Number) Ram(Label) Class-1(Label)  
2222(Number) John(Label) Class-1(Label)  
3333(Number) Sam(Label) Class-5(Label)  
4444(Number) Michell(Label) Class-7(Label)  
cell(1,1) data is a label
-------------------------
rollno(Label) name(Label) standard(Label)  
1111(Number) Ram(Label) class-10(Label)  
2222(Number) John(Label) Class-1(Label)  
3333(Number) Sam(Label) Class-5(Label)  
4444(Number) Michell(Label) Class-7(Label)  
cell(1,1) data is a label