SpreadScript Tcl Example

#!/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!"