Reading and writting data from .xlsx spreadsheet using Apache POI API

Why to use Apache POI and not Jexcel API ?

Below are the drawbacks of using Jexcel API for interacting with excel :

1. Jexcel supports .xls format of MS excel, which is not the default option we get while saving excel sheets, nowadays. So most of the excel sheets are by default get saved in .xlsx format. Apache POI supports not only .xls but also .xlsx type of spreadsheets.
2. We normally come across scenario such as - we want to read test data from the excel sheet and then write back to same excel sheet with some other related information. This can be achieved using Apache POI and not by Jexcel API as Jexcel does not support modifying a spreadsheet that is being used for reading.
3. Jexcel API is no longer in development i.e. no future updates/enhancements are not possible but Apache POI is still in development and updates/enhancements are very much possible.

So, in the long run, it'd be beneficial to use Apache POI in code rather than Jexcel API.

Let's see a simple script that uses Apache POI to read and write back to same spreadsheet. This is most common scenario for updating test results (PASS/FAIL) of our selenium tests to the scenario's spreadsheet.

Create TestScenarios.xlsx as given in below screenshot -

In this spreadsheet, first is positive and second is negative scenario for logging to AUT. Ensure that all the entries in spreadsheet are Strings and not integer as our script handles Strings only.

Script:

import org.testng.annotations.Test;
import org.testng.annotations.BeforeMethod;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.DataProvider;

public class ApachePOIReadandWriteBack {
   WebDriver driver = null;
   File scenarioFile = new File("D:\\path\\to\\the\\file\\TestScenarios.xlsx");
@Test(dataProvider = "dp")
   public void f(String testCaseID, String userName, String password, String result) {
   driver.findElement(By.xpath(".//*[@name = 'userName']")).sendKeys(userName);
   driver.findElement(By.xpath(".//*[@name = 'password']")).sendKeys(password);
   driver.findElement(By.xpath(".//*[@name='login']")).click();
   try {
      FileInputStream fis = new FileInputStream(scenarioFile);
      XSSFWorkbook workbook = (XSSFWorkbook) WorkbookFactory.create(fis);
      XSSFSheet sheet = workbook.getSheetAt(0);
      int rowNum = Integer.parseInt(testCaseID);
      Row row = sheet.getRow(rowNum);
      if (driver.getTitle().equals("Find a Flight: Mercury Tours:")) {
      row.createCell(3).setCellValue("PASS");
   } else {
      row.createCell(3).setCellValue("FAIL");
   }
   fis.close();
   FileOutputStream fos = new FileOutputStream(scenarioFile);
   workbook.write(fos);
   fos.close();
   } catch (FileNotFoundException e) {
      e.printStackTrace();
   } catch (EncryptedDocumentException e) {
      e.printStackTrace();
   } catch (InvalidFormatException e) {
      e.printStackTrace();
   } catch (IOException e) {
      e.printStackTrace(); }
   }

@BeforeMethod
   public void beforeMethod() {
   driver = new FirefoxDriver();
   driver.manage().window().maximize();
   driver.get("http://newtours.demoaut.com");
   }

@AfterMethod
   public void afterMethod() {
   driver.quit();
   }

@DataProvider
   public Object[][] dp() {
   Object[][] o = null;
   try {
   FileInputStream fis = new FileInputStream(scenarioFile);
   XSSFWorkbook workbook = (XSSFWorkbook) WorkbookFactory.create(fis);
   XSSFSheet sheet = workbook.getSheetAt(0);
   o = new Object[sheet.getLastRowNum()][sheet.getRow(0).getLastCellNum()];
   Iterator<Row> rowIterator = sheet.iterator();
   while (rowIterator.hasNext()) {
      Row row = rowIterator.next();
      if (row.getRowNum() == 0) {
      continue;
      }
   Iterator<Cell> cellIterator = row.cellIterator();
   while (cellIterator.hasNext()) {
      Cell cell = cellIterator.next();
      o[(row.getRowNum() - 1)][cell.getColumnIndex()] = cell.getStringCellValue();
      }
   }
   fis.close();
   } catch (FileNotFoundException e) {
      e.printStackTrace();
   } catch (EncryptedDocumentException e) {
      e.printStackTrace();
   } catch (InvalidFormatException e) {
      e.printStackTrace();
   } catch (IOException e) {
      e.printStackTrace();
   }
   return o;
   }
}

Output:
Once you run the script, script opens firefox browser and takes user name and password from the spreadsheet and depending upon successful login, writes the result back into excel sheet for the current test data.


Script Code Explanation:

Code for reading data from excel sheet is present in dp() function, annotated by @DataProvider. This code ignores first row of the excel sheet as it contains headers of columns. The dp() function returns multi-dimensional array having values read from excel sheet.

Code for writing data to excel sheet is present in f() function, annotated by @Test. This function makes use of test case ID that is read from the excel sheet to write the result, so that correct result gets updated for the appropriate row.

Don't forget to put comment about your doubts/questions/feedback!

Enjoy and Happy Testing!!