Write data to the excel sheet in Selenium using POI

Earlier, we have discussed different techniques to accommodate parameterization in our Selenium automation test. Today we are going to discuss commands (code) to write data to the excel sheet using the POI jar file. Before we proceed further to achieve today’s agenda, I would recommend you following hot tutorials on parameterization:

Advantages of writing data to the excel sheet

  • Ease in handover test data to the client or stakeholders (Portable)
  • Have a checklist of pass and failure status against each scenario mentioned in the excel sheet
  • Ease of maintenance

Write data excel sheet selenium

Differences between POI and JXL

Format Supported: It supports xlsx and xls both. Format Supported: It supports only xls format.
Version: It supports all the versions of Excel. Version: It does not support Excel 2007 and later.
It supports conditional formatting. It does not support conditional formatting.
Apache POI releases its update regularly. It was last updated in 2009.
It supports rich text. It does not support rich text.
Documentation is elaborated. Documentation is limited.

The hierarchical flow of the implementation of POI to write data to the Excel sheet

You got all the advantages of maintaining an excel sheet for the parameterization. Now let me give you a gist of the steps of the instantiation and calling of the methods and interfaces.

  • FileInputStream
  • Workbook -> XSSFWorkbook or HSSFWorkbook
  • Sheet
  • Row
  • Cell
  • FileOutputStream

How to implement POI jar file to write data to the excel sheet?

Now we are going to implement all those Interfaces and classes to write data to the sheet. Below sample code create a cell and write cell heading at 0th row and 2nd column.

In the above code, we are taking file by defining its path through FileInputStream and further, we created the instance of Workbook and XSSFWorkbook because our sheet is having .xlsx extension (HSSFWorkbook for .xls extension). We use createCell() method first to assign the cell and then we write data by using setCellValue() method.  Here is the screenshot from excel sheet after writing data.

write data excel sheet

Above code is used to write data to the single column. Let’s look at another piece of code which writes data for the entire row to the assigned cell.

Write data to excel sheet

Join our Facebook group and don’t miss any updates on Automation testing. Click on below button to join instantly.

Join Inviul fb group

Leave a Reply