how to create XLS file using ASP

[jermine]

i need to create an excel file of the result of my SQL query using ASP.

in my query form, i am supposed to have two buttons.
The first one is the submit (i have no problem directing the result of the query to another page). But i dont know what to do with the second button. THIS button should generate an excel version of the report / result of my query.

i was told to use a "session". but i dont know what this is or how to use it. how do i tell the "generate excel file" button to use the same parameters of my query and create the xls file. =(
i am fairly new with asp and VB AND im stuck on this for 2 days now..

please please help.

thanks in advance

 

[Bastien]

see thread333-109338

hth

Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[ToddWW]

Read these documents. They have very good information about generating Excel files with ASP.


Article #1: Gives example code and shows how to stream a Basic Excel Worksheet to the browser. (Excel 97 and up)

http://support.microsoft.com/support/kb/articles/Q199/8/41.ASP

Article #2: Gives example code and shows how to stream a formatted Excel Workbook (with multiple worksheets) to the browser. (Excel 2000 and up)

http://support.microsoft.com/support/kb/articles/Q271/5/72.ASP

Article #3: Gives example code on how you can actually launch your visitor's Excel Software using client side VBScript, create worksheets, and populate the worksheets with data. (Very Cool)

http://support.microsoft.com/support/kb/articles/Q198/7/03.ASP

Once you get the hang of it, here's what I like to do. I go to Excel and create the look and feel of the worksheet that I want ASP to generate. Then I save that worksheet / workbook as an HTML document. View the source of the HTML document and have ASP copy that format.

Hope this helps..

TW

 

[Deadline]

Simple...
Just add the following line to the top of your code.

<%response.ContentType = &quot;application/vnd.ms-excel&quot; %>

And whatever follows will be automatically interpreted as Excel file.

Now have two buttons in the main page(btnSubmit and btnSubmitExcel).

In the next page(result.asp), include the following at the top of the page..

<%
 IF Request.Form(&quot;btnSubmitExcel&quot;)=&quot;Submit&quot; THEN 
   response.ContentType = &quot;application/vnd.ms-excel&quot;
 END IF %>

Hope this helps...Let us know how this technique worked for you... Thank you...
RR

 

[jermine]

can i specify the path where i want to save the excel file???

where to i put the code:

<%response.ContentType = &quot;application/vnd.ms-excel&quot; %> ??
in the main asp page??

 

[ToddWW]

Jermine,

No you cannot specify a path for the Excel file to be saved. Depending on the settings of the computer that is requesting the Excel file, Windows will either prompt to save the file (in which case the user specify's the file location) or windows will load the excel document into the browser window (then the user can select file / save as to save the file) or windows will open the file in a seperate excel window.

Read the articles that I suggested above. If you do nothing more, follow the instructions / copy to script into your environment just to see how it works. It answers your second question on code placement.

TW

 

[vikaskalra]

Hi Todd,

IN YOUR LAST POST YOU HAVE SPECIFIED:

(Depending on the settings of the computer that is requesting the Excel file, Windows will either prompt to save the file (in which case the user specify's the file location) or windows will load the excel document into the browser window (then the user can select file / save as to save the file) or windows will open the file in a seperate excel window)

COULD YOU PLEASE SPECIFY WHAT ARE THOSE SETTINGS ON THE CLIENT THAT CHANGE THE BEHAVIOUR OF THE PROCEDURE FOR DISPLAYING THE EXCEL SHEET IN IE.

PLEASE REPLY ASAP,

REARDS,
VIKAS

 

[jasonymk]

hi TW,

i have read and try the article you have post about automating excel files in browser...but for the 3rd article, when i move the excel.html file to my server, it wont work. i wonder what should be change in the code?

please help thank you.

jason

 

[Mighty]

I have another question in relation to Todd

http://WWW's

response. I used the Article 1 method and my Excel file is being created in a browser window. The problem is that Excel is interpreting some fields as date fields when I want them to be a string. How can I set the format of all fields to text only.

Mighty