Releases: ClosedXML/ClosedXML
0.102.2
0.104.0-preview2
Second test release for checking SourceLink support on nuget (first failed due to fody/PDB checksum) #2070
0.104.0-preview1
Test release for checking SourceLink support #2070
0.103.0-beta
There won't be a non-beta release for 0.103. The production release will be 0.104, not 0.103. This milestone was about fixing technical debt, but ultimately it needs some more time to mature before it is sent to the users.
There are some nice performance updates, so in spirit of release early, release often, there will be a beta package on nuget.
Breaking changes
Rich text is now immutable behind the scenes (and will likely be turned into immutable in the future). It should be transparent to the user, though IXLPhonetic no longer has a setter for its IXLPhonetic.Text, IXLPhonetic.Start and IXLPhonetic.End properties.
New calculation engine just works in a different way and will behave differently.
Significant changes
XLParser replaced with ClosedParser
The XLParser has been replaced with ClosedParser. The key benefits are
- performance - ~2μs/formula, it's likely formulas will be parseable on the demand, necessary for construction of dependency tree
- A1/R1C1 parsing parity - both modes can be parsed with no problems
- AST oriented - it's likely a construction of AST in memory won't even be necessary, just use AST factory to evaluate formula directly
There is also a visualizer to display AST in a browser at https://parser.closedxml.io
Formula Calculation
In previous version, formulas used to be calculated recursively. Each formula checked it's supporting cells for other formulas and if there were some, they were recursively evaluated. There was some logic to decrease number of evaluations. That works for a very simple cases, but isn't very good for various non-happy paths (i.e. cells weren't calculated when they should be).
This version has replaced it with a standard
- dependency tree for checking which formulas are dirty and need to be recalculated
- calculation chain that manages dependencies and order of formulas during calculation
For more info, see docs, the Microsoft has a page about principles Excel Recalculation
and there is one with API at docs.closedxml.io.
Workbook structure
Internal structure has been cleaned up and optimized.
The dirty tracking has been moved out of cells to formulas and thus memory taken up by a single cell value is now only 16 bytes instead of 24 (?) bytes in 0.102. Of course there are some other structures around that take up memory as well, but the single cell value is now 16 bytes (I hoped for 8, but not feasible with double, DateTime and TimeSpan as possible cell values - all take up 8 bytes... not enough bits).
The same string in different instances is now not duplicated, but only one instance is used. As seen on following test, it can lead to significant decrease in memory consumption. 250k rows with 10 text rows (same string, different instance): 117 MiB om 0.103 vs 325 MiB in 0.102.1.
InsertData performance
Insert 250k rows of 10 columns of text and 5 columns of numbers (gist).
| Description | Rows | Columns | Time/Memory to insert data | Save workbook | Total time/memory |
|---|---|---|---|---|---|
| 0.103.0-beta | 250 000 | 15 | 1.619 sec / 117 MiB | 6.343 sec | 477 MiB |
| 0.102.1 | 250 000 | 15 | 7.160 sec / 325 MiB | 6.676 sec | 692 MiB |
Loading of cells is now done through streaming
Basically workbooks with a large amount of cells should see ~15%-20% speedup (as long as there are mainly values, not styles or OLAP metadata....).
Reading the 250k from previous chapter:
| Description | Rows | Columns | Time to load data | Used memory |
|---|---|---|---|---|
| 0.103.0-beta | 250 000 | 15 | 15.648 sec | 236 MiB |
| 0.102.1 | 250 000 | 15 | 20.460 sec | 329 MiB |
What's Changed
Technical debt
- Add shared string table for plain text by @jahav in #2115
- Store rich text as an immutable rich text by @jahav in #2116
- Save SST part directly from SST instance. by @jahav in #2118
- Replace XLParser with ClosedParser. by @jahav in #2138
- Get areas a formula depends on by @jahav in #2152
- An initial work on a dependency tree for formulas by @jahav in #2155
- Add names areas to dependency tree by @jahav in #2156
- Add API to remove cell formula from dependency tree by @jahav in #2160
- Add XLCalculationChain by @jahav in #2167
- Add ability to detect cycle in calculation chain by @jahav in #2169
- Evaluate calculation chain by @jahav in #2172
- Move saving of parts into separate writers by @jahav in #2177
Performance improvements
- Convert InsertData to streaming&bulk by @jahav in #2173
- Load cells from workbook using a streaming by @jahav in #2174
Features
- Add FontScheme property to a font by @jahav in #2114
- Implement loading of workbook theme colors by @sbeca in #2117
Bugfixes
- update accessibility of string.Contains(char) polyfill to internal by @Applesauce314 in #2134
- Make SheetId a unique across XLWorksheets by @jahav in #2142
- Fix incorrect logic check in Array Rescale by @sbeca in #2157
- Fix ROUND (and probably others) not handling the result of binary operations on refs by @sbeca in #2153
Documentation
- Add themes documentation by @jahav in #2154
- Add documentation about how formulas are calculated. by @jahav in #2176
New Contributors
- @Applesauce314 made their first contribution in #2134
- @sbeca made their first contribution in #2117
Full Changelog: 0.102.0...0.103.0-beta
Contributors
Assets 2
0.102.1 - SixLabors.Fonts dependency update
SixLabors.Fonts has released version 1.0.0 and some NET Framework projects suddently have errors due to NuGet behavior.
If a project is consuming ClosedXML through package.config instead of PackageReference style projects, the NuGet will resolve version 1.0.0 instead of declared beta19 dependency. SixLabors.Fonts has API changes and thus it will start to throw MissingMethodExceptions.
The issue should only affect net framework projects, not dotnet core that use PackageReference style by default.
What's Changed
Full Changelog: 0.102.0...0.102.1
Contributors
Assets 2
0.102.0
Breaking changes
Please read migration guide from 0.101 to 0.102. The key ones changes are:
IXLCellis now a proxy to a sparse array and a new proxy is created each time it is requested by user code.Object.ReferenceEquals(ws.Cell("A1"), ws.Cell("A1"))now evaluates tofalse(used to betrue)IXLWorksheet AddWorksheet(DataTable dataTable)andIXLWorksheet AddWorksheet(DataTable dataTable, string sheetName)now use different name for the created table.
Significant changes
Replaced cell storage engine (#1969)
The cells in a workbook used to be stored in a Dictionary<int, Dictionary<XLCell>>. That has several significant drawbacks and the storage has been replaced with a sparse arrays of individual slices (basically a sparse array containing a specific part of a cell).
Key benefits (in long term):
- Decrease in memory consumption - sparse arrays don't use values that aren't use, from esoteric OLAP cube id to styles.
- Bulk operations - there was pretty much no way to optimize bulk operations. Clear - go through each cell. With sparse arrays, ClosedXML can operate on pices of an array (e.g. preallocate for large inserts, clear an area).
- Most operations use only data from a slice (e.g. setting a formula doesn't have to check all cells, only those that contain formula). Due to nature of sparse array of a slice, I only need to go through cells that contains actually useful content.
- Easier insertion/deletion of rows. XLCell originally contain an address and when a row was inserted, I had to go though all cells and fix address (+ 20 other things).
Other than memory, it's a potential for the future. Replacing a storage engine is not simple and pretty much everything uses XLCell adapter.
An example of different for 500k rows of value only cells (gist). About 200MB vs 900MB.
Embedded fonts (#2106)
Default graphic engine of ClosedXML now contains an embedded font. That should be a quality of life improvement for users on Linux and other non-Windows environment who encountered
Unable to find font font name or fallback font fallback font name. Install missing fonts or specify a different fallback font through ‘LoadOptions.DefaultGraphicEngine = new DefaultGraphicEngine(“Fallback font name”)’..
The embedded font is an absolute bare bones Carlito font (though with a different name to avoid collision with the real one).
See doc for workflow of font selection: https://closedxml.readthedocs.io/en/latest/features/graphic-engine.html#fallback-and-embedded-font
Array formulas
A basic support for array formulas has been added. You can create array formula through IXLRangeBase object.
csharp ws.Range("B2:D3").FormulaArrayA1 = "B1:D1*POWER(1+A2:A3, 2)";
For more info, see doc: https://closedxml.readthedocs.io/en/latest/features/formulas.html#array-formulas
Refactored pivot source (#1238)
The ancient PR from 2019 has been finally fixed and merged. It doesn't really add very useful features (multiple pivot tables don't have to have individual data store files in a xlsx file), but it is a big step to represent pivot cache data in a workbook. That is pretty much required to do anything useful with pivot tables.
The biggest visible improvement is that there is at least some very basic documentation about pivot tables https://closedxml.readthedocs.io/en/latest/features/pivot-tables.html
Bugfix: Normal style is not required in a workbook (#2102)
Some other OOXML producers (e.g. NPOI) don't add Normal style into a stylesheet and in 0.101 a feature had a missing null check leading to NullReferenceException.
Misc info & next release
Not really something that is useful at this moment, but there has been significant work done on a replacement of a XLParser (https://github.com/ClosedXML/ClosedXML.Parser).
I hope to be done with the Phase 1 of my maintainership - architecture and dependencies. Most of the "OMG, this must be done before anything else" has been done. I hope to finish the rest (split writers of individual files from 5000+LOC file, use better parser and use calculation chain for formula evaluation) in the next one.
What's Changed
Performance improvements
- Use spare arrays to store cell values. by @jahav in #2093
- Having many hyperlinks caused slow save by @jahav in #2076
- Fix Stack Overflow exception and optimize for bigger datasets by @JakubTracz in #2042
Features
- Add ability to read WebP images by @jahav in #2071
- Implement array formulas by @jahav in #2082
- Include an embedded font to ClosedXML by @jahav in #2106
- Consolidated and refactored pivot sources by @igitur in #1238
- Add ability to set table name when adding a worksheet with datatable … by @NickNack2020 in #2044
Bugfixes
- SUMIFS multiple dimensions by @igitur in #2061
- Handle different number types of NumberLiteral by @jahav in #2058
- Don't fail to load empty style part by @jahav in #2102
- Workbook with drawing kept original DOM by @jahav in #2108
Documentation
- Update IXLCell.cs Wrong summary on GetDouble() by @MortenSpjotvoll in #2059
- Enable nullables in main project by @lahma in #2056
Quality of life
New Contributors
- @MortenSpjotvoll made their first contribution in #2059
- @JakubTracz made their first contribution in #2042
Full Changelog: 0.101.0...0.102.0
Contributors
Assets 2
0.102.0-rc: Stepping stones
Significant changes
Replaced cell storage engine (#1969)
The cells in a workbook used to be stored in a Dictionary<int, Dictionary<XLCell>>. That has several significant drawbacks and the storage has been replaced with a sparse arrays of individual slices (basically a sparse array containing a specific part of a cell).
Key benefits (in long term):
- Decrease in memory consumption - sparse arrays don't use values that aren't use, from esoteric OLAP cube id to styles.
- Bulk operations - there was pretty much no way to optimize bulk operations. Clear - go through each cell. With sparse arrays, ClosedXML can operate on pices of an array (e.g. preallocate for large inserts, clear an area).
- Most operations use only data from a slice (e.g. setting a formula doesn't have to check all cells, only those that contain formula). Due to nature of sparse array of a slice, I only need to go through cells that contains actually useful content.
- Easier insertion/deletion of rows. XLCell originally contain an address and when a row was inserted, I had to go though all cells and fix address (+ 20 other things).
Other than memory, it's a potential for the future. Replacing a storage engine is not simple and pretty much everything uses XLCell adapter.
An example of different for 500k rows of value only cells (gist). About 200MB vs 900MB.
Embedded fonts (#2106)
Default graphic engine of ClosedXML now contains an embedded font. That should be a quality of life improvement for users on Linux and other non-Windows environment who encountered
Unable to find font font name or fallback font fallback font name. Install missing fonts or specify a different fallback font through ‘LoadOptions.DefaultGraphicEngine = new DefaultGraphicEngine(“Fallback font name”)’..
The embedded font is an absolute bare bones Carlito font (though with a different name to avoid collision with the real one).
See doc for workflow of font selection: https://closedxml.readthedocs.io/en/latest/features/graphic-engine.html#fallback-and-embedded-font
Array formulas
A basic support for array formulas has been added. You can create array formula through IXLRangeBase object.
csharp ws.Range("B2:D3").FormulaArrayA1 = "B1:D1*POWER(1+A2:A3, 2)";
For more info, see doc: https://closedxml.readthedocs.io/en/latest/features/formulas.html#array-formulas
Refactored pivot source (#1238)
The ancient PR from 2019 has been finally fixed and merged. It doesn't really add very useful features (multiple pivot tables don't have to have individual data store files in a xlsx file), but it is a big step to represent pivot cache data in a workbook. That is pretty much required to do anything useful with pivot tables.
The biggest visible improvement is that there is at least some very basic documentation about pivot tables https://closedxml.readthedocs.io/en/latest/features/pivot-tables.html
Misc info & next release
Not really something that is useful at this moment, but there has been significant work done on a replacement of a XLParser (https://github.com/ClosedXML/ClosedXML.Parser).
I hope to be done with the Phase 1 of my maintainership - architecture and dependencies. Most of the "OMG, this must be done before anything else" has been done. I hope to finish the rest (split writers of individual files from 5000+LOC file, use better parser and use calculation chain for formula evaluation) in the next one.
What's Changed
Performance improvements
- Having many hyperlinks caused slow save by @jahav in #2076
- Fix Stack Overflow exception and optimize for bigger datasets by @JakubTracz in #2042
- Use spare arrays to store cell values. by @jahav in #2093
Features
- Add ability to read WebP images by @jahav in #2071
- Implement array formulas by @jahav in #2082
- Include an embedded font to ClosedXML by @jahav in #2106
- Consolidated and refactored pivot sources by @igitur in #1238
- Add ability to set table name when adding a worksheet with datatable … by @NickNack2020 in
Bugfixes
- SUMIFS multiple dimensions by @igitur in #2061
- Handle different number types of NumberLiteral by @jahav in #2058
- Don't fail to load empty style part by @jahav in #2102
Documentation
- Update IXLCell.cs Wrong summary on GetDouble() by @MortenSpjotvoll in #2059
- Enable nullables in main project by @lahma in #2056
Quality of life
New Contributors
- @MortenSpjotvoll made their first contribution in #2059
- @JakubTracz made their first contribution in #2042
Full Changelog: 0.101.0...0.102-rc
Contributors
Assets 2
0.101.0
Mostly speed improvements, but some cool and nifty improvements, see changelog below.
Other than some enums being changed from int to byte, and a new method on IXLGraphicEngine interface there should be no breaking changes in the release (see https://closedxml.readthedocs.io/en/latest/migrations/migrate-to-0.101.html).
Worksheet saving (#1838)
Saving of cells in a worksheet has been significantly improved. As an example, a file of 100k rows and 44 columns (see gist) took 38 seconds to save in 0.100.3, but only 17 seconds in 0.101-rc .
Adjust to content (#1991)
Adjust to content has been refactored and it now calculates size of text by counting glyphs. Originally, we asked SixLabors.Fonts library to calculate it, but it is really slow (it has to deal with all possible typographic options, like having rtl and ltr text on same line).
It is faster to just use glyph size, plus I am pretty sure Excel also calculates width/height of a cell that way (likely legacy from earlier 90s version).
Time to run AdjustToContent using a sample of 1000 rows.
| Run# | 0.96.0 | 0.100.3 | 0.101-rc |
|---|---|---|---|
| 1 | 168 ms | 1203 ms | 1674 ms |
| 2 | 4 ms | 568 ms | 31 ms |
| 3 | 4 ms | 551 ms | 25 ms |
| 4 | 4 ms | 540 ms | 26 ms |
| 5 | 3 ms | 544 ms | 25 ms |
| 6 | 3 ms | 550 ms | 26 ms |
| 7 | 4 ms | 556 ms | 46 ms |
| 8 | 3 ms | 556 ms | 28 ms |
| 9 | 4 ms | 541 ms | 23 ms |
| 10 | 3 ms | 535 ms | 23 ms |
Changelog
Performance improvements
- Improve performance when opening workbook by @lahma in #1998
- Change ExpressionCache to use ConditionalWeakTable by @lahma in #1968
- Separate XLCellFormula out of XLCell by @jahav in #1972
- Don't allocate XLCellFormula for each XLCell by @jahav in #2043
- Speedup adjust to content by @jahav in #2037
- Save SheetData through XmlWriter by @jahav in #1984
- Reimplement SEARCH function and add wildcard struct by @jahav in #2007
Features
- Calculate default column width from default font. by @nakamura2000 in #1954
- Add ability to load and save data table formulae by @jahav in #1974
- ROWS and COLUMNS functions by @jahav in #1981
Cleanup updates
- Remove unnecessary reference to Microsoft.CSharp by @lahma in #1999
- Simplify depdendency graph for newer target frameworks by @georg-jung in #1973
- Add a new XLCellValue.FromObject() method by @0xced in #1982
- Create SECURITY.md by @punkd23 in #1756
- Fix warnings by @leotsarev in #1695
- Update fonts library to beta19 by @jahav in #1971
Bugfixes
- fix: mixed types in table throws exception on save by @kappni in #2026
- Use space as culture group separator in AutoFilterTests by @lahma in #2000
- Fix NRE during normal style loading by @lahma in #2051
- Fix reserved fill value checking by @jahav in #1977
- Output unicode codepoints from astral planes by @jahav in #1978
- Remove exception from FIND function. by @jahav in #2018
- Fix negative text orientation. by @jahav in #2040
- Formula hyperlinks shouldn't have nodes in worksheet by @jahav in #2055
Documentation
- Add documentation about tables feature by @jahav in #2029
- Update Contributing - How to reconcile files by @NickNack2020 in #2045
New Contributors
- @lahma made their first contribution in #1968
- @georg-jung made their first contribution in #1973
- @punkd23 made their first contribution in #1756
- @kappni made their first contribution in #2026
Full Changelog: 0.100.3...0.101.0
Contributors
Assets 2
0.101.0 Release Candidate
Mostly speed improvements, but some cool and nifty improvements, see changelog below. Prod release will be 1 week after RC.
Other than some enums being changed from int to byte, and a new method on IXLGraphicEngine interface there should be no breaking changes in the release (see https://closedxml.readthedocs.io/en/latest/migrations/migrate-to-0.101.html).
Worksheet saving (#1838)
Saving of cells in a worksheet has been significantly improved. As an example, a file of 100k rows and 44 columns (see gist) took 38 seconds to save in 0.100.3, but only 17 seconds in 0.101-rc .
Adjust to content (#1991)
Adjust to content has been refactored and it now calculates size of text by counting glyphs. Originally, we asked SixLabors.Fonts library to calculate it, but it is really slow (it has to deal with all possible typographic options, like having rtl and ltr text on same line).
It is faster to just use glyph size, plus I am pretty sure Excel also calculates width/height of a cell that way (likely legacy from earlier 90s version).
Time to run AdjustToContent using a sample of 1000 rows.
| Run# | 0.96.0 | 0.100.3 | 0.101-rc |
|---|---|---|---|
| 1 | 168 ms | 1203 ms | 1674 ms |
| 2 | 4 ms | 568 ms | 31 ms |
| 3 | 4 ms | 551 ms | 25 ms |
| 4 | 4 ms | 540 ms | 26 ms |
| 5 | 3 ms | 544 ms | 25 ms |
| 6 | 3 ms | 550 ms | 26 ms |
| 7 | 4 ms | 556 ms | 46 ms |
| 8 | 3 ms | 556 ms | 28 ms |
| 9 | 4 ms | 541 ms | 23 ms |
| 10 | 3 ms | 535 ms | 23 ms |
Changelog
Performance improvements
- Improve performance when opening workbook by @lahma in #1998
- Change ExpressionCache to use ConditionalWeakTable by @lahma in #1968
- Separate XLCellFormula out of XLCell by @jahav in #1972
- Don't allocate XLCellFormula for each XLCell by @jahav in #2043
- Speedup adjust to content by @jahav in #2037
- Save SheetData through XmlWriter by @jahav in #1984
- Reimplement SEARCH function and add wildcard struct by @jahav in #2007
Features
- Calculate default column width from default font. by @nakamura2000 in #1954
- Add ability to load and save data table formulae by @jahav in #1974
- ROWS and COLUMNS functions by @jahav in #1981
Cleanup updates
- Remove unnecessary reference to Microsoft.CSharp by @lahma in #1999
- Simplify depdendency graph for newer target frameworks by @georg-jung in #1973
- Add a new XLCellValue.FromObject() method by @0xced in #1982
- Create SECURITY.md by @punkd23 in #1756
- Fix warnings by @leotsarev in #1695
- Update fonts library to beta19 by @jahav in #1971
Bugfix
- fix: mixed types in table throws exception on save by @kappni in #2026
- Use space as culture group separator in AutoFilterTests by @lahma in #2000
- Fix reserved fill value checking by @jahav in #1977
- Output unicode codepoints from astral planes by @jahav in #1978
- Remove exception from FIND function. by @jahav in #2018
- Fix negative text orientation. by @jahav in #2040
Documentation
New Contributors
- @lahma made their first contribution in #1968
- @georg-jung made their first contribution in #1973
- @punkd23 made their first contribution in #1756
- @kappni made their first contribution in #2026
Full Changelog: 0.100.3...0.101.0
Contributors
Assets 2
0.100.3 Bulk insert of numbers
Fix a regression where some types of numbers were inserted as text by InsertData/InsertTable API.
// Only int and double were inserted as numbers, now all types are
cell.InsertData(new object[] { (sbyte)1, (byte)2, (short)3, (ushort)4, (uint)6, (long)7, (ulong)8, 15f, 17m });
What's Changed
Full Changelog: 0.100.2...0.100.3