ASP RSS ASP RSS

Exporting ASP pages to Excel

Reply

•
•
Join Date: Mar 2006
Posts: 20
Reputation: SeekAnswers is an unknown quantity at this point 
Solved Threads: 0
SeekAnswers SeekAnswers is offline Offline
Newbie Poster

Exporting ASP pages to Excel

 
0
  #1
Mar 30th, 2006
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...
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2006
Posts: 10
Reputation: delizeka is an unknown quantity at this point 
Solved Threads: 0
delizeka's Avatar
delizeka delizeka is offline Offline
Newbie Poster

Re: Exporting ASP pages to Excel

 
0
  #2
Apr 2nd, 2006
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>
Reply With Quote Quick reply to this message  
•
•
Join Date: Mar 2006
Posts: 20
Reputation: SeekAnswers is an unknown quantity at this point 
Solved Threads: 0
SeekAnswers SeekAnswers is offline Offline
Newbie Poster

Re: Exporting ASP pages to Excel

 
0
  #3
Apr 4th, 2006
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...


•
•
•
•
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>
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2006
Posts: 10
Reputation: delizeka is an unknown quantity at this point 
Solved Threads: 0
delizeka's Avatar
delizeka delizeka is offline Offline
Newbie Poster

Re: Exporting ASP pages to Excel

 
0
  #4
Apr 5th, 2006
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Mar 2006
Posts: 20
Reputation: SeekAnswers is an unknown quantity at this point 
Solved Threads: 0
SeekAnswers SeekAnswers is offline Offline
Newbie Poster

Re: Exporting ASP pages to Excel

 
0
  #5
Apr 5th, 2006
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....


•
•
•
•
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 1
Reputation: artbishop is an unknown quantity at this point 
Solved Threads: 0
artbishop artbishop is offline Offline
Newbie Poster

Re: Exporting ASP pages to Excel

 
0
  #6
Jan 14th, 2009
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2009
Posts: 3
Reputation: sesame is an unknown quantity at this point 
Solved Threads: 0
sesame sesame is offline Offline
Newbie Poster

Re: Exporting ASP pages to Excel

 
0
  #7
Feb 2nd, 2009
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2009
Posts: 3
Reputation: sesame is an unknown quantity at this point 
Solved Threads: 0
sesame sesame is offline Offline
Newbie Poster

Re: Exporting ASP pages to Excel

 
0
  #8
Feb 2nd, 2009
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.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the ASP Forum
Thread Tools Search this Thread



archive asp asp.net aspandmssqlserver2005 aspandmssqlserver2005connection aspconnection connection database databaseconnection dreamweaver excel fso iis msmsql mssql2005 mssqlserver2005 mssqlserver2005andasp mssqlserverandasp opentextfile record searchbox selectoption single specfic sqlserver sqlserverconnection windows7
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC