Exporting ASP pages to Excel

[SeekAnswers]

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...

[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:

<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "filename=excelfile.xls"
%>
<table>
<tr>
<td>Category Name</td>
<td>Category Description</td>
</tr>
<tr>
<td>Software</td>
<td>Holds data for software</td>
</tr>
<tr>
<td>Hardware</td>
<td>Hardware related data</td>
</tr>
</table>

[SeekAnswers]

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...

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:

<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "filename=excelfile.xls"
%>
<table>
<tr>
<td>Category Name</td>
<td>Category Description</td>
</tr>
<tr>
<td>Software</td>
<td>Holds data for software</td>
</tr>
<tr>
<td>Hardware</td>
<td>Hardware related data</td>
</tr>
</table>

[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

[SeekAnswers]

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....

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

[artbishop]

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:

<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Integra.asp" -->
<%
var Reviews_cmd = Server.CreateObject ("ADODB.Command");
Reviews_cmd.ActiveConnection = MM_Integra_STRING;
Reviews_cmd.CommandText = "SELECT * FROM dbo.Test";
Reviews_cmd.Prepared = true;

var Reviews = Reviews_cmd.Execute();
var Reviews_numRows = 0;
%><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {color: #000000}
-->
</style>
<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "filename=Globe_CHE_DataCapture.xls"
%>
</head>

 
<body>

<table>
<tr>
<td><span class="style1">Category Name</span></td>
<td><span class="style1">Category Description</span></td>
</tr>
<tr>
<td><span class="style1">Software</span></td>
<td><span class="style1">Holds <strong class="highlight">data</strong> for software</span></td>
</tr>
<tr>
<td><span class="style1">Hardware</span></td>
<td><span class="style1">Hardware related data</span></td>
</tr>
</table>
<%=(Reviews.Fields.Item("EmpID").Value)%>
</body>
</html>
<%
Reviews.Close();
%>

Any suggestions? thank you

[sesame]

I use VBscript:

<script language="VBScript" type="text/vbscript" >
    Sub ToExcel
        strCopy = MyTable.InnerHTML
        document.parentwindow.clipboardData.SetData "text", strCopy

        Set objExcel = CreateObject("Excel.Application")
        objExcel.Visible = True
    
        Set objWorkbook = objExcel.Workbooks.Add()
        Set objWorksheet = objWorkbook.Worksheets(1)
        objWorksheet.Paste
        objWorksheet.Rows(1).delete
        objWorksheet.Cells.ColumnWidth = 20
        objWorksheet.Cells.EntireColumn.AutoFit
        objWorksheet.Cells.AutoFilter
        objWorksheet.Cells.EntireRow.AutoFit
        objWorksheet.Cells(1, 1).Select
        Hyperlinks.Delete
        
        For i=1 to 1000 
            objWorksheet.Cells(1, i).Select
            Selection.Hyperlinks.Delete
            objWorksheet.Range(4, i).Select
            Selection.Hyperlinks.Delete
        Next         
        
    End Sub
</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.

[sesame]

I use VBscript:

<script language="VBScript" type="text/vbscript" >
    Sub ToExcel
        strCopy = MyTable.InnerHTML
        document.parentwindow.clipboardData.SetData "text", strCopy

        Set objExcel = CreateObject("Excel.Application")
        objExcel.Visible = True
    
        Set objWorkbook = objExcel.Workbooks.Add()
        Set objWorksheet = objWorkbook.Worksheets(1)
        objWorksheet.Paste
        objWorksheet.Rows(1).delete
        objWorksheet.Cells.ColumnWidth = 20
        objWorksheet.Cells.EntireColumn.AutoFit
        objWorksheet.Cells.AutoFilter
        objWorksheet.Cells.EntireRow.AutoFit
        objWorksheet.Cells(1, 1).Select
        Hyperlinks.Delete
        
        For i=1 to 1000 
            objWorksheet.Cells(1, i).Select
            Selection.Hyperlinks.Delete
            objWorksheet.Range(4, i).Select
            Selection.Hyperlinks.Delete
        Next         
        
    End Sub
</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.

[ronhymes]

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:

<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "filename=excelfile.xls"
%>
<table>
<tr>
<td>Category Name</td>
<td>Category Description</td>
</tr>
<tr>
<td>Software</td>
<td>Holds data for software</td>
</tr>
<tr>
<td>Hardware</td>
<td>Hardware related data</td>
</tr>
</table>

Thank you. This solved my problem.

[cameron.reid97]

Thanks for the post. Here’s a post which shows how to convert your excel to web in minutes http://blog.caspio.com/integration/convert-ms-excel-to-web/

[tughralkhan]

<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=excelfile.xls"
%>