Working with Microsoft Excel in Java

1. Overview

In this tutorial, we’ll demonstrate the use of the Apache POI, JExcel, and Fastexcel APIs for working with Excel spreadsheets.

These libraries can be used to dynamically read, write and modify the content of an Excel spreadsheet and provide an effective way of integrating Microsoft Excel into a Java Application.

2. Maven Dependencies

To begin, we will need to add the following dependencies to our pom.xml file:

<dependency> 
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId> 
  <version>5.5.0</version> 
</dependency> 
<dependency> 
  <groupId>org.apache.poi</groupId> 
  <artifactId>poi-ooxml</artifactId> 
  <version>5.5.0</version> 
</dependency>
<dependency>
  <groupId>org.jxls</groupId>
  <artifactId>jxls-jexcel</artifactId>
  <version>1.0.9</version>
</dependency>
<dependency>
    <groupId>org.dhatim</groupId>
    <artifactId>fastexcel-reader</artifactId>
    <version>0.19.0</version>
</dependency>
<dependency>
    <groupId>org.dhatim</groupId>
    <artifactId>fastexcel</artifactId>
    <version>0.19.0</version>
</dependency>

The latest versions of poi, poi-ooxml, jxls-jexcel, fastexcel-reader, and fastexcel can be downloaded from Maven Central.

3. Apache POI

The Apache POI library supports both .xls and .xlsx files and is a more complex library than other Java libraries for working with Excel files.

It provides the Workbook interface for modeling an Excel file, and the Sheet, Row, and Cell interfaces that model the elements of an Excel file, as well as implementations of each interface for both file formats.

When working with the newer .xlsx file format, we’d use the XSSFWorkbook, XSSFSheet, XSSFRow, and XSSFCell classes.

To work with the older .xls format, we use the HSSFWorkbook, HSSFSheet, HSSFRow, and HSSFCell classes.

3.1. Reading From Excel

Let’s create a method that opens a .xlsx file and then reads content from the first sheet of the file.

The method for reading cell content varies depending on the type of data in the cell. The type of cell content can be determined using the getCellType() method of the Cell interface.

First, let’s open the file from a given location:

FileInputStream file = new FileInputStream(new File(fileLocation));
Workbook workbook = new XSSFWorkbook(file);

Next, let’s retrieve the first sheet of the file and iterate through each row:

Sheet sheet = workbook.getSheetAt(0);

Map<Integer, List<String>> data = new HashMap<>();
int i = 0;
for (Row row : sheet) {
    data.put(i, new ArrayList<String>());
    for (Cell cell : row) {
        switch (cell.getCellType()) {
            case STRING: ... break;
            case NUMERIC: ... break;
            case BOOLEAN: ... break;
            case FORMULA: ... break;
            default: data.get(new Integer(i)).add(" ");
        }
    }
    i++;
}

Apache POI has different methods for reading each type of data. Let’s expand on the content of each switch case above.

When the cell type enum value is STRING, the content will be read using the getRichStringCellValue() method of the Cell interface:

data.get(new Integer(i)).add(cell.getRichStringCellValue().getString());

Cells having the NUMERIC content type can contain either a date or a number and are read in the following manner:

if (DateUtil.isCellDateFormatted(cell)) {
    data.get(i).add(cell.getDateCellValue() + "");
} else {
    data.get(i).add(cell.getNumericCellValue() + "");
}

For BOOLEAN values, we have the getBooleanCellValue() method:

data.get(i).add(cell.getBooleanCellValue() + "");

And when the cell type is FORMULA, we can use the getCellFormula() method:

data.get(i).add(cell.getCellFormula() + "");

3.2. Writing to Excel

Apache POI uses the same interfaces presented in the previous section for writing to an Excel file and has better support for styling than JExcel.

Let’s create a method that writes a list of persons to a sheet titled “Persons”.

First, we will create and style a header row that contains “Name” and “Age” cells:

Workbook workbook = new XSSFWorkbook();

Sheet sheet = workbook.createSheet("Persons");
sheet.setColumnWidth(0, 6000);
sheet.setColumnWidth(1, 4000);

Row header = sheet.createRow(0);

CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

XSSFFont font = ((XSSFWorkbook) workbook).createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 16);
font.setBold(true);
headerStyle.setFont(font);

Cell headerCell = header.createCell(0);
headerCell.setCellValue("Name");
headerCell.setCellStyle(headerStyle);

headerCell = header.createCell(1);
headerCell.setCellValue("Age");
headerCell.setCellStyle(headerStyle);

Next, let’s write the content of the table in a different style:

CellStyle style = workbook.createCellStyle();
style.setWrapText(true);

Row row = sheet.createRow(2);
Cell cell = row.createCell(0);
cell.setCellValue("John Smith");
cell.setCellStyle(style);

cell = row.createCell(1);
cell.setCellValue(20);
cell.setCellStyle(style);

Finally, let’s write the content to a “temp.xlsx” file in the current directory and close the workbook:

File currDir = new File(".");
String path = currDir.getAbsolutePath();
String fileLocation = path.substring(0, path.length() - 1) + "temp.xlsx";

FileOutputStream outputStream = new FileOutputStream(fileLocation);
workbook.write(outputStream);
workbook.close();

Let’s test the above methods in a JUnit test that writes content to the temp.xlsx file and then reads the same file to verify it contains the text we have written:

public class ExcelIntegrationTest {

    private ExcelPOIHelper excelPOIHelper;
    private static String FILE_NAME = "temp.xlsx";
    private String fileLocation;

    @Before
    public void generateExcelFile() throws IOException {
        File currDir = new File(".");
        String path = currDir.getAbsolutePath();
        fileLocation = path.substring(0, path.length() - 1) + FILE_NAME;

        excelPOIHelper = new ExcelPOIHelper();
        excelPOIHelper.writeExcel();
    }

    @Test
    public void whenParsingPOIExcelFile_thenCorrect() throws IOException {
        Map<Integer, List<String>> data
          = excelPOIHelper.readExcel(fileLocation);

        assertEquals("Name", data.get(0).get(0));
        assertEquals("Age", data.get(0).get(1));

        assertEquals("John Smith", data.get(1).get(0));
        assertEquals("20", data.get(1).get(1));
    }
}

4. JExcel

The JExcel library is a lightweight library with the advantage that it’s easier to use than Apache POI, but with the disadvantage that it only provides support for processing Excel files in the .xls (1997-2003) format.

At the moment, .xlsx files are not supported.

4.1. Reading From Excel

In order to work with Excel files, this library provides a series of classes that represent the different parts of an Excel file. The Workbook class represents the entire collection of sheets. The Sheet class represents a single sheet, and the Cell class represents a single cell of a spreadsheet.

Let’s write a method that creates a workbook from a specified Excel file, gets the first sheet of the file, and then traverses its content and adds each row in a HashMap:

public class JExcelHelper {

    public Map<Integer, List<String>> readJExcel(String fileLocation) 
      throws IOException, BiffException {
 
        Map<Integer, List<String>> data = new HashMap<>();

        Workbook workbook = Workbook.getWorkbook(new File(fileLocation));
        Sheet sheet = workbook.getSheet(0);
        int rows = sheet.getRows();
        int columns = sheet.getColumns();

        for (int i = 0; i < rows; i++) {
            data.put(i, new ArrayList<String>());
            for (int j = 0; j < columns; j++) {
                data.get(i)
                  .add(sheet.getCell(j, i)
                  .getContents());
            }
        }
        return data;
    }
}

4.2. Writing to Excel

For writing to an Excel file, the JExcel library offers classes similar to the ones used above, which model a spreadsheet file: WritableWorkbook, WritableSheet, and WritableCell.

The WritableCell class has subclasses corresponding to the different types of content that can be written: Label, DateTime, Number, Boolean, Blank, and Formula.

This library also supports basic formatting, such as controlling font, color, and cell width.

Let’s write a method that creates a workbook called “temp.xls” in the current directory and then writes the same content we wrote in the Apache POI section.

First, let’s create the workbook:

File currDir = new File(".");
String path = currDir.getAbsolutePath();
String fileLocation = path.substring(0, path.length() - 1) + "temp.xls";

WritableWorkbook workbook = Workbook.createWorkbook(new File(fileLocation));

Next, let’s create the first sheet and write the header of the Excel file, containing the “Name” and “Age” cells:

WritableSheet sheet = workbook.createSheet("Sheet 1", 0);

WritableCellFormat headerFormat = new WritableCellFormat();
WritableFont font
  = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD);
headerFormat.setFont(font);
headerFormat.setBackground(Colour.LIGHT_BLUE);
headerFormat.setWrap(true);

Label headerLabel = new Label(0, 0, "Name", headerFormat);
sheet.setColumnView(0, 60);
sheet.addCell(headerLabel);

headerLabel = new Label(1, 0, "Age", headerFormat);
sheet.setColumnView(0, 40);
sheet.addCell(headerLabel);

With a new style, let’s write the content of the table we’ve created:

WritableCellFormat cellFormat = new WritableCellFormat();
cellFormat.setWrap(true);

Label cellLabel = new Label(0, 2, "John Smith", cellFormat);
sheet.addCell(cellLabel);
Number cellNumber = new Number(1, 2, 20, cellFormat);
sheet.addCell(cellNumber);

It’s very important to remember to write to the file and close it at the end so it can be used by other processes, using the write() and close() methods of the Workbook class:

workbook.write();
workbook.close();

5. Fastexcel

Fastexcel is an easy-to-use library with limited features and a lower memory footprint compared to Apache POI.

Its multi-threaded support using CompletableFuture makes it a great alternative when working with large files that aren’t feature-extensive. At the moment, the library has only basic style support and doesn’t support graphs.

5.1. Reading From Excel

Let’s write a method to access the Excel file and read the data from its first sheet. Let’s add the data to a Map of row indices as keys and a list of the content from that row as value:

public class FastexcelHelper {

    public Map<Integer, List<String>> readExcel(String fileLocation) throws IOException {
        Map<Integer, List<String>> data = new HashMap<>();

        try (FileInputStream file = new FileInputStream(fileLocation); ReadableWorkbook wb = new ReadableWorkbook(file)) {
            Sheet sheet = wb.getFirstSheet();
            try (Stream<Row> rows = sheet.openStream()) {
                rows.forEach(r -> {
                    data.put(r.getRowNum(), new ArrayList<>());

                    for (Cell cell : r) {
                        data.get(r.getRowNum()).add(cell.getRawValue());
                    }
                });
            }
        }

        return data;
    }
}

Here, we’re using cell.getRawValue() which returns the value of that cell as a String. Alternatively, based on CellType we can use the getCellAsBoolean(int cellIndex), getCellAsDate(int cellIndex), getCellAsString(int cellIndex) or getCellAsNumber(int cellIndex) methods from the Row class to read a cell’s content.

5.2. Writing to Excel

Unlike the libraries mentioned above, Fastexcel uses different interfaces to write to an Excel file than the ones used to read Excel.

We’ll begin by creating a Workbook from an OutputStream. We’ll then write the “Name” and “Age” headers in the default first sheet and add style details to the cells. Next, let’s add another row with a person’s name and age:

public void writeExcel() throws IOException {
    File currDir = new File(".");
    String path = currDir.getAbsolutePath();
    String fileLocation = path.substring(0, path.length() - 1) + "fastexcel.xlsx";

    try (OutputStream os = Files.newOutputStream(Paths.get(fileLocation)); Workbook wb = new Workbook(os, "MyApplication", "1.0")) {
        Worksheet ws = wb.newWorksheet("Sheet 1");
        ws.width(0, 25);
        ws.width(1, 15);
        
        ws.range(0, 0, 0, 1).style().fontName("Arial").fontSize(16).bold().fillColor("3366FF").set();
        ws.value(0, 0, "Name");
        ws.value(0, 1, "Age");
        
        ws.range(2, 0, 2, 1).style().wrapText(true).set();
        ws.value(2, 0, "John Smith");
        ws.value(2, 1, 20L);
    }
}

Let’s write a small test to verify our code:

@Test
public void whenParsingExcelFile_thenCorrect() throws IOException {
    Map<Integer, List<String>> data = fastexcelHelper.readExcel(fileLocation);

    assertEquals("Name", data.get(1).get(0));
    assertEquals("Age", data.get(1).get(1));

    assertEquals("John Smith", data.get(3).get(0));
    assertEquals("20", data.get(3).get(1));
}

Here, we also see that the Row class in the fastexcel-reader library uses the variable rowNum with an offset of 1. Whereas, the value(int r, int c, Object value) method from the Worksheet class from the fastexcel library expects the row’s index with an offset of 0.

6. Conclusion

In this article, we saw how to use the Apache POI API, JExcel API, and Fastexcel API to read and write an Excel file from a Java program.

When deciding on which library to use, we should consider the benefits and drawbacks of each library. For example, Apache POI is feature-rich and has support for graphs but has a high memory footprint. In contrast, Fastexcel has limited features but consumes less memory than Apache POI.