New
0.105.0
What's Changed
Major enhancements
Automatic fixer of function names
Correct name for newer functions (post 2013) is not what is seen in the GUI (e.g. correct name for CONCAT is _xlfn.CONCAT). That is rather obscure fact not known to most developers. The formula setters (e.g. IXLCell.FormulaA1) now automatically fix function names, so it is stored correctly in the file.
using var wb = new XLWorkbook();
var ws = wb.AddWorksheet();
// Originally required "_xlfn.CONCAT(\"hello\", \" world!\")";
ws.Cell("A1").FormulaA1 = "CONCAT(\"hello\", \" world!\")";
Pre-0.105
0.105
Sorting updates references
In many cases, the sorted area has a column with references. The formula often references another row. Pre-0.105, the references in the formulas weren't updated correctly.
using var wb = new XLWorkbook();
var ws = wb.AddWorksheet();
ws.Cell("A1").Value = 4;
ws.Cell("B1").FormulaA1 = "A1+1";
ws.Cell("A2").Value = 2;
ws.Cell("B2").FormulaA1 = "A2+1";
ws.Cell("A3").Value = 1;
ws.Cell("B3").FormulaA1 = "A3+1";
ws.Range("A1:B3").Sort(1, XLSortOrder.Ascending);
Reimplementation/refactoring of old function infrastructure
Basically all implemented functions should be more faithful to how Excel behaves and evaluation of functions should be faster. implemented functions should be "complete" in sense that they correctly work for various arguments (e.g. various forms of ROMAN or pattern search in SUMIFS).
The functions (before refactoring) had serious problems with ranges, errors or type coercion or structured references. The original parser back then didn't even parse literal arrays ({1,2,3;4,5,6}). Parser and other things were updated, but because there was ~180 functions, original implementation was kept and functions were functions were reused through an adapter. Except the adapter never worked right and there were some other serious problems.
Changes
Bugfixes
- Improve VML inset parsing by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2595
- Fix evaluation of LOG10 function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2597
- Structural change caused ParsingException on defined names (Unexpected token EofSymbolId) by @Igor-Zlatomrezhev in https://github.com/ClosedXML/ClosedXML/pull/2462
- Don't round TimeSpan to milliseconds by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2444
- Update rich text when text of a rich run changes by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2516
- Mark IF function as a range function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2509
- Fix whitespace preservation in a rich text by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2512
- Total row label doesn't have correct value in a sheet by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2638
- Hyperlinks move on structural changes by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2412 +related changes:
- Move hyperlink from MiscSlice to XLHyperlinks collection by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2409
- Remove unused hyperlink prop from misc slice by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2408
- Move IXLRangeBase.Hyperlinks to IXLWorksheet.Hyperlinks by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2407
- Fix hyperlink copy between sheets by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2605
Enhancements
- Add load/save of external pivot cache source by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2585
- Add consolidate,scenario pivot sources for load/save by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2586
- Sorting of ranges adjusts formula references by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2413
- Add a missing prefix to future functions by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2598
Dependencies
- Update ClosedParser to 2.0 (bang reference) by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2596
- Upgrade DocumentFormat.OpenXml in Examples project by @rominator1983 in https://github.com/ClosedXML/ClosedXML/pull/2414
- add nuget.config to not rely on settings of development machine by @rominator1983 in https://github.com/ClosedXML/ClosedXML/pull/2419
- Update System.IO.Packaging by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2447
- Update core runtime version to net8.0 by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2466
- Update RBush and OpenXML SDK dependencies (security) by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2505
Technical debt
- Add methods to XLSheetRange to determine an area after area deletion by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2410
- Add methods to XLSheetRange to determine an area after area insertion by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2411
- Use strong name for dlls by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2599
Documentation
- Update NuGet Badge for Improved Visual Consistency by @hitensam in https://github.com/ClosedXML/ClosedXML/pull/2468
Performance
- Fix O(n^2) issue in pivot cache creation by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2403
Remove legacy function infrastructure
- Remove legacy formula (Expression) infrastructure by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2583
- Reimplement SUMPRODUCT function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2433
- Remove allocations in SUMPRODUCT by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2434
- Reimplement AVERAGE function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2435
- Reimplement COUNT function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2436
- Reimplement MIN by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2437
- Reimplement STDEV + STDEV.S by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2438
- Reimplement STDEVP + STDEV.P by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2439
- Reimplement VAR function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2440
- Reimplement VARP function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2441
- Reimplement PRODUCT function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2442
- Reimplement GEOMEAN function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2446
- Reimplement AVERAGEA function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2449
- Reimplement MAXA function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2450
- Reimplement MEDIAN function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2451
- Reimplement SUMSQ function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2452
- Reimplement DEVSQ function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2453
- Reimplement MINA function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2454
- Reimplement VARA function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2457
- Reimplement VARPA function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2458
- Reimplement STDEVA function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2459
- Reimplement STDEVPA function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2460
- Reimplement WEEKDAY function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2463
- Reimplement NETWORKDAYS function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2464
- Reimplement WORKDAY function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2465
- Split tally into value provider and aggregation by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2467
- Reimplement SUBTOTAL (1-11) by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2471
- Add SUBTOTAL 101-111 functions by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2473
New Contributors
- @hitensam made their first contribution in https://github.com/ClosedXML/ClosedXML/pull/2468
- @Igor-Zlatomrezhev made their first contribution in https://github.com/ClosedXML/ClosedXML/pull/2462
Full Changelog: https://github.com/ClosedXML/ClosedXML/compare/0.104.0-rc1...0.105.0