Set the Date Format Using Apache POI

Last updated: January 8, 2024

Written by: baeldung

Java IO

Refactor Java code safely — and automatically — with OpenRewrite.

Refactoring big codebases by hand is slow, risky, and easy to put off. That’s where OpenRewrite comes in. The open-source framework for large-scale, automated code transformations helps teams modernize safely and consistently.

Each month, the creators and maintainers of OpenRewrite at Moderne run live, hands-on training sessions — one for newcomers and one for experienced users. You’ll see how recipes work, how to apply them across projects, and how to modernize code with confidence.

Join the next session, bring your questions, and learn how to automate the kind of work that usually eats your sprint time.

Distributed systems often come with complex challenges such as service-to-service communication, state management, asynchronous messaging, security, and more.

Dapr (Distributed Application Runtime) provides a set of APIs and building blocks to address these challenges, abstracting away infrastructure so we can focus on business logic.

In this tutorial, we'll focus on Dapr's pub/sub API for message brokering. Using its Spring Boot integration, we'll simplify the creation of a loosely coupled, portable, and easily testable pub/sub messaging system:

>> Flexible Pub/Sub Messaging With Spring Boot and Dapr

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.

2. Create a Custom DataFormat

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.

3. Use a Default DataFormat

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 code backing this article is available on GitHub. Once you're logged in as a Baeldung Pro Member, start learning and coding on the project.