PySheet

Post date: Oct 07, 2012 5:1:42 PM

PySheet is a simple Python based spreadsheet with integrated interpreter shell. The language for the formulas is Python. This program requires Python 2.6, a recent version of the wxPython library, and my EzWX library to run correctly. EzWX is my attempt at making wxPython a little easier to use to make a fully-functional GUI application. At the very least I'm sure that it will provide an example on how to implement certain GUI features using wxPython. Believe me, it took a lot of scouring the web and trial and error to figure it all out. To keep you from having to do a separate download I'm including ezwx.py in this distribution. Download PySheet at the bottom of this page.

Edit: I've finally gotten around to getting a working Windows setup and noticed that there are a few glitches in the UI. I'll see if I can figure out how to make the Windows version work the same as Linux. In the meantime, when you first start the program in windows just resize it a bit and the grid will redraw correctly. The other thing I noticed right away is that the enter key behaves differently when editing a cell. No it is not supposed to pop you down to the interpreter -- go figure.

Formulas and values can be manipulated from the spreadsheet cells or from the interpreter command line. The formula syntax requires the use of an equals sign similar to a regular spreadsheet. A formula can be entered in the interpreter window as follows:

>>> a3 = '=a1+a2'

Alternatively the formula can be entered directly into cell a3 without the quotes:

=a1+a2

Cell dependencies are handled automatically using Tarjan's algorithm. In the case of a circular dependency a simple iterative method is used to find a solution assuming that it converges within 100 iterations.

Helper functions are defined in ssfuncs.py and are imported automatically when the program starts up. Range() can be used with functions that require an iterable as follows:

>>> a11 = '= sum(Range(a1,a10))' note this is equivalent:

>>> a11 = '= sum(Range("a1", "a10"))'

I have made cell variables know their own name so you don't have to always quote cell addresses. This also makes pySheet work more like you would expect for a spreadsheet program.

I have included a handy trick so you don't have to use the range function if you don't want to. The spreadsheet has a 'shortcut' name of ss. So instead of the Range() function you could use the following:

>>> sum(ss[a1:a10])

or

>>> a11 = '=sum(ss[a1:a10])'

This also works in the cells. Using ss was the closest I could come to having spreadsheet-like range functionality.

There is a helper function called Fill() which can be used to fill cell values while incrementing (left to right, top to bottom):

>>> Fill(a1,f10) # Can optionally provide a start and increment

Copy, paste, clear, and delete should work the way you would expect. Formula addresses are relative-translated when you do a copy etc. To create an absolute address use a trailing underscore after the column letter or the row number:

>>> a11 = '=a_1_ + b_1 + c1_'

I have attempted to provide multi-level undo/redo however it does not handle the commands you type at the command line at this time. I have attempted to verify that it works correctly in many scenarios including inserts, deletes, cuts, pastes, etc.

The file format despite the extension of .sht is actually just Python code like what you would type on the interpreter command line to populate cells. In fact a load is really just running exec on the file. Each non-empty cell is written out as a single Python statement.

Another of the quirks of me trying to keep this true to Python and avoiding the temptation to redefine the formula language is cell name case. You can use either upper or lower case but I had to pick one for Python to use in the 'locals' dictionary so I chose lower case. While it is completely transparent when working on the grid (everything is is just converted to lower), when you are working in the interpreter sometimes it works the way you expect, sometimes it doesn't. So use lower case all the time and your life will be easier.

Presently all of the math library is imported by default, perhaps others should be too. The import command and many others are disabled in the interpreter so any additional library imports would have to be included in pysheet.py when the instance of the Spreadsheet class is created (see the very bottom of the file). My intention is to eventually handle the selection of library imports via the GUI.

Note, this is really intended as an example/demo in the hopes that the techniques and code will be useful to people writing their own programs more than using this as an application. I'm working on a multi-sheet more capable version with plots and a few other neat things but I figured that this may be helpful for some people for the time being.

This was possible in large part due to the efforts of others who were generous enough to share with the world their ideas and software:

Download PySheet_v0.1.zip: https://drive.google.com/file/d/1g6xwY-FC09x7SzXB9yxa9QO8gHvYScq3/view?usp=sharing