SQL to EXCEL through Browser - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

SQL to EXCEL through Browser

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-17 : 22:40:12

Sub DownloadExcelCSV(SQLcommand, CSVFileName)
on error resume next
Response.ContentType = "application/vnd.ms-excel"
%>
<html>
<head>
</head>
<body>
<%
'//Create the DataGrid
Set objDataGrid2 = New clsDataGrid
Call objDataGrid2.CreateConnection(application("DBaddr"))
objDataGrid2.Command = SQLcommand
Call objDataGrid2.SetTableOptions("", 0, 2, 0)
objDataGrid2.GridAlign = "center"
objDataGrid2.AutoGenerateCols = False
objDataGrid2.Bind()
Set objDataGrid2 = Nothing
%>
</body>
</html>
<%
End Sub
%>

The above subroutine generates HTML code that I've validated using the W3C validator. It displays perfectly if I comment out the Response.ContentType line. I'll include the HTML below if anyone's interested.

With the response.contenttype included, EXCEL opens, and I get a blank spreadsheet with no gridlines.

Sam

HTML follows:

<html>
<head>
</head>
<body>
<table border="0" cellpadding="2" cellspacing="0" align="center">
<tr><td align="center">Centername</td><td align="center">Active</td><td align="center">Completions</td><td align="center">Percent</td><td align="center">Denied</td><td align="center">Inactive</td><td align="center">Total Inactive + Active</td></tr>

<tr><td>HDQ</td><td>268</td><td>268</td><td>100.0%</td><td>0</td><td>26</td><td>294</td></tr>

<tr><td>NESDIS</td><td>1915</td><td>1907</td><td>99.6%</td><td>1</td><td>67</td><td>1982</td></tr>
<tr><td>NMFS</td><td>3692</td><td>3591</td><td>97.3%</td><td>0</td><td>278</td><td>3970</td></tr>
<tr><td>NOS</td><td>1825</td><td>1818</td><td>99.6%</td><td>0</td><td>181</td><td>2006</td></tr>

<tr><td>NWS</td><td>5791</td><td>5714</td><td>98.7%</td><td>4</td><td>321</td><td>6112</td></tr>
<tr><td>OAR</td><td>1806</td><td>1804</td><td>99.9%</td><td>2</td><td>215</td><td>2021</td></tr>
<tr><td>OFA</td><td>1158</td><td>1060</td><td>91.5%</td><td>0</td><td>30</td><td>1188</td></tr>

<tr><td>OMAO</td><td>589</td><td>568</td><td>96.4%</td><td>0</td><td>9</td><td>598</td></tr>
<tr><td>Grand Total</td><td>17044</td><td>16730</td><td>98.2%</td><td>7</td><td>1127</td><td>18171</td></tr>
</table>

</body>
</html>

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-18 : 01:02:38

Might that be because Excel doesn't understand HTML ?
If you output the sheet as a CSV it will work.

Damian

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-18 : 10:03:56

Hi Damian,

I had come to believe EXCEL does understand tables (though I've never had it work). There are a number of articles on the subject, here's one

[url]http://www.web-savant.com/users/kathi/asp/samples/tut/Export_to_Excel.asp[/url]

I'd tried to use csv, but EXCEL keeps appending .xls to my filename.csv and it blows the import.

If you have any ideas about how to ensure EXCEL opens the csv as a csv I'd like to hear them.

The code appears below.

Sam

Sub DownloadExcelCSV(SQL, CSVFileName)
on error resume next
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment;filename=" & CSVFileName & ".csv"
openConn()
set rs = DataConn.execute(SQL)
'==================================
' WRITE THE CSV HEADER
'==================================
FirstCol = True
For each rsobj in rs.fields
If Not FirstCol then response.write ", "
FirstCol = False
response.write rsobj.name
next
response.Write vbcrlf

'==================================
' WRITE THE CSV ROWS OF DATA
'==================================
Dim AllRows
AllRows = rs.GetRows()
rs.close
SET rs = Nothing
closeconn()

For y = 0 to Ubound(AllRows, 2)
If VarType(AllRows(0,y)) = vbString Then
response.write ", """ & REPLACE(Cstr(AllRows(0,y)), """", """""") & """"
Else
response.write ", " & Cstr(AllRows(0,y))
End If
For x = 1 to Ubound(AllRows,1)
If VarType(AllRows(x,y)) = vbString Then
response.write ", """ & REPLACE(Cstr(AllRows(x,y)), """", """""") & """"
Else
response.write ", " & Cstr(AllRows(x,y))
End If
Next
response.write vbcrlf
Next

End Sub

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2003-03-18 : 10:29:32

Sam,

What version of Excel are you using to view the document? I do this all the time with Excel 2000 and have no problems.

Jeremy

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-03-18 : 10:32:11

Hi Sam.

It adds .xls because of this row:

Response.ContentType = "application/vnd.ms-excel"

Try replacing with:

Response.ContentType = "text/plain"

it should use excel anyway to open .csv files.

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-18 : 13:32:51

using text/plain - the browser prompts the user to find an application to open the file. This is too much for my customers.

Interestingly, I had no problem with the csv upload the first day it was running. EXCEL would open the file just fine. Then, the following day, it started appending the .xls.

In both cases, the line
Response.ContentType = "application/vnd.ms-excel"

was present.

SamC

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-03-18 : 13:47:46

quote:

using text/plain - the browser prompts the user to find an application to open the file. This is too much for my customers.

Sounds like no program is registered to open .csv files - which is strange. Excel normally registers itself for that extension.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-18 : 18:36:45

Give "application/x-msexcel" a try. See if that works for you.

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-18 : 20:08:05

BTW, you can save a CSV file with an .XLS extension and Excel will convert it correctly, in fact it does it automatically without running the wizard. It should work unless the browser does something to interfere, but I know it works with regular files opened from the hard drive.

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-19 : 00:53:53

I finally got the HTML table to work. I don't know why, but my datagrid control was somehow disrupting the stream. Although the HTML was perfectly conformant, it would not work in EXCEL!

I threw out the datagrid control, and used ADO RS.Getstring to build a table. The resulting table displays nicely in EXCEL.

There's a benefit that HTML has over CSV. When data contains commas or possible " (quotation marks), CSV will require special handling for that field. The <TD>data</TD> doesn't seem to be bothered.

It also seems that the formatting is imported, so the header row can be colored, etc, etc..

Sam

Code follows

Set p_objConn = Server.CreateObject("ADODB.Connection")
p_objConn.Open application("DBaddr")
Set p_objRS = Server.CreateObject("ADODB.Recordset")
Set p_objRS = p_objConn.Execute(SQLcommand)
strRS = p_objRS.GetString(, , "</td><td>", "</td></tr>" & vbcrlf & "<tr><td>", " ")
p_objRS.Close
Set p_objRS = Nothing
p_objConn.Close
Set p_objConn = Nothing

Response.Clear()
Response.AddHeader "Content-Disposition", "attachment;filename=" & CSVFileName & ".xls"
Response.ContentType = "application/vnd.ms-excel"

%>
<table border="1">
<tr><td>

<%= strRS %>
</td></tr>
</table>
</body>

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-19 : 16:18:53

I have a problem with the ADO GETSTRING that may be more for an ADO / ASP forum. If it isn't too out of context, here it is:

The table built by the getstring above always returns an empty last row.

Is it a problem with the way I've coded the table or is it a characteristic of the getstring method?

Sam

changed 'below' to 'above'

Edited by - SamC on 03/19/2003 17:27:50

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-19 : 20:51:43

Yes, ADO has a row TERMINATOR character, not a DELIMITER, it always appends it to the last row of the recordset. It's usually harmless, but you can trim it off by taking the Left() of the resulting string, minus the length of the row terminator character(s).

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-20 : 23:34:41

Thanks Rob,

I flinch at the thought of overhead of pouring one string into another to remove trailing characters, but it did fit nicely in a write..

<table border="1">
<tr><td>

<%= response.write (left(strRS,len(strRS)-17) %>
</td></tr>
</table>
</body>
-----------------------------

It eliminates one step in removing the trailers, and the table looks right now.

New topic, but a variation on the problem of rendering streams without writing the file to disk first..

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment;filename=" & CSVFileName & ".csv"

The two lines above instruct the browser to pass the stream to EXCEL, but what are the counterparts if I want the browser to prompt the user to save a file to disk??

Response.ContentType = "text/plain"
Response.AddHeader "Content-Disposition", "attachment;filename=" & CSVFileName & ".csv"

Surprise - The above lines display the result in the browser window?? I need a no-trick solution to prompt the user to save the file.

Sam

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-20 : 23:55:16

quote:

Surprise - The above lines display the result in the browser window?? I need a no-trick solution to prompt the user to save the file.

Welcome to the world of web applications Sam. Unfortunately it is very hard to force a browser to do anything. You can merely suggest.... and hope

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-21 : 00:03:13

The only way I know that works even slightly reliably is to create an <a href=...> that points to the file...which of course means you have to save it to the web server as one. It's not a big deal really, you write a little job to go through periodically and delete all of the files that are a day or two old. Damian's right though, a browser is not really a file transfer mechanism, nor is HTTP that kind of protocol; they're geared to handle streams, not files.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources