ClosedXML 0.97.0

.NET Standard 2.0
dotnet add package ClosedXML --version 0.97.0
NuGet\Install-Package ClosedXML -Version 0.97.0
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="ClosedXML" Version="0.97.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add ClosedXML --version 0.97.0
#r "nuget: ClosedXML, 0.97.0"
#r directive can be used in F# Interactive, C# scripting and .NET Interactive. Copy this into the interactive tool or source code of the script to reference the package.
// Install ClosedXML as a Cake Addin
#addin nuget:?package=ClosedXML&version=0.97.0

// Install ClosedXML as a Cake Tool
#tool nuget:?package=ClosedXML&version=0.97.0

Release 0.97 - Laying foundation

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.

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

Product Versions
.NET net5.0 net5.0-windows net6.0 net6.0-android net6.0-ios net6.0-maccatalyst net6.0-macos net6.0-tvos net6.0-windows net7.0 net7.0-android net7.0-ios net7.0-maccatalyst net7.0-macos net7.0-tvos net7.0-windows
.NET Core netcoreapp2.0 netcoreapp2.1 netcoreapp2.2 netcoreapp3.0 netcoreapp3.1
.NET Standard netstandard2.0 netstandard2.1
.NET Framework net461 net462 net463 net47 net471 net472 net48
MonoAndroid monoandroid
MonoMac monomac
MonoTouch monotouch
Tizen tizen40 tizen60
Xamarin.iOS xamarinios
Xamarin.Mac xamarinmac
Xamarin.TVOS xamarintvos
Xamarin.WatchOS xamarinwatchos
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (214)

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.

FidelizarMais.Shared.Packages.Common

Package Description

ClosedXML.Extensions.Mvc

MVC extensions for ClosedXML

CsvHelper.Excel

An implementation of ICsvParser and ICsvSerializer from CsvHelper that reads and writes using the ClosedXml library.

CsvHelper.Excel.Core

An implementation of ICsvParser and ICsvSerializer from CsvHelper that reads and writes using the ClosedXml library.

GitHub repositories (22)

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.
fullstackhero/dotnet-webapi-boilerplate
Clean Architecture Template for .NET 6.0 WebApi built with Multitenancy Support.
focus-creative-games/luban
你的最佳游戏配置解决方案 {excel, csv, xls, xlsx, json, bson, xml, yaml, lua, unity scriptableobject} => {json, bson, xml, lua, yaml, protobuf(pb), msgpack, flatbuffers, erlang, custom template} data + {c++, java, c#, go(golang), lua, javascript(js), typescript(ts), erlang, rust, gdscript, protobuf schema, flatbuffers schema, custom template} code。 不仅仅是导表工具。支持unity , ue4, tolua, xlua, slua, ilruntime, puerts等热更新插件;强大的数据校验能力;完善的本地化机制。a powerful game config export tool and code generator that supports for i18n and l10n.
mini-software/MiniExcel
Fast, Low-Memory, Easy Excel .NET helper to import/export/template spreadsheet
phongnguyend/Practical.CleanArchitecture
Asp.Net Core 6 Clean Architecture (Microservices + Dapr, Modular Monolith, Monolith), Blazor, Angular 14, React 18, Vue 2.7, Domain-Driven Design, CQRS, SOLID, Asp.Net Core Identity Custom Storage, Identity Server 4 Admin UI, Entity Framework Core, Selenium, SignalR, Hosted Services, Health Checks, Cloud (Azure, AWS) Services, ...
Version Downloads Last updated
0.97.0 188,756 10/21/2022
0.96.0 1,221,456 6/29/2022
0.95.4 10,306,279 12/16/2020
0.95.3 4,818,769 5/25/2020
0.95.2 513,360 4/26/2020
0.95.1 139,360 4/23/2020
0.95.0 465,833 4/15/2020
0.95.0-beta2 71,810 8/21/2019
0.95.0-beta1 38,411 4/4/2019
0.94.2 4,364,355 12/18/2018
0.94.0 46,844 12/12/2018
0.94.0-rc2 4,467 11/29/2018
0.94.0-rc1 7,199 11/11/2018
0.93.1 835,981 8/7/2018
0.93.0 371,483 6/25/2018
0.93.0-rc3 7,361 6/7/2018
0.93.0-rc2 2,977 5/31/2018
0.93.0-beta4 3,960 5/14/2018
0.93.0-beta2 4,400 4/26/2018
0.93.0-beta1 2,119 4/19/2018
0.92.1 652,604 4/10/2018
0.92.0-beta1 4,252 3/22/2018
0.91.1 39,780 4/4/2018
0.91.0 229,267 1/31/2018
0.91.0-beta3 2,702 1/23/2018
0.91.0-beta2 12,499 12/8/2017
0.91.0-beta1 2,143 11/29/2017
0.90.0 544,817 10/23/2017
0.90.0-beta2 2,436 10/6/2017
0.89.0 318,559 9/12/2017
0.89.0-beta1 4,170 8/23/2017
0.88.0 176,831 7/24/2017
0.88.0-beta1 9,764 7/10/2017
0.87.1 1,030,677 4/3/2017
0.86.0 307,150 1/6/2017
0.85.0 234,597 12/7/2016
0.80.1 477,867 9/15/2016
0.76.0 1,339,987 12/16/2014
0.75.0 159,072 9/17/2014
0.74.0 30,178 8/10/2014
0.73.0 35,522 6/24/2014
0.72.3 18,111 6/4/2014
0.72.2 2,373 6/4/2014
0.72.1 46,912 6/4/2014
0.72.0 5,649 6/4/2014
0.71.1 11,893 5/26/2014
0.70.0 7,358 5/18/2014
0.69.2 136,887 10/3/2013
0.69.1 52,795 8/15/2013
0.69.0 4,640 8/10/2013
0.68.1 75,864 10/20/2012
0.68.0 3,451 10/12/2012
0.67.2 16,579 8/14/2012
0.67.1 2,377 8/13/2012
0.67.0 2,417 8/12/2012
0.66.1 3,076 7/28/2012
0.66.0 2,591 7/18/2012
0.65.2 5,878 4/21/2012
0.64.0 7,328 2/4/2012