Issue 127034 - xlsx file: imported DateTime cells are empty (Excel 2010 compatible)
Summary: xlsx file: imported DateTime cells are empty (Excel 2010 compatible)
Status: UNCONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: 4.1.2
Hardware: All All
: P5 (lowest) Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-07-09 13:01 UTC by Falcon
Modified: 2017-05-20 11:55 UTC (History)
1 user (show)

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


Attachments
xlsx with 2 datetime notation: 1 works fine and another is empty in Calc (4.43 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-07-09 13:01 UTC, Falcon
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description Falcon 2016-07-09 13:01:55 UTC
Created attachment 85603 [details]
xlsx with 2 datetime notation: 1 works fine and another is empty in Calc

Calc do not read DateTime in cells when date in ISO 8601 format (like "1961-04-12T07:55:00.123")!

By documentation (Ecma Office Open XML paragraph 18.18.11) ST_CellType cell type for DateTime are:
1. "n" and value for date is number (like "22383.32986")
example:
   <c r="C3" s="5" t="n">
    <v>22383.32986</v>
   </c>

2. "d" and value for date is datetime (like "1961-04-12T07:55:00.123")
example:
   <c r="C4" s="5" t="d">
    <v>1961-04-12T07:55:00.123</v>
   </c>

Calc ignores the second case.

In attachment xlsx in cells B3 and C3 datetime as number - and Calc ok, in cells B4 and D4 datetime in ISO 8601 format - and Calc ignores dates.

In Excel 2010 all cells shown fine.

Tested in OO Calc 4.1.2 in Windows 7.
Also it's reproducible for LibreOffice Calc 4.3.3.2 in Debian 8 and 5.1.3.2 in Windows 7: https://bugs.documentfoundation.org/show_bug.cgi?id=100822

(Document was created using ZEXMLSS 0.0.11)
Comment 1 orcmid 2016-07-09 16:37:51 UTC
(In reply to Falcon from comment #0)
> Created attachment 85603 [details]
> xlsx with 2 datetime notation: 1 works fine and another is empty in Calc
> 
> Calc do not read DateTime in cells when date in ISO 8601 format (like
> "1961-04-12T07:55:00.123")!
> 
> By documentation (Ecma Office Open XML paragraph 18.18.11) ST_CellType cell
> type for DateTime are:
> 1. "n" and value for date is number (like "22383.32986")
> example:
>    <c r="C3" s="5" t="n">
>     <v>22383.32986</v>
>    </c>
> 
> 2. "d" and value for date is datetime (like "1961-04-12T07:55:00.123")
> example:
>    <c r="C4" s="5" t="d">
>     <v>1961-04-12T07:55:00.123</v>
>    </c>
> 
> Calc ignores the second case.
> 

That is plausible.  Which ECMA Office Open XML version are you checking?

The AOO .xlsx import filter is probably older than the time when the clarification of date handling was improved in the ECMA/ISO specification and in the Microsoft Excel 2010 implementation.  This would have been part of satisfying various parties about "strict" handling of ISO dates while also having the more-interoperable "n" case (the default in Excel, I believe).  It is likely that the AOO filter is more "OOOXML transitional."

The actual approach in Apache OpenOffice and in ODF 1.2 OpenFormula is a counterpart of the Excel "n" case.  

We need to check whether the equivalent of the "d" case is producible in AOO Calc.  Then having the .xslx import filter work appropriately is an additional challenge (along with .ods interop cases).

More analysis is needed.  Thanks for digging into this, Falcon.
Comment 2 Falcon 2016-07-09 19:01:59 UTC
It's in "ECMA-376, 4th Edition Office Open XML File Formats — Fundamentals and Markup Language Reference" (2012) on page 2432.

http://www.ecma-international.org/publications/files/ECMA-ST/ECMA-376,%20Fourth%20Edition,%20Part%201%20-%20Fundamentals%20And%20Markup%20Language%20Reference.zip

Hm, there are 5-th edition here: http://www.ecma-international.org/publications/standards/Ecma-376.htm