Issue 80715 - Exported time breaks Excel XML format
Summary: Exported time breaks Excel XML format
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: save-export (show other issues)
Version: OOo 2.2.1
Hardware: PC Windows XP
: P4 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: needhelp
Depends on:
Blocks:
 
Reported: 2007-08-15 21:25 UTC by joghurt
Modified: 2013-08-07 15:12 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description joghurt 2007-08-15 21:25:59 UTC 
Steps to reproduce:
1. Enter a time value that is greater than one day, for example 34:56:12.
Alternatively, enter a value greater than 1.0 (for example, 1.2345).
2. To be sure, format the cell to "[HH]:MM:SS".
3. Export the spreadsheet as Excel 2003 XML.
4. Try to load the file into Excel (I've tried Excel 2007).
5. Excel gives an XML error message and rejects the entire file.

If one checks the XML file, the following string is exported:
<Cell><Data ss:Type="DateTime">1899-12-31T34:56:12.000</Data></Cell>

The time part shouldn't exceed 23:59:59.999. The correct data would be:
1900-01-01T10:56:12.000 (this is exactly what Excel exports).
Comment 1 frank 2007-08-16 14:03:03 UTC 
Hi Swante,

seems that Excel have a problem with 'negative' dates, Dates before 01.01.1900.
So the XSLT may change this tag accordingly. Problem here is this change may
have influences on date calculations. Please ask DR for details.

Maybe this is a candidate for a wontfix, it's up to you.

Frank
Comment 2 joghurt 2007-08-17 09:01:58 UTC 
Hey, wait a minute! "1899-12-31" is NOT, repeat: NOT a "negative" date. Check an
XML file exported by a real Excel. Or simply read MSDN on Windows date types,
such as DATE or COleDateTime. I was going to post another error which is based
on the same bug in the Excel XML import/export: if I export 0:00:00 from Excel
(that is, 1899-12-31T0:00:00.000), this cell is displayed as 24:00:00 in OOo Calc.

Anyway, having a date-time with an hour value greater than 24 is surely a bug.
Furthermore, generating an XML that does not conform to the referenced schema is
also surely a bug.

And as we're speaking about Excel's own format, I think one should take Excel as
the reference in this matter. So it's NOT Excel that's having a problem.
Comment 3 svante.schubert 2007-08-17 18:58:28 UTC 
Thanks for the issue joghurt.
Although it is uncertain when I will ever be able to work on this filter again.
I really hope than someone of the community is able to provide some patches for
this.

Marked with keyword 'needhelp'!