[ Download Code ]
There is a quick trick to ensuring newlines are handled correctly when rendering a DataTable to Excel. The key is adding the following style:
tr { mso-data-placement: same-cell; }
The example demonstrates assigning a DataTable to a DataGrid's DataSource and then using an HtmlTextWriter to render the DataGrid control. The result is then written via the Response object. In order to handle newlines the style needs to be streamed out to the Response object before the data.
This is accomplished by the following:
string header = @"<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">
<head>
<style>
<!--table
br {mso-data-placement:same-cell;}
tr {vertical-align:top;}
-->
</style>
</head>
<body>";
Response.Write(header);
The header is included because the ExcelUtil.cs class clears the Response object before rendering any data. After the data is written to the Response object the stream is concluded by writing out the body and HTML end tags.
string footer = "</body></HTML>";
Response.Write(footer);
Download the example to see the code in action.