Working with Excel Spreadsheets in code

Everytime I see some code that has to work with data in Excel files it always uses OLE automation. The worst case of this is on servers to process uploaded files, Microsoft doesn’t support Office on backend servers. I think a much better method is to use the OLEDB JET database providers, whether it’s for Domino, ASP, ASP.NET or anything else for that matter.

There’s no need to install Office at all, if your using Windows 2008 Server 64 bit you will need to install a download from Microsoft to get the ADODB dlls. This method is supported in all versions of Excel including 2007, but this uses the ACE Provider instead of JET.

Anyway, here’s some code written in LotusScript to demonstrate Create, Read and Update of Excel data. Unfortunately Delete is not supported.

The following code is used to read a XLS file. It will only read the first Worksheet, but you could read through all of them.

On Error Goto processError

 Const filename = "c:\users\ian\my documents\writexls.xls"
 Const adSchemaTables = 20

 Dim adodbConnection As Variant
 Dim connectionString As String
 Dim adodbRecordSet As Variant
 Dim worksheet As String

 connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
 filename & ";Extended Properties=""Excel 8.0;HDR=Yes;"""

 Set adodbConnection = CreateObject("adodb.connection")
 adodbConnection.Open(connectionString) 

 Set adodbRecordSet = adodbConnection.OpenSchema(adSchemaTables)
 worksheet = adodbRecordSet.Fields("table_name").Value

 adodbRecordSet.Close
 adodbRecordSet.Open "SELECT * FROM " + worksheet, adodbConnection
 While Not adodbRecordSet.EOF
  For i = 0 To (adodbRecordSet.Fields.Count - 1)
   Msgbox adodbRecordSet.Fields(i).Value
  Next
  adodbRecordSet.MoveNext
 Wend

 adodbRecordSet.Close
 adodbConnection.Close

finally:

 Set adodbConnection = Nothing
 Set adodbCommand = Nothing
 Exit Sub

processError:

 Print "Error: " & Error & ": " + Error$ + " on line " & Erl
 Goto finally

Now, here’s the code to create, insert and update data in the file:

On Error Goto processError

 Const filename = "c:\users\ian\my documents\writexls.xls"

 Dim adodbConnection As Variant
 Dim connectionString As String
 Dim adodbCommand As Variant

 Kill filename

 connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
 filename & ";Extended Properties=""Excel 8.0;HDR=Yes;"""

 Set adodbConnection = CreateObject("adodb.connection")
 adodbConnection.Open(connectionString) 

 Set adodbCommand = CreateObject("adodb.command")
 Set adodbCommand.ActiveConnection = adodbConnection

 adodbCommand.CommandText = "CREATE TABLE [Contacts] ([Id] number, [Name] string)"
 adodbCommand.Execute

 adodbCommand.CommandText = "INSERT INTO [Contacts] ([Id], [Name]) VALUES ('1', 'Ian')"
 adodbCommand.Execute
 adodbCommand.CommandText = "INSERT INTO [Contacts] ([Id], [Name]) VALUES ('2', 'Tom')"
 adodbCommand.Execute

 adodbCommand.CommandText = "UPDATE [Contacts] SET [Name] = 'Paul' WHERE [Id] = 1"
 adodbCommand.Execute

 adodbConnection.Close

finally:

 Set adodbConnection = Nothing
 Set adodbCommand = Nothing
 Exit Sub

processError:

 Print "Error: " & Error & ": " + Error$ + " on line " & Erl
 Goto finally