473,320 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,320 developers and data experts.

How To Export Your Displayed Data To Excel

Merlin1857
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
  1.  
  2. <%@ Language=VBScript %>
  3. <!-- #include file="../incfiles/adovbs.inc" -->
  4. <% 
  5. 'Declare some variables
  6. dim Conn,RS1
  7. '==================CONNECTION CODE TO SQL SERVER=======================
  8. 'Create a connection object to the database
  9. Set Conn = Server.CreateObject("ADODB.Connection")
  10.  
  11. 'Create a recordset object
  12. Set RS1 = Server.CreateObject("ADODB.RecordSet")
  13.  
  14. 'Feed the connection string into a variable called strConn
  15. strConn = "Provider=SQLOLEDB;Data Source=MYSERVERNAME;Initial Catalog=MYDATABASENAME;"&_
  16. "User Id=USERNAME;Password=PASSWORD;"
  17.  
  18. 'Feed the connection string into the connection object
  19. Conn.Open strConn 
  20. '==========================CONNECTION CODE=============================
  21.  
  22. 'Create your sql statement
  23. sql1 = "Select * from tblMYTABLE WHERE ID < 10000" 
  24.  
  25. 'Obtain the data using all the work done above
  26. RS1.Open sql1, Conn 
  27. 'Test to see if we have a recordset coming back from the database and trap the error if there is no data
  28. If RS1.eof Then
  29. %>
  30. <html>
  31. <body>
  32. <table border="0" width="100%" cellspacing="0" cellpadding="2">
  33. <tr>
  34. <td>&nbsp;<font face="Arial" color="#FF0000" size="2">No Records Match Your Search</font></td>
  35. </tr>
  36. </table>
  37. <%
  38. 'If there is data carry on
  39. Else
  40. %>
  41. <!-- 
  42. We will need to fire the data we gather to the next page which produces the Excel sheet so here 
  43. we use a form to do so instead of formulating the sql statement again when we export to excel 
  44. I pass the statement above inside a hidden field called 'InpSQL' below, this makes sure that 
  45. what they see on this page is exactly what they get in Excel.
  46. -->
  47. <form method="POST" action="02extoex.asp">
  48. <table border=0 width="100%" cellspacing="0" cellpadding="2">
  49. <tr>
  50. <td>
  51. <input type="submit" value="Export To Excel Spread Sheet" name="B1" style="font-size: 8pt">&nbsp;
  52. <input type="hidden" name="InpSQL" size="63" value="<%=sql1%>"></td>
  53. </tr>
  54. </table>
  55.  
  56. </form>
  57. <!-- This area is for display to the user and shows how the data to be exported will look -->
  58. <table border="1" width="100%" cellspacing="0" cellpadding="2" bordercolor="#000000">
  59. <tr>
  60. <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field1Header</font></td>
  61. <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field2Header</font></td>
  62. <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field3Header</font></td>
  63. <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field4Header</font></td>
  64. <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field5Header</font></td>
  65. <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field6Header</font></td>
  66. <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field7Header</font></td>
  67. </tr>
  68. <%
  69. 'Whilst there are records to show keep going
  70. Do While Not RS1.eof
  71. %>
  72. <!-- 
  73. Note the use of &nbsp; in each field this makes sure the data looks OK in
  74. a bordered table if the return is null. 
  75. -->
  76. <tr>
  77. <td><font face="Arial" size="2"><%=RS1("Field1Result")%>&nbsp;</font></td>
  78. <td><font face="Arial" size="2"><%=RS1("Field2Result")%>&nbsp;</font></td>
  79. <td><font face="Arial" size="2"><%=RS1("Field3Result")%>&nbsp;</font></td>
  80. <td><font face="Arial" size="2"><%=RS1("Field4Result")%>&nbsp;</font></td>
  81. <td><font face="Arial" size="2"><%=RS1("Field5Result")%>&nbsp;</font></td>
  82. <td><font face="Arial" size="2"><%=RS1("Field6Result")%>&nbsp;</font></td>
  83. <td><font face="Arial" size="2"><%=RS1("Field7Result")%>&nbsp;</font></td>
  84. </tr>
  85. <%
  86. 'Loop through each record and write it to the screen
  87. RS1.Movenext
  88. Loop
  89. End If
  90. %>
  91. </table>
  92. </body>
  93. </html>
  94.  
  95. <%
  96. 'Clean up and close the connections and recordset objects
  97. RS1.Close
  98. Conn.Close
  99.  
  100. Set Conn = Nothing
  101. Set RS1 = Nothing
  102. %>
  103.  
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
  1.  
  2.  
  3. <%@ Language=VBScript %>
  4. <!-- #include file="../incfiles/adovbs.inc" -->
  5. <% 
  6. 'Declare some variables
  7. dim Conn,RS1
  8.  
  9. '==================CONNECTION CODE TO SQL SERVER=======================
  10. 'Create a connection object to the database
  11. Set Conn = Server.CreateObject("ADODB.Connection")
  12.  
  13. 'Create a recordset object
  14. Set RS1 = Server.CreateObject("ADODB.RecordSet")
  15.  
  16. 'Feed the connection string into a variable called strConn
  17. strConn = "Provider=SQLOLEDB;Data Source=MYSERVERNAME;Initial Catalog=MYDATABASENAME;"&_
  18. "User Id=USERNAME;Password=PASSWORD;"
  19.  
  20. 'Feed the connection string into the connection object
  21. Conn.Open strConn 
  22. '==========================CONNECTION CODE=============================
  23.  
  24. 'Obtain the sql statement which we fed into the hidden field in the last page
  25. sql1 = Request("InpSQL")
  26.  
  27. 'If you aren't happy with doing that repeat your sql statement here
  28. 'sql1 = "Select * from tblMYTABLE WHERE ID < 10000"
  29.  
  30. 'The assumption here is that if we saw data in the last page the data exists so there is no need to
  31. 'test again for errors so we just go for the data
  32.  
  33. RS1.Open sql1, Conn 
  34.  
  35. 'This is the the code which tells the page to open Excel and give it the data to display
  36. Response.ContentType = "application/vnd.ms-excel"
  37. 'You can give the spreadsheet a name at the point its produced
  38. Response.AddHeader "Content-Disposition", "attachment; filename=MYSPREADSHEETNAME.xls" 
  39. %>
  40.  
  41. <!-- 
  42. Note that I have formatted the output header here to a dark blue background and white text
  43. this will be reflected in the spreadsheet when its produced and you could extend this to your own tastes of course.
  44. -->
  45.  
  46. <table border="1" width="100%">
  47. <tr>
  48. <td bgcolor="#000080"><font color="#FFFFFF">Field1Header</font></td>
  49. <td bgcolor="#000080"><font color="#FFFFFF">Field2Header</font></td>
  50. <td bgcolor="#000080"><font color="#FFFFFF">Field3Header</font></td>
  51. <td bgcolor="#000080"><font color="#FFFFFF">Field4Header</font></td>
  52. <td bgcolor="#000080"><font color="#FFFFFF">Field5Header</font></td>
  53. <td bgcolor="#000080"><font color="#FFFFFF">Field6Header</font></td>
  54. <td bgcolor="#000080"><font color="#FFFFFF">Field7Header</font></td>
  55. </tr>
  56. <%Do While Not RS1.eof%>
  57. <tr>
  58. <td><%=RS1("Field1Result")%></td>
  59. <td><%=RS1("Field2Result")%></td>
  60. <td><%=RS1("Field3Result")%></td>
  61. <td><%=RS1("Field4Result")%></td>
  62. <td><%=RS1("Field5Result")%></td>
  63. <td><%=RS1("Field6Result")%></td>
  64. <td><%=RS1("Field7Result")%></td>
  65. </tr>
  66. <%
  67. RS1.Movenext
  68. Loop
  69. %>
  70. </table>
  71. <%
  72. RS1.Close
  73. Conn.Close
  74.  
  75. Set Conn = Nothing
  76. Set RS1 = Nothing
  77. %>
  78.  
That's how its done. A practical and easy way to make your site more inter-active.
Sep 12 '07 #1
7 28835
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 Expert 2GB
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
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
Oct 19 '07 #4
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 Expert 512MB
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
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
Please excuse my ignorance, I am very new to ASP and programming, could someone explain what is meant by "FieldResult" 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.("FieldResult") is unknown to me. could someone provide a better example or explanation.


Example of what I can do

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

display Result is "Bob"
Apr 6 '15 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

0
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...
1
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....
4
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...
2
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...
2
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...
5
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...
1
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...
2
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,...
2
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.........
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.