SpreadScript Python Example

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