Bug 43551 - [PATCH] Fixes bug when writing dates to excel files w/ 1904 date windowing
Summary: [PATCH] Fixes bug when writing dates to excel files w/ 1904 date windowing
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-dev
Hardware: All All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords: PatchAvailable
Depends on:
Blocks:
 
Reported: 2007-10-04 07:03 UTC by Alex Jacoby
Modified: 2007-12-04 09:07 UTC (History)
0 users



Attachments
Fixes bug, adds test cases, adds comments (13.86 KB, patch)
2007-10-04 07:04 UTC, Alex Jacoby
Details | Diff
Test data file for 1900 date windowing (6.00 KB, application/octet-stream)
2007-10-06 12:41 UTC, Alex Jacoby
Details
Test data file for 1904 date windowing (6.00 KB, application/octet-stream)
2007-10-06 12:43 UTC, Alex Jacoby
Details
invalid patch (23.74 KB, patch)
2007-10-06 16:51 UTC, Pavel Krupets
Details | Diff
new patch added several tests and fixed 1904 start date problem (28.04 KB, patch)
2007-10-07 11:31 UTC, Pavel Krupets
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Jacoby 2007-10-04 07:03:32 UTC
Reading dates from files w/ 1904 date windowing was handled, but writing dates
wasn't.  This fixes it and adds a test case.
Comment 1 Alex Jacoby 2007-10-04 07:04:43 UTC
Created attachment 20918 [details]
Fixes bug, adds test cases, adds comments
Comment 2 Alex Jacoby 2007-10-04 07:07:21 UTC
Patch effects the follwing files:
HSSFCell, HSSFDateUtil, their respective test classes, and the test data files
1900DateWindowing.xls and 1904DateWindowing.xls.
Comment 3 Pavel Krupets 2007-10-06 12:18:01 UTC
I think you forgot to attach xls files :)
Comment 4 Alex Jacoby 2007-10-06 12:41:42 UTC
Created attachment 20936 [details]
Test data file for 1900 date windowing

Sorry -- new to subversion -- I had read that it did binary diffs for updates,
but of course I should have realized they wouldn't be included in a patch file.
Comment 5 Alex Jacoby 2007-10-06 12:43:13 UTC
Created attachment 20937 [details]
Test data file for 1904 date windowing

I added one cell to each of these files: the first is untouched and is used
just for reading.  The new cell is used to write and then read back the value.
Comment 6 Pavel Krupets 2007-10-06 13:25:14 UTC
need to figure out how to access 1900/1904 property from HSSFCell.
Comment 7 Pavel Krupets 2007-10-06 13:25:47 UTC
sorry from Date function :)) Was thinking about one thing and writing about another
Comment 8 Alex Jacoby 2007-10-06 13:32:14 UTC
If you mean from the HSSFDateUtil class, you need to pass the info as a
parameter (DateUtil is just a collection of static methods, so there's no other
way for it to know which type of date windowing is being used by its caller.)
My patch replicates the same style as the earlier patch which fixed the read
error: it passes a boolean use1904windowing parameter to the effected method.
Comment 9 Pavel Krupets 2007-10-06 15:01:21 UTC
No. Some functions are 1900/1904 sensitive.
Comment 10 Alex Jacoby 2007-10-06 15:07:35 UTC
(In reply to comment #9)
> No. Some functions are 1900/1904 sensitive.

I just double-checked.  The only functions which are 1900/04 sensitive have a
boolean parameter saying that.  (Well, the old one-param versions are in there
so they don't break existing code, but they just delegate to the 2-param version
and default to 1900 date windowing since it's more common.)

We're talking about HSSFDateUtil, right?  All methods are static, and its
constructor is private.  Maybe I'm missing something...
Comment 11 Pavel Krupets 2007-10-06 15:14:36 UTC
the following functions use HSSFDateUtil and need access to 1900/1904 flag

org.apache.poi.hssf.record.formula.functions.Date#evaluate,
org.apache.poi.hssf.record.formula.functions.Day#evaluate,
org.apache.poi.hssf.record.formula.functions.Month#evaluate,
org.apache.poi.hssf.record.formula.functions.Year#evaluate

month might ignore it but day couldn't because of Excel Day Bug, year couldn't
as well.
Comment 12 Alex Jacoby 2007-10-06 16:27:57 UTC
Got it.  I hadn't looked in the scratchpad area at all, or compiled it.

It looks like org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator is solely
responsible for grabbing the numeric value from the cell and sticking it in a
NumberEval, so it could also pass along 1900/04 windowing information to the
NumberEval if we extended NumberEval to store that info too.

Sound doable?
Comment 13 Alex Jacoby 2007-10-06 16:42:51 UTC
Just to confirm: The patch won't *break* any of the existing code.  The
functions will continue to work as before, always assuming 1900 date windowing
(which is probably right in nearly all cases, as long as you aren't on a Mac). 
It looks like we can fix that in a separate patch.

So, anyone want to commit this patch first?  Or is there something else I should do?
Comment 14 Pavel Krupets 2007-10-06 16:51:19 UTC
Created attachment 20938 [details]
invalid patch
Comment 15 Alex Jacoby 2007-10-06 17:04:19 UTC
Looks good to me :)
Comment 16 Pavel Krupets 2007-10-07 11:31:16 UTC
Created attachment 20939 [details]
new patch added several tests and fixed 1904 start date problem

this is the patch to apply, don't forget xls files
Comment 17 Nick Burch 2007-12-04 04:08:01 UTC
Is this patch now final and ready to be committed?
Comment 18 Alex Jacoby 2007-12-04 05:16:29 UTC
Yes -- I believe Pavel and I have both finished working on it.
Comment 19 Nick Burch 2007-12-04 09:07:15 UTC
Thanks for this, now committed.