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.
Georgios Petasis wrote this simple example on comp.lang.tcl:
package require tcom set excel [::tcom::ref createobj Excel.Application] set workbooks [$excel Workbooks] set workbook [$workbooks Open filename.xls] $workbook SaveAs filename.csv
A follow up post indicates that, rather than creating a csv formatted file, this script creates a binary excel file, with a name that ends in csv. Not what the user was hoping to get.
PO 2007/06/12 - Script to convert from binary XLS to ASCII CSV file:
You need to place an Excel file named test.xls in the directory containing your Tcl script.
package require tcom set XlFileFormat(xlCSV) [expr 6] set excel [::tcom::ref createobj Excel.Application] set workbooks [$excel Workbooks] # Always use absolute and native file names. set workbook [$workbooks Open [file nativename [file join [pwd] test.xls]]] # Switch off displaying alerts about already existing CSV files. # Switch them on afterwards. $excel DisplayAlerts False $workbook SaveAs [file nativename [file join [pwd] test.csv]] $XlFileFormat(xlCSV) $excel DisplayAlerts True # Do not forget to quit Excel. Again we have to switch off the alerts. $excel DisplayAlerts False $excel Quit
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 startup. 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 lose 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.
# Set the path to your excel file.
set excelFilePath "book1.xlsx"
set excelApp [::tcom::ref createobject Excel.Application]
set workbooks [$excelApp Workbooks]
set workbook [$workbooks Open [file nativename [file join [pwd] $excelFilePath] ] ]
set worksheets [$workbook Worksheets]
set worksheet [$worksheets Item [expr 1]]
set cells [$worksheet Cells]
# Read all the values in column A
set rowCount 1
set end 0
while { $end == 0 } {
set columnValue [[$cells Item $rowCount A] Value]
if { $columnValue == ""} {
set end 1
continue
}
puts $columnValue
incr rowCount
}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 2 3} {
foreach column {A B C} {
$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. Turn off alarts
# so the warnings won't be displayed.
$application DisplayAlerts [expr 0]
$range MergeCells [expr 1]
$application DisplayAlerts [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 {}Speeding up Excel Writes
For small reports, inputing cells via the Cells collection (used in the above example) works well. As your reports get larger -- several hundred rows or more -- you may find performance lagging. A report with more than 800 rows, for me, took about a minute to complete. It turns out, though, that by using the clipboard along with the Paste worksheet method that you can produce reports very quickly. In fact, using this technique, you can completely bypass the need for any iteration when writing data to your spreadsheet.
package require tcom
package require csv
set lst {{1 2 3} {4 5 6} {hello world etc}}
# Assume you have a worksheet (ws) variable already established.
# Any range here will do.
set range [$ws Range "A1"]
# The below method call, even though it appears to operate only on the given
# range, will (at least for Office XP versions of Excel) operate on the entire
# worksheet that the range belongs to. TextToColumns tells Excel to expect
# pasted text to assume a certain format -- the above code would look like
# Range("A1").TextToColumns Comma:=True
# in visual basic, if you are following along with the help section in Excel.
$range -namedarg TextToColumns Comma 1
# Commented out below is my personal preference -- Excel expects fields to be
# double-quoted if they contain a comma character or whitespace, but csv::join*
# will only double quote fields with commas in them. Using vertical bars, which
# are rare in my circumstances, the matter is avoided completely.
#$range -namedarg TextToColumns Other 1 OtherChar "|"
# This will append a (possibly mammoth) string of csv lines to the clipboard.
# csv::joinlist works on a list of lists of values, while csv::join would produce
# just one row from one list of values.
clipboard clear
clipboard append [csv::joinlist $lst ","] ;# Use "|" if you use the Other named arguments...
# Selecting the range will tell Excel where to start the paste
$range Select
$ws Paste
# And that's it! If you need to surround your cells with borders, you'll have to look
# into the Borders collection and set the LineStyle property of the individual
# borders therein. For that, you would need the entire range of your pasted dataset,
# which I'll leave up to you to calculate. For us, the code would be...
set range [$ws Range "A1" "C3"]
set borders [$range Borders]
# I don't know the exact numeric values of the below constants -- to be honest, I use
# tcom::import and look up the values that way -- but you get the idea. Here we set
# the inside borders between the cells.
[$borders Item [expr xlInsideHorizontal]] LineStyle xlContinuous
[$borders Item [expr xlInsideVertical]] LineStyle xlContinuous
# Here we set borders around the entire range.
$range BorderAround xlContinuousI tried the above example and ended at an errormsg. It seems, that Excel 2003 needs all arguments for methods like TextToColumns. Also one should select the range of the pasted data before using the method. The following code worked for me:
# other code here $worksheet Paste # creating the Fieldinfo matrix. One could also use foreach, if the number of columns is unkown. # Column numbers start with 1. 2 = format as text. # Excel example: FieldInfo = Array( Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), ... ) set FieldInf list [list 1 2 list 2 2 list 3 2 list 4 2 list 5 2 list 6 2 list 7 2 list 8 2 [list 9 2] $range Select $range -namedarg TextToColumns Destination ::tcom::na DataType ::tcom::na TextQualifier ::tcom::na ConsecutiveDelimiter expr 1 Tab ::tcom::na Semicolon expr 1 Comma ::tcom::na Space ::tcom::na Other ::tcom::na FieldInfo $FieldInf DecimalSeparator ::tcom::na ThousandsSeparator ::tcom::na TrailingMinusNumbers expr 1 # other code here
PO 2011/03/29 - The following code works for me with Excel 2007.
It uses the twapi extension to copy the data to the clipboard, so you do not need Tk. The data is stored in the clipboard in CSV format, so the TextToColumns conversion is not necessary.
package require tcom
package require twapi
package require csv
set lst {{1 2 3} {4 5 6} {"hello; world" "etc" "next"}}
set retVal catch {::tcom::ref getactiveobject "Excel.Application"} appId
if { $retVal != 0 } {
set retVal catch {::tcom::ref createobject "Excel.Application"} appId
if { $retVal != 0 } {
error "Cannot start Excel application"
}
}
$appId Visible expr 1
set workbooks $appId Workbooks
set workbookId [$workbooks Add [expr -4167]
set worksheets $workbookId Worksheets
set worksheetId [$worksheets Item [expr 1]
set csvFmt twapi::register_clipboard_format "Csv"
twapi::open_clipboard
twapi::empty_clipboard
twapi::write_clipboard $csvFmt csv::joinlist $lst ";"
twapi::close_clipboard
# If not waiting at least a little while,
# on some computers the Paste method fails.
after 10
$worksheetId Paste
PO 2011/07/17 - The TcomOffice extension offers high-level procedures for Excel automation based on Tcom.
PO 2012/12/23 - TcomOffice has been superseded by CAWT, the COM Automation With Tcl package. CAWT now uses TWAPI for COM access.
APE 2018/02/27 - trying to insert a picture in Excel, I list below a way to discover the tcom commands that helped me this evening.
1) first open Excel and record a macro of what you want to do (Visual Basic opens with Alt+F11) in my case something like this :
Range("G8").Select
ActiveSheet.Pictures.Insert("C:\dir1\dir2\picture.gif").Select
2) create the tcom handlers for the different classes
set appId tcom::ref createobject "Excel.Application" set workbooks $appId Workbooks set workbook $workbooks Add set worksheets $workbook Worksheets set worksheet $worksheets Item 1 set pic $worksheet Pictures
3) finally call the command Insert
set range $worksheet Range G8 $range Select $pic Insert file nativename "C:/dir1/dir2/picture.gif"
hope this can be usefull