SpreadScript Tcl Example

This is a simple Tcl script using SpreadScript to change and print the contents of an Excel spreadsheet in order to demonstrate some of the features available in SpreadScript. The most recent version of this script is included in the distribution in the "samples" directory.
#!/usr/bin/env tclsh

##
## SpreadScript example code
##
## This script demonstrates some of the functions available in the API.
##
## NOTE:	 To use this script, you may need to change the path to the license
##			 below.
##

package require spreadscript

# change this to point to your license
if {[llength [array names env SPREADSCRIPT_LICENSE]] != 0} {
	 set SPREADSCRIPT_LICENSE $env(SPREADSCRIPT_LICENSE)
} else {
	 set SPREADSCRIPT_LICENSE "/opt/SpreadScript/SpreadScript-license.txt"
}

proc dumpCell {sheet row col} {
	 # need access to this global constant
	 global SS_FORMULA_CELL

	 # get the cell type
	 set type [sheet getType $row $col]
	 # get formatted cell contents as a string
	 set value [sheet getText $row $col]
	 # get printable type
	 set typeString [getTypeString $type]
	 # print cell
	 puts -nonewline [format "%d, %d: %s -- %s" $row $col $value $typeString]
	 
	 # if cell has a formula
	 if {$type == $SS_FORMULA_CELL} {
		  # print the formula
		  puts [format "  <%s>" [sheet getFormula $row $col]]
	 } else {
		  puts ""
	 }
}

proc dumpSheet {sheet} {
	 # print the sheet by getting the sheet's dimensions and iterating over the
	 # cells
	 puts [format "******* dump sheet: %s ********" [sheet getName]]

	 Range dims -this [sheet getDimensions]
	 set firstRow [dims getMinRow]
	 set lastRow [dims getMaxRow]
	 set firstCol [dims getMinColumn]
	 set lastCol [dims getMaxColumn]

	 set col $firstCol
	 while {$col <= $lastCol} {
		  set row $firstRow
		  while {$row <= $lastRow} {
				dumpCell sheet $row $col
				incr row
		  }
		  incr col
	 }

	 # destroy the dims Range
	 rename dims ""
}


#### main

# initialize the library, passing in the license file location
init $SPREADSCRIPT_LICENSE

# create a blank Workbook object
Workbook book
# make it the active Workbook (important)
book activate
# open a spreadsheet
book open "sample.xls"

# get the first (and only) sheet
Worksheet sheet -this [book getSheet 1]
	 
# put a formula in B3
sheet setFormula 3 2 "=min(a1..a3)"
# recalculate (it's not automatic)
sheet recalc

# change default format to fixed
sheet setDefaultFormat $SS_FMT_FIXED
# with 1 place after decimal
sheet setDefaultPlaces 1
# print the sheet
dumpSheet sheet

# now change default to float
sheet setDefaultFormat $SS_FMT_FLOAT
# with 3 places after decimal
sheet setDefaultPlaces 3
# print the sheet
dumpSheet sheet

# set borders on B2; only the Excel file format saves borders
set border [Border border $SS_BORDER_THIN $SS_BORDER_MEDIUM  \
                          $SS_BORDER_DASHED $SS_BORDER_DOTTED]
sheet setBorder 2 2 $border

# save it as an XS3 file
book save "sample_out.xs3" $SS_NEXS_XS3
# save it as an Excel file
book save "sample_out.xls" $SS_EXCEL_95
# save range to HTML table
set range [Range range [sheet cget -this]	 1 1 3 3]
range exportHTML "sample_out.html"

# delete the sheet command
rename sheet ""

# delete the book command
rename book ""

# Create a second book and put some numbers in it
Workbook book
# make this book the active book (important)
book activate

# create a blank sheet
Worksheet sheet -this [book createSheet "The Sheet"]

# put the even numbers in 10 through 28 in A5:A14
set i 5
while {$i < 15} {
	 sheet setNumber $i 1 [expr 2 * $i]
	 incr i
}
# print the sheet
dumpSheet sheet

# only necessary if you wish to force deletion (not necessary here)
rename book ""

puts "Done!"