Selenium: Excel Read and Write using java Apache POI library

We will see the use of apache POI open source library in java to read and write excel file.

Download

  • If using normal java project, download 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
  • If using maven project, then add below dependency in pom.xml

 

Unlike jexcel api, apache 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.

 

Create an excel file and sheet if 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.

Note :

There is another way to set the workbook, by using WorkbookFactory class (From ss.usermodel package, so you need not to write logic to handle both .xls and .xlsx file, refer post workbookfactory usage)



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.

 

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.

 

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_type1st and then fetch the data accordingly.

 

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,
Share the post with others if find useful,
Any queries?, leave comments or discuss 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 *