We're happy to announce the release of Spread.NET 12 SP1 version 12.0.20191.0. This release is especially exciting, with enhancements that improve performance, upgrading experience, and Excel-like features. The Spread.NET 12 v1 sp1 is now available for immediate download!
New Features and Enhancements
- Dynamic Array Support - You can now enter cell formulas that return array values which “spill” into empty adjacent cells! This powerful feature is a new alternative to array formulas and includes support for seven new formula functions and the new operators for spilled range (#) and range intersection (@).
- New Spread Designer Enhancements - The Spread Designer tool now supports the Formulas tab with tools for Defined Names, Formula Auditing and Calculation. This new UI also supports enabling the new dynamic array support in the spreadsheet (which is not enabled by default).
- Alternating Styles Support - You can now apply table-like formatting to entire sheets and skins with alternating row and column styles using the new flat-style model. This feature includes support for built-in Excel styles and themes, including gradient and pattern fill.
- Deselect the Selection like Microsoft Excel® – You can now use the CTRL key with the mouse or keyboard to remove specific cells from the selection, as in Microsoft Excel®.
- 1-based Indexing like VSTO – You can now create the IWorkbook to use 1-based indexing for all GrapeCity.Spreadsheet.IWorkbook APIs, like Microsoft Excel® VSTO APIs. This can enable easier porting of Excel VBA code or old Spread COM version code (which also used 1-based row and column indexing) to .NET, which has always used 0-based indexing for all APIs in previous releases for CLS compliance.
- New F4 Key Action – You can now use the F4 key while editing a formula reference to toggle the reference between absolute and relative column and row references, as in Microsoft Excel®.
- New Constructor for AsyncFunction – You can now create a custom AsyncFunction which uses specified FunctionAttributes. This enables creating custom asynchronous functions which are volatile, or which return arrays, or otherwise require specifying some FunctionAttributes for proper function.
Let's dive in...
Dynamic Arrays are Here
The most exciting thing in this new release is the dynamic array support. This new feature enables users to create new formulas which returning arrays of results that automatically “spill” to adjacent empty cells and includes several new powerful functions that make many previously very complex use cases much easier by greatly simplifying the formulas required.
Implementing dependent drop-down lists and searchable dropdowns are much cleaner to create and much simpler to follow and audit, since the formulas are so much less complicated.
Using Dynamic Arrays
First you need to enable dynamic arrays in the spreadsheet – by default, dynamic arrays are not automatically enabled, and you need to explicitly enable them in one of the following ways:
- In the Spread Designer for Windows Forms, there is a new Formulas tab:
You can check the box for Dynamic Array under Calculation Engine to enable the support for dynamic arrays.
- Using code, you can enable dynamic arrays using the new flag in CalculationFeatures:
[C#]
fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = GrapeCity.Spreadsheet.CalcFeatures.DynamicArray;
[VB]
FpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = GrapeCity.Spreadsheet.CalcFeatures.DynamicArray
You can type a formula like “={1,1,2,2,3}” which hard codes a horizontal array of values which spills horizontally:
Or a formula like “={1;1;2;2;3}” which hard codes a vertical array of values which spills vertically:
Or a formula like “={1,1,2,1,3;1,1,2,1,3;1,2,3,2,5;1,2,3,2,5;1,2,2,2,5}” which hard codes a 2-dimensional array of values which spills horizontally and vertically:
When any cell in the spilled range is selected, a blue border is drawn around the entire spilled range to indicate the range, and the FormulaTextBox shows the cell formula as disabled and is not editable.
Powerful New Functions for Dynamic Arrays
Six new functions for dynamic arrays are now supported, empowering you with new tools for fast data analysis. The SEQUENCE and RANDARRAY functions can generate arrays of sequential or random data, and the new FILTER, SORT, SORTBY, and UNIQUE functions make data analysis much easier that ever before. These new functions enable many common use cases with far fewer and much simpler formulas than before.
Create a random ordering for a list using SORTBY with RANDARRAY:
Create headers for a schedule of time slots every 10 minutes using SEQUENCE and NOW:
Create a pivot table crosstab report directly in cells using just three formulas and TRANSPOSE, SORT, UNIQUE, and SUMIFS:
There is so much more this feature can do – many upcoming blogs will cover this feature in depth and explore the powerful use cases enabled by these new functions.
Formula Tracing in WinForms Spread Designer
In my recent blog series about Spread.NET 12 WinForms calculation, I wrote about how to use Spread.NET 12 WinForms to audit calculations and trace formula references.
Now with this release of Spread.NET 12 WinForms, these formula tracing tools are available under the Formula Auditing group in Trace Precedents, Trace Dependents, and Remove Arrows:
New Alternating Styles Support
Spread.NET 12 WinForms now supports alternating row and column styles in the new default flat-style spreadsheet core model for styles. Now sheet skins can apply alternating row style settings to the sheet, even when using the new flat-style core!
This support includes both row and column styles, implemented directly in the spreadsheet core (GrapeCity.Spreadsheet.dll) using the same optimized implementation as table styles, so performance is awesome!
Now when using the flat-style spreadsheet core, the legacy AlternatingRows class will wrap the new interfaces and make old code work to set the alternating row styles, or you can call directly into the new public interfaces of GrapeCity.Spreadsheet.dll to create the alternating row and column styles.
Deselect the Selection
Microsoft Excel® now supports deselecting cells which are selected and keeping the remaining selection – now Spread.NET WinForms 12 will emulate this behavior when the SelectionPolicy is MultiRange. This means that you can easily select a range of cells to apply some change, when deselect certain of those cells to skip applying the change to those cells, and keep the remaining cells selected, which is actually a very useful enhancement for surprisingly many use cases!
It is often easier to select a large range and then deselect certain parts than it is to select many non-contiguous ranges at once to make a change.
1-Based Indexing Like VSTO
Microsoft Excel® supports 1-based indexing in the Visual Basic for Applications (VBA) object model for Visual Studio Tools for Office (VSTO), and a common use case for Spread.NET is to create a spreadsheet application from a macro-enabled Microsoft Excel® workbook that uses VBA and VSTO. Now porting VBA code to Spread.NET is easier than ever before, because now you can keep all 1-based indexing logic when porting to Spread.NET using the new support for a 1-based wrapper!
All it takes is one call to get the 1-based object:
[C#]
IWorkbook iwb = WorkbookSet.CreateBase1Object(fpSpread1.AsWorkbook());
[VB]
Dim iwb As IWorkbook = WorkbookSet.CreateBase1Object(FpSpread1.AsWorkbook())
New F4 Action
Microsoft Excel® supports using the F4 key while typing a formula reference to toggle the reference between various combinations of relative and absolute references for the column and row, and this is also now supported starting with this release of Spread.NET 12 WinForms. This minor enhancement can make typing formulas with specific relative and absolute requirements a lot easier than before.
AsynFunction and FunctionAttributes
Spread.NET 12 WinForms has rich support for creating custom calculation functions. There is even support for creating asynchronous functions like the RTD function using the AyncFunction class.
Starting with this release of Spread.NET, you can create custom calculation functions that inherit from AsyncFunction and implement support for asynchronous calculation, and also use various FunctionAttributes to indicate special function behaviors, such as volatility or returning an array value.