1. Introduction
When we’re working with dates in Apache POI, we want to make sure that they’re formatted correctly.
Fortunately, it’s easy to set the date format using Apache POI. In this tutorial, we’ll show how to define a custom DataFormat for a date as CellStyle with Apache POI and how to use existing DataFormats.
2. Starting Point
Our starting point will be a new XSSFWorkbook, an XSSFCell, and an already-created CellStyle:
XSSFWorkbook wb = new XSSFWorkbook();
CellStyle cellStyle = wb.createCellStyle();
wb.createSheet();
XSSFSheet sheet = wb.getSheetAt(0);
XSSFCell dateCell = sheet.createRow(0).createCell(0);
dateCell.setCellValue(new Date());
Since we haven’t set up our wished DataFormat yet, our Date would have been converted to a number and will be displayed as:
44898,9262857176
This way of representation is badly readable for us humans. Therefore, in the following, we’ll look at how we can create a better visualization by formatting.
First, we need to create a new CreationHelper. With the CreationHelper, we can create a new DataFormat with a specific Format. This DataFormat is internally stored and gets referenced by a short. We’ve to add it to the CellStyle itself and apply the CellStyle to the Cell:
CreationHelper createHelper = wb.getCreationHelper();
short format = createHelper.createDataFormat().getFormat("m.d.yy h:mm");
cellStyle.setDataFormat(format);
dateCell.setCellStyle(cellStyle);
After we’ve set up this custom CellStyle, our date will be formatted:
02.12.2022 21:30
However, if we create a new custom DataFormat, we should always remember that an Excel workbook supports a maximum number of 65.000 cell styles. So we should always reuse existing cell styles and apply them to multiple cells whenever possible.
As we learned, Apache POI uses shorts to link to different DataFormats. Excel already has a lot of builtin DataFormats which we can use by calling them directly by their short:
cellStyle.setDataFormat((short) 14);
dateCell.setCellStyle(cellStyle);
Afterward, we can get the DataFormat in a String representation with the following line of code:
cellStyle.getDataFormatString();
In our example, we’d get the following:
m/d/yy
The most common DataFormats are:
| Short Value |
Format |
| 14 |
m/d/yy |
| 15 |
d-mmm-yy |
| 16 |
d-mmm |
| 17 |
mmm-yy |
| 18 |
h:mm AM/PM |
| 19 |
h:mm:ss AM/PM |
| 20 |
h:mm |
| 21 |
h:mm:ss |
| 22 |
m/d/yy h:mm |
We should always use one of those data formats if it fits our needs because Excel will display them as one of their formats and not a custom one. This will also trigger Excel to use a localized visualization of the format.
4. Conclusion
As we can see, setting the date format using Apache POI is quick and easy but also necessary to visualize dates in a human-readable way. We should try it next time we work with dates in our spreadsheets.
The complete examples are available over on GitHub.
