How to set format=DATE in XML file to properly import in Calc?

pavion · November 7, 2018, 2:19pm

Hi, I have a program that generated XML file and this file should be opened with Calc. But this program for date cells sets as type=String, so all date data are imported as “String” and when sorting the date data in column they are sorted alphabetically instead of date. The are currently sorted 01.01.2018 (dd.mm.yyyy), then 02.02.2018 and then 03.01.2018. You notice first and last are from January and second one is February. They are sorted by string, not the actual date.

The cause of the problem is date cells are set as String.

<Cell><Data ss:Type="String">21.1.2009</Data></Cell>

I need something like Type=“Date” but it looks it does not exists. The closes think that I found on web is the following:
Excel XML, how can I get Excel to display my DateTime field in yyyy-MM-dd format? - Stack Overflow which suggest to add new Style and then add this style definition in Cells.

<Styles>
    <Style ss:ID="myDateStyle">
        <Font ss:Color="#FF0000"/>
        <NumberFormat ss:Format="yyyy-mm-dd"/>
    </Style>
 </Styles>

and then after reference on it:

<Cell ss:StyleID="myDateStyle"><Data ss:Type="DateTime">2009-01-21</Data></Cell>

but date is imported as number (I intentionally added red color in style to see if style is acceped):
image description

Note: First column appears to have a valid date, but it has single quote character in front of it, so it is string not date.

Bellow is whole document. Download it and save it as samle.xls and open it with text editor. I have added three empty lines when relevant part of code appears and three empty line after it, to easily recognize the relevant part of code.
The whole document:
sample.xls

I should somehow define date field is “Data” data type with ‘yyyy-mm-dd’ definition, but can’t figure it out how. Everything I test it always opens as number. How to define a cell to be displayed as Date data field.

P.S. If in Calc I manually right click and select “Format Cells” and select Category=Date and in “Format code” write: DD.MM.YYYY date field is correctly displayed as 21.01.2009.

erAck · November 7, 2018, 2:47pm

In OOXML a date is just a date serial number (number of days since the null-date, which usually is 1899-12-31, so 1 is 1900-01-01, 2 is 1900-01-02, … caveat, Excel thinks 1900-02-29 exists) formatted as date. Easiest is to just enter the date value 2018-11-23 into a cell and save it to .xlsx and see yourself, the number is 43427 in this example.

You’d have to train your program to properly write the required numeric value and cell style information.

Probably easier is to write ODF .ods that knows a distinct date cell type, which for this example would be

<table:table-cell office:value-type="date" office:date-value="2018-11-23" calcext:value-type="date">
  <text:p>2018-11-23</text:p>
</table:table-cell>

mikekaganski · November 7, 2018, 3:25pm

… and use Flat ODF Spreadsheet (.fods) format, which is the XML (unlike .ods, which is zip package with XMLs inside).

Lupp · November 7, 2018, 4:25pm

If DATEVALUE(“2018-11-23”) is 43427 then datevalue=0 should represent “1899-12-30” (as it actually is by default in Calc).

pavion · November 7, 2018, 7:07pm

Thanks advices. I know dates are saved as integers. I don’t want to use ODF format, because all sort of Excel versions are used in our company, but only a few advanced users use LibreOffice. There is probably only tiny setting to change existing XML format (I am not talking about OOXML). The best way of seeing this simplified format is by downloading my XML file above and opening in text editor. I would be super happy if some has any kind of tip how to find this “date” related info for my XML.

mikekaganski · November 7, 2018, 7:41pm

It is very easy to see the tiny date-related info: just open the XML in Excel, format as you like, and re-save in the same 2003 XML format. And then try to open it in LO and see that LO will ignore the date formatting.

pavion · November 7, 2018, 7:56pm

@Mike2, I don’t have Excel at the hand right now, to test this (I will test tomorrow). What you have written seems like a bug in LibreOffice then???

pavion · November 8, 2018, 10:06am

I have got access to Microsoft Excel 2016 and above code works fine. Excel correctly displays the date in dd.mm.yyyy, but LibreOffice displays number instead. It appears to me that LibreOffice Calc does not recognizes <NumberFormat ss:Format="yyyy-mm-dd"/> part of Excel XML file. Probably a bug or unsupported feature in LibreOffice.

erAck · November 9, 2018, 6:13pm

Indeed looks like it is Excel’s internal pre-dated non-standardized XML 2003 format (I confused that earlier), but if applying the NumberFormat doesn’t work in Calc then shrug it’s not implemented. That old XML format doesn’t have much priority.