XLParser

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 grammarcontains less than 100 tokens and production rules, and is thus easy to implement in another language or parser generator.

Quickstart

You can parse formulas and view parse trees inthe online demo.

or

Usethe NuGet package.

or

1. Download thelatest release
2. Extract somewhere convenient
3. Build Irony GrammarExplorer, see the Debugging section below, and open it
4. Click on the...button at the top and selectAdd Grammar
5. Point to theXLParser.dllfile in the folder you extracted and click ok
6. You can now parse formulas and see the trees in theTesttab

Background

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 theEnronandEUSESdatasets. 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.

Using XLParser

Building

Open theXLParser.slnfile insrc/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 withNuGet

TheExcelFormulaParserclass is your main entry point. You can parse a formula throughExcelFormulaParser.Parse( "yourformula" ).

ExcelFormulaParserhas several useful methods that operate directly on the parse tree likeAllNodesto traverse the whole tree orGetFunctionto get the function name of a node that represents a function call. You canPrintany node.

FormulaAnalyzerprovides 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.

Debugging

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 IronyExplorer solution (lib/IronyExplorer/IronyExplorer.sln) and building it with release configuration. After that you can use the binary inlib/IronyExplorer/src/IronyExplorer.GrammarExplorer/bin/Release/IronyExplorer.GrammarExplorer.exe.

To load the XLParser grammar, first make sure you have built XLParser. Then open the GrammarExplorer and add the grammar (...button) fromsrc/XLParser/bin/Debug/XLParser.dll.

In Visual Studio you can see the printed version of any node during debugging by addingyournode.Print(),acin the watch window.

Documentation

Browse theAPI documentation

Seethe doc folderfor further documentation.

License

All files of XLParser are released under theMozilla Public License 2.0.