Bug 48336 - Error reading date
Summary: Error reading date
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.5-FINAL
Hardware: Other Linux
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2009-12-03 09:46 UTC by vdhanda
Modified: 2009-12-03 21:06 UTC (History)
1 user (show)

Excel file (7.50 KB, application/vnd.ms-excel)
2009-12-03 09:46 UTC, vdhanda
BiffViewer dump (83.14 KB, text/plain)
2009-12-03 09:47 UTC, vdhanda

Note You need to log in before you can comment on or make changes to this bug.
Description vdhanda 2009-12-03 09:46:09 UTC
Created attachment 24665 [details]
Excel file

The attached Excel has a date value in row 1, column D.  POI doesn't read it as a date.  The file was created by SoftArtisans OfficeWriter for Excel (http://officewriter.softartisans.com).

Comment 1 vdhanda 2009-12-03 09:47:09 UTC
Created attachment 24666 [details]
BiffViewer dump
Comment 2 Josh Micich 2009-12-03 10:04:23 UTC
There is something wrong with cell D1.  It displays as '#######################' in my Excel 2007.  Excel has roll-over tip text that provides more information: "Dates and times that are negative or too large display as ######."
Indeed the cell value is -1094
HSSFCell.getNumericCellValue() should return -1094.0 and HSSFCell.getDateCellValue() should return null due to this invalid date value.

The cell has a custom format 'mmm-dd-yyyy' which suggests that the application that wrote this file intended for the cell to have a date value.

I am marking this bug as invalid because it appears that the problem is with "SoftArtisans OfficeWriter"
Comment 3 vdhanda 2009-12-03 10:14:08 UTC
Can you try opening it in OpenOffice.  It shows the correct value as 12/31/1896.
Comment 4 Josh Micich 2009-12-03 15:17:26 UTC
A short term work around is for you to use methods from DateUtil like setCalendar to calculate dates with negative 'serial date' values.  However it seems like you might be requesting a real change (to how POI behaves).

Normally such an enhancement request is rejected on the grounds that it involves POI doing something that Excel considers invalid.  However, I agree that the functionality you desire is very reasonable.  I think the restriction on dates before 1900 is silly.  On the other hand, we can't go about implementing every request for features from other applications that go beyond the Excel file format spec.

I want to wait for comments from other POI committers before doing anything.  There is also the general question of how we accept/reject/prioritize bugs and feature requests that raise issues of compliance with other applications.  I don't know of any POI documentation that deals with this.
Comment 5 vdhanda 2009-12-03 21:06:58 UTC
Thanks Josh.    

It seems that the "culprit" is HSSFDateUtil.isValidExcelDate(double value).  The fix might be as simple as unconditionally returning "true" in that method instead of the current "return (value > -Double.MIN_VALUE);"

I pretty much did that and it worked for me.

Perhaps, as a courtesy, one could "guard" this change with a -DinterpretNegativeValuesAsDate flag.