A customer had a requirement to do this. The default implementation of export to Excel doesn't do this. I had to go looking for it. This is what I found:
'<summary>
'An overload for this function
'</summary>
Public Shared Sub DataTableToXhtmlTable(ByVal dt As DataTable, ByVal filename As String, ByVal WriteToResponse As Boolean)Dim dv As New DataView(dt)
DataTableToXhtmlTable(dv, filename, WriteToResponse)
End Sub
'<summary>
'This export method is similar to the common technique of binding a dataset to a datagrid/gridview
'and rendering the contents to produce a HTML table that Excel can understand. However the datagrid
'approach is not reliable if the data contains html characters, e.g. < or >, it produces invalid XML,
'which causes problems in Excel and OpenOffice.
'An alternative approach is to derive a GridView control that automatically sets HtmlEncode = true on
'all the BoundColumns, but this can produce very bloated output where non ASCII characters are represented
'and Excel will not decode the HtmlEncoded text.
'I found the simplest approach is to parse the dataview and write out an XHTML table. This way the
'output is guaranteed to be valid XHTML, and compatible with Excel and OpenOffice (use the HtmlDocument filter).
'</summary>
'<param name="dv"> The data source</param>
'<param name="filename"> If WriteToResponse is true, this must be a file name, otherwise a full path+file name to save the file to</param>
'<param name="WriteToResponse"> if true, Response.Writes the output to the client browser,
' otherwise writes the contents to the specified file path</param>
Public Shared Sub DataTableToXhtmlTable(ByVal dv As DataView, ByVal filename As String, ByVal WriteToResponse As Boolean)
Using sw As StringWriter = New StringWriter()sw.WriteLine(
My.Resources.ExcelBookXML.Header)
For Each dr As DataRow In dv.Table.Rowssw.WriteLine(
"<Row>")
For i As Integer = 0 To 13
Dim o As Object = dr.ItemArray(i)
If o.ToString = "" Then
sw.WriteLine("<Cell ss:StyleID='s23' />")
Else
Select Case i
Case 0
'An ordinary strgin
sw.WriteLine("<Cell ss:StyleID='s22'>" + _
"<Data ss:Type='String'>{0}" + _
"</Data></Cell>", XmlEscape(o.ToString()))
Case 1sw.WriteLine(
"<Cell ss:StyleID='s22'>" + _
"<Data ss:Type='String'>{0}" + _
"</Data></Cell>", XmlEscape(o.ToString()))
Case 2 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))
Case 3 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))
Case 4
'An ordinary number
sw.WriteLine("<Cell ss:StyleID='s22'>" + _
"<Data ss:Type='Number'>{0}" + _
"</Data></Cell>", XmlEscape(o.ToString()))
Case 5 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))
Case 6
'An ordinary date
Dim StartDate As DateTime = CDate(o)
sw.WriteLine("<Cell ss:StyleID='s24'>" + _
"<Data ss:Type='DateTime'>{0}" + _
"</Data></Cell>", StartDate.ToString("yyyy-MM-dd"))
Case 7 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))
Case 8
'A number that I want formatted as string
sw.WriteLine("<Cell ss:StyleID='s23'>" + _
"<Data ss:Type='String'>${0}" + _
"</Data></Cell>", XmlEscape(o.ToString()))
Case 9 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='Number'>{0}</Data></Cell>", XmlEscape(o.ToString()))
Case 10 : sw.WriteLine("<Cell ss:StyleID='s23'><Data ss:Type='String'>${0}</Data></Cell>", XmlEscape(o.ToString()))
Case 11 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))
Case 12 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))
Case Else : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))
End Select
End If
Next
sw.WriteLine("</Row>")
Next
sw.WriteLine(My.Resources.ExcelBookXML.Footer)
If (WriteToResponse) Then
Dim response As HttpResponse = HttpContext.Current.ResponseWith response
.Clear()
.Charset = System.Text.UTF8Encoding.UTF8.WebName
.ContentEncoding = System.Text.UTF8Encoding.UTF8
.AddHeader("Content-Disposition", String.Format("attachment; filename='{0}';", filename))
.ContentType = "application/vnd.ms-excel"
.Write(sw.ToString())
.End()
End With
Else
File.WriteAllText(filename, sw.ToString())
End If
End Using
End Sub
'<Summary>
' Replace < & > characters with their xml escaped equivalents
'</Summary>
Public Shared Function XmlEscape(ByVal s As String) As String
s = Regex.Replace(s, "<", "<")
s = Regex.Replace(s, ">", ">")s = Regex.Replace(s,
"&", "&")
If s = "" Then
Return Nothing
Else
Return s
End If
End Function
