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