XML Spreadsheet into Excel using MVC, Cell format always General, how to change?

Question

The legacy system uses Excel 2003 so I cant use any of the newer third party products as they are for 2007 and above and have been left with a legacy XML Spreadsheet creation system.

I now need to modify the cell formating in Excel so that a number is a number, a date is a date, etc... As everything is currently formatting to General in Excel.

I have scanned the web and tried the following coding in the partial view to just have the styling and the workbook section:

<ss:Style ss:ID="MyTime">
<NumberFormat ss:Format="Long Time" />
</ss:Style>
<ss:Style ss:ID="MyNumber">
<NumberFormat ss:Format="General Number" />
</ss:Style>  

This is represented in the XML outfile which is

<Cell ss:StyleID='MyNumber'><Data ss:Type='Number'>419,717,200</Data></Cell>

This is created in the main view and built up line by line using:

private static IHtmlString ToXmlColumn(string columnContent, string ssType, string ssFormat)
{
 return MvcHtmlString.Create(string.Format("<Cell ss:StyleID='{2}'><Data ss:Type='{1}'>{0}</Data></Cell>", SecurityElement.Escape(columnContent), ssType, ssFormat));
}

Download uses the base of application/vnd.ms-excel

public ExcelActionResult(string fileName, TModel model, ControllerContext context, string viewName = null)
        : base("application/vnd.ms-excel")
    {
        _context = context;
        _model = model;
        _viewName = viewName ?? (string)_context.RouteData.Values["action"];

        FileDownloadName = fileName;



    }

However when I download the xls and reopen it in Excel 2003 all the cell formats are still General.

What have I missed or what am I doing wrong?

This code is everywhere in the system so Im looking for a fix or a workaround, rather than needing to rewrite it or using the XML output to create XSD and map across as the deadline is too tight for me to do that.

Because I can officially answer my own question at the bottom, here is the answer:

In case anyone else needs to find something similar I will answer it myself.

look here for the custom codes to use:

http://office.microsoft.com/en-gb/excel-help/number-format-codes-HP005198679.aspx

But I would look at replacing this:

<ss:Style ss:ID="MyTime">
<NumberFormat ss:Format="Long Time" />
</ss:Style>

<ss:Style ss:ID="MyNumber">
<NumberFormat ss:Format="General Number" />
</ss:Style>  

with the below if wanting a number

<ss:Style ss:ID="MyTime">
<NumberFormat ss:Format="HH:MM:SS" />
</ss:Style>

<ss:Style ss:ID="MyNumber">
<NumberFormat ss:Format="0" />
</ss:Style>  

or below if wanting 12000 to display as 12,000.00 in custom rather than general

<ss:Style ss:ID="MyNumber">
<NumberFormat ss:Format="#,##0.00" />
</ss:Style>  

I have spent all day hunting on the internet to find the above so I will answer this on any XML Spreadsheet query I find as it was annoying to find and difficult too.

Answer 1

Don't have Excel 2003, but Excel 2013 can save it as "XML Spreadsheet 2003(*.xml)" It comes out as below XML file.

This style will display as no decimal, no thousand ",".

<Style ss:ID="s64">
  <NumberFormat ss:Format="0"/>
</Style

Style ID should link to Column tag like <Column ss:StyleID="s64" ss:Width="61.5"/>

This style will display as 2 decimal places with "," as 1000 separator.

<Style ss:ID="s66">
  <NumberFormat ss:Format="Standard"/>
</Style>

And this one should be long date time format:

<NumberFormat ss:Format="m/d/yy\ h:mm;@"/>

Hope this is the right direction for you. Good luck!

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<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="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>YourName</Author>
  <LastAuthor>YourName</LastAuthor>
  <Created>2015-06-25T18:25:51Z</Created>
  <Version>15.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>9195</WindowHeight>
  <WindowWidth>17280</WindowWidth>
  <WindowTopX>0</WindowTopX>
  <WindowTopY>0</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s63">
   <NumberFormat ss:Format="[h]:mm:ss;@"/>
  </Style>
  <Style ss:ID="s64">
   <NumberFormat ss:Format="0"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:StyleID="s63" ss:AutoFitWidth="0"/>
   <Column ss:StyleID="s64" ss:Width="60"/>
   <Row>
    <Cell><Data ss:Type="String">My Time</Data></Cell>
    <Cell><Data ss:Type="String">My Number</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="DateTime">1899-12-31T12:12:34.000</Data></Cell>
    <Cell><Data ss:Type="Number">123213</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveCol>1</ActiveCol>
     <RangeSelection>C2</RangeSelection>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>