ClosedXML Versions Save

ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.

0.100.2

1 year ago

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

Full Changelog: https://github.com/ClosedXML/ClosedXML/compare/0.100.1...0.100.2

0.100.1

1 year ago

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

Full Changelog: https://github.com/ClosedXML/ClosedXML/compare/0.100.0...0.100.1

0.100.0

1 year ago

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 1874-dtoMemory-save-30k-text-DOM

Save cell and strings through streaming: 0.8 GiB 1874-dtoMemory-save-30k-text-streaming

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). The value was also returned as an Object. This approach has several drawbacks

  • Object is not suitable representation of cell value. User had no idea what kind of values could be returned as a cell value. Everything could also break down, if a new type would be returned (e.g. XLError).
  • Setter could accept different types that the getter returned. E.g. it was possible to set cell value to a IXLColumn.
  • Values were always boxed/unboxed. That is not a problem for small amount of data, but it is not great for large workbooks.
  • It caused an potentially buggy behavior in other places of the ClosedXML.

Value of a cell is not represented by a XLCellValue structure. It is basically a union of one of possible types that can be value of a cell:

  • blank
  • boolean
  • number
  • text
  • error
  • datetime - basically number representing serial datetime, use serial datetime.
  • duration - basically number representing serial datetime, use serial datetime

Since datetime and duration are basically masqaraded number, you can use XLCellValue.GetUnifiedNumber() to get a backing number, no matter if the type is number, datetime and duration.

The structure contains implicit operators, as well as other methods to make transaction as seamless as possible

// Will use an implicit cast operator to convert string to XLCellValue and pass it to the Value setter
ws.Cell("A1").Value = "Text";

There is also a new singleton Blank.Value that represent a blank value of a cell. Null is not blank. Empty string is not a blank value of a cell. Null instead of blank was considered and everything is just so much easier to work with, if blank is represented as a custom singleton type and not as a null.

XLCellValue will be able to represent all values of a cell and won't be boxed/unboxed all the time.

Cell data type is no longer guessed

ClosedXML used to guess a data type from a value. It caused all sort of unexpected behaviors (e.g. text value Z12.31 has been converted to date time 12/30/2022 19:00). Date caused most problems, but other sometimes too (e.g. text "Infinity" was detected as a number).

This behavior was likely intended to emulate how user interacts with an Excel. Excel guesses type, but only if the cell Number Format is set to "General" (e.g. if NumberFormat is set to Text, there is no conversion even in Excel). Application is not human and doesn't have to interact with xlsx in the same way.

This behavior was removed. Type that is set is the type that will be returned. Note that although XLCellValue can represent date and time as a different types, in reality that is only presentation logic for user. They are both just serial date time numbers.

Cell value now can be XLError or Blank

Cell value now can accurately represent error or a blank value.

ClosedXML used to throw on error value and cell couldn't contain an error. That was a significant problem, especially for formula calculation where formula referenced a cell that should contain an error value.

ClosedXML used to represent blank cell as an empty string, but no longer. It uses Blank.Value singleton, wrapped in XLCellValue. Also brings significant improvement in accuracy for CalcEngine evaluation.

Text to number coercion

Excel has a pretty complicated undocumented coercion process from text to number. It can convert fraction text (="1 1/2"*2 is 3), dates (e.g. ="1900-01-05"*2 is 10, though date format is culture specific), percent (e.g. ="100%"*2), braces imply negative value (="(100%)"*2 = -2) and many more. That causes a significant problems for formula evaluation, especially if the source cell contains a date as a text, not as a date.

ClosedXml used to only convert test that looked like double, it now coerces nearly everything Excel does. Coercion from dates should mostly work, but Excel has it's own database of acceptable formats and it's own format, while we rely on .NET Core infrastructure.

CalcEngine doesn't throw exceptions

Thanks to incorporation of XLError to core of CalcEngine, the exceptions are no longer necessary and have been removed. Error is a normal value type that is used during formula evaluation (e.g. ISNA accepts it and VLOOKUP returns it).

Technically speaking CalcEngine can still throw MissingContextException, but only if evaluation is not called from a cell, but from method like XLWorkbook.Evaluate. Functions like ROW just can't work without the context of the cell.

Unimplemented functions now return #NAME?

If you ever tried to use CalcEngine, you have encountered a dreaded The function *SomeFunctionwas not recognised. exception.

ClosedXML will no longer throw an exception on unimplemented function, but will return #NAME? error instead. It has several reasons

  • It aligns behavior of user defined functions in like with predefined functions. ClosedXML doesn't throw anything on =SOME.UNKNOWN.FUN(4), why should it throw on =LARGE(A1:A5,1)?
  • By default, ClosedXML doesn't save calculated values. A portion of workbook that doesn't use unimplemented function should work correctly, maybe that is enough for some use case? Excel (nearly always) recalculates everything on load anyway.

Basically, the exception doesn't bring any benefit and only imposes costs. User can report missing function on #NAME? error just like on exception.

Array literal can now be parsed

CalcEngine now can evaluate array literal expressions, so formulas like VLOOKUP(4, {1,2; 3,2; 5,3; 7,4}, 2) now actually work.

Array processing is limited to argument parsing across formulas and CalcEngine still needs some love to process it work correctly. Array formulas are still not implemented.

Reimplementation of information and lookup functions

Information and lookup functions were reimplemented to take advantage of other improvements. They should now be compliant with Excel (with exception of wildcard search for VLOOKUP).

Documentation in the version control

Documentation is being moved from wiki to the ReadTheDocs. It has been there for since 2019, but we didn't actually had any documentation. Documentation is super important and ClosedXML lacks in that area. It is of course WIP, but it should improve over the time (see https://closedxml.readthedocs.io/en/latest/features/protect.html, https://closedxml.readthedocs.io/en/latest/features/cell-format.html#number-format or infamous https://closedxml.readthedocs.io/en/latest/tips/missing-font.html).

The move to ReadTheDocs has significant advantages:

  • It is in version control. That means every PR now can contain modification to documentation.
  • It is built as part of CI
  • It is versioned.
  • It uses ReStructured Text (rst) that has more rich style options and even plugins. Commonmark is heavily limited in style application.
  • It can generate documentation from xml comments
  • It can use references and includes. That means all examples can be in separate files and only included to documentation. Separate example files could be just complied and checked for correctness (we are not doing that ATM, but will likely do at some point in the future). That would solve the pesky issue of outdated examples in documentation.

Notes about breaking changes

We are not breaking the compatibility just because. Break imposes heavy penalty on users of the library. That makes it less likely to use it and that is definitely not the goal. Even the ClosedXML.Report must be fixed after every release.

That is not desirable situation. Version 1.0 and semantic versioning is certainly the goal. But it must be with an clear API that can endure some development between minor version. That is just not the case at the moment.

API will be reviewed along with the documentation and will be adjusted as necessary. ClosedXML will practice release early, release often. If breaking changes are not acceptable, stay on version that works and wait for 1.0 (though that will likely take at least a year, likely more... we are on a second decade).

Technically we do semver since forever, since Major version zero (0.y.z) is for initial development. Anything MAY change at any time. The public API SHOULD NOT be considered stable. ). Initial development for a decade /sigh.

Future plans

Similar to current release, the general plan is to work on neglected foundational things and bug fixes.

  • Fix AutoFilter - doesn't work correctly, API is a mess and accepts any type. I wanted to have it done for 0.100 ¯_(ツ)_/¯
  • Finish CalcEngine redesign with array formulas.
  • Update XLParser to 1.6.2, I added PRs 162 and163 to improve speed by about factor of 3x (test dataset was parsed in 13 seconds vs 47 originally). But not enough time to upgrade the version ¯_(ツ)_/¯
  • Housekeeping of PR - some PRs were merged, but most are still there.
  • Cell sizing is a mess. Clean it up and fix AdjustToContent to be in line with what Excel does (research was done: https://github.com/ClosedXML/ClosedXML/wiki/Cell-Dimensions).
  • Make a fuzzer for function evaluation that compares ClosedXML implementation with result from Excel

It is likely there will be 0.100.x to fix whatever bugs XLCellValue caused that weren't convered by tests.

Pivot tables won't get any love in 0.101, but hopefully in the next one. It is one of distinguishing features of ClosedXML and it has a lot of reported issues.

What's Changed

Full Changelog: https://github.com/ClosedXML/ClosedXML/compare/0.97.0...0.100.0

0.97.0

1 year ago

Project has been on hiatus for a while, but things are now moving forward. See full list of changes at https://github.com/ClosedXML/ClosedXML/milestone/17?closed=1

Breaking changes

  • Methods that were depending on the System.Common.Drawing were removed (use another overload):
    • IXLPictures.AddPicture(Bitmap bitmap)
    • IXLPictures.AddPicture(Bitmap bitmap, String name)
    • IXLWorksheet.AddPicture(Bitmap bitmap)
    • IXLWorksheet.AddPicture(Bitmap bitmap, string name)
  • Date/time formulas (NOW(), HOUR()...) returns serial date-time, cell is no longer implicitly XLDataType.DateTime or XLDataType.TimeSpan. The DataType has to be set explicitely.
  • CalcEngine can now return XLError on error, not a CalcEngineException exception.
  • Non-windows environments: ClosedXML must be configured an available fallback font for the graphic engine, otherwise an will throw an exception (the exception message contains info what to do). System.Drawing.Common had some kind of logic, now it has to be done manually.

Improvements

System.Drawing.Common removal (#1805)

We have removed a System.Drawing.Common dependency, it was deprecated and throws runtime exception when called on non-windows environments. All complexity has been hidden behind an interface IXLGraphicEngine and a default implementation DefaultGraphicEngine in the `ClosedXML.Graphics namespace. The default engine uses SixLabors.Fonts library for font measurements. You can read more on the Graphic Engine wiki page.

On non-windows environment, it will be necessary to specify a default font. Use this code

// All workbooks created later will use the engine with a fallback font DejaVu Sans
LoadOptions.DefaultGraphicsEngine = new DefaultGraphicEngine("DejaVu Sans"); // or Tahoma or any other font that is installed

Use XLParser to parse formulas

ClosedXML has used a handcrafted parser for a while. The parse could parse a simple formulas, but a lot of features were out of its grasp (e.g. arrays, references to other worksheets, operations on references and so much more). We have replaced the original the original parser with the XLParser to facilitate a more powerful formulas.

You can try the parsing yourself on an online demo page: https://xlparser.perfectxl.nl/demo/

Through slower than the original parser, we are working with upstream to improve performance (https://github.com/spreadsheetlab/XLParser/issues/163, https://github.com/spreadsheetlab/XLParser/issues/161). Not-so-close future of CalcEngine is also multi threaded.

CalcEngine redesign

CalcEngine has been half-rewritten. It can now correctly represent all Excel types (e.g. Error is now a value, not an an exception) and perform operations on them (e.g. reference unions, comparisons work as they should).

As an example, SUM of two areas that overlap should count overlapping cells twice, thus the result should be 12, not 9.

using var wb = new XLWorkbook();
var ws = wb.AddWorksheet();
ws.Range(1, 1, 3, 3).SetValue(1);
var sum = ws.Evaluate("SUM((A1:B3,B1:C3))");
Console.WriteLine($"Result of a SUM function: {sum}");

Result of a SUM function: 12

image

A major change has been implicit intersections in the semantic of 2019 excel (Excel 2021 and 365 already support dynamic array formulas):

using var wb = new XLWorkbook();
var ws = wb.AddWorksheet();
ws.Cell("A1").SetValue(0);
ws.Cell("A2").SetValue(Math.PI / 4);
ws.Cell("A3").SetValue(Math.PI / 2);

var c1 = ws.Cell("C1");
c1.FormulaA1 = "SIN(A1:A3)";
var c2 = ws.Cell("C2");
c2.FormulaA1 = "SIN(A1:A3)";
var c3 = ws.Cell("C3");
c3.FormulaA1 = "SIN(A1:A3)";

Console.WriteLine($"C1: {c1.Value} C2: {c2.Value} C3: {c3.Value}");

C1: 0 C2: 0.7071067811865472 C3: 1

XLWorksheet.Evaluate functions now have an optional parameters to specify a context where is formula evaluated. If formula requires a context and it is missing, it will throw a MissingContextException.

using var wb = new XLWorkbook();
var ws = wb.AddWorksheet();
var row = ws.Evaluate("ROW()", "A3"); // Needs A3 for the context, otherwise MissingContextException
Console.WriteLine($"Result of a ROW function: {row}");

Add ROW #1851 and COLUMN # 1818 functions

We have added a support for ROW and COLUMN functions. They even return arrays (e.g. formula ROW(A2:D4) return {2;3;4}) , though it is difficult to see due to half revamped of CalcEngine.

using var wb = new XLWorkbook();
var ws = wb.AddWorksheet();
var sum = ws.Evaluate("SUM(COLUMN(B1:D2))");
Console.WriteLine($"Result: {sum}");

Result: 9

Future plans

We hope to make a release every few months. For the next release, the general plan is to work on neglected things before new features.

  • Housekeeping - we have a lot of issues and PRs that were just left in limbo. Triage them.
  • Look at memory performance and ways to decrease it.
  • Continue work on CalcEngine. Add support for array and (hopefully) array functions/dynamic array functions,
  • Make a guide for functions PR + make a fuzzer that automatically compares a result of function in Excel and the result in the ClosedXml for various inputs (especially edge conditions).
  • Cell sizing is a mess. Clean it up (a lot of research was done during Common.Drawing replacement).

0.96.0

1 year ago

See full list of changes at: https://github.com/ClosedXML/ClosedXML/pulls?q=is%3Apr+milestone%3Av0.96+is%3Aclosed

Breaking changes

  • ClosedXML NuGet package is now signed. ClosedXML.Signed is deprecated. #1552
  • #1396
    • IXLCell.Hyperlink removed; use GetHyperlink() to get an existing hyperlink or to create a new one if it does not exist; instead of property setter, use SetHyperlink(); to replace the existing hyperlink with a fresh one, use CreateHyperlink();
    • IXLCell.DataValidation removed; use GetDataValidation();
    • IXLCell.NewDataValidation removed; use CreateDataValidation();
    • IXLCell.Comment removed; use GetComment() to access the existing comment or to create a new one if it does not exist; use CreateComment() to replace the existing comment with the fresh one;
    • IXLCell.RichText removed; use GetRichText() to access the existing rich text or to create a new one if it does not exist; use CreateRichText() to replace the existing rich text with the fresh one.

0.95.1

4 years ago

Bugs

#1399 .CellsUsed() should return only cells with content.

0.95.0

4 years ago

Breaking changes

  • Range strings, e.g. A1:B2 are now always treated as absolute, i.e. relative to A1 instead of relative to the top left cell of a given range.
  • IXLSheetProtection has been totally refactored and now uses a flag based enum to control the elements that are allowed or locked.
  • Removed IXLSheetProtection.IsProtected setter
  • XLClearOptions and XLCellUsedOptions 's flags are now aligned
  • IXLDataValidation.Ranges made readonly
  • To add or remove ranges from the data validation rule the user must use dedicated methods (AddRange, AddRanges, ClearRanges, RemoveRange)
  • A single data validation rule cannot apply to ranges from different worksheets (it was a bug that this was possible)

Pull requests merged since 0.95.0-beta2

Bug fixes

#1063 Copy comment style correctly when shifting ranges #1108 Correctly save empty cell with quote prefix (do not treat as empty) #1125 Change cached value type when data type changes #1132 elements with missing r attribute: XLAddress constructor had parameters swapped #1133 Enumerate ranges in a deterministic order #1134 Correctly propagate worksheet style to cells from a worksheet #1135 Correctly load column-wide styles #1137 Fix changing inner borders of the range #1150 Correctly clear inline string when required #1153 Correctly throw DivisionByZeroException when denominator is zero. #1155 Ignore _X (capital X) as XML escaping char #1161 Avoid creation of new item in Internals.RowsCollection when adding comment #1168 Fix initialization of default styles for XLCells, XLRows, XLColumns and XLRanges #1172 Clear all RelId's if workbook is loaded from template. #1178 Fixes in GetHashCode functions (#1073) #1196 Fix error by two pivot with one source #1195 #1223 Fix pivot subtotals loading #1234 Merged ranges cell values should not be set, except for top left cell of merged range #1245 Fix the issue with conditional formats created in non-US culture (#1187) #1247 Fix issues related to cells inserting (#1236) #1266 Fix CONCATENATE for cell references #1282 Treat missing numberFormatId as General numberformat #1289 Remove unused code and fix ATAN2 tests #1292 Redefine rules for sheet name escaping (#1287) #1307 Fix null exception when saving a file #1316 Fix exception when loading rows with no row index populated (#1314) #1323 #651: fix loading with deleted worksheets. #1351 Fix null DateTime in XLCell.InsertDataInternal with IEnumerable #1356 Fix a regular expression used for extracting A1 references #1359 Fixed the Expression double conversion operator to handle string values #1362 Set longText attribute for string items in pivot table #1369 Fix issues with loading comments (#1244) #1384 When saving, mark the first selected tab as active if no active tabs exist (#1383) #1386 Set the first sheet active and visible on loading if no active tab is specified in file (#1383)

Enhancments

#1015 Implement TEXTJOIN function (#1010) #1038 IXLSheetProtection refactoring #1054 Implement workbook FileSharing and a few of the properties #1058 Don't recalculate formula cached values after load #1077 Refactor digit and letter parsing to allow Unicode characters #1109 Don't automatically adjust columns width to contents when inserting datatable. Some users want to opt out because of PlatformNotSupportedException #1122 Add helper methods to add worksheet with default sheet name #1129 Alternative implementation of pivot table formats #1130 Enhance TRUNC function for additional parameter #1136 Implement NUMBERVALUE function #1151 Performance improvement to delete entire columns #1158 Check that print titles are valid row ranges or column ranges #1166 Ignore default Excel function namespace and implement CONCAT function #1169 Avoid creating new XLCell instances during range shifting #1170 Remove IXLSheetProtection.IsProtected public setter #1174 Correctly dispose temporary memorystream #1175 Sparklines implementation #1183 Improve Merge operation performance #1194 Disallow table name that is also a valid address (#1192) #1205 Improve performance of worksheet copying (#1188) #1208 optional range consolidation via save options #1219 Implement MATCH and INDEX functions #1224 Improve performance of loading workbooks having many merged ranges (#1220) #1227 Refactor comments #1232 Set versioning number to allow enhanced pivot table features #1237 Improve HLOOKUP and VLOOKUP performance #1240 Prevent copying of deleted worksheets. #1253 Replace licenseUrl (deprecated) with license tag #1278 Clear CellsCollection on WorksheetInternals disposal (#1276) #1284 Improvements for workbook saving (#1280) #1293 Remove the unnecessary check for VML elements not existing (#1285) #1326 PopulateAutoFilter range check (#1325) #1353 Implement % operator and correctly parse expressions with multiple unary operators (--, ++) #1357 Replace String.Compare with TryGetValue #1364 Fix issues occurred when running tests on .Net Core 3.0 #1368 Clear inlined rich text data on saving (#1361) #1376 CEILING and FLOOR fixes #1379 Explicitly set document type on saving (#1375) #1381 Exclude dependent SUBTOTAL formulas in SUBTOTAL evaluation #1388 Remove the dependency on FastMember. Improve performance of InsertData (#797) #1391 Replace ContainsKey with TryGetValue

0.95.0-beta1

5 years ago

Breaking changes

  • Range strings, e.g. A1:B2 are now always treated as absolute, i.e. relative to A1 instead of relative to the top left cell of a given range.
  • IXLSheetProtection has been totally refactored and now uses a flag based enum to control the elements that are allowed or locked.
  • Removed IXLSheetProtection.IsProtected setter
  • XLClearOptions and XLCellUsedOptions 's flags are now aligned

Enhancements

  • #1015 Text join func enhancement (Thanks @James-Whitfield)
  • #1038 IXLSheetProtection refactoring
  • #1054 Implement workbook FileSharing and a few of the properties
  • #1058 Dont recalculate formula cached values after load
  • #1077 Refactor digit and letter parsing to allow Unicode characters
  • #1109 Don't automatically adjust columns width to contents when inserting datatable. Some users want to opt out because of PlatformNotSupportedException
  • #1129 Implementation pivot table formats (Thanks @b0bi79)
  • #1130 Enhance TRUNC function for additional parameter
  • #1136 Implement NUMBERVALUE function
  • #1151 Performance improvement to delete entire columns
  • #1158 Check that print titles are valid row ranges or column ranges
  • #1170 Remove IXLSheetProtection.IsProtected public setter

Bug fixes

  • #1063 Copy comment style correctly when shifting ranges
  • #1108 Correctly save empty cell with quote prefix (do not treat as empty)
  • #1132 <c> elements with missing r attribute: XLAddress constructor had parameters swapped
  • #1133 Enumerate ranges in a deterministic order
  • #1134 Correctly propagate worksheet style to cells from a worksheet
  • #1135 Correctly load column-wide styles
  • #1137 Fix changing inner borders of the range
  • #1150 Correctly clear inline string when required
  • #1153 Correctly throw DivisionByZeroException when denominator is zero.
  • #1155 Ignore _X (capital X) as XML escaping char
  • #1161 Avoid creation of new item in Internals.RowsCollection when adding comment
  • #1168 Fix initialization of default styles for XLCells, XLRows, XLColumns and XLRanges

Thanks to @Pankraty for many PRs, as usual.

Binaries are available at: ClosedXML: https://ci.appveyor.com/project/ClosedXML/closedxml/builds/23591647/job/dyitdjnq6ta3nnpb/artifacts ClosedXML.Signed: https://ci.appveyor.com/project/ClosedXML/closedxml/builds/23591647/job/hk6hbr6kt807lvxf/artifacts

  • @igitur

0.94.2

5 years ago

Bug fix release

  • #1091 Wrong worksheet for copied conditional formats fix
  • #1102 Add special case for inserting DBNull values

Also see the release notes for v0.94.0

0.94.0

5 years ago

Breaking changes

  • This unsigned ClosedXML package now depends on FastMember instead of FastMember.Signed. The signed ClosedXML.Signed still depends on FastMember.Signed.
  • IDisposable interface removed from IXLWorksheet. See #1049
  • IXLPictures interface has changed to accept parameters of type IXLCell instead of IXLAddress. See #930
  • IXLBaseAutoFilter removed and replaced with the existing IXLAutoFilter. See #909
  • IXLRange.ToString() and IXLRangeAddress.ToString() now return context aware range strings. See #1021

Enhancements

  • #856 Clear pivot table rendered range upon loading to avoid conflict on rendered cells when opening the file.
  • #877 Add helper property VisibleRows for autofilters
  • #884 Allow pivot tables to have no theme
  • #904 Implement the full range of Item subclasses for pivot tables
  • #905 Don't choke on pivot table saving when formulas fail
  • #908 New methods to append and replace table data
  • #909 New method to reapply autofilter
  • #926 Disallow table with null .DataRange when saving
  • #930 Store picture markers against a range to allow shifting when inserting and deleting
  • #932 Optionally propagate extra table columns when changing data
  • #945 Allow Guid values to be inserted when dumping data
  • #962 Add ability to set cell value to an enum value
  • #978 Add table helper methods: IXLTables.TryGetTable()
  • #996 Allow table field names consisting of only whitespace
  • #999 Allow names to start with backslash
  • #1005 Ignore print areas that point to external workbooks
  • #1019 Improve XLCell.TryGetValue and implement additional tests
  • #1021 Return simpler range address values
  • #1025 Lazy initialize graphics
  • #1033 Optimize the performance of searching range intersections on small collections
  • #1035 Improve implicit string casting
  • #1036 Don't validate named ranges when loading file
  • #1044 Add check that data validation value length does not exceed 255 chars
  • #1064 Make TryGetValue_decimal_Good2test environment-independent

Bug fixes

  • #876 Correctly unprotect worksheet without password
  • #887 Correctly determine used range when there are merged ranges on a worksheet
  • #903 Clear all cell contents (formats too) in pivot table range upon loading.
  • #906 Maintain pivot table page field order
  • #931 Delete table rows correctly
  • #947 Fix pivot table integrity when saving multiple times
  • #956 Do not quote worksheet name with whitespace when saving pivot table worksheet source
  • #969 Fix ranges consolidation
  • #985 Set XLPivotField.ShowBlankItems by default to false and fix treatment of defaults from loading file
  • #988 FIX for XLConditionalFormat equality comparer fails for ColorScale
  • #998 Copies of tables should have RelId default to null
  • #1023 Sort ranges to shift before enumerating through them to avoid new target address conflicting with existing range
  • #1028 Copy row/column visibility setting
  • #1029 CellsUsed refactoring and fix
  • #1031 Fix for merging cells
  • #1032 Change the way invalid ranges are saved
  • #1041 Autofilter with multiple columns with filters
  • #1047 String comparisons should be case-insensitive
  • #1048 Shift data validation ranges when inserting / delete ranges