ClosedXML/ClosedXML
### [`v0.97.0`](https://togithub.com/ClosedXML/ClosedXML/releases/tag/0.97.0)
[Compare Source](https://togithub.com/ClosedXML/ClosedXML/compare/0.96.0...0.97.0)
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](https://togithub.com/ClosedXML/ClosedXML/issues/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](https://togithub.com/SixLabors/Fonts) library for font measurements. You can read more on the [Graphic Engine](https://togithub.com/ClosedXML/ClosedXML/wiki/Graphic-Engine) wiki page.
On non-windows environment, it will be necessary to specify a default font. Use this code
```csharp
// 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://togithub.com/spreadsheetlab/XLParser/issues/163), [https://github.com/spreadsheetlab/XLParser/issues/161](https://togithub.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.
```csharp
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
A major change has been implicit intersections in the semantic of 2019 excel (Excel 2021 and 365 already support dynamic array formulas):
```csharp
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`.
```csharp
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](https://togithub.com/ClosedXML/ClosedXML/issues/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.
```csharp
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).
Configuration
š Schedule: Branch creation - At any time (no schedule defined), Automerge - At any time (no schedule defined).
š¦ Automerge: Disabled by config. Please merge this manually once you are satisfied.
ā» Rebasing: Whenever PR becomes conflicted, or you tick the rebase/retry checkbox.
š Ignore: Close this PR and you won't be reminded about this update again.
[ ] If you want to rebase/retry this PR, click this checkbox.
This PR has been generated by Mend Renovate. View repository job log here.
This PR contains the following updates:
0.96.0->0.97.0Release Notes
ClosedXML/ClosedXML
### [`v0.97.0`](https://togithub.com/ClosedXML/ClosedXML/releases/tag/0.97.0) [Compare Source](https://togithub.com/ClosedXML/ClosedXML/compare/0.96.0...0.97.0) 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](https://togithub.com/ClosedXML/ClosedXML/issues/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](https://togithub.com/SixLabors/Fonts) library for font measurements. You can read more on the [Graphic Engine](https://togithub.com/ClosedXML/ClosedXML/wiki/Graphic-Engine) wiki page. On non-windows environment, it will be necessary to specify a default font. Use this code ```csharp // 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://togithub.com/spreadsheetlab/XLParser/issues/163), [https://github.com/spreadsheetlab/XLParser/issues/161](https://togithub.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. ```csharp 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  A major change has been implicit intersections in the semantic of 2019 excel (Excel 2021 and 365 already support dynamic array formulas): ```csharp 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`. ```csharp 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](https://togithub.com/ClosedXML/ClosedXML/issues/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. ```csharp 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).Configuration
š Schedule: Branch creation - At any time (no schedule defined), Automerge - At any time (no schedule defined).
š¦ Automerge: Disabled by config. Please merge this manually once you are satisfied.
ā» Rebasing: Whenever PR becomes conflicted, or you tick the rebase/retry checkbox.
š Ignore: Close this PR and you won't be reminded about this update again.
This PR has been generated by Mend Renovate. View repository job log here.