How to write data to excel in selenium Java

Writing data to an Excel file is a common requirement in automation testing, especially for reporting test results or logging data dynamically. In this article, I will explain how to write data into Excel files using Java with the help of JXL and Apache POI libraries.

The JXL library supports writing data only to .xls files, whereas Apache POI allows writing to both .xls and .xlsx formats. In Apache POI:

The HSSF classes are used for writing data to .xls files (Excel 97–2003 format).
The XSSF classes are used for writing data to .xlsx files (Excel 2007 and later).

Below, I’ve provided sample scripts demonstrating how to write data to both .xls and .xlsx files using Selenium with Java.

Write data to Excel

Wrtie to XL with HSSF

Wrtie to XL with XSSF

import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

public class WriteXlsFileWithLoop {
public static void main(String[] args) {
try {
// Create workbook and sheet for .xls file
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("TestData");

// Define the number of rows and columns
int totalRows = 5;
int totalCols = 3;

// Write header row
Row header = sheet.createRow(0);
header.createCell(0).setCellValue("ID");
header.createCell(1).setCellValue("Name");
header.createCell(2).setCellValue("Result");

// Write data using nested for loops
for (int i = 1; i <= totalRows; i++) { // Start from row 1 (since 0 is header)
Row row = sheet.createRow(i);

for (int j = 0; j < totalCols; j++) {
Cell cell = row.createCell(j);

switch (j) {
case 0 -> cell.setCellValue(i); // ID
case 1 -> cell.setCellValue("TestCase_" + i); // Name
case 2 -> cell.setCellValue(i % 2 == 0 ? "Passed" : "Failed"); // Result
}
}
}

// Auto-size all columns for better visibility
for (int j = 0; j < totalCols; j++) {
sheet.autoSizeColumn(j);
}

// Write to file
FileOutputStream fileOut = new FileOutputStream("LoopData.xls");
workbook.write(fileOut);

// Close resources
fileOut.close();
workbook.close();

System.out.println("Data successfully written to LoopData.xls");

} catch (Exception e) {
e.printStackTrace();
}
}
}

import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

public class WriteXlsxFileWithLoop {
public static void main(String[] args) {
try {
// Create workbook and sheet for .xlsx file
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("EmployeeData");

// Define the number of rows and columns
int totalRows = 5;
int totalCols = 3;

// Write header row
Row header = sheet.createRow(0);
header.createCell(0).setCellValue("EmpID");
header.createCell(1).setCellValue("Name");
header.createCell(2).setCellValue("Department");

// Write data dynamically using nested loops
for (int i = 1; i <= totalRows; i++) {
Row row = sheet.createRow(i);

for (int j = 0; j < totalCols; j++) {
Cell cell = row.createCell(j);

switch (j) {
case 0 -> cell.setCellValue(100 + i); // Employee ID
case 1 -> cell.setCellValue("Employee_" + i); // Name
case 2 -> cell.setCellValue(i % 2 == 0 ? "QA" : "Development"); // Department
}
}
}

// Auto-size columns
for (int j = 0; j < totalCols; j++) {
sheet.autoSizeColumn(j);
}

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("EmployeeDataLoop.xlsx");
workbook.write(fileOut);

// Close resources
fileOut.close();
workbook.close();

System.out.println("Data successfully written to EmployeeDataLoop.xlsx");

} catch (Exception e) {
e.printStackTrace();
}
}
}

🧩 Common Explanation of the Code

This program demonstrates how to write data into Excel files in Java using the Apache POI library. The same logic applies for both .xls and .xlsx formats — the only difference is the class used to create the workbook (HSSFWorkbook for .xls and XSSFWorkbook for .xlsx).

🔹 Step-by-Step Explanation

Import Required Classes
The program imports classes from the org.apache.poi package to work with Excel files, sheets, rows, and cells.
Create a Workbook
A Workbook object represents the entire Excel file.
- For .xls → Workbook workbook = new HSSFWorkbook();
- For .xlsx → Workbook workbook = new XSSFWorkbook();
Create a Sheet
Inside the workbook, a new sheet is created using: Sheet sheet = workbook.createSheet("SheetName"); Each sheet acts like a page or tab inside the Excel file.
Define Rows and Columns
Variables are defined to control how many rows and columns of data will be created. int totalRows = 5; int totalCols = 3;
Create a Header Row
The first row (index 0) is typically used for column headers such as “ID,” “Name,” and “Result.” Row header = sheet.createRow(0); header.createCell(0).setCellValue("ID"); header.createCell(1).setCellValue("Name"); header.createCell(2).setCellValue("Result");
Write Data Using Loops
A nested for loop is used to create rows and cells dynamically:
- The outer loop iterates over rows.
- The inner loop iterates over columns.
Inside the inner loop, switch or if statements decide what data to write into each cell. for (int i = 1; i <= totalRows; i++) { Row row = sheet.createRow(i); for (int j = 0; j < totalCols; j++) { Cell cell = row.createCell(j); // Write data based on column } }
Auto-Size Columns
To make the Excel sheet look neat, columns are automatically resized to fit the content: sheet.autoSizeColumn(j);
Write the File to Disk
Once all data is written, the workbook is saved to a physical file using a FileOutputStream: FileOutputStream fileOut = new FileOutputStream("FileName.xls"); workbook.write(fileOut);
Close Resources
It’s important to close both the file stream and workbook to release system resources: fileOut.close(); workbook.close();
Output Confirmation
Finally, a success message confirms that the data has been written successfully.

✅ Key Points

Step	Description
Workbook	Represents the Excel file
Sheet	Represents a tab within the workbook
Row	Represents a horizontal row in Excel
Cell	Represents an individual data cell
HSSFWorkbook	Used for `.xls` files
XSSFWorkbook	Used for `.xlsx` files
Loops	Used to generate multiple rows and columns dynamically

💡 Use in Selenium Automation

This approach is often used in data-driven testing or report generation:

Writing test results or logs to Excel after each test case.
Generating dynamic data sheets for input-driven tests.
Maintaining execution reports in Excel format.

Write data to Excel

Wrtie to XL with HSSF

Wrtie to XL with XSSF

🧩 Common Explanation of the Code

🔹 Step-by-Step Explanation

✅ Key Points

💡 Use in Selenium Automation

Related posts: