Bug 45531 - HSSFDateUtil.getJavaDate() should return Date for negative cell values
Summary: HSSFDateUtil.getJavaDate() should return Date for negative cell values
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Blocks: 60165
  Show dependency tree
Reported: 2008-08-03 19:40 UTC by Ondra Žižka
Modified: 2016-10-11 14:32 UTC (History)
1 user (show)

XLS file from OpenOffice 2.4.1 stored as Excel 97/2000/XP file (64.00 KB, application/octet-stream)
2008-08-07 02:21 UTC, Ondra Žižka

Note You need to log in before you can comment on or make changes to this bug.
Description Ondra Žižka 2008-08-03 19:40:51 UTC
HSSFDateUtil.getJavaDate( int ) returns null, because:

isValidExcelDate() returns false for negative numbers.

Although, I've tried filling my great-grandmother's birth date (1.8.1898), and Excel stores this as a negative number, which is convertible back to Date using the computation code in getJavaDate(double date, boolean use1904windowing).

Is there some reason for limiting isValidExcelDate() to positive numbers?

I suggest to overload HSSFDateUtil.getJavaDate( int, boolean, boolean ) which would allow converting negative values to date.

Thanks, Ondra Zizka.
Comment 1 Josh Micich 2008-08-04 01:10:23 UTC
This may be a limitation of Excel (I am having trouble entering dates prior to 1900).  If I am mistaken about this, can you please upload an example spreadsheet with a pre-1900 date stored in one of the cells?

Comment 2 Ondra Žižka 2008-08-05 05:58:00 UTC
When saving with Excel 2003 SP3, pre-1900 dates behave correctly.
I'll attach the file and check if it uses negative double or some hack.

However, I must admit, that the Excel file I've been trying so far was saved using OpenOffice.org.

Anyway, POI could be able to read it, using overloaded method... OOo generated Excel files also count :-)

May I fix the bug? What do I need for it?
Comment 3 Josh Micich 2008-08-06 00:56:02 UTC
The functionality (storing dates prior to 1900) you are suggesting makes sense, but it is important to identify exactly what Excel does with the same.

There are different levels of support that POI might provide:
(a) to read every file written by MS Office apps
(b) to produce files readable by MS Office apps
(c) to read files (produced by other apps) which are readable by MS Office
(d) to produce files also readable by other apps
(e) to read files (produced by other apps) which are not readable by MS Office, but presumably readable by the source app. 
(f) to produce files only readable by other apps

Generally speaking, levels (a)+(b) are high priority, (c)+(d) are low priority and (e)+(f) are mostly avoided.  There is still not enough information to classify this functionality request.  If you could do some further investigation that would be very helpful.  Please upload an OOO and/or Excel example spreadsheet with pre-1900 dates.

If you want to try writing a patch for this, here is some information about getting started:

Comment 4 Ondra Žižka 2008-08-07 02:21:01 UTC
Created attachment 22399 [details]
XLS file from OpenOffice 2.4.1 stored as Excel 97/2000/XP file

One column with dates 1.1.1898 to 1.1.1930.
Comment 5 Ondra Žižka 2008-08-12 17:55:48 UTC
Please excuse some delay on my side - I am applying for a job, so I've to focus that :-)  I will return to the feature request right after. (Posting this comment to prevent this feat.req. being dismissed.)