Menu
I generate an XML file through C#, and the relevent part looks like this
<ss:Cell>
<ss:Data ss:Type="DateTime">2009-01-18T00:00:00.000</ss:Data>
</ss:Cell>
However it displays in excel as a number , like 41249 that bears no resemblence to the original date. If I right click the cell, and change the format to dd/MM/yyyy or whatever, then it displays the correct date. Anyone know how I can set the format in the XML for how the date is displayed? The data is there, it's just being displayed incorrectly.
802
The dateTime is specified in the following form "YYYY-MM-DDThh:mm:ss" where: YYYY indicates the year. MM indicates the month. DD indicates the day.
On the Home tab, in the Number group, click the Dialog Box Launcher next to Number. You can also press CTRL+1 to open the Format Cells dialog box. In the Category list, click Date or Time. In the Type list, click the date or time format that you want to use.
Go to the Excel file and click on the developer tab, then go to source. This will add a panel to the right, which will allow you to add your XSD by clicking on “XML Maps” at the bottom right. The XML Maps window will open. Click on “Add” and add your XSD.
In the <Styles> section add
<Style ss:ID="s22">
<NumberFormat ss:Format="yyyy\-mm\-dd"/>
</Style>
then mark your cell with the style:
<Cell ss:StyleID="s22"><Data ss:Type="DateTime">2009-01-21T00:00:00.000</Data></Cell>
Alternatively, open your XML file in Excel, make the change you want, re-save it as XML, then look to see what changed.
143
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
