ExcelADO is a VBScript class which allows you to open an Excel file via ADO. 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 Recordset directly. There are 2 different methods of accessing Excel workbooks: OLE and ADO. Click here to see my ExcelOLE class.
What is OLE Automation?
Object Linking and Embedding is a method of controlling an application (Excel) via another application (ASP).
Allows access to all VBA functions in excel.
Allows you to control everything which Excel can control (data, formatting, menu functions etc)
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.
Fast and reliable.
Can use SQL queries to select/update data. (Most methods and properties of the Connection and Recordset objects are supported)
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=ExcelADO.asp -->
'Create a new instance
Set MyExcelFile = New ExcelADO
'Open a Spreadsheet
if not MyExcelFile.OpenSpreadsheet("ExcelFile.xls") then Response.Write(MyExcelFile.ErrorText & "<br>")
'Open a Worksheet
if not MyExcelFile.OpenWorksheet("Sheet1$",MyExcelFile.forReading) then Response.Write(MyExcelFile.ErrorText & "<br>")
'Display the Header row
For ArrayIndex = 0 to MyExcelFile.NumColumns -1
Response.Write("<td>" & MyExcelFile.Columns(ArrayIndex) & "</td>")
'Display all of the data in that
For ArrayIndex = 0 to MyExcelFile.NumColumns -1
Response.Write("<td>" & MyExcelFile.ColumnData(ArrayIndex) & "</td>")
See ExcelADODemo.asp for a full demo.
String. Show a text message describing the last error.
String. Contains the virtual path and file name.
String. Contains the physical path and file name.
Array. Contains an array of length NumSheets-1 of all the Worksheet and Named space names. This is a 0 based array.
Array. Contains an array of length NumColumns-1 of all the Column names (if found). This is a 0 based array.
Integer. Number of tables (Worksheets and Named spaces) in the current Workbook.
Integer. Number of columns in the current worksheet/named space.
String. Additional SQL query text. Your statement should start with the "where" expression
Integer. Defaults to 0 or DriverJet.
Sets which ODBC driver to use. See the Driverxxxx constants below.
Object. The Excel Recordset object.
0. load into the Driver property (above).
Sets the ODBC driver to use the Microsoft Database JET 4 driver. This is the best and most reliable driver. This is the default driver.
1. load into the Driver property (above).
Sets the ODBC driver to use the Microsoft Excel (.xls) driver. The Jet driver is known to be better than this driver.
2. load into the Driver property (above).
Sets the ODBC driver to use the Microsoft Excel Text (.csv & .txt) driver. This is used for comma and tab delimited files.
0. Used with the OpenWorksheet method (below).
This I/O mode is readonly.
1. Used with the OpenWorksheet method (below).
This I/O mode is read and write.
Returns: True if spreadsheet opened successfully
Parameters: Absolute or Virtual path and file name. Must not be relative (start with "../")
Example: if not OpenSpreadsheet("test.xls") then 'do error handling
Opens the spreadsheet and sets the objRSXL recordsset object. The driver specified with the Driver variable is used to open the spreadsheet (this defaults to the MS JET4 driver).
VirtualFileName and AbsoluteFileName Properties are updated.
Sheets and NumSheets Properties are updated.
Returns: True if the Worksheet opened successfully
Parameters: Worksheet name, I/OMode
Example: if not OpenWorksheet("Sheet1$",forReading) then 'do error handling
Activates the selected Worksheet - OpenSpreadsheet must be run before this. All other methods in this class require this to be run before them (ie ReadRow).
Returns: True if the current row is valid (1 to 65536) and the recordset is no EOF.
Example: if not ReadRow then 'do error handling
Reads the current row of the recordset into an array.
Returns: The data located on the currently read row and the selected column
Parameters: Column number (from 1 to 256)
Example: mycell = ColumnData(2)
Retrieves the data from the result of the ReadRow method.
Closes the recordset.
Closes the spreadsheet, releasing file locks and resources.
NOTE: You can not create a new file in this class to write to - you must use an existing file as your template. (Use FSO to copy it to a unique filename first).
Run ExcelADODemo.asp for a full demo and reference code.
If you improve this code (this is version 1 so plenty to improve upon), please send me a copy! Thanks!
hunter @ beanland.net.au
1.0 First version.