ClosedXML/ClosedXML (ClosedXML)
### [`v0.104.1`](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.104.1)
[Compare Source](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.104.0...0.104.1)
Release notes from [0.102.1](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.102.1) to the 0.104.1.
Summary of breaking changes is available at [docs.closedxml.io](https://docs.closedxml.io/):
- [Migration from 0.102 to 0.103](https://docs.closedxml.io/en/latest/migrations/migrate-to-0.103.html)
- [Migration from 0.103 to 0.104](https://docs.closedxml.io/en/latest/migrations/migrate-to-0.104.html)
- New calculation engine just works in a different way and will behave slightly differently.
### OpenXML SDK
OpenXML SDK has released version 3. The 0.104.0 uses it as a dependency.
### XLParser replaced with ClosedParser
The [*XLParser*](https://redirect.github.com/spreadsheetlab/XLParser) has been replaced with [*ClosedParser*](https://redirect.github.com/ClosedXML/ClosedXML.Parser). 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 ****
#### 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](https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation)
and there is one with API at [docs.closedxml.io](https://docs.closedxml.io/en/latest/concepts/formula-calculation.html).
### Structured references
New parser also allows a basic evaluation of structured references. Format of structured reference must use official grammar, not Excel friendly names (e.g. `Pastry[@Name]` is user-friendly name for `Pastry[[#This Row],[Name]]`). It's now possible to
```csharp
using var wb = new XLWorkbook();
var ws = wb.AddWorksheet();
ws.Cell("A1").InsertTable(new Pastry[]
{
new("Cake", 14),
new("Waffle", 3),
}, "Pastry");
ws.Cell("D1").FormulaA1 = "SUM(Pastry[Price])";
ws.Cell("D3").FormulaA1 = "\"Pastry \" & Pastry[[#This Row],[Name]]";
wb.RecalculateAllFormulas();
Console.WriteLine($"Expected: {17}, Actual: {ws.Cell("D1").Value}");
Console.WriteLine($"Expected: \"Pastry Waffle\", Actual: {ws.Cell("D3").Value}");
```
> Expected: 17, Actual: 17
> Expected: "Pastry Waffle", Actual: Pastry Waffle
#### Renaming sheet updates formulas
When a sheet is renamed, a formula referencing the sheet is also updated. This is a part of long term effort to fix effects of structural changes of a workbook. It will be a long road (e.g. sheet still delete doesn't swicth to `#REF!`),\*\* but is one of basic features that should be working acorss the board.
```csharp
using var wb = new XLWorkbook();
var sheet = wb.AddWorksheet();
var anotherSheet = wb.AddWorksheet("Another");
sheet.Cell("A1").FormulaA1 = "Another!B4";
anotherSheet.Name = "Changed";
Console.WriteLine(sheet.Cell("A1").FormulaA1);
```
> Changed!B4
#### 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](https://gist.github.com/jahav/bdc5fe3c90f25544ca6ae1394bbe3561)).
| 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 |
Of course, this includes all stuff from [0.103.0-beta](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.103.0-beta). Version 0.103 never got a non-beta release.
### Pivot tables
The internal structure of pivot tables, along with most other features, has been completely overhauled. This update should significantly reduce crashes when loading and saving workbooks containing pivot tables.
The main issue with the previous internal structure was that it didn't align with the structure used by OOXML. This was problematic because we need to support all valid files. As a result, we have to handle a wide range of inputs and correctly convert them to our internal structure, which is rather hard. A more clear 1:1 mapping with OOXML is much simpler and more reliable.
### AutoFilter
The Autofilter feature has been revamped, which includes some API changes. Its behavior is now more closely aligned with how Excel operates. The XML documentation provides detailed explanations, and there is a [dedicated documentation page](https://docs.closedxml.io/en/latest/features/autofilter.html). Several bugs have also been fixed.
For more details, refer to the [Autofilter section of the migration guide](https://docs.closedxml.io/en/latest/migrations/migrate-to-0.104.html#autofilter).
### Source link
Although ClosedXML still doesn't have source package (Fody static weaving causes pdb mismatch and nuget will refuse symbol package), there is a source link info in the package.
SourceLink basically takes a repository and a commit from the package and retrieves source from directly from forge (in this case GitHub).
### CommonCrawl dataset
When workbook is a valid one, ClosedXML shouldn't throw on load. That is a rather high priority (more than saving or manipulation). Unfortunately, that is hard to find such areas that cause most problems.
One of activities that was going in a background is trying to use excel files around the internet (found by CommonCrawl) to evaluate how bad it is. There aren't results yet, but it is something that is going on.
### What's Changed
#### Technical debt
- Add shared string table for plain text by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2115](https://redirect.github.com/ClosedXML/ClosedXML/pull/2115)
- Store rich text as an immutable rich text by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2116](https://redirect.github.com/ClosedXML/ClosedXML/pull/2116)
- Save SST part directly from SST instance. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2118](https://redirect.github.com/ClosedXML/ClosedXML/pull/2118)
- Move saving of parts into separate writers by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2177](https://redirect.github.com/ClosedXML/ClosedXML/pull/2177)
- Enable nullable in a few more classes by [@sbeca](https://redirect.github.com/sbeca) in [https://github.com/ClosedXML/ClosedXML/pull/2188](https://redirect.github.com/ClosedXML/ClosedXML/pull/2188)
#### Performance improvements
- Convert InsertData to streaming\&bulk by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2173](https://redirect.github.com/ClosedXML/ClosedXML/pull/2173)
- Load cells from workbook using a streaming by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2174](https://redirect.github.com/ClosedXML/ClosedXML/pull/2174)
- Improve sorting performance by [@igitur](https://redirect.github.com/igitur) in [https://github.com/ClosedXML/ClosedXML/pull/1649](https://redirect.github.com/ClosedXML/ClosedXML/pull/1649)
- Remove multiple enumerations by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2236](https://redirect.github.com/ClosedXML/ClosedXML/pull/2236)
- Optimise workbook loading by stopping unneeded invalidation by [@sbeca](https://redirect.github.com/sbeca) in [https://github.com/ClosedXML/ClosedXML/pull/2284](https://redirect.github.com/ClosedXML/ClosedXML/pull/2284)
- Remove IXLStylized.Styles property by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2361](https://redirect.github.com/ClosedXML/ClosedXML/pull/2361)
- Convert XLNumberFormatKey and XLAlignmentKey to readonly structs by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2364](https://redirect.github.com/ClosedXML/ClosedXML/pull/2364)
- Convert XLBorderKey, XLFillKey, XLFontKey and XLProtectionKey to readonly structs by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2365](https://redirect.github.com/ClosedXML/ClosedXML/pull/2365)
- Convert XLStyleKey to readonly struct by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2366](https://redirect.github.com/ClosedXML/ClosedXML/pull/2366)
- Eliminate a couple of performance killers - cherry pick for develop by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2371](https://redirect.github.com/ClosedXML/ClosedXML/pull/2371)
#### Features
- Add FontScheme property to a font by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2114](https://redirect.github.com/ClosedXML/ClosedXML/pull/2114)
- Implement loading of workbook theme colors by [@sbeca](https://redirect.github.com/sbeca) in [https://github.com/ClosedXML/ClosedXML/pull/2117](https://redirect.github.com/ClosedXML/ClosedXML/pull/2117)
#### Bugfixes
- update accessibility of string.Contains(char) polyfill to internal by [@Applesauce314](https://redirect.github.com/Applesauce314) in [https://github.com/ClosedXML/ClosedXML/pull/2134](https://redirect.github.com/ClosedXML/ClosedXML/pull/2134)
- Make SheetId a unique across XLWorksheets by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2142](https://redirect.github.com/ClosedXML/ClosedXML/pull/2142)
- Fix incorrect logic check in Array Rescale by [@sbeca](https://redirect.github.com/sbeca) in [https://github.com/ClosedXML/ClosedXML/pull/2157](https://redirect.github.com/ClosedXML/ClosedXML/pull/2157)
- Fix ROUND (and probably others) not handling the result of binary operations on refs by [@sbeca](https://redirect.github.com/sbeca) in [https://github.com/ClosedXML/ClosedXML/pull/2153](https://redirect.github.com/ClosedXML/ClosedXML/pull/2153)
- Load and save data validations from/to worksheet extension by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2203](https://redirect.github.com/ClosedXML/ClosedXML/pull/2203)
- Handle invalid length hex colors by [@Bobbar](https://redirect.github.com/Bobbar) in [https://github.com/ClosedXML/ClosedXML/pull/2191](https://redirect.github.com/ClosedXML/ClosedXML/pull/2191)
#### Documentation
- Add themes documentation by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2154](https://redirect.github.com/ClosedXML/ClosedXML/pull/2154)
- Add documentation about how formulas are calculated. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2176](https://redirect.github.com/ClosedXML/ClosedXML/pull/2176)
- Update README.md - link update by [@sikanderiqbal](https://redirect.github.com/sikanderiqbal) in [https://github.com/ClosedXML/ClosedXML/pull/2234](https://redirect.github.com/ClosedXML/ClosedXML/pull/2234)
- Document how are functions evaluated by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2274](https://redirect.github.com/ClosedXML/ClosedXML/pull/2274)
- Fix typo "makred" by [@JuliusJacobsohn](https://redirect.github.com/JuliusJacobsohn) in [https://github.com/ClosedXML/ClosedXML/pull/2368](https://redirect.github.com/ClosedXML/ClosedXML/pull/2368)
#### Breaking changes
- First page number can be negative -> change API type to int by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2237](https://redirect.github.com/ClosedXML/ClosedXML/pull/2237)
- Rename IXLNamedRange to IXLDefinedName by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2258](https://redirect.github.com/ClosedXML/ClosedXML/pull/2258)
#### AutoFilter
- AutoFilter rework - 1/? - Regular filter matches string. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2238](https://redirect.github.com/ClosedXML/ClosedXML/pull/2238)
- AutoFilter rework - 2/? - fix types for custom filters by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2239](https://redirect.github.com/ClosedXML/ClosedXML/pull/2239)
- AutoFilter rework - 3/? - Top and average filter refactor, remove setters of internal state by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2240](https://redirect.github.com/ClosedXML/ClosedXML/pull/2240)
- AutoFilter rework - 4/? - Top/Average filters work after loading by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2241](https://redirect.github.com/ClosedXML/ClosedXML/pull/2241)
- AutoFilter rework - 5/? - Unify Regular and DateTimeGrouping filters by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2242](https://redirect.github.com/ClosedXML/ClosedXML/pull/2242)
- AutoFilter rework - 6/7 - Add tests by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2243](https://redirect.github.com/ClosedXML/ClosedXML/pull/2243)
- AutoFilter rework - 7/7 - Add documentation by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2245](https://redirect.github.com/ClosedXML/ClosedXML/pull/2245)
#### Formulas
- Replace XLParser with ClosedParser. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2138](https://redirect.github.com/ClosedXML/ClosedXML/pull/2138)
- Update ClosedXML.Parser to 1.0 by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2250](https://redirect.github.com/ClosedXML/ClosedXML/pull/2250)
- Implement structured references by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2251](https://redirect.github.com/ClosedXML/ClosedXML/pull/2251)
- Replace regex-powered code for A1-R1C1 formula conversion with AST-based one by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2253](https://redirect.github.com/ClosedXML/ClosedXML/pull/2253)
- Change source of truth for defined names from union of ranges to a formula by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2263](https://redirect.github.com/ClosedXML/ClosedXML/pull/2263)
- When sheet is renamed, rename it also in defined name formula by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2264](https://redirect.github.com/ClosedXML/ClosedXML/pull/2264)
- Update sheet names in formulas when sheet is renamed by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2273](https://redirect.github.com/ClosedXML/ClosedXML/pull/2273)
- Get areas a formula depends on by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2152](https://redirect.github.com/ClosedXML/ClosedXML/pull/2152)
- An initial work on a dependency tree for formulas by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2155](https://redirect.github.com/ClosedXML/ClosedXML/pull/2155)
- Add names areas to dependency tree by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2156](https://redirect.github.com/ClosedXML/ClosedXML/pull/2156)
- Add API to remove cell formula from dependency tree by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2160](https://redirect.github.com/ClosedXML/ClosedXML/pull/2160)
- Add XLCalculationChain by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2167](https://redirect.github.com/ClosedXML/ClosedXML/pull/2167)
- Add ability to detect cycle in calculation chain by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2169](https://redirect.github.com/ClosedXML/ClosedXML/pull/2169)
- Evaluate calculation chain by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2172](https://redirect.github.com/ClosedXML/ClosedXML/pull/2172)
#### Functions
- Implement FV and IPMT Excel functions and adapt 2 existing functions by [@sbeca](https://redirect.github.com/sbeca) in [https://github.com/ClosedXML/ClosedXML/pull/2199](https://redirect.github.com/ClosedXML/ClosedXML/pull/2199)
- Reimplement COUNTA function. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2277](https://redirect.github.com/ClosedXML/ClosedXML/pull/2277)
- Reimplement FACT function by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2280](https://redirect.github.com/ClosedXML/ClosedXML/pull/2280)
- Reimplement COMBIN function by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2281](https://redirect.github.com/ClosedXML/ClosedXML/pull/2281)
- Add BINOMDIST function by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2282](https://redirect.github.com/ClosedXML/ClosedXML/pull/2282)
- Implement Large Formula - Targets [#1716](https://redirect.github.com/ClosedXML/ClosedXML/issues/1716) by [@NickNack2020](https://redirect.github.com/NickNack2020) in [https://github.com/ClosedXML/ClosedXML/pull/2050](https://redirect.github.com/ClosedXML/ClosedXML/pull/2050)
- Reimplement legacy MAX function. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2269](https://redirect.github.com/ClosedXML/ClosedXML/pull/2269)
#### Dependencies
- Remove outdated transitive package upgrades by [@Arjan321](https://redirect.github.com/Arjan321) in [https://github.com/ClosedXML/ClosedXML/pull/2267](https://redirect.github.com/ClosedXML/ClosedXML/pull/2267)
- Update OpenXML SDK to 3.0. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2248](https://redirect.github.com/ClosedXML/ClosedXML/pull/2248)
- Update language level and use polyfill for missing functions. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2194](https://redirect.github.com/ClosedXML/ClosedXML/pull/2194)
#### Fixes
- Preserve VML part with form controls across load/save. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2205](https://redirect.github.com/ClosedXML/ClosedXML/pull/2205)
- Ignore sheets with invalid id by [@mihailmacarie](https://redirect.github.com/mihailmacarie) in [https://github.com/ClosedXML/ClosedXML/pull/2008](https://redirect.github.com/ClosedXML/ClosedXML/pull/2008)
- Fixed NullReferenceException at loading workbook with empty si element by [@psynomorph](https://redirect.github.com/psynomorph) in [https://github.com/ClosedXML/ClosedXML/pull/2218](https://redirect.github.com/ClosedXML/ClosedXML/pull/2218)
- Fix InvalidCastException at workbook loading by [@lvxiao312](https://redirect.github.com/lvxiao312) in [https://github.com/ClosedXML/ClosedXML/pull/2231](https://redirect.github.com/ClosedXML/ClosedXML/pull/2231)
- Get formula only if it is neither null or empty by [@PascalGeschwillBIS](https://redirect.github.com/PascalGeschwillBIS) in [https://github.com/ClosedXML/ClosedXML/pull/2216](https://redirect.github.com/ClosedXML/ClosedXML/pull/2216)
- Fix column/row style combination for non-materialized cells. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2249](https://redirect.github.com/ClosedXML/ClosedXML/pull/2249)
- Fix ROW function so it works in array formulas. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2268](https://redirect.github.com/ClosedXML/ClosedXML/pull/2268)
- Make legacy functions work with error argument by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2270](https://redirect.github.com/ClosedXML/ClosedXML/pull/2270)
- Write cells with empty text to file (used to be treated as blanks) by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2278](https://redirect.github.com/ClosedXML/ClosedXML/pull/2278)
- Add a load test for dialog sheet by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2334](https://redirect.github.com/ClosedXML/ClosedXML/pull/2334)
- Fix TimeSpan conversion by [@OldBuddy](https://redirect.github.com/OldBuddy) in [https://github.com/ClosedXML/ClosedXML/pull/2318](https://redirect.github.com/ClosedXML/ClosedXML/pull/2318)
- Fix an edge case when ranges got unmerged when the adjacent range of the same size got deleted by [@Pankraty](https://redirect.github.com/Pankraty) in [https://github.com/ClosedXML/ClosedXML/pull/2358](https://redirect.github.com/ClosedXML/ClosedXML/pull/2358)
- Correctly load quoted and unquoted sheet names in print area definitions by [@igitur](https://redirect.github.com/igitur) in [https://github.com/ClosedXML/ClosedXML/pull/2380](https://redirect.github.com/ClosedXML/ClosedXML/pull/2380)
- Change format of symbol package to snupkg by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2204](https://redirect.github.com/ClosedXML/ClosedXML/pull/2204)
- Convert Blank to DateTime as 0 serial date in legacy functions by [@georgheimltwat](https://redirect.github.com/georgheimltwat) in [https://github.com/ClosedXML/ClosedXML/pull/2180](https://redirect.github.com/ClosedXML/ClosedXML/pull/2180)
- Correctly interpret date and time values by [@TheObliterator](https://redirect.github.com/TheObliterator) in [https://github.com/ClosedXML/ClosedXML/pull/2184](https://redirect.github.com/ClosedXML/ClosedXML/pull/2184)
- Fix NRE in IXLCell.Active by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2195](https://redirect.github.com/ClosedXML/ClosedXML/pull/2195)
#### Pivot tables
- Implement a data structure to hold pivot table close to file structure by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2275](https://redirect.github.com/ClosedXML/ClosedXML/pull/2275)
- Load all PivotTableDefinition fields into the XLPivotTable by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2285](https://redirect.github.com/ClosedXML/ClosedXML/pull/2285)
- Start new pivot table part writer by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2287](https://redirect.github.com/ClosedXML/ClosedXML/pull/2287)
- Write pivot table row/column axes, filter fields and data fields by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2290](https://redirect.github.com/ClosedXML/ClosedXML/pull/2290)
- Add conditional formatting structure to pivot table by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2297](https://redirect.github.com/ClosedXML/ClosedXML/pull/2297)
- Reimplement pivot table logic by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2307](https://redirect.github.com/ClosedXML/ClosedXML/pull/2307)
- Pivot table grand total styles by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2374](https://redirect.github.com/ClosedXML/ClosedXML/pull/2374)
- Modify pivot field labels style by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2375](https://redirect.github.com/ClosedXML/ClosedXML/pull/2375)
- Fix the pivot table Layout property by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2384](https://redirect.github.com/ClosedXML/ClosedXML/pull/2384)
- Add style API for pivot field headers by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2385](https://redirect.github.com/ClosedXML/ClosedXML/pull/2385)
- Pivot style subtotals by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2386](https://redirect.github.com/ClosedXML/ClosedXML/pull/2386)
- Pivot style data by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2393](https://redirect.github.com/ClosedXML/ClosedXML/pull/2393)
- Improve error message when adding a pivot value field by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2394](https://redirect.github.com/ClosedXML/ClosedXML/pull/2394)
- Style intersection of pivot value field and axis field by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2395](https://redirect.github.com/ClosedXML/ClosedXML/pull/2395)
- Style pivot area based on pivot field axis values by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2396](https://redirect.github.com/ClosedXML/ClosedXML/pull/2396)
- Add support for Excel files containing 'Adobe' branded jpeg images by [@ctmatt](https://redirect.github.com/ctmatt) in [https://github.com/ClosedXML/ClosedXML/pull/2391](https://redirect.github.com/ClosedXML/ClosedXML/pull/2391)
- Fix O(n^2) issue in pivot cache creation by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2403](https://redirect.github.com/ClosedXML/ClosedXML/pull/2403)
- Load pivot records by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2178](https://redirect.github.com/ClosedXML/ClosedXML/pull/2178)
- Represent pivot field statistics by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2182](https://redirect.github.com/ClosedXML/ClosedXML/pull/2182)
- Write loaded pivot records to the output file by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2183](https://redirect.github.com/ClosedXML/ClosedXML/pull/2183)
- Detach source reference of pivot cache from a range by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2186](https://redirect.github.com/ClosedXML/ClosedXML/pull/2186)
### New Contributors
- [@mihailmacarie](https://redirect.github.com/mihailmacarie) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2008](https://redirect.github.com/ClosedXML/ClosedXML/pull/2008)
- [@psynomorph](https://redirect.github.com/psynomorph) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2218](https://redirect.github.com/ClosedXML/ClosedXML/pull/2218)
- [@lvxiao312](https://redirect.github.com/lvxiao312) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2231](https://redirect.github.com/ClosedXML/ClosedXML/pull/2231)
- [@PascalGeschwillBIS](https://redirect.github.com/PascalGeschwillBIS) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2216](https://redirect.github.com/ClosedXML/ClosedXML/pull/2216)
- [@sikanderiqbal](https://redirect.github.com/sikanderiqbal) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2234](https://redirect.github.com/ClosedXML/ClosedXML/pull/2234)
- [@Arjan321](https://redirect.github.com/Arjan321) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2267](https://redirect.github.com/ClosedXML/ClosedXML/pull/2267)
- [@OldBuddy](https://redirect.github.com/OldBuddy) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2318](https://redirect.github.com/ClosedXML/ClosedXML/pull/2318)
- [@JuliusJacobsohn](https://redirect.github.com/JuliusJacobsohn) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2368](https://redirect.github.com/ClosedXML/ClosedXML/pull/2368)
- [@ctmatt](https://redirect.github.com/ctmatt) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2391](https://redirect.github.com/ClosedXML/ClosedXML/pull/2391)
- [@Applesauce314](https://redirect.github.com/Applesauce314) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2134](https://redirect.github.com/ClosedXML/ClosedXML/pull/2134)
- [@sbeca](https://redirect.github.com/sbeca) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2117](https://redirect.github.com/ClosedXML/ClosedXML/pull/2117)
**Full Changelog**: https://github.com/ClosedXML/ClosedXML/compare/0.104.0-preview2...0.104.0-rc1
### [`v0.104.0`](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.102.3...0.104.0)
[Compare Source](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.102.3...0.104.0)
### [`v0.102.3`](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.102.3)
[Compare Source](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.102.2...0.102.3)
#### What's Changed
- Eliminate a couple of performance killers by [@Pankraty](https://redirect.github.com/Pankraty) in [https://github.com/ClosedXML/ClosedXML/pull/2363](https://redirect.github.com/ClosedXML/ClosedXML/pull/2363)
**Full Changelog**: https://github.com/ClosedXML/ClosedXML/compare/0.102.2...0.102.3
### [`v0.102.2`](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.102.2)
[Compare Source](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.102.1...0.102.2)
Add a warning about allowed ranges of DocumentFormat.OpenXML see issue [#2220](https://redirect.github.com/ClosedXML/ClosedXML/issues/2220) and PR [#2246](https://redirect.github.com/ClosedXML/ClosedXML/issues/2246).
#### What's Changed
- Add dependency version range for DocumentFormat.OpenXml by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2246](https://redirect.github.com/ClosedXML/ClosedXML/pull/2246)
**Full Changelog**: https://github.com/ClosedXML/ClosedXML/compare/0.102.1...0.102.2
### [`v0.102.1`](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.102.1): - SixLabors.Fonts dependency update
[Compare Source](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.102.0...0.102.1)
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](https://learn.microsoft.com/en-us/nuget/consume-packages/package-references-in-project-files), 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 `MissingMethodException`s.
The issue should only affect net framework projects, not dotnet core that use `PackageReference` style by default.
##### What's Changed
- Update SixLabors.Fonts dependency to version 1.0.0 by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2149](https://redirect.github.com/ClosedXML/ClosedXML/pull/2149)
**Full Changelog**: https://github.com/ClosedXML/ClosedXML/compare/0.102.0...0.102.1
### [`v0.102.0`](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.102.0)
[Compare Source](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.101.0...0.102.0)
##### Breaking changes
Please read migration guide from [0.101 to 0.102](https://closedxml.readthedocs.io/en/latest/migrations/migrate-to-0.102.html). The key ones changes are:
- `IXLCell` is 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 to `false` (used to be `true`)
- `IXLWorksheet AddWorksheet(DataTable dataTable)` and `IXLWorksheet AddWorksheet(DataTable dataTable, string sheetName)` now use different name for the created table.
##### Significant changes
##### Replaced cell storage engine ([#1969](https://redirect.github.com/ClosedXML/ClosedXML/issues/1969))
The cells in a workbook used to be stored in a `Dictionary>`. 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](https://gist.github.com/jahav/505d679ebc5b06a1adb1901498ae1b46)). About 200MB vs 900MB.
##### Embedded fonts ([#2106](https://redirect.github.com/ClosedXML/ClosedXML/issues/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](https://redirect.github.com/ClosedXML/ClosedXML/issues/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](https://redirect.github.com/ClosedXML/ClosedXML/issues/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](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2093](https://redirect.github.com/ClosedXML/ClosedXML/pull/2093)
- Having many hyperlinks caused slow save by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2076](https://redirect.github.com/ClosedXML/ClosedXML/pull/2076)
- Fix Stack Overflow exception and optimize for bigger datasets by [@JakubTracz](https://redirect.github.com/JakubTracz) in [https://github.com/ClosedXML/ClosedXML/pull/2042](https://redirect.github.com/ClosedXML/ClosedXML/pull/2042)
##### Features
- Add ability to read WebP images by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2071](https://redirect.github.com/ClosedXML/ClosedXML/pull/2071)
- Implement array formulas by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2082](https://redirect.github.com/ClosedXML/ClosedXML/pull/2082)
- Include an embedded font to ClosedXML by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2106](https://redirect.github.com/ClosedXML/ClosedXML/pull/2106)
- Consolidated and refactored pivot sources by [@igitur](https://redirect.github.com/igitur) in [https://github.com/ClosedXML/ClosedXML/pull/1238](https://redirect.github.com/ClosedXML/ClosedXML/pull/1238)
- Add ability to set table name when adding a worksheet with datatable … by [@NickNack2020](https://redirect.github.com/NickNack2020) in [https://github.com/ClosedXML/ClosedXML/pull/2044](https://redirect.github.com/ClosedXML/ClosedXML/pull/2044)
##### Bugfixes
- SUMIFS multiple dimensions by [@igitur](https://redirect.github.com/igitur) in [https://github.com/ClosedXML/ClosedXML/pull/2061](https://redirect.github.com/ClosedXML/ClosedXML/pull/2061)
- Handle different number types of NumberLiteral by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2058](https://redirect.github.com/ClosedXML/ClosedXML/pull/2058)
- Don't fail to load empty style part by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2102](https://redirect.github.com/ClosedXML/ClosedXML/pull/2102)
- Workbook with drawing kept original DOM by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2108](https://redirect.github.com/ClosedXML/ClosedXML/pull/2108)
##### Documentation
- Update IXLCell.cs Wrong summary on GetDouble() by [@MortenSpjotvoll](https://redirect.github.com/MortenSpjotvoll) in [https://github.com/ClosedXML/ClosedXML/pull/2059](https://redirect.github.com/ClosedXML/ClosedXML/pull/2059)
- Enable nullables in main project by [@lahma](https://redirect.github.com/lahma) in [https://github.com/ClosedXML/ClosedXML/pull/2056](https://redirect.github.com/ClosedXML/ClosedXML/pull/2056)
##### Quality of life
- Suppress false positives from vulnerability checkers by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2100](https://redirect.github.com/ClosedXML/ClosedXML/pull/2100)
##### New Contributors
- [@MortenSpjotvoll](https://redirect.github.com/MortenSpjotvoll) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2059](https://redirect.github.com/ClosedXML/ClosedXML/pull/2059)
- [@JakubTracz](https://redirect.github.com/JakubTracz) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2042](https://redirect.github.com/ClosedXML/ClosedXML/pull/2042)
**Full Changelog**: https://github.com/ClosedXML/ClosedXML/compare/0.101.0...0.102.0
### [`v0.101.0`](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.101.0)
[Compare Source](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.100.3...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](https://redirect.github.com/ClosedXML/ClosedXML/issues/1838))
Saving of cells in a worksheet has been significantly improved. As an example, a file of 100k rows and 44 columns (see [gist](https://gist.github.com/jahav/e9d8c5cf2830fb1754101cb0a66f7e36)) took 38 seconds to save in 0.100.3, but only 17 seconds in 0.101-rc .
##### Adjust to content ([#1991](https://redirect.github.com/ClosedXML/ClosedXML/issues/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](https://redirect.github.com/lahma) in [https://github.com/ClosedXML/ClosedXML/pull/1998](https://redirect.github.com/ClosedXML/ClosedXML/pull/1998)
- Change ExpressionCache to use ConditionalWeakTable by [@lahma](https://redirect.github.com/lahma) in [https://github.com/ClosedXML/ClosedXML/pull/1968](https://redirect.github.com/ClosedXML/ClosedXML/pull/1968)
- Separate XLCellFormula out of XLCell by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/1972](https://redirect.github.com/ClosedXML/ClosedXML/pull/1972)
- Don't allocate XLCellFormula for each XLCell by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2043](https://redirect.github.com/ClosedXML/ClosedXML/pull/2043)
- Speedup adjust to content by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2037](https://redirect.github.com/ClosedXML/ClosedXML/pull/2037)
- Save SheetData through XmlWriter by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/1984](https://redirect.github.com/ClosedXML/ClosedXML/pull/1984)
- Reimplement SEARCH function and add wildcard struct by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2007](https://redirect.github.com/ClosedXML/ClosedXML/pull/2007)
##### Features
- Calculate default column width from default font. by [@nakamura2000](https://redirect.github.com/nakamura2000) in [https://github.com/ClosedXML/ClosedXML/pull/1954](https://redirect.github.com/ClosedXML/ClosedXML/pull/1954)
- Add ability to load and save data table formulae by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/1974](https://redirect.github.com/ClosedXML/ClosedXML/pull/1974)
- ROWS and COLUMNS functions by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/1981](https://redirect.github.com/ClosedXML/ClosedXML/pull/1981)
##### Cleanup updates
- Remove unnecessary reference to Microsoft.CSharp by [@lahma](https://redirect.github.com/lahma) in [https://github.com/ClosedXML/ClosedXML/pull/1999](https://redirect.github.com/ClosedXML/ClosedXML/pull/1999)
- Simplify depdendency graph for newer target frameworks by [@georg-jung](https://redirect.github.com/georg-jung) in [https://github.com/ClosedXML/ClosedXML/pull/1973](https://redirect.github.com/ClosedXML/ClosedXML/pull/1973)
- Add a new XLCellValue.FromObject() method by [@0xced](https://redirect.github.com/0xced) in [https://github.com/ClosedXML/ClosedXML/pull/1982](https://redirect.github.com/ClosedXML/ClosedXML/pull/1982)
- Create SECURITY.md by [@punkd23](https://redirect.github.com/punkd23) in [https://github.com/ClosedXML/ClosedXML/pull/1756](https://redirect.github.com/ClosedXML/ClosedXML/pull/1756)
- Fix warnings by [@leotsarev](https://redirect.github.com/leotsarev) in [https://github.com/ClosedXML/ClosedXML/pull/1695](https://redirect.github.com/ClosedXML/ClosedXML/pull/1695)
- Update fonts library to beta19 by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/1971](https://redirect.github.com/ClosedXML/ClosedXML/pull/1971)
##### Bugfixes
- fix: mixed types in table throws exception on save by [@kappni](https://redirect.github.com/kappni) in [https://github.com/ClosedXML/ClosedXML/pull/2026](https://redirect.github.com/ClosedXML/ClosedXML/pull/2026)
- Use space as culture group separator in AutoFilterTests by [@lahma](https://redirect.github.com/lahma) in [https://github.com/ClosedXML/ClosedXML/pull/2000](https://redirect.github.com/ClosedXML/ClosedXML/pull/2000)
- Fix NRE during normal style loading by [@lahma](https://redirect.github.com/lahma) in [https://github.com/ClosedXML/ClosedXML/pull/2051](https://redirect.github.com/ClosedXML/ClosedXML/pull/2051)
- Fix reserved fill value checking by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/1977](https://redirect.github.com/ClosedXML/ClosedXML/pull/1977)
- Output unicode codepoints from astral planes by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/1978](https://redirect.github.com/ClosedXML/ClosedXML/pull/1978)
- Remove exception from FIND function. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2018](https://redirect.github.com/ClosedXML/ClosedXML/pull/2018)
- Fix negative text orientation. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2040](https://redirect.github.com/ClosedXML/ClosedXML/pull/2040)
- Formula hyperlinks shouldn't have nodes in worksheet by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2055](https://redirect.github.com/ClosedXML/ClosedXML/pull/2055)
##### Documentation
- Add documentation about tables feature by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/2029](https://redirect.github.com/ClosedXML/ClosedXML/pull/2029)
- Update Contributing - How to reconcile files by [@NickNack2020](https://redirect.github.com/NickNack2020) in [https://github.com/ClosedXML/ClosedXML/pull/2045](https://redirect.github.com/ClosedXML/ClosedXML/pull/2045)
##### New Contributors
- [@lahma](https://redirect.github.com/lahma) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/1968](https://redirect.github.com/ClosedXML/ClosedXML/pull/1968)
- [@georg-jung](https://redirect.github.com/georg-jung) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/1973](https://redirect.github.com/ClosedXML/ClosedXML/pull/1973)
- [@punkd23](https://redirect.github.com/punkd23) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/1756](https://redirect.github.com/ClosedXML/ClosedXML/pull/1756)
- [@kappni](https://redirect.github.com/kappni) made their first contribution in [https://github.com/ClosedXML/ClosedXML/pull/2026](https://redirect.github.com/ClosedXML/ClosedXML/pull/2026)
**Full Changelog**: https://github.com/ClosedXML/ClosedXML/compare/0.100.3...0.101.0
### [`v0.100.3`](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.100.3): Bulk insert of numbers
[Compare Source](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.100.2...0.100.3)
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
- Insert all number types as numbers by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/1967](https://redirect.github.com/ClosedXML/ClosedXML/pull/1967)
**Full Changelog**: https://github.com/ClosedXML/ClosedXML/compare/0.100.2...0.100.3
### [`v0.100.2`](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.100.2): Nullable conversion
[Compare Source](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.100.1...0.100.2)
##### Nullable conversions
Second fix for https://github.com/ClosedXML/ClosedXML/releases/tag/0.100.0.
Added conversion for nullable numbers/DateTime/TimeSpan to `XLCellValue`. Null values will be converted to `Blank.Value` in the `XLCellValue`. There is no value in having user code littered by `cell.Value = nullableNumber ?? Blank.Value`.
Null strings assigned to XLCellValue will also be converted to `Blank.Value` (unlike exception from 0.100).
##### What's Changed
- Add conversion for nullable numbers/datetime/timespan by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/1963](https://redirect.github.com/ClosedXML/ClosedXML/pull/1963)
**Full Changelog**: https://github.com/ClosedXML/ClosedXML/compare/0.100.1...0.100.2
### [`v0.100.1`](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.100.1): Implicit conversion
[Compare Source](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.100.0...0.100.1)
This is a quick improvement for https://github.com/ClosedXML/ClosedXML/releases/tag/0.100.0.
It adds an implicit conversion for decimal numbers to `XLCellValue`, so users who generate workbooks with decimal number don't have to add explicit casting from decimal to double everywhere.
##### What's Changed
- Add implicit conversion from decimal to double for XLCellValue. by [@jahav](https://redirect.github.com/jahav) in [https://github.com/ClosedXML/ClosedXML/pull/1961](https://redirect.github.com/ClosedXML/ClosedXML/pull/1961)
**Full Changelog**: https://github.com/ClosedXML/ClosedXML/compare/0.100.0...0.100.1
### [`v0.100.0`](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.100.0): 0.100: Clean Break
[Compare Source](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.97.0...0.100.0)
##### Clean Break
These are release notes for a version 0.100. We skipped a few version since the last release (0.97), because 0.100 should denote a major change at the very heart of ClosedXML. Not as clean break as I hoped, but close enough.
The list of all things that were changed from 0.97 to 0.100 is at the **migration guide** at the https://closedxml.readthedocs.io/en/latest/migrations/migrate-to-0.100.html
This is more like list of you should upgrade despite breaking changes :)
##### Memory consumption during big was decreased
Memory consumption during saving of large data workbooks was significantly improved. Originally, ClosedXML workbook representation was converted to DocumentFomrat.OpenXML DOM representation and the DOM was then saved. Instead of creating whole DOM, sheet data (=cell values) are now directly streamed to the output file and aren't included in the DOM.
To demonstrate difference, see the before and after memory consumption of a report that generated 30 000 rows, 45 columns. Memory consumption has decreased from **2.08** GiB 🡆 **0.8** GiB.
Save cells and strings through DOM: **2.08 GiB**
Save cell and strings through streaming: **0.8 GiB**
The purple area are bytes of uncompressed package zip stream.
##### Cell value is now strongly typed
`IXLCell.Value` and `IXLCellValue.CachedValue` have now type `XLCellValue`. At the core, xlsx consists of addressable cells with a functions that transform a set of values in source cells to different values in target cells. Is is really important to represent potential values of cells by a sane type. All other things, pivot tables, auto filter, graphs rely on this premise.
Cell value has been represented as string text and a value. The string depended on the value, e.g. *0*/*1* for boolean. That has been the case since the beginning of the ClosedXML project (see the [original XLCell](https://redirect.github.com/ClosedXML/ClosedXML/blob/29e7f01e74c20dd19f0366ad4a190615e12772f2/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs)). The value was also returned as an `Object`.
Configuration
📅 Schedule: Branch creation - At any time (no schedule defined), Automerge - At any time (no schedule defined).
🚦 Automerge: Disabled by config. Please merge this manually once you are satisfied.
♻ Rebasing: Whenever PR becomes conflicted, or you tick the rebase/retry checkbox.
🔕 Ignore: Close this PR and you won't be reminded about this update again.
[ ] If you want to rebase/retry this PR, check this box
This PR contains the following updates:
0.97.0->0.104.1Release Notes
ClosedXML/ClosedXML (ClosedXML)
### [`v0.104.1`](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.104.1) [Compare Source](https://redirect.github.com/ClosedXML/ClosedXML/compare/0.104.0...0.104.1) Release notes from [0.102.1](https://redirect.github.com/ClosedXML/ClosedXML/releases/tag/0.102.1) to the 0.104.1. Summary of breaking changes is available at [docs.closedxml.io](https://docs.closedxml.io/): - [Migration from 0.102 to 0.103](https://docs.closedxml.io/en/latest/migrations/migrate-to-0.103.html) - [Migration from 0.103 to 0.104](https://docs.closedxml.io/en/latest/migrations/migrate-to-0.104.html) - New calculation engine just works in a different way and will behave slightly differently. ### OpenXML SDK OpenXML SDK has released version 3. The 0.104.0 uses it as a dependency. ### XLParser replaced with ClosedParser The [*XLParser*](https://redirect.github.com/spreadsheetlab/XLParser) has been replaced with [*ClosedParser*](https://redirect.github.com/ClosedXML/ClosedXML.Parser). 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 **Configuration
📅 Schedule: Branch creation - At any time (no schedule defined), Automerge - At any time (no schedule defined).
🚦 Automerge: Disabled by config. Please merge this manually once you are satisfied.
♻ Rebasing: Whenever PR becomes conflicted, or you tick the rebase/retry checkbox.
🔕 Ignore: Close this PR and you won't be reminded about this update again.
This PR was generated by Mend Renovate. View the repository job log.