A C# Excel formula parser with the following properties:
- High compatiblity
XLParser has been tested on over a million real-world formulas and has a 99.9% succesful parse rate.
- Compact parse trees
XLParser was designed for and is used in research of Excel spreadsheets and refactoring, all of which are easier if parse trees are smaller
- Compact grammar
Our grammar contains less than 100 tokens and production rules, and is thus easy to implement in another language or parser generator.
You can parse formulas and view parse trees in the online demo.
Use the NuGet package.
- Download the latest release
- Extract somewhere convenient
- Build Irony GrammarExplorer, see the Debugging section below, and open it
- Click on the
...button at the top and select
- Point to the
XLParser.dllfile in the folder you extracted and click ok
- You can now parse formulas and see the trees in the
XLParser is the reference implementation of the Excel grammar published in the paper "A Grammar for Spreadsheet Formulas Evaluated on Two Large Datasets" by Efthimia Aivaloglou, David Hoepelman and Felienne Hermans.
XLParser can parse Excel formulas and is intended to facilitate the analysis of spreadsheet formulas, and for that purpose produces compact parse trees. XLParser has a 99.99% success rate on the Enron and EUSES datasets. Note however that XLParser is not very restrictive, and thus might parse formulas that Excel would reject as invalid, keep this in mind when parsing user input with XLParser.
XLParser is based on the C# Irony parser framework.
XLParser.sln file in
src/ in Visual Studio and press build. The dependencies are already included in compiled form in this repository.
Using in your project
The easiest way to add the dependency to your project is with NuGet
ExcelFormulaParser class is your main entry point. You can parse a formula through
ExcelFormulaParser has several useful methods that operate directly on the parse tree like
AllNodes to traverse the whole tree or
GetFunction to get the function name of a node that represents a function call. You can
FormulaAnalyzer provides functionality for analyzing the parse tree. For example,
FormulaAnalyzer.ParserReferences() returns all references (to cells, cell ranges, named ranges, horizontal or vertical ranges) that the formula contains.
Irony, the parser framework XLParser uses, includes a tool called the "grammar explorer". This is a great way to play around with the grammar and parse trees.
To use this tool, you first need to build it once by opening the Irony solution (
lib/Irony/Irony_All.2012.sln) and building it with release configuration. After that you can use the binary in
To load the XLParser grammar, first make sure you have built XLParser. Then open the GrammarExplorer and add the grammar (
... button) from
In Visual Studio you can see the printed version of any node during debugging by adding
yournode.Print(),ac in the watch window.
See the doc folder for further documentation
All files of XLParser are released under the Mozilla Public License 2.0.