tburke1234567
asked on
Asp to excel problem
I am trying to open a set of results from a record set generated thru asp in excel. I am testing this on a local server and it works fine for me. But when i goto the web server it opens the file and prompts me to save in as an asp file, or opens the file in dreamweaver as a comma delimtied file....here is the header for my page i use
Response.ContentType = "application/download"
Response.Addheader "Content-Disposition", "inline; filename=accounts.csv"
Is there a setting in iis that i need to set or something else...i have tried various extension to the content type setting and still doesnt work. Also some of my colleagues have had no problem opening this file while some others can open it in excel but it is just one big line of comma delimted text!!any ideas
Response.ContentType = "application/download"
Response.Addheader "Content-Disposition", "inline; filename=accounts.csv"
Is there a setting in iis that i need to set or something else...i have tried various extension to the content type setting and still doesnt work. Also some of my colleagues have had no problem opening this file while some others can open it in excel but it is just one big line of comma delimted text!!any ideas
ASKER
Thanks but im actually using asp so im still stumped!!!
try this :
Response.ContentType = "application/vnd.ms-excel"
Response.Addheader "Content-Disposition", "inline; filename=accounts.csv"
Response.ContentType = "application/vnd.ms-excel"
Response.Addheader "Content-Disposition", "inline; filename=accounts.csv"
one more thing.. make sure you are not having any reference or including any style sheets in this page.. anything more than simple basic html like table, tr, td etc only.
ASKER
Nope tried all of the above and alas still have the same original problem!!!
Below is a copy of my working code. Here's a thought: did the file associations for your system get messed up somehow? Maybe dreamweaver associated itself with some file types that is shouldn't have. Try opening the page on a computer that has Excel installed but not dreamweaver.
Response.ContentType = "application/vnd.ms-excel"
Response.Expires = -1500
Response.ExpiresAbsolute = Now() - 1
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
Response.CacheControl = "no-cache"
Response.Buffer = True
Dim objConn, strConn, objRS, SQL, i, djidnumber
Set objConn = Server.CreateObject("ADODB
strConn = "DSN=foo;Database=foo;UID=
objConn.Open strConn
SQL = "SELECT contact, company, region, address, city, state, zipcode, email, website, telephone, membership_origin, djidnumber, membership_number, membership_date FROM members;"
set objRS = objConn.execute(SQL)
Response.Write "<table border=1><tr>"
For i = 0 To objRS.Fields.Count - 1
Response.Write "<td><b>" & objRS(i).Name & "</B></TD>"
Next
Do While Not objRS.EOF
Response.Write "<tr>"
For i = 0 to objRS.Fields.Count - 1
Response.Write "<td>" & objRS(i) & "</td>"
Next
Response.Write "</tr>"
objRS.MoveNext
Loop
Response.Write "</tr></table>"
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
Response.ContentType = "application/vnd.ms-excel"
Response.Expires = -1500
Response.ExpiresAbsolute = Now() - 1
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
Response.CacheControl = "no-cache"
Response.Buffer = True
Dim objConn, strConn, objRS, SQL, i, djidnumber
Set objConn = Server.CreateObject("ADODB
strConn = "DSN=foo;Database=foo;UID=
objConn.Open strConn
SQL = "SELECT contact, company, region, address, city, state, zipcode, email, website, telephone, membership_origin, djidnumber, membership_number, membership_date FROM members;"
set objRS = objConn.execute(SQL)
Response.Write "<table border=1><tr>"
For i = 0 To objRS.Fields.Count - 1
Response.Write "<td><b>" & objRS(i).Name & "</B></TD>"
Next
Do While Not objRS.EOF
Response.Write "<tr>"
For i = 0 to objRS.Fields.Count - 1
Response.Write "<td>" & objRS(i) & "</td>"
Next
Response.Write "</tr>"
objRS.MoveNext
Loop
Response.Write "</tr></table>"
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
ASKER
djlurch,
very close its now opening a comma delimted file in an excel window within internet explorer, but i still have to separate in manually in excel!!!anyway i can get it to open it in excel itself instead of an excel window in ie???
very close its now opening a comma delimted file in an excel window within internet explorer, but i still have to separate in manually in excel!!!anyway i can get it to open it in excel itself instead of an excel window in ie???
try adding
Response.AddHeader "Content-Disposition", "attachment; filename=accounts.csv"
to the above given solution
Response.AddHeader "Content-Disposition", "attachment; filename=accounts.csv"
to the above given solution
ASKER
Thanks but nope still not working!!!back to opening it in dreamweaver!!and i checked the file asscociations and its fine...also tried it with :
Response.Addheader "Content-Disposition", "inline; filename=accounts.csv"
and still not working
Response.Addheader "Content-Disposition", "inline; filename=accounts.csv"
and still not working
thats real funny :-?.. if it was previously opening in excel in IE, the downloadable should also be opening in excel not in dreamweaver.. can you copy paste a more detailed code you are using ... may be looking at the code might help..
ASKER
<%
'Response.ContentType = "application/xls"
Response.ContentType = "application/vnd.ms-excel"
Response.Expires = -1500
Response.ExpiresAbsolute = Now() - 1
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
'Response.AddHeader "Content-Disposition", "attachment; filename=accounts.csv"
Response.Addheader "Content-Disposition", "inline; filename=accounts.csv"
Response.CacheControl = "no-cache"
Response.Buffer = True
Dim ExConn, EXRS
Set ExConn = Server.createObject("ADODB
ExConn.open "usual details here!!!"
set EXRS = ExConn.execute("select debtorname from ma26reports where debtor = '" & Session("DebtorCode") & "'")
if EXRS.eof = true then
tmpDebtor = ""
else
tmpDebtor = EXRS("debtorname")
end if
tmpCSV = request("txtHiddenCsv")
response.Write(tmpDebtor) & "," & date & vbcrlf
response.write "Invoice No, Invoice Date, Lead Name, Dot, Ref, City Codes, Amount, Amount O/S" & vbcrlf
response.Write(tmpCSV) & vbcrlf & vbcrlf
response.Write(",,,,,Total
%>
'Response.ContentType = "application/xls"
Response.ContentType = "application/vnd.ms-excel"
Response.Expires = -1500
Response.ExpiresAbsolute = Now() - 1
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
'Response.AddHeader "Content-Disposition", "attachment; filename=accounts.csv"
Response.Addheader "Content-Disposition", "inline; filename=accounts.csv"
Response.CacheControl = "no-cache"
Response.Buffer = True
Dim ExConn, EXRS
Set ExConn = Server.createObject("ADODB
ExConn.open "usual details here!!!"
set EXRS = ExConn.execute("select debtorname from ma26reports where debtor = '" & Session("DebtorCode") & "'")
if EXRS.eof = true then
tmpDebtor = ""
else
tmpDebtor = EXRS("debtorname")
end if
tmpCSV = request("txtHiddenCsv")
response.Write(tmpDebtor) & "," & date & vbcrlf
response.write "Invoice No, Invoice Date, Lead Name, Dot, Ref, City Codes, Amount, Amount O/S" & vbcrlf
response.Write(tmpCSV) & vbcrlf & vbcrlf
response.Write(",,,,,Total
%>
try this
<%
Response.ContentType = "application/vnd.ms-excel"
Response.Expires = -1500
Response.ExpiresAbsolute = Now() - 1
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
Response.AddHeader "Content-Disposition", "attachment; filename=accounts.csv"
Response.CacheControl = "no-cache"
Response.Buffer = True
Dim ExConn, EXRS
Set ExConn = Server.createObject("ADODB
ExConn.open "usual details here!!!"
set EXRS = ExConn.execute("select debtorname from ma26reports where debtor = '" & Session("DebtorCode") & "'")
if EXRS.eof = true then
tmpDebtor = ""
else
tmpDebtor = EXRS("debtorname")
end if
tmpCSV = request("txtHiddenCsv")
response.Write "<table><tr><td colspan=4>" & tmpDebtor & "</td><td colspan=4>" & date & "</td></tr>"
response.write "<tr><td>Invoice No</td><td>Invoice Date</td><td>Lead Name</td><td> Dot</td><td> Ref</td><td> City Codes</td><td> Amount</td><td> Amount O/S</td></tr>"
response.Write "<tr><td colspan=8>" & tmpCSV & "</td></tr>"
response.Write ""<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td>Totals</td><td>" & request("txtHiddenAmt") & "</td><td>" & request("txtHiddenBal") & "</td></tr></table>"
%>
<%
Response.ContentType = "application/vnd.ms-excel"
Response.Expires = -1500
Response.ExpiresAbsolute = Now() - 1
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
Response.AddHeader "Content-Disposition", "attachment; filename=accounts.csv"
Response.CacheControl = "no-cache"
Response.Buffer = True
Dim ExConn, EXRS
Set ExConn = Server.createObject("ADODB
ExConn.open "usual details here!!!"
set EXRS = ExConn.execute("select debtorname from ma26reports where debtor = '" & Session("DebtorCode") & "'")
if EXRS.eof = true then
tmpDebtor = ""
else
tmpDebtor = EXRS("debtorname")
end if
tmpCSV = request("txtHiddenCsv")
response.Write "<table><tr><td colspan=4>" & tmpDebtor & "</td><td colspan=4>" & date & "</td></tr>"
response.write "<tr><td>Invoice No</td><td>Invoice Date</td><td>Lead Name</td><td> Dot</td><td> Ref</td><td> City Codes</td><td> Amount</td><td> Amount O/S</td></tr>"
response.Write "<tr><td colspan=8>" & tmpCSV & "</td></tr>"
response.Write ""<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td>Totals</td><td>" & request("txtHiddenAmt") & "</td><td>" & request("txtHiddenBal") & "</td></tr></table>"
%>
If the file is foo.asp you may be stuck opening the excel file with Excel in the browser. That is probably more of a browser issue than a programming issue.
If you want to open it in Excel directly there are a few options:
1) Use a different browser (Firefox opened it with Excel natively for me)
2) Kludge Approach
a) IIS Settings -- associate *.xls extensions to be processed by ASP/VBScript Engine
b) Save the file as foo.xls
If you want to open it in Excel directly there are a few options:
1) Use a different browser (Firefox opened it with Excel natively for me)
2) Kludge Approach
a) IIS Settings -- associate *.xls extensions to be processed by ASP/VBScript Engine
b) Save the file as foo.xls
ASKER
nope tried all that and still no cigar!!!very frustrating eh???!
very furstrating, i must say.. well.. with this latest code what happens? does it open in excel or still dreamweaver? if in excel do you see the html content or a blank page? and if you choose to save the file instead of opening.. can you open that file in notepad and paste the contents of the same here.
ASKER
its still putting into dreamweaver....i need it to go straight into excel as its for people who haven a clue how to use pc's!!
1. do you get an option to save/open the file? if you do can you save the file and opening it in notepad .. copy paste the content here..
2. if you dont get this option in IE, can u try some other browser or try it from some other pc..
what i really wanna know is the content created is in correct format or not?
2. if you dont get this option in IE, can u try some other browser or try it from some other pc..
what i really wanna know is the content created is in correct format or not?
ASKER
just got it working thanks with another browser
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
zapthedingbat: Umm....I already suggested that.
So glad to provide code and the solution and still not get the points.
>2) Kludge Approach
>a) IIS Settings -- associate *.xls extensions to be processed by ASP/VBScript Engine
>b) Save the file as foo.xls
If you thought of it independently...good call :)
So glad to provide code and the solution and still not get the points.
>2) Kludge Approach
>a) IIS Settings -- associate *.xls extensions to be processed by ASP/VBScript Engine
>b) Save the file as foo.xls
If you thought of it independently...good call :)
ASKER
Sorry about that!!!!still battling with IE but think i have it sorted now anywoo!!
This is all I use to get data to open into excel
Once I call this then I perform my query and format the results in the same way as if the viewer was looking at the data in a browser
<%
'Change HTML header to specify Excel's MIME content type
Response.Buffer = TRUE
Response.ContentType = "application/vnd.ms-excel"
%>
Once I call this then I perform my query and format the results in the same way as if the viewer was looking at the data in a browser
<%
'Change HTML header to specify Excel's MIME content type
Response.Buffer = TRUE
Response.ContentType = "application/vnd.ms-excel"
%>
Ok so there is only one issue I can see. You need to have Excel loaded on the web server. The objects you are using are requiring Excel. There is only one issue with loading Excel to the server. While in all sorts of ASP books which demonstrate the use of ASP and Excel together it is a HUGE license violation. The best way to accomplish this would be to use ASP's file scripting ability and stream the data to a text file in a CSV format. Afterwards copy the new foo.txt to foo.cvs and delete the foo.txt file. If you are interested I have code that works brilliantly which I use in several applications currently.
-V.V.
-V.V.
No that is incorrect. you do not need Excel installed on the server. You do however need it installed on the local workstation. Unless you know for sure the user is going to have excel installed locally I would suggest the same as you did in using a CSV that is generic for different spreadsheet applications.
If the user does not have excel you can expect an open / save dialog instead of opening an excel session. as your local workstation does not recognize the mime type coming across from the server
if there is an association on the endusers system to handle CSV files then all you need to do is change the content type to
Response.ContentType = "text/csv" .
If there is no association then using content-disposition
Response.ContentType = "text/csv"
'Response.AddHeader "Content-Disposition", "attachment; filename=whatever.csv"
WHich will force a download of the file instaed of opening in the browser
If the user does not have excel you can expect an open / save dialog instead of opening an excel session. as your local workstation does not recognize the mime type coming across from the server
if there is an association on the endusers system to handle CSV files then all you need to do is change the content type to
Response.ContentType = "text/csv" .
If there is no association then using content-disposition
Response.ContentType = "text/csv"
'Response.AddHeader "Content-Disposition", "attachment; filename=whatever.csv"
WHich will force a download of the file instaed of opening in the browser
Response.Write "<html xmlns:x=""urn:schemas-micr
Response.Write "<head>"
Response.Write "<!--[if gte mso 9]><xml>"
Response.Write "<x:ExcelWorkbook>"
Response.Write "<x:ExcelWorksheets>"
Response.Write "<x:ExcelWorksheet>"
Response.Write "<x:Name>Notifications Captured</x:Name>"
Response.Write "<x:WorksheetOptions>"
...format to write out your spreadsheet then you must use:
<%@ Language = VBscript %>
<%Response.ContentType = "application/vnd.ms-excel"
Try that.