Link to home
Create AccountLog in
Microsoft Excel

Microsoft Excel

--

Questions

--

Followers

Top Experts

Avatar of enrique_aeo
enrique_aeo

export to excel
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

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.


Avatar of ajisasaggiajisasaggi🇮🇳

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())

Avatar of enrique_aeoenrique_aeo

ASKER

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 '\'  

I attached the file...
erreXPORTACION.bmp

Reward 1Reward 2Reward 3Reward 4Reward 5Reward 6

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.


Avatar of jake072jake072🇨🇦

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

sorry man, i have the same error, i attached the file
errSintaxis.JPG

Avatar of kumar754kumar754🇺🇸

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>

Free T-shirt

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.


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

Avatar of kumar754kumar754🇺🇸

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...

hi, it is a varchar (50) view attched
exportTOexcel.JPG

Reward 1Reward 2Reward 3Reward 4Reward 5Reward 6

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.


Avatar of cubaman_24cubaman_24🇪🇸

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.

please give me an example

ASKER CERTIFIED SOLUTION
Avatar of cubaman_24cubaman_24🇪🇸

Link to home
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Create Account

Avatar of cubaman_24cubaman_24🇪🇸

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. ;)

Free T-shirt

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Microsoft Excel

Microsoft Excel

--

Questions

--

Followers

Top Experts

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.