944,030 Members | Top Members by Rank

Web Development > ASP > Exporting ASP pages to Excel
Ad:
  • ASP Discussion Thread
  • Unsolved
  • Views: 26927
  • ASP RSS
Permalink
Mar 30th, 2006
0

Exporting ASP pages to Excel

Expand Post »
Hi, I'm a web developer for a small company, I had programmed using ASP about six years ago, thus my skills are very rusty at the moment...recently, my boss had asked me to export the database information from the ASP pages to an Microsoft Excel file, allowing the users to save and type in the file name, if possible, can anyone offer any guidance, directions or tips in doing so...I will be greatly appreciated...thanks...
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
SeekAnswers is offline Offline
20 posts
since Mar 2006
Permalink
Apr 2nd, 2006
0

Re: Exporting ASP pages to Excel

Hi,

There is a tricky way for solving your problem. If you create a HTML table which holds all the data you want to export as an excel file by writing an ASP page, and if you put the proper headers to your ASP page, your HTML table can be downloaded as an Excel file. And this excel file is nicely handled by MS Excel.

You can try the code below:

ASP Syntax (Toggle Plain Text)
  1.  
  2. <%
  3. Response.ContentType = "application/vnd.ms-excel"
  4. Response.AddHeader "Content-Disposition", "filename=excelfile.xls"
  5. %>
  6. <table>
  7. <tr>
  8. <td>Category Name</td>
  9. <td>Category Description</td>
  10. </tr>
  11. <tr>
  12. <td>Software</td>
  13. <td>Holds data for software</td>
  14. </tr>
  15. <tr>
  16. <td>Hardware</td>
  17. <td>Hardware related data</td>
  18. </tr>
  19. </table>
Reputation Points: 10
Solved Threads: 0
Newbie Poster
delizeka is offline Offline
10 posts
since Apr 2006
Permalink
Apr 4th, 2006
0

Re: Exporting ASP pages to Excel

Thanks for your help, but I need to export the database data that is being displayed on an ASP page, to an Excel file, preferably if the filename can be entered by the user, any idea how I can do that?

Is there a predefined function or method in ASP that we can make use of?

Thanks for your time, and I will look forward to your reply...


Quote originally posted by delizeka ...
Hi,

There is a tricky way for solving your problem. If you create a HTML table which holds all the data you want to export as an excel file by writing an ASP page, and if you put the proper headers to your ASP page, your HTML table can be downloaded as an Excel file. And this excel file is nicely handled by MS Excel.

You can try the code below:

ASP Syntax (Toggle Plain Text)
  1.  
  2. <%
  3. Response.ContentType = "application/vnd.ms-excel"
  4. Response.AddHeader "Content-Disposition", "filename=excelfile.xls"
  5. %>
  6. <table>
  7. <tr>
  8. <td>Category Name</td>
  9. <td>Category Description</td>
  10. </tr>
  11. <tr>
  12. <td>Software</td>
  13. <td>Holds data for software</td>
  14. </tr>
  15. <tr>
  16. <td>Hardware</td>
  17. <td>Hardware related data</td>
  18. </tr>
  19. </table>
Reputation Points: 10
Solved Threads: 0
Newbie Poster
SeekAnswers is offline Offline
20 posts
since Mar 2006
Permalink
Apr 5th, 2006
0

Re: Exporting ASP pages to Excel

If I don't understand wrong, you can do it as I explained. For example; Let me say I have a link says "Download all data as an Excel file" in my web site. It should go to an asp page when it is clicked, for example ; datatoexcel.asp.

You can create datatoexcel.asp such that at the top of the page "Response.ContentType = "application/vnd.ms-excel" is placed. Then you can dynamically get your data from your database as dynamically created tables into your asp file. So when "Download all data as an Excel file" link is clicked, your datatoexcel.asp file tell client's browser that an excel file is coming. So browser want to download file and let user to save file.

You can take look at this link : http://www.codetoad.com/asp_excel.asp

There is another way to create an excel file. In this method you create an office document object on the server side. But this requires that necessary component must be installed on the web server.

You can read this article about this method : http://www.4guysfromrolla.com/webtech/022801-1.shtml
Reputation Points: 10
Solved Threads: 0
Newbie Poster
delizeka is offline Offline
10 posts
since Apr 2006
Permalink
Apr 5th, 2006
0

Re: Exporting ASP pages to Excel

Thanks for your tip, I managed to get my ASP page working after spending some time on debugging it, I guess I'm really rusty afterall...

By the way, do you know how I can upload images to the web server, using ASP page, i.e. using some simple textboxes for the user to enter the path for the image etc?

Thanks for your aid in the previous problem, you are a life-saver....


Quote originally posted by delizeka ...
If I don't understand wrong, you can do it as I explained. For example; Let me say I have a link says "Download all data as an Excel file" in my web site. It should go to an asp page when it is clicked, for example ; datatoexcel.asp.

You can create datatoexcel.asp such that at the top of the page "Response.ContentType = "application/vnd.ms-excel" is placed. Then you can dynamically get your data from your database as dynamically created tables into your asp file. So when "Download all data as an Excel file" link is clicked, your datatoexcel.asp file tell client's browser that an excel file is coming. So browser want to download file and let user to save file.

You can take look at this link : http://www.codetoad.com/asp_excel.asp

There is another way to create an excel file. In this method you create an office document object on the server side. But this requires that necessary component must be installed on the web server.

You can read this article about this method : http://www.4guysfromrolla.com/webtech/022801-1.shtml
Reputation Points: 10
Solved Threads: 0
Newbie Poster
SeekAnswers is offline Offline
20 posts
since Mar 2006
Permalink
Jan 14th, 2009
0

Re: Exporting ASP pages to Excel

Hi!

I followed the steps and I am getting page cannot be displayed message. Could you help me what's the problem with the following code:

asp Syntax (Toggle Plain Text)
  1. <%@LANGUAGE="JAVASCRIPT"%>
  2. <!--#include file="Integra.asp" -->
  3. <%
  4. var Reviews_cmd = Server.CreateObject ("ADODB.Command");
  5. Reviews_cmd.ActiveConnection = MM_Integra_STRING;
  6. Reviews_cmd.CommandText = "SELECT * FROM dbo.Test";
  7. Reviews_cmd.Prepared = true;
  8.  
  9. var Reviews = Reviews_cmd.Execute();
  10. var Reviews_numRows = 0;
  11. %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  12. <html xmlns="http://www.w3.org/1999/xhtml">
  13. <head>
  14. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  15. <title>Untitled Document</title>
  16. <style type="text/css">
  17. <!--
  18. .style1 {color: #000000}
  19. -->
  20. </style>
  21. <%
  22. Response.ContentType = "application/vnd.ms-excel"
  23. Response.AddHeader "Content-Disposition", "filename=Globe_CHE_DataCapture.xls"
  24. %>
  25. </head>
  26.  
  27.  
  28. <body>
  29.  
  30. <table>
  31. <tr>
  32. <td><span class="style1">Category Name</span></td>
  33. <td><span class="style1">Category Description</span></td>
  34. </tr>
  35. <tr>
  36. <td><span class="style1">Software</span></td>
  37. <td><span class="style1">Holds <strong class="highlight">data</strong> for software</span></td>
  38. </tr>
  39. <tr>
  40. <td><span class="style1">Hardware</span></td>
  41. <td><span class="style1">Hardware related data</span></td>
  42. </tr>
  43. </table>
  44. <%=(Reviews.Fields.Item("EmpID").Value)%>
  45. </body>
  46. </html>
  47. <%
  48. Reviews.Close();
  49. %>




Any suggestions? thank you
Last edited by peter_budo; Jan 14th, 2009 at 7:23 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
artbishop is offline Offline
1 posts
since Jan 2009
Permalink
Feb 2nd, 2009
0

Re: Exporting ASP pages to Excel

I use VBscript:

ASP Syntax (Toggle Plain Text)
  1. <script language="VBScript" type="text/vbscript" >
  2.     Sub ToExcel
  3.         strCopy = MyTable.InnerHTML
  4.         document.parentwindow.clipboardData.SetData "text", strCopy
  5.  
  6.         Set objExcel = CreateObject("Excel.Application")
  7.         objExcel.Visible = True
  8.  
  9.         Set objWorkbook = objExcel.Workbooks.Add()
  10.         Set objWorksheet = objWorkbook.Worksheets(1)
  11.         objWorksheet.Paste
  12.         objWorksheet.Rows(1).delete
  13.         objWorksheet.Cells.ColumnWidth = 20
  14.         objWorksheet.Cells.EntireColumn.AutoFit
  15.         objWorksheet.Cells.AutoFilter
  16.         objWorksheet.Cells.EntireRow.AutoFit
  17.         objWorksheet.Cells(1, 1).Select
  18.         Hyperlinks.Delete
  19.  
  20.         For i=1 to 1000 
  21.             objWorksheet.Cells(1, i).Select
  22.             Selection.Hyperlinks.Delete
  23.             objWorksheet.Range(4, i).Select
  24.             Selection.Hyperlinks.Delete
  25.         Next         
  26.  
  27.     End Sub
  28. </script>

which works, but I would like to do macro-like activities once Excel is opened which doesn't seem to work like selecting cells or ranges, etc. Not sure why this is so, because some people seem to think this works. For example, my table has hyperlinks which I'd like removed within Excel, but selecting the cells to delete the hyperlink doesn't work.

Best of luck,
Dean.
Last edited by peter_budo; Feb 4th, 2009 at 2:20 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
sesame is offline Offline
3 posts
since Feb 2009
Permalink
Feb 2nd, 2009
0

Re: Exporting ASP pages to Excel

I use VBscript:

ASP Syntax (Toggle Plain Text)
  1. <script language="VBScript" type="text/vbscript" >
  2.     Sub ToExcel
  3.         strCopy = MyTable.InnerHTML
  4.         document.parentwindow.clipboardData.SetData "text", strCopy
  5.  
  6.         Set objExcel = CreateObject("Excel.Application")
  7.         objExcel.Visible = True
  8.  
  9.         Set objWorkbook = objExcel.Workbooks.Add()
  10.         Set objWorksheet = objWorkbook.Worksheets(1)
  11.         objWorksheet.Paste
  12.         objWorksheet.Rows(1).delete
  13.         objWorksheet.Cells.ColumnWidth = 20
  14.         objWorksheet.Cells.EntireColumn.AutoFit
  15.         objWorksheet.Cells.AutoFilter
  16.         objWorksheet.Cells.EntireRow.AutoFit
  17.         objWorksheet.Cells(1, 1).Select
  18.         Hyperlinks.Delete
  19.  
  20.         For i=1 to 1000 
  21.             objWorksheet.Cells(1, i).Select
  22.             Selection.Hyperlinks.Delete
  23.             objWorksheet.Range(4, i).Select
  24.             Selection.Hyperlinks.Delete
  25.         Next         
  26.  
  27.     End Sub
  28. </script>

which works, but I would like to do macro-like activities once Excel is opened which doesn't seem to work like selecting cells or ranges, etc. Not sure why this is so, because some people seem to think this works. For example, my table has hyperlinks which I'd like removed within Excel, but selecting the cells to delete the hyperlink doesn't work.

Best of luck,
Dean.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
sesame is offline Offline
3 posts
since Feb 2009
Permalink
Feb 6th, 2011
0
Re: Exporting ASP pages to Excel
Click to Expand / Collapse  Quote originally posted by delizeka ...
Hi,

There is a tricky way for solving your problem. If you create a HTML table which holds all the data you want to export as an excel file by writing an ASP page, and if you put the proper headers to your ASP page, your HTML table can be downloaded as an Excel file. And this excel file is nicely handled by MS Excel.

You can try the code below:

ASP Syntax (Toggle Plain Text)
  1.  
  2. <%
  3. Response.ContentType = "application/vnd.ms-excel"
  4. Response.AddHeader "Content-Disposition", "filename=excelfile.xls"
  5. %>
  6. <table>
  7. <tr>
  8. <td>Category Name</td>
  9. <td>Category Description</td>
  10. </tr>
  11. <tr>
  12. <td>Software</td>
  13. <td>Holds data for software</td>
  14. </tr>
  15. <tr>
  16. <td>Hardware</td>
  17. <td>Hardware related data</td>
  18. </tr>
  19. </table>
Thank you. This solved my problem.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
ronhymes is offline Offline
1 posts
since Feb 2011
Permalink
28 Days Ago
0

Convert Excel to Web

Thanks for the post. Here’s a post which shows how to convert your excel to web in minutes http://blog.caspio.com/integration/c...-excel-to-web/
Last edited by cameron.reid97; 28 Days Ago at 2:02 pm.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
cameron.reid97 is offline Offline
1 posts
since Jan 2012
Message:
Previous Thread in ASP Forum Timeline: search form with dropdown list
Next Thread in ASP Forum Timeline: Login and display user information from access database





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC