Original challenge: Standard library only
I set myself a little challenge to write a simple spreadsheet-like program in Python in less than 100 lines, using only the standard library.
The code in this repository manages it in 100 non-blank lines, including comments. 88 if you ignore the comments. The full code is also at the end of this post. Note: I have also redone the code in PyQT5. This is discussed after the tkinter code.
This is what the program looks like when you run it:
You can click on cells to edit their formulae and move around with the arrow keys. Formulae are evaluated once you press Return or navigate way from a cell. Every cell is evaluated as a Python expression. This means you don’t do the Excel thing of having formulae start with =. Everything is a fomula. So the formula in A2 in the screenshot above is valid and will evaluate to 1 once focus moves away from the cell. The math module is imported by default, so you can use sin and cos and so on. I use a very cheap and nasty call of eval to make it all work, which you should never do for production code.
Other than using eval, I also play a little fast and loose with the way objects are allowed to touch each other in the code. I allow each cell to access all their siblings directly instead of going through an intermediary. I probably also break a couple of other OO rules along the way with the way the cells propagate their calculations when they are changed. But the whole code only took a couple of hours to write and it actually works.
And now, here is the full code:
#!/usr/bin/env python
import tkinter as tk
import math
import re
from collections import ChainMap
Nrows = 5
Ncols = 5
cellre = re.compile(r'b[A-Z][0-9]b')
def cellname(i, j):
return f'{chr(ord("A")+j)}{i+1}'
class Cell():
def __init__(self, i, j, siblings, parent):
self.row = i
self.col = j
self.siblings = siblings
self.name = cellname(i, j)
self.formula = '0'
self.value = 0
# Dependencies - must be updated if this cell changes
self.deps = set()
# Requirements - values required for this cell to calculate
self.reqs = set()
self.var = tk.StringVar()
entry = self.widget = tk.Entry(parent,
textvariable=self.var,
justify='right')
entry.bind('<FocusIn>', self.edit)
entry.bind('<FocusOut>', self.update)
entry.bind('<Return>', self.update)
entry.bind('<Up>', self.move(-1, 0))
entry.bind('<Down>', self.move(+1, 0))
entry.bind('<Left>', self.move(0, -1))
entry.bind('<Right>', self.move(0, 1))
self.var.set(self.value)
def move(self, rowadvance, coladvance):
targetrow = (self.row + rowadvance) % Nrows
targetcol = (self.col + coladvance) % Ncols
def focus(event):
targetwidget = self.siblings[cellname(targetrow, targetcol)].widget
targetwidget.focus()
return focus
def calculate(self):
currentreqs = set(cellre.findall(self.formula))
# Add this cell to the new requirement's dependents
for r in currentreqs - self.reqs:
self.siblings[r].deps.add(self.name)
# Add remove this cell from dependents no longer referenced
for r in self.reqs - currentreqs:
self.siblings[r].deps.remove(self.name)
# Look up the values of our required cells
reqvalues = {r: self.siblings[r].value for r in currentreqs}
# Build an environment with these values and basic math functions
environment = ChainMap(math.__dict__, reqvalues)
# Note that eval is DANGEROUS and should not be used in production
self.value = eval(self.formula, {}, environment)
self.reqs = currentreqs
self.var.set(self.value)
def propagate(self):
for d in self.deps:
self.siblings[d].calculate()
self.siblings[d].propagate()
def edit(self, event):
self.var.set(self.formula)
self.widget.select_range(0, tk.END)
def update(self, event):
self.formula = self.var.get()
self.calculate()
self.propagate()
# If this was after pressing Return, keep showing the formula
if hasattr(event, 'keysym') and event.keysym == "Return":
self.var.set(self.formula)
class SpreadSheet(tk.Frame):
def __init__(self, rows, cols, master=None):
super().__init__(master)
self.rows = rows
self.cols = cols
self.cells = {}
self.pack()
self.create_widgets()
def create_widgets(self):
# Frame for all the cells
self.cellframe = tk.Frame(self)
self.cellframe.pack(side='top')
# Column labels
blank = tk.Label(self.cellframe)
blank.grid(row=0, column=0)
for j in range(self.cols):
label = tk.Label(self.cellframe, text=chr(ord('A')+j))
label.grid(row=0, column=j+1)
# Fill in the rows
for i in range(self.rows):
rowlabel = tk.Label(self.cellframe, text=str(i + 1))
rowlabel.grid(row=1+i, column=0)
for j in range(self.cols):
cell = Cell(i, j, self.cells, self.cellframe)
self.cells[cell.name] = cell
cell.widget.grid(row=1+i, column=1+j)
root = tk.Tk()
app = SpreadSheet(Nrows, Ncols, master=root)
app.mainloop()
Part 2: PyQT5
I posted this blog to Reddit and one of the commenters mentioned that PyQT5 has a nice table widget which is more spreadsheet-like. So I re-did the spreadsheet in PyQT5. The code is functionally similar at the moment and also has approximately the same number of lines (87 non-comment code lines). The main differences are that I could use the QTableWidget to handle all the navigation. I had to do some interesting things with delegates to enable to editing mode I wanted, though, which cost me all the lines I saved from the navigation. I think my next step is going to be to extract the calculation logic into a common class which both of these can use – there is way too much duplication going on here right now!
So, this is what the PyQT5 one looks like. It’s a little more compact, but otherwise very similar. The interaction is slightly more spreadsheet-like though, and you get automatic highlighting and selection of multiple cells for free. Very nice.
And here is the code. You will notice a great deal of similarity. The key to getting the edit mode thing to work properly is the piece of magic with SpreadSheetDelegate. I didn’t think of that myself – I stole it from this PyQT5 example, which has a much nicer spreadsheet (you can change colours, fonts, print etc) with much worse formula logic.
#!/usr/bin/env python
import re
import sys
from collections import ChainMap
import math
from PyQt5.QtCore import Qt
from PyQt5.QtWidgets import (QApplication, QMainWindow, QTableWidget,
QTableWidgetItem, QItemDelegate, QLineEdit)
cellre = re.compile(r'b[A-Z][0-9]b')
def cellname(i, j):
return f'{chr(ord("A")+j)}{i+1}'
class SpreadSheetDelegate(QItemDelegate):
def __init__(self, parent=None):
super(SpreadSheetDelegate, self).__init__(parent)
def createEditor(self, parent, styleOption, index):
editor = QLineEdit(parent)
editor.editingFinished.connect(self.commitAndCloseEditor)
return editor
def commitAndCloseEditor(self):
editor = self.sender()
self.commitData.emit(editor)
self.closeEditor.emit(editor, QItemDelegate.NoHint)
def setEditorData(self, editor, index):
editor.setText(index.model().data(index, Qt.EditRole))
def setModelData(self, editor, model, index):
model.setData(index, editor.text())
class SpreadSheetItem(QTableWidgetItem):
def __init__(self, siblings):
super(SpreadSheetItem, self).__init__()
self.siblings = siblings
self.value = 0
self.deps = set()
self.reqs = set()
def formula(self):
return super().data(Qt.DisplayRole)
def data(self, role):
if role == Qt.EditRole:
return self.formula()
if role == Qt.DisplayRole:
return self.display()
return super(SpreadSheetItem, self).data(role)
def calculate(self):
formula = self.formula()
if formula is None or formula == '':
self.value = 0
return
currentreqs = set(cellre.findall(formula))
name = cellname(self.row(), self.column())
# Add this cell to the new requirement's dependents
for r in currentreqs - self.reqs:
self.siblings[r].deps.add(name)
# Add remove this cell from dependents no longer referenced
for r in self.reqs - currentreqs:
self.siblings[r].deps.remove(name)
# Look up the values of our required cells
reqvalues = {r: self.siblings[r].value for r in currentreqs}
# Build an environment with these values and basic math functions
environment = ChainMap(math.__dict__, reqvalues)
# Note that eval is DANGEROUS and should not be used in production
self.value = eval(formula, {}, environment)
self.reqs = currentreqs
def propagate(self):
for d in self.deps:
self.siblings[d].calculate()
self.siblings[d].propagate()
def display(self):
self.calculate()
self.propagate()
return str(self.value)
class SpreadSheet(QMainWindow):
def __init__(self, rows, cols, parent=None):
super(SpreadSheet, self).__init__(parent)
self.rows = rows
self.cols = cols
self.cells = {}
self.create_widgets()
def create_widgets(self):
table = self.table = QTableWidget(self.rows, self.cols, self)
headers = [chr(ord('A') + j) for j in range(self.cols)]
table.setHorizontalHeaderLabels(headers)
table.setItemDelegate(SpreadSheetDelegate(self))
for i in range(self.rows):
for j in range(self.cols):
cell = SpreadSheetItem(self.cells)
self.cells[cellname(i, j)] = cell
self.table.setItem(i, j, cell)
self.setCentralWidget(table)
if __name__ == '__main__':
app = QApplication(sys.argv)
sheet = SpreadSheet(5, 5)
sheet.resize(520, 200)
sheet.show()
sys.exit(app.exec_())
Leave a Reply