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

J

Jakki

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.NoCache)
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?
 
J

Jakki

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/excel/HA101543831033.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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Save As Authentication Issue From ASP.NET 0
Export to Excel 2007 5
Copying data from Web Site to Excel spreadsheet 0
Export data from Excel to a local web page 0
How to load text into Excel from web? 3
Importing web data to Excel 2003 1
export plain data to Text file 5
Exporting data to Excel 1

Top