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.
Read Example
Write Example
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 warning dialogs to be displayed).
set sheetCount [$worksheets Count]
for {set n ${sheetCount}} {${n}>1} {incr n -1} {
set worksheet [$worksheets Item [expr ${n}]]
${worksheet} Delete
}
# 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
# 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 sfile [file join [pwd] newExcelFile]
if { [catch {$workbook SaveAs ${sfile}} errmsg] } {
# not saved
puts ${errmsg}
} else {
# ok
}
# Exit from Excel
$application Quit
# For some reason you have to exit the process that is running
# Tcom to release the connection to Excel. If you don't exit
# then Excel will hang when you try to open a document with Excel.
exit