How to export plain text from web site to Excel without data conve
 

Hi all,

I have made a web site where I produce report data to be (optionally) viewed
in Excel at the client computer. This is how I do it i Visual Basic (Studio
2005, .NET 2.0) (extract) at the web server (in the *.aspx.vb file):

Dim delimiter As Char = vbTab
Response.Clear()
Response.Cache.SetCacheability(HttpCacheability.No Cache)
Response.AppendHeader("content-disposition",
"inline;filename=report.xls")
Response.ContentType = "application/ms-excel"
Dim lstrbReportData As StringBuilder = GetTheData(delimiter)
Response.Write(lstrbReportData.ToString )
Response.End()
Response.Close()

This works fine EXCEPT that Excel will do data conversions. E.g. leading
zeroes are removed, values may be converted to dates etc.

Is there a way I can tell Excel to treat all data as plain text, similar to
when I manually import a text file to excel, where I can set all columns to
Text format instead of General?

Some Response.appendheader maybe?

How to export plain text from web site to Excel without data conve
 

I have found a solution. If anyone is interested, I post it here.

The Response.appendheader adds a header to http which is consumed by the
browser (and not excel) so that is not the solution.
http://www.w3schools.com/ASP/met_addheader.asp

I need to communicate with Excel, so I checked for command line arguments
(switches). There is no option to tell Excel to treat everything as text. But
it should have been! See e.g.
http://office.microsoft.com/en-us/ex...543831033.aspx

So I need to talk to Excel directly. I do this by using html, because Excel
understands html.

In Excel, if you look at Format - Cell, you can select Custom and enter the
option @. This means Text. (You may enter date formats etc, but I want Text).
In html you can tell Excel this via the mso-number-format which can be
entered as a style.
mso = Microsoft Office
http://agoric.com/sources/software/htmltoExcel

So in my code (see previous post), I just update the GetTheData method. I
don't need the delimiter any more. In GetTheData I have a header like this:

Const lcstrStartHTML As String = _
" <html" & _
" <head" & _
" <style" & _
" td {mso-number-format:\@}" & _
" .heading {mso-number-format:\@;font-weight:bold;color:Maroon;}" & _
" </style" & _
" </head" & _
" <body" & _
" <table"


and a trailer like this:
Const lcstrEndHTML As String = _
" </table" & _
" </body" & _
" </html"

In the data production I insert the following constants as appropriate:

Const lcstrRowStart As String = "<tr"
Const lcstrRowEnd As String = "</tr"
Const lcstrCellStart As String = "<td"
Const lcstrHeaderDataStart As String = "<td class=""heading"""
Const lcstrCellEnd As String = "</td"

Summary example of GetTheData algorithm:

Dim lstrB As New StringBuilder(lcstrStartHTML)
lstrB.Append(vbCrLf & lcstrRowStart & vbCrLf)
For Each s As String In Headings
lstrB.Append(lcstrHeaderDataStart & s & lcstrCellEnd)
Next
lstrB.Append(vbCrLf & lcstrRowEnd & vbCrLf)

lblnFinishedReadingData = lobjDataFetch.GetNextRow(larrReport)
While Not lblnFinishedReadingData
lstrB.Append(vbCrLf & lcstrRowStart & vbCrLf)
For Each s As String In larrReport
lstrB.Append(lcstrCellStart & s & lcstrCellEnd)
Next
lstrB.Append(vbCrLf & lcstrRowEnd & vbCrLf)
lblnFinishedReadingData = lobjDataFetch.GetNextRow(larrReport)
End While
lstrB.Append(vbCrLf & lcstrEndHTML)
Return lstrB


Voila!