Solved

ASP to Excel

Posted on 2002-03-04
7
1,989 Views
Last Modified: 2012-08-13
I have an HTML table that I want the user to be able to save to Excel.  I have tried diffence method but still not work.

My first method is:

<%@ language=vbscript %>
<%
  response.buffer = true
  response.ContentType = "application/vnd.ms-excel"
  response.AddHeader "content-disposition", "inline; filename=dynamic.xls"

  response.write "<table width=200>"
  response.write "<tr>"
  for i = 1 to 4
    response.write "<td width=40>"
    response.write i + i
    response.write "</td>"
  next
  response.write "<td width=40><b>=sum(A1:D1)</b></td>"
  response.write "</tr>"
  response.write "</table>"

  response.flush
  response.end

%>

But the result showed in browser is in html format not in excel:
2 4 6 8 =sum(A1:D1)

So, I try the other method and run in a computer:

<%
Dim ExcelSheet

Set ExcelSheet = CreateObject("Excel.Sheet")

' Make Excel visible through the Application object.

ExcelSheet.Application.Visible = true
' Place some text in the first cell of the sheet.
ExcelSheet.ActiveSheet.Cells(1,1).Value = "This is column A, row 1"
' Save the sheet.
ExcelSheet.SaveAs Server.Mappath("TEST.XLS")
' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing
%>

No Excel file is generated in the application path and an error is occured

error '80010001'
Call was rejected by callee
/excel.asp, line 4

What's wrong with my coding and my web server? What do I need to do in order to fix the problem?

0
Comment
Question by:leo_wh_cheung
7 Comments
 
LVL 1

Accepted Solution

by:
loveneesh_bansal earned 50 total points
Comment Utility
hi
try this
<%
Response.ContentType = "application/vnd.ms-excel"
%>

This informs the browser that the code to follow is Excel formatted, and Netscape or IE will prompt the user to Save or Open the file. When they Open the file, Excel is launched and the report is viewed by Excel. In order for Excel to understand your data, you need only to create an HTML table, which Excel 97 will then convert into its own format. NOTE: This must be the first line of code on the page! (Actually, it just has to be before any other header or HTML info is output to the browser, but put it at the top and it won't cause you problems)



<%
2    Response.ContentType = "application/vnd.ms-excel"
3    
4    set conntemp=server.createobject("adodb.connection")
5    cnpath="DBQ=" & server.mappath("/stevesmith/data/timesheet.mdb")
6    conntemp.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & cnpath
7    set RS=conntemp.execute("select * from donut")
8    %>
9     <TABLE BORDER=1>
10    <TR>
11    <%
12   ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
13   ' % Loop through Fields Names and print out the Field Names
14   ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
15    j = 2 'row counter
16    For i = 0 to RS.Fields.Count - 1
17    %>
18    <TD><B><% = RS(i).Name %></B></TD>
19    <% Next %>
20    <TD><B>On Hand (calculated)</B></TD>
21    <TD><B>Gross (calculated)</B></TD>
22    </TR>
23    <%
24   ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
25   ' % Loop through rows, displaying each field
26   ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
27    Do While Not RS.EOF
28    %>
29    <TR>
30    <% For i = 0 to RS.Fields.Count - 1
31    %>
32    <TD VALIGN=TOP><% = RS(i) %></TD>
33    <% Next %>
34    <TD>=b<%=j%>-c<%=j%>-d<%=j%></TD>
35    <TD>=d<%=j%>*e<%=j%></TD>
36    </TR>
37    <%
38    RS.MoveNext
39    j = j + 1
40    Loop
41   ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
42   ' % Make sure to close the Result Set and the Connection object
43   ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
44    RS.Close
45    %>
46    <TR BGCOLOR=RED>
47    <TD>Totals</TD>
48    <TD>=SUM(B2:B6)</TD>
49    <TD>=SUM(C2:C6)</TD>
50    <TD>=SUM(D2:D6)</TD>
51    <TD>n/a</TD>
52    <TD>=SUM(F2:F6)</TD>
53    <TD>=SUM(G2:G6)</TD>


i hope this will work

With Regards

Loveneesh
0
 
LVL 8

Expert Comment

by:drittich
Comment Utility
In your first example change

 response.write "<td width=40><b>=sum(A1:D1)</b></td>"

to

 response.write "<td width=40>=sum(A1:D1)</td>"
0
 

Author Comment

by:leo_wh_cheung
Comment Utility
loveneesh_bansal,

The output of your program is in HTML Format too. No excel file is generated.

Actaully, is there something wrong with the setting of my web server?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:leo_wh_cheung
Comment Utility
loveneesh_bansal,

The output of your program is in HTML Format too. No excel file is generated.

Actaully, is there something wrong with the setting of my web server?
0
 
LVL 18

Expert Comment

by:bruno
Comment Utility
http://msdn.microsoft.com/library/default.asp?URL=/library/en-us/dn_voices_webmen/html/webteam07032000.asp


scroll down until the "turning the tables" article....


are you sure you have excel on your computer???
0
 

Author Comment

by:leo_wh_cheung
Comment Utility
loveneesh_bansal,

I use another computer to run the following coding and it can generate an excel file in the browser. Thanks for your advice.

<% response.ContentType = "application/vnd.ms-excel"
 response.AddHeader "content-disposition", "inline; filename=dynamic.xls"

 response.write "<table width=200>"
 response.write "<tr>"
 for i = 1 to 4
   response.write "<td width=40>"
   response.write i + i
   response.write "</td>"
 next
 response.write "<td width=40><b>=sum(A1:D1)</b></td>"
 response.write "</tr>"
 response.write "</table>"

 response.flush
 response.end

%>

Leo
0
 
LVL 1

Expert Comment

by:loveneesh_bansal
Comment Utility
hi leo

thanks for accepting the answer.

loveneesh
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now