ExcelOLE v1.4
ExcelOLE is a VBScript class which allows you to open an Excel file via OLE Automation. The class object includes several easy to use methods to read and write data into an excel file. For more advanced features, the class allows you to manipulate the Excel Workbook directly. There are 2 different methods of accessing Excel workbooks: OLE and ADO. Click here to see my ExcelADO class.
What is OLE Automation?
Object Linking and Embedding is a method of controlling an application (Excel)
via another application (ASP).
Pros:
Allows access to all VBA functions in excel.
Allows you to control everything which Excel can control (data, formatting, menu functions etc)Cons:
You must have Microsoft Excel installed on your server - Excel is a very complex file format. Only Microsoft and a hand full of vendors can read it properly - and they all charge $ for their software.
If something goes wrong (Excel decides to ask you a question which is never displayed and can't be answered) the object will sit and wait forever (hang). If you test your script and have a reasonable level of error checking, then this is not an issue.
What is ADO?
Active Data Objects is the normal way of manipulating data in ASP. This
typically uses a Database driver and Recordset to stream data in and out of the
target object.
Pros:
Fast and reliable.
Can use SQL queries to select/update data. (Most methods and properties of the Connection and Recordset objects are supported)Cons:
You must have Microsoft Excel (or just it's ODBC drivers if you can achieve that) installed on your server - Excel is a very complex file format. Only Microsoft and a hand full of vendors can read it properly - and they all charge $ for their software.
You must have a header row. You can not access any area which does not have a header row (column names) covering it.
Column names must not contain non alpha numeric chars. (ie #)
Supports data only - no formatting etc.
Sample Basic Usage
It is suggested that you keep the class in it's include file, but you can copy the code out and paste it directly into your page if you like.
<!-- #include file=ExcelOLE.asp -->
'Create a new instance
Set MyExcelFile = New ExcelOLE
'Open a Spreadsheet
if not MyExcelFile.OpenSpreadsheet("ExcelFile.xls") then
Response.Write(MyExcelFile.ErrorText & "<br>")
'Open a Worksheet
if not MyExcelFile.OpenWorksheet("Sheet1") then
Response.Write(MyExcelFile.ErrorText & "<br>")
'Display the Header row
Response.Write("<table><tr>")
For ArrayIndex = 0 to MyExcelFile.NumColumns -1
Response.Write("<td>" &
MyExcelFile.Columns(ArrayIndex) & "</td>")
Next
'Display all of the data in that
While MyExcelFile.ReadRow
Response.Write("<tr>")
For ArrayIndex = 0 to MyExcelFile.NumColumns -1
Response.Write("<td>" &
MyExcelFile.ColumnData(ArrayIndex) & "</td>")
Next
Response.Write("</tr>")
wend
Response.Write("</table>")
See ExcelOLEDemo.asp for a full demo.
For a comprehensive help on the Excel functions available, Open Excel (with
any workbook) and got to Tools | Macro | Visual Basic Editor | Help.
Properties / Objects
ErrorText
String. Show a text message describing the last error.
VirtualFileName
String. Contains the virtual path and file name.
AbsoluteFileName
String. Contains the physical path and file name.
Sheets
Array. Contains an array of length NumSheets-1 of all the Worksheet
names. This is a 0 based array.
Columns
Array. Contains an array of length NumColumns-1 of all the Column names
(if found). This is a 0 based array.
CurrentRow
Long Integer. Holds the current row number - if using the ReadRow object.
Must be a number between 1 and 65535.
NumSheets
Integer. Number of sheets in the current Workbook.
NumColumns
Integer. Number of sheets in the current Workbook.
ExcelApp
Object. The Excel Application object.
ExcelBook
Object. The Excel Workbook object.
Methods
NewSpreadsheet
Returns: True if spreadsheet created successfully
Parameters: Absolute or Virtual path and file name. Must not be relative
(start with "../")
Syntax: NewSpreadsheet(FileName)
Example: if not NewSpreadsheet("test.xls") then 'do error handling
Creates the spreadsheet on disk and sets the ExcelBook object.
VirtualFileName and AbsoluteFileName Properties are
updated.
Sheets and NumSheets Properties are updated.
OpenSpreadsheet
Returns: True if spreadsheet opened successfully
Parameters: Absolute or Virtual path and file name. Must not be relative
(start with "../")
Syntax: OpenSpreadsheet(FileName)
Example: if not OpenSpreadsheet("test.xls") then 'do error handling
Opens the spreadsheet and sets the ExcelBook object.
VirtualFileName and AbsoluteFileName Properties are
updated.
Sheets and NumSheets Properties are updated.
If you have more than one web user, your workbook should be shared otherwise
Excel may 'hang' on opening - as it tries unsuccessfully to give "workbook
is locked errors". In your workbook select Tools, Share Workbook,
check/tick: Allow changes by more than one user.
OpenWorksheet
Returns: True if the Worksheet opened successfully
Parameters: Worksheet name
Syntax: OpenWorksheet(TabName)
Example: if not OpenWorksheet("Sheet1") then 'do error handling
Activates the selected Worksheet - all other methods in this class require this
- if you are using direct calls to the ExcelBook object then the
worksheet doesn't really need to be opened.
CopyWorksheet
Returns: True if the Worksheet copied successfully
Parameters: Source worksheet name, Target worksheet name
Syntax: CopyWorksheet(TabName,NewTabName)
Example: if not CopyWorksheet("Sheet1","newSheet") then 'do error handling
Copies the source worksheet to a new target sheet (which it creates)
and
Activates it.
ReadRow
Returns: True if the current row is valid (1 to 65536) or there is data
found on the current row
Parameters: none
Syntax: ReadRow
Example: if not ReadRow then 'do error handling
Reads the current row into an array. Accessing an array is a lot faster than
constantly reading from Excel.
ColumnData
Returns: The data located on the currently read row and the selected
column
Parameters: Column number (from 1 to 256)
Syntax: ColumnData(Column)
Example: mycell = ColumnData(2)
Retrieves the data from the result of the ReadRow method.
CellRead
Returns: The data located on the specified cell co-ordinates. Null if
read error
Parameters: Row number (from 1 to 65536), Column number (from 1 to 256)
Syntax: CellRead(Row,Column)
Example: CellData = CellRead(1,2)
CellWrite
Returns: True if no error
Parameters: Row number (from 1 to 65536), Column number (from 1 to 256),
Data for cell
Syntax: CellWrite(Row,Column,Data)
Example: If not CellWrite(1,2,"this is new data") then 'do error
handling
SaveSpreadsheet
Returns: True if saved successfully
Parameters: Optional. Absolute or Virtual path and file name. Must not be relative
(start with "../")
Syntax: SaveSpreadsheet(FileName) or SaveSpreadsheet("")
Example: If not SaveSpreadsheet("newfile.xls") then 'do error handling
Saves the spreadsheet.
VirtualFileName and AbsoluteFileName Properties are
updated if file name provided.
If no file name parameter is provided then the current workbook is
saved/updated (rather than "Save As").
CloseSpreadsheet
Returns: none
Parameters: none
Syntax: CloseSpreadsheet
Example: CloseSpreadsheet
Closes the spreadsheet, releasing file locks and resources.
Excel Permissions / Rights
Run ExcelOLEDemo.asp for a full demo and reference code.
If you improve this code, please
send me a copy! Thanks!
Hunter Beanland
hunter @ beanland.net.au
http://www.beanland.net.au/programming/
Version History
1.0 First version.
1.1 Fixed OpenWorksheet where no tabs or old versions of VBScript
1.2 Fixed CloseSpreadsheet to properly close and destroy the ExcelApp.
(thanks Brian M). Added no filename option to SaveSpreadsheet method
1.3 Added NewSpreadsheet method
1.4 Added CopyWorksheet method. Exposed the ExcelApp object