A release candidate 1 for 0.104.0.

• Migration from 0.102 to 0.103
• Migration from 0.103 to 0.104

Of course, this includes all stuff from 0.103.0-beta. Version 0.103 never got a non-beta release.

OpenXML SDK

OpenXML SDK has released version 3.0.1. The 0.104-rc1 uses it as a dependency.

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. Several bugs have also been fixed.

For more details, refer to the Autofilter section of the migration guide.

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

Breaking changes

• First page number can be negative -> change API type to int by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2237
• Rename IXLNamedRange to IXLDefinedName by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2258

AutoFilter

• AutoFilter rework - 1/? - Regular filter matches string. by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2238
• AutoFilter rework - 2/? - fix types for custom filters by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2239
• AutoFilter rework - 3/? - Top and average filter refactor, remove setters of internal state by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2240
• AutoFilter rework - 4/? - Top/Average filters work after loading by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2241
• AutoFilter rework - 5/? - Unify Regular and DateTimeGrouping filters by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2242
• AutoFilter rework - 6/7 - Add tests by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2243
• AutoFilter rework - 7/7 - Add documentation by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2245

Formulas

• Update ClosedXML.Parser to 1.0 by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2250
• Implement structured references by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2251
• Replace regex-powered code for A1-R1C1 formula conversion with AST-based one by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2253
• Change source of truth for defined names from union of ranges to a formula by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2263
• When sheet is renamed, rename it also in defined name formula by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2264
• Reimplement legacy MAX function. by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2269
• Implement Large Formula - Targets #1716 by @NickNack2020 in https://github.com/ClosedXML/ClosedXML/pull/2050
• Update sheet names in formulas when sheet is renamed by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2273
• Implement FV and IPMT Excel functions and adapt 2 existing functions by @sbeca in https://github.com/ClosedXML/ClosedXML/pull/2199
• Reimplement COUNTA function. by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2277
• Reimplement FACT function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2280
• Reimplement COMBIN function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2281
• Add BINOMDIST function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2282

Docs

• Update README.md - link update by @sikanderiqbal in https://github.com/ClosedXML/ClosedXML/pull/2234
• Document how are functions evaluated by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2274
• Fix typo "makred" by @JuliusJacobsohn in https://github.com/ClosedXML/ClosedXML/pull/2368

Performance

• Improve sorting performance by @igitur in https://github.com/ClosedXML/ClosedXML/pull/1649
• Remove multiple enumerations by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2236
• Optimise workbook loading by stopping unneeded invalidation by @sbeca in https://github.com/ClosedXML/ClosedXML/pull/2284
• Remove IXLStylized.Styles property by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2361
• Convert XLNumberFormatKey and XLAlignmentKey to readonly structs by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2364
• Convert XLBorderKey, XLFillKey, XLFontKey and XLProtectionKey to readonly structs by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2365
• Convert XLStyleKey to readonly struct by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2366
• Eliminate a couple of performance killers - cherry pick for develop by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2371

Dependencies

• Remove outdated transitive package upgrades by @Arjan321 in https://github.com/ClosedXML/ClosedXML/pull/2267
• Update OpenXML SDK to 3.0. by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2248

Fixes

• Preserve VML part with form controls across load/save. by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2205
• Ignore sheets with invalid id by @mihailmacarie in https://github.com/ClosedXML/ClosedXML/pull/2008
• Fixed NullReferenceException at loading workbook with empty si element by @psynomorph in https://github.com/ClosedXML/ClosedXML/pull/2218
• Fix InvalidCastException at workbook loading by @lvxiao312 in https://github.com/ClosedXML/ClosedXML/pull/2231
• Get formula only if it is neither null or empty by @PascalGeschwillBIS in https://github.com/ClosedXML/ClosedXML/pull/2216
• Fix column/row style combination for non-materialized cells. by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2249
• Fix ROW function so it works in array formulas. by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2268
• Make legacy functions work with error argument by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2270
• Write cells with empty text to file (used to be treated as blanks) by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2278
• Add a load test for dialog sheet by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2334
• Fix TimeSpan conversion by @OldBuddy in https://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 in https://github.com/ClosedXML/ClosedXML/pull/2358
• Correctly load quoted and unquoted sheet names in print area definitions by @igitur in https://github.com/ClosedXML/ClosedXML/pull/2380

Pivot tables

• Implement a data structure to hold pivot table close to file structure by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2275
• Load all PivotTableDefinition fields into the XLPivotTable by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2285
• Start new pivot table part writer by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2287
• Write pivot table row/column axes, filter fields and data fields by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2290
• Add conditional formatting structure to pivot table by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2297
• Reimplement pivot table logic by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2307
• Pivot table grand total styles by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2374
• Modify pivot field labels style by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2375
• Fix the pivot table Layout property by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2384
• Add style API for pivot field headers by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2385
• Pivot style subtotals by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2386
• Pivot style data by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2393
• Improve error message when adding a pivot value field by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2394
• Style intersection of pivot value field and axis field by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2395
• Style pivot area based on pivot field axis values by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2396
• Add support for Excel files containing 'Adobe' branded jpeg images by @ctmatt in https://github.com/ClosedXML/ClosedXML/pull/2391

New Contributors

• @mihailmacarie made their first contribution in https://github.com/ClosedXML/ClosedXML/pull/2008
• @psynomorph made their first contribution in https://github.com/ClosedXML/ClosedXML/pull/2218
• @lvxiao312 made their first contribution in https://github.com/ClosedXML/ClosedXML/pull/2231
• @PascalGeschwillBIS made their first contribution in https://github.com/ClosedXML/ClosedXML/pull/2216
• @sikanderiqbal made their first contribution in https://github.com/ClosedXML/ClosedXML/pull/2234
• @Arjan321 made their first contribution in https://github.com/ClosedXML/ClosedXML/pull/2267
• @OldBuddy made their first contribution in https://github.com/ClosedXML/ClosedXML/pull/2318
• @JuliusJacobsohn made their first contribution in https://github.com/ClosedXML/ClosedXML/pull/2368
• @ctmatt made their first contribution in https://github.com/ClosedXML/ClosedXML/pull/2391

Full Changelog: https://github.com/ClosedXML/ClosedXML/compare/0.104.0-preview2...0.104.0-rc1