How To Export Your Displayed Data To Excel

Merlin1857 · #1 September 12th, 2007, 08:13 AM

Its great producing data for users to look at in your web pages and generally that is sufficient for their needs but sometimes you may want to supply your user with the data in a form they can actually do something more with. This code shows you how to display data from your database and then how to give that data to the user in the form of a useable Excel spreadsheet which they can then take away and play with themselves. The way I have shown this done here is to display the data first, using my article 'How To Dynamically Search A Database Table' you could give your users the ability to re-query the data before they produce it. This is the code for the first page called 01viewdata.asp :

Expand|Select|Wrap|Line Numbers

  
<%@ Language=VBScript %>

<!-- #include file="../incfiles/adovbs.inc" -->

<% 

'Declare some variables

dim Conn,RS1

'==================CONNECTION CODE TO SQL SERVER=======================

'Create a connection object to the database

Set Conn = Server.CreateObject("ADODB.Connection")
 
'Create a recordset object

Set RS1 = Server.CreateObject("ADODB.RecordSet")
 
'Feed the connection string into a variable called strConn

strConn = "Provider=SQLOLEDB;Data Source=MYSERVERNAME;Initial Catalog=MYDATABASENAME;"&_

"User Id=USERNAME;Password=PASSWORD;"
 
'Feed the connection string into the connection object

Conn.Open strConn 

'==========================CONNECTION CODE=============================
 
'Create your sql statement

sql1 = "Select * from tblMYTABLE WHERE ID < 10000" 
 
'Obtain the data using all the work done above

RS1.Open sql1, Conn 

'Test to see if we have a recordset coming back from the database and trap the error if there is no data

If RS1.eof Then

%>

<html>

<body>

<table border="0" width="100%" cellspacing="0" cellpadding="2">

<tr>

<td>&nbsp;<font face="Arial" color="#FF0000" size="2">No Records Match Your Search</font></td>

</tr>

</table>

<%

'If there is data carry on

Else

%>

<!-- 

We will need to fire the data we gather to the next page which produces the Excel sheet so here 

we use a form to do so instead of formulating the sql statement again when we export to excel 

I pass the statement above inside a hidden field called 'InpSQL' below, this makes sure that 

what they see on this page is exactly what they get in Excel.

-->

<form method="POST" action="02extoex.asp">

<table border=0 width="100%" cellspacing="0" cellpadding="2">

<tr>

<td>

<input type="submit" value="Export To Excel Spread Sheet" name="B1" style="font-size: 8pt">&nbsp;

<input type="hidden" name="InpSQL" size="63" value="<%=sql1%>"></td>

</tr>

</table>
 
</form>

<!-- This area is for display to the user and shows how the data to be exported will look -->

<table border="1" width="100%" cellspacing="0" cellpadding="2" bordercolor="#000000">

<tr>

<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field1Header</font></td>

<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field2Header</font></td>

<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field3Header</font></td>

<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field4Header</font></td>

<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field5Header</font></td>

<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field6Header</font></td>

<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field7Header</font></td>

</tr>

<%

'Whilst there are records to show keep going

Do While Not RS1.eof

%>

<!-- 

Note the use of &nbsp; in each field this makes sure the data looks OK in

a bordered table if the return is null. 

-->

<tr>

<td><font face="Arial" size="2"><%=RS1("Field1Result")%>&nbsp;</font></td>

<td><font face="Arial" size="2"><%=RS1("Field2Result")%>&nbsp;</font></td>

<td><font face="Arial" size="2"><%=RS1("Field3Result")%>&nbsp;</font></td>

<td><font face="Arial" size="2"><%=RS1("Field4Result")%>&nbsp;</font></td>

<td><font face="Arial" size="2"><%=RS1("Field5Result")%>&nbsp;</font></td>

<td><font face="Arial" size="2"><%=RS1("Field6Result")%>&nbsp;</font></td>

<td><font face="Arial" size="2"><%=RS1("Field7Result")%>&nbsp;</font></td>

</tr>

<%

'Loop through each record and write it to the screen

RS1.Movenext

Loop

End If

%>

</table>

</body>

</html>
 
<%

'Clean up and close the connections and recordset objects

RS1.Close

Conn.Close
 
Set Conn = Nothing

Set RS1 = Nothing

%>

This is the clever bit which exports to excel. You could fire this out straight from a link without the need to display it like we did above if you wanted to which would result in the Excel spreadsheet coming straight into existence. This is the code for a page called 02extoex.asp which the link from the first page produces.

Expand|Select|Wrap|Line Numbers

  
<%@ Language=VBScript %>

<!-- #include file="../incfiles/adovbs.inc" -->

<% 

'Declare some variables

dim Conn,RS1
 
'==================CONNECTION CODE TO SQL SERVER=======================

'Create a connection object to the database

Set Conn = Server.CreateObject("ADODB.Connection")
 
'Create a recordset object

Set RS1 = Server.CreateObject("ADODB.RecordSet")
 
'Feed the connection string into a variable called strConn

strConn = "Provider=SQLOLEDB;Data Source=MYSERVERNAME;Initial Catalog=MYDATABASENAME;"&_

"User Id=USERNAME;Password=PASSWORD;"
 
'Feed the connection string into the connection object

Conn.Open strConn 

'==========================CONNECTION CODE=============================
 
'Obtain the sql statement which we fed into the hidden field in the last page

sql1 = Request("InpSQL")
 
'If you aren't happy with doing that repeat your sql statement here

'sql1 = "Select * from tblMYTABLE WHERE ID < 10000"
 
'The assumption here is that if we saw data in the last page the data exists so there is no need to

'test again for errors so we just go for the data
 
RS1.Open sql1, Conn 
 
'This is the the code which tells the page to open Excel and give it the data to display

Response.ContentType = "application/vnd.ms-excel"

'You can give the spreadsheet a name at the point its produced

Response.AddHeader "Content-Disposition", "attachment; filename=MYSPREADSHEETNAME.xls" 

%>
 
<!-- 

Note that I have formatted the output header here to a dark blue background and white text

this will be reflected in the spreadsheet when its produced and you could extend this to your own tastes of course.

-->
 
<table border="1" width="100%">

<tr>

<td bgcolor="#000080"><font color="#FFFFFF">Field1Header</font></td>

<td bgcolor="#000080"><font color="#FFFFFF">Field2Header</font></td>

<td bgcolor="#000080"><font color="#FFFFFF">Field3Header</font></td>

<td bgcolor="#000080"><font color="#FFFFFF">Field4Header</font></td>

<td bgcolor="#000080"><font color="#FFFFFF">Field5Header</font></td>

<td bgcolor="#000080"><font color="#FFFFFF">Field6Header</font></td>

<td bgcolor="#000080"><font color="#FFFFFF">Field7Header</font></td>

</tr>

<%Do While Not RS1.eof%>

<tr>

<td><%=RS1("Field1Result")%></td>

<td><%=RS1("Field2Result")%></td>

<td><%=RS1("Field3Result")%></td>

<td><%=RS1("Field4Result")%></td>

<td><%=RS1("Field5Result")%></td>

<td><%=RS1("Field6Result")%></td>

<td><%=RS1("Field7Result")%></td>

</tr>

<%

RS1.Movenext

Loop

%>

</table>

<%

RS1.Close

Conn.Close
 
Set Conn = Nothing

Set RS1 = Nothing

%>

That's how its done. A practical and easy way to make your site more inter-active.

movieking81 · #2 October 18th, 2007, 09:21 PM

Great, maybe you can answer a question for me. How do you default the gridlines in Excel to "on" when you export. This has been driving me nuts. I think you can do it with .NET, but I'm using classic ASP at the moment.

Thanks

Dean

jhardman · #3 October 18th, 2007, 09:54 PM

Dean,

It appears that Merlin is just using the HTML equivalent for producing the XLS output. So I would use the HTML border attribute or possibly an appropriate style attribute since those are the closest HTML equivalent to what you are asking. If you try it, let me know if it works.

Jared

movieking81 · #4 October 19th, 2007, 03:37 PM

Yeah, I've used the border attributes in the past, it just bugs me that every time I export data to an Excel sheet the default grid lines are turned off. It's not a big deal, it's just turning the grid lines on is quicker than formatting the exported data with a border. Also some of my users expect grid lines and it's always a 10 min discussion about why they have to turn them on. I don't know why there isn't a function/property for that, maybe...

workbookname.gridlines = on

How hard would that have been. Something that Excel could read when it opens the document. Sorry, now I'm just ranting.

Thanks

Dean

srkidd12 · #5 May 2nd, 2008, 03:57 PM

This example works great for me, but do you know how to have the excel file automatically attach it to an email being sent out by a CDO message?

DrBunchman · #6 June 11th, 2008, 04:50 PM

Hi srkidd12,

You would do this in a slightly different way - instead of setting a page's content type to Excel you would need to create the excel file on your server, write the content to it and save it. You could then attach that file to an e-mail.

Hope this helps,

Dr B

aashishn86 · #7 March 5th, 2009, 12:10 PM

that was a real helpful article....
just wanted to know , how can i create more than one worksheet in the same workbook ??

How To Export Your Displayed Data To Excel

re: How To Export Your Displayed Data To Excel

re: How To Export Your Displayed Data To Excel

re: How To Export Your Displayed Data To Excel

re: How To Export Your Displayed Data To Excel

re: How To Export Your Displayed Data To Excel

re: How To Export Your Displayed Data To Excel

/bytes/development

/bytes/it

/bytes/about