ASP: Export to Excel

Introduction

Author: Martin Warning
Year: 2007
License: Free

This code shows an example on how to export data to Microsoft's Excel (xls) format. This code is most useful when the data is received from a database. This allows you to loop through the different fields. To export to Excel you have to create a table in (X) HTML which will be the basis for the cells in Excel. The code examples provided are complete ASP documents. To use them just copy all the code into an ASP document, save it and you can try it out.

The key to this export is the our output is not an html document but an Excel document. To do so we need to tell the browser what document type we are sending it. We use the ASP code Response.ContentType = "application/vnd.ms-excel" to achieve this. Response.ContentType is used to identify a document as a different file type to the browser. Excel's xls format is one of many document types you can create using ASP.

Code

Languages: The following code is an example export document: 
<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>

<% Response.ContentType = "application/vnd.ms-excel" %>
<html>
<body>
<table>
<tr>
	<td style="background-color:#aeaeae;"><b>Column 1</b></td>
	<td style="background-color:#aeaeae;"><b>Column 2</b></td>
	<td style="background-color:#aeaeae;"><b>Totals</b></td>
</tr>
<tr>
	<td>1</td>
	<td>2</td>
	<td><b>=SUM(A2:B2)<b></td>
</tr>
<tr>
	<td>3</td>
	<td>4</td>
	<td><b>=SUM(A3:B3)<b></td>
</tr>
</table>
</body>
</html>
Example: See it in action

This code will export 3 rows and 3 columns to Excel. In the last column in row 2 & 3 we call the Excel SUM function to sum the result of the 2 previous columns. We will also do some formatting in Excel with CSS. The First row will have a gray background and bold titles. The last column will have the data in bold.

The following code is a more complex export document: 
<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>

<% Response.ContentType = "application/vnd.ms-excel" %>

<html>
<body>
<table>
<tr>
	<td style="background-color:#aeaeae;"><b>Column 1</b></td>
	<td style="background-color:#aeaeae;"><b>Column 2</b></td>
	<td style="background-color:#aeaeae;"><b>Totals</b></td>
</tr>
<tr>
	<td>1</td>
	<td>2</td>
	<td><b>=SUM(A2:B2)</b></td>
</tr>
<tr>
	<td style="border-bottom-width:2px;border-bottom-style:solid;">3</td>
	<td style="border-bottom-width:2px;border-bottom-style:solid;">4</td>
	<td style="border-bottom-width:2px;border-bottom-style:solid;"><b>=SUM(A3:B3)</b></td>
</tr>
<tr>
	<td><b>=SUM(A2:A3)</b></td>
	<td><b>=SUM(B2:B3)</b></td>
	<td><b>=IF(C2<C3,"Yes","No")</b></td>
</table>
</body>
</html>

In this code we add some things to the previous code. We add some more formatting by including a line above the last row. We also include a more complex Excel code by using an IF clause.

Example: See it in action

We can use Response.AddHeader "Content-Disposition", "attachment; filename=ExcelExport.xls" to force the browser to open the document in Excel instead of inside the browser.

The following code shows the addition of this code: 
<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=ExcelExport.xls"
%>

The user can either open the document or save it depending on the user browser. In the case the document is saved we predefine the name to use to save the Excel document with filename=ExcelExport.xls. The user can of course define a different name. You could for example use a variable to determine the file name by using something like filename=" & strFileName where strFileName is the variable for the file name.

Downloads

ASP:
Zip file example.zip

Printed from: http://flyinglowlander.com/ (5/20/2012)
© FlyingLowander.com 2006 - 2012

Visit http://flyinglowlander.com for more XHTML, CSS, ASP and JavaScript examples, templates and tutorials.