I am looking for a grammar of Excel formulas in BNF or another notation. We need to parse the input formula with JavaScript to check its validity and the actual formula evaluation will then take place on the server side.
I am looking for a grammar of Excel formulas in BNF or another notation. We need to parse the input formula with JavaScript to check its validity and the actual formula evaluation will then take place on the server side.
Share Improve this question edited Mar 16, 2024 at 18:26 adius 15k7 gold badges48 silver badges50 bronze badges asked Dec 9, 2012 at 14:00 RamSafariRamSafari 811 silver badge5 bronze badges 2- Why do you need it? Isn't it sufficient that the server does the validation? – John Dvorak Commented Dec 9, 2012 at 14:03
- Hi Jan, thanks for your ments. We are trying to get the excel spreadsheet in the web browser. We need a way to get the cell references highlighted on the fly when we enter/select the cell, ranges etc. After going through the concepts behind this, we need a way to define the grammar for excel formulas and need to parse the formulas for getting cell references, coloring the cells, identifying the functions etc. To give you more clarity on this, just look at the google docs spreadsheet functionality which we are trying to implement the it. – RamSafari Commented Dec 9, 2012 at 16:37
3 Answers
Reset to default 5MS-XLS has a free-form grammar (tries to be an EBNF but is far from formal)
More hints are available in MS-OE376 and section L.2.16 of ECMA-376.
But to answer your question, there is no official BNF
Section 2.2.2 (page 32) of the [MS-XLSX]: Excel (.xlsx) Extensions to the Office Open XML SpreadsheetML File Format has a useful grammar describing Excel formulas. Other versions of the specification are also available.
Here's a site where the author parses Excel formulas in javascript. There are other versions of his parser available.
http://ewbi.blogs./develops/2004/12/excel_formula_p.html