2007.04.26 09:26 AM

Excel Formula Parsing News

Earlier this month, a reader named Tjaard commented on my original Excel Formula Parsing post that the JavaScript parser failed to account for numbers in formulas formatted using scientific notation (e.g., =3.1E-24-2.1E-24). Never having used scientific notation for literal numbers in formulas, I didn't even realize it was possible. It is, so I've updated the VS2K5 solution linked to in the subsequent Excel Formula Parsing, in C# post and I'm heading over to fix the JavaScript parser now. Tjaard, thanks for bringing this to my attention.

In other news, I got word from Matt Brown, a core contributor to the Prototype-based Rico JavaScript library, that beta 1 of Rico 2.0 is now available for download and that its LiveGrid now supports a number of spreadsheet-like features, including formulas (see the help link on the example page for details on the supported functions). More importantly, at least from my perspective, is that Matt was able to incorporate the JavaScript version of my parser into his work and by his own reckoning it "really accelerated things". That's pretty cool. Best of luck to the Rico team with their new version.

Finally, if you've been following along, you might remember that back in November Robin Macharg kindly provided us with a Python port of the original JavaScript parser. Robin shared the code by pasting it into a comment on the post, but the comment ate the code's indentation. So, I was delighted to receive the following note from Robin late last month:

I've attached an updated version of the port, along with a colourised HTML version. Since Python is indentation-sensitive I'd really appreciate it if you could put both copies up on the blog; it will make the port much more accessible to fans of the snake.

I've used the excel formula parser to help with a tool that converts Excel workbooks into standalone (no Excel - or Windows -required) python programs. They use an inhouse dependency tracking library (to handle A1=B1+C1 where A1 depends on B1 and C1. Change the value of B1 and the value of A1 also changes) but could just as easily use something like Cells/pyCells (see e.g. http://pycells.pdxcb.net/). This allows us to effectively stick the functionality of legacy xls files on a big-iron unix web-server as CGI. Getting a token stream was the first (and hardest) step, so again, a BIG thanks.

I've now got to try and do the same with old Lisp code!

I couldn't get my CSS and TypePad template to play nicely with Robin's HTML, so I'm just going to link to the files: here's the colorized HTML and here's the actual Python code. Also, if you're interested in this kind of thing, I urge you to check out the PyCells link that Robin provided - pretty cool stuff. Robin, thanks again for sharing this with everyone.

Carla | 2007.04.30 02:56 PM

testing

ewbi.develops | 2010.01.11 07:57 PM

Note that Excel files store formulas in a byte-code form. When loaded in Excel, the formulas are translated into a text form that may be specific to the locale. An alternative might be to use a library that reads Excel files directly. E.g. xlrd for Python--although its formula parsing is experimental.

Craig McQueen | 2010.02.18 01:53 AM