New Zealand's new Copyright Law presumes 'Guilt Upon Accusation' and will Cut Off Internet Connections without a trial. CreativeFreedom.org.nz is against this unjust law - help us


A Space for All Things .NET Related


How to output numeric data as strings in Excel

By James Hippolite, in , posted: 28-Sep-2007 07:27

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



Tag(s):         


Other related posts:
Geek Post Monthly Newsletter Volume 2 Issue 6
Exam 70-300 Objective 3
COALESCE T-SQL Function






Add a comment

Please note: comments that are inappropriate or promotional in nature will be deleted. E-mail addresses are not displayed, but you must enter a valid e-mail address to confirm your comments.

Are you a registered Geekzone user? Login to have the fields below automatically filled in for you and to enable links in comments. If you have (or qualify to have) a Geekzone Blog then your comment will be automatically confirmed and shown in this blog post.

Your name:

Your e-mail:

Your webpage:

JamesHip's profile

James Hippolite
Wellington
New Zealand


Welcome to my technical blog. 

Here, I attempt to distill the Microsoft Certified Professional Developer knowledge I have accumulated since first qualifying MCP in 1996.  This blog started on 13 September 2007 as an off-shoot from my mixed up personal blog.  But it took a shot in the arm from Scott Hanselman's talk at TechEd New Zealand 08 "32 Ways To Make Your Blog Suck Less".