Wrong datetime conversion loading 2004 xml excel spreadsheet

Aspose.Cells Product Family
1

Hello,
I have a problem handle datetime values in aspose cells.
One of our application create export data in xml format.

That is how we define header and footer:

Dim l_sHeader : l_sHeader = “<?xml version=""1.0""?>” & vbLf _
& “<?mso-application progid=""Excel.Sheet""?>” & vbLf _
& “<Workbook xmlns=”“urn:schemas-microsoft-com:office:spreadsheet”" “ _
& “xmlns:o=”“urn:schemas-microsoft-com:office:office”” “ _
& “xmlns:x=”“urn:schemas-microsoft-com:office:excel”” “ _
& “xmlns:ss=”“urn:schemas-microsoft-com:office:spreadsheet”” “ _
& “xmlns:html=”“HTML 4.01 Specification"”>” & vbLf _
& " <DocumentProperties xmlns=““urn:schemas-microsoft-com:office:office””>“ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ” <ExcelWorkbook xmlns=““urn:schemas-microsoft-com:office:excel””>“ & vbLf _
& ” False“ & vbLf _
& ” False“ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ” <Style ss:ID=““Default”” ss:Name=““Normal””>“ & vbLf _
& ” <Alignment ss:Vertical=““Bottom””/>“ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ” <Style ss:ID=““Text””>“ & vbLf _
& ” <Alignment ss:Vertical=““Bottom”” ss:WrapText=““1"”/>” & vbLf _
& " “ & vbLf _
& ” <Style ss:ID=““Integer””>“ & vbLf _
& ” <NumberFormat ss:Format=““0"”/>” & vbLf _
& " “ & vbLf _
& ” <Style ss:ID=““Datetime””>“ & vbLf _
& ” <NumberFormat ss:Format=““d/m/yy\ h:mm;@””/>“ & vbLf _
& ” “ & vbLf _
& ” <Style ss:ID=““Date””>“ & vbLf _
& ” <NumberFormat ss:Format=““d/m/yy;@””/>“ & vbLf _
& ” “ & vbLf _
& ” <Style ss:ID=““Time””>“ & vbLf _
& ” <NumberFormat ss:Format=“”[$-F400]h:mm:ss\ AM/PM"“/>” & vbLf _
& " “ & vbLf _
& ” <Style ss:ID=““Float””>“ & vbLf _
& ” <NumberFormat ss:Format=““Fixed””/>“ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ” <Worksheet ss:Name=““Tabelle1"”>” & vbLf _
& " “ & vbLf
Dim l_sFooter : l_sFooter = ”
“ & vbLf _
& ” <WorksheetOptions xmlns=““urn:schemas-microsoft-com:office:excel””>“ & vbLf _
& ” “ & vbLf _
& ” “ & vbLf _
& ”" & vbLf


Then we write rows like this one:
<Row>
<Cell><Data ss:Type=“String”>Termin Test</Data></Cell>
<Cell><Data ss:Type=“String”>Standard</Data></Cell>
<Cell><Data ss:Type=“String”>Archer, Jonathan</Data></Cell>
<Cell ss:StyleID=“Datetime”><Data ss:Type=“DateTime”>2018-07-01T00:00:00.000</Data></Cell>
<Cell ss:StyleID=“Datetime”><Data ss:Type=“DateTime”>2018-07-02T00:00:00.000</Data></Cell>
<Cell><Data ss:Type=“String”>Archer, Jonathan</Data></Cell>
<Cell ss:StyleID=“Date”><Data ss:Type=“DateTime”>2016-11-16T00:00:00.000</Data></Cell>
</Row>


As you can see the date format is correct.
If we save the xml data to file and load it with original excel the date format in the example above will shown correct as July 1st and July 2nd.

Using Aspose.Cells component to load the file the example dates will mixed by month and day creating dates from January 7 to February 7.

I use a component based on Aspose.Cells. Inside the component Load/Save is just like this:

_workbook = new Workbook(source);

…

_workbook.Save(_path, SaveFormat.Xlsx);


Nothing special.
So it looks like the Aspose.Cells is not working with the correct format somehow.

Please help!






2

Hi,


Thanks for providing us some details.

Could you provide us SpreadsheetML file (.xml file (Excel 2003 XML)), so we could evaluate your issue properly via Aspose.Cells APIs.

Thank you.
3

Attached a sample file.

4

Hi,


Thanks for the template file.

Please try our latest version/fix: Aspose.Cells for .NET v17.3.1

Aspose.Cells for .NET v17.03.1 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v17.03.1 (.NET 4.0) compiled in .NET Framework 4.0.
(Note: please choose any of the fixes for your underlying .NET framework version)

I have tested your scenario/ case a bit using the following sample code with your template file, it works fine and as expected (I extend the “Beginn” and “Ende” columns to check their values). I have also attached the output file for your reference:
e.g
Sample code:

Workbook wb = new Workbook(“e:\test2\export_21_3_2017_13_38_58.xls”);
wb.Save(“e:\test2\out1.xlsx”, SaveFormat.Xlsx);

Let us know if you find any issue.

Thank you.
5

Ok, the latest version fixed the bug as it was not correct in 16.12.0.
Have some trouble deploy new version now but nevertheless it works.
Thank you.

6

Hi,


Good to know that your issue is sorted out by the new version/fix. Feel free to write us back if you have further query or you still have any issue, we will be happy to assist you soon.

Thank you.