Database (DB/2, ODBC, Oracle) : Q: Can I read and write data to/from a Microsoft Excel spreadsheet?

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.
2.
3.
4.
5.
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.
10.
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.