Solved

export to excel

Posted on 2010-08-17
17
1,245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

737 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