This page provides examples of how to read and write a Microsoft [Excel] document using the [Tcom] package. See [How one discovers the API for a COM-exporting application] for more help on finding Excel commands.

[Tom Krehbiel]

----
'''A note on client disconnection'''

The [Tcom] client doesn't disconnect from the server when you issue a close or quit command. This can result in strange behavior such as Excel hanging on starts up. I found this note on comp.lang.tcl which helps explain the problem.

 #> > I think there is one problem ... tcom (or better your tcl script)
 #> > holds a reverence to Excel.
 #> 
 #> To close Excel you need to loose this reference!
 #> A COM server could only be unloaded if the last reference to the
 #> last  served COM object is decremented to zero.
 #> 
 #> How to loose this reference depends on the version of tcom.
 #> Below the version 3 you have to use the release command.
 #> With version 3 you only have to store the handle in a variable.
 #> If the variable is deleted, because of leaving the scope the
 #> release command of tcom will be called automatically internally!
 #> 
 #> So ... do you store the handle to the Excel object in a global
 #> variable? Than simply unset this variable or set the variable
 #> to a different value!
 #> 
 #> But there is no need to close your shell!
 #> 
 #> Regards
 #> 
 #> Martin Lemburg

----
'''This example illustrates how to READ an Excel document.'''

-under construction-


----
'''This example illustrates how to WRITE an Excel document.'''


 package require tcom

 # define alignment constants
 set align(horiz,general)    [expr 1]
 set align(horiz,center)     [expr -4108]
 set align(horiz,left)       [expr -4131]
 set align(horiz,right)      [expr -4152]
 set align(horiz,justify)    [expr -4130]
 set align(vert,bottom)      [expr -4107]
 set align(vert,center)      [expr -4108]
 set align(vert,justify)     [expr -4130]
 set align(vert,top)         [expr -4160]

 # define border constants
 set border(weight,hairline) [expr 1]
 set border(weight,medium)   [expr -4138]
 set border(weight,thick)    [expr 4]
 set border(weight,thin)     [expr 2]
 set border(line,diagDown)   [expr 5]
 set border(line,diagUp)     [expr 6]
 set border(line,bottom)     [expr 9]
 set border(line,left)       [expr 7]
 set border(line,right)      [expr 10]
 set border(line,top)        [expr 8]
 set border(line,insideHoriz) [expr 12]
 set border(line,insideVert) [expr 11]

 # Connect to Excel COM object.
 set application [::tcom::ref createobject Excel.Application]

 # Don't display Excel gui
 # (warning and error dialogs will still be displayed).
 $application Visible 0

 # Create inital workbook.
 # It will contain 3 worksheets by default.
 set workbooks [$application Workbooks]
 set workbook [$workbooks Add]
 set worksheets [$workbook Worksheets]

 # Delete all but one worksheet.
 # This will cause alert dialogs to be displayed unless they are turned off.
 $application DisplayAlerts [expr 0]
 set sheetCount [$worksheets Count]
 for {set n ${sheetCount}} {${n}>1} {incr n -1} {
     set worksheet [$worksheets Item [expr ${n}]]
     ${worksheet} Delete 
 }
 # Turn the alerts back on.
 $application DisplayAlerts [expr 1]

 # Add a worksheet named "NewTab".
 set lastWorksheet [$worksheets Item [$worksheets Count]]
 set worksheet [$worksheets Add [::tcom::na] $lastWorksheet]
 $worksheet Name "NewTab"

 # Rename the first tab.
 set worksheet [$worksheets Item [expr 1]]
 $worksheet Name "Booleans"

 # Puts values in some cells.
 set cells [$worksheet Cells]
 set i 0
 foreach row {1} {
     foreach column {A B C} {
         $cells Item $row $column [incr i]
     }
 }
 foreach row {1 2 3} {
     foreach column {A} {
         $cells Item $row $column [incr i]
     }
 }
 $cells Item 2 B "This cell demonstrates\nmerge, alignment, font, size and border settings."

 # Define a range of cells to use with the following commands.
 set range [$worksheet Range B2 C3]

 # Merge the range of cells into a single cell
 # (if there are values in cells other then the upper-left cell
 # then a warning dialog will be displayed)
 $range MergeCells [expr 1]

 # Set alignement and wrap properties for the cell range.

 # Set horizontal and vertical alignment for the cell range.
 $range HorizontalAlignment $align(horiz,left)
 $range VerticalAlignment $align(vert,top)

 # Turn on text wrapping for the cell range
 # Note: text wrapping doesn't work the way you would expect.
 # if text wrapping isn't turned on then the new line character
 # is ignored.
 $range WrapText [expr 1]

 # Set some font properties for the cell range.
 set font [$range Font]
 $font Bold [expr 1]
 $font Italic [expr 1]
 $font Name "Courier"
 $font Size [expr 18]

 # Set some border properties for the cell range.
 set borders [$range Borders]
 $borders Weight $border(weight,thick) 
 $borders Item $border(line,bottom) 

 # Set the width and height for the merged cell's row and column.
 set range [$worksheet Range B2]
 $range ColumnWidth 30
 $range RowHeight 100

 # Raise the first tab.
 set worksheet [$worksheets Item [expr 1]]
 $worksheet Select

 # Protect the content of the sheets in the workbook
 set sheetCount [$worksheets Count]
 for {set n 1} {${n}<=${sheetCount}} {incr n} {
     set worksheet [$worksheets Item [expr ${n}]]
     # Protect([Password], [DrawingObjects], [Contents], [Scenarios], [UserInterfaceOnly])
     ${worksheet} Protect [::tcom::na] [expr 1] [expr 1] [expr 1]
 }

 # Save the workbook to a file
 # 1) The extention '.xls' will automatically be added to the file name.
 # 2) If the file already exists then a dialog will be displayed.
 # 3) If 'newExcelFile' is already in use by Excel then a error will be returned.
 set FileName [file join [pwd] newExcelFile]
 if { [catch {$workbook SaveAs ${FileName}} errmsg] } {
     # not saved
     puts ${errmsg}
 } else {
     # ok
 }

 # Release the client connection to server
 set application {}

----
[Category Example]