What's New in Spread.NET v13

Spread.NET v13.45.20203.0 - August 5, 2020

• various bugfixes (see release notes)

Spread.NET v13.45.20201.0 - April 22, 2020

• various bugfixes (see release notes)

Spread.NET v13 - November 5, 2019

Enhanced Shape Engine

Spread.NET 13 WinForms features a new enhanced shapes engine that is 100% compatible with Microsoft Excel®.

Spread.NET 13 WinForms can import this macro-enabled XLSM workbook using the Document Caching feature and keep the embedded VBA in memory for later export. The enhanced shapes support the IShape.Action event to handle shape-clicks and implement the identical logic as the VBA in C# or VB.NET code. You can find this new demo sample in the Spread.NET 13 WinForms Control Explorer under Showcase demos.

Documentation:

• Enhanced Shape Engine

• Document Caching

Shape Property Bindings

Enhanced shape property bindings supports binding the AutoShapeType, TextEffect, Fill, Line, Top, Left, Height, and Width properties to particular cells, enabling some very rich user interface possibilities. Cell formulas can update any of those properties dynamically based on calculations.

Documentation:

• Shape Property Bindings

Copy-paste Enhancements

Spread.NET 13 WinForms features new enhanced rich copy/paste between Microsoft Excel® and Spread. To enable this new functionality, you must set a property in the Property Grid:

 Figure 6 - Enable FpSpread.Features.RichClipboard using property grid

Documentation:

• New Enhanced Rich Copy/Paste

Slicers for Table Filtering

Spread.NET 13 WinForms features new slicers for table filtering, including import and export of slicers in XLSX files and all built-in slicer styles supported by Microsoft Excel®.

Slicers can be applied to the spreadsheet using the Spread Designer tool:

 Figure 7 - Insert Slicer in Spread Designer

The Spread Designer will show the Insert Slicer dialog:

 Figure 8 - Insert Slicer Dialog

The Slicer object shows the unique values in the table column and can quickly filter the table to show only the rows with that value in the table column:

 Figure 9 - Slicer created for Country table column

You can create rich interfaces and dashboards by combining Slicers with Charts – the Slicers and Charts do not need to be on the same Worksheet as the Table for the Slicer, so you can create the Chart to hide the Series that are not visible, and user's changes to the Slicer will update the Chart.

Documentation:

• Working With Slicers

VisualFunctions for Custom Data Visualization

Spread.NET 13 WinForms features support for a new type of custom calculation function called a VisualFunction.

Using this feature to draw custom content in the cell is similar to creating a custom Sparkline, but much easier, as this feature integrates with the new Calculation Engine and VisualFunction inherts from the new Function class.

Documentation:

• Creating and Using a Visual Function
• Using Sparklines
• Calculation Engine

Enhanced XLOOKUP and XMATCH Functions

Spread.NET 13 WinForms features enhanced support for the new XLOOKUP and XMATCH functions, which are at this time still only available in the Office Insiders builds of Microsoft Excel®.

These new functions are much better than the old LOOKUP, VLOOKUP, HLOOKUP, and MATCH for several reasons:

1. XLOOKUP and XMATCH can perform vertical or horizontal lookups (or both when nested), depending on the orientation of lookup_array.
2. XLOOKUP and XMATCH can perform non-exact lookups with correct results even when the data is not sorted (unlike HLOOKUP/VLOOKUP/MATCH).
3. XLOOKUP and XMATCH perform exact-match lookups by default (unlike LOOKUP/HLOOKUP/VLOOKUP/MATCH).
4. XLOOKUP does not require referencing the entire range containing _lookup_array_ and _return_array_, only those two particular ranges – thus XLOOKUP can be more efficient than HLOOKUP/VLOOKUP in terms of required recalculations.
5. XLOOKUP arguments adjust automatically when columns or rows are inserted or removed that move the _lookup_array_ or _return_array_, since it used range references instead of indexes.
6. XLOOKUP and XMATCH are enhanced in Spread.NET to support a new _search_mode 0 - All_ which returns all matching items in an array, which can spill to adjacent cells when dynamic arrays are enabled.

Documentation:

• XLOOKUP
• XMATCH
• Working With Dynamic Array Formulas

Automatic Formatting of Formula Values

1. Cell Formula using certain functions automatically applies cell format (like Microsoft Excel).
2. Cell References to other cells automatically use the format of the referenced cell like Microsoft Excel.
3. This feature must be enabled explicitly to work (for backwards compatibility).

Spread.NET 13 WinForms features automatic formatting of formula values like Microsoft Excel®. You can type a formula using the DATE function and the value will automatically format as date, or type a formula which references a cell formatted as currency, accounting, or any other cell format, and that format will also get applied automatically on the value in the formula cell. You can still apply a new cell format to replace the automatic format if you want to show a different format in the cell.

Documentation:

• Auto Format Formulas
• FormatCells Class Members
• IWorkbook.Features

Automatic adjustment of the row header Width

Spread.NET 13 WinForms features a simple enhancement which many customers have requested – dynamically-sized row headers that adjust automatically to show the row numbers as you scroll down the sheet:

Figures 13-15 Automatic adjustment of row header width while scrolling down

This feature allows the user to scroll down very large spreadsheets and see the row index numbers.

Documentation:

• Auto Expand Row Headers

BackgroundImage in IWorksheet

Spread.NET 13 WinForms now supports setting a background image for the workbook in the IWorkbook interface using the new overloaded method SetBackgroundPicture:

Documentation:

• SetBackgroundPicture Method

Editing Formulas Now Inserts Structured References to Table Cells

 Figure 16 - Editing formula inserts structured references to table cells

When editing a formula using the FormulaTextBox control, you can select a table cell, table column, or entire table and a structured reference will automatically be inserted when applicable.For entire table column references and entire table references, this is always applicable, but for table cell references to use the '[@ColumnName]' syntax, the formula cell must be in the same row as the table cell – if the table cell is in a different row, then a regular cell reference must be used instead.

Documentation:

• Working with the Formula Text Box
• Structured References

New BeforeRightClick Event

Spread.NET 13 WinForms features a new event BeforeRightClick which fires when the end-user right-clicks inside the spreadsheet, before any processing by the control to handle showing the context menu.

Documentation:

• BeforeRightClick Event
• Locating the Pointer Using HitTest

New Samples Added in WinForms Control Explorer

1. Showcase: Car Insurance Claim shows how to take a Macro-enabled Microsoft Excel® workbook (*.XLSM) containing VBA code and custom shapes and convert it into a .NET WinForms application that can export a new Macro-enabled Microsoft Excel® workbook with all user changes and VBA macro content preserved in the resulting XLSM.
2. Features: Calculation – Dynamic Arrays shows how to use the Dynamic Array feature and contains many useful examples using dynamic arrays, including worksheets for each of the new formula functions FILTER, RANDARRAY, SEQUENCE, SINGLE, SORT, SORTBY, and UNIQUE. Learn now to create cascading dependent drop-down lists and searchable drop-down lists, generate cross-tab reports with three easy formulas, and how to apply dynamic arrays to do many other tasks.
3. Features: Calculation – Formula Tracing shows how to trace the precedent and dependent cells for formulas, showing arrows between referenced cells, and how to use new APIs for tracing the precedent and dependent cells for formulas to generate detailed reports for auditing cell formulas.
4. Features: Calculation – Custom Function shows how to implement a custom calculation function that returns an array or results, which can be used with an array formula or using the new Dynamic Array feature (if enabled with IWorkbookSet.CalculationEngine.CalcFeatures). A custom function is a custom class that implements a new calculation function that can be used directly in cell formulas in code or by the end-user, can perform any required logic, and can return any of the standard types of formula function results including returning cell or range references or arrays or values.
5. Features: Calculation – XLOOKUP and XMATCH Functions shows how to use the new XLOOKUP and XMATCH calculation functions and the enhanced search_mode 0 - All to return all matching items in an array.
6. Features: Calculation – Custom Data Visualization shows how to create a newVisualFunctionwhich draws a QR code inside the cell.
7. Features: Slicers – Introduction shows how to use slicers for filtering a table in a worksheet, including how to set the slicer style and settings.
8. Features: New Shape Engine – Flowchart shows how to create a flowchart using the new Enhanced Shape Engine.

New Samples Added in WinForms Control Explorer:

• Spread.NET 13 WinForms Control Explorer