dotnet add package ClosedXML.Parser --version 1.0.0-beta4
NuGet\Install-Package ClosedXML.Parser -Version 1.0.0-beta4
<PackageReference Include="ClosedXML.Parser" Version="1.0.0-beta4" />
paket add ClosedXML.Parser --version 1.0.0-beta4
#r "nuget: ClosedXML.Parser, 1.0.0-beta4"
// Install ClosedXML.Parser as a Cake Addin #addin nuget:?package=ClosedXML.Parser&version=1.0.0-beta4&prerelease // Install ClosedXML.Parser as a Cake Tool #tool nuget:?package=ClosedXML.Parser&version=1.0.0-beta4&prerelease
ClosedParser is a project to parse OOXML grammar to create an abstract syntax tree that can be later evaluated.
Official source for the grammar is MS-XML, chapter 2.2.2 Formulas. The provided grammar is not usable for parser generators, it's full of ambiguities and the rules don't take into account operator precedence.
How to use
- Call parsing methods
FormulaParser<TScalarValue, TNode>.CellFormulaA1("Sum(A1, 2)", astFactory)
FormulaParser<TScalarValue, TNode>.CellFormulaR1C1("Sum(R1C1, 2)", astFactory)
There is a visualizer to display AST in a browser at https://parser.closedxml.io
- Performance - ClosedXML needs to parse formula really fast. Limit allocation and so on.
- Evaluation oriented - Parser should concentrates on creation of abstract syntax trees, not concrete syntax tree. Goal is evaluation of formulas, not transformation.
- Multi-use - Formulas are mostly used in cells, but there are other places with different grammar rules (e.g. sparklines, data validation)
- Multi notation (A1 or R1C1) - Parser should be able to parse both A1 and R1C1 formulas. I.e.
SUM(R5)can mean return sum of cell
R5in A1 notation, but return sum of all cells on row 5 in R1C1 notation.
Project uses ANTLR4 grammar file as the source of truth and a lexer. There is also ANTLR parser is not used, but is used as a basis of recursive descent parser (ANTLR takes up 8 seconds vs RDS 700ms for parsing of enron dataset).
ANTLR4 one of few maintained parser generators with C# target.
The project has a low priority, XLParser mostly works, but in the long term, replacement is likely.
ENRON dataset parsed using recursive descent parser and DFA lexer in Release mode:
- Total: 946320
- Elapsed: 1838 ms
- Per formula: 1.942 μs
2μs per formula should be something like 6000 instructions (under unrealistic assumption 1 instruction per 1 Hz), so basically fast enough.
The primary goal is to parse formulas stored in file, not user supplied formulas. The formulas displayed in the GUI is not the same as formula stored in the file. Several examples:
- The IFS function is a part of future functions. In the file, it is stored as
_xlfn.IFS, but user sees
- In the structured references, user sees @ as an indication that structured references this row, but in reality it is a specifier
- External references are accepted only in form of an index to an external file (e.g.
- There are several formula implementations out there with slighly different grammar incompatible with OOXML formulas (
!'Some name in external wb'). They are out of scope of the project.
Why not use XLParser
ClosedXML is currently using XLParser and transforming the concrete syntax tree to abstract syntax tree.
- Grammar extensively uses regexps extensively. Regexs are slow, especially for NET4x target, allocates extra memory. XLParser takes up 47 seconds for Enron dataset on .NET Framework. .NET teams had made massive improvements on regexs, so it takes only 16 seconds on NET7.
- IronParser needs to determine all possible tokens after every token, that is problematic, even with the help of
- AST: XLParser creates concentrates on creation of concrete syntax tree, but for ClosedXML, we need abstract syntax tree for evaluation. IronParser is not very friendly in that regard
XLParser uses(IronParser recently released version 1.2).
IronParser, an unmaintained project
- Doesn't have support for lambdas and R1C1 style.
ANTLR lexer takes up about 3.2 seconds for Enron dataset. With ANTLR parsing, it takes up 11 seconds. I want that 7+ seconds in performance and no allocation, so RDS that takes up 700 ms.
Use vscode-antlr4 plugin for debugging the grammar.
- Each token that contains some data that are extracted for a node (e.g.
column 3) has a separate test class in
Lexersdirectory with a
- Each parser rule has a test class in
Rulesdirectory. It should contain all possible combinatins of a rule and comparing it with the AST nodes.
- Data set tests are in
DataSetTests.cs. Each test tries to parse formula and ensures that ANTLR can parse it RDS can and can't parse a formula when ANTLR can't. There is no check of the output, just that formulas can be parsed. Data are contained in a
datadirectory in CSV format with a one column.
Rolex is a DFA based lexer released under MIT license (see Rolex: Unicode Enabled Lexer Generator in C#). ANTLR is still the source of truth, but it is used to generate Rolex grammar and then DFA for a lexer.
It is rather complicated, but two times faster than ANTLR lexer (1.9 us vs 3.676 us per formula).
Prepare rolex grammars
- Run Antlr2Rolex over FormulaLexer.g4 with A1 version to ClosedXML.Parser\Rolex\LexerA1.rl
/*at the beginning of Local A1 References section. It comments out A1_REFERENCE and all its fragments
/*at the beinning of Local R1C1 References section. It contains a different tokens for A1_REFERENCE and its fragments
- Run Antlr2Rolex over FormulaLexer.g4 with R1C1 version to ClosedXML.Parser\Rolex\LexerR1C1.rl
Fix Rolex generator
- Fix bug in Rolex generator that doesn't recognize property \u1234 (just add
Generate a DFA through Rolex
Rolex.exe ClosedXML.Parser\Rolex\LexerA1.rl /noshared /output ClosedXML.Parser\Rolex\RolexA1Dfa.cs /namespace ClosedXML.Parser.Rolex
Rolex.exe ClosedXML.Parser\Rolex\LexerR1C1.rl /noshared /output ClosedXML.Parser\Rolex\RolexR1C1Dfa.cs /namespace ClosedXML.Parser.Rolex
- Lexer generation during build
- Proper CI pipeline.
- Azure Function
- PR to Rolex to fix unicode bug.
|Product||Versions Compatible and additional computed target framework versions.|
|.NET||net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed.|
|.NET Core||netcoreapp2.0 was computed. netcoreapp2.1 was computed. netcoreapp2.2 was computed. netcoreapp3.0 was computed. netcoreapp3.1 was computed.|
|.NET Standard||netstandard2.0 is compatible. netstandard2.1 is compatible.|
|.NET Framework||net461 was computed. net462 was computed. net463 was computed. net47 was computed. net471 was computed. net472 was computed. net48 was computed. net481 was computed.|
|MonoAndroid||monoandroid was computed.|
|MonoMac||monomac was computed.|
|MonoTouch||monotouch was computed.|
|Tizen||tizen40 was computed. tizen60 was computed.|
|Xamarin.iOS||xamarinios was computed.|
|Xamarin.Mac||xamarinmac was computed.|
|Xamarin.TVOS||xamarintvos was computed.|
|Xamarin.WatchOS||xamarinwatchos was computed.|
- System.Memory (>= 4.5.4)
- No dependencies.
NuGet packages (1)
Showing the top 1 NuGet packages that depend on ClosedXML.Parser:
See release notes https://github.com/ClosedXML/ClosedXML/releases/tag/0.104.0-preview2 ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.
GitHub repositories (1)
Showing the top 1 popular GitHub repositories that depend on ClosedXML.Parser:
ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.