ExcelADO is a VB 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 DataSet (DataTable or DataRow) directly. There are 2 different methods of accessing Excel workbooks: OLE and ADO. Click here to see my ExcelOLE class.
This is a VB class which should work in WinForms mode (not tested), but the demo is in ASP.NET.
What is OLE Automation (COM)?
Object Linking and Embedding is a method of controlling an application (Excel) via another application (VB.NET).
Allows access to all 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 web 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 VB.NET. This typically uses a Jet Database driver and a DataSet (DataTable and DataRow) 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.
Can not delete rows.
Can not create a new file or change it's schema
Supports data only - no formatting etc.
Sample Basic Usage
It is suggested that you copy the class into a separate vb file and compile it into your assemblies, but you can copy the code out and paste it directly into your page if you like.
'Create a new instanceDim
MyExcelFile As New ExcelADO()
'Open a SpreadsheetIf Not
Response.Write(MyExcelFile.ErrorText & "<br>")If Not
'Open a Worksheet (worksheets have a $ after the name)
Response.Write(MyExcelFile.ErrorText & "<br>")
'You read rows via ReadRow method
For intCol = 0 To
MyExcelFile.ExcelDataRow(intCol) & ", ")
'or Display each data row via directly accessing the DataTable
For intRow = 0 To
MyExcelFile.ExcelDataTable.Rows.Count - 1
For intCol = 0 To
MyExcelFile.ExcelDataTable.Columns.Count - 1
MyExcelFile.ExcelDataTable.Rows(intRow).Item(intCol) & ",
'Insert a new row
MyExcelFile.NewRow() 'Must create new row first
MyExcelFile.ExcelDataRow(0) = "Newly"
MyExcelFile.ExcelDataRow(1) = "inserted"
'Add the row back to the table (update the table)
MyExcelFile.InsertRow() Then Response.Write(
MyExcelFile.ErrorText & "<br>")
See ExcelADODemo.aspx (and ExcelADODemo.aspx.vb) for a full demo.
String. Show a text message describing the last error.
String Array. Contains an array of length NumSheets-1 of all the Worksheet and Named space names. This is a 0 based array.
String 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
DriverType. Defaults to DriverType.JetExcel
Sets which ODBC driver to use. DriverType.JetText is a delimited text file mode of the Jet driver.
DataSet. The DataSet of the entire Excel spreadsheet.
DataTable. The DataTable from the DataSet of the entire Excel spreadsheet.
DataRow. The current row use by ReadRow, InsertRow and UpdateRow.
OLEDBConnector. If you need to alter it directly (generally not used by you).
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 up the ExcelDataSet and ExcelDataTable objects. The driver specified with the Driver variable is used to open the spreadsheet (this defaults to the MS JET4 Excel driver).
Sheets and NumSheets Properties are updated.
Returns: True if the Worksheet opened successfully
Parameters: Worksheet name
Example: if not OpenWorksheet("Sheet1$") 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).
NOTE: You can not create a new file in this class to write to - you must use an existing file as your template.
Returns: Number of records affected
Parameters: SQL command string
Example: if SQLExecute("Sheet1$") < 1 then 'do error handling
Executes SQL command on the file. Used internally, but may be of value to you too.
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 DataSet into the ExcelDataRow object.
Creates a new ExcelDataRow object ready to be altered.
Returns: True if inserted correctly
Example: if not InsertRow() then 'do error handling
Added the ExcelDataRow object back into the DataSet and issues a SQL command to insert it to the file.
NewRow() must be called before altering the ExcelDataRow object.
Returns: True if updated correctly
Example: if not UpdateRow() then 'do error handling
The changed ExcelDataRow values are sent via a SQL command to be updated in the file.
Altering the ExcelDataRow object will automatically update the DataSet (linked), but this method must be called to commit to the file.
NOTE: More the one row which has the same values, will update all rows which are the same.
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.
Clears the DataSet.
Closes the spreadsheet, releasing file locks and resources.
Run ExcelADODemo.aspx 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.02 Fixed SQL in Update function. Slight refactor.
1.0 First version based on my ExcelADO v1.0 class (classic ASP).