ASP to Excel

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?

leo_wh_cheungAsked:
Who is Participating?
 
loveneesh_bansalConnect With a Mentor Commented:
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
 
drittichCommented:
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
 
leo_wh_cheungAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
leo_wh_cheungAuthor Commented:
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
 
brunoCommented:
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
 
leo_wh_cheungAuthor Commented:
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
 
loveneesh_bansalCommented:
hi leo

thanks for accepting the answer.

loveneesh
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.