diff options
| -rw-r--r-- | Tools/ss1.py | 740 |
1 files changed, 740 insertions, 0 deletions
diff --git a/Tools/ss1.py b/Tools/ss1.py new file mode 100644 index 0000000..ccbb9fc --- /dev/null +++ b/Tools/ss1.py @@ -0,0 +1,740 @@ +"""SS1 -- a spreadsheet.""" + +import os +import re +import sys +import cgi +import rexec +from xml.parsers import expat + +LEFT, CENTER, RIGHT = "LEFT", "CENTER", "RIGHT" + +def ljust(x, n): + return x.ljust(n) +def center(x, n): + return x.center(n) +def rjust(x, n): + return x.rjust(n) +align2action = {LEFT: ljust, CENTER: center, RIGHT: rjust} + +align2xml = {LEFT: "left", CENTER: "center", RIGHT: "right"} +xml2align = {"left": LEFT, "center": CENTER, "right": RIGHT} + +align2anchor = {LEFT: "w", CENTER: "center", RIGHT: "e"} + +def sum(seq): + total = 0 + for x in seq: + if x is not None: + total += x + return total + +class Sheet: + + def __init__(self): + self.cells = {} # {(x, y): cell, ...} + self.rexec = rexec.RExec() + m = self.rexec.add_module('__main__') + m.cell = self.cellvalue + m.cells = self.multicellvalue + m.sum = sum + + def cellvalue(self, x, y): + cell = self.getcell(x, y) + if hasattr(cell, 'recalc'): + return cell.recalc(self.rexec) + else: + return cell + + def multicellvalue(self, x1, y1, x2, y2): + if x1 > x2: + x1, x2 = x2, x1 + if y1 > y2: + y1, y2 = y2, y1 + seq = [] + for y in range(y1, y2+1): + for x in range(x1, x2+1): + seq.append(self.cellvalue(x, y)) + return seq + + def getcell(self, x, y): + return self.cells.get((x, y)) + + def setcell(self, x, y, cell): + assert x > 0 and y > 0 + assert isinstance(cell, BaseCell) + self.cells[x, y] = cell + + def clearcell(self, x, y): + try: + del self.cells[x, y] + except KeyError: + pass + + def clearcells(self, x1, y1, x2, y2): + for xy in self.selectcells(x1, y1, x2, y2): + del self.cells[xy] + + def clearrows(self, y1, y2): + self.clearcells(0, y1, sys.maxint, y2) + + def clearcolumns(self, x1, x2): + self.clearcells(x1, 0, x2, sys.maxint) + + def selectcells(self, x1, y1, x2, y2): + if x1 > x2: + x1, x2 = x2, x1 + if y1 > y2: + y1, y2 = y2, y1 + return [(x, y) for x, y in self.cells + if x1 <= x <= x2 and y1 <= y <= y2] + + def movecells(self, x1, y1, x2, y2, dx, dy): + if dx == 0 and dy == 0: + return + if x1 > x2: + x1, x2 = x2, x1 + if y1 > y2: + y1, y2 = y2, y1 + assert x1+dx > 0 and y1+dy > 0 + new = {} + for x, y in self.cells: + cell = self.cells[x, y] + if hasattr(cell, 'renumber'): + cell = cell.renumber(x1, y1, x2, y2, dx, dy) + if x1 <= x <= x2 and y1 <= y <= y2: + x += dx + y += dy + new[x, y] = cell + self.cells = new + + def insertrows(self, y, n): + assert n > 0 + self.movecells(0, y, sys.maxint, sys.maxint, 0, n) + + def deleterows(self, y1, y2): + if y1 > y2: + y1, y2 = y2, y1 + self.clearrows(y1, y2) + self.movecells(0, y2+1, sys.maxint, sys.maxint, 0, y1-y2-1) + + def insertcolumns(self, x, n): + assert n > 0 + self.movecells(x, 0, sys.maxint, sys.maxint, n, 0) + + def deletecolumns(self, x1, x2): + if x1 > x2: + x1, x2 = x2, x1 + self.clearcells(x1, x2) + self.movecells(x2+1, 0, sys.maxint, sys.maxint, x1-x2-1, 0) + + def getsize(self): + maxx = maxy = 0 + for x, y in self.cells: + maxx = max(maxx, x) + maxy = max(maxy, y) + return maxx, maxy + + def reset(self): + for cell in self.cells.itervalues(): + if hasattr(cell, 'reset'): + cell.reset() + + def recalc(self): + self.reset() + for cell in self.cells.itervalues(): + if hasattr(cell, 'recalc'): + cell.recalc(self.rexec) + + def display(self): + maxx, maxy = self.getsize() + width, height = maxx+1, maxy+1 + colwidth = [1] * width + full = {} + # Add column heading labels in row 0 + for x in range(1, width): + full[x, 0] = text, alignment = colnum2name(x), RIGHT + colwidth[x] = max(colwidth[x], len(text)) + # Add row labels in column 0 + for y in range(1, height): + full[0, y] = text, alignment = str(y), RIGHT + colwidth[0] = max(colwidth[0], len(text)) + # Add sheet cells in columns with x>0 and y>0 + for (x, y), cell in self.cells.iteritems(): + if x <= 0 or y <= 0: + continue + if hasattr(cell, 'recalc'): + cell.recalc(self.rexec) + if hasattr(cell, 'format'): + text, alignment = cell.format() + assert isinstance(text, str) + assert alignment in (LEFT, CENTER, RIGHT) + else: + text = str(cell) + if isinstance(cell, str): + alignment = LEFT + else: + alignment = RIGHT + full[x, y] = (text, alignment) + colwidth[x] = max(colwidth[x], len(text)) + # Calculate the horizontal separator line (dashes and dots) + sep = "" + for x in range(width): + if sep: + sep += "+" + sep += "-"*colwidth[x] + # Now print The full grid + for y in range(height): + line = "" + for x in range(width): + text, alignment = full.get((x, y)) or ("", LEFT) + text = align2action[alignment](text, colwidth[x]) + if line: + line += '|' + line += text + print line + if y == 0: + print sep + + def xml(self): + out = ['<spreadsheet>'] + for (x, y), cell in self.cells.iteritems(): + if hasattr(cell, 'xml'): + cellxml = cell.xml() + else: + cellxml = '<value>%s</value>' % cgi.escape(cell) + out.append('<cell row="%s" col="%s">\n %s\n</cell>' % + (y, x, cellxml)) + out.append('</spreadsheet>') + return '\n'.join(out) + + def save(self, filename): + text = self.xml() + f = open(filename, "w") + f.write(text) + if text and not text.endswith('\n'): + f.write('\n') + f.close() + + def load(self, filename): + f = open(filename, 'r') + SheetParser(self).parsefile(f) + f.close() + +class SheetParser: + + def __init__(self, sheet): + self.sheet = sheet + + def parsefile(self, f): + parser = expat.ParserCreate() + parser.StartElementHandler = self.startelement + parser.EndElementHandler = self.endelement + parser.CharacterDataHandler = self.data + parser.ParseFile(f) + + def startelement(self, tag, attrs): + method = getattr(self, 'start_'+tag, None) + if method: + for key, value in attrs.iteritems(): + attrs[key] = str(value) # XXX Convert Unicode to 8-bit + method(attrs) + self.texts = [] + + def data(self, text): + text = str(text) # XXX Convert Unicode to 8-bit + self.texts.append(text) + + def endelement(self, tag): + method = getattr(self, 'end_'+tag, None) + if method: + method("".join(self.texts)) + + def start_cell(self, attrs): + self.y = int(attrs.get("row")) + self.x = int(attrs.get("col")) + + def start_value(self, attrs): + self.fmt = attrs.get('format') + self.alignment = xml2align.get(attrs.get('align')) + + start_formula = start_value + + def end_int(self, text): + try: + self.value = int(text) + except: + self.value = None + + def end_long(self, text): + try: + self.value = long(text) + except: + self.value = None + + def end_double(self, text): + try: + self.value = float(text) + except: + self.value = None + + def end_complex(self, text): + try: + self.value = complex(text) + except: + self.value = None + + def end_string(self, text): + try: + self.value = text + except: + self.value = None + + def end_value(self, text): + if isinstance(self.value, BaseCell): + self.cell = self.value + elif isinstance(self.value, str): + self.cell = StringCell(self.value, + self.fmt or "%s", + self.alignment or LEFT) + else: + self.cell = NumericCell(self.value, + self.fmt or "%s", + self.alignment or RIGHT) + + def end_formula(self, text): + self.cell = FormulaCell(text, + self.fmt or "%s", + self.alignment or RIGHT) + + def end_cell(self, text): + self.sheet.setcell(self.x, self.y, self.cell) + +class BaseCell: + __init__ = None # Must provide + """Abstract base class for sheet cells. + + Subclasses may but needn't provide the following APIs: + + cell.reset() -- prepare for recalculation + cell.recalc(rexec) -> value -- recalculate formula + cell.format() -> (value, alignment) -- return formatted value + cell.xml() -> string -- return XML + """ + +class NumericCell(BaseCell): + + def __init__(self, value, fmt="%s", alignment=RIGHT): + assert isinstance(value, (int, long, float, complex)) + assert alignment in (LEFT, CENTER, RIGHT) + self.value = value + self.fmt = fmt + self.alignment = alignment + + def recalc(self, rexec): + return self.value + + def format(self): + try: + text = self.fmt % self.value + except: + text = str(self.value) + return text, self.alignment + + def xml(self): + method = getattr(self, '_xml_' + type(self.value).__name__) + return '<value align="%s" format="%s">%s</value>' % ( + align2xml[self.alignment], + self.fmt, + method()) + + def _xml_int(self): + if -2**31 <= self.value < 2**31: + return '<int>%s</int>' % self.value + else: + return self._xml_long() + + def _xml_long(self): + return '<long>%s</long>' % self.value + + def _xml_float(self): + return '<double>%s</double>' % repr(self.value) + + def _xml_complex(self): + return '<complex>%s</double>' % repr(self.value) + +class StringCell(BaseCell): + + def __init__(self, text, fmt="%s", alignment=LEFT): + assert isinstance(text, (str, unicode)) + assert alignment in (LEFT, CENTER, RIGHT) + self.text = text + self.fmt = fmt + self.alignment = alignment + + def recalc(self, rexec): + return self.text + + def format(self): + return self.text, self.alignment + + def xml(self): + s = '<value align="%s" format="%s"><string>%s</string></value>' + return s % ( + align2xml[self.alignment], + self.fmt, + cgi.escape(self.text)) + +class FormulaCell(BaseCell): + + def __init__(self, formula, fmt="%s", alignment=RIGHT): + assert alignment in (LEFT, CENTER, RIGHT) + self.formula = formula + self.translated = translate(self.formula) + self.fmt = fmt + self.alignment = alignment + self.reset() + + def reset(self): + self.value = None + + def recalc(self, rexec): + if self.value is None: + try: + self.value = rexec.r_eval(self.translated) + except: + exc = sys.exc_info()[0] + if hasattr(exc, "__name__"): + self.value = exc.__name__ + else: + self.value = str(exc) + return self.value + + def format(self): + try: + text = self.fmt % self.value + except: + text = str(self.value) + return text, self.alignment + + def xml(self): + return '<formula align="%s" format="%s">%s</formula>' % ( + align2xml[self.alignment], + self.fmt, + self.formula) + + def renumber(self, x1, y1, x2, y2, dx, dy): + out = [] + for part in re.split('(\w+)', self.formula): + m = re.match('^([A-Z]+)([1-9][0-9]*)$', part) + if m is not None: + sx, sy = m.groups() + x = colname2num(sx) + y = int(sy) + if x1 <= x <= x2 and y1 <= y <= y2: + part = cellname(x+dx, y+dy) + out.append(part) + return FormulaCell("".join(out), self.fmt, self.alignment) + +def translate(formula): + """Translate a formula containing fancy cell names to valid Python code. + + Examples: + B4 -> cell(2, 4) + B4:Z100 -> cells(2, 4, 26, 100) + """ + out = [] + for part in re.split(r"(\w+(?::\w+)?)", formula): + m = re.match(r"^([A-Z]+)([1-9][0-9]*)(?::([A-Z]+)([1-9][0-9]*))?$", part) + if m is None: + out.append(part) + else: + x1, y1, x2, y2 = m.groups() + x1 = colname2num(x1) + if x2 is None: + s = "cell(%s, %s)" % (x1, y1) + else: + x2 = colname2num(x2) + s = "cells(%s, %s, %s, %s)" % (x1, y1, x2, y2) + out.append(s) + return "".join(out) + +def cellname(x, y): + "Translate a cell coordinate to a fancy cell name (e.g. (1, 1)->'A1')." + assert x > 0 # Column 0 has an empty name, so can't use that + return colnum2name(x) + str(y) + +def colname2num(s): + "Translate a column name to number (e.g. 'A'->1, 'Z'->26, 'AA'->27)." + s = s.upper() + n = 0 + for c in s: + assert 'A' <= c <= 'Z' + n = n*26 + ord(c) - ord('A') + 1 + return n + +def colnum2name(n): + "Translate a column number to name (e.g. 1->'A', etc.)." + assert n > 0 + s = "" + while n: + n, m = divmod(n-1, 26) + s = chr(m+ord('A')) + s + return s + +import Tkinter as Tk + +class SheetGUI: + + """Beginnings of a GUI for a spreadsheet. + + TO DO: + - clear multiple cells + - Select rows or columns + - Insert, clear, remove rows or columns + - Show new contents while typing + - Scroll bars + - Grow grid when window is grown + - Proper menus + - Undo, redo + - Cut, copy and paste + - Formatting and alignment + """ + + def __init__(self, filename="sheet1.xml", rows=10, columns=5): + """Constructor. + + Load the sheet from the filename argument. + Set up the Tk widget tree. + """ + # Create and load the sheet + self.filename = filename + self.sheet = Sheet() + if os.path.isfile(filename): + self.sheet.load(filename) + # Calculate the needed grid size + maxx, maxy = self.sheet.getsize() + rows = max(rows, maxy) + columns = max(columns, maxx) + # Create the widgets + self.root = Tk.Tk() + self.root.wm_title("Spreadsheet: %s" % self.filename) + self.beacon = Tk.Label(self.root, text="A1", + font=('helvetica', 16, 'bold')) + self.entry = Tk.Entry(self.root) + self.savebutton = Tk.Button(self.root, text="Save", + command=self.save) + self.cellgrid = Tk.Frame(self.root) + # Configure the widget lay-out + self.cellgrid.pack(side="bottom", expand=1, fill="both") + self.beacon.pack(side="left") + self.savebutton.pack(side="right") + self.entry.pack(side="left", expand=1, fill="x") + # Bind some events + self.entry.bind("<Return>", self.return_event) + self.entry.bind("<Shift-Return>", self.shift_return_event) + self.entry.bind("<Tab>", self.tab_event) + self.entry.bind("<Shift-Tab>", self.shift_tab_event) + # Now create the cell grid + self.makegrid(rows, columns) + # Select the top-left cell + self.currentxy = None + self.cornerxy = None + self.setcurrent(1, 1) + # Copy the sheet cells to the GUI cells + self.sync() + + def makegrid(self, rows, columns): + """Helper to create the grid of GUI cells. + + The edge (x==0 or y==0) is filled with labels; the rest is real cells. + """ + self.gridcells = {} + # Create the top row of labels + for x in range(1, columns+1): + self.cellgrid.grid_columnconfigure(x, minsize=64) + cell = Tk.Label(self.cellgrid, text=colnum2name(x), relief='raised') + cell.grid_configure(column=x, row=0, sticky='WE') + self.gridcells[x, 0] = cell + # Create the leftmost column of labels + for y in range(1, rows+1): + cell = Tk.Label(self.cellgrid, text=str(y), relief='raised') + cell.grid_configure(column=0, row=y, sticky='WE') + self.gridcells[0, y] = cell + # Create the real cells + for x in range(1, columns+1): + for y in range(1, rows+1): + cell = Tk.Label(self.cellgrid, relief='sunken', + bg='white', fg='black') + cell.grid_configure(column=x, row=y, sticky='NWSE') + self.gridcells[x, y] = cell + def helper(event, self=self, x=x, y=y): + self.setcurrent(x, y) + cell.bind("<Button-1>", helper) + def shelper(event, self=self, x=x, y=y): + self.setcorner(x, y) + cell.bind("<Shift-Button-1>", shelper) + + def save(self): + self.sheet.save(self.filename) + + def setcurrent(self, x, y): + "Make (x, y) the current cell." + if self.currentxy is not None: + self.change_cell() + self.clearfocus() + name = cellname(x, y) + cell = self.sheet.getcell(x, y) + if cell is None: + text = "" + elif isinstance(cell, FormulaCell): + text = '=' + cell.formula + else: + text, alignment = cell.format() + self.beacon['text'] = name + self.entry.delete(0, 'end') + self.entry.insert(0, text) + self.entry.selection_range(0, 'end') + self.entry.focus_set() + self.currentxy = x, y + self.cornerxy = None + gridcell = self.gridcells.get(self.currentxy) + if gridcell is not None: + gridcell['bg'] = 'lightBlue' + + def setcorner(self, x, y): + if self.currentxy is None or self.currentxy == (x, y): + self.setcurrent(x, y) + return + self.clearfocus() + self.cornerxy = x, y + x1, y1 = self.currentxy + x2, y2 = self.cornerxy or self.currentxy + if x1 > x2: + x1, x2 = x2, x1 + if y1 > y2: + y1, y2 = y2, y1 + for x in range(x1, x2+1): + for y in range(y1, y2+1): + gridcell = self.gridcells.get((x, y)) + if gridcell is not None: + gridcell['bg'] = 'lightBlue' + name1 = cellname(*self.currentxy) + name2 = cellname(*self.cornerxy) + self.beacon['text'] = "%s:%s" % (name1, name2) + + + def clearfocus(self): + if self.currentxy is not None: + x1, y1 = self.currentxy + x2, y2 = self.cornerxy or self.currentxy + if x1 > x2: + x1, x2 = x2, x1 + if y1 > y2: + y1, y2 = y2, y1 + for x in range(x1, x2+1): + for y in range(y1, y2+1): + gridcell = self.gridcells.get((x, y)) + if gridcell is not None: + gridcell['bg'] = 'white' + + def return_event(self, event): + "Callback for the Return key." + self.change_cell() + x, y = self.currentxy + self.setcurrent(x, y+1) + return "break" + + def shift_return_event(self, event): + "Callback for the Return key with Shift modifier." + self.change_cell() + x, y = self.currentxy + self.setcurrent(x, max(1, y-1)) + return "break" + + def tab_event(self, event): + "Callback for the Tab key." + self.change_cell() + x, y = self.currentxy + self.setcurrent(x+1, y) + return "break" + + def shift_tab_event(self, event): + "Callback for the Tab key with Shift modifier." + self.change_cell() + x, y = self.currentxy + self.setcurrent(max(1, x-1), y) + return "break" + + def change_cell(self): + "Set the current cell from the entry widget." + x, y = self.currentxy + text = self.entry.get() + cell = None + if text.startswith('='): + cell = FormulaCell(text[1:]) + else: + for cls in int, long, float, complex: + try: + value = cls(text) + except: + continue + else: + cell = NumericCell(value) + break + if cell is None and text: + cell = StringCell(text) + if cell is None: + self.sheet.clearcell(x, y) + else: + self.sheet.setcell(x, y, cell) + self.sync() + + def sync(self): + "Fill the GUI cells from the sheet cells." + self.sheet.recalc() + for (x, y), gridcell in self.gridcells.iteritems(): + if x == 0 or y == 0: + continue + cell = self.sheet.getcell(x, y) + if cell is None: + gridcell['text'] = "" + else: + if hasattr(cell, 'format'): + text, alignment = cell.format() + else: + text, alignment = str(cell), LEFT + gridcell['text'] = text + gridcell['anchor'] = align2anchor[alignment] + + +def test_basic(): + "Basic non-gui self-test." + import os + a = Sheet() + for x in range(1, 11): + for y in range(1, 11): + if x == 1: + cell = NumericCell(y) + elif y == 1: + cell = NumericCell(x) + else: + c1 = cellname(x, 1) + c2 = cellname(1, y) + formula = "%s*%s" % (c1, c2) + cell = FormulaCell(formula) + a.setcell(x, y, cell) +## if os.path.isfile("sheet1.xml"): +## print "Loading from sheet1.xml" +## a.load("sheet1.xml") + a.display() + a.save("sheet1.xml") + +def test_gui(): + "GUI test." + g = SheetGUI() + g.root.mainloop() + +if __name__ == '__main__': + #test_basic() + test_gui() |