1 """SS1 -- a spreadsheet."""
8 from xml.parsers import expat
10 LEFT, CENTER, RIGHT = "LEFT", "CENTER", "RIGHT"
18 align2action = {LEFT: ljust, CENTER: center, RIGHT: rjust}
20 align2xml = {LEFT: "left", CENTER: "center", RIGHT: "right"}
21 xml2align = {"left": LEFT, "center": CENTER, "right": RIGHT}
23 align2anchor = {LEFT: "w", CENTER: "center", RIGHT: "e"}
35 self.cells = {} # {(x, y): cell, ...}
36 self.rexec = rexec.RExec()
37 m = self.rexec.add_module('__main__')
38 m.cell = self.cellvalue
39 m.cells = self.multicellvalue
42 def cellvalue(self, x, y):
43 cell = self.getcell(x, y)
44 if hasattr(cell, 'recalc'):
45 return cell.recalc(self.rexec)
49 def multicellvalue(self, x1, y1, x2, y2):
55 for y in range(y1, y2+1):
56 for x in range(x1, x2+1):
57 seq.append(self.cellvalue(x, y))
60 def getcell(self, x, y):
61 return self.cells.get((x, y))
63 def setcell(self, x, y, cell):
64 assert x > 0 and y > 0
65 assert isinstance(cell, BaseCell)
66 self.cells[x, y] = cell
68 def clearcell(self, x, y):
74 def clearcells(self, x1, y1, x2, y2):
75 for xy in self.selectcells(x1, y1, x2, y2):
78 def clearrows(self, y1, y2):
79 self.clearcells(0, y1, sys.maxint, y2)
81 def clearcolumns(self, x1, x2):
82 self.clearcells(x1, 0, x2, sys.maxint)
84 def selectcells(self, x1, y1, x2, y2):
89 return [(x, y) for x, y in self.cells
90 if x1 <= x <= x2 and y1 <= y <= y2]
92 def movecells(self, x1, y1, x2, y2, dx, dy):
93 if dx == 0 and dy == 0:
99 assert x1+dx > 0 and y1+dy > 0
101 for x, y in self.cells:
102 cell = self.cells[x, y]
103 if hasattr(cell, 'renumber'):
104 cell = cell.renumber(x1, y1, x2, y2, dx, dy)
105 if x1 <= x <= x2 and y1 <= y <= y2:
111 def insertrows(self, y, n):
113 self.movecells(0, y, sys.maxint, sys.maxint, 0, n)
115 def deleterows(self, y1, y2):
118 self.clearrows(y1, y2)
119 self.movecells(0, y2+1, sys.maxint, sys.maxint, 0, y1-y2-1)
121 def insertcolumns(self, x, n):
123 self.movecells(x, 0, sys.maxint, sys.maxint, n, 0)
125 def deletecolumns(self, x1, x2):
128 self.clearcells(x1, x2)
129 self.movecells(x2+1, 0, sys.maxint, sys.maxint, x1-x2-1, 0)
133 for x, y in self.cells:
139 for cell in self.cells.itervalues():
140 if hasattr(cell, 'reset'):
145 for cell in self.cells.itervalues():
146 if hasattr(cell, 'recalc'):
147 cell.recalc(self.rexec)
150 maxx, maxy = self.getsize()
151 width, height = maxx+1, maxy+1
152 colwidth = [1] * width
154 # Add column heading labels in row 0
155 for x in range(1, width):
156 full[x, 0] = text, alignment = colnum2name(x), RIGHT
157 colwidth[x] = max(colwidth[x], len(text))
158 # Add row labels in column 0
159 for y in range(1, height):
160 full[0, y] = text, alignment = str(y), RIGHT
161 colwidth[0] = max(colwidth[0], len(text))
162 # Add sheet cells in columns with x>0 and y>0
163 for (x, y), cell in self.cells.iteritems():
166 if hasattr(cell, 'recalc'):
167 cell.recalc(self.rexec)
168 if hasattr(cell, 'format'):
169 text, alignment = cell.format()
170 assert isinstance(text, str)
171 assert alignment in (LEFT, CENTER, RIGHT)
174 if isinstance(cell, str):
178 full[x, y] = (text, alignment)
179 colwidth[x] = max(colwidth[x], len(text))
180 # Calculate the horizontal separator line (dashes and dots)
182 for x in range(width):
185 sep += "-"*colwidth[x]
186 # Now print The full grid
187 for y in range(height):
189 for x in range(width):
190 text, alignment = full.get((x, y)) or ("", LEFT)
191 text = align2action[alignment](text, colwidth[x])
200 out = ['<spreadsheet>']
201 for (x, y), cell in self.cells.iteritems():
202 if hasattr(cell, 'xml'):
205 cellxml = '<value>%s</value>' % cgi.escape(cell)
206 out.append('<cell row="%s" col="%s">\n %s\n</cell>' %
208 out.append('</spreadsheet>')
209 return '\n'.join(out)
211 def save(self, filename):
213 f = open(filename, "w")
215 if text and not text.endswith('\n'):
219 def load(self, filename):
220 f = open(filename, 'r')
221 SheetParser(self).parsefile(f)
226 def __init__(self, sheet):
229 def parsefile(self, f):
230 parser = expat.ParserCreate()
231 parser.StartElementHandler = self.startelement
232 parser.EndElementHandler = self.endelement
233 parser.CharacterDataHandler = self.data
236 def startelement(self, tag, attrs):
237 method = getattr(self, 'start_'+tag, None)
239 for key, value in attrs.iteritems():
240 attrs[key] = str(value) # XXX Convert Unicode to 8-bit
244 def data(self, text):
245 text = str(text) # XXX Convert Unicode to 8-bit
246 self.texts.append(text)
248 def endelement(self, tag):
249 method = getattr(self, 'end_'+tag, None)
251 method("".join(self.texts))
253 def start_cell(self, attrs):
254 self.y = int(attrs.get("row"))
255 self.x = int(attrs.get("col"))
257 def start_value(self, attrs):
258 self.fmt = attrs.get('format')
259 self.alignment = xml2align.get(attrs.get('align'))
261 start_formula = start_value
263 def end_int(self, text):
265 self.value = int(text)
269 def end_long(self, text):
271 self.value = long(text)
275 def end_double(self, text):
277 self.value = float(text)
281 def end_complex(self, text):
283 self.value = complex(text)
287 def end_string(self, text):
293 def end_value(self, text):
294 if isinstance(self.value, BaseCell):
295 self.cell = self.value
296 elif isinstance(self.value, str):
297 self.cell = StringCell(self.value,
299 self.alignment or LEFT)
301 self.cell = NumericCell(self.value,
303 self.alignment or RIGHT)
305 def end_formula(self, text):
306 self.cell = FormulaCell(text,
308 self.alignment or RIGHT)
310 def end_cell(self, text):
311 self.sheet.setcell(self.x, self.y, self.cell)
314 __init__ = None # Must provide
315 """Abstract base class for sheet cells.
317 Subclasses may but needn't provide the following APIs:
319 cell.reset() -- prepare for recalculation
320 cell.recalc(rexec) -> value -- recalculate formula
321 cell.format() -> (value, alignment) -- return formatted value
322 cell.xml() -> string -- return XML
325 class NumericCell(BaseCell):
327 def __init__(self, value, fmt="%s", alignment=RIGHT):
328 assert isinstance(value, (int, long, float, complex))
329 assert alignment in (LEFT, CENTER, RIGHT)
332 self.alignment = alignment
334 def recalc(self, rexec):
339 text = self.fmt % self.value
341 text = str(self.value)
342 return text, self.alignment
345 method = getattr(self, '_xml_' + type(self.value).__name__)
346 return '<value align="%s" format="%s">%s</value>' % (
347 align2xml[self.alignment],
352 if -2**31 <= self.value < 2**31:
353 return '<int>%s</int>' % self.value
355 return self._xml_long()
358 return '<long>%s</long>' % self.value
360 def _xml_float(self):
361 return '<double>%s</double>' % repr(self.value)
363 def _xml_complex(self):
364 return '<complex>%s</double>' % repr(self.value)
366 class StringCell(BaseCell):
368 def __init__(self, text, fmt="%s", alignment=LEFT):
369 assert isinstance(text, (str, unicode))
370 assert alignment in (LEFT, CENTER, RIGHT)
373 self.alignment = alignment
375 def recalc(self, rexec):
379 return self.text, self.alignment
382 s = '<value align="%s" format="%s"><string>%s</string></value>'
384 align2xml[self.alignment],
386 cgi.escape(self.text))
388 class FormulaCell(BaseCell):
390 def __init__(self, formula, fmt="%s", alignment=RIGHT):
391 assert alignment in (LEFT, CENTER, RIGHT)
392 self.formula = formula
393 self.translated = translate(self.formula)
395 self.alignment = alignment
401 def recalc(self, rexec):
402 if self.value is None:
404 # A hack to evaluate expressions using true division
405 rexec.r_exec("from __future__ import division\n" +
406 "__value__ = eval(%s)" % repr(self.translated))
407 self.value = rexec.r_eval("__value__")
409 exc = sys.exc_info()[0]
410 if hasattr(exc, "__name__"):
411 self.value = exc.__name__
413 self.value = str(exc)
418 text = self.fmt % self.value
420 text = str(self.value)
421 return text, self.alignment
424 return '<formula align="%s" format="%s">%s</formula>' % (
425 align2xml[self.alignment],
429 def renumber(self, x1, y1, x2, y2, dx, dy):
431 for part in re.split('(\w+)', self.formula):
432 m = re.match('^([A-Z]+)([1-9][0-9]*)$', part)
437 if x1 <= x <= x2 and y1 <= y <= y2:
438 part = cellname(x+dx, y+dy)
440 return FormulaCell("".join(out), self.fmt, self.alignment)
442 def translate(formula):
443 """Translate a formula containing fancy cell names to valid Python code.
447 B4:Z100 -> cells(2, 4, 26, 100)
450 for part in re.split(r"(\w+(?::\w+)?)", formula):
451 m = re.match(r"^([A-Z]+)([1-9][0-9]*)(?::([A-Z]+)([1-9][0-9]*))?$", part)
455 x1, y1, x2, y2 = m.groups()
458 s = "cell(%s, %s)" % (x1, y1)
461 s = "cells(%s, %s, %s, %s)" % (x1, y1, x2, y2)
466 "Translate a cell coordinate to a fancy cell name (e.g. (1, 1)->'A1')."
467 assert x > 0 # Column 0 has an empty name, so can't use that
468 return colnum2name(x) + str(y)
471 "Translate a column name to number (e.g. 'A'->1, 'Z'->26, 'AA'->27)."
475 assert 'A' <= c <= 'Z'
476 n = n*26 + ord(c) - ord('A') + 1
480 "Translate a column number to name (e.g. 1->'A', etc.)."
484 n, m = divmod(n-1, 26)
485 s = chr(m+ord('A')) + s
492 """Beginnings of a GUI for a spreadsheet.
495 - clear multiple cells
496 - Insert, clear, remove rows or columns
497 - Show new contents while typing
499 - Grow grid when window is grown
502 - Cut, copy and paste
503 - Formatting and alignment
506 def __init__(self, filename="sheet1.xml", rows=10, columns=5):
509 Load the sheet from the filename argument.
510 Set up the Tk widget tree.
512 # Create and load the sheet
513 self.filename = filename
515 if os.path.isfile(filename):
516 self.sheet.load(filename)
517 # Calculate the needed grid size
518 maxx, maxy = self.sheet.getsize()
519 rows = max(rows, maxy)
520 columns = max(columns, maxx)
523 self.root.wm_title("Spreadsheet: %s" % self.filename)
524 self.beacon = Tk.Label(self.root, text="A1",
525 font=('helvetica', 16, 'bold'))
526 self.entry = Tk.Entry(self.root)
527 self.savebutton = Tk.Button(self.root, text="Save",
529 self.cellgrid = Tk.Frame(self.root)
530 # Configure the widget lay-out
531 self.cellgrid.pack(side="bottom", expand=1, fill="both")
532 self.beacon.pack(side="left")
533 self.savebutton.pack(side="right")
534 self.entry.pack(side="left", expand=1, fill="x")
536 self.entry.bind("<Return>", self.return_event)
537 self.entry.bind("<Shift-Return>", self.shift_return_event)
538 self.entry.bind("<Tab>", self.tab_event)
539 self.entry.bind("<Shift-Tab>", self.shift_tab_event)
540 self.entry.bind("<Delete>", self.delete_event)
541 self.entry.bind("<Escape>", self.escape_event)
542 # Now create the cell grid
543 self.makegrid(rows, columns)
544 # Select the top-left cell
545 self.currentxy = None
547 self.setcurrent(1, 1)
548 # Copy the sheet cells to the GUI cells
551 def delete_event(self, event):
552 if self.cornerxy != self.currentxy and self.cornerxy is not None:
553 self.sheet.clearcells(*(self.currentxy + self.cornerxy))
555 self.sheet.clearcell(*self.currentxy)
557 self.entry.delete(0, 'end')
560 def escape_event(self, event):
561 x, y = self.currentxy
562 self.load_entry(x, y)
564 def load_entry(self, x, y):
565 cell = self.sheet.getcell(x, y)
568 elif isinstance(cell, FormulaCell):
569 text = '=' + cell.formula
571 text, alignment = cell.format()
572 self.entry.delete(0, 'end')
573 self.entry.insert(0, text)
574 self.entry.selection_range(0, 'end')
576 def makegrid(self, rows, columns):
577 """Helper to create the grid of GUI cells.
579 The edge (x==0 or y==0) is filled with labels; the rest is real cells.
582 self.columns = columns
584 # Create the top left corner cell (which selects all)
585 cell = Tk.Label(self.cellgrid, relief='raised')
586 cell.grid_configure(column=0, row=0, sticky='NSWE')
587 cell.bind("<ButtonPress-1>", self.selectall)
588 # Create the top row of labels, and confiure the grid columns
589 for x in range(1, columns+1):
590 self.cellgrid.grid_columnconfigure(x, minsize=64)
591 cell = Tk.Label(self.cellgrid, text=colnum2name(x), relief='raised')
592 cell.grid_configure(column=x, row=0, sticky='WE')
593 self.gridcells[x, 0] = cell
596 cell.bind("<ButtonPress-1>", self.selectcolumn)
597 cell.bind("<B1-Motion>", self.extendcolumn)
598 cell.bind("<ButtonRelease-1>", self.extendcolumn)
599 cell.bind("<Shift-Button-1>", self.extendcolumn)
600 # Create the leftmost column of labels
601 for y in range(1, rows+1):
602 cell = Tk.Label(self.cellgrid, text=str(y), relief='raised')
603 cell.grid_configure(column=0, row=y, sticky='WE')
604 self.gridcells[0, y] = cell
607 cell.bind("<ButtonPress-1>", self.selectrow)
608 cell.bind("<B1-Motion>", self.extendrow)
609 cell.bind("<ButtonRelease-1>", self.extendrow)
610 cell.bind("<Shift-Button-1>", self.extendrow)
611 # Create the real cells
612 for x in range(1, columns+1):
613 for y in range(1, rows+1):
614 cell = Tk.Label(self.cellgrid, relief='sunken',
615 bg='white', fg='black')
616 cell.grid_configure(column=x, row=y, sticky='NSWE')
617 self.gridcells[x, y] = cell
621 cell.bind("<ButtonPress-1>", self.press)
622 cell.bind("<B1-Motion>", self.motion)
623 cell.bind("<ButtonRelease-1>", self.release)
624 cell.bind("<Shift-Button-1>", self.release)
626 def selectall(self, event):
627 self.setcurrent(1, 1)
628 self.setcorner(sys.maxint, sys.maxint)
630 def selectcolumn(self, event):
631 x, y = self.whichxy(event)
632 self.setcurrent(x, 1)
633 self.setcorner(x, sys.maxint)
635 def extendcolumn(self, event):
636 x, y = self.whichxy(event)
638 self.setcurrent(self.currentxy[0], 1)
639 self.setcorner(x, sys.maxint)
641 def selectrow(self, event):
642 x, y = self.whichxy(event)
643 self.setcurrent(1, y)
644 self.setcorner(sys.maxint, y)
646 def extendrow(self, event):
647 x, y = self.whichxy(event)
649 self.setcurrent(1, self.currentxy[1])
650 self.setcorner(sys.maxint, y)
652 def press(self, event):
653 x, y = self.whichxy(event)
655 self.setcurrent(x, y)
657 def motion(self, event):
658 x, y = self.whichxy(event)
664 def whichxy(self, event):
665 w = self.cellgrid.winfo_containing(event.x_root, event.y_root)
666 if w is not None and isinstance(w, Tk.Label):
669 except AttributeError:
674 self.sheet.save(self.filename)
676 def setcurrent(self, x, y):
677 "Make (x, y) the current cell."
678 if self.currentxy is not None:
681 self.beacon['text'] = cellname(x, y)
682 self.load_entry(x, y)
683 self.entry.focus_set()
684 self.currentxy = x, y
686 gridcell = self.gridcells.get(self.currentxy)
687 if gridcell is not None:
688 gridcell['bg'] = 'yellow'
690 def setcorner(self, x, y):
691 if self.currentxy is None or self.currentxy == (x, y):
692 self.setcurrent(x, y)
696 x1, y1 = self.currentxy
697 x2, y2 = self.cornerxy or self.currentxy
702 for (x, y), cell in self.gridcells.iteritems():
703 if x1 <= x <= x2 and y1 <= y <= y2:
704 cell['bg'] = 'lightBlue'
705 gridcell = self.gridcells.get(self.currentxy)
706 if gridcell is not None:
707 gridcell['bg'] = 'yellow'
708 self.setbeacon(x1, y1, x2, y2)
710 def setbeacon(self, x1, y1, x2, y2):
711 if x1 == y1 == 1 and x2 == y2 == sys.maxint:
713 elif (x1, x2) == (1, sys.maxint):
717 name = "%d:%d" % (y1, y2)
718 elif (y1, y2) == (1, sys.maxint):
720 name = "%s" % colnum2name(x1)
722 name = "%s:%s" % (colnum2name(x1), colnum2name(x2))
724 name1 = cellname(*self.currentxy)
725 name2 = cellname(*self.cornerxy)
726 name = "%s:%s" % (name1, name2)
727 self.beacon['text'] = name
730 def clearfocus(self):
731 if self.currentxy is not None:
732 x1, y1 = self.currentxy
733 x2, y2 = self.cornerxy or self.currentxy
738 for (x, y), cell in self.gridcells.iteritems():
739 if x1 <= x <= x2 and y1 <= y <= y2:
742 def return_event(self, event):
743 "Callback for the Return key."
745 x, y = self.currentxy
746 self.setcurrent(x, y+1)
749 def shift_return_event(self, event):
750 "Callback for the Return key with Shift modifier."
752 x, y = self.currentxy
753 self.setcurrent(x, max(1, y-1))
756 def tab_event(self, event):
757 "Callback for the Tab key."
759 x, y = self.currentxy
760 self.setcurrent(x+1, y)
763 def shift_tab_event(self, event):
764 "Callback for the Tab key with Shift modifier."
766 x, y = self.currentxy
767 self.setcurrent(max(1, x-1), y)
770 def change_cell(self):
771 "Set the current cell from the entry widget."
772 x, y = self.currentxy
773 text = self.entry.get()
775 if text.startswith('='):
776 cell = FormulaCell(text[1:])
778 for cls in int, long, float, complex:
784 cell = NumericCell(value)
786 if cell is None and text:
787 cell = StringCell(text)
789 self.sheet.clearcell(x, y)
791 self.sheet.setcell(x, y, cell)
795 "Fill the GUI cells from the sheet cells."
797 for (x, y), gridcell in self.gridcells.iteritems():
800 cell = self.sheet.getcell(x, y)
802 gridcell['text'] = ""
804 if hasattr(cell, 'format'):
805 text, alignment = cell.format()
807 text, alignment = str(cell), LEFT
808 gridcell['text'] = text
809 gridcell['anchor'] = align2anchor[alignment]
813 "Basic non-gui self-test."
816 for x in range(1, 11):
817 for y in range(1, 11):
819 cell = NumericCell(y)
821 cell = NumericCell(x)
825 formula = "%s*%s" % (c1, c2)
826 cell = FormulaCell(formula)
827 a.setcell(x, y, cell)
828 ## if os.path.isfile("sheet1.xml"):
829 ## print "Loading from sheet1.xml"
830 ## a.load("sheet1.xml")
837 filename = sys.argv[1]
839 filename = "sheet1.xml"
840 g = SheetGUI(filename)
843 if __name__ == '__main__':