# curl https://devtools.krishanchawla.com/raw/java-excel-util.txt

JAVA EXCEL UTILITY - COMPREHENSIVE
Production-ready Excel utility class with read/write operations, cell styling, and data manipulation for test automation

Category: Testing · Type: snippet
Official docs: https://poi.apache.org/

────────────────────────────────────────────────────────────

## What is Excel Utility?

**ExcelUtil** is a comprehensive Java utility class for reading and writing Excel files using Apache POI. It provides a clean API for data-driven testing, test result logging, and Excel-based data management in test automation frameworks.

---

## ☕ Java - Complete ExcelUtil Class

Production-ready utility with read/write operations.

```java
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import java.io.*;
import java.util.*;

public class ExcelUtil {
    private String filePath;
    private FileInputStream fis;
    private FileOutputStream fos;
    private Workbook workbook;
    private Sheet sheet;

    // Constructor
    public ExcelUtil(String filePath) {
        this.filePath = filePath;
        try {
            fis = new FileInputStream(filePath);
            if (filePath.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else if (filePath.endsWith(".xls")) {
                workbook = new HSSFWorkbook(fis);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // Get row count
    public int getRowCount(String sheetName) {
        sheet = workbook.getSheet(sheetName);
        return sheet.getLastRowNum() + 1;
    }

    // Get cell data
    public String getCellData(String sheetName, int rowNum, int colNum) {
        try {
            sheet = workbook.getSheet(sheetName);
            Row row = sheet.getRow(rowNum);
            Cell cell = row.getCell(colNum);
            return getCellValue(cell);
        } catch (Exception e) {
            return "";
        }
    }

    // Set cell data
    public boolean setCellData(String sheetName, int rowNum, int colNum, String data) {
        try {
            sheet = workbook.getSheet(sheetName);
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                row = sheet.createRow(rowNum);
            }
            Cell cell = row.getCell(colNum);
            if (cell == null) {
                cell = row.createCell(colNum);
            }
            cell.setCellValue(data);

            fos = new FileOutputStream(filePath);
            workbook.write(fos);
            fos.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    // Get cell value as string
    private String getCellValue(Cell cell) {
        if (cell == null) return "";

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                }
                return String.valueOf((long) cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    // Close workbook
    public void close() {
        try {
            if (workbook != null) workbook.close();
            if (fis != null) fis.close();
            if (fos != null) fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
```

---

## 📖 Java - Read Operations

Advanced reading methods for test data.

```java
public class ExcelReader extends ExcelUtil {

    public ExcelReader(String filePath) {
        super(filePath);
    }

    // Read entire sheet as 2D array
    public String[][] readSheet(String sheetName) {
        int rows = getRowCount(sheetName);
        int cols = getColumnCount(sheetName);
        String[][] data = new String[rows][cols];

        for (int i = 0; i < rows; i++) {
            for (int j = 0; j < cols; j++) {
                data[i][j] = getCellData(sheetName, i, j);
            }
        }
        return data;
    }

    // Read sheet as List of Maps (header as keys)
    public List<Map<String, String>> readSheetAsMap(String sheetName) {
        List<Map<String, String>> data = new ArrayList<>();
        int rows = getRowCount(sheetName);
        int cols = getColumnCount(sheetName);

        // Get headers from first row
        String[] headers = new String[cols];
        for (int j = 0; j < cols; j++) {
            headers[j] = getCellData(sheetName, 0, j);
        }

        // Read data rows
        for (int i = 1; i < rows; i++) {
            Map<String, String> row = new LinkedHashMap<>();
            for (int j = 0; j < cols; j++) {
                row.put(headers[j], getCellData(sheetName, i, j));
            }
            data.add(row);
        }
        return data;
    }

    // Get test data by test case name
    public Map<String, String> getTestData(String sheetName, String testCaseName) {
        List<Map<String, String>> allData = readSheetAsMap(sheetName);
        for (Map<String, String> row : allData) {
            if (row.get("TestCase").equals(testCaseName)) {
                return row;
            }
        }
        return null;
    }

    // Get column count
    public int getColumnCount(String sheetName) {
        Sheet sheet = workbook.getSheet(sheetName);
        Row row = sheet.getRow(0);
        return row != null ? row.getLastCellNum() : 0;
    }

    // Check if sheet exists
    public boolean isSheetExist(String sheetName) {
        return workbook.getSheet(sheetName) != null;
    }

    // Get all sheet names
    public List<String> getAllSheetNames() {
        List<String> sheetNames = new ArrayList<>();
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            sheetNames.add(workbook.getSheetName(i));
        }
        return sheetNames;
    }

    // Find row number by column value
    public int findRowByColumnValue(String sheetName, int colNum, String value) {
        int rows = getRowCount(sheetName);
        for (int i = 0; i < rows; i++) {
            if (getCellData(sheetName, i, colNum).equals(value)) {
                return i;
            }
        }
        return -1;
    }
}
```

---

## ✍️ Java - Write Operations

Comprehensive writing methods with styling.

```java
public class ExcelWriter extends ExcelUtil {

    public ExcelWriter(String filePath) {
        super(filePath);
    }

    // Write entire row
    public boolean writeRow(String sheetName, int rowNum, String[] data) {
        try {
            Sheet sheet = workbook.getSheet(sheetName);
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                row = sheet.createRow(rowNum);
            }

            for (int i = 0; i < data.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(data[i]);
            }

            saveWorkbook();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    // Append row to sheet
    public boolean appendRow(String sheetName, String[] data) {
        int lastRow = getRowCount(sheetName);
        return writeRow(sheetName, lastRow, data);
    }

    // Create new sheet
    public boolean createSheet(String sheetName) {
        try {
            if (!isSheetExist(sheetName)) {
                workbook.createSheet(sheetName);
                saveWorkbook();
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    // Write with cell styling
    public boolean setCellDataWithStyle(String sheetName, int rowNum, int colNum,
                                        String data, CellStyle style) {
        try {
            Sheet sheet = workbook.getSheet(sheetName);
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                row = sheet.createRow(rowNum);
            }
            Cell cell = row.getCell(colNum);
            if (cell == null) {
                cell = row.createCell(colNum);
            }
            cell.setCellValue(data);
            if (style != null) {
                cell.setCellStyle(style);
            }

            saveWorkbook();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    // Create header style (bold, colored background)
    public CellStyle createHeaderStyle() {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }

    // Create pass/fail style
    public CellStyle createPassStyle() {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(IndexedColors.GREEN.getIndex());
        style.setFont(font);
        return style;
    }

    public CellStyle createFailStyle() {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(IndexedColors.RED.getIndex());
        style.setFont(font);
        return style;
    }

    // Auto-size all columns
    public void autoSizeColumns(String sheetName) {
        Sheet sheet = workbook.getSheet(sheetName);
        int colCount = getColumnCount(sheetName);
        for (int i = 0; i < colCount; i++) {
            sheet.autoSizeColumn(i);
        }
        saveWorkbook();
    }

    // Write test result
    public boolean writeTestResult(String sheetName, String testCaseName,
                                    String status, String remarks) {
        int rowNum = findRowByColumnValue(sheetName, 0, testCaseName);
        if (rowNum == -1) return false;

        CellStyle style = status.equalsIgnoreCase("PASS") ?
                         createPassStyle() : createFailStyle();

        setCellDataWithStyle(sheetName, rowNum, getColumnCount(sheetName) - 2,
                            status, style);
        setCellData(sheetName, rowNum, getColumnCount(sheetName) - 1, remarks);

        return true;
    }

    // Save workbook
    private void saveWorkbook() {
        try {
            FileOutputStream fos = new FileOutputStream(filePath);
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
```

---

## 🎨 Java - Cell Styling & Formatting

Advanced styling methods.

```java
public class ExcelStyler {
    private Workbook workbook;

    public ExcelStyler(Workbook workbook) {
        this.workbook = workbook;
    }

    // Create custom style
    public CellStyle createStyle(IndexedColors bgColor, IndexedColors fontColor,
                                  boolean bold, HorizontalAlignment alignment) {
        CellStyle style = workbook.createCellStyle();

        // Font
        Font font = workbook.createFont();
        font.setBold(bold);
        font.setColor(fontColor.getIndex());
        style.setFont(font);

        // Background
        style.setFillForegroundColor(bgColor.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // Alignment
        style.setAlignment(alignment);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // Borders
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        return style;
    }

    // Create date format style
    public CellStyle createDateStyle(String format) {
        CellStyle style = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        style.setDataFormat(createHelper.createDataFormat().getFormat(format));
        return style;
    }

    // Create number format style
    public CellStyle createNumberStyle(String format) {
        CellStyle style = workbook.createCellStyle();
        DataFormat dataFormat = workbook.createDataFormat();
        style.setDataFormat(dataFormat.getFormat(format));
        return style;
    }
}
```

---

## 🧪 Java - Usage Examples

Complete usage demonstration.

```java
public class ExcelUtilDemo {

    public static void main(String[] args) {
        String filePath = "src/test/resources/TestData.xlsx";

        // Example 1: Read test data
        readTestDataExample(filePath);

        // Example 2: Write test results
        writeTestResultsExample(filePath);

        // Example 3: Create new report
        createReportExample(filePath);
    }

    // Example 1: Reading data
    public static void readTestDataExample(String filePath) {
        ExcelReader reader = new ExcelReader(filePath);

        // Read specific cell
        String username = reader.getCellData("LoginTests", 1, 0);
        System.out.println("Username: " + username);

        // Read entire sheet as Map
        List<Map<String, String>> testData =
            reader.readSheetAsMap("LoginTests");

        for (Map<String, String> row : testData) {
            System.out.println("Test: " + row.get("TestCase"));
            System.out.println("User: " + row.get("Username"));
            System.out.println("Pass: " + row.get("Password"));
        }

        // Get specific test data
        Map<String, String> tc001 =
            reader.getTestData("LoginTests", "TC_001");

        reader.close();
    }

    // Example 2: Writing results
    public static void writeTestResultsExample(String filePath) {
        ExcelWriter writer = new ExcelWriter(filePath);

        // Write test result
        writer.writeTestResult("LoginTests", "TC_001", "PASS",
                              "Login successful");

        writer.writeTestResult("LoginTests", "TC_002", "FAIL",
                              "Invalid credentials error");

        // Auto-size columns
        writer.autoSizeColumns("LoginTests");

        writer.close();
    }

    // Example 3: Creating report
    public static void createReportExample(String filePath) {
        ExcelWriter writer = new ExcelWriter(filePath);

        // Create new sheet
        writer.createSheet("TestResults");

        // Write headers
        String[] headers = {"Test Case", "Status", "Duration",
                           "Executed By", "Date"};
        writer.writeRow("TestResults", 0, headers);

        // Apply header style
        CellStyle headerStyle = writer.createHeaderStyle();
        for (int i = 0; i < headers.length; i++) {
            writer.setCellDataWithStyle("TestResults", 0, i,
                                       headers[i], headerStyle);
        }

        // Write test data
        String[] test1 = {"TC_Login_001", "PASS", "2.5s", "John", "2025-12-29"};
        writer.appendRow("TestResults", test1);

        writer.autoSizeColumns("TestResults");
        writer.close();
    }
}
```

---

## 📦 Maven Dependencies

Required Apache POI dependencies.

```xml
<dependencies>
    <!-- Apache POI for .xlsx files -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.5</version>
    </dependency>

    <!-- Apache POI for .xls files -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.5</version>
    </dependency>

    <!-- XML Beans (POI dependency) -->
    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>5.1.1</version>
    </dependency>
</dependencies>
```

---

## 🔧 Data-Driven Testing Integration

TestNG integration example.

```java
import org.testng.annotations.*;

public class DataDrivenTest {
    private ExcelReader reader;

    @DataProvider(name = "loginData")
    public Object[][] getLoginData() {
        reader = new ExcelReader("src/test/resources/TestData.xlsx");

        List<Map<String, String>> data =
            reader.readSheetAsMap("LoginTests");

        Object[][] testData = new Object[data.size()][3];

        for (int i = 0; i < data.size(); i++) {
            Map<String, String> row = data.get(i);
            testData[i][0] = row.get("Username");
            testData[i][1] = row.get("Password");
            testData[i][2] = row.get("ExpectedResult");
        }

        reader.close();
        return testData;
    }

    @Test(dataProvider = "loginData")
    public void testLogin(String username, String password,
                         String expectedResult) {
        // Your test logic here
        System.out.println("Testing: " + username);
    }
}
```

---

## 🎯 Best Practices

**1. Always close workbooks:**
```java
try {
    ExcelUtil excel = new ExcelUtil("data.xlsx");
    // operations
} finally {
    excel.close();
}
```

**2. Use try-with-resources:**
```java
try (ExcelReader reader = new ExcelReader("data.xlsx")) {
    // operations
} catch (Exception e) {
    e.printStackTrace();
}
```

**3. Validate before reading:**
```java
if (reader.isSheetExist("TestData")) {
    String value = reader.getCellData("TestData", 0, 0);
}
```

**4. Handle null values:**
```java
String data = reader.getCellData(sheet, row, col);
if (data == null || data.isEmpty()) {
    data = "default_value";
}
```

---

## ⚡ Performance Tips

**1. Reuse workbook instances:**
```java
// Don't create multiple instances for same file
ExcelUtil excel = new ExcelUtil("data.xlsx");
// Perform all operations
excel.close();
```

**2. Batch write operations:**
```java
// Write multiple cells, then save once
for (int i = 0; i < 100; i++) {
    // Set cell values
}
excel.saveWorkbook(); // Save once at end
```

**3. Use SXSSFWorkbook for large files:**
```java
// For writing large files (streaming)
SXSSFWorkbook workbook = new SXSSFWorkbook(100); // Keep 100 rows in memory
```

---

## 🐛 Common Issues & Solutions

**Issue 1: File already open**
```java
// Solution: Close Excel file before running tests
// Or use different file name for output
```

**Issue 2: OutOfMemoryError**
```java
// Solution: Use streaming API for large files
SXSSFWorkbook workbook = new SXSSFWorkbook();
```

**Issue 3: Date format issues**
```java
// Solution: Use DateUtil to check and format dates
if (DateUtil.isCellDateFormatted(cell)) {
    Date date = cell.getDateCellValue();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    return sdf.format(date);
}
```

────────────────────────────────────────────────────────────

Full page: https://devtools.krishanchawla.com/devtools/java-excel-util
More tools: https://devtools.krishanchawla.com