Working with Excel Spreadsheets
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
admin :: Jul.04.2010 :: Uncategorized :: No Comments »
