Dates, Numbers & DataFormatter

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

Baseline date: 2026-10-09. This page was written against Apache POI 5.5.1 (the latest release on that date; the 6.0 line is announced but has not been released), Java 21 and later, Spring Boot 4.x on Spring Framework 7 and Jakarta EE 11. It explains the two things that surprise every developer who moves values between Java and a spreadsheet: a cell has no date type and no integer type, only a double that a number format decorates, and the text a user sees in a cell is produced by that format and not stored anywhere. The page covers how dates and times are stored, the DateUtil and LocalDateTime methods, DataFormatter (the class that renders a cell as Excel shows it), a table of format codes and the text they produce, and the rules for reading numbers that arrive as text or lose precision. Number formats as a styling tool (how to create and share them) are in Cell Styles, Fonts & Formats; the cell types and the ss.usermodel interfaces are in Spreadsheet Usermodel Basics; formula results are in Formulas & Evaluation. The examples use the Bookshelf scenario described in the section landing page.

How Excel stores dates, times and numbers

A spreadsheet cell holds one of a few raw values: a double, a string, a boolean, an error code, a formula, or nothing. There is no date cell and no integer cell. A date is a double that counts days, and the cell only looks like a date because its style carries a number format such as yyyy-mm-dd.

Concept How it is stored

Serial date

The integer part is the number of days since an epoch. 45658 is 1 January 2025 in the default system.

Time of day

The fractional part is the fraction of a day. 0.5 is 12:00 and 0.75 is 18:00, so 45658.75 is 1 January 2025 at 18:00. A time-only cell is a value below 1, such as 0.3958333 for 09:30.

Time zone

None. A date in a workbook is a "local wall clock" value without an offset. LocalDateTime and LocalDate are the natural Java types; Instant and ZonedDateTime need an explicit decision about the zone before writing.

Numbers

A 64-bit IEEE 754 double, exactly as double in Java. Excel displays at most 15 significant digits and silently keeps no more than that when a user types a longer number.

Integers

Stored as doubles too. getNumericCellValue() returns 1234.0, not 1234, and an integer larger than 9,007,199,254,740,992 (2 to the power 53) loses precision.

Text that looks like a number

A string cell. Excel marks it with a green triangle (number stored as text). POI never converts it for you.

The 1900 and 1904 date systems

An .xlsx workbook records which epoch it uses in the workbook properties (date1904).

System Serial 1 means Where it comes from

1900 (default)

1 January 1900. Serial 61 is 1 March 1900.

Excel for Windows and the default of every file POI creates.

1904

1 January 1904 (serial 0 is the epoch). The same visible date has a serial number 1,462 days lower.

Workbooks created in old Excel for Mac and some tools that export them. A date that is off by four years and one day is almost always a 1904 workbook read as 1900.

The 1900 system has a deliberate historical bug that Excel keeps for compatibility with Lotus 1-2-3: it treats 1900 as a leap year and has a serial 60 for the non-existent 29 February 1900. POI follows Excel, so serial numbers below 60 do not match a real count of calendar days, and DateUtil.getLocalDateTime(60.0) returns 1 March 1900 (there is no way to represent the phantom day in java.time). In practice any date after 1 March 1900 converts correctly and the issue only matters for very old historical data.

POI handles the epoch for you whenever it can see the workbook: Cell.getDateCellValue() and Cell.getLocalDateTimeCellValue() read the flag of the workbook the cell belongs to, and XSSFWorkbook.isDate1904() exposes it. The static DateUtil methods have no workbook, so they take a use1904windowing boolean that you must pass yourself (the overloads without it assume 1900).

Writing dates

Cell has overloads for the Java date types, and each one stores the matching serial number.

Method Behaviour

setCellValue(LocalDate)

Stores the date at midnight as a serial number. Preferred for dates.

setCellValue(LocalDateTime)

Stores date and time as a serial number with a fraction. Preferred for timestamps.

setCellValue(java.util.Date)

Same, using the JVM default time zone to find the local date and time. Prefer the java.time overloads.

setCellValue(Calendar)

Same, using the calendar’s own fields. Kept for old code.

setCellValue(double)

Stores a raw serial number; use it only for a time of day (a fraction of a day) or when you convert yourself with DateUtil.getExcelDate.

Writing the value is only half of the job: a date cell without a date number format shows the raw serial (45658). Always set a style with a date format, and create that style once and reuse it for all cells, because an .xlsx workbook is limited to 64,000 cell styles (the sharing rule is explained in Cell Styles, Fonts & Formats).

The first half of the example below writes a loan sheet with a date, a timestamp and a time-only cell; the second half reads the file back with the matching getters. It prints the serial number of each cell, whether POI considers it a date, and the converted java.time value.

import java.io.IOException;
import java.nio.file.Path;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteDates {

    public static void main(String[] args) throws IOException {
        Path file = Path.of("books-dates.xlsx");
        try (Workbook workbook = new XSSFWorkbook()) {
            CreationHelper helper = workbook.getCreationHelper();
            Sheet sheet = workbook.createSheet("Loans");

            // One style per format, created once and reused (the style limit is 64,000 in .xlsx).
            CellStyle dateStyle = workbook.createCellStyle();
            dateStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-mm-dd"));
            CellStyle dateTimeStyle = workbook.createCellStyle();
            dateTimeStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-mm-dd hh:mm"));
            CellStyle timeStyle = workbook.createCellStyle();
            timeStyle.setDataFormat(helper.createDataFormat().getFormat("hh:mm"));

            Row row = sheet.createRow(0);

            Cell loanDate = row.createCell(0);
            loanDate.setCellValue(LocalDate.of(2025, 1, 1));       // stored as the serial number 45658
            loanDate.setCellStyle(dateStyle);

            Cell returnBy = row.createCell(1);
            returnBy.setCellValue(LocalDateTime.of(2025, 1, 15, 18, 0)); // 45672.75
            returnBy.setCellStyle(dateTimeStyle);

            Cell openingTime = row.createCell(2);
            openingTime.setCellValue(LocalTime.of(9, 30).toSecondOfDay() / 86_400.0); // a time of day is a fraction of a day
            openingTime.setCellStyle(timeStyle);

            try (var out = java.nio.file.Files.newOutputStream(file)) {
                workbook.write(out);
            }
        }

        try (Workbook workbook = WorkbookFactory.create(file.toFile(), null, true)) {
            Row row = workbook.getSheet("Loans").getRow(0);
            for (Cell cell : row) {
                System.out.println(cell.getAddress() + " serial=" + cell.getNumericCellValue()
                    + " isDate=" + DateUtil.isCellDateFormatted(cell));
            }
            LocalDateTime returnBy = row.getCell(1).getLocalDateTimeCellValue();
            System.out.println(returnBy.format(DateTimeFormatter.ISO_LOCAL_DATE_TIME));
            LocalDate loanDate = row.getCell(0).getLocalDateTimeCellValue().toLocalDate();
            LocalTime opening = row.getCell(2).getLocalDateTimeCellValue().toLocalTime();
            System.out.println(loanDate + " " + opening);
        }
    }
}

Running it prints the following. The three cells hold the numbers 45658, 45672.75 and 0.3958333..., and the number format is the only reason they are dates:

A1 serial=45658.0 isDate=true
B1 serial=45672.75 isDate=true
C1 serial=0.3958333333333333 isDate=true
2025-01-15T18:00:00
2025-01-01 09:30

Source: POI Quick Guide, Create date cells

Reading dates and the DateUtil class

DateUtil is the static helper behind every date conversion. Its main members:

Method Purpose

isCellDateFormatted(Cell)

True if the cell is numeric and its number format is a date or time format. This is the test that tells a date from a number before calling getLocalDateTimeCellValue().

isADateFormat(int formatIndex, String formatString)

The same decision for a format that is not attached to a cell yet.

isCellInternalDateFormatted(Cell)

True only for the built-in date formats (ids such as 14 or 22), not for custom ones.

getExcelDate(LocalDate) and getExcelDate(LocalDateTime)

Java to serial number. The overloads with a boolean take the 1904 flag.

getLocalDateTime(double) and getLocalDateTime(double, boolean)

Serial number to LocalDateTime; the boolean is the 1904 flag.

getJavaDate(double)

Serial number to the legacy java.util.Date in the default time zone.

isValidExcelDate(double)

False for negative numbers, which are not valid dates in the 1900 system.

enableThreadLocalCache(boolean)

Controls a thread-local cache that speeds up the repeated date conversions of a big sheet. Leave it enabled (the default).

Cell.getLocalDateTimeCellValue() returns null for a blank cell and throws IllegalStateException ("Cannot get a NUMERIC value from a STRING cell") for a text cell, so an import has to look at the cell type first.

How POI decides that a cell is a date

isCellDateFormatted does not look at the value. It looks at the cell’s number format and applies a heuristic on the format code: letters such as y, m, d, h, s outside quotes and brackets make it a date format, and so does the elapsed-time code [h]:mm. Consequences:

  • A number with a style General is never a date, even if the value is 45658. There is no way to know that the author meant a date; that decision belongs to your import validation.

  • A custom code that happens to contain date letters in a literal (for example 0.0 "d" for days) is protected by the quotes, but unquoted letters are read as date codes.

  • The format of a cell can be changed by conditional formatting, and the same cell then shows as a date or as a number depending on its value. Use the isCellDateFormatted(Cell, ConditionalFormattingEvaluator) overload if you want to respect it (the evaluator is described in Data Validation & Conditional Formatting).

  • A string cell containing 2025-01-01 is not a date for POI. Users paste ISO dates as text all the time.

Coercing real-world cells

An upload endpoint cannot trust the type of a cell: the same column holds a real date in one row, an ISO string in the next, a formula in the third and an empty cell in the fourth. The example gathers that logic in two small methods that return Optional so the caller can attach a row number to the validation message (the full import flow is in a later page of the section). A formula cell is evaluated first with FormulaEvaluator.evaluateFormulaCell, which returns the type of the result.

import java.io.IOException;
import java.math.BigDecimal;
import java.nio.file.Path;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.Optional;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class DateCoercion {

    private static final DateTimeFormatter ISO = DateTimeFormatter.ISO_LOCAL_DATE;

    /** Reads a "published on" cell that users fill as a real date or as ISO text. */
    static Optional<LocalDate> readDate(Cell cell, FormulaEvaluator evaluator) {
        if (cell == null) {
            return Optional.empty();
        }
        CellType type = cell.getCellType() == CellType.FORMULA
            ? evaluator.evaluateFormulaCell(cell)
            : cell.getCellType();
        return switch (type) {
            case NUMERIC -> DateUtil.isCellDateFormatted(cell)
                ? Optional.of(cell.getLocalDateTimeCellValue().toLocalDate())
                : Optional.empty();              // a plain number, not a date: report it as invalid
            case STRING -> {
                try {
                    yield Optional.of(LocalDate.parse(cell.getStringCellValue().trim(), ISO));
                } catch (DateTimeParseException e) {
                    yield Optional.empty();
                }
            }
            default -> Optional.empty();         // blank, boolean, error
        };
    }

    /** Reads a price cell as an exact decimal, whatever the cell type. */
    static Optional<BigDecimal> readPrice(Cell cell, FormulaEvaluator evaluator) {
        if (cell == null) {
            return Optional.empty();
        }
        CellType type = cell.getCellType() == CellType.FORMULA
            ? evaluator.evaluateFormulaCell(cell)
            : cell.getCellType();
        try {
            return switch (type) {
                case NUMERIC -> Optional.of(BigDecimal.valueOf(cell.getNumericCellValue()));
                case STRING -> Optional.of(new BigDecimal(cell.getStringCellValue().trim().replace(',', '.')));
                default -> Optional.empty();
            };
        } catch (NumberFormatException e) {
            return Optional.empty();
        }
    }

    public static void main(String[] args) throws IOException {
        try (Workbook workbook = WorkbookFactory.create(Path.of("books-import.xlsx").toFile(), null, true)) {
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                System.out.println(readDate(row.getCell(1), evaluator) + " " + readPrice(row.getCell(3), evaluator));
            }
        }
    }
}

Source: POI Javadoc, DateUtil

DataFormatter: the text Excel shows

DataFormatter turns a cell into the string the user sees in Excel: it applies the number format of the cell’s style, including thousands separators, the number of decimals, percentages, currencies, dates and the colours and sections of a format code. This is what you want when you read a spreadsheet as "a table of text", for example to import a catalogue, to build a preview for the user or to write a CSV that matches the screen.

getNumericCellValue() and getStringCellValue() return the raw content; DataFormatter.formatCellValue(cell) returns the displayed text. They give different answers whenever a style is involved:

  • A numeric 0.256 with format 0% is 0.256 raw and 26% formatted.

  • A numeric 42 with format 000000 is 42.0 raw and 000042 formatted. This is how an identifier with leading zeros survives a round trip through a number cell.

  • A date cell is a double for getNumericCellValue() and 2025-01-01 for formatCellValue.

  • A string cell is returned unchanged by both.

  • A boolean is TRUE or FALSE, an error cell is the error text (#DIV/0!), and a blank cell is the empty string.

The formatCellValue overloads and the options

Member Behaviour

formatCellValue(Cell)

Formats the cell. For a formula cell it returns the formula text (for example 0.1+0.2) unless cached values are enabled, because no evaluator is available.

formatCellValue(Cell, FormulaEvaluator)

Evaluates the formula first and formats the result with the style of the cell. This is the overload to use on workbooks that contain formulas.

formatCellValue(Cell, FormulaEvaluator, ConditionalFormattingEvaluator)

The same, also applying number formats and colours that conditional formatting would give the cell.

new DataFormatter(Locale)

Formats with the symbols and names of the given locale: decimal and grouping separators, and month and day names. The default constructor uses the JVM default locale, which makes the output differ between a laptop and a container.

new DataFormatter(Locale, boolean emulateCSV)

With emulateCSV true, the output imitates a CSV export of the sheet (the Javadoc describes it as emulating CSV output); the default is false.

setUseCachedValuesForFormulaCells(boolean)

When true, a formula cell is formatted from the result cached in the file, without evaluating anything. Default false in 5.5.1.

setUse4DigitYearsInAllDateFormats(boolean)

Turns the 2-digit years of built-in formats such as m/d/yy into 4-digit years.

addFormat(String, Format) and setDefaultNumberFormat(Format)

Register your own java.text.Format for a format string, or for numbers with the General format.

formatRawCellContents(double, int, String)

Formats a raw number with a format index and code, without a cell. Useful for tests.

A DataFormatter caches the Format objects it creates, so create one and reuse it for the whole import of one file. It keeps mutable state and is not documented as thread safe: do not share an instance between threads; one per request (or one per import job) is cheap.

Locale: the same cell, different text

A format code such as mmmm yyyy is locale independent in the file, and the rendering is not. With the cell value 45658.5 the formatter returns January 2025 for Locale.US, Januar 2025 for Locale.GERMANY and janvier 2025 for Locale.FRANCE. Numbers follow the same rule: 1234.5 with #,##0.00 is 1,234.50 for the US and 1.234,50 for Germany. Pass the locale of the user who wrote the file (or a fixed Locale.ROOT/Locale.US for machine-to-machine imports) and do not rely on the server default. The built-in formats with ids below 164 (such as id 14, shown as m/d/yy) use fixed US-style patterns in POI and only the names are localised.

Formula cells, the evaluator and the cached value

A formula cell stores two things in the file: the formula text and the last result that Excel (or the writing library) calculated, called the cached value. POI’s DataFormatter can show either.

Call POI 5.5.1 result for 0.1+0.2 Remarks

formatCellValue(cell)

0.1+0.2 (the formula text)

Surprising to most users. This is the long-standing 5.x default.

formatCellValue(cell, evaluator)

0.3

The evaluator calculates the formula now; slow for large sheets, fails on unsupported functions.

setUseCachedValuesForFormulaCells(true) then formatCellValue(cell)

The cached value, formatted

Fast; correct only if the file has a cached value (a workbook written by POI has none until it is evaluated).

The POI site announces that 6.0 will make DataFormatter use cached formula values by default. The 6.0 release has not shipped (as of 2026-10-09) and its final behaviour must be checked against its release notes, but the practical advice is the same in both lines: set the option explicitly, so your import behaves the same on 5.5.x and on 6.0. Choose the cached value for uploads generated by Excel and the evaluator when the workbook was produced by a library that does not write cached results. Evaluation, its limits and its cache are explained in Formulas & Evaluation.

Format codes and the text they produce

The table shows what DataFormatter (Locale.US, POI 5.5.1) returns for a cell with each format code and value. It was produced by running the formatter on a real workbook, not copied from documentation. A code with several sections separated by semicolons applies the first section to positives, the second to negatives, the third to zero and the fourth to text; a bracket such as [Red] selects a colour that DataFormatter ignores (it returns text, not colours).

Format code Cell value Displayed value Notes

General

1234.5

1234.5

No grouping, up to 15 digits, no trailing zeros.

General

9780306406157

9.78031E+12

Long integers fall into scientific notation: never store an ISBN-13 as a number.

0

1234.567

1235

Rounds half away from zero, like Excel.

0.00

1234.567

1234.57

#,##0

1234567.891

1,234,568

Thousands separator of the locale.

#,##0.00

1234567.891

1,234,567.89

0%

0.256

26%

The value is multiplied by 100.

0.00%

0.256

25.60%

0.00E+00

1234.5

1.23E+03

Scientific.

"$"#,##0.00

1234.5

$1,234.50

A quoted literal is a currency symbol; there is no Java currency conversion.

#,##0.00;[Red](#,##0.00)

-1234.5

(1,234.50)

Negative section; the red colour is lost.

000000

42

000042

Zero padding for identifiers.

# ?/?

0.75

3/4

Fraction.

@

text ISBN

ISBN

Text format; the cell is a string cell.

yyyy-mm-dd

45658

2025-01-01

ISO date.

dd/mm/yyyy

45658

01/01/2025

d mmm yyyy

45658

1 Jan 2025

Month name depends on the locale.

mmmm d, yyyy

45658

January 1, 2025

dddd

45658

Wednesday

Day of the week.

yyyy-mm-dd hh:mm

45658.75

2025-01-01 18:00

mm after h is minutes, otherwise months.

h:mm AM/PM

45658.75

6:00 PM

12-hour clock.

[h]:mm

1.5

36:00

Elapsed time can exceed 24 hours.

hh:mm:ss

0.5

12:00:00

A time of day.

built-in id 14 (m/d/yy)

45658

1/1/25

Fixed US-style pattern.

built-in id 22 (m/d/yy h:mm)

45658.5

1/1/25 12:00

Two reminders when you write these formats rather than read them: create the code with DataFormat.getFormat(String) and reuse the style, and remember that the code is stored in the file, so a decimal separator in the code (0.00) is always a dot and the locale only decides how it is rendered.

Source: POI Javadoc, DataFormatter

Reading numbers: text, precision and BigDecimal

Numbers stored as text

A catalogue sheet usually contains ISBN, shelf code and price columns that users type by hand, so a number may arrive as a string cell (0012345), as a numeric cell (12345) or as a formula result. A reliable rule is to read what the user sees and then decide:

  1. For identifiers (ISBN, inventory code, postal code) use DataFormatter.formatCellValue. A string cell comes back unchanged, with its leading zeros; a numeric cell with format 000000 comes back padded. Do not use getNumericCellValue() for them.

  2. For quantities and prices, branch on the cell type: use getNumericCellValue() for numeric cells and parse the trimmed text for string cells, normalising the decimal separator according to the locale you expect. POI offers no locale-aware parser for text; java.text.NumberFormat.getInstance(Locale) or BigDecimal do that work.

  3. Reject (and report with the row number) a cell whose text cannot be parsed. Never silently turn it into zero.

An ISBN-13 stored as the number 9780306406157 also shows the limit of General: it renders as 9.78031E+12, and because a double has about 15 reliable digits, a longer number such as a 16-digit card or order number is already damaged in the file. Write such identifiers as strings and apply the @ format so Excel keeps them as text.

Precision and BigDecimal

A double cannot represent most decimal fractions exactly: 0.1 + 0.2 is 0.30000000000000004, and a Java double read from a cell has the same property. DataFormatter hides this because it rounds to the cell’s format (0.3), getNumericCellValue() shows it. Rules for money and exact quantities:

  • Read with BigDecimal.valueOf(double), which uses the shortest decimal text of the double (0.1 stays 0.1), and never new BigDecimal(double), which exposes the binary expansion.

  • Round explicitly with setScale(2, RoundingMode.HALF_UP) at the point where the business rule says so. Do not rely on the number format to round: it only changes the display, not the stored value.

  • When you write, cell.setCellValue(bigDecimal.doubleValue()) is the only option (a cell is a double); keep the exact amount in the database and treat the workbook as a presentation.

  • Totals calculated with a formula (SUM) use doubles in Excel as well, so a sheet total and a BigDecimal total can differ in the last digit.

The next example reads the first two data rows of the catalogue, shows the identifier as the user sees it, formats the price with a German locale and reads the exact value, whether the price is a typed number or a formula. It uses the WorkbookFactory.create(File, String, boolean) overload with readOnly true so the source file is never modified (see Spreadsheet Usermodel Basics for opening workbooks).

import java.io.IOException;
import java.math.BigDecimal;
import java.nio.file.Path;
import java.util.Locale;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ReadBooks {

    public static void main(String[] args) throws IOException {
        // The user's locale decides the decimal separator and the month names, not the server's default.
        DataFormatter formatter = new DataFormatter(Locale.GERMANY);

        try (Workbook workbook = WorkbookFactory.create(Path.of("books-import.xlsx").toFile(), null, true)) {
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                Cell isbnCell = row.getCell(0);
                Cell priceCell = row.getCell(3);
                if (isbnCell == null || priceCell == null) {
                    continue;
                }
                // 1. The text exactly as Excel shows it: leading zeros, "9.78E+12" and all.
                String shown = formatter.formatCellValue(isbnCell);

                // 2. A formula cell: with the evaluator the result is formatted, without it the formula text is returned.
                String price = formatter.formatCellValue(priceCell, evaluator);

                // 3. The exact numeric value, when the cell really holds a number.
                BigDecimal exact = null;
                if (priceCell.getCellType() == CellType.NUMERIC
                        || priceCell.getCellType() == CellType.FORMULA
                            && evaluator.evaluateFormulaCell(priceCell) == CellType.NUMERIC) {
                    exact = BigDecimal.valueOf(priceCell.getNumericCellValue());
                }
                System.out.println(shown + " | " + price + " | " + exact);
            }
        }
    }
}

With a first row holding the text 0012345 and the formula 19.9*2, and a second row holding the number 9780306406157 and the value 1234.5, the program prints (German locale, price format #,##0.00):

0012345 | 39,80 | 39.8
9,78031E+12 | 1.234,50 | 1234.5

Source: POI Quick Guide, Cell contents and Formula evaluation

Memory, performance and threading

  • Date conversions and DataFormatter are cheap compared with parsing the file, but a million-cell sheet calls them a million times. Reuse one DataFormatter and one FormulaEvaluator per workbook and keep the default thread-local cache of DateUtil enabled.

  • formatCellValue(cell, evaluator) evaluates every formula it meets; on a big workbook prefer the cached value option, or evaluate only the columns you need. The event API (see Streaming Reads: Event & SAX APIs) formats cells with the same DataFormatter, so the semantics on this page apply there too.

  • Streaming writes keep the style limits of the file, so create the date, number and currency styles once, before the loop, even with SXSSF (see Streaming Writes with SXSSF).

  • DataFormatter instances and FormulaEvaluator instances are not thread safe. One document, one evaluator, one formatter per thread.

Pitfalls

Symptom Cause and fix

Dates show as 45658

The cell has no date format. Set a style with a date format on every date cell.

Dates are off by four years and one day

The workbook uses the 1904 system and a DateUtil method without the flag was used. Use the cell methods, or read isDate1904() and pass it.

A date shifts by a day or several hours

Conversion through java.util.Date and the JVM zone. Use LocalDate and LocalDateTime, and decide the zone yourself if you start from an Instant.

IllegalStateException: Cannot get a NUMERIC value from a STRING cell

The user typed text. Check getCellType() (and evaluate formulas) before calling a typed getter.

formatCellValue prints a formula instead of its result

No evaluator and cached values are off. Pass the evaluator, or call setUseCachedValuesForFormulaCells(true).

The same file imports differently on the laptop and in production

DataFormatter created with the default locale. Always pass a Locale.

9.78031E+12 instead of an ISBN

The identifier was stored as a number. Write it as a string with the @ format; read it with formatCellValue.

Money differs in the last cent

Double arithmetic and new BigDecimal(double). Use BigDecimal.valueOf and round explicitly.

A text date such as 01/02/2025 is read as 1 February by one user and 2 January by another

Text dates are ambiguous. Accept real date cells and ISO text (yyyy-MM-dd) only, and reject the rest with a clear message.

What POI does not provide

  • No conversion of an Excel format code to a java.time.format.DateTimeFormatter pattern, and no parser for text dates or locale-formatted numbers: the formatter goes from a value to text only.

  • No time zone or offset in a cell. Excel has no concept for it.

  • No automatic detection of "this numeric column is really a date" when the style is General.

  • No fixed-point or decimal cell: every number is a double.

  • DataFormatter returns text only; it drops the colours of [Red]-style sections (the colour matters for display tools and is not exposed by this class).

Changes between 4.x, 5.x and 6.0

Line Relevant changes

3.x and earlier

Cell.getDateCellValue() and the Date overloads were the only date API. Cell types were integer constants, deprecated in 3.15.

4.x

The integer cell types were removed in 4.0 in favour of the CellType enum; getCellTypeEnum() was deprecated. The java.time overloads (setCellValue(LocalDate), setCellValue(LocalDateTime), getLocalDateTimeCellValue()) were added in 4.1. POI follows SemVer since 4.0.0.

5.x

The CellType enum is the only API for cell types (getCellTypeEnum() was removed in 5.0). The java.time overloads (available since 4.1), DateUtil.getLocalDateTime and the DataFormatter cached-value switch are available, with the default set to false. The current line is 5.5.1.

6.0 (announced, not released)

Per the POI site: DataFormatter uses cached formula values by default; deprecated APIs are removed; JDK 17 is needed. Check the final release notes before upgrading.

References