Q: Can I read and write data to/from a Microsoft Excel spreadsheet?
Problem
Some users have expressed interest in being able to interact with a Microsoft Excel spreadsheet from VA Smalltalk.
Solution
Microsoft provides ODBC database drivers for Excel with Windows. If you add an ODBC data source for your Excel spreadsheet via the Windows ODBC Data Source Administrator tool, the spreadsheet will be available for use by the VA Smalltalk ODBC database feature. Note that there are probably limitations to what all you can do with an Excel spreadsheet. The examples below show how to read and write rows of data.
Follow these steps to get started:
1. Create a new Excel spreadsheet with two worksheets defined. Name the first worksheet ‘Table1’ and the second worksheet ‘Employees’.
2. On the Table1 worksheet, add a header row with the following values: ‘Col1’, ‘Col2’, and ‘Col3’. Under each column, add several rows of random data.
3. On the Employees worksheet, add a header row with five columns containing the following values: ‘FIRSTNAME’, ‘MIDDLENAME’, ‘LASTNAME’, ‘HOME_PHONE’, and ‘MOBILE_PHONE’. Enter several rows of data to each of these columns.
4. Save the spreadsheet as an excel workbook named ‘VASmalltalkODBCTest’.
5. Create a Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) ODBC data source using the Microsoft ODBC Data Source Administrator utility.
6. Set the Data Source Name to ‘VASmalltalkODBCTest’ .
7. Press the Select Workbook… button and select the Excel workbook you saved in step #4.
8. Press the Options >> button and then uncheck the Read Only checkbox.
9. Save the data source by pressing the OK button.
10. Start up a VA Smalltalk development image and install the ST: Database, ODBC feature.
11. Play with the Excel spreadsheet by executing the following ode snippets. Note that when referring to worksheets, you must refer to them using this notation: [WorksheetName$] instead of just using the worksheet name. For example, refer to the ‘Table1’ worksheet by specifying ‘[Table1$]’:
" Execute the following code snippet to Read all rows from the Table1 worksheet. An Inspector should popup with the contents of the 'table' "
| conSpec querySpec connection result resultCollection |
conSpec := AbtDatabaseConnectionSpec
forDbmClass: #AbtOdbcDatabaseManager
dataSourceName: 'VASmalltalkODBCTest' .
conSpec promptEnabled: false.
connection := conSpec connect.
querySpec :=
AbtQuerySpec new
statement: 'select * from [Table1$]'.
result := connection resultTableFromQuerySpec: querySpec.
resultCollection := OrderedCollection new.
result
do: [ :eRow | resultCollection add: eRow asString ].
connection disconnect.
resultCollection inspect.
==================================================
" Execute the following code snippet to Read all rows from the Employees worksheet. An inspector should popup with the contents
of the 'table' "
| conSpec querySpec connection result resultCollection |
conSpec := AbtDatabaseConnectionSpec
forDbmClass: #AbtOdbcDatabaseManager
dataSourceName: 'VASmalltalkODBCTest' .
conSpec promptEnabled: false.
connection := conSpec connect.
querySpec :=
AbtQuerySpec new
statement: 'select * from [Employees$]'.
result := connection resultTableFromQuerySpec: querySpec.
resultCollection := OrderedCollection new.
result
do: [ :eRow | resultCollection add: eRow asString ].
connection disconnect.
resultCollection inspect.
====================================================
"Execute the following code snippet to insert a new row in the Employees worksheet. "
| conSpec connection table newRow |
conSpec := AbtDatabaseConnectionSpec
forDbmClass: #AbtOdbcDatabaseManager
dataSourceName: 'VASmalltalkODBCTest' .
conSpec promptEnabled: false.
connection := conSpec connect.
table := connection openTableNamed: '[Employees$]'.
newRow := table emptyRow.
newRow
at: #FIRSTNAME put: 'Fred' ;
at: #MIDDLENAME put: 'Q' ;
at: #LASTNAME put: 'Flintstone' ;
at: #'HOME_PHONE' put: '9191234567' ;
at: #'MOBILE_PHONE' put: '9192345678'.
table addRow: newRow.
connection disconnect.