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;
sb.Append("

"); 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.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();
«1345

Comments

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!

Categories