1#!/usr/bin/env python3
2
3"""
4SS1 -- a spreadsheet-like application.
5"""
6
7import os
8import re
9import sys
10from xml.parsers import expat
11from xml.sax.saxutils import escape
12
13LEFT, CENTER, RIGHT = "LEFT", "CENTER", "RIGHT"
14
15def ljust(x, n):
16    return x.ljust(n)
17def center(x, n):
18    return x.center(n)
19def rjust(x, n):
20    return x.rjust(n)
21align2action = {LEFT: ljust, CENTER: center, RIGHT: rjust}
22
23align2xml = {LEFT: "left", CENTER: "center", RIGHT: "right"}
24xml2align = {"left": LEFT, "center": CENTER, "right": RIGHT}
25
26align2anchor = {LEFT: "w", CENTER: "center", RIGHT: "e"}
27
28def sum(seq):
29    total = 0
30    for x in seq:
31        if x is not None:
32            total += x
33    return total
34
35class Sheet:
36
37    def __init__(self):
38        self.cells = {} # {(x, y): cell, ...}
39        self.ns = dict(
40            cell = self.cellvalue,
41            cells = self.multicellvalue,
42            sum = sum,
43        )
44
45    def cellvalue(self, x, y):
46        cell = self.getcell(x, y)
47        if hasattr(cell, 'recalc'):
48            return cell.recalc(self.ns)
49        else:
50            return cell
51
52    def multicellvalue(self, x1, y1, x2, y2):
53        if x1 > x2:
54            x1, x2 = x2, x1
55        if y1 > y2:
56            y1, y2 = y2, y1
57        seq = []
58        for y in range(y1, y2+1):
59            for x in range(x1, x2+1):
60                seq.append(self.cellvalue(x, y))
61        return seq
62
63    def getcell(self, x, y):
64        return self.cells.get((x, y))
65
66    def setcell(self, x, y, cell):
67        assert x > 0 and y > 0
68        assert isinstance(cell, BaseCell)
69        self.cells[x, y] = cell
70
71    def clearcell(self, x, y):
72        try:
73            del self.cells[x, y]
74        except KeyError:
75            pass
76
77    def clearcells(self, x1, y1, x2, y2):
78        for xy in self.selectcells(x1, y1, x2, y2):
79            del self.cells[xy]
80
81    def clearrows(self, y1, y2):
82        self.clearcells(0, y1, sys.maxsize, y2)
83
84    def clearcolumns(self, x1, x2):
85        self.clearcells(x1, 0, x2, sys.maxsize)
86
87    def selectcells(self, x1, y1, x2, y2):
88        if x1 > x2:
89            x1, x2 = x2, x1
90        if y1 > y2:
91            y1, y2 = y2, y1
92        return [(x, y) for x, y in self.cells
93                if x1 <= x <= x2 and y1 <= y <= y2]
94
95    def movecells(self, x1, y1, x2, y2, dx, dy):
96        if dx == 0 and dy == 0:
97            return
98        if x1 > x2:
99            x1, x2 = x2, x1
100        if y1 > y2:
101            y1, y2 = y2, y1
102        assert x1+dx > 0 and y1+dy > 0
103        new = {}
104        for x, y in self.cells:
105            cell = self.cells[x, y]
106            if hasattr(cell, 'renumber'):
107                cell = cell.renumber(x1, y1, x2, y2, dx, dy)
108            if x1 <= x <= x2 and y1 <= y <= y2:
109                x += dx
110                y += dy
111            new[x, y] = cell
112        self.cells = new
113
114    def insertrows(self, y, n):
115        assert n > 0
116        self.movecells(0, y, sys.maxsize, sys.maxsize, 0, n)
117
118    def deleterows(self, y1, y2):
119        if y1 > y2:
120            y1, y2 = y2, y1
121        self.clearrows(y1, y2)
122        self.movecells(0, y2+1, sys.maxsize, sys.maxsize, 0, y1-y2-1)
123
124    def insertcolumns(self, x, n):
125        assert n > 0
126        self.movecells(x, 0, sys.maxsize, sys.maxsize, n, 0)
127
128    def deletecolumns(self, x1, x2):
129        if x1 > x2:
130            x1, x2 = x2, x1
131        self.clearcells(x1, x2)
132        self.movecells(x2+1, 0, sys.maxsize, sys.maxsize, x1-x2-1, 0)
133
134    def getsize(self):
135        maxx = maxy = 0
136        for x, y in self.cells:
137            maxx = max(maxx, x)
138            maxy = max(maxy, y)
139        return maxx, maxy
140
141    def reset(self):
142        for cell in self.cells.values():
143            if hasattr(cell, 'reset'):
144                cell.reset()
145
146    def recalc(self):
147        self.reset()
148        for cell in self.cells.values():
149            if hasattr(cell, 'recalc'):
150                cell.recalc(self.ns)
151
152    def display(self):
153        maxx, maxy = self.getsize()
154        width, height = maxx+1, maxy+1
155        colwidth = [1] * width
156        full = {}
157        # Add column heading labels in row 0
158        for x in range(1, width):
159            full[x, 0] = text, alignment = colnum2name(x), RIGHT
160            colwidth[x] = max(colwidth[x], len(text))
161        # Add row labels in column 0
162        for y in range(1, height):
163            full[0, y] = text, alignment = str(y), RIGHT
164            colwidth[0] = max(colwidth[0], len(text))
165        # Add sheet cells in columns with x>0 and y>0
166        for (x, y), cell in self.cells.items():
167            if x <= 0 or y <= 0:
168                continue
169            if hasattr(cell, 'recalc'):
170                cell.recalc(self.ns)
171            if hasattr(cell, 'format'):
172                text, alignment = cell.format()
173                assert isinstance(text, str)
174                assert alignment in (LEFT, CENTER, RIGHT)
175            else:
176                text = str(cell)
177                if isinstance(cell, str):
178                    alignment = LEFT
179                else:
180                    alignment = RIGHT
181            full[x, y] = (text, alignment)
182            colwidth[x] = max(colwidth[x], len(text))
183        # Calculate the horizontal separator line (dashes and dots)
184        sep = ""
185        for x in range(width):
186            if sep:
187                sep += "+"
188            sep += "-"*colwidth[x]
189        # Now print The full grid
190        for y in range(height):
191            line = ""
192            for x in range(width):
193                text, alignment = full.get((x, y)) or ("", LEFT)
194                text = align2action[alignment](text, colwidth[x])
195                if line:
196                    line += '|'
197                line += text
198            print(line)
199            if y == 0:
200                print(sep)
201
202    def xml(self):
203        out = ['<spreadsheet>']
204        for (x, y), cell in self.cells.items():
205            if hasattr(cell, 'xml'):
206                cellxml = cell.xml()
207            else:
208                cellxml = '<value>%s</value>' % escape(cell)
209            out.append('<cell row="%s" col="%s">\n  %s\n</cell>' %
210                       (y, x, cellxml))
211        out.append('</spreadsheet>')
212        return '\n'.join(out)
213
214    def save(self, filename):
215        text = self.xml()
216        with open(filename, "w", encoding='utf-8') as f:
217            f.write(text)
218            if text and not text.endswith('\n'):
219                f.write('\n')
220
221    def load(self, filename):
222        with open(filename, 'rb') as f:
223            SheetParser(self).parsefile(f)
224
225class SheetParser:
226
227    def __init__(self, sheet):
228        self.sheet = sheet
229
230    def parsefile(self, f):
231        parser = expat.ParserCreate()
232        parser.StartElementHandler = self.startelement
233        parser.EndElementHandler = self.endelement
234        parser.CharacterDataHandler = self.data
235        parser.ParseFile(f)
236
237    def startelement(self, tag, attrs):
238        method = getattr(self, 'start_'+tag, None)
239        if method:
240            method(attrs)
241        self.texts = []
242
243    def data(self, text):
244        self.texts.append(text)
245
246    def endelement(self, tag):
247        method = getattr(self, 'end_'+tag, None)
248        if method:
249            method("".join(self.texts))
250
251    def start_cell(self, attrs):
252        self.y = int(attrs.get("row"))
253        self.x = int(attrs.get("col"))
254
255    def start_value(self, attrs):
256        self.fmt = attrs.get('format')
257        self.alignment = xml2align.get(attrs.get('align'))
258
259    start_formula = start_value
260
261    def end_int(self, text):
262        try:
263            self.value = int(text)
264        except (TypeError, ValueError):
265            self.value = None
266
267    end_long = end_int
268
269    def end_double(self, text):
270        try:
271            self.value = float(text)
272        except (TypeError, ValueError):
273            self.value = None
274
275    def end_complex(self, text):
276        try:
277            self.value = complex(text)
278        except (TypeError, ValueError):
279            self.value = None
280
281    def end_string(self, text):
282        self.value = text
283
284    def end_value(self, text):
285        if isinstance(self.value, BaseCell):
286            self.cell = self.value
287        elif isinstance(self.value, str):
288            self.cell = StringCell(self.value,
289                                   self.fmt or "%s",
290                                   self.alignment or LEFT)
291        else:
292            self.cell = NumericCell(self.value,
293                                    self.fmt or "%s",
294                                    self.alignment or RIGHT)
295
296    def end_formula(self, text):
297        self.cell = FormulaCell(text,
298                                self.fmt or "%s",
299                                self.alignment or RIGHT)
300
301    def end_cell(self, text):
302        self.sheet.setcell(self.x, self.y, self.cell)
303
304class BaseCell:
305    __init__ = None # Must provide
306    """Abstract base class for sheet cells.
307
308    Subclasses may but needn't provide the following APIs:
309
310    cell.reset() -- prepare for recalculation
311    cell.recalc(ns) -> value -- recalculate formula
312    cell.format() -> (value, alignment) -- return formatted value
313    cell.xml() -> string -- return XML
314    """
315
316class NumericCell(BaseCell):
317
318    def __init__(self, value, fmt="%s", alignment=RIGHT):
319        assert isinstance(value, (int, float, complex))
320        assert alignment in (LEFT, CENTER, RIGHT)
321        self.value = value
322        self.fmt = fmt
323        self.alignment = alignment
324
325    def recalc(self, ns):
326        return self.value
327
328    def format(self):
329        try:
330            text = self.fmt % self.value
331        except:
332            text = str(self.value)
333        return text, self.alignment
334
335    def xml(self):
336        method = getattr(self, '_xml_' + type(self.value).__name__)
337        return '<value align="%s" format="%s">%s</value>' % (
338                align2xml[self.alignment],
339                self.fmt,
340                method())
341
342    def _xml_int(self):
343        if -2**31 <= self.value < 2**31:
344            return '<int>%s</int>' % self.value
345        else:
346            return '<long>%s</long>' % self.value
347
348    def _xml_float(self):
349        return '<double>%r</double>' % self.value
350
351    def _xml_complex(self):
352        return '<complex>%r</complex>' % self.value
353
354class StringCell(BaseCell):
355
356    def __init__(self, text, fmt="%s", alignment=LEFT):
357        assert isinstance(text, str)
358        assert alignment in (LEFT, CENTER, RIGHT)
359        self.text = text
360        self.fmt = fmt
361        self.alignment = alignment
362
363    def recalc(self, ns):
364        return self.text
365
366    def format(self):
367        return self.text, self.alignment
368
369    def xml(self):
370        s = '<value align="%s" format="%s"><string>%s</string></value>'
371        return s % (
372            align2xml[self.alignment],
373            self.fmt,
374            escape(self.text))
375
376class FormulaCell(BaseCell):
377
378    def __init__(self, formula, fmt="%s", alignment=RIGHT):
379        assert alignment in (LEFT, CENTER, RIGHT)
380        self.formula = formula
381        self.translated = translate(self.formula)
382        self.fmt = fmt
383        self.alignment = alignment
384        self.reset()
385
386    def reset(self):
387        self.value = None
388
389    def recalc(self, ns):
390        if self.value is None:
391            try:
392                self.value = eval(self.translated, ns)
393            except:
394                exc = sys.exc_info()[0]
395                if hasattr(exc, "__name__"):
396                    self.value = exc.__name__
397                else:
398                    self.value = str(exc)
399        return self.value
400
401    def format(self):
402        try:
403            text = self.fmt % self.value
404        except:
405            text = str(self.value)
406        return text, self.alignment
407
408    def xml(self):
409        return '<formula align="%s" format="%s">%s</formula>' % (
410            align2xml[self.alignment],
411            self.fmt,
412            escape(self.formula))
413
414    def renumber(self, x1, y1, x2, y2, dx, dy):
415        out = []
416        for part in re.split(r'(\w+)', self.formula):
417            m = re.match('^([A-Z]+)([1-9][0-9]*)$', part)
418            if m is not None:
419                sx, sy = m.groups()
420                x = colname2num(sx)
421                y = int(sy)
422                if x1 <= x <= x2 and y1 <= y <= y2:
423                    part = cellname(x+dx, y+dy)
424            out.append(part)
425        return FormulaCell("".join(out), self.fmt, self.alignment)
426
427def translate(formula):
428    """Translate a formula containing fancy cell names to valid Python code.
429
430    Examples:
431        B4 -> cell(2, 4)
432        B4:Z100 -> cells(2, 4, 26, 100)
433    """
434    out = []
435    for part in re.split(r"(\w+(?::\w+)?)", formula):
436        m = re.match(r"^([A-Z]+)([1-9][0-9]*)(?::([A-Z]+)([1-9][0-9]*))?$", part)
437        if m is None:
438            out.append(part)
439        else:
440            x1, y1, x2, y2 = m.groups()
441            x1 = colname2num(x1)
442            if x2 is None:
443                s = "cell(%s, %s)" % (x1, y1)
444            else:
445                x2 = colname2num(x2)
446                s = "cells(%s, %s, %s, %s)" % (x1, y1, x2, y2)
447            out.append(s)
448    return "".join(out)
449
450def cellname(x, y):
451    "Translate a cell coordinate to a fancy cell name (e.g. (1, 1)->'A1')."
452    assert x > 0 # Column 0 has an empty name, so can't use that
453    return colnum2name(x) + str(y)
454
455def colname2num(s):
456    "Translate a column name to number (e.g. 'A'->1, 'Z'->26, 'AA'->27)."
457    s = s.upper()
458    n = 0
459    for c in s:
460        assert 'A' <= c <= 'Z'
461        n = n*26 + ord(c) - ord('A') + 1
462    return n
463
464def colnum2name(n):
465    "Translate a column number to name (e.g. 1->'A', etc.)."
466    assert n > 0
467    s = ""
468    while n:
469        n, m = divmod(n-1, 26)
470        s = chr(m+ord('A')) + s
471    return s
472
473import tkinter as Tk
474
475class SheetGUI:
476
477    """Beginnings of a GUI for a spreadsheet.
478
479    TO DO:
480    - clear multiple cells
481    - Insert, clear, remove rows or columns
482    - Show new contents while typing
483    - Scroll bars
484    - Grow grid when window is grown
485    - Proper menus
486    - Undo, redo
487    - Cut, copy and paste
488    - Formatting and alignment
489    """
490
491    def __init__(self, filename="sheet1.xml", rows=10, columns=5):
492        """Constructor.
493
494        Load the sheet from the filename argument.
495        Set up the Tk widget tree.
496        """
497        # Create and load the sheet
498        self.filename = filename
499        self.sheet = Sheet()
500        if os.path.isfile(filename):
501            self.sheet.load(filename)
502        # Calculate the needed grid size
503        maxx, maxy = self.sheet.getsize()
504        rows = max(rows, maxy)
505        columns = max(columns, maxx)
506        # Create the widgets
507        self.root = Tk.Tk()
508        self.root.wm_title("Spreadsheet: %s" % self.filename)
509        self.beacon = Tk.Label(self.root, text="A1",
510                               font=('helvetica', 16, 'bold'))
511        self.entry = Tk.Entry(self.root)
512        self.savebutton = Tk.Button(self.root, text="Save",
513                                    command=self.save)
514        self.cellgrid = Tk.Frame(self.root)
515        # Configure the widget lay-out
516        self.cellgrid.pack(side="bottom", expand=1, fill="both")
517        self.beacon.pack(side="left")
518        self.savebutton.pack(side="right")
519        self.entry.pack(side="left", expand=1, fill="x")
520        # Bind some events
521        self.entry.bind("<Return>", self.return_event)
522        self.entry.bind("<Shift-Return>", self.shift_return_event)
523        self.entry.bind("<Tab>", self.tab_event)
524        self.entry.bind("<Shift-Tab>", self.shift_tab_event)
525        self.entry.bind("<Delete>", self.delete_event)
526        self.entry.bind("<Escape>", self.escape_event)
527        # Now create the cell grid
528        self.makegrid(rows, columns)
529        # Select the top-left cell
530        self.currentxy = None
531        self.cornerxy = None
532        self.setcurrent(1, 1)
533        # Copy the sheet cells to the GUI cells
534        self.sync()
535
536    def delete_event(self, event):
537        if self.cornerxy != self.currentxy and self.cornerxy is not None:
538            self.sheet.clearcells(*(self.currentxy + self.cornerxy))
539        else:
540            self.sheet.clearcell(*self.currentxy)
541        self.sync()
542        self.entry.delete(0, 'end')
543        return "break"
544
545    def escape_event(self, event):
546        x, y = self.currentxy
547        self.load_entry(x, y)
548
549    def load_entry(self, x, y):
550        cell = self.sheet.getcell(x, y)
551        if cell is None:
552            text = ""
553        elif isinstance(cell, FormulaCell):
554            text = '=' + cell.formula
555        else:
556            text, alignment = cell.format()
557        self.entry.delete(0, 'end')
558        self.entry.insert(0, text)
559        self.entry.selection_range(0, 'end')
560
561    def makegrid(self, rows, columns):
562        """Helper to create the grid of GUI cells.
563
564        The edge (x==0 or y==0) is filled with labels; the rest is real cells.
565        """
566        self.rows = rows
567        self.columns = columns
568        self.gridcells = {}
569        # Create the top left corner cell (which selects all)
570        cell = Tk.Label(self.cellgrid, relief='raised')
571        cell.grid_configure(column=0, row=0, sticky='NSWE')
572        cell.bind("<ButtonPress-1>", self.selectall)
573        # Create the top row of labels, and configure the grid columns
574        for x in range(1, columns+1):
575            self.cellgrid.grid_columnconfigure(x, minsize=64)
576            cell = Tk.Label(self.cellgrid, text=colnum2name(x), relief='raised')
577            cell.grid_configure(column=x, row=0, sticky='WE')
578            self.gridcells[x, 0] = cell
579            cell.__x = x
580            cell.__y = 0
581            cell.bind("<ButtonPress-1>", self.selectcolumn)
582            cell.bind("<B1-Motion>", self.extendcolumn)
583            cell.bind("<ButtonRelease-1>", self.extendcolumn)
584            cell.bind("<Shift-Button-1>", self.extendcolumn)
585        # Create the leftmost column of labels
586        for y in range(1, rows+1):
587            cell = Tk.Label(self.cellgrid, text=str(y), relief='raised')
588            cell.grid_configure(column=0, row=y, sticky='WE')
589            self.gridcells[0, y] = cell
590            cell.__x = 0
591            cell.__y = y
592            cell.bind("<ButtonPress-1>", self.selectrow)
593            cell.bind("<B1-Motion>", self.extendrow)
594            cell.bind("<ButtonRelease-1>", self.extendrow)
595            cell.bind("<Shift-Button-1>", self.extendrow)
596        # Create the real cells
597        for x in range(1, columns+1):
598            for y in range(1, rows+1):
599                cell = Tk.Label(self.cellgrid, relief='sunken',
600                                bg='white', fg='black')
601                cell.grid_configure(column=x, row=y, sticky='NSWE')
602                self.gridcells[x, y] = cell
603                cell.__x = x
604                cell.__y = y
605                # Bind mouse events
606                cell.bind("<ButtonPress-1>", self.press)
607                cell.bind("<B1-Motion>", self.motion)
608                cell.bind("<ButtonRelease-1>", self.release)
609                cell.bind("<Shift-Button-1>", self.release)
610
611    def selectall(self, event):
612        self.setcurrent(1, 1)
613        self.setcorner(sys.maxsize, sys.maxsize)
614
615    def selectcolumn(self, event):
616        x, y = self.whichxy(event)
617        self.setcurrent(x, 1)
618        self.setcorner(x, sys.maxsize)
619
620    def extendcolumn(self, event):
621        x, y = self.whichxy(event)
622        if x > 0:
623            self.setcurrent(self.currentxy[0], 1)
624            self.setcorner(x, sys.maxsize)
625
626    def selectrow(self, event):
627        x, y = self.whichxy(event)
628        self.setcurrent(1, y)
629        self.setcorner(sys.maxsize, y)
630
631    def extendrow(self, event):
632        x, y = self.whichxy(event)
633        if y > 0:
634            self.setcurrent(1, self.currentxy[1])
635            self.setcorner(sys.maxsize, y)
636
637    def press(self, event):
638        x, y = self.whichxy(event)
639        if x > 0 and y > 0:
640            self.setcurrent(x, y)
641
642    def motion(self, event):
643        x, y = self.whichxy(event)
644        if x > 0 and y > 0:
645            self.setcorner(x, y)
646
647    release = motion
648
649    def whichxy(self, event):
650        w = self.cellgrid.winfo_containing(event.x_root, event.y_root)
651        if w is not None and isinstance(w, Tk.Label):
652            try:
653                return w.__x, w.__y
654            except AttributeError:
655                pass
656        return 0, 0
657
658    def save(self):
659        self.sheet.save(self.filename)
660
661    def setcurrent(self, x, y):
662        "Make (x, y) the current cell."
663        if self.currentxy is not None:
664            self.change_cell()
665        self.clearfocus()
666        self.beacon['text'] = cellname(x, y)
667        self.load_entry(x, y)
668        self.entry.focus_set()
669        self.currentxy = x, y
670        self.cornerxy = None
671        gridcell = self.gridcells.get(self.currentxy)
672        if gridcell is not None:
673            gridcell['bg'] = 'yellow'
674
675    def setcorner(self, x, y):
676        if self.currentxy is None or self.currentxy == (x, y):
677            self.setcurrent(x, y)
678            return
679        self.clearfocus()
680        self.cornerxy = x, y
681        x1, y1 = self.currentxy
682        x2, y2 = self.cornerxy or self.currentxy
683        if x1 > x2:
684            x1, x2 = x2, x1
685        if y1 > y2:
686            y1, y2 = y2, y1
687        for (x, y), cell in self.gridcells.items():
688            if x1 <= x <= x2 and y1 <= y <= y2:
689                cell['bg'] = 'lightBlue'
690        gridcell = self.gridcells.get(self.currentxy)
691        if gridcell is not None:
692            gridcell['bg'] = 'yellow'
693        self.setbeacon(x1, y1, x2, y2)
694
695    def setbeacon(self, x1, y1, x2, y2):
696        if x1 == y1 == 1 and x2 == y2 == sys.maxsize:
697            name = ":"
698        elif (x1, x2) == (1, sys.maxsize):
699            if y1 == y2:
700                name = "%d" % y1
701            else:
702                name = "%d:%d" % (y1, y2)
703        elif (y1, y2) == (1, sys.maxsize):
704            if x1 == x2:
705                name = "%s" % colnum2name(x1)
706            else:
707                name = "%s:%s" % (colnum2name(x1), colnum2name(x2))
708        else:
709            name1 = cellname(*self.currentxy)
710            name2 = cellname(*self.cornerxy)
711            name = "%s:%s" % (name1, name2)
712        self.beacon['text'] = name
713
714
715    def clearfocus(self):
716        if self.currentxy is not None:
717            x1, y1 = self.currentxy
718            x2, y2 = self.cornerxy or self.currentxy
719            if x1 > x2:
720                x1, x2 = x2, x1
721            if y1 > y2:
722                y1, y2 = y2, y1
723            for (x, y), cell in self.gridcells.items():
724                if x1 <= x <= x2 and y1 <= y <= y2:
725                    cell['bg'] = 'white'
726
727    def return_event(self, event):
728        "Callback for the Return key."
729        self.change_cell()
730        x, y = self.currentxy
731        self.setcurrent(x, y+1)
732        return "break"
733
734    def shift_return_event(self, event):
735        "Callback for the Return key with Shift modifier."
736        self.change_cell()
737        x, y = self.currentxy
738        self.setcurrent(x, max(1, y-1))
739        return "break"
740
741    def tab_event(self, event):
742        "Callback for the Tab key."
743        self.change_cell()
744        x, y = self.currentxy
745        self.setcurrent(x+1, y)
746        return "break"
747
748    def shift_tab_event(self, event):
749        "Callback for the Tab key with Shift modifier."
750        self.change_cell()
751        x, y = self.currentxy
752        self.setcurrent(max(1, x-1), y)
753        return "break"
754
755    def change_cell(self):
756        "Set the current cell from the entry widget."
757        x, y = self.currentxy
758        text = self.entry.get()
759        cell = None
760        if text.startswith('='):
761            cell = FormulaCell(text[1:])
762        else:
763            for cls in int, float, complex:
764                try:
765                    value = cls(text)
766                except (TypeError, ValueError):
767                    continue
768                else:
769                    cell = NumericCell(value)
770                    break
771        if cell is None and text:
772            cell = StringCell(text)
773        if cell is None:
774            self.sheet.clearcell(x, y)
775        else:
776            self.sheet.setcell(x, y, cell)
777        self.sync()
778
779    def sync(self):
780        "Fill the GUI cells from the sheet cells."
781        self.sheet.recalc()
782        for (x, y), gridcell in self.gridcells.items():
783            if x == 0 or y == 0:
784                continue
785            cell = self.sheet.getcell(x, y)
786            if cell is None:
787                gridcell['text'] = ""
788            else:
789                if hasattr(cell, 'format'):
790                    text, alignment = cell.format()
791                else:
792                    text, alignment = str(cell), LEFT
793                gridcell['text'] = text
794                gridcell['anchor'] = align2anchor[alignment]
795
796
797def test_basic():
798    "Basic non-gui self-test."
799    a = Sheet()
800    for x in range(1, 11):
801        for y in range(1, 11):
802            if x == 1:
803                cell = NumericCell(y)
804            elif y == 1:
805                cell = NumericCell(x)
806            else:
807                c1 = cellname(x, 1)
808                c2 = cellname(1, y)
809                formula = "%s*%s" % (c1, c2)
810                cell = FormulaCell(formula)
811            a.setcell(x, y, cell)
812##    if os.path.isfile("sheet1.xml"):
813##        print "Loading from sheet1.xml"
814##        a.load("sheet1.xml")
815    a.display()
816    a.save("sheet1.xml")
817
818def test_gui():
819    "GUI test."
820    if sys.argv[1:]:
821        filename = sys.argv[1]
822    else:
823        filename = "sheet1.xml"
824    g = SheetGUI(filename)
825    g.root.mainloop()
826
827if __name__ == '__main__':
828    #test_basic()
829    test_gui()
830