Setting date cell format in excel xml

Question

I need to export a DataSet to excel file. I have date columns in the DataSet. I need to make sure that the date format of the cells has locale: English (United Kingdom) with the date format of dd/mm/yyyy. Currently I am setting the format dd/mm/yyyy using

<Style ss:ID="DateLiteral">
 <NumberFormat ss:Format="dd/mm/yyyy;@"/>
</Style> 

in the Workbook element. How can I set the desired locale? Also it will be nice if I know how can I set the desired locale using the current culture of user's machine using C#.net.

Thanks :)

Answer 1

It isn't necessary to force a custom format, you can use one of the default Excel date or time formats

• General Date
• Long Date
• Medium Date
• Short Date
• Long Time
• Medium Time
• Short Time

Dates and/or time with these formats are displayed according to the system date format of the user opening the spreadsheet.

For example a date of 31 December 2009 with a format of “Short date” would be displayed in the USA as 12/31/2009 and in the UK as 31/12/2009.

The spreadsheet XML would look like this:

<Style ss:ID="DateLiteral">
 <NumberFormat ss:Format="Short Date"/>
</Style>

<Row>
 <Cell ss:StyleID="DateLiteral">
  <Data ss:Type="DateTime">2009-12-31T00:00:00.000</Data>
 </Cell>
</Row>