How to stop the leading 0's from being stripped off when exporting to excel from a datatable?

Answer 1

add a ' (single quote) to the front of the string.

Answer 2

If you know a field is supposed to be a 5-digit zip code (or some part of a SSN or whatever), you're probably going to have to fix it manually via an sprintf() or whatever the c# equivalent is. If it's a field that's indeterminate, you're completely at the mercy of whatever is reading the data.

Sorry, I misread the question as reading from Excel to HTML.

Have you considered exporting as CSV instead of Excel?

Answer 3

I don't know the output of your XSL transformation: I will assume it's the xml format for Excel. Trying to reverse the process I wrote three numbers (007) in an Excel sheet: once as number, once as text and once as number but formatted to show 3 digits padded with zeros. Then I saved it as xml and looked at it. Here is the fragment:

<Row>
    <Cell><Data ss:Type="Number">7</Data></Cell>
</Row>
<Row>
    <Cell><Data ss:Type="String" x:Ticked="1">007</Data></Cell>
</Row>
<Row>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">7</Data></Cell>
</Row>

I'm not copying the style but you can easily do it.

Edit: as always Google Is Your Friend (and mine, too ;-) ): http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm#CSVAndExcel.

Edit (2): I thought the link was enough. The article is saying that (if you are sure the target is only Excel) you can use an Excel-specific CSV syntax. So in your case and looking at your code I think you should insert the missing commas and change the opening

writer.WriteString("\"");

into

writer.WriteString("=\"");

Beware that I didn't try.

Just one question out of curiosity: wouldn't it be simpler to just output what you need working on the DataSet instead of

• transforming it in XML
• generating an ad-hoc XSL
• performing the XSL transformation
• copying the result to the Response stream

?