How To Export Your Displayed Data To Excel

14 New Member

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.

Sep 12 '07 #1

Subscribe Reply

28880

movieking81

New Member

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

Oct 18 '07 #2

jhardman

3,406

Recognized Expert Specialist

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

Oct 18 '07 #3

movieking81

New Member

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.gr idlines = 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

Oct 19 '07 #4

srkidd12

New Member

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?

May 2 '08 #5

DrBunchman

979

Recognized Expert Contributor

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

Jun 11 '08 #6

aashishn86

New Member

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

Mar 5 '09 #7

rambow4

New Member

Please excuse my ignorance, I am very new to ASP and programming, could someone explain what is meant by "FieldResul t" on line 58, I really have no idea what to put there? I am able to open a recordset, run getRows(), store the values to a variable and display the contents, but the rs.("FieldResul t") is unknown to me. could someone provide a better example or explanation.

Example of what I can do

rs.open(sql,con n)
dim values = rs.GetRows()
response.write( values(0,0)

display Result is "Bob"

Apr 6 '15 #8

Similar topics

4754

ASP Net IE6 - export data to Excel - Page_Load twice and new session fired

by: Funbeat | last post by:

Hi everybody, I'm facing with the following problem (bug ?) : A page is calling another one (export.aspx) for exporting data to excel. The tecnhique used is to create a Excel-MIME stream for viewing it directly in the browser. (below is a sample code)

.NET Framework

5012

Export to Excel

by: Matt | last post by:

I have an ASP page that calls ASP routines that I created that execute a database query and return the results to a recordset. I then iterate through the recordset and display the data in a table. Before I iterate through the recordset I instruct the browser that the content type is Excel using the following line: (Response.ContentType =...

ASP / Active Server Pages

14749

Code to Export Multiple Tables to Excel

by: Paolo | last post by:

Friends, I need help with some code to export different tables to a single spreadsheet in Excel. My excel file is named REPORT and the spreadsheet is named CLIENTS. I do have the code to export a single table to Excel but have problems with multimple tables. Thanks.

Microsoft Access / VBA

2460

Export DataGrid to Excel

by: Bidarkota | last post by:

Hi, I have a DataGrid in which there are some images and Data are displayed and in the webform.asp page i am using some stylesheets. when i export the datagrid all the images are also exported to datagrid and it is giving an alert that following stylesheets are missing when i export it. can anyone help me about this problem. I have seen...

ASP.NET

9219

How to export image to Excel document

by: wubin_98 | last post by:

Hi, I want to export a gridview data and a image from image control to export to an Excel document. When I executed following code, GridView data was exported to Excel properly. But no image was displayed in Excel. Only a blank image icon was show inside Excel. Could any body point out what is wrong in my code or missing anything?

ASP.NET

2810

Export to Excel issue..

by: karthick | last post by:

Hi, I am exporting a Gridview to Excel and it works fine without any issues. But as one of the field holds values such as "71646E100" it gets converted to: "7.16E+104" (like a formula) in Excel. How can I prevent this from happening ?. I need to value to be in Excel as it is displayed in the Gridview (on the web page).

ASP.NET

9757

Access Tables to Excel Worksheet Export

by: smaczylo | last post by:

Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm having I'd be most appreciative. The database is already constructed, I'm just wanting to export the data to an excel file. In short, I'm hoping...

Microsoft Access / VBA

5547

export data to excel creating multiple worksheets

by: hal | last post by:

Hello all, I've been searching all day for an article or tutorial on how to get data from a SQL Server 2000 database and export the data to excel 2003 so that multiple worksheets are created, and information is displayed in these worksheets. Data will be coming from multiple tables, and I'm using C# and asp.net 2.0. User will click on an...

ASP.NET

3991

How to export data into an excel file using IReports tool?

by: mangalamonkey | last post by:

I want to export data into an excel file using iReports.I could do that but the data is displayed as an image. I need the data to be displayed as cells as in the excel sheet. plz do reply.........

Java

7398

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...

Windows Server

7805

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...

Online Marketing

7752

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...

General

5969

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...

Career Advice

4944

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...

C# / C Sharp

3449

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...

Networking - Hardware / Configuration

1878

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1013

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

701

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

General