HarryCool
asked on
ASP to Excel
To export data in Excel from an ASP page I tried both the below mentioned menthods without success.
1st Method:
<%@ 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
response.write "</tr>"
response.write "</table>"
response.flush
response.end
%>
and the table is created in HTML page itself.
I also tried CreateObject code :-
<%
dim oExcel
oExcel = createobject("Excel.Sheet"
oExcel.Worksheets(1).Range
oExcel.saveAs("c:\a.xls\")
%>
I am getting this error -
Microsoft VBScript runtime error '800a0046'
Permission denied: 'CreateObject'
/test.asp, line 12
Please help !
1st Method:
<%@ 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
response.write "</tr>"
response.write "</table>"
response.flush
response.end
%>
and the table is created in HTML page itself.
I also tried CreateObject code :-
<%
dim oExcel
oExcel = createobject("Excel.Sheet"
oExcel.Worksheets(1).Range
oExcel.saveAs("c:\a.xls\")
%>
I am getting this error -
Microsoft VBScript runtime error '800a0046'
Permission denied: 'CreateObject'
/test.asp, line 12
Please help !
keystrokes
How about make it: Server.CreateObject ?
ASKER
Gives the same result.
you have to check your rights.
1) Run DCOMCNFG.EXE
2) In the Application tab select Microsoft Excel Application and then click
the Properties button to configure the application.
3) In the Properties dialog you can configure your permissions for the
Microsoft Excel Application via the Security tab.
Typically, you can solve many of your ASP Server.CreateObject problems by
changing the launch permission which can be set on the Identity tab. I
changed mine from "The launching user" to "The interactive user" and my
problems creating a Microsoft Execl document went away.
1) Run DCOMCNFG.EXE
2) In the Application tab select Microsoft Excel Application and then click
the Properties button to configure the application.
3) In the Properties dialog you can configure your permissions for the
Microsoft Excel Application via the Security tab.
Typically, you can solve many of your ASP Server.CreateObject problems by
changing the launch permission which can be set on the Identity tab. I
changed mine from "The launching user" to "The interactive user" and my
problems creating a Microsoft Execl document went away.
Hi HarryCool,
I'm not clear on what you are trying to achieve:
1. Using an Excel file as a source, display data in ASP -- OR --
2. Allow the end-user to save data in ASP as an Excel file (client-side). -- OR --
3. Save data in ASP to an Excel file on the server (server-side)
If it's #1, then you can access the Excel file from ASP using the Excel ODBC driver. All the details are at:
http://www.asp101.com/samples/xl_data.asp
This provides detailed explanation, samples, and add'l references.
If it's #2, then you can use an Excel Web query:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office06012000.asp
If it's #3, then it will depend upon the actual data source that the ASP is getting the data from. Please provide more details.
HTH.
Best Regards,
Jim
I'm not clear on what you are trying to achieve:
1. Using an Excel file as a source, display data in ASP -- OR --
2. Allow the end-user to save data in ASP as an Excel file (client-side). -- OR --
3. Save data in ASP to an Excel file on the server (server-side)
If it's #1, then you can access the Excel file from ASP using the Excel ODBC driver. All the details are at:
http://www.asp101.com/samples/xl_data.asp
This provides detailed explanation, samples, and add'l references.
If it's #2, then you can use an Excel Web query:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office06012000.asp
If it's #3, then it will depend upon the actual data source that the ASP is getting the data from. Please provide more details.
HTH.
Best Regards,
Jim
hey,
There are 3 methods in which you can create an excel from an ASP page( if i got ur question correctly).
1) using the content disposition
2) using the excel object ( when u are creating an object it is excel.application and not excel.sheet as u have done.once this is created then u have to create a worbook and then a worksheet and then u cn add to this sheet.)
the only problem with the above 2 methods are that they are slow when it comes to large number of rows.
but then we have this 3rd method which is very fast.
3) create a filesystem object. use the createtextfile method to create a file. here lies teh trick, in the createtextfile method give the name as harry.xls( assuming harry is the name of the excel sheet u want to create) eg/
set fil = server.createobject(script
set xl_fil = fil.createtextfile("harry.
to add rows to the the xls created u can use the writeline method.
eg. if u want to add "harry" in column A and "cool" in column B then u can write
xl_fil.writeline("harry" & chr(13) & "cool")
where chr(13) is a tab delimiter, which will seperate the columns.
the only flip side to this is , the xls created wont be fromatted. I guess this will suffice the need.
if u need it formatted, then let me know...will send u the code...
There are 3 methods in which you can create an excel from an ASP page( if i got ur question correctly).
1) using the content disposition
2) using the excel object ( when u are creating an object it is excel.application and not excel.sheet as u have done.once this is created then u have to create a worbook and then a worksheet and then u cn add to this sheet.)
the only problem with the above 2 methods are that they are slow when it comes to large number of rows.
but then we have this 3rd method which is very fast.
3) create a filesystem object. use the createtextfile method to create a file. here lies teh trick, in the createtextfile method give the name as harry.xls( assuming harry is the name of the excel sheet u want to create) eg/
set fil = server.createobject(script
set xl_fil = fil.createtextfile("harry.
to add rows to the the xls created u can use the writeline method.
eg. if u want to add "harry" in column A and "cool" in column B then u can write
xl_fil.writeline("harry" & chr(13) & "cool")
where chr(13) is a tab delimiter, which will seperate the columns.
the only flip side to this is , the xls created wont be fromatted. I guess this will suffice the need.
if u need it formatted, then let me know...will send u the code...
I like anand0802 suggestion best.. You could also try setting the content type to "text/csv" I get better results with that.
Dave
Dave
ASKER
What I exactly want is this :
1. I have a table displayed on the page
2. There's an option to export that data in excel
3. The user should be able to click that link / button
4. The table should get exported in an excel file
5. that excel file should open up in the browser
6. The user can save that file using "save as"
7. I'd also like to do some little formatting too.
Since I am new to ASP. Can someone gimme the exact code (using a small example)
Also if it works out fine, I'm gonna increase the points !!
1. I have a table displayed on the page
2. There's an option to export that data in excel
3. The user should be able to click that link / button
4. The table should get exported in an excel file
5. that excel file should open up in the browser
6. The user can save that file using "save as"
7. I'd also like to do some little formatting too.
Since I am new to ASP. Can someone gimme the exact code (using a small example)
Also if it works out fine, I'm gonna increase the points !!
HarryCool, I have done this thing a dozen times and I always end up doing the same thing...I output to CSV that way even if the user uses another spreadsheet chances are my file will open in their sreadsheet program.
'Your first tow lines of asp file
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "attachment; filename=yourfilename.csv"
As long as these are the first 2 lines of your file you should be fine..
You still do a response.write but instead of to the clients screen it goes to the file. (they are prompted save/open) So to make a csv if very easy
'First row contains your column name
response.write chr(34) & "column1" & chr(34) & "," & chr(34) & "column2" & chr(34)
'subsequent rows contain the data
response,write chr(34) & "val1" & chr(34) & "," & chr(34) & "val1" & chr(34)
Very easy from Recordset or array...
'your out put would look like this
"column1","column2"
"val1","val2"
And because you have the 2 lines at the top..your clients pc knows to open the prgram associated with thier spreadsheet..typically Excel.
Don't think that by using CSV your somehow getting less than Excel format...it's all the same gack donja know.
Dave
'Your first tow lines of asp file
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "attachment; filename=yourfilename.csv"
As long as these are the first 2 lines of your file you should be fine..
You still do a response.write but instead of to the clients screen it goes to the file. (they are prompted save/open) So to make a csv if very easy
'First row contains your column name
response.write chr(34) & "column1" & chr(34) & "," & chr(34) & "column2" & chr(34)
'subsequent rows contain the data
response,write chr(34) & "val1" & chr(34) & "," & chr(34) & "val1" & chr(34)
Very easy from Recordset or array...
'your out put would look like this
"column1","column2"
"val1","val2"
And because you have the 2 lines at the top..your clients pc knows to open the prgram associated with thier spreadsheet..typically Excel.
Don't think that by using CSV your somehow getting less than Excel format...it's all the same gack donja know.
Dave
ASKER
Didn't help.
I get the text written in the browser window only. This is how my ASP file looks........
<%@ Language=VBScript %>
<%
Response.ContentType = "test/csv<BR>"
Response.AddHeader "Content-Disposition", "attachment; filename=yourfilename.csv<
%>
<HTML>
<HEAD>
<title> asp to excel </title>
</HEAD>
<BODY>
<%
response.write chr(34) & "column1" & chr(34) & "," & chr(34) & "column2" & chr(34)
Response.write chr(34) & "val1" & chr(34) & "," & chr(34) & "val1" & chr(34)
%>
</BODY>
If possible pls mail me an example asp page at harrycool21@yahoo.com
I get the text written in the browser window only. This is how my ASP file looks........
<%@ Language=VBScript %>
<%
Response.ContentType = "test/csv<BR>"
Response.AddHeader "Content-Disposition", "attachment; filename=yourfilename.csv<
%>
<HTML>
<HEAD>
<title> asp to excel </title>
</HEAD>
<BODY>
<%
response.write chr(34) & "column1" & chr(34) & "," & chr(34) & "column2" & chr(34)
Response.write chr(34) & "val1" & chr(34) & "," & chr(34) & "val1" & chr(34)
%>
</BODY>
If possible pls mail me an example asp page at harrycool21@yahoo.com
HarryCool, I have done this thing a dozen times and I always end up doing the same thing...I output to CSV that way even if the user uses another spreadsheet chances are my file will open in their sreadsheet program.
'Your first tow lines of asp file
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "attachment; filename=yourfilename.csv"
As long as these are the first 2 lines of your file you should be fine..
You still do a response.write but instead of to the clients screen it goes to the file. (they are prompted save/open) So to make a csv if very easy
'First row contains your column name
response.write chr(34) & "column1" & chr(34) & "," & chr(34) & "column2" & chr(34)
'subsequent rows contain the data
response,write chr(34) & "val1" & chr(34) & "," & chr(34) & "val1" & chr(34)
Very easy from Recordset or array...
'your out put would look like this
"column1","column2"
"val1","val2"
And because you have the 2 lines at the top..your clients pc knows to open the prgram associated with thier spreadsheet..typically Excel.
Don't think that by using CSV your somehow getting less than Excel format...it's all the same gack donja know.
Dave
'Your first tow lines of asp file
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "attachment; filename=yourfilename.csv"
As long as these are the first 2 lines of your file you should be fine..
You still do a response.write but instead of to the clients screen it goes to the file. (they are prompted save/open) So to make a csv if very easy
'First row contains your column name
response.write chr(34) & "column1" & chr(34) & "," & chr(34) & "column2" & chr(34)
'subsequent rows contain the data
response,write chr(34) & "val1" & chr(34) & "," & chr(34) & "val1" & chr(34)
Very easy from Recordset or array...
'your out put would look like this
"column1","column2"
"val1","val2"
And because you have the 2 lines at the top..your clients pc knows to open the prgram associated with thier spreadsheet..typically Excel.
Don't think that by using CSV your somehow getting less than Excel format...it's all the same gack donja know.
Dave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It appears that this question has been abandoned. I will make a recommendation to the EE Moderators on its resolution in one week. I appreciate any comments that would help me to make a recommendation.
In the absence of responses, I will recommend the following:
[ACCEPT chisholmd LAST COMMENT AS ANSWER]
Silence = You agree with recommendation or don't care.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
apollois
EE Cleanup Volunteer
In the absence of responses, I will recommend the following:
[ACCEPT chisholmd LAST COMMENT AS ANSWER]
Silence = You agree with recommendation or don't care.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
apollois
EE Cleanup Volunteer
Comment from expert acepted as answer
Computer101
E-E Admin
Computer101
E-E Admin
Hello all,
I follow the sample code of chisholmd , and it works fine. I would like to incorporate the code into the <b>response.write </b> format .
Like in your sample code, you have
response,write chr(34) & "val1" & chr(34) & "," & chr(34) & "val1" & chr(34)
How do I incorporate it with this script for example:
<td >Subtotal for <%=strName%>: &
Your help is greatly appreciated. God blessed!!
Beginning of script--------------------
<%Response.ContentType = "application/save"
Response.AddHeader "Content-Disposition", "attachment; filename=yourfilename.xls"
Dim strName, strNameN, intLastRow, intFlag
strName = ""
strNameN = "N"
intLastRow = 1
intName = 1
intFlag = 0
ftotal = 0.0
fftotal = 0.0
intSeat1 = 0
intSeat11 = 0
intSeat2 = 0
intSeat22 = 0
%>
<table cellspacing="0" cellpadding="1" width="100%">
<tr>
<%for intN = 1 to intarr%>
<td ><%=arrFldHead(intN - 1)%></td>
<%Next%>
</tr>
<%objRSView.MoveFirst %>
<%While NOT objRSView.EOF%>
<%if strName <> CStr(objRSView(arrFldName(
<tr>
<td >Subtotal for <%=strName%>: &
<td ><%=intSeat1%></td>
<td > </td>
<td > </td>
<td ><%=formatCurrency(ftotal,
<input type="hidden" name="SS" value="<%=intLastRow%>">
</tr>
<%
ftotal = 0.0
intSeat1 = 0
end if %>
<tr>
<%
for intN = 1 to intarr
if intN = 1 then
if isNull(objRSView(arrFldNam
strNameN = " "
elseif strName <> CStr(objRSView(arrFldName(
strNameN = objRSView(arrFldName(intN - 1))
strName = objRSView(arrFldName(intN - 1))
intFlag = 1
else
strNameN = " "
end if
end if
if isNull(objRSView(arrFldNam
%>
<td > </td>
<% else %>
<% if intN = 1 then %>
<td ><%= strNameN %></td>
<% elseif intN = 18 then %>
<td ><%= objRSView(arrFldName(intN - 1)) %></td>
<% elseif intN = 19 or intN = 20 or intN = 21 then %>
<td ><%=formatCurrency(objRSVi
<% else %>
<td class=tableV><%= objRSView(arrFldName(intN - 1)) %></td>
<%end if
end if
if intN = 18 then
intSeat1 = intSeat1 + CInt(objRSView(arrFldName(
intSeat11 = intSeat11 + CInt(objRSView(arrFldName(
%>
<input type="hidden" name="<%=arrFldName(intN - 1)%>" value="<%=objRSView(arrFld
<% elseif intN = 21 then
ftotal = ftotal + CDbl(objRSView(arrFldName(
fftotal = fftotal + CDbl(objRSView(arrFldName(
%>
<input type="hidden" name="<%=arrFldName(intN - 1)%>" value="<%=objRSView(arrFld
<% else%>
<input type="hidden" name="<%=arrFldName(intN - 1)%>" value="<%=objRSView(arrFld
<%end if %>
<% Next %>
<% objRSView.MoveNext %>
</tr>
<%
intLastRow = intLastRow + 1
intFlag = 0
Wend
%>
<tr>
<td align=right >Subtotal for <%=strName%>: &
<td align=right><%=intSeat1%><
<td align=right> </td>
<td align=right> </td>
<td align=right><%=formatCurre
<input type="hidden" name="SS" value="<%=intLastRow%>">
</tr>
<tr >
<td align=right >Grand Total &nb
<td align=right><%=intSeat11%>
<td align=right> </td>
<td align=right> </td>
<td align=right><%=formatCurre
</tr>
</table>
%>
I follow the sample code of chisholmd , and it works fine. I would like to incorporate the code into the <b>response.write </b> format .
Like in your sample code, you have
response,write chr(34) & "val1" & chr(34) & "," & chr(34) & "val1" & chr(34)
How do I incorporate it with this script for example:
<td >Subtotal for <%=strName%>: &
Your help is greatly appreciated. God blessed!!
Beginning of script--------------------
<%Response.ContentType = "application/save"
Response.AddHeader "Content-Disposition", "attachment; filename=yourfilename.xls"
Dim strName, strNameN, intLastRow, intFlag
strName = ""
strNameN = "N"
intLastRow = 1
intName = 1
intFlag = 0
ftotal = 0.0
fftotal = 0.0
intSeat1 = 0
intSeat11 = 0
intSeat2 = 0
intSeat22 = 0
%>
<table cellspacing="0" cellpadding="1" width="100%">
<tr>
<%for intN = 1 to intarr%>
<td ><%=arrFldHead(intN - 1)%></td>
<%Next%>
</tr>
<%objRSView.MoveFirst %>
<%While NOT objRSView.EOF%>
<%if strName <> CStr(objRSView(arrFldName(
<tr>
<td >Subtotal for <%=strName%>: &
<td ><%=intSeat1%></td>
<td > </td>
<td > </td>
<td ><%=formatCurrency(ftotal,
<input type="hidden" name="SS" value="<%=intLastRow%>">
</tr>
<%
ftotal = 0.0
intSeat1 = 0
end if %>
<tr>
<%
for intN = 1 to intarr
if intN = 1 then
if isNull(objRSView(arrFldNam
strNameN = " "
elseif strName <> CStr(objRSView(arrFldName(
strNameN = objRSView(arrFldName(intN - 1))
strName = objRSView(arrFldName(intN - 1))
intFlag = 1
else
strNameN = " "
end if
end if
if isNull(objRSView(arrFldNam
%>
<td > </td>
<% else %>
<% if intN = 1 then %>
<td ><%= strNameN %></td>
<% elseif intN = 18 then %>
<td ><%= objRSView(arrFldName(intN - 1)) %></td>
<% elseif intN = 19 or intN = 20 or intN = 21 then %>
<td ><%=formatCurrency(objRSVi
<% else %>
<td class=tableV><%= objRSView(arrFldName(intN - 1)) %></td>
<%end if
end if
if intN = 18 then
intSeat1 = intSeat1 + CInt(objRSView(arrFldName(
intSeat11 = intSeat11 + CInt(objRSView(arrFldName(
%>
<input type="hidden" name="<%=arrFldName(intN - 1)%>" value="<%=objRSView(arrFld
<% elseif intN = 21 then
ftotal = ftotal + CDbl(objRSView(arrFldName(
fftotal = fftotal + CDbl(objRSView(arrFldName(
%>
<input type="hidden" name="<%=arrFldName(intN - 1)%>" value="<%=objRSView(arrFld
<% else%>
<input type="hidden" name="<%=arrFldName(intN - 1)%>" value="<%=objRSView(arrFld
<%end if %>
<% Next %>
<% objRSView.MoveNext %>
</tr>
<%
intLastRow = intLastRow + 1
intFlag = 0
Wend
%>
<tr>
<td align=right >Subtotal for <%=strName%>: &
<td align=right><%=intSeat1%><
<td align=right> </td>
<td align=right> </td>
<td align=right><%=formatCurre
<input type="hidden" name="SS" value="<%=intLastRow%>">
</tr>
<tr >
<td align=right >Grand Total &nb
<td align=right><%=intSeat11%>
<td align=right> </td>
<td align=right> </td>
<td align=right><%=formatCurre
</tr>
</table>
%>