Solved

export to excel

Posted on 2010-08-17
17
1,234 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!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

840 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