Working with excel using selenium webdriver (JXL.jar file)

Download  below JXL jar file and build with eclipse IDE for working working with excel using selenium webdriver (JXL.jar file)

http://www.findjar.com/jar/net.sourceforge.jexcelapi/jars/jxl-2.6.jar.html
http://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl
http://www.quicklyjava.com/jexcel-jar-download/


We will face below two issues while use JXL.jar file

1) Exception in thread "main" jxl.read.biff.BiffException: Unable to recognize OLE stream

-> if we are using .xlsx format(D:\Selenium_Testing\Selenium_Webdriver_Project\Login.xlsx), we will get this Exception.

-> The xls format (< Excel 2007) is comprised of binary BIFF data in an OLE container. The xlsx format (>= Excel 2007) is comprised of XML files in a zip container.

The Java Excel API only deals with the first format so it throws an exception when it doesn't encounter an OLE container.

You will need to restrict your input to xls files only or find another tool that handles both formats.

-> Apache POI handles both file types in Java: poi.apache.org/spreadsheet/index.html

http://stackoverflow.com/questions/5428794/biffexception-while-reading-an-excel-sheet


2) java.lang.ArrayIndexOutOfBoundsException - Its occures due to array doesnot initialize with any value.

-> If Excell cell does not have any value (Empty) means. its throw the above exception.






Jxl.jar in selenium webdriver


FileInputStream input1=new FileInputStream("D:\\Selenium_Testing\\Selenium_Webdriver_Project\\Login1.xls");
Workbook book1=Workbook.getWorkbook(input1);
Sheet Sheet1=book1.getSheet(0);
String Username=Sheet1.getCell(1,0).getContents();


-> getCell() - to retrive the vale from Excel cell.

Syntex :

1) getcell(ColNumber-1,RowNumber-1)

Example : getcell(2,5) -> Its rerun the value from Third col and six row.

OR

getcell(String Cell_Index)

Example: getcell("A1") -> Its return the value from A1.

**********************************************************

Read the Excel using selenium webdriver (JXL.jar Add on)

String First_name="Arun";
String Last_name="Raj";

FileInputStream input1=new FileInputStream("D:\\Selenium_Testing\\Selenium_Webdriver_Project\\Login1.xls");
Workbook book1=Workbook.getWorkbook(input1);
Sheet Sheet1=book1.getSheet("Sheet1");  // or getSheet(0); Sheet name or Sheet index starts from Zero.

String Username=Sheet1.getCell("A1").getContents();  //getCell("index of cell")
String Password=Sheet1.getCell(2,1).getContents();   /getCell(ColNumber-1,RowNumber-1)
String Password1=Sheet1.getCell(1,1).getContents();

System.out.println("UserName=" +Username);
System.out.println("Password =" +Password);
System.out.println("Password =" +Password1);

****************************************

Write the Excel using Selenium webdriver (JXL.jar Add on)

String First_name="Arun";
String Last_name="Raj";

FileOutputStream output1= new FileOutputStream("D:\\Selenium_Testing\\Selenium_Webdriver_Project\\Login1.xls");

WritableWorkbook wwb=Workbook.createWorkbook(output1);
WritableSheet ws=wwb.createSheet("Testdata1", 0);   //CreateSheet("SheetName","index") - If index <=0, add in front.
//if index>0, then sheet add as per the index.

Label l1=new Label(0,2,First_name);    //Label(ColNumber-1,RowNumber-1,Data)
Label l2=new Label(0,6,Last_name);

ws.addCell(l1);
ws.addCell(l2);
wwb.write();
wwb.close();

Referance = http://jexcelapi.sourceforge.net/resources/javadocs/current/docs/

**************************************

Write the data into different sheets of the same excel using selenium webdriver (Jxl.jar add on)


String First_name="Arun";
String Last_name="Raj
FileOutputStream output1= new FileOutputStream("D:\\Selenium_Testing\\Selenium_Webdriver_Project\\Login1.xls");

WritableWorkbook wwb=Workbook.createWorkbook(output1);
WritableSheet ws=wwb.createSheet("Testdata1", 0);
WritableSheet ws1=wwb.createSheet("Testdata2", 1);

Label l1=new Label(0,2,First_name);
Label l2=new Label(0,6,Last_name);

ws.addCell(l1);
ws1.addCell(l2);
wwb.write();
wwb.close();

PS: The above method is not appending the data in Excel. Those values are replaced by new one.

*****************************


Append data in Existing Excel using selenium webdriver (Jxl.jar Add on)


String First_name="Arun";
Workbook wb=Workbook.getWorkbook(new File("D:\\Selenium_Testing\\Selenium_Webdriver_Project\\Login1.xls"));
WritableWorkbook wwb=Workbook.createWorkbook(new File("D:\\Selenium_Testing\\Selenium_Webdriver_Project\\Login1.xls"), wb);
WritableSheet ws=wwb.getSheet(1);
Label l3=new Label(0,0,First_name);
ws.addCell(l3);
wwb.write();
wwb.close();


***********************

Jxl.Jar = Best for reading data from Excel.
Apache POI = Best for writing data into Excel.

********************

Apache POI

For Download = http://poi.apache.org/download.html