Manage Microsoft Excel files in Java with Apache POI

May 25, 2017

Code examples in the current post can be found in GitHub java-samples/apache-poi repository.

Two most important use cases where MS Excel documents are useful in automation testing are:

Data-driven testing where test data is input from an Excel file
Output test results to an Excel file for stakeholders

So far I did not need to use Excel in my automation. Where data-driven testing was needed I either used JUnit data provider (see more in Data driven testing with JUnit parameterized tests and Data driven testing with JUnit and Gradle posts) or feeding data in CSV format and reading them with Apache Commons CSV library. My colleague Petar Yordanov introduced me to Apache POI which is very powerful for Excel and other MS format documents management.

Apache POI

The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008). See more on Apache POI home page. Full details on supported POI formats can be found in Apache POI Component Overview. From this page, you can navigate to more details how to work with a specific type of document. Full details on Excel management can be found on Apache POI Spreadsheet Guide.

Usage

Include in Maven project

In the current example, I will use the poi-ooxml library which for XML based formats introduced in Microsoft Office 2007.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.16</version>
</dependency>

Creating a Workbook

Excel file is actually a XSSFWorkbook object from org.apache.poi.xssf.usermodel package. Workbook can be created empty or from existing file:

// Create empty workbook
XSSFWorkbook newWorkBook = new XSSFWorkbook();
// Create workbook from existing file
XSSFWorkbook existingWorkBook = new XSSFWorkbook(new File("fileName.xlsx"));

Manage sheets

XSSFWorkbook class provides different methods for sheet management: createSheet(), cloneSheet(), getNumberOfSheets(), getSheet(), getSheetAt(), getSheetName(), getSheetIndex(). Create or get sheet methods return a XSSFSheet object.

Manage sheet content

Once you have the XSSFSheet object, either from create or get, you can manage rows in it. Some of the methods are: createRow(), getRow(), removeRow(), getLastRowNum(), getPhysicalNumberOfRows(), rowIterator(). Create or get row methods return a XSSFRow object. Inside row you can manage cells. Some of the methods are: createCell(), getCell(), removeCell(), getLastCellNum(), getPhysicalNumberOfCells(), cellIterator(). Create or get cell methods return a XSSFCell object. Some the methods are: setCellComment(), setCellFormula(), setCellStyle(), setCellType(), setCellValue().

Manage Excel documents

Below are shown examples of simple ExcelWriter class that writes to Excel file. This class is used in SampleExcelApp showing how to write the text. Reading from Excel is shown in SampleExcelAppTest verifying the correct saving of the document.

package com.automationrhapsody.apachepoi;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelWriter {

    private final XSSFWorkbook workBook;

    private Map<String, Integer> nextRows = new HashMap<>();
    private String currentSheet;
    private boolean isSaved;

    public ExcelWriter() {
        workBook = new XSSFWorkbook();
    }

    public void writeAndClose(File excelFile) {
        if (isSaved) {
            throw new IllegalArgumentException("Workbook already saved!");
        }
        try {
            workBook.write(new FileOutputStream(excelFile));
            workBook.close();
            isSaved = true;
        } catch (IOException ioe) {
            // TODO log
        }
    }

    public void switchToSheet(String sheetName) {
        currentSheet = sheetName;
        if (workBook.getSheet(sheetName) == null) {
            workBook.createSheet(currentSheet);
            nextRows.put(currentSheet, 0);
        }
    }

    public void writeRow(String... values) {
        XSSFSheet sheet = workBook.getSheet(currentSheet);
        int nextRow = nextRows.get(currentSheet);
        XSSFRow row = sheet.createRow(nextRow);

        for (int i = 0; i < values.length; i++) { 
            XSSFCell cell = row.createCell(i);
            cell.setCellType(CellType.STRING);
            cell.setCellValue(values[i]);
        }

        nextRows.put(currentSheet, nextRow + 1);
    }

    public void setCellColour(int rowNumber, int cellNumber,
            IndexedColors colour) {
        XSSFCellStyle style = workBook.createCellStyle();
        style.setFillForegroundColor(colour.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        int nextRow = nextRows.get(currentSheet);
        if (rowNumber > nextRow) {
            // TODO log or exception?
            rowNumber = nextRow;
        }
        XSSFSheet sheet = workBook.getSheet(currentSheet);
        int lastCell = sheet.getRow(rowNumber - 1).getLastCellNum();
        if (cellNumber > lastCell) {
            // TODO log or exception?
            cellNumber = lastCell;
        }

        sheet.getRow(rowNumber - 1).getCell(cellNumber - 1)
            .setCellStyle(style);
    }
}

package com.automationrhapsody.apachepoi;

import java.io.File;

import org.apache.poi.ss.usermodel.IndexedColors;

public class SampleExcelApp {

    public static void main(String[] args) {
        String sheetName = "SheetName";

        ExcelWriter excelWriter = new ExcelWriter();
        excelWriter.switchToSheet(sheetName);
        excelWriter.writeRow("A1-blue", "B1", "C1");
        excelWriter.writeRow("A2", "B2", "C2");
        excelWriter.setCellColour(1, 1, IndexedColors.BLUE);

        excelWriter.switchToSheet("NewSheetName");
        excelWriter.writeRow("A1", "B1");
        excelWriter.writeRow("A2", "B2-red");
        excelWriter.setCellColour(2, 2, IndexedColors.RED);

        excelWriter.switchToSheet(sheetName);
        excelWriter.writeRow("A3", "B3", "C3");
        excelWriter.writeRow("A4", "B4", "C4");

        File excelFile = new File("testReport.xlsx");
        excelWriter.writeAndClose(excelFile);
    }
}

package com.automationrhapsody.apachepoi;

import java.io.File;

import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.BeforeClass;
import org.junit.Test;

import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.is;

public class SampleExcelAppTest {

    private static final File FILE = new File("testReport.xlsx");

    private static XSSFWorkbook workbookUnderTest;

    @BeforeClass
    public static void beforeClass() throws Exception {
        if (FILE.exists()) {
            FILE.delete();
        }

        App.main(null);

        workbookUnderTest = new XSSFWorkbook(FILE);
    }

    @Test
    public void testNumberOfSheets() {
        assertThat(workbookUnderTest.getNumberOfSheets(), is(2));
    }

    @Test
    public void testSheetName() {
        assertThat(workbookUnderTest.getSheetName(0), is("SheetName"));
        assertThat(workbookUnderTest.getSheetName(1), is("NewSheetName"));
    }

    @Test
    public void testFirstSheetContent() {
        XSSFSheet sheet = workbookUnderTest.getSheetAt(0);
        assertThat(sheet.getLastRowNum(), is(3));
        assertThat(sheet.getRow(3).getLastCellNum(), is((short) 3));
        assertThat(sheet.getRow(3).getCell(2).getStringCellValue(), 
                is("C4"));

        assertThat(sheet.getRow(0).getCell(0).getCellStyle()
            .getFillForegroundColor(), is(IndexedColors.BLUE.getIndex()));
        assertThat(sheet.getRow(1).getCell(1).getCellStyle()
            .getFillForegroundColor(), 
                is(IndexedColors.AUTOMATIC.getIndex()));
    }

    @Test
    public void testSecondSheetContent() {
        XSSFSheet sheet = workbookUnderTest.getSheetAt(1);
        assertThat(sheet.getLastRowNum(), is(1));
        assertThat(sheet.getRow(1).getLastCellNum(), is((short) 2));
        assertThat(sheet.getRow(1).getCell(1).getStringCellValue(), 
                is("B2-red"));

        assertThat(sheet.getRow(1).getCell(1).getCellStyle()
            .getFillForegroundColor(), is(IndexedColors.RED.getIndex()));    
        assertThat(sheet.getRow(0).getCell(0).getCellStyle()
            .getFillForegroundColor(), 
                is(IndexedColors.AUTOMATIC.getIndex()));
    }
}

Conclusion

Apache POI is a very powerful toolkit for managing MS documents especially Excel, which might be needed in your test automation for reporting of data-driven testing.

Tags:

Java MS Excel