export to excel using string builder - maintain tabs/CrLf in data

edited December 2015 in C#

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.
in fact the spacing (tabs) get killed in this post when i save.
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)
W0135-481 W0135 Outer Barrel 17.5"
W0135-460 W0135 Outer Barrel 17.5"
W0135-456 W0135 Outer Barrel 17.5"
W0135-455 W0135 Outer Barrel 17.5"
728-168S W0135 Outer Barrel 17.5"

protected void ExportAsSB(DataTable myDt)
StringBuilder sb = new StringBuilder();
int i = 0;

"); foreach (DataColumn dc in myDt.Columns) { sb.Append(""); } sb.Append(""); foreach (DataRow dr in myDt.Rows) { sb.Append(""); for (i = 0; i < myDt.Columns.Count; i++) { if (dr[i].ToString() == "") { sb.Append(""); } else { sb.Append(""); } } sb.Append(""); } sb.Append("
" + dc.ColumnName + "
" + dr[i].ToString() + "

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



  • 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 " " ............ but i read somewhere that encapsulating it in "


    " 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?

    <td><pre>" + dr["MATERIALDESCRIPTION"].ToString().Replace(Environment.NewLine,"<br />").Replace("\t","&emsp;") + "</pre></td>
  • anyone know how to get rid of the multiple rows this


    pre> 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?

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion