ASP to Excel

HarryCool
HarryCool used Ask the Experts™
on
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></td>"
  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("D20").Value = "Jack"
oExcel.saveAs("c:\a.xls\")
%>

I am getting this error -

Microsoft VBScript runtime error '800a0046'

Permission denied: 'CreateObject'

/test.asp, line 12


Please help !
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
How about make it: Server.CreateObject ?

Author

Commented:
Gives the same result.

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

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Commented:
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
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(scripting.filesystemobject")
set xl_fil = fil.createtextfile("harry.xls")

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

Author

Commented:
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 !!
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








 



Author

Commented:
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<BR>"
%>

<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








 



Sorrey..web site posted previous post in stead of this one..

You have added <br>'s for some reason and that is causing it to not work.

try this:

<%@ Language=VBScript %>
<%
Response.ContentType = "test/csv"
Response.AddHeader "Content-Disposition", "attachment; filename=yourfilename.csv"
    response.write chr(34) & "column1" & chr(34) & "," & chr(34) & "column2" & chr(34)
    Response.write chr(34) & "val1" & chr(34) & "," & chr(34)  & "val1" & chr(34)
%>

Commented:
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
Comment from expert acepted as answer

Computer101
E-E Admin

Commented:
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%>:&nbsp;&nbsp;&nbsp;&nbsp;</td>

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(0))) and intLastRow <> 1 then %>
      <tr>
            <td >Subtotal for <%=strName%>:&nbsp;&nbsp;&nbsp;&nbsp;</td>
            <td ><%=intSeat1%></td>
            <td >&nbsp;</td>
            <td  >&nbsp;</td>
            <td ><%=formatCurrency(ftotal,-1)%></td>
            <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(arrFldName(intN - 1))) then
                              strNameN = "&nbsp;"
                        elseif      strName <> CStr(objRSView(arrFldName(intN - 1))) then
                              strNameN = objRSView(arrFldName(intN - 1))
                              strName = objRSView(arrFldName(intN - 1))
                              intFlag = 1
                        else
                              strNameN = "&nbsp;"
                        end if
                  end if      
            
            if isNull(objRSView(arrFldName(intN - 1))) then
          %>
                  <td >&nbsp;</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(objRSView(arrFldName(intN - 1)),-1)%></td>
                  <% else %>            
                        <td class=tableV><%= objRSView(arrFldName(intN - 1)) %></td>
                  <%end if
            end if
             
            if intN = 18 then
                  intSeat1 = intSeat1 + CInt(objRSView(arrFldName(intN - 1)))
                  intSeat11 = intSeat11 + CInt(objRSView(arrFldName(intN - 1)))
            %>
                  <input type="hidden" name="<%=arrFldName(intN - 1)%>" value="<%=objRSView(arrFldName(intN - 1))%>">
                
            <% elseif intN = 21 then
                  ftotal = ftotal + CDbl(objRSView(arrFldName(intN - 1)))
                  fftotal = fftotal + CDbl(objRSView(arrFldName(intN - 1)))
            %>
                  <input type="hidden" name="<%=arrFldName(intN - 1)%>" value="<%=objRSView(arrFldName(intN - 1))%>">
                
          <%      else%>
                  <input type="hidden" name="<%=arrFldName(intN - 1)%>" value="<%=objRSView(arrFldName(intN - 1))%>">
          <%end if %>
            <% Next %>
         <% objRSView.MoveNext %>
    </tr>
      
      <%
      intLastRow = intLastRow + 1      
      intFlag = 0
      Wend
      %>
      <tr>
            <td align=right  >Subtotal for <%=strName%>:&nbsp;&nbsp;&nbsp;&nbsp;</td>
            <td align=right><%=intSeat1%></td>
            <td align=right>&nbsp;</td>
            <td align=right>&nbsp;</td>
            <td align=right><%=formatCurrency(ftotal,-1)%></td>
            <input type="hidden" name="SS" value="<%=intLastRow%>">
      </tr>
      <tr >
            <td align=right >Grand Total&nbsp;&nbsp;&nbsp;&nbsp;</td>
            <td align=right><%=intSeat11%></td>
            <td align=right>&nbsp;</td>
            <td align=right>&nbsp;</td>
            <td align=right><%=formatCurrency(fftotal,-1)%></td>
      </tr>
      
</table>
%>

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial