Spreadsheet with Java Apache POI

This article is about how to create a spreadsheet with the Apache POI 4.1.1 Java library. There is also a "Busy Developers' Guide" available on its home page. Mind that POI contains much more than just a spreadsheet API (MS Word, PowerPoint, ...).

Here is a screenshot of what I'm gonna present source code for:

In first line, the sheet renders the number PI 3 times in different formats. In second line it displays today's date in two different formats, and a text "End of Test". There are no formulas here, this is just about creation and formatting.

Following is the example project's Maven pom.xml, containing the library-dependency.

<project 
    xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>fri.poi</groupId>
  <artifactId>poiTest</artifactId>
  <version>0.0.1-SNAPSHOT</version>

  <dependencies>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>4.1.1</version>
    </dependency>

  </dependencies>

</project>

And here is the Java source for it. Explanations see below. Mind that this code needs at least Java 7.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

import java.io.*; import java.util.Date; import org.apache.poi.ss.usermodel.*; /** * Usage of Apache POI: create a spreadsheet * with number-, date- and string-cells. */ public class ExcelExample { public static void main(String[] args) throws Exception { new ExcelExample().create(); } public Workbook create() throws Exception { final Workbook workbook = WorkbookFactory.create(false); // false: use HSSF, else ClassNotFoundException final DataFormat format = workbook.createDataFormat(); // create contents final Sheet sheet = workbook.createSheet("First Tab"); final Row row0 = sheet.createRow(0); final Cell cell00 = row0.createCell(0); // cell00.setCellType(CellType.NUMERIC); // deprecated! cell00.setCellValue(Math.PI); final Cell cell01 = row0.createCell(1); cell01.setCellValue(Math.PI); cell01.setCellStyle(workbook.createCellStyle()); // replace default by individual cell-style cell01.getCellStyle().setDataFormat(format.getFormat("0.0000")); final Cell cell02 = row0.createCell(2); cell02.setCellValue(Math.PI); cell02.setCellStyle(workbook.createCellStyle()); cell02.getCellStyle().setDataFormat(format.getFormat("0.00")); final Row row1 = sheet.createRow(1); final Cell cell10 = row1.createCell(0); cell10.setCellValue(new Date()); cell10.setCellStyle(workbook.createCellStyle()); cell10.getCellStyle().setDataFormat(format.getFormat("yyyy-dd-MM")); final Cell cell11 = row1.createCell(1); cell11.setCellValue(new Date()); cell11.setCellStyle(workbook.createCellStyle()); cell11.getCellStyle().setDataFormat(format.getFormat("dd.MM.yyyy")); final String END_OF_TEST = "End of Test"; final Cell cell12 = row1.createCell(2); cell12.setCellValue(END_OF_TEST); //sheet.autoSizeColumn(0); // doesn't always work well sheet.setColumnWidth(0, 30 * 256); // 30 characters wide sheet.setColumnWidth(1, 20 * 256); // 20 characters wide sheet.setColumnWidth(2, END_OF_TEST.length() * 256); workbook.close(); final File excelFile = new File("ExcelExample.xlsx"); try (OutputStream outputStream = new FileOutputStream(excelFile)) { workbook.write(outputStream); System.out.println("Created file: "+excelFile.getAbsolutePath()); } return workbook; } }

A Workbook is what we would call "Excel Sheet". A Sheet is one tab inside such a workbook, having rows numbered from 0 to n, and any row can have cells numbered from 0 to n. There can be undefined rows and cells anywhere in between. Cells contain a style, by default this is just one global style, but you can also set specific styles into them. Inside the style you can have a format mask for the cell content.

Line 16 creates a Workbook in an implementation-agnostic way by retrieving it from a factory. The boolean parameter lets me choose between

1. XSSF (XML-format, since 2007, extension .xslx) and
2. HSSF (old EXCEL format 1997-2007, extension .xsl)

implementations. By default POI comes just with HSSF, so setting true would cause a ClassNotFoundException.

On line 17, I create a data-format to generate cell format masks. This seems to be globally workbook-bound.

Line 20 and 22 show how to create a new sheet with title "First Tab", and a new row in it. This row will contain 3 cells showing the number PI in different formats.

On line 24 the first cell is generated, by means of its owner row. I don't put any format on it, just the value, let's see the outcome. Mind that cell.setCellType(CellType.XXX) is deprecated in newest POI.

Line 28 creates a cell where a format mask should round PI to 4 decimals. It is important to

• set a newly created style into the cell (see line 30) when the cell needs an individual format.

Else it would use the global style that automatically is in every cell, and all cells would be formatted the same way, derived from the latest setDataFormat() call. On line 31, the individual cell style receives its format mask from the DataFormat object created on line 17.

Well, basically that's it.
On line 33 there is another cell showing PI, this time formatted to just 2 decimals. The subsequent row also contains 3 cells, two dates formatted in different ways, and a final string cell on line 51. Mind that the string automatically got aligned left, while numbers and dates got aligned right.

Before saving the in-memory workbook, I size its columns. There would be just "####" signs displayed in case a content is too big for its column, and that's confusing for users being new to Office tools. When using autoSizeColumn(), this must be done after the cells received their values and formats. But on line 55, I size the columns explicitly. The width is given in 1/256-th of an average character's width. (You should encapsulate this calculation into a method!)

Finally the workbook gets closed and saved to persistence on line 62. The file ExcelExample.xlsx would be located in the working directory of the application, like given by System.getProperty("user.dir"). The try-clause that automatically closes the output-stream is available since Java 7.

This was just a short sneak into POI, showing basic techniques.