f you are using ASP pages to display data?usually data from a database in a report format?it is usually beneficial to send this data as an Excel Spreadsheet that your users can download and work on. For example, a manager could view data on-screen via your ASP page, but if they want to format it a different way before presenting it to their boss, they cannot use your report as-is. However, if they can get the data as an Excel Spreadsheet, then they can do what they want with it.
The technique itself is extremely simple. To send data as an Excel Spreadsheet, you need to do two things. First, indicate to the browser that the data that is coming is in Excel format. Then format your data as a set of Comma Separated Values.
- Indicate that the data is being sent in Excel format and provide a file name for your streaming data. The browser will open the appropriate application to view your data based on the extension of the filename you provide. In your ASP page, use the following code. (Note: The following lines of code have to be sent to the browser before any content is sent. Otherwise, you will get an HTTP header error, so place them at the very top of your ASP page.)
- Format the data. You have to send each row of the Excel spreadsheet as a Comma Separated list of columns in the sheet. So for a 4-column-by-5-row spreadsheet, you will have to send five rows of data. In each row, you will separate the four columns of data with commas. If the data is a string containing a comma, delimit it with double quotes.
' -- Tell the browser the data is a Comma Separated Values List (CSV)response.ContentType="application/csv"' -- Tell the browser to associate a file name with the dataResponse.AddHeader "Content-Disposition", "filename=mydata.csv;"
Assuming that you are sending the contents of an ADO recordset use the following code:
' -- First, send the header row containing the field names.' -- For this example we are using the recordset field names. ' -- You could also substitute with your own header field names if required.' -- Assume that your recordset variable is called objRSDim i, strQuoteFor i = 0 to objRS.Fields.Count - 1 Response.Write objRS.Fields(i).Name & ","Next' -- End the line so your row is complete; use the built-in constant 'vbnewLine'Response.Write vbNewLine' -- Then send the entire data setDo While Not objRS.EOF For i = 0 to objRS.Fields.Count - 1 Response.Write objRS.Fields(i) & "," Next ' -- End the line so your row is complete Response.Write vbNewLine ' -- Next record objRS.MoveNextLoop
If you are reporting data using ASP, you can also provide a link that says 'Download this data as an Excel Spreadsheet,' so your users can view data any way they want.
To see a sample of this code, browse to 10MinExcel.asp. Use the link at the bottom to obtain the data as an Excel spreadsheet. You will be prompted to either Open or Save As. Choose either option and view the data in Excel.