Solved

export to excel

Posted on 2010-08-17
17
1,209 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 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

20 Experts available now in Live!

Get 1:1 Help Now