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

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;
    }
}

I have this same problem at work. Haven't found a good solution yet — Joe Phillips
– Joe Phillips, Commented Jan 8, 2009 at 16:39

Fabrizio C. · Accepted Answer · 2009-01-08 16:31:25Z

2

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

?

edited Jan 8, 2009 at 16:31

answered Jan 8, 2009 at 14:47

Fabrizio C.

1,56410 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Xaisoft Over a year ago

I added the method that creates the stylesheet above.

Fabrizio C. Over a year ago

I see... You are producing CSV (or similar): so my hint does not apply. Sorry.

Xaisoft Over a year ago

Can I do something to the WriteAttributeString which would tell it to export it as text?

Xaisoft Over a year ago

The link didn't help me out to much because I am exporting a columns in a datatable to excel. Instead, I am trying to find out if there is a way I can change the stylesheet?

Xaisoft Over a year ago

Great, putting "=\"" worked. Why does this work? It probably would be simpler just to work directly with the dataset, but I didn't write the code, so it's kind of hard for me to answer.

|

Dan Williams · Accepted Answer · 2009-01-08 14:34:16Z

1

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

answered Jan 8, 2009 at 14:34

Dan Williams

4,95011 gold badges39 silver badges46 bronze badges

1 Comment

Xaisoft Over a year ago

If I add a ' quote to the front of the string, it shows up in the file.

Joe Casadonte · Accepted Answer · 2009-01-08 15:02:44Z

0

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?

edited Jan 8, 2009 at 15:02

answered Jan 8, 2009 at 14:34

Joe Casadonte

17.1k11 gold badges50 silver badges61 bronze badges

2 Comments

Xaisoft Over a year ago

So, there is nothing you can set in the Response.Header.

Fabrizio C. Over a year ago

Looking at the edited question it looks like you are actually emitting CSV (or other non comma separated text).

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related