I have a template Excel spreadsheet where I have formatted a column with a custom date style as "dd mmm yyyy" – i.e. dates entered in this column will be formatted to <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1ate Month="8" Day="4" Year="2005">04 Aug 2005</st1ate>.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Now then, I have a table of data in a typed DataSet which I am transforming on to my template spreadsheet using:
foreach (CustomerDS.CustomerRow customerRow in …)
customerRow.DOB.ToString("dd MMM yyyy"));
The above snippet iterates through every row in my DataTable seeking out the DOB field and transforming it on the date column on my template Excel spreadsheet.
Although the format of the column on the template Excel spreadsheet matches the ToString("dd MMM yyyy") cast, the spreadsheet fails to recognise the data in the column as a date type and so renders sorting the column useless.
Unless I click into the cells of the columns, only then does Excel begin to see that the data is in fact a date type and so consequently aligns the cell content to the right.
Is there a way to get Excel to recognise my date data as dates automatically ???