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.
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 {}