Use ASP/SQL to export to Excel

sankar · July 17th, 2003, 07:48 AM

How can I prompt the user to save the newly created excell file on his machine? I am chaning the header type to make excel file from database but it gets opened in Internet Explorer. I don't want that to happen. Pls suggest.
Thx in adv.

uit · July 17th, 2003, 08:42 AM

Are you creating an actual Excel File? If so, how are you doing that? I've always tried to but just never figured it out so I create a CSV file on the fly and then the user can open it (if Excel is installed, Excel opens it) if not, then its a text file.

Anyhow, the first 5-6 lines or so are what does the trick for what you've asked:

<%
fn = "QUERYRESULTS.xls"
Response.Buffer = TRUE
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition","attachment;filename=" & fn
'Response.AddHeader "Content-Disposition","filename=D:\results.xls"

set oCNN = server.createobject("adodb.connection")
set oRST = server.CreateObject("adodb.recordset")
oCNN.open application("SQLCONN")
strSQL = session("_MAINSQL")
set oRST = oCNN.Execute(strSQL)

%>
<HTML>
<BODY>
<TABLE border="1" cellpadding="0" cellspacing="0" bordercolor="#a9a9a9">
    <TR>
        <%
        With oRST
            For Each fld In .Fields
                With fld
                    %>
                    <TH bgcolor="#003468">
                        <%=.Name%>
                    </TH>
                    <%
                End With
            Next 'fld
        End With
        %>
    </TR>
<%

'--Reset Pointer To First Row...
oRST.MoveFirst()

Do Until oRST.EOF
%>
    <TR>
    <%
    With oRST
        For Each fld in .Fields
            With fld
                %>
                    <TD><%=.Value%></TD>
                <%
            End With
        Next 'fld
    %>
    </TR>
<%
    .MoveNext()
    End With
Loop
%>
</TABLE>
<%
oRST.Close
Set oRST = Nothing
oCNN.Close
Set oCNN = Nothing
%>
</BODY>
</HTML>

Greg Griffiths · July 17th, 2003, 02:19 PM

There is a good tutorial at http://www.greggriffiths.org/webdev/both/excel/ on this subject.

U.N.C.L.E. · July 18th, 2003, 01:45 AM

sankar,

I think your problem is with Excel opening in your browser to display your spreadsheet that you generate. Correct?

The solution to your problem depends on the version of Office you have installed. There is also a known bug when you generate an Excel spreadsheet (really an html table) using content type of application/vnd.ms-excel from within an ASP page.

If you are using Office 2000 you might want to look at this page (it suggest upgrade to Office SR-1a):
http://support.microsoft.com/default...b;EN-US;266263

Here is a way to modify the open or prompt to save behavior you describe:
http://support.microsoft.com/default...b;en-us;303475

Hope that helps.

rodmcleay · July 18th, 2003, 02:12 AM

I have a neat solution for this problem but I have not fully read previous suggestions so please take as one of many answers.

copy this code below into a page and call it downloadFile.asp
replace the yourIPAddress and yourFolder with the ipaddress and folder of the place you will be storing the excel files.
replace yourFile.xls (in two places) with a file that is in the folder, just to test it.
If you put a link to this page on another page it will automatically prompt you to open/save the file.
It will work for any file type.

Then ammend the code to take the file name as a querystring parameter that you will provide in the link and you are away.

A nice little bonus with this code is you can point the file to another asp page which generates a comma seperated page (or anything for that matter) from a databases and it will save/open that. To do this you would have a different value for the file name in the "xml.open" line (to point to the asp page) to the one in the "filename=yourFile.xls" (which is what the file will be called when saved).

This work and it works well so if you have any probs let me know and I will send you some demo code.

Code:

<% @language ="vbscript" %>
<% response.buffer = true %>
<%    
    Response.Buffer = True
      Dim objXMLHTTP, xml
      Set xml = Server.CreateObject("Microsoft.XMLHTTP")     xml.Open "GET","http://yourIPAddress/yourFolder/yourFile.xls",False
      xml.Send    
      ' Add a header to give it a file name:
      Response.AddHeader "Content-Disposition", _
    "attachment;filename=yourFile.xls"    
      ' Specify the content type to tell the browser what to do.
    'THIS FOOLS THE BROWSER INTO THINKING YOU WISH TO DOWNLOAD A ZIP FILE
      Response.ContentType = "application/zip"    
      ' Binarywrite the bytes to the browser
      Response.BinaryWrite xml.responseBody
      Set xml = Nothing
%>

Good luck

Rod

sankar · July 18th, 2003, 02:22 AM

Quote:

quote:Originally posted by Greg Griffiths
There is a good tutorial at http://www.greggriffiths.org/webdev/both/excel/ on this subject.

I have checked this tutorial. Although it talks about prompting the user for saving the newly created .csv file, it actually never demonstrates that.

sankar · July 18th, 2003, 02:36 AM

Quote:

quote:Originally posted by uit
Are you creating an actual Excel File? If so, how are you doing that? I've always tried to but just never figured it out so I create a CSV file on the fly and then the user can open it (if Excel is installed, Excel opens it) if not, then its a text file.

Anyhow, the first 5-6 lines or so are what does the trick for what you've asked:

<%
fn = "QUERYRESULTS.xls"
Response.Buffer = TRUE
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition","attachment;filename=" & fn
'Response.AddHeader "Content-Disposition","filename=D:\results.xls"

set oCNN = server.createobject("adodb.connection")
set oRST = server.CreateObject("adodb.recordset")
oCNN.open application("SQLCONN")
strSQL = session("_MAINSQL")
set oRST = oCNN.Execute(strSQL)

%>
<HTML>
<BODY>
<TABLE border="1" cellpadding="0" cellspacing="0" bordercolor="#a9a9a9">
    <TR>
        <%
        With oRST
            For Each fld In .Fields
                With fld
                    %>
                    <TH bgcolor="#003468">
                        <%=.Name%>
                    </TH>
                    <%
                End With
            Next 'fld
        End With
        %>
    </TR>
<%

'--Reset Pointer To First Row...
oRST.MoveFirst()

Do Until oRST.EOF
%>
    <TR>
    <%
    With oRST
        For Each fld in .Fields
            With fld
                %>
                    <TD><%=.Value%></TD>
                <%
            End With
        Next 'fld
    %>
    </TR>
<%
    .MoveNext()
    End With
Loop
%>
</TABLE>
<%
oRST.Close
Set oRST = Nothing
oCNN.Close
Set oCNN = Nothing
%>
</BODY>
</HTML>
This is not opening the file download confirm box, anyway it creates an HTML table and displays the data in that

sankar · July 18th, 2003, 02:57 AM

Quote:

quote:Originally posted by rodmcleay
I have a neat solution for this problem but I have not fully read previous suggestions so please take as one of many answers.

copy this code below into a page and call it downloadFile.asp
replace the yourIPAddress and yourFolder with the ipaddress and folder of the place you will be storing the excel files.
replace yourFile.xls (in two places) with a file that is in the folder, just to test it.
If you put a link to this page on another page it will automatically prompt you to open/save the file.
It will work for any file type.

Then ammend the code to take the file name as a querystring parameter that you will provide in the link and you are away.

A nice little bonus with this code is you can point the file to another asp page which generates a comma seperated page (or anything for that matter) from a databases and it will save/open that. To do this you would have a different value for the file name in the "xml.open" line (to point to the asp page) to the one in the "filename=yourFile.xls" (which is what the file will be called when saved).

This work and it works well so if you have any probs let me know and I will send you some demo code.

Code:

<% @language ="vbscript" %>
<% response.buffer = true %>
<%    
    Response.Buffer = True
      Dim objXMLHTTP, xml
      Set xml = Server.CreateObject("Microsoft.XMLHTTP")     xml.Open "GET","http://yourIPAddress/yourFolder/yourFile.xls",False
      xml.Send    
      ' Add a header to give it a file name:
      Response.AddHeader "Content-Disposition", _
    "attachment;filename=yourFile.xls"    
      ' Specify the content type to tell the browser what to do.
    'THIS FOOLS THE BROWSER INTO THINKING YOU WISH TO DOWNLOAD A ZIP FILE
      Response.ContentType = "application/zip"    
      ' Binarywrite the bytes to the browser
      Response.BinaryWrite xml.responseBody
      Set xml = Nothing
%>

Good luck

Rod
Hi Rod,
It opens in Internet Explorer with some binary code. Pls suggest what to do.

rodmcleay · July 18th, 2003, 03:00 AM

I will send you and email directly. Send back your email address

Rod

======================================
They say, best men are moulded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

rodmcleay · July 18th, 2003, 03:42 AM

Sankar,
I have not heard from you and I am leaving for the weekend.

Here is my most detailed instruction demo which I have working on my machine now. If you still have problems....
Speak to you on Monday.

Create the three pages below in your web application as three seperate pages.

Change the IPADDRESS in download asp to the IPADDRESS of you application and the FOLDER name / structure to where ever you put the files within your application.

Also save any excel file and call it testExcelFile.xls into the same directory.

Go to theLinkPage.html in your app and test the links.

Once you get this working you can mess with the code to make it suit your files and folders better.

::::CODE FOR PAGE CALLED (download.asp)::::
<% @language ="vbscript" %>
<% response.buffer = true %>
<%
    Response.Buffer = True
     Dim objXMLHTTP, xml
     Set xml = Server.CreateObject("Microsoft.XMLHTTP")
    'Point this to the file you wish to download
     xml.Open "GET","http://IPADDRESS/FOLDER/" & request("FileName"),False
     xml.Send
     ' Add a header to give it a file name:
     Response.AddHeader "Content-Disposition", _
    "attachment;filename=" & request("DisplayFileName")
     ' Specify the content type to tell the browser what to do.
    'THIS FOOLS THE BROWSER INTO THINKING YOU WISH TO DOWNLOAD A ZIP FILE
     Response.ContentType = "application/zip"
     ' Binarywrite the bytes to the browser
     Response.BinaryWrite xml.responseBody
     Set xml = Nothing
%>
::::END CODE (download.asp)::::

::::CODE FOR PAGE CALLED (generatedExample.asp)::::
<% @language ="vbscript" %>
<% response.buffer = true %>
<%
'Obviously you can do much more than this with a db connection and stuff.
response.write("CSV FILE WILL OPEN IN EXCEL" & vbNewLine)
response.write("a,b,c,d" & vbNewLine)
response.write("1,2,3,4" & vbNewLine)
%>
::::END CODE (generatedExample.asp)::::

::::CODE FOR PAGE CALLED (theLinkPage.html)::::

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
    <title>Untitled</title>
</head>

<body>
These are the links you put on your page.
<BR>
<a href="download.asp?DisplayFileName=displayExcelFil eName.xls&FileName=testExcelFile.xls">Download Your Excel File</a>

<BR><BR>
<a href="download.asp?DisplayFileName=displayGenerate dFileName.xls&FileName=generatedExample.asp">Downl oad Your Generated File</a>

</body>
</html>

::::END CODE (theLinkPage.html)::::

The working demo is at

http://192.168.150.100:81/HiSankar/theLinkPage.html

But you can not see the working asp code that way.

'Av a Good Weekend!
Rod

======================================
They say, best men are moulded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================