f7
f7 is a spreadsheet formula execution library
git clone https://git.vogt.world/f7.git
Log | Files | README.md | LICENSE.md
← All files
name: README.md
-rw-r--r--
3435
 1# F7
 2
 3F7 is the spreadsheet formula execution library. It contains a parser, executor, and formulas
 4necessary to run a spreadsheet. That means you can do things
 5like `=(SUM(A1:A9) / COUNT(A1:A9)) * MAX(Sheet1!A1:A)`
 6or basically anything else you'd want to write in a cell in a common spreadsheet.
 7
 8I built it back in 2019 for a product that never ended up launching, and thought I'd open source it.
 9I'm not sure how usable it is in your application. It's not exactly plug-and-play, so to speak, but
10there might be something that someone can learn from it.
11
12I used ANTLR G4 to write grammars that produce JS/TS parser/lexers to get the AST, and then uses an
13engine and some formulas to compute the cell values.
14
15It mostly supports:
16
17* Basic math, including order of operations.
18* Relative and absolute A1-notation references.
19* Sheets.
20* Sheet references.
21* Formulas for engineering, info, logic, math, parser, statistical, and text.
22* Most formulas check types, so they behave similarly to common spreadsheets.
23* Named ranges.
24* Excel-like data types: Boolean, Number, Error, String, Date, Empty.
25* Some Google Sheets formulas, some Excel formulas.
26* Errors that work like Google Sheets including #DIV, and, for example, circular-dependency errors.
27* Array projection using `= {1, 2, 3}` notation.
28* Grid projection using `= {1, 2; 3; 4; 5, 6}` notation.
29
30Here are the things it does not support:
31
32* XML import or export. Since this seemed like the easiest part, I didn't worry about it, assuming
33  I'd be able to find a good XML parser to wrap the models.
34* Good memory management. It stores basically everything using in-memory hash maps.
35* Performance. For example, it mostly uses iteration for hashmap lookups when doing range-based
36  queries.
37* Google Sheets LTR-style, left-hand association of the `^` power operator. (Google Sheets, and
38  Excel are different, so `=2^3^4` in Excel is 4096, but something like 2.417851639E24 in Google
39  Sheets.)
40* Row-column notation.
41
42One thing to note is that exponential operators are left-hand associative. Google Sheets, and Excel
43are different, so `=2^3^4` in Excel is 4096, but something like 2.417851639E24 in Google Sheets. You
44can find out more about these strange discrepancies if you grep the code for "HACK" or "TODO".
45
46If I had to do this again, I would do it very differently. But I certainly had fun the first time.
47
48## License
49
50Copyright 2022 Ben Vogt.
51
52Permission is hereby granted, free of charge, to any person obtaining a copy of this software and
53associated documentation files (the "Software"), to deal in the Software without restriction,
54including without limitation the rights to use, copy, modify, merge, publish, distribute,
55sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is
56furnished to do so, subject to the following conditions:
57
58The above copyright notice and this permission notice shall be included in all copies or substantial
59portions of the Software.
60
61THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT
62NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
63NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES
64OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
65CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.