ClosedXML 0.100.3
Prefix ReservedSee the version list below for details.
dotnet add package ClosedXML --version 0.100.3
NuGet\Install-Package ClosedXML -Version 0.100.3
<PackageReference Include="ClosedXML" Version="0.100.3" />
paket add ClosedXML --version 0.100.3
#r "nuget: ClosedXML, 0.100.3"
// Install ClosedXML as a Cake Addin #addin nuget:?package=ClosedXML&version=0.100.3 // Install ClosedXML as a Cake Tool #tool nuget:?package=ClosedXML&version=0.100.3
Basic how to
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.
using (var workbook = new XLWorkbook())
{
var worksheet = workbook.Worksheets.Add("Sample Sheet");
worksheet.Cell("A1").Value = "Hello World!";
worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)";
workbook.SaveAs("HelloWorld.xlsx");
}
For more information see the documentation or the wiki.
Release notes from 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 number types are
cell.InsertData(new object[] { (sbyte)1, (byte)2, (short)3, (ushort)4, (uint)6, (long)7, (ulong)8, 15f, 17m });
Release notes for 0.100.2
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).
Release notes for 0.100.1
Added implicit conversion from decimal
to XLCellValue
, so it is not necessary to cast decimal
to double
explicitly.
Basically this now works, as it did pre-0.100:
decimal number = 15.5m;
ws.Cell(1,1).Value = number;
Release notes for 0.100
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 significantly 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). 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.
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
.NET Core | netcoreapp2.0 was computed. netcoreapp2.1 was computed. netcoreapp2.2 was computed. netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.0 is compatible. netstandard2.1 was computed. |
.NET Framework | net461 was computed. net462 was computed. net463 was computed. net47 was computed. net471 was computed. net472 was computed. net48 was computed. net481 was computed. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen40 was computed. tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
.NETStandard 2.0
- DocumentFormat.OpenXml (>= 2.16.0)
- ExcelNumberFormat (>= 1.1.0)
- Microsoft.CSharp (>= 4.7.0)
- SixLabors.Fonts (>= 1.0.0-beta18)
- System.Buffers (>= 4.5.1)
- System.IO.Packaging (>= 6.0.0)
- System.Memory (>= 4.5.4)
- XLParser (>= 1.5.2)
NuGet packages (380)
Showing the top 5 NuGet packages that depend on ClosedXML:
Package | Downloads |
---|---|
ClosedXML.Report
ClosedXML.Report is a tool for report generation and data analysis in .NET applications through the use of Microsoft Excel. ClosedXML.Report is a .NET-library for report generation Microsoft Excel without requiring Excel to be installed on the machine that's running the code. |
|
CsvHelper.Excel.Core
An implementation of ICsvParser and ICsvSerializer from CsvHelper that reads and writes using the ClosedXml library. |
|
ClosedXML.Extensions.Mvc
MVC extensions for ClosedXML |
|
ClosedXML.Extensions.WebApi
WebApi extensions for ClosedXML |
|
SanteDB.BI
SanteDB Business Intelligence (BI) core logic. Provides structures for data sources, queries, reports, and report controls rendered in all SanteDB software solutions. |
GitHub repositories (33)
Showing the top 5 popular GitHub repositories that depend on ClosedXML:
Repository | Stars |
---|---|
nopSolutions/nopCommerce
ASP.NET Core eCommerce software. nopCommerce is a free and open-source shopping cart.
|
|
mini-software/MiniExcel
Fast, Low-Memory, Easy Excel .NET helper to import/export/template spreadsheet (support Linux, Mac)
|
|
YarnSpinnerTool/YarnSpinner
Yarn Spinner is a tool for building interactive dialogue in games!
|
|
open-rpa/openrpa
Free Open Source Enterprise Grade RPA
|
|
phongnguyend/Practical.CleanArchitecture
Full-stack .Net 8 Clean Architecture (Microservices, Modular Monolith, Monolith), Blazor, Angular 18, React 18, Vue 3, BFF with YARP, Domain-Driven Design, CQRS, SOLID, Asp.Net Core Identity Custom Storage, OpenID Connect, Entity Framework Core, OpenTelemetry, SignalR, Hosted Services, Health Checks, Rate Limiting, Cloud Services (Azure, AWS, GCP).
|
Version | Downloads | Last updated |
---|---|---|
0.104.1 | 30,308 | 9/30/2024 |
0.104.0-rc1 | 10,790 | 9/17/2024 |
0.104.0-preview2 | 265,401 | 10/26/2023 |
0.103.0-beta | 23,429 | 9/28/2023 |
0.102.3 | 1,029,266 | 7/18/2024 |
0.102.2 | 5,637,609 | 1/5/2024 |
0.102.1 | 4,185,128 | 8/18/2023 |
0.102.0 | 1,684,841 | 6/24/2023 |
0.102.0-rc | 5,549 | 6/18/2023 |
0.101.0 | 2,771,908 | 4/9/2023 |
0.101.0-rc | 4,574 | 4/1/2023 |
0.100.3 | 3,505,909 | 1/12/2023 |
0.100.2 | 59,039 | 1/10/2023 |
0.100.1 | 25,392 | 1/9/2023 |
0.100.0 | 121,375 | 1/9/2023 |
0.97.0 | 4,109,434 | 10/21/2022 |
0.96.0 | 5,946,387 | 6/29/2022 |
0.95.4 | 21,370,785 | 12/16/2020 |
0.95.3 | 8,502,479 | 5/25/2020 |
0.95.2 | 705,941 | 4/26/2020 |
0.95.1 | 214,996 | 4/23/2020 |
0.95.0 | 1,118,527 | 4/15/2020 |
0.95.0-beta2 | 99,026 | 8/21/2019 |
0.95.0-beta1 | 43,994 | 4/4/2019 |
0.94.2 | 6,481,331 | 12/18/2018 |
0.94.0 | 68,949 | 12/12/2018 |
0.94.0-rc2 | 5,298 | 11/29/2018 |
0.94.0-rc1 | 8,521 | 11/11/2018 |
0.93.1 | 1,072,248 | 8/7/2018 |
0.93.0 | 497,517 | 6/25/2018 |
0.93.0-rc3 | 8,138 | 6/7/2018 |
0.93.0-rc2 | 4,164 | 5/31/2018 |
0.93.0-beta4 | 4,721 | 5/14/2018 |
0.93.0-beta2 | 5,382 | 4/26/2018 |
0.93.0-beta1 | 2,892 | 4/19/2018 |
0.92.1 | 1,082,118 | 4/10/2018 |
0.92.0-beta1 | 5,816 | 3/22/2018 |
0.91.1 | 60,219 | 4/4/2018 |
0.91.0 | 302,850 | 1/31/2018 |
0.91.0-beta3 | 4,213 | 1/23/2018 |
0.91.0-beta2 | 14,509 | 12/8/2017 |
0.91.0-beta1 | 2,946 | 11/29/2017 |
0.90.0 | 740,617 | 10/23/2017 |
0.90.0-beta2 | 3,245 | 10/6/2017 |
0.89.0 | 392,182 | 9/12/2017 |
0.89.0-beta1 | 5,282 | 8/23/2017 |
0.88.0 | 221,938 | 7/24/2017 |
0.88.0-beta1 | 10,540 | 7/10/2017 |
0.87.1 | 1,703,917 | 4/3/2017 |
0.86.0 | 425,031 | 1/6/2017 |
0.85.0 | 339,202 | 12/7/2016 |
0.80.1 | 727,287 | 9/15/2016 |
0.76.0 | 1,662,407 | 12/16/2014 |
0.75.0 | 209,970 | 9/17/2014 |
0.74.0 | 41,310 | 8/10/2014 |
0.73.0 | 39,686 | 6/24/2014 |
0.72.3 | 28,154 | 6/4/2014 |
0.72.2 | 4,559 | 6/4/2014 |
0.72.1 | 57,828 | 6/4/2014 |
0.72.0 | 10,934 | 6/4/2014 |
0.71.1 | 17,082 | 5/26/2014 |
0.70.0 | 12,800 | 5/18/2014 |
0.69.2 | 164,698 | 10/3/2013 |
0.69.1 | 85,174 | 8/15/2013 |
0.69.0 | 7,429 | 8/10/2013 |
0.68.1 | 103,005 | 10/20/2012 |
0.68.0 | 5,992 | 10/12/2012 |
0.67.2 | 23,486 | 8/14/2012 |
0.67.1 | 4,608 | 8/13/2012 |
0.67.0 | 4,718 | 8/12/2012 |
0.66.1 | 5,400 | 7/28/2012 |
0.66.0 | 4,966 | 7/18/2012 |
0.65.2 | 10,189 | 4/21/2012 |
0.64.0 | 22,141 | 2/4/2012 |