From an ASP page, is there a way write to an EXCEL file without having EXCEL installed on the IIS ma

F

fniles

In my ASP page, I have a button that what it does is writing information to
an EXCEL file.
Currently I am using Server.CreateObject("excel.application") to do that.
Is that correct that in order to do that, the IIS server needs to have EXCEL
installed on it ?
My client does not have EXCEL loaded on their server and will not load it.
Is there any other way to write to an EXCEL file without using without
having to install EXCEL on the IIS server ?
Thank you.

Set spread = Server.CreateObject("excel.application")
spread.Workbooks.Open sExcelPath
spread.ActiveWorkbook.Worksheets("data").Select
iNumCol = 0
for iNumCol = 0 to 3
spread.ActiveWorkbook.ActiveSheet.Cells(1, iNumCol).Value = "col " &
iNumCol
next
spread.DisplayAlerts = False
spread.ActiveWorkbook.SaveAs spath & "\myExcelFile.xls"
spread.DisplayAlerts = True
spread.Workbooks.Close
spread.Quit
 
J

Jon Wallace

In order to write to any file which has a bespoke format (Word document,
PDF, Excel spreadsheet etc...) you need a handler - an API set which can
take input from a program and create the resultant file from it.

Without something installed which has the ability to create such files it's
not possible to write them. What you could try however is moving your code
which creates the excel file client-side so instead of the ASP processing /
creating the spreadsheet, you have a resultant file which has client-side
script in essense doing the same thing. This would of course very much
depend on how complicated your spreadsheet was, how much data was going into
it and where that data was comming from.

In short, no - you need some engine to create the spreadsheet in some form
or another.

Hope this helps,
Jon

www.insidetheregistry.com
 
B

Bob Barrows

fniles said:
In my ASP page, I have a button that what it does is writing
information to an EXCEL file.
Currently I am using Server.CreateObject("excel.application") to do
that. Is that correct that in order to do that, the IIS server needs
to have EXCEL installed on it ?

Of course!
My client does not have EXCEL loaded on their server and will not
load it.

Then you will have to give up on automating Excel in server-side code (which
is not a bad thing to have to give up, actually)
Is there any other way to write to an EXCEL file without
using without having to install EXCEL on the IIS server ?
Thank you.

Yes
This should explain things:
http://support.microsoft.com/default.aspx?scid=kb;en-us;257757

It includes a link to this:
http://support.microsoft.com/kb/199841/
 
B

Bob Riemersma

fniles said:
In my ASP page, I have a button that what it does is writing information
to an EXCEL file.
...
My client does not have EXCEL loaded on their server and will not load it.
Is there any other way to write to an EXCEL file without using without
having to install EXCEL on the IIS server ?

Any Windows OS of a practical vintage will already have the components
required to create a *basic* Excel workbook with one or more worksheets
filled with data. This is documented in many places, including a number of
MS KB articles.

A limitation is that you have no control over things like cell fonts, column
widths, etc. Even this may be overcome to an extent by copying a
pre-formatted "template" workbook and populating it with data. However the
example below (a WSH script for easy testing) creates a workbook from
scratch in the current directory:

Option Explicit

Private Const adCmdTable = 2
Private Const adLockOptimistic = 3
Private Const adOpenStatic = 3
Private Const adVarWChar = 202

Private catWB, tblWS, rsWS, intRow

Private Sub AddColumn(ByVal Table, ByVal ColName)
Dim Column

Set Column = CreateObject("ADOX.Column")
Column.Name = ColName
Column.Type = adVarWChar 'Can also use numeric types.
Table.Columns.Append Column
End Sub

Set catWB = CreateObject("ADOX.Catalog")
catWB.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='created.xls';" _
& "Extended Properties='Excel 8.0;HDR=Yes'"
Set tblWS = CreateObject("ADOX.Table")
tblWS.Name = "Sample"
AddColumn tblWS, "First"
AddColumn tblWS, "Second"
AddColumn tblWS, "Third"
catWB.Tables.Append tblWS
Set tblWS = Nothing
Set rsWS = CreateObject("ADODB.Recordset")
With rsWS
Set .ActiveConnection = catWB.ActiveConnection
Set catWB = Nothing
.Open "Sample", , adOpenStatic, adLockOptimistic, adCmdTable
For intRow = 2 To 100
.AddNew
.Fields("First").Value = CStr(intRow * 10)
.Fields("Second").Value = CStr(intRow * 100)
.Fields("Third").Value = CStr(intRow + 10000)
.Update
Next
.Close
End With
Set rsWS = Nothing

MsgBox "Complete!", vbOkOnly, "Create Workbook"
 
S

Sylvain Lafontaine

Unless you want your spreadsheets to be opened by versions of Excel 2000 and
before, the easiest way would be to use the SpreadsheetML specification.
XML files are plain text files that can be write by any IO file systems;
including those available with ASP. Don't forget to set the mime type to
Excel; see the last two references below.

http://msdn.microsoft.com/en-us/library/bb226687(office.11).aspx
http://msdn.microsoft.com/en-us/library/bb226693(office.11).aspx

http://www.brainbell.com/tutorials/ms-office/excel/Save_To_SpreadsheetML_And_Extracting_Data.htm
http://www.brainbell.com/tutorials/ms-office/excel/Create_Spreadsheets_Using_SpreadsheetML.htm

http://support.microsoft.com/default.aspx?scid=kb;en-us;257757
http://support.microsoft.com/kb/199841/

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
B

Bill McCarthy

Hi fniles,

You might find it a lot easier to write out the excel file as xml.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,733
Messages
2,569,440
Members
44,829
Latest member
PIXThurman

Latest Threads

Top