Working with excel using selenium web driver (using Apache POI)

Apache POI

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


-> Apache POI is a powerful Java library to work with different Microsoft Office file formats such as Excel, Power point, Visio, MS Word etc.

-> The name POI was originally an acronym for Poor Obfuscation Implementation, referring humorously to the fact that the file formats seemed to be deliberately obfuscated, but poorly, since they were successfully reverse-engineered.

-> Apache POI library v3.8 or above. And download .Jar file like this format poi-bin-3.10 .jar in this link = http://poi.apache.org/download.html

-> Make sure to include apache poi jar file to your project. If your project uses Maven as dependency management, add following in your Pom.xml file.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.8</version>
</dependency>
If you are not using Maven then you can directly add required JAR files in your classpath.





POI classes used for working with Excel

Apache POI main classes usually start with either HSSF, XSSF or SXSSF.

-> HSSF is the POI Project’s pure Java implementation of the Excel ’97(-2007) file format. e.g. HSSFWorkbook, HSSFSheet.

-> XSSF is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format. e.g. XSSFWorkbook, XSSFSheet.

-> SXSSF (since 3.8-beta3) is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. e.g. SXSSFWorkbook, SXSSFSheet. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document.

-> Apart from above classes, Row and Cell are used to interact with a particular row and a particular cell in excel sheet.

-> Another useful class FormulaEvaluator is used to evaluate the formula cells in excel sheet.

-> A wide range of classes like CellStyle, BuiltinFormats, ComparisonOperator, ConditionalFormattingRule, FontFormatting, IndexedColors, PatternFormatting, SheetConditionalFormatting etc. are used when you have to add formatting in a sheet, mostly based on some rules.





Similar to HSSF(Horrible SpreadSheet Format), POI has different prefix for other file formats too:

-> HSSF (Horrible SpreadSheet Format) – reads and writes Microsoft Excel (XLS) format files.
-> XSSF (XML SpreadSheet Format) – reads and writes Office Open XML (XLSX) format files.
-> HPSF (Horrible Property Set Format) – reads “Document Summary” information from Microsoft Office files.
-> HWPF (Horrible Word Processor Format) – aims to read and write Microsoft Word 97 (DOC) format files.
-> HSLF (Horrible Slide Layout Format) – a pure Java implementation for Microsoft PowerPoint files.
-> HDGF (Horrible DiaGram Format) – an initial pure Java implementation for Microsoft Visio binary files.
-> HPBF (Horrible PuBlisher Format) – a pure Java implementation for Microsoft Publisher files.
-> HSMF (Horrible Stupid Mail Format) – a pure Java implementation for Microsoft Outlook MSG files
-> DDF (Dreadful Drawing Format) – a package for decoding the Microsoft Office Drawing format.


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

Reading an excel file

Reading an excel file is also very simple if we divide this in steps.

-> Create workbook instance from excel sheet
-> Get to the desired sheet
-> Increment row number
-> Iterate over all cells in a row
-> Repeat step 3 and 4 until all data is read



Code For Reading data from .xls file

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

FileInputStream file1=new FileInputStream(new File("D:\\Selenium_Testing\\Selenium_Webdriver_Project\\Login1.xls"));
HSSFWorkbook workbook1=new HSSFWorkbook(file1);
HSSFSheet worksheet1=workbook1.getSheetAt(1);
Iterator<Row> rowiterator1=worksheet1.iterator();
while(rowiterator1.hasNext())
{
Row row1=rowiterator1.next();
Iterator<Cell> celliterator1=row1.cellIterator();
while(celliterator1.hasNext())
{
Cell cell1=celliterator1.next();
int cellType = cell1.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cell1.getNumericCellValue()+"\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.println(cell1.getStringCellValue()+"\t");
break;
}
System.out.println("\n");
}
}
file1.close();


Note : 
1) Change the above code from "Switch" to "if-else" statement if we have error like "case expressions must be constant expressions".
2) In Eclipse IDe, we can change "Switch" to "if-else" by clik on switch and pressing "CTRL+1".


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

Code For Reading data from .xlsx file

FileInputStream file1=new FileInputStream(new File("D:\\Selenium_Testing\\Selenium_Webdriver_Project\\POI_Example.xlsx"));
XSSFWorkbook workbook1=new XSSFWorkbook(file1);
XSSFSheet worksheet1=workbook1.getSheetAt(0);
Iterator<Row> rowiterator1=worksheet1.iterator();
while(rowiterator1.hasNext())
{
Row row1=rowiterator1.next();
Iterator<Cell> celliterator1=row1.cellIterator();
while(celliterator1.hasNext())
{
Cell cell1=celliterator1.next();
int cellType = cell1.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cell1.getNumericCellValue()+"\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.println(cell1.getStringCellValue()+"\t");
break;
}
System.out.println("\n");
}
}
file1.close();


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

How to write .xls file using apachi poi in selenium webdriver


We should use HSSFWorkbook and HSSFSheet class while working with .xls file.


package Model_Package;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ExcelOperation {


public static void main(String[] args) throws IOException,FileNotFoundException {

try{
Cell searchcell=null;
FileInputStream File1=new FileInputStream("D:\\Selenium_Testing\\Selenium_Webdriver_Project\\Apache.xls");

HSSFWorkbook workbook1=new HSSFWorkbook(File1);
HSSFSheet Sheet1=workbook1.getSheetAt(0);   // Here "0" refers First WorkSheet. if "1" means its refer Seconds Sheet

Row row1=Sheet1.createRow(0);   //Here "0" indicates First Row.
searchcell=row1.createCell(0);  //Here "0" indicates first column of the specified row.

searchcell.setCellValue("Selenium");   //insert the "Selenium" text into that cell value
File1.close(); // close the input File Stream

FileOutputStream File2=new FileOutputStream("D:\\Selenium_Testing\\Selenium_Webdriver_Project\\Apache.xls");
workbook1.write(File2);
File2.close();

}
catch(Exception e)
{e.printStackTrace();}

}

}




i) How to get know last used row(End of the Text) in Excel Sheet

Integer Lastrow;
Lastrow = Sheet1.getLastRowNum();
Lastrow=Lastrow+1;



ii)  Issue while write Excel Sheet using Apache POI

We got Error while use below code to access the cell in Excel


 
  FileInputStream file = new FileInputStream(new File("C:\\testdata.xls"));  
  HSSFWorkbook workbook = new HSSFWorkbook(file);
 
  HSSFSheet sheet = workbook.getSheetAt(0);
   
  Cell searchText3 = sheet.getRow(0).getCell(0);
  searchText3.setCellValue("Test Search Keyword");
 
  file.close();
 
  FileOutputStream outFile =new FileOutputStream(new File("C:\\testdata.xls"));
  workbook.write(outFile);
  outFile.close();



The below Error while access the cell in Excel


Exception in thread "main" java.lang.NullPointerException at xltest.main(xltest.java:28)


Solution :

-> The below code is not working and return Null value.

 Cell searchText3 = sheet.getRow(0).getCell(0);

So we got the Null Pointer Exception while run the code.

-> Instead of above code we shoule use below code for access cell in Excel


Row row1=Sheet1.createRow(0);
searchcell=row1.createCell(0);







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



How to write .xlsx file using apachi poi in selenium webdriver


We should use XSSFWorkbook and XSSFSheet class while working with .xlsx file.



package Model_Package;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ExcelOperation {


public static void main(String[] args) throws IOException,FileNotFoundException {

try{
Cell searchcell=null;
FileInputStream File1=new FileInputStream("D:\\Selenium_Testing\\Selenium_Webdriver_Project\\Apache.xls");

XSSFWorkbook workbook1=new XSSFWorkbook(File1);
XSSFSheet Sheet1=workbook1.getSheetAt(0);  // Here "0" refers First WorkSheet. if "1" means its refer Seconds Sheet

Row row1=Sheet1.createRow(0);   //Here "0" indicates First Row.
searchcell=row1.createCell(0);  //Here "0" indicates first column of the specified row.

searchcell.setCellValue("Selenium");   //insert the "Selenium" text into that cell value
File1.close(); // close the input File Stream

FileOutputStream File2=new FileOutputStream("D:\\Selenium_Testing\\Selenium_Webdriver_Project\\Apache.xls");
workbook1.write(File2);
File2.close();

}
catch(Exception e)
{e.printStackTrace();}

}

}