How to Read/Write Excel Data using Apache POI Selenium

Related Product

Automated tests often involve stimulus and check data stored in Excel file, but say and updating this data reliably can be challenging. Apache POI cater the APIs involve to work with Excel files directly in Java, making it ideal for use with Selenium WebDriver.

This article explains how to read and compose Excel files in both .xls and .xlsx formatting use Apache POI in Selenium.

What is Apache POI?

Apache POI is an open-source Java library often utilized to create and handle Microsoft Office-based files. Users can leverage POI to perform diverse operations (modify, create, display, read) on specific file formats (Excel file be one of them).

Since Java does not offer built-in support for Excel files, testers need open-source APIs to work with them. Apache POI supply a Java API that lets users operate and maneuver file format built on the Office Open XML (OOXML) standard and Microsoft & # 8217; s OLE2 criterion.

To create or maintain Excel Workbooks, Apache POI provide a ” Workbook ” as a super-interface of all classes. It belong toorg.apache.poi.ss.usermodelpackage. It uses WorkbookFactory form to create the appropriate workbook (i.e. HSSFWorkbook or XSSFWorkbook). The two classes which implement the “ Workbook ” interface are given below:

• HSSFWorkbook& # 8211; Methods of this course are employ to read or write data to Microsoft Excel file in .xls format.
• XSSFWorkbook& # 8211; Methods of this class are used to read/write data to Microsoft Excel and OpenOffice XML files in .xls or .xlsx format.

Apache POI Installation

Step 1& # 8211; Download the Apache POI jar file from the official site.

Step 2 & # 8211; Once the zip file is downloaded, evoke it and save it.

For autonomous testing across multiple user personas, check out SUSATest — it explores your app like 10 different real users.

Step 3 & # 8211;Configure the build path in Eclipse and add all the POI external jars lean below.

Once all the Jar file are added, the exploiter can read and write the data from and to Excel files.

Note:Apache POI has two sets of classes for working with Excel files:

• HSSF (HSSFWorkbook/HSSFSheet/HSSFRow/HSSFCell): For .xls files (Excel 97–2003).
• XSSF (XSSFWorkbook/XSSFSheet/XSSFRow/XSSFCell): For .xlsx file (Excel 2007 and subsequently).

Choose the right one based on your file format.

Read Data from Excel File in Selenium

The code below is utilise to say the data from the sample Excel sheet in Selenium. This is the excel sheet information that will be used for reading data in this instance.

import org.openqa.selenium.WebDriver; import org.openqa.selenium.remote.DesiredCapabilities; import java.io.FileInputStream; import java.io.IOException; import org.apache.poi.ss.usermodel.Cell; meaning org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public family BrowserStackReadExcelTest {public stable vacuum independent (String [] args) throws IOException {//Path of the excel file FileInputStream fs = new FileInputStream (`` D: \\DemoFile.xlsx ''); //Creating a workbook XSSFWorkbook workbook = new XSSFWorkbook (fs); XSSFSheet sheet = workbook.getSheetAt (0); Row row = sheet.getRow (0); Cell cell = row.getCell (0); System.out.println (sheet.getRow (0) .getCell (0)); Row row1 = sheet.getRow (1); Cell cell1 = row1.getCell (1); System.out.println (sheet.getRow (0) .getCell (1)); Row row2 = sheet.getRow (1); Cell cell2 = row2.getCell (1); System.out.println (sheet.getRow (1) .getCell (0)); Row row3 = sheet.getRow (1); Cell cell3 = row3.getCell (1); System.out.println (sheet.getRow (1) .getCell (1)); //String cellval = cell.getStringCellValue (); //System.out.println (cellval);}}

In this example, specific rows and columns are accessed to read single cell values. To read the total Excel file, loop through all rows and columns, as testify below.

import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import java.io.File; significance java.io.FileInputStream; import java.io.IOException; public class ApachePOI {public static void principal (String [] args) throws IOException {// Path of the Excel file File file = new File (`` E: \\TestData\\TestData.xls ''); // Create an object of FileInputStream to read the Excel file FileInputStream inputStream = new FileInputStream (file); // Create workbook instance for .xls file HSSFWorkbook workbook = new HSSFWorkbook (inputStream); // Get the sheet HSSFSheet sheet = workbook.getSheet (`` STUDENT_DATA ''); // Get total rows int rowCount = sheet.getLastRowNum () - sheet.getFirstRowNum (); // Loop through all rows for (int i = 0; i & lt; = rowCount; i++) {// Get total columns in the row int cellCount = sheet.getRow (i) .getLastCellNum (); System.out.println (`` Row `` + i + `` data: ''); // Loop through columns in the row for (int j = 0; j & lt; cellCount; j++) {System.out.print (sheet.getRow (i) .getCell (j) .getStringCellValue () + ``, '');} System.out.println ();}}}

Write Data into Excel File in Selenium

The code below shows how to publish data into an Excel file using Apache POI. It opens an existing workbook, iterate through each row, and creates a new cell in the third column (indicant 2) to write the value WriteintoExcel.

import java.io.FileInputStream; import java.io.FileNotFoundException; signification java.io.FileOutputStream; import java.io.IOException; import org.openqa.selenium.remote.DesiredCapabilities; importation org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; importee org.apache.poi.ss.usermodel.Workbook; meaning org.apache.poi.xssf.usermodel.XSSFWorkbook; public class WriteintoExcel {public static void main (String [] args) throws IOException {String path = `` D: //DemoFile.xlsx ''; FileInputStream fs = new FileInputStream (path); Workbook wb = new XSSFWorkbook (fs); Sheet sheet1 = wb.getSheetAt (0); int lastRow = sheet1.getLastRowNum (); for (int i=0; i & lt; =lastRow; i++) {Row row = sheet1.getRow (i); Cell cell = row.createCell (2); cell.setCellValue (`` WriteintoExcel '');} FileOutputStream fos = new FileOutputStream (way); wb.write (fos); fos.close ();}}

In the codification below, ground on the cell value WriteintoExcel, data will be pen as picture below.

How BrowserStack Helps in Selenium Testing

BrowserStack provides a cloud program where Selenium test can run on 3,500+ browsers and devices. When you use Apache POI to deal exam datum, you can give that data into Selenium scripts and execute them on BrowserStack ’ s infrastructure.

This allows you to test the same scenarios across different environment, such as Chrome on Windows or Safari on macOS, without setting up a local gimmick lab. Its seamless integration with CI/CD pipelines means automated tryout can be triggered with every build, cater quick feedback and ensuring consistent lineament across environments.

Talk to an Expert

Conclusion

Apache POI allows you to read and pen test datum from Excel files, do it easy to progress data‑driven Selenium tests. This separates test logic from tryout data and simplifies maintaining or updating trial event.

BrowserStack append value to this setup by providing a cloud environment to run those Selenium tests across many browsers and devices. Its support for parallel execution and unseamed CI/CD integration get it ideal for teams that require to run data‑driven tests promptly and dependably across different platforms.

Related Guides