Creating XML for import into Excel, particularly dates

Question

Greetings.

I have a simple application that generates some performance-logging data, and I need the output to be accessible to Excel.

I create an XML document with the fields etc in it, and can open this in Excel.

The problem is, how do I coerce Excel to treat dates as dates? I've tried saving the date value as various formats, but Excel always treats it as text. If I click in the cell and hit Enter, it happily displays it as a date. Same when I do the "Text to Columns" thing.

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<entries>
  <entry>
    <StartDate>14/07/2009 01:02:35</StartDate>
    <Total>1084</Total>
    <Connecting>788</Connecting>
    <Disconnecting>0</Disconnecting>
    <Queries>98</Queries>
  </entry>
  <entry>
    <StartDate>14/07/2009 01:10:00</StartDate>
    <Total>1054</Total>
    <Connecting>228</Connecting>
    <Disconnecting>1</Disconnecting>
    <Queries>104</Queries>
  </entry>
</entries>

Answer 1

The trick is to have Excel infer the dates for you. In order for a date to be treated as such, format your date value as UTC (iso8601) and remove the "Z" and everything after that (excel doesn't support those). For example, turn this:

1999-05-31T13:20:00Z-05:00 

to this:

1999-05-31T13:20:00

For more information on how Excel infers XML types check out MSDN

Answer 2

You can specify the worksheet format completely. Feels a bit ugly, the producer needing to understand so much of the destination.

<Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="12" ss:ExpandedRowCount="15" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="13.2">
   <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="47.4" ss:Span="11"/>
   <Row ss:Index="4">
    <Cell ss:StyleID="s22"><Data ss:Type="DateTime">2009-04-20T00:00:00.000</Data></Cell>