Solved

create xls file thru asp page

Posted on 2001-08-06
10
1,697 Views
Last Modified: 2008-03-27
we need to mke a form that when submitted will create a excel file on the webserver.
I found some code for an example:
--------------------
<%
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
%>
------------------------
but it gives me this error:

error '800706bb'
The RPC server is too busy to complete this operation.

/index.asp, line 3

excel 2000 is installed on the server, AspAllowoutofProcComponents is true
iis 4 is running.

does anyone know how to create an excel file with asp?
0
Comment
Question by:mattdunlap
10 Comments
 
LVL 19

Expert Comment

by:webwoman
ID: 6356923
Why do you need an Excel file on the server? It would be much better to either write a text file, save the info in a database, or not save it at all.

If you just want a text file, there are lots of options to do that. If you want to save it to a database, it would definitely be cleaner.
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6356956
Pretty easy really, this is what I use.  The fields are populated with record data from a dB, but it is easy to get it from a form, plus I have a requirement to save and then open the file;

<%@ Language=VBScript%>
<% ' Set database connection and recordset properties %>
<!--#INCLUDE FILE="Database.asp"-->
<%
     sSql = Request("sSql")
     Set rs = RunWithRS(sSQL)
theSessionID = Session.SessionID
'Response.Write theSessionID
%>
<%
'Create a random Filename based on the SessionID

fileExcel = theSessionID&".xls"
filePath = Server.mapPath("temp")
filename = filePath & "\" & fileExcel

Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set MyFile = fs.CreateTextFile(filename, 2, True)
strLineTitle = "" 'Initialize the variable for storing the fieldnames
strLineTitle = "Name" &chr(9) &_
"FirstName" &chr(9) &_
"Middle" &chr(9) &_
"LastName" &chr(9) &_
"Suffix" &chr(9) &_
"Title" &chr(9) &_
"Company" &chr(9) &_
"Department" &chr(9) &_
"Address1" &chr(9) &_
"Address2" &chr(9) &_
"Address3" &chr(9) &_
"City" &chr(9) &_
"State" &chr(9) &_
"ZipCode" &chr(9) &_
"Country" &chr(9) &_
"Phone" &chr(9) &_
"Fax" &chr(9) &_
"DirectLine" &chr(9) &_
"CompanyMaine" &chr(9) &_
"MobilePhone" &chr(9) &_
"Pager" &chr(9) &_
"Phone1" &chr(9) &_
"Phone2" &chr(9) &_
"Email" &chr(9) &_
"Email2" &chr(9) &_
"Email3" &chr(9) &_
"WebPage" &chr(9) &_
"Source" &chr(9) &_
"Level" &chr(9) &_
"Categories" &chr(9) &_
"V/S/O" &chr(9) &_
"Region" &chr(9) &_
"Notes"
            strLineTitle = strLineTitle & vbCrlf
         'Write this string into the file
          MyFile.writeline strLineTitle

         'Retrieve the values from the database and write into the Excel file
          Do while Not rs.EOF
            str = rs.Fields("Name")&chr(9) &_
rs.Fields("FirstName")&chr(9) &_
rs.Fields("Middle")&chr(9) &_
rs.Fields("LastName")&chr(9) &_
rs.Fields("Suffix")&chr(9) &_
rs.Fields("Title")&chr(9) &_
rs.Fields("Company")&chr(9) &_
rs.Fields("Department")&chr(9) &_
rs.Fields("Address1")&chr(9) &_
rs.Fields("Address2")&chr(9) &_
rs.Fields("Address3")&chr(9) &_
rs.Fields("City")&chr(9) &_
rs.Fields("State")&chr(9) &_
rs.Fields("ZipCode")&chr(9) &_
rs.Fields("Country")&chr(9) &_
rs.Fields("Phone")&chr(9) &_
rs.Fields("Fax")&chr(9) &_
rs.Fields("DirectLine")&chr(9) &_
rs.Fields("CompanyMain")&chr(9) &_
rs.Fields("MobilePhone")&chr(9) &_
rs.Fields("Pager")&chr(9) &_
rs.Fields("Phone1")&chr(9) &_
rs.Fields("Phone2")&chr(9) &_
rs.Fields("Email")&chr(9) &_
rs.Fields("Email2")&chr(9) &_
rs.Fields("Email3")&chr(9) &_
rs.Fields("WebPage")&chr(9) &_
rs.Fields("Source")&chr(9) &_
rs.Fields("Level")&chr(9) &_
rs.Fields("Categories")&chr(9) &_
rs.Fields("V/S/O")&chr(9) &_
rs.Fields("Region")&chr(9) &_
rs.Fields("Notes")&chr(9)
          strLine = str
          MyFile.writeline strLine
       rs.MoveNext
       Loop

       'Clean up
       MyFile.Close
       Set MyFile=Nothing
       Set fs=Nothing


' Now open the newly created .xls file
Response.CacheControl = "no-store"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
Response.ContentType = "application/vnd.ms-excel"
'Response.Clear
Response.Redirect "temp/"&theSessionID&".xls"
'Response.End
%>

0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6357381
Give this a whirl...

<%
Dim Excel, oSheet, oRng

 Set Excel = CreateObject("Excel.Application")
 Excel.Visible = True
 
 Excel.Workbooks.Add

 Set oSheet = Excel.ActiveSheet

 oSheet.Cells(1,1).Value = "TEXT"

 Set oRng = oSheet.Range("A1", "Z1")
 oRng.EntireColumn.AutoFit
 Excel.ActiveWorkbook.RefreshAll
 
 Excel.ActiveWorkbook.SaveAs Server.Mappath & "test.xls", FileFormat:=xlNormal
 Excel.Quit
%>
0
 
LVL 1

Expert Comment

by:LCP
ID: 6364682
there is a license and threading problem if u run excel object in asp. usually, it is prefered that you create a html table to store your data and specify the content type to excel (application/vnd.ms-excel), then your browser will open the html table in excel at client browser.

<table>
<tr>
<td>A1</td><td>B1</td>
</tr>
<tr>
<td>A2</td><td>B2</td>
</tr>
</table>

you can use mgfranz 's method to create a csv file instead of html table which also can be open by excel in client browser.
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6364723
I have never had a problem with what you are refering to LCP, but then again I have not had to develop an app with the Object for Excel, I have always just created the file with FSO.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Expert Comment

by:LCP
ID: 6364799
IF u have use excel object, u may face excel object make your pc hang sometimes. Your can imagine if a number of users create an excel object in web server at the same time.. the performance of web server can't be imaginated.

Microsoft have introduce another object library in office 2000 called Office Web Component, it also got similar functionality as excel object library and it is smaller in size. But you must install ms office 2000 in web server, and according to their license agreement, you must make sure the each client browser must install a version of ms office b4 use (if I didn't interpret wrong)..
therefore, I always recommend use html table and csv file format. :>
0
 
LVL 1

Expert Comment

by:LCP
ID: 6364884
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6364894
I see...
0
 
LVL 33

Expert Comment

by:hongjun
ID: 8671383
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
[PAQ with NO REFUND]

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

hongjun
EE Cleanup Volunteer
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 8736168
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
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…

760 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

19 Experts available now in Live!

Get 1:1 Help Now