Solved

export to excel

Posted on 2010-08-17
17
1,225 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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 Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

776 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