Generalized Apache POI script for reading and writing to .xlsx files

In this post, we will be looking to simplify the script mentioned in this post.

Below script is generalized version so it can be used by other classes for reading and writing to .xlsx files. The code for reading and writing to .xlsx is made modular by putting them in different class and making read and write methods static. Any test that needs to read from excel file can just call the readFromXLSX() method and similarly any test that needs to write data to excel file, can just call the writeTestResultToXLSX() method and passing the appropriate parameters to it.

Here's the class that contains the read and write methods:
Below code makes use of Enumerations to restrict the value of test result to be either PASS or FAIL. This would make TestNG tests that will use these methods, not to write illegal values into the excel sheet. 

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;

public class ApachePOIMethods {

   public static Object[][] readFromXLSX(File scenarioFile) {
   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;
   }

public static void writeTestResultToXLSX(File scenarioFile, String testCaseID, int cellNo, resultEnum rEnum) {
   FileInputStream fis;
   try {
   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);
   row.createCell(cellNo).setCellValue(rEnum.toString());
   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();
      }
   }

public enum resultEnum {
   PASS, FAIL;
   }

}

Sample TestNG test that will make use of above read and write methods is given below:

import org.testng.annotations.Test;
import blogsScripts.ApachePOIMethods.resultEnum;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.DataProvider;
import java.io.File;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.Assert;
import org.testng.annotations.AfterMethod;

public class OptimizedApachePOIReadAndWriteBack {
   WebDriver driver = null;
   File scenarioFile = new File("D:\\path\\to\\excel\\sheet\\TestScenarios.xlsx");
   int XLSXresultCell = 3; // target cell in scenarioFile where PASS/FAIL should be written

@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();

   if (driver.getTitle().equals("Find a Flight: Mercury Tours:")) {
      ApachePOIMethods.writeTestResultToXLSX(scenarioFile, testCaseID, XLSXresultCell, resultEnum.PASS);
   } else {
      ApachePOIMethods.writeTestResultToXLSX(scenarioFile, testCaseID, XLSXresultCell, resultEnum.FAIL);
   }
}

@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() {
   return ApachePOIMethods.readFromXLSX(scenarioFile);
   }
}

Output:
Once you run the TestNG test, 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.

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

Enjoy and Happy Testing!!