How to output numeric data as strings in Excel

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, "<", "&lt;")

s = Regex.Replace(s, ">", "&gt;")s = Regex.Replace(s,

"&", "&amp;")

If s = "" Then

Return Nothing

Else

Return s

End If

End Function