Solved

export to excel

Posted on 2010-08-17
17
1,215 Views
Last Modified: 2013-12-17
I have this code for export to excel
    private static void FileDownloadPrompt(string FileName, SqlDataReader reader, string ContentType, string FileExtension)
    {
        //set the conttent type of the file to be downloaded
        System.Web.HttpContext.Current.Response.ContentType = ContentType;

        //add the response headers
        System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=\"" + FileName + FileExtension + "\"");

        //add the contents of the result set to the response stream
        System.Web.HttpContext.Current.Response.Write(ToHTMLTable(reader));

        //end the http response
        System.Web.HttpContext.Current.Response.End();
    }

    private static string ToHTMLTable(SqlDataReader iDr)
    {
        System.Text.StringBuilder sb = new System.Text.StringBuilder("");

        //check to see if there the reader has columns
        if (iDr.FieldCount != 0)
        {
            sb.Append("<table border=1><tr>");

            //loop thru each column
            for (int i = 0; i < iDr.FieldCount; i++)
            {
                sb.Append("<td>" + Convert.ToString(iDr.GetName(i)) + "</td>");
            }

            sb.Append("</tr><tr>");

            //loop until the record reaches eol(end of line)
            while (iDr.Read())
            {
                //loop thru each column, this is necessesary so that we could map
                //the columns to their proper place
                for (int i = 0; i < iDr.FieldCount; i++)
                {
                    sb.Append("<td>" + Convert.ToString(iDr.GetValue(i)) + "</td>");
                }

                sb.Append("</tr><tr>");
            }

            sb.Remove(sb.ToString().LastIndexOf("<tr>"), 4).Append("</table>");
        }
        //return our stringbuilder
        return sb.ToString();
    }

After export to excel is showing characters are exranos
1. NumeroDocumento column must be of type text
2. NumeroSentencia column is showing strange characters, the text to be displayed is "N° de la sentencia"
3. DecisionSuperior column is showing strange characters, the text to be displayed is "Decisión Superior"
I attached this file
errorExcel.JPG
0
Comment
Question by:enrique_aeo
  • 6
  • 3
  • 2
  • +2
17 Comments
 
LVL 2

Expert Comment

by:ajisasaggi
ID: 33455418
Please try these.

1. Assign this style to your data cells: <td style="mso-number-format:\@">
2, 3. Set the character encoding to Unicode. Add the following lines after adding the content-disposition header.
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Unicode
System.Web.HttpContext.Current.Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble())
0
 

Author Comment

by:enrique_aeo
ID: 33459254
the point 1 and 2 it i OK, but
I try this
sb.Append("<td style="mso-number-format:\@">" + Convert.ToString(iDr.GetName(i)) + "</td>");
but i have this error:
Error      4      Unexpected character '\'  
0
 

Author Comment

by:enrique_aeo
ID: 33507262
I attached the file...
erreXPORTACION.bmp
0
 
LVL 14

Expert Comment

by:jake072
ID: 33531250
enrique_aeo,

If you need a \ in a C# string, then you need to use \\

\ is a special character in C#...  \n is newline, for example, so if you want a literal \ in your string, you must double it.

Hope that helps,

Jake
0
 

Author Comment

by:enrique_aeo
ID: 33533464
sorry man, i have the same error, i attached the file
errSintaxis.JPG
0
 
LVL 12

Expert Comment

by:kumar754
ID: 33534266
you can use styles from the one listed below. In you case everything you wanted to display as Text, so use the "text" or "textnowrap" styles

you can use them in your code like this:

sb.Append("<td style=\"mso-number-format:General; text-align:general; white-space:nowrap; mso-spacerun:yes\">" + Convert.ToString(iDr.GetName(i)) + "</td>");

Here are all the different styles you can use while exporting data to excel:

.currency {mso-number-format:Currency}
.currencybold { mso-number-format:Currency;font-weight:700}

.number0dec {mso-number-format:"0"}
.number2dec {mso-number-format: Fixed;}
.number4dec {mso-number-format:"0\.0000"}
.number6dec {mso-number-format:"0\.000000"}
.number4decbold {mso-number-format:"0\.0000"; font-weight:700}
.number2decbold {mso-number-format: Fixed; font-weight:700;}

.text {mso-number-format:General;text-align:general;white-space:normal;mso-spacerun: yes }

.textnowrap {mso-number-format:General; text-align:general;white-space:nowrap; mso-spacerun: yes }

.num2text {mso-style-parent:text; mso-number-format:"\@";white-space:normal}

.shortdate{mso-number-format:"Short Date"; white-space:normal;}

.MMYY {mso-number-format:"MM/YY"}

.MMDDYY {mso-number-format:"m/d/yy"}

.monthyear {mso-number-format:"\[ENG\]\[$-409\]mmm\\ yyyy\;\@";}

.rotateup {mso-rotate:90}

.excel_bottom_border{border-bottom-style: solid; border-bottom-width:.5pt}

.excel_top_border{border-top-style: solid; border-top-width: .5pt}

// to use formula in Excel
<td>=sum(a1:a4)</td>
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:enrique_aeo
ID: 33536622
i use this code
                sb.Append("<td style=\"mso-number-format:General; text-align:general; white-space:nowrap; mso-spacerun:yes\">" + Convert.ToString(iDr.GetName(i)) + "</td>");
buy i have te same error (view yellow column)
sameERROR.JPG
0
 
LVL 12

Expert Comment

by:kumar754
ID: 33537839
What's the db field type for this column?
U might have to convert it in long integer and the convert to string for excel export.
Let me know...
0
 

Author Comment

by:enrique_aeo
ID: 33537881
hi, it is a varchar (50) view attched
exportTOexcel.JPG
0
 
LVL 8

Expert Comment

by:cubaman_24
ID: 33585411
An excell doc is nothing more than a zip file containing xml documents. try creating an xslt to transform your data, zip it according to standard and you're done.
0
 

Author Comment

by:enrique_aeo
ID: 33585638
please give me an example
0
 
LVL 8

Accepted Solution

by:
cubaman_24 earned 500 total points
ID: 33585992
Enrique:
Open excell 2007. Create your template.
Save it as Office 2007 format (xlsx).
Change file extension of excell file to zip.
Open it using winzip or other compression application.
Youll find several folders.Open \xl\worksheets. There will be an xml file named like every worksheet in your workbook (default: sheet1.xml, sheet2.xml, sheet3.xml)
Use an xsl transform to make your data in the same format as those files.
Substitute existing files with the ones you created in your transform.
Rezip.
Change extension of zip file back to  xlsx

You can do it all programatically. If you need a good zip library, search for Ionic.zip in google.

Best regards.

public static string TransformXml(string xmlPostedFile,
                                          string xslPath,
                                          bool enableDocumentFunction,
                                          bool enableScript){
            if (string.IsNullOrEmpty(xmlPostedFile)){
                return string.Empty;
            }
            StringWriterWithEncoding stringWriter = null;
            StringReader stringReader = null;
            try{
                XslCompiledTransform xslt = new XslCompiledTransform();
                XmlUrlResolver resolver = new XmlUrlResolver();
                resolver.Credentials = CredentialCache.DefaultCredentials;
                if (xslPath.StartsWith("~")){
                    xslt.Load(HttpContext.Current.Server.MapPath(xslPath),
                              new XsltSettings(enableDocumentFunction, enableScript), resolver);
                }
                else{
                    xslt.Load(xslPath, new XsltSettings(enableDocumentFunction, enableScript), resolver);
                }
                StringBuilder sb = new StringBuilder();

                stringWriter = new StringWriterWithEncoding(sb, Encoding.UTF8);

                stringReader = new StringReader(xmlPostedFile);
                XmlReader tmp = XmlReader.Create(stringReader);
                xslt.Transform(tmp, null, stringWriter);
                return stringWriter.GetStringBuilder().ToString();
            }
            finally{
                if (stringWriter != null){
                    stringWriter.Dispose();
                }
                if (stringReader != null){
                    stringReader.Dispose();
                }
            }
        }

Open in new window

0
 
LVL 8

Expert Comment

by:cubaman_24
ID: 33586051
You can achive similar results using excell 2003 xml format, wich is a single xml file instead that xlsx format, just play around and read some xslt reference book. ;)
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now