What's Excel's spec?
December 12, 2020 12:48 PM Subscribe
In short I'm asked to build a program that takes a complex site and turn it into an Excel sheet. Ignoring that this is a huge request, I'm trying to define what Excel formulas actually are. Like in a normal language at some point I'd build an abstract syntax tree to represent the language, this is what I'm trying to do with things like =SUM(A1:B3) ... but I'm having a hard time figuring out my question because Google is full of answers on how to use Excel.
Basically a company has a bunch of programs that do all kinds of things but the output is an Excel to the client. If it was just a csv that'd be really easy but a lot of times they want basically the functions in Typescript to be maintained in Excel only trnaspiled into whatever Excel uses. There's a lot of other requirements like using Excel's native filtering capability, etc. and have the document output with all that. I know Excel uses VBA and the answer might be "transpile into VBA" but I think the idea is that the end user gets basically an Excel sheet they know how to use and manipulate, most people don't use VBA. It could be that VBA generates those formulas so compile whatever into VBA and the formulas magically appear. It doesn't really matter.
How should I look at this? I know Microsoft recently announced "lambda" which is kinda solving what I'm trying to do minus some of the other functionality. I have been told that for the purposes of this working against desktop versions of Excel is what's needed so if there's undocumented limitations to the web version I don't have to worry about that.
I'm sure I'm not the first person to do this. The people I'm dealing with are very smart but there's still an underlying notion that renaming a csv to xlsx makes it in Excel how hard can it be to get the formulas there? My thought is compiling a method in language X so it works in language Y is not trivial especially when the language is proprietary. Any pointers on how to approach this or what I should google so if I tell them 6 months and 5 engineers I can at least back it up.
Basically a company has a bunch of programs that do all kinds of things but the output is an Excel to the client. If it was just a csv that'd be really easy but a lot of times they want basically the functions in Typescript to be maintained in Excel only trnaspiled into whatever Excel uses. There's a lot of other requirements like using Excel's native filtering capability, etc. and have the document output with all that. I know Excel uses VBA and the answer might be "transpile into VBA" but I think the idea is that the end user gets basically an Excel sheet they know how to use and manipulate, most people don't use VBA. It could be that VBA generates those formulas so compile whatever into VBA and the formulas magically appear. It doesn't really matter.
How should I look at this? I know Microsoft recently announced "lambda" which is kinda solving what I'm trying to do minus some of the other functionality. I have been told that for the purposes of this working against desktop versions of Excel is what's needed so if there's undocumented limitations to the web version I don't have to worry about that.
I'm sure I'm not the first person to do this. The people I'm dealing with are very smart but there's still an underlying notion that renaming a csv to xlsx makes it in Excel how hard can it be to get the formulas there? My thought is compiling a method in language X so it works in language Y is not trivial especially when the language is proprietary. Any pointers on how to approach this or what I should google so if I tell them 6 months and 5 engineers I can at least back it up.
Parsing Excel formulas: A grammar and its application on 4 large datasets
posted by jedicus at 1:11 PM on December 12, 2020 [5 favorites]
posted by jedicus at 1:11 PM on December 12, 2020 [5 favorites]
Excel does use VBA to build macros but that is not the same as the formula language used to analyze and manipulate the data, which as far as I know, doesn't correspond to any other specific language. It has later been altered to DAX for Power BI, but that is still different.
So do they want a specific formula inserted into a specific cell upon output creation? Or do they want an excel spreadsheet with a functioning macro that contains certain excel formulas? VBA would accomplish the latter, although I am not sure how you would create a macro outside of Excel, since building them is a very manual process. The former, although I've never tried it, I would insert the formulas as a string via a program like SSIS.
posted by Young Kullervo at 2:28 PM on December 12, 2020
So do they want a specific formula inserted into a specific cell upon output creation? Or do they want an excel spreadsheet with a functioning macro that contains certain excel formulas? VBA would accomplish the latter, although I am not sure how you would create a macro outside of Excel, since building them is a very manual process. The former, although I've never tried it, I would insert the formulas as a string via a program like SSIS.
posted by Young Kullervo at 2:28 PM on December 12, 2020
There are several implementations of Excel formulas parsers / interpreters in GitHub if you want to see how people have analysed this problem before
posted by crocomancer at 2:36 PM on December 12, 2020
posted by crocomancer at 2:36 PM on December 12, 2020
Not sure if Google Sheets is an option, but you can write custom Google Sheets formulas in EcmaScript, which would be a lot easier to translate to from TypeScript.
The idea of transpiling TS to excel formulas hurts my head. If you pull it off, please update us, because I'd love to see it in action!
posted by kaefer at 3:10 PM on December 12, 2020 [2 favorites]
The idea of transpiling TS to excel formulas hurts my head. If you pull it off, please update us, because I'd love to see it in action!
posted by kaefer at 3:10 PM on December 12, 2020 [2 favorites]
If your goal is to create "proper" Excel spreadsheets, there are lots of software libraries out there which allow you to create Excel workbooks in either XLSX or XLS format, with all the formatting, filtering and formulae you want. They're not much harder to use than a CSV generator. A popular, free one for Java is Apache POI.
If you're trying to embed custom functionality into an Excel workbook, then the Office Add-in framework uses standard web technologies. You're basically creating a website which uses Excel as it's browser, but with access to everything Excel has to offer. There are lots of custom Excel plugin frameworks out there, but the Microsoft official version has many advantages.
If you're trying to write a replacement for Excel, I'd be very wary and want to understand the requirements clearly. There's a risk you're being asked to build a system that can do anything, which is a recipe for disaster. Once you have a clear specification, if you need visualisation/editing tools similar to Excel, many web grid frameworks, such as agGrid, can handle those requirements. If you need to create a custom language, then you might want to start researching "Domain Specific Languages".
posted by matsho at 3:40 PM on December 12, 2020 [4 favorites]
If you're trying to embed custom functionality into an Excel workbook, then the Office Add-in framework uses standard web technologies. You're basically creating a website which uses Excel as it's browser, but with access to everything Excel has to offer. There are lots of custom Excel plugin frameworks out there, but the Microsoft official version has many advantages.
If you're trying to write a replacement for Excel, I'd be very wary and want to understand the requirements clearly. There's a risk you're being asked to build a system that can do anything, which is a recipe for disaster. Once you have a clear specification, if you need visualisation/editing tools similar to Excel, many web grid frameworks, such as agGrid, can handle those requirements. If you need to create a custom language, then you might want to start researching "Domain Specific Languages".
posted by matsho at 3:40 PM on December 12, 2020 [4 favorites]
To create a custom formula in Excel, use VBA. And you'll want to create .xlsm files, macro enabled xls
posted by at at 9:02 PM on December 12, 2020
posted by at at 9:02 PM on December 12, 2020
This thread is closed to new comments.
Generally, I'd suggest that Excel formulas are significantly less flexible than Typescript; creating Excel spreadsheets that replicate a website is not a common requirement.
If I were you, I'd try to get a clearer requirement of what the Typescript is doing for them, and see if there's a way to natively replicate it in Excel. Then, I'd build something that takes their website in some standard format - presumably they've got the information they want in Excel as serialized data somewhere? - and generates an XLSX using, say, XlsxWriter.
posted by sagc at 1:04 PM on December 12, 2020 [1 favorite]