How to stop the leading 0's from being strippe

Question

I am running into the same problem as in this question:

How do you prevent leading zeros from being stripped when importing an excel doc using c#

But I am not sure if that is the best solution for my scenario. Here is the code I am using to do the export. Does anyone know what I can change to prevent the leading 0's from being stripped off?

private static void Export_with_XSLT_Web(DataSet dsExport, 
                                         string[] sHeaders, 
                                         string[] sFileds, 
                                         ExportFormat FormatType, 
                                         string FileName)
{

        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Buffer = true;

        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.AppendHeader("content-disposition", 
                                                  "attachment; 
                                                  filename=" + FileName);
        }

        // XSLT to use for transforming this dataset.                       
        MemoryStream stream = new MemoryStream();
        XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);

        CreateStylesheet(writer, sHeaders, sFileds, FormatType);
        writer.Flush();
        stream.Seek(0, SeekOrigin.Begin);

        XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
        XslTransform xslTran = new XslTransform();
        xslTran.Load(new XmlTextReader(stream), null, null);

        using(StringWriter sw = new StringWriter())
        {
            xslTran.Transform(xmlDoc, null, sw, null);

            HttpContext.Current.Response.Write(sw.ToString());                
            writer.Close();
            stream.Close();
            HttpContext.Current.Response.End();
        }
    }
}

Here is the method that creates the stylesheet, is there anything in here that I can change to bring in some or all fields as text.

private static void CreateStylesheet(XmlTextWriter writer, 
                                     string[] sHeaders, 
                                     string[] sFileds, 
                                     ExportFormat FormatType)
{
    try
    {
        // xsl:stylesheet
        string ns = "http://www.w3.org/1999/XSL/Transform";
        writer.Formatting = Formatting.Indented;
        writer.WriteStartDocument();
        writer.WriteStartElement("xsl", "stylesheet", ns);
        writer.WriteAttributeString("version", "1.0");
        writer.WriteStartElement("xsl:output");
        writer.WriteAttributeString("method", "text");
        writer.WriteAttributeString("version", "4.0");
        writer.WriteEndElement();

        // xsl-template
        writer.WriteStartElement("xsl:template");
        writer.WriteAttributeString("match", "/");

        // xsl:value-of for headers
        for(int i = 0; i < sHeaders.Length; i++)
        {
            writer.WriteString("\"");
            writer.WriteStartElement("xsl:value-of");
            writer.WriteAttributeString("select", "'" + sHeaders[i] + "'");
            writer.WriteEndElement(); // xsl:value-of
            writer.WriteString("\"");
        }

        // xsl:for-each
        writer.WriteStartElement("xsl:for-each");
        writer.WriteAttributeString("select", "Export/Values");
        writer.WriteString("\r\n");

        // xsl:value-of for data fields
        for(int i = 0; i < sFileds.Length; i++)
        {
            writer.WriteString("\"");
            writer.WriteStartElement("xsl:value-of");
            writer.WriteAttributeString("select", sFileds[i]);
            writer.WriteEndElement(); // xsl:value-of
            writer.WriteString("\"");
        }

        writer.WriteEndElement(); // xsl:for-each
        writer.WriteEndElement(); // xsl-template
        writer.WriteEndElement(); // xsl:stylesheet
        writer.WriteEndDocument();
    }
    catch(Exception Ex)
    {
        throw Ex;
    }
}

Answer 1

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

?

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

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