Browse DevX
Sign up for e-mail newsletters from DevX


Got Data? Send It to Your Users as an Excel Spreadsheet from Your ASP Page

To send data as an Excel spreadsheet, 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. Get a step-by-step tutorial.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

  1. 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.)
  2. ' -- 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 data Response.AddHeader "Content-Disposition", "filename=mydata.csv;"

  3. 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.

  4. 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 objRS Dim i, strQuote For 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 set Do 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.MoveNext Loop

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.

Rama Ramachandran is the Vice President of Technology with Imperium Solutions and is a Microsoft Certified Solution Developer and Site Builder. He has extensive experience with building database systems and has co-authored several books including "Professional Visual InterDev 6 Programming" and "Professional Data Access" (Wrox). Rama teaches Visual Basic and Web development at Fairfield University and University of Connecticut.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date