Spread.NET 13 is Released

Spread.NET 13 is now available. This release features many enhancements to Microsoft Excel-compatibility and several new features and enhancements.

What's New in Spread.NET 13

Enhanced Shape Engine

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

To use the new Enhanced Shape Engine, you must first enable it either using the Property Grid:

Figure 1 - Enable FpSpread.Features.EnhancedShapesEngine using property grid Figure 1 - Enable FpSpread.Features.EnhancedShapesEngine using property grid

Or you can enable the Enhanced Shape Engine engine in code:

[C#]

Enable Enhanced Shaped Engine in code

fpSpread1.Features.EnhancedShapeEngine = true;

[VB]

Enable Enhanced Shape Engine in code

fpSpread1.Features.EnhancedShapeEngine = True

All the Excel shapes are supported:

Figure 2 Enhanced Shapes Figure 2 - Enhanced Shapes

The Enhanced Shape Engine supports multiple selection, grouping, adjustments, and connection points, and can be used to create diagrams, cell call-outs, and flowcharts:

Figure 3 Flowchart example created in Spread Figure 3 - Flowchart example created in Spread

Above is a sample flowchart created in code using the new Enhanced Shape Engine API, which mirrors the VSTO API. The flowchart comes from one of my favorite web comics.

Enhanced Shapes Engine can be used to create a rich interactive interface such as this car insurance claim example:

Figure 4 Car Insurance Claim created using shapes Figure 4 - Car Insurance Claim created using shapes

This example was created using Microsoft Excel® to port the code from an identical SpreadJS blog and demo sample, then using VBA to implement the logic to handle the clicks on the shapes.

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:

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.

In the example above showing the car insurance claim, the shape properties are bound to values in Sheet2:

Figure 5 Shape property bindings for car insurance claim sample Figure 5 - Shape property bindings for car insurance claim sample

Binding the shape properties in code is easy:

[C#]

Bind Shape Properties

IWorkbook workbook = fpSpread1.AsWorkbook();
IShapes shapes = workbook.Worksheets[0].Shapes;

// bind shape properties to cells
shapes["Front"].Bindings.Add("Left", "Sheet2!B2");
shapes["Front"].Bindings.Add("Top", "Sheet2!C2");
shapes["Front"].Bindings.Add("Width", "Sheet2!D2");
shapes["Front"].Bindings.Add("Height", "Sheet2!E2");
shapes["Front"].Bindings.Add("Line", "Sheet2!F2");

[VB]

Bind Shape Properties

Dim workbook As IWorkbook = fpSpread1.AsWorkbook
Dim shapes As IShapes = workbook.Worksheets(0).Shapes


' bind shape properties to cells
shapes("Front").Bindings.Add("Left", "Sheet2!B2")
shapes("Front").Bindings.Add("Top", "Sheet2!C2")
shapes(Front").Bindings.Add("Width", "Sheet2!D2")
shapes("Front").Bindings.Add("Height", "Sheet2!E2")
shapes("Front").Bindings.Add("Line", "Sheet2!F2")

Documentation:

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 Figure 6 - Enable FpSpread.Features.RichClipboard using property grid

Or you can set this property in code:

[C#]

Enable FpSpread.Features.RichClipboard in code

fpSpread1.Features.RichClipboard = true;

[VB]

Enable FpSpread.Features.RichClipboard in code

fpSpread1.Features.RichClipboard = True

With RichClipboard enabled, you can:

  1. Copy a selected cell range, or a set of selected cell ranges, from Microsoft Excel® to Spread, including formatting, formulas, and values.
  2. Copy a selected Shape, Picture, Slicer, or a set of one or more Shape(s), Picture(s), and Slicer(s) from Microsoft Excel® to Spread or from Spread to Microsoft Excel®, including formatting, styles, effects, and text.

Documentation:

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 Figure 7 - Insert Slicer in Spread Designer

The Spread Designer will show the Insert Slicer dialog:

Figure 8 Insert Slicer Dialog Figure 8 - Insert Slicer Dialog

You can also show the Insert Slicer dialog in code:

[C#]

Show Insert Slicer Dialog in code

SlicerInsertForm dlg = new SlicerInsertForm(table, new Point(25, 25));
dlg.ShowDialog(this);

[VB] Show Insert Slicer dialog in code

Dim dlg As SlicerInsertForm = new SlicerInsertForm(table, new Point(25, 25))
dlg.ShowDialog(Me)

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 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:

VisualFunctions for Custom Data Visualization

Spread.NET 13 WinForms features support for a new type of custom calculation function called a VisualFunction. You can define a custom formula function that draws content in the cell or applies formatting.

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.

Using this feature to apply a style in the cell is similar to conditional format, but easier, faster, and more powerful, since you can implement custom logic for the condition and avoid the overhead of conditional formats.

For example, you can define a VisualFunction which draws a QR code:

Figure 10 VisualFunction showing QR code Figure 10 - VisualFunction showing QR code

Referencing a VisualFunction in a formula requires using the prefix "VF." on the name of the VisualFunction, and the function can have customized arguments. The function implementation determines the arguments required for the function and handles drawing the content in the cell based on those arguments specified in the cell formula. In the above example, the VisualFunction QRCODE accepts two arguments, and the formula specifies cells B1 and C1, so the result is a standard QR code encoding the GrapeCity Spread website.

Documentation:

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.

These new functions do not require enabling the Dynamic Array feature, but it is necessary to enable that feature when using the enhanced _search_mode 0 - All_ which returns all matching items if you want those values to spill to adjacent cells:

Figure 11 Enabling Dynamic Array in ribbon bar Formulas tab Figure 11 - Enabling Dynamic Array in ribbon bar Formulas tab

You can also enable the Dynamic Array feature in code:

[C#] Enable Dynamic Arrays in code

fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = GrapeCity.Spreadsheet.CalcFeatures.DynamicArray;

[VB] Enable Dynamic Arrays in code

fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = GrapeCity.Spreadsheet.CalcFeatures.DynamicArray

Documentation:

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.

The automatic format is based on the cell format in the final referenced cell in the formula which contains the value. For example, if you type a value like 1500.65 into cell A1 and then apply a Currency format, then the cell shows $1500.65. If you then type a formula =A1 in cell A2, then the value in cell A2 will also show the Currency format.

If you open the Cell Format dialog for cell A2 and change the cell format in that cell to Accounting, then the value in cell A2 will update to show Accounting format instead of Currency format.

If you then type the formula =A1 in cell B1 and then drag-fill the formula down to cell B2, then both of those cells will show the value $1500.65 formatting as Currency, because Currency format is the format that is applied to the cell at the end of the reference chain, A1:

Figure 12 Automatic formatting of formula values based on original cell format Figure 12 - Automatic formatting of formula values based on original cell format

Please note that for backwards compatibility, this feature is not enabled by default and must be explicitly enabled in the application in code using IWorkbook.Features:

[C#] Enable IWorkbook.Features.AutoFormatting in code workbook.Features.AutoFormatting = True

[VB] Enable IWorkbook.Features.AutoFormatting in code workbook.Features.AutoFormatting = True

Documentation:

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:

13

14

15

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:

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:

[C#] IWorksheet.SetBackgroundImage method overloads

void IWorksheet.SetBackgroundPicture(string filename, [string contentType = null])
void IWorksheet.SetBackgroundPicture(System.IO.Stream imageStream, string contentType)

[VB] IWorksheet.SetBackgroundImage method overloads

Sub SetBackgroundPicture(filename As String, Optional contentType As String = Nothing)
Sub SetBackgroundPicture(imageStream As System.IO.Stream, contentType As String)

The picture must be loaded from a file or stream. and the content type specified must be:

  • image/jpeg
  • image/png
  • image/tiff
  • image/gif
  • image/Bmp

Documentation:

Editing Formulas Now Inserts Structured References to Table Cells

Figure 16 Editing formula 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:

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. You can implement code in the event handler to use the FpSpread.HitTest method to determine the clicked location and object under the cursor, and override the default behavior to show a custom context menu, or modify the default context menu, or attach event handlers to the default context menu – this new event introduces lots of new flexibility for handling right-clicks in the spreadsheet.

Documentation:

New Samples Added in WinForms Control Explorer

The new Spread.NET 13 WinForms Control Explorer has some exciting new samples to demonstrate features and common use cases. All samples are available in both C# and VB and are included in the setup installer:

  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:

Download Now!<%/if%>

Sean Lawyer

Product Manager
comments powered by Disqus