SpreadScript Python Example

This is a simple Python 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 python

##
## 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.
##

import spreadscript
import os

# change this to point to your license
if (os.environ.has_key("SPREADSCRIPT_LICENSE")):
    SPREADSCRIPT_LICENSE = os.environ["SPREADSCRIPT_LICENSE"]
else:
    SPREADSCRIPT_LICENSE = "/opt/SpreadScript/SpreadScript-license.txt"

def dumpCell(sheet, row, col):
    type = sheet.getType(row, col)        # get the cell type
    value = sheet.getText(row, col)       # get formatted cell contents string
    typeString = spreadscript.getTypeString(type)     # get printable type
    print "%d, %d: %s -- %s" % (row, col, value, typeString),  # print cell
    if type == spreadscript.SS_FORMULA_CELL:          # if cell has a formula
        print " <%s>" % (sheet.getFormula(row, col))  # print the formula
    else:
        print

def dumpSheet1(sheet):
    # print the sheet by getting the sheet's dimensions and iterating over the
    # cells
    dims = sheet.getDimensions()
    print "******* dump sheet: %s ********" % (sheet.getName())
    for col in range(dims.getMinColumn(), 1 + dims.getMaxColumn()):
        for row in range(dims.getMinRow(), 1 + dims.getMaxRow()):
            dumpCell(sheet, row, col)

def dumpSheet2(sheet):
    # print the sheet by getting the sheet's dimensions and iterating over the
    # cells
    print "******* dump sheet 2 ********"
    dims = sheet.getDimensions()
    lastRow = dims.getMaxRow()
    firstCol = dims.getMinColumn()
    lastCol = dims.getMaxColumn()

    col = firstCol
    while col <= lastCol:
        row = dims.getMinRow()
        while row <= lastRow:
            dumpCell(sheet, row, col)
            row = row + 1
        col = col + 1

if __name__ == "__main__":
    # initialize the library, passing in the license file location
    spreadscript.init(SPREADSCRIPT_LICENSE)

    book = spreadscript.Workbook()    # create a blank Workbook object
    book.activate()                   # make it the active Workbook (important)
    book.open("sample.xls")           # open a spreadsheet
    
    sheet = book.getSheet(1)          # get the first (and only) sheet
    
    sheet.setFormula(3, 2, "=min(a1..a3)")  # put a formula in B3
    sheet.recalc()                          # recalculate (it's not automatic)

    # set default format to fixed
    sheet.setDefaultFormat(spreadscript.SS_FMT_FIXED)
    sheet.setDefaultPlaces(1)                  # with 1 place after decimal
    dumpSheet1(sheet)                          # print the sheet

    # now change default format to float
    sheet.setDefaultFormat(spreadscript.SS_FMT_FLOAT)
    sheet.setDefaultPlaces(3)                  # with 3 places after decimal
    dumpSheet1(sheet)                          # print the sheet

    # set borders on B2; only the Excel file format saves borders
    sheet.setBorder(2, 2, spreadscript.Border(spreadscript.SS_BORDER_THIN,
                                              spreadscript.SS_BORDER_MEDIUM,
                                              spreadscript.SS_BORDER_DASHED,
                                              spreadscript.SS_BORDER_DOTTED));

    book.save("sample_out.xs3", spreadscript.SS_NEXS_XS3)  # save as XS3 file
    book.save("sample_out.xls", spreadscript.SS_EXCEL_95)  # save as Excel file

    r = spreadscript.Range(sheet, 1, 1, 3, 3)
    r.exportHTML("sample_out.html")                   # save range to HTML table

    # Create a second book and put some numbers in it
    book = spreadscript.Workbook()  # create a new book
    book.activate()                 # make this book the active book (important)
    
    sheet = book.createSheet("The Sheet")  # create a blank sheet
    for i in range(5, 15):
        # put the even numbers in 10 through 28 in A5:A14
        sheet.setNumber(i, 1, 2 * i)
    dumpSheet1(sheet)                      # print the sheet
    
    del book  # only necessary if you wish to force deletion (not necessary here)
    
    print "Done!"