By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,611 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How To Export Your Displayed Data To Excel

Merlin1857
P: 14
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
Share this Article
Share on Google+
7 Comments


P: 52
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
Expert 2.5K+
P: 3,405
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

P: 52
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

P: 14
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
Expert 100+
P: 979
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

P: 52
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

P: 1
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