Improved
0.105.0-rc
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
- 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
- Add selection criteria logic for functions by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2474
- Reimplement SUMIF by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2495
- Reimplement SUMIFS function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2499
- Reimplement COUNTIF by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2502
- Reimplement COUNTIFS by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2506
- Reimplement COUNTBLANK by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2507
- Blanks should not be ignored in not-equls criteria funcs (SUMIF/S, COUTNIF/S) by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2510
- Refactor simple number functions from legacy by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2511
- Reimplement simple legacy funcs by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2513
- Reimplement ROMAN function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2515
- Reimplement INDEX function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2518
- Reimplement MATCH function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2522
- Reimplement 10 simple MathTrig funcs by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2523
- Reimplement SERIESSUM function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2525
- Reimplement 10 simple mathtrig funcs by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2524
- Reimplement MULTINOMIAL function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2526
- Refactor DECIMAL function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2527
- Reimplement LCM by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2528
- Refactor GCD by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2529
- Convert FLOOR.MATH from legacy by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2530
- Refactor BASE function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2531
- Reimplement ARABIC function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2533
- Convert MDETERM from legacy by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2534
- Convert MINVERSE from legacy by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2535
- Convert MMULT from legacy by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2536
- Reimplement ASC by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2539
- Reimplement CHAR function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2540
- Refactor CLEAN function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2541
- Reimplement CODE function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2543
- Refactor CONCAT function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2544
- Reimplement CONCATENATE function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2545
- Reimplement DOLLAR by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2546
- Reimplement FIXED function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2547
- Reimplement LEFT by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2548
- Refactor LEN from legacy by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2549
- Reimplement LOWER function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2550
- Refactor MID function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2552
- Refactor RIGHT function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2553
- Refactor REPT function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2554
- Reimplement PROPER function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2556
- Refactor TRIM by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2557
- Reimplement T function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2558
- Refactor TEXTJOIN function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2559
- Reimplement REPLACE function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2561
- Reimplement SUBSTITUTE by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2562
- Refactor DATE function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2563
- Reimplement YEAR, MONTH, DAY functions by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2564
- Refactor HOUR, MINUTE, SECOND function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2566
- Reimplement TIME function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2567
- Reimplement TEXT, DAYS360 by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2569
- Refactor YEARFRAC by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2570
- Reimplement WEEKNUM by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2571
- Reimplement DATEDIF by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2573
- Reimplement DAYS by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2574
- Refactor EDATE by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2575
- Reimplement EOMONTH function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2577
- Reimplement DATEVALUE function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2578
- Reimplement TIMEVALUE function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2579
- Reimplement NUMBERVALUE function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2580
- Reimplement ISOWEEKNUM function by @jahav in https://github.com/ClosedXML/ClosedXML/pull/2582
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-rc