incubator-ooo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dennis E. Hamilton" <dennis.hamil...@acm.org>
Subject RE: Nominate release blocker: 118999 - Leap year not correctly calculated
Date Sun, 04 Mar 2012 05:31:35 GMT
We've wandered away from the failure to convert 2000-02-29 and 2400-02-29 correctly on reading
of those dates in saved Calc cells to a possible interaction with the Excel leap-year calendar
bug.  The Smoke test will also demonstrate whether or not that bug is evident in the Calc
cases.  It is not.

But the but should be revealed when transferring spreadsheets between Calc and Excel.  

 - Dennis

LOOKING AROUND FURTHER

[It would be good to build dataflow diagrams that explain these passing of documents through
different consumers and producers. What follow below is not so meticulous.  It is a trial.
 In order to work through this more systematically, there need to be more carefully staged
tests and alternative smoke tests.]

In converting to Excel 2003 XML format, the result should be opened in Excel to see how much
is preserved or not.  The discrepancy for dates before 1900-03-01 should also appear.  

If the serial days column data is lost, that is a bug in the export (or the re-import).

SAVING TO XLS (EXCEL 27/2000/ME)

I saved the Smoke Test document to Excel 97/2000/ME .xls format.  

It round trips back into AOO just fine.  All formatting is preserved.

If I open the saved .xls in Excel, it will show the dates from serial days 0 to 2 as 1900-01-00
(!), 1900-01-01, and 1900-01-02.  

(It is not possible to enter 1900-01-00 as a date.  Excel displays this because the 0 serial-day
value was delivered in the cell exported by Calc.  It is not possible to enter that date into
the cell, though one can enter 0 into a numeric cell and then change its format to date, or
obtain the 0 as a formula result, etc.)  

Excel also shows Serial Day 60 as 1900-02-29.  For all later dates and serial-day numbers,
Calc and Excel agree completely, and the Calc formatting is preserved in Excel also.  I added
that screen shot to show how the Excel "leap-year bug" shows up using the same smoke test
document.

LO 3.5.0 will open the .XLS file correctly as stored.  (I see I have an error in the Smoke
Test .ODP for the expected result in C16.  It should be 2100-02-29, the value actually taken
as text and not a date.) 

SAVING TO XML (EXCEL 2003 XML FORMAT)

To save the Smoke Test as Excel 2003 XML format requires a JRE so it doesn't work on my bare
Windows 8 64-bit and Vista SP1 32-bit test installs.  To get quick results, I did the Excel
2003 XML format using the Oracle OOo-dev 3.4.0 that I have installed.

On round trip of the Excel XML format, by the time it gets back into Calc, the date formatting
is changed (1899-12-30 becomes 30-12-99 on my configuration) and the serial days formulas
are some mangled versions of the OpenFormula formulas that were in the original .odp.  These
are all Calc defects somewhere in the export to import path.

IMPORTANT: The roundtrip of this export DOES NOT demonstrate any 2000-02-29 or 2400-02-29
problem in OpenOffice or in Excel.  Also, attempting to import this file into LO 3.5.0 fails
with a General input/output error, perhaps because there is no JRE available.  It opens in
LO 3.3.2 with the same peculiar loss of formatting and column B formula mangling as in OOo-dev
3.4.

When I correct the formula in B3 in OpenOffice Calc and do a fill down to populate the rest
in OO.o Calc, the correct values are revealed.  

When I open the Excel XML version in Excel, there are a number of very strange things. I see
the same date formatting as in the Smoke Test .ODP, but that is my default date format on
the machine I ran Excel on, so it might be a coincidence. It appears that the export from
Calc adjusts the date cell values with serial day value below 61 by subtracting 1, so the
cell for 1889-12-30 comes into Excel with serial day number -1.  The cells that OO.o did not
accept leap day dates for came over as text (appropriately).  On re-entering 1900-02-29 manually
in cell A7, the serial day 60 shows up.  

In column B, the formulas were not accepted by Excel.  Instead, Excel simply presented the
values that were in the XML file as the last-calculated values and dropped the formulas. 
This is probably an OO.o export bug that is not converting OpenFormula to Excel correctly.
 It could be that the XML export had not been updated properly when OpenOffice.org converted
to OpenFormula (assuming that it once worked).  If I manually correct column be by making
cell B3 hold "=A3", fill down, and also change the column format to numbers with commas, you
can see how dates from 1900-03-01 onward are in agreement with OpenOffice but the earlier
dates don't work, and the correction by -1 for earlier dates actually creates an Excel error
for 1899-12-30. 

I made a screen capture of the cleaned-up Excel view also.




-----Original Message-----
From: Pedro Giffuni [mailto:pfg@apache.org] 
Sent: Saturday, March 03, 2012 17:25
To: ooo-dev@incubator.apache.org
Subject: Re: Nominate release blocker: 118999 - Leap year not correctly calculated

On 03/03/12 17:34, Dennis E. Hamilton wrote:
> Amen on understanding the scope of the bug!!
>
> As promised, I built a smoke-test document and ran it.  The bug does not appear at all
in any Windows version of OpenOffice.org that I tested.  In particular, it does not appear
in OpenOffice.org 3.3.0, in the Oracle OOo-dev 3.4.0 developer release, nor in the Apache
OpenOffice OOo-dev 3.4 Developer Snapshot r1293550.
>
> For more grounding, I confirmed that the bug also is missing from LibreOffice 3.3.2,
the one I use for production, but it does appear in LibreOffice 3.5.0.
>
> So, whatever the origin of the defect, it apparently does not exist in the Apache OpenOffice
lineage from OpenOffice.org.
>
> On the other hand, it would be good to keep the smoketest document around, just in case.
>
> The file and screen captures demonstrating the presence and absence of smoke are all
attacked to the AOO Bugzilla report #118999.
>

The code is only used for conversions and apparently recent
versions of LO use it more aggressively but I can't find huge
differences with what we do (not that I looked too hard).

In any case I did a conversion of Dennis' file to Excel XML and
while the result is ugly (the "Serial days" information is lost and
1899 is formatted "99"), the dates are still consistent.

I am doing more tests converting stuff but for now I changed
the status to "irreproducible". As originally planned I wont
commit my patch for 3.4 but it still looks like a latent bug
waiting to strike so I will test Dennis' file with my patch for
inclusion after the release.

Thank you, Dennis!

cheers,

Pedro.


Mime
View raw message