safehammad
commented
1 year ago Interesting thought. Yes, the formulae would already be calculated if exported from Excel. We could wait for now until we see a firm use case. And the fully functional S-expression calculator is already useful.
Just for information, in LibreOffice, you can export the formulae rather than the calculated values as follows:
- File > Save As
- Check "Edit filter settings"
- In the resulting dialog, check "Save cell formulas instead of calculated values"
With regard to how we might implement it, if we choose to do so...
Starting from scratch using Instaparse is an option, but there are several infix calculators out there already, for example, I've tried Richard Hull's infix library which works just fine. That said, basheetka is still a single portable script without dependencies, and it might be nice to keep it like that, at least for now. So another option is to roll our own infix calculator and embed a couple of functions in basheetka.bb, perhaps with the help of this blog post from Edd Mann.
nitincodery
When using Excel formulas inside cells, they are typically converted into calculated values. However, S-expressions are retained as strings, while native formulas are not. This means that when you open a CSV file in Excel, any formulas present in the file will be calculated and displayed as their resulting values. If you were to save the file after opening it in Excel, the formulas would be replaced with the calculated values, and the original formulas would be lost.
Entering Excel formulas directly in a text editor can indeed be cumbersome, especially because you don't have the visual aid of cell references. It becomes challenging to keep track of the correct cell references when typing formulas manually.
I tried using Instaparse, a library for parsing grammars, to create a basic grammar for reading Excel formulas in infix notation, and it worked well. However, when entered an Excel formula inside Excel itself and exported it as a CSV file, the formulas were lost, and the file was saved with the calculated values.
Given these limitations, it may seem pointless to add support for reading Excel formulas in a CSV file. Excel treats CSV files as plain data without preserving formulas.