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