Using Apache POI to Read Excel

Apache POI is a Java library to read and write Microsoft Documents including Word and Excel. Java Excel API can read and write Excel 97-2003 XLS files and also Excel 2007+ XLSX files. In this article, we show how to get going using the Apache POI library to work with Excel files.

Maven Dependency

You need the following maven dependency to work with Apache POI.

<poi.version>3.15</poi.version>
...
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>${poi.version}</version>
</dependency>

The Basics

Create an Excel 2007 XLSX file by using the class XSSFWorkbook as follows:

Workbook wb = new XSSFWorkbook();

To Work with the older Excel 97-2003 XLS format, use the following:

Workbook wb = new HSSFWorkbook();

Once you have a Workbook, you can use the interfaces Sheet, Row, Cell, CellStyle to avoid tying your application to the version-specific interfaces.

The First Spreadsheet

Let’s say hello to a Hello-World spreadsheet created in Java. The code below creates an Excel spreadsheet with a single sheet named “Population”.

Workbook wb = new XSSFWorkbook();
String safeName = WorkbookUtil.createSafeSheetName("Population");
Sheet sheet = wb.createSheet(safeName);
FileOutputStream fileOut = new FileOutputStream("hello.xlsx");
wb.write(fileOut);
fileOut.close();

The WorkbookUtil.createSafeSheetName() is used since certain characters are invalid in a sheet name. This utility method replaces such characters with the space character.

Creating a Row and Cells

A Row object needs to be created inside a sheet before you can fill it with data. A sheet is considered to be comprised of a set or rows. Create a specific numbered row with the call:

int rowNum = 0;
Row row = sheet.createRow((short)rowNum);

Let us now add some cells to this row. This row will serve as the header row since we will add some column titles to the row. There is no such concept of header row in Apache POI or Excel; we just treat the first row as such.

row.createCell(0).setCellValue(ch.createRichTextString("Rank"));
row.createCell(1).setCellValue(ch.createRichTextString("Country"));
row.createCell(2).setCellValue(ch.createRichTextString("Total(persons)"));
row.createCell(3).setCellValue(ch.createRichTextString("per sq.km"));
row.createCell(4).setCellValue(ch.createRichTextString("Date"));

Here is what the sheet looks like after adding the header row:

Auto Sizing Columns

You will notice that the columns are of equal size and some text in the columns is not visible. Wouldn’t it be nice to auto-size the columns so all the text is visible? Here is how you can do it. Note that you should do this once just before writing the spreadsheet to a file to avoid having to resize columns when not required.

for (short i = sheet.getRow(0).getFirstCellNum(),
    end = sheet.getRow(0).getLastCellNum() ; i < end ; i++) {
    sheet.autoSizeColumn(i);
}

Multi-Line Column

In the above code, we might want to split the text in a column into multiple lines. This is not just a matter of inserting a new-line (or a carriage-return-new-line). You need to set the cell style to allow multi-line cells to show the data.

Assume we need to show a header cell in two lines:

Cell cell = row.createCell(2);
cell.setCellValue("Total\r\n(persons)");

Once the data is inserted into the cell, set the cell style to allow wrapping.

CellStyle style = sheet.getWorkbook().createCellStyle();
style.setWrapText(true);
cell.setCellStyle(style);

After this, the cell data shows multiple lines if required.

Adding Rows

Let us now add rows in a loop to our spreadsheet. The data we want to add looks like this:

List<List<String>> list = Arrays
    .asList(Arrays.asList("1","China","1,378,020,000","147.75","2016"),
    Arrays.asList("2","India","1,266,884,000","426.10","2016 WFB"),
    Arrays.asList("3","United States of America","323,128,000","35.32","2016"),
    Arrays.asList("4","Indonesia","257,453,000","142.12","2016"),
    Arrays.asList("5","Brazil","206,081,000","24.66","2016"));

And here is the loop creating each row:

for (List<String> arr : list) {
    row = sheet.createRow(rowNum); rowNum++;
    row.createCell(0).setCellValue(arr.get(0));
    row.createCell(1).setCellValue(arr.get(1));
    row.createCell(2).setCellValue(arr.get(2));
    row.createCell(3).setCellValue(arr.get(3));
    row.createCell(4).setCellValue(arr.get(4));
}

The spreadsheet now looks like this. Notice that we have a few Excel errors: “Number Stored As Text”. That is because we are using Cell.setCellValue(String) method to set the cell value. Let us see how we can fix it.

Fixing “Number Stored As Text”

The solution is simple. Parse the text value to integer or double and use that to set the value. Pretty simple fix for the Rank.

int value = Integer.parseInt(arr.get(0));
row.createCell(0).setCellValue(value);

Similarly for the floating point value.

double value = Double.parseDouble(arr.get(3));
row.createCell(3).setCellValue(value);

After these changes, the error disappears on the two columns.

Using NumberFormat for Parsing

The fix for the Total field is a bit more involved. The numeric value is formatted with commas for the thousands-separator which we need to parse to obtain the value.

First, we create a NumberFormatwith the correct Locale. We store the NumberFormat for re-use (without recreating it every time).

private NumberFormat fmt = NumberFormat.getInstance(Locale.US);

We use the NumberFormat for parsing the string value into a double.

Cell cell = row.createCell(2);
String value = arr.get(2);
try {
    Number n = fmt.parse(value);
    cell.setCellValue(n.intValue());
} catch(java.text.ParseException ex) {
    System.err.println("Row " + rowNum + ": " + value + ": " +
               ex.getMessage());
}

With that update, the “Number Stored As Text” error has disappeared.

Setting Cell Style

However, we would still like to store the number with thousands-separator as before. For this, we need to tell Excel that the number is to be formatted with the correct formatter. This is done by setting the cell style. (The CellStyle is expensive to create and use. It should be created outside the loop and stored for reuse).

CellStyle popStyle = workbook.createCellStyle();
short format = (short)BuiltinFormats.getBuiltinFormat("#,##0");
popStyle.setDataFormat(format);

After the style is created, we need to set it on the cell.

Cell cell = row.createCell(2);
String value = arr.get(2);
try {
    Number n = fmt.parse(value);
    cell.setCellStyle(popStyle);
    cell.setCellValue(n.intValue());
} catch(java.text.ParseException ex) {
    System.err.println("Row " + rowNum + ": " + value + ": " +
    ex.getMessage());
}

Now we can see that the numbers are formatted correctly.

Some More Formatting

The last bit of formatting we need is to eliminate the “Number Stored as Text” on the Date column. As you can see, some rows have an integer value and some have a non-numeric value mixed in. We handle that column as follows. Try to parse the value for a number to set it as a number, and if it fails set the value as a string.

String value = arr.get(4);
try {
    int year = Integer.parseInt(value);
    row.createCell(4).setCellValue(year);
} catch(NumberFormatException ex) {
    row.createCell(4).setCellValue(value);
}

Looks like the spreadsheet is now all fixed up. Woo hoo!

Summary

And that was a beginner introduction to using the Apache POI library to create a spreadsheet. We have just scratched the surface of what the library can do. Hope you learned something useful today.

Stay tuned for the next installment, where we'll show you how to convert CSV to Excel along with more formatting examples.