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!
Let's dive in...
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.
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:
You can check the box for Dynamic Array under Calculation Engine to enable the support for dynamic arrays.
[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.
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.
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:
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.
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.
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())
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.
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.
By popular demand, the new release of Spread.NET 12 v12 sp1 includes new VB.NET demo samples! Now all the helpful demo samples for Spread.NET 12 for WinForms are available in both C# and VB.NET.
To get these new sample, download and install Spread.NET 12 for WinForms today!