Selenium: Excel Read and Write using java Apache POI library

We will see the use of apache POI library on excel read and write operations.

Download basic required apache POI jar files from here.

Add the below jar files along with selenium related to Eclipse:

poi-x.xx-*.jar (x-xx* for latest releases)
poi-ooxml-x.xx*.jar
xmlbeans-x.x.x.jar
poi-ooxml-schemas-x.xx-*.jar
 
Unlike jexcel api, apacke POI handles both the types of excel file, .xls and .xlsx file as well.For handling .xls files, we use HSSF (Horrible SpreadSheet Format) related POI classes.
For handling .xlsx files, we use XSSF (XML SpreadSheet Format) related POI classes.

If you are using XSSF classes, then you can deal with both the excel format files, so no worries!We will be only discussing the operations that are needed for our selenium framework and the operations are as listed below.

  • Create an excel file if it’s not existed.
  • Enter (update) data in a cell.
  • Read data from particular cell.
  • Highlight particular cell.
1) Create an excel file and sheet if it’s not existed

Here in this below code, I am importing all the required libraries for the rest of the code snippets, so it will be easy for reference.





setExcelFile method takes 2 argument, path of excel file and sheet name, that we will be working on, we are checking if the excel file/sheet exist, if not, create it.

2) Enter(update) data in a cell

setCellData method takes 3 arguments, string text we want to enter into cell with rownum and colnum, this method enters data into an empty cell and also updates if data already existed.

 

3) Read data from particular cell:

getCellData method takes the rownum and colnum to fetch the data from excel sheet,

excel path and sheet name are already taken care by the setExcelFile method.

There are different ways to fetch the data from cell, like we need to fetch the cell_type

1st and then fetch the data accordingly.

 

4) Highlight particular cell.

highlightCell method takes 3 argument, which color to highlight the cell(row and col).

You can call the above 4 methods based on your requirement from the main() method of the same class or can import the class into another class(import pkg.poi.excelutil) and call these methods.
Let me know of any other requirements that needs to be added in this post, so everyone can leverage the code, Thanks!

Content posted is based on learning or working experience, please leave comments if anything needs to be added or updated, discuss your queries on our facebook:qavalidation.com, Thanks!

3 Responses

  1. Gopika says:

    Good information about read excel. Can you please provide information about how to read data from a JSON file. Because JSON is commonly used for data exchange.

  1. May 4, 2016

    […] we discussed in earlier post Read/Write excel using Apache POI, we have to use: For handling .xls files, we use HSSF (Horrible SpreadSheet Format) related POI […]

  2. June 7, 2016

    […] To click on the Selenium -> ExcelRW item, instead of mouse hover, we can use the url link associated with the ExcelRW item i.e- http://www.qavalidation.com/2015/03/selenium-excel-read-and-write-apachePOI.html […]

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: