Bug 63292 - DataFormatter.formatCellValue ignores use1904Windowing w/4-part date formats
Summary: DataFormatter.formatCellValue ignores use1904Windowing w/4-part date formats
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 4.0.0-FINAL
Hardware: PC Mac OS X 10.1
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2019-03-27 18:18 UTC by Shawn Smith
Modified: 2019-03-30 20:13 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Shawn Smith 2019-03-27 18:18:14 UTC
Most DataFormatter formatting methods take a 'use1904Windowing' argument to be able to format dates correctly based on whether the original spreadsheet was created using an epoch year of 1900 or 1904.  But w/4-part format strings the 'use1904Windowing' argument is ignored and the date is formatted assuming 1900.

Workaround: when calling DataFormatter.formatRawCellContents() method you can convert the input number to always use 1900 epoch before passing it in.  But this workaround is harder to apply to DataFormatter.formatCellValue().

Here's a test case that illustrates two cases of calling DataFormatter.formatRawCellContents():
 a) using a 1-part format string works
 b) using a 4-part format string ignores 'use1904Windowing'

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.junit.Test;

import java.util.Date;

import static org.junit.Assert.assertEquals;

public class FormatterTest {
    public void test1904With4PartFormat() {
        Date date = new Date();
        int formatIndex = 105;
        String formatString1 = "[$-F400]m/d/yy h:mm:ss\\ AM/PM";
        String formatString4 = "[$-F400]m/d/yy h:mm:ss\\ AM/PM;[$-F400]m/d/yy h:mm:ss\\ AM/PM;_-* \"\"??_-;_-@_-";

        String s1900, s1904;

        // These two format calls return the same thing, as expected:
        // The assertEquals() passes with 1-part format
        s1900 = format(date, formatIndex, formatString1, false);
        s1904 = format(date, formatIndex, formatString1, true);
        assertEquals(s1900, s1904);  // WORKS

        // These two format calls should return the same thing but don't:
        // It fails with 4-part format because the call to CellFormat ignores 'use1904Windowing'
        s1900 = format(date, formatIndex, formatString4, false);
        s1904 = format(date, formatIndex, formatString4, true);
        assertEquals(s1900, s1904);  // FAILS
        // Results in org.junit.ComparisonFailure:
        // Expected :3/27/19 12:58:34 PM
        // Actual   :3/26/15 12:58:34 PM

    private String format(Date date, int formatIndex, String formatString, boolean use1904Windowing) {
        DataFormatter formatter = new DataFormatter();
        double n = DateUtil.getExcelDate(date, use1904Windowing);
        return formatter.formatRawCellContents(n, formatIndex, formatString, use1904Windowing);
Comment 1 Greg Woolsey 2019-03-30 20:13:44 UTC
Good catch.  I found where the use1904Windowing flag was not passed when it should be, and was able to get it or pass it in all calling contexts.

Thanks for the test case - it now passes as of r1856648.