0

i do an export to excel in asp.net and one of the columns does hold quite a bit of data (4k) [directly below]. as you can see below it's tabbed and has multiple newlines. using string builder does keep the data together in one cell, however the formatting is gone completely when exported.
i want to be able to keep the formatting in the cell if possible and i can't use Microsoft.Office.Interop library.
does anyone have any suggestions? it seems like excel itself is killing the formatting. i've added the code i'm using. thanks rik

W1995-113 W1995 Outer Head Gyro
W0137-587 W0137 Outer Tube Stabiliser (8"15/32)Gyro
W0137-237 W0137 Outer Tube Stabiliser (8"15/32)
W0137-537 W0137 Outer Tube Stabiliser (8"15/32)
W0137-631 W0137 Outer Tube Stabiliser (8"15/32)
W0137-298 W0137 Outer Tube Stabiliser (8"15/32)
W0137-288 W0137 Outer Tube Stabiliser (8"15/32)

 protected void ExportAsSB(DataTable myDt)
        {
            StringBuilder sb = new StringBuilder();
            int i = 0;
            sb.Append("<table border=1><tr>");
            foreach (DataColumn dc in myDt.Columns)
            {
                sb.Append("<td>" + dc.ColumnName + "</td>");
            }
            sb.Append("</tr>");
            foreach (DataRow dr in myDt.Rows)
            {
                sb.Append("<tr>");
                for (i = 0; i < myDt.Columns.Count; i++)
                {
                    if (dr[i].ToString() == "")
                    {
                        sb.Append("<td></td>");
                    }
                    else
                    {
                        sb.Append("<td>" + dr[i].ToString() + "</td>");
                    }
                }
                sb.Append("</tr>");
            }
            sb.Append("</table></body></html>");
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.Charset = "";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.AddHeader("Content-Disposition", "attachment;filename=Workorder.xls");
            Response.Write(sb.ToString());
            Response.End();
        }
1
Contributor
2
Replies
11
Views
1 Year
Discussion Span
Last Post by rikb53
0

i think i've found something that will work - or for me it seems to work. but only seems to have an effect if i break the string builder assembly to be specifc to the one export. in other words all columns are accounted for and named:
since it's building html - i looked for the CrLf as "Environment.NewLine" replaced it w/ the html equivalent. i also did a replace on any
tabs (\t) and used "&emsp;" ............ but i read somewhere that encapsulating it in "<pre></pre>" will preserve any custom formatting as well.
so far this is working perfectly, of course that excel column expands like crazy, but that's what the users asked for.
anyone ever use this type of thing?
thanks
rik

<td><pre>" + dr["MATERIALDESCRIPTION"].ToString().Replace(Environment.NewLine,"<br />").Replace("\t","&emsp;") + "</pre></td>
0

anyone know how to get rid of the multiple rows this creates? it seems like i start and something else crops up & yeah ive tried :
br { mso-data-placement:same-cell; } - is there a special way or place to put this?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.