What is Apache POI?
Apache POI is a Java API that provides support for reading and writing Microsoft Office documents such as Excel, Word, and PowerPoint.
It is widely used in Selenium for data-driven testing, where test data is stored in Excel files.
In Selenium, Apache POI is used to manipulate Excel files and extract test data from them—for example, reading test data from an Excel file and using it to populate web forms during automated testing.
Key Classes and Interfaces
-
Workbook
-
Sheet
-
Row
-
Cell
These classes and interfaces let you create, modify, read, and write Excel files from Java code.
Reading and Writing Excel Through Selenium
To read and write Excel data in Selenium, you use Apache POI.
Steps
-
Initialize the WebDriver, navigate to the desired page, and obtain the data to write (
dataToWrite). -
Open the Excel file for reading using
FileInputStreamand load it into aWorkbookobject. -
Retrieve the desired sheet from the Workbook using
getSheetAt(). -
Read data by retrieving the desired row and cell and getting the value using
getStringCellValue(). -
Create a new row and cell in the sheet for writing data.
-
Set the value of the cell using
setCellValue(). -
Save the changes using
FileOutputStreamandwrite(). -
Close the input stream, output stream, and Workbook.
-
Close the WebDriver.
Example
// Read
FileInputStream fis = new FileInputStream("testdata.xlsx");
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
String cellValue = sheet.getRow(1).getCell(0).getStringCellValue();
// Write
Row newRow = sheet.createRow(2);
Cell newCell = newRow.createCell(0);
newCell.setCellValue(dataToWrite);
FileOutputStream fos = new FileOutputStream("testdata.xlsx");
workbook.write(fos);
fis.close();
fos.close();
workbook.close();
Reading From an XML File Into a HashMap
For XML parsing, use the Java API for XML Processing (JAXP) with a DocumentBuilder.
Example
import org.w3c.dom.*;
import javax.xml.parsers.*;
import java.io.File;
import java.util.HashMap;
import java.util.Map;
public class XMLToHashMapExample {
public static void main(String[] args) {
try {
// Create a DocumentBuilderFactory and DocumentBuilder
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
// Load the XML file
File xmlFile = new File("data.xml");
Document document = builder.parse(xmlFile);
// Create a HashMap to store the data
Map<String, String> dataMap = new HashMap<>();
// Traverse the XML and store data in the HashMap
Element root = document.getDocumentElement();
NodeList nodeList = root.getElementsByTagName("entry");
for (int i = 0; i < nodeList.getLength(); i++) {
Element entry = (Element) nodeList.item(i);
String key = entry.getAttribute("key");
String value = entry.getTextContent();
dataMap.put(key, value);
}
// Print the contents of the HashMap
System.out.println(dataMap);
} catch (Exception e) {
e.printStackTrace();
}
}
}
The parser loads the XML file into a Document.
getElementsByTagName("entry") collects all entries, and each entry's key attribute and text content are stored in the HashMap.
Reading From a CSV File Into a HashMap
For CSV parsing, use a library such as OpenCSV, which provides utilities for reading and writing CSV files.
Example
import com.opencsv.CSVReader;
import java.io.FileReader;
import java.util.HashMap;
import java.util.Map;
public class CSVToHashMapExample {
public static void main(String[] args) {
try {
CSVReader reader =
new CSVReader(new FileReader("data.csv"));
Map<String, String> dataMap =
new HashMap<>();
String[] line;
while ((line = reader.readNext()) != null) {
// Column 0 = Key
// Column 1 = Value
dataMap.put(line[0], line[1]);
}
reader.close();
System.out.println(dataMap);
} catch (Exception e) {
e.printStackTrace();
}
}
}
Each CSV row is read as a String[].
-
Column 0 → Key
-
Column 1 → Value
The values are stored inside the HashMap.
This follows the same key-value concept commonly used with property files in automation frameworks.
Wiring the Data Into Tests (DataProvider)
Data files become data-driven tests through TestNG's DataProvider.
A utility method reads the Excel, CSV, or XML file into an Object[][].
Each row in the file becomes one row of the array.
The @DataProvider then feeds this data into the @Test method, causing the test to execute once for each data set.
Complete Data-Driven Pipeline
Excel / CSV / XML File
↓
Apache POI / OpenCSV / JAXP
↓
Object[][]
↓
@DataProvider
↓
@Test Method
FAQs
What is Apache POI used for?
Apache POI is used for reading and writing Microsoft Office documents such as Excel, Word, and PowerPoint.
In Selenium, it is primarily used for data-driven testing using Excel files through the Workbook, Sheet, Row, and Cell classes.
How do you read a cell from Excel?
Open the Excel file using FileInputStream, create a Workbook, retrieve the required sheet using getSheetAt(), access the row with getRow(), access the cell with getCell(), and retrieve the value using getStringCellValue().
How do you write to Excel?
Create the required row and cell using createRow() and createCell(), assign the value using setCellValue(), then save the workbook using FileOutputStream and workbook.write(). Finally, close all streams and the workbook.
How do you read XML into a HashMap?
Use JAXP's DocumentBuilder to parse the XML file, iterate through the NodeList of entries, and store each key attribute and text content in a HashMap.
How do you read CSV into a HashMap?
Use OpenCSV's CSVReader, loop through each row using readNext(), and store Column 0 as the key and Column 1 as the value in the HashMap.
How does file data reach the tests?
A utility method reads the file and converts it into an Object[][].
A TestNG @DataProvider supplies the data to the @Test method, allowing the test to execute once for each row of data.
