Simple Web-based Spreadsheet using SpreadScript

A sample application written in Python using SpreadScript to present a form-based interactive spreadsheet on the web. 

#!/usr/bin/env python

# point to the directory with the SpreadScript Python package files
import sys
sys.path.append('/home/tkm/lib/python')

import cgi
import spreadscript
import os
import string
import tempfile
import traceback

TEMPXLS = '/spread_temp.xls'
TEMPXS3 = '/spread_temp.xs3'
TEMPCSV = '/spread_temp.csv'

# Send the standard HTML header.
def sendHeader():
    print 'Content-type: text/html'
    print

# Show a tag for SpreadScript.
def showGreyTrout():
    print '''<table border="0" cellspacing="0" cellpadding="0" align="right">
             <tr><td>
             <font size="-1">Powered by
             <a href="http://www.greytrout.com/spreadscript.html">SpreadScript</a> from
             <a href="http://www.greytrout.com/">GreyTrout Software</a>.<br>
             <div align="right">
             <a href="http://www.greytrout.com/spread.html">spread.py</a></div>
             </font></td></tr></table>'''

# Creates the form used to create a new spread sheet.
def createNewSheet(form, numRows, numCols, level):
    print '<%s>Create a new spreadsheet</%s>' % (level, level)
    print '''<form method="post" action="/cgi-bin/spread.sh">
             <table border="0" cellspacing="0" cellpadding="5">
             <tr>
             <td>Number of rows: </td>
             <td><input type="text" name="numRows" value="%s"></td></tr>''' % \
          (numRows, )
    print '''<tr>
             <td>Number of columns: </td>
             <td><input type="text" name="numCols" value="%s"></td></tr>''' % \
          (numCols, )
    print '''<tr><td><input type="submit" name="action" value="Create">
             </td></tr></table>
             </form>'''

# Show the user a form to create a new spreadsheet.
def showNewSheet(form):
    sendHeader()
    print '''<html><title>Create a New Spreadsheet</title>
             <body bgcolor="white">'''

    showGreyTrout()
    createNewSheet(form, 4, 3, 'h1')

    print '</body></html>'

# Show the current spreadsheet with the value and formula for each cell.
def showSheet(form):
    sendHeader()
    print '<html><title>Spreadsheet</title> <body bgcolor="white">'

    showGreyTrout()

    print 'NOTE:  Use .. rather than : for ranges.  For example, a1..a4.<br>'
    print 'NOTE:  Begin formulas with =.  For example, =sum(a1..a4).<p>'

    # get the number of rows and columns from the hidden fields in the form
    numRows = 0
    numCols = 0
    if form.has_key('numRows'):
        numRows = int(form['numRows'].value)
    if form.has_key('numCols'):
        numCols = int(form['numCols'].value)

    if numRows == 0 or numCols == 0:
        print '<b>Error:</b> Invalid dimensions: row = %d, column = %d<p>' % \
              (numRows, numCols)
    else:
        print '<h1>Spreadsheet</h1>'

        # create a spreadsheet
        spreadscript.init("/home/tkm/bin/SpreadScript-license.txt")
        book = spreadscript.Workbook()
        book.activate()
        sheet = book.createSheet("Sample")

        # put the formulas and values in the spreadsheet and recalculate
        if form.has_key('action') and form['action'].value == 'Recalc':
            makeSheet(form, numRows, numCols, sheet)
        sheet.recalc()

        # display the spreadsheet as a grid
        showGrid(form, numRows, numCols, sheet)

        # save worksheet
        # Excel file
        try:
            book.save('/tmp' + TEMPXLS, spreadscript.SS_EXCEL_95)
            os.chmod('/tmp' + TEMPXLS, 0644)
        except:
            pass  # nothing to do for exception

        # NExS XS3 file
        try:
            book.save('/tmp' + TEMPXS3, spreadscript.SS_NEXS_XS3)
            os.chmod('/tmp' + TEMPXS3, 0644)
        except:
            pass  # nothing to do for exception

        # Comma separated values (csv)
        try:
            dims = sheet.getDimensions()
            dims.exportCSV('/tmp' + TEMPCSV)
            os.chmod('/tmp' + TEMPCSV, 0644)
        except:
            pass  # nothing to do for exception

    # form for creating a new spreadsheet
    print '<hr>'
    createNewSheet(form, numRows, numCols, 'h2')

    print '<hr>'
    showGreyTrout()
    
    print '</body>'
    print '</html>'

# Get the contents from each cell (text, number, or formula) entered into the
# form's text boxes.
def getCellContents(form, r, c):
    cell = spreadscript.rcToText(r, c)
    contents = None
    if form.has_key(cell):
        contents = string.strip(form[cell].value)
    return contents

# Returns 1 if s is a number, 0 otherwise.
# There's probably a better way....
def isNumber(s):
    try:
        float(s)
        return 1  # no exception, must be a number
    except:
        return 0  # threw exception, it's not a number

# Makes a spreadsheet using the contents of the grid of text boxes.  It fills
# in the SpreadScript spreadsheet passed in as sheet.
def makeSheet(form, numRows, numCols, sheet):
    for r in range(1, numRows + 1):
        for c in range(1, numCols + 1):
            contents = getCellContents(form, r, c)
            if contents and contents[0] == '=':   # formula
                sheet.setFormula(r, c, contents)
            elif contents and isNumber(contents): # number
                sheet.setNumber(r, c, float(contents))
            elif contents:                        # text
                sheet.setText(r, c, contents)

# Show the buttons and links for recalculating, etc.
def showGridCmdArea(showFormulas, showValues, showBoth):
    # recalc button
    print '''<table width="100%" cellspacing="0" cellpadding="3"><tr>
             <td valign="middle"><input type="submit" name="action"
                                  value="Recalc"></td><td valign="middle">'''

    # option radio buttons for whether to show formulas, values or both
    print '''<table align="right" border="0" cellspacing="0" cellpadding="0">
             <tr><td>Show: &nbsp;
             <input type="radio" name="showwhat" value="showformulas" '''
    if showFormulas:
        print 'checked'
    print '> formulas &nbsp;'

    print '<input type="radio" name="showwhat" value="showvalues" '
    if showValues:
        print 'checked'
    print '> values &nbsp;'

    print '<input type="radio" name="showwhat" value="showboth" '
    if showBoth:
        print 'checked'
    print '> both'

    # links to files for downloading
    print '''&nbsp;&nbsp;&nbsp;&nbsp; Save as: &nbsp;
             <a href="%s">Excel file</a> &nbsp;
             <a href="%s">NExS XS3 file</a> &nbsp;
             <a href="%s">CSV file</a>
             </td></tr></table>''' % (TEMPXLS, TEMPXS3, TEMPCSV)

    print '</td></tr></table>'

# show the spreadsheet as an HTML table in a form
def showGrid(form, numRows, numCols, sheet):
    # determine whether to show formulas, values, or both
    showFormulas = 0
    showValues = 0
    showBoth = 0
    if form.has_key('showwhat'):
        if form['showwhat'].value == 'showformulas':
            showFormulas = 1
        elif form['showwhat'].value == 'showvalues':
            showValues = 1
        elif form['showwhat'].value == 'showboth':
            showBoth = 1
    else:
        showBoth = 1

    # the spreadsheet is a table inside of a form; use POST rather than GET
    # due to the potentially large amount of data;
    # hidden fields contain the size of the spreadsheet
    print '''<form method="post" action="/cgi-bin/spread.sh">
             <input type="hidden" name="numRows" value="%d">
             <input type="hidden" name="numCols" value="%d">''' % \
          (numRows, numCols)

    # show buttons
    showGridCmdArea(showFormulas, showValues, showBoth)
    
    # the spreadsheet as a table and the column headings
    print '''<table border="1" cellspacing="0" cellpadding="2">
             <tr><td bgcolor="#CCCCCC">&nbsp;</td>'''
    for c in range(1, numCols + 1):
        print '<td bgcolor="#CCCCCC" align="center">%s</td>' % \
              spreadscript.rcToText(1, c)[:-1]  # just keep the column label
    print '</tr>'

    # show values and formulas for each cell in a spreadsheet-style table
    for r in range(1, numRows + 1):
        print '<tr><td bgcolor="#CCCCCC" align="center">%d:</td>' % (r, )
        for c in range(1, numCols + 1):
            print '<td>' ,

            # optionally show values
            if showValues or showBoth:
                print sheet.getText(r, c) + '<br>' ,

            # optionally show formulas; formulas must always reside in the
            # spreadsheet
            print '<input type=' ,
            if showFormulas or showBoth:
                print '"text" size="13" ' ,    # formulas in text boxes
            else:
                print '"hidden" ' ,            # formulas are hidden
            print 'name="%s" ' % (spreadscript.rcToText(r, c), ) ,
            contents = getCellContents(form, r, c)
            if contents:
                print 'value="%s"></td>' % (contents, )
            else:
                print '></td>'
        print '</tr>'

    print '</table>'

    # show buttons, but don't check any of these (otherwise, may get a
    # dictionary for it)
    showFormulas = showValues = showBoth = 0
    showGridCmdArea(showFormulas, showValues, showBoth)
    
    print '</form>'

### Main
if __name__ == '__main__':
    # for debugging
    sys.stderr = sys.stdout

    # kick off the script
    try:
        form = cgi.FieldStorage()

        if form.has_key('action') and form['action'].value == 'Recalc' or \
           form.has_key('action') and form['action'].value == 'Create':
            showSheet(form)
        else:  # new sheet
            showNewSheet(form)
    except:
        # for debugging
        sendHeader()
        print '<p><b>An exception occurred.</b>\n<pre>'
        traceback.print_exc()
        print '</pre>'