ExcelOLE.NET v1.0

ExcelOLE is a VB class which allows you to open an Excel file via OLE Automation (COM). 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.

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).

Pros:
Allows access to all 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 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.

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.
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 instance
Dim MyExcelFile As New ExcelOLE()

'Open a Spreadsheet
If Not MyExcelFile.OpenSpreadsheet(Server.MapPath("ExcelFile.xls")) Then Response.Write(MyExcelFile.ErrorText & "<br>")
'Open a Worksheet
If Not MyExcelFile.OpenWorksheet("Sheet1") Then Response.Write(MyExcelFile.ErrorText & "<br>")

'Display each data row via the ReadRow method
While MyExcelFile.ReadRow
   
For intCol = 0 To MyExcelFile.NumColumns - 1
        Response.Write(
MyExcelFile.ColumnData(intCol) & ", ")
    Next
    Response.Write("<br>")
End While

'Write to the file via the CellWrite method
If Not MyExcelFile.CellWrite(2, 2, "I am newly inserted") Then Response.Write(MyExcelFile.ErrorText & "<br>")

'or Write to the file via Excel's own methods
MyExcelFile
.ExcelBook.ActiveSheet.Cells(2, 1).Value = "I am newly inserted"

See ExcelOLEDemo.aspx (and ExcelOLEDemo.aspx.vb) 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.

Visual Studio & Compiler References

For the Excel object to be recognised in Visual Studio, go to: Project, Add Reference, COM, Microsoft Excel Object Library.

If compiling manually, add a resource reference to the compiler: ..\bin\Interop.Excel.dll (assuming VS has copied it there from the step above). If you don't have this file, then search around of the web for help (I saw it somewhere, just don't remember where).
Otherwise, you can change the code to use the Microsoft.VisualBasic.GetObject( , "Excel.Application") method to dynamically create the COM link.

Properties / Objects

ErrorText
String. Show a text message describing the last error.

Sheets
String Array. Contains an array of length NumSheets-1 of all the Worksheet names. This is a 0 based array.

Columns
String Array. Contains an array of length NumColumns-1 of all the Column names (if found). This is a 0 based array.

NumSheets
Integer. Number of sheets in the current Workbook.

NumColumns
Integer. Number of sheets in the current Workbook.

ExcelApp
Excel.Application Object. The Excel Application itself.

ExcelBook
Excel.Workbook Object. The current Excel Workbook.

Methods

OpenSpreadsheet
Returns: True if spreadsheet opened successfully
Parameters: Optional. Absolute path and file name or blank.
Syntax: OpenSpreadsheet(FileName)
Example: if not OpenSpreadsheet("test.xls") then 'do error handling
Opens the spreadsheet and sets the ExcelBook object.
Pass blank ("") if you wish to create a new workbook.
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.
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 path and file name.
Syntax: SaveSpreadsheet(FileName) or SaveSpreadsheet("")
Example: If not SaveSpreadsheet("newfile.xls") then 'do error handling
Saves the spreadsheet.
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

See ExcelOLEDemo.aspx (and ExcelOLEDemo.aspx.vb) for a full demo.
If you improve this code, please send me a copy! Thanks!

Hunter Beanland
hunter @ beanland.net.au
http://www.beanland.net.au/programming/dotnet/

Version History
1.03 Fixed the disposing of COM object, lower bound check on co/row, slight refactor
1.0 First version based on my ExcelOLE 1.4 class (for classic ASP)