What's New in Spread.NET v12

Spread.NET v12.2 - July 22, 2019

This release includes:

• New Windows Forms version 12.45.20193.0
• New WPF/Silverlight version 12.45.20193.0
• New WinRT version 12.45.20193.0
• New ASP.NET version 12.45.20194.0

This release includes customer reported bug fixes for Windows Forms.

Read the release notes

This release is free for all licensed users of Spread.NET 12 and is also available as a 30-day free trial for new customers.

Spread.NET v12.1 - May 28, 2019

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:

1. 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.

2. 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.

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:

Formula Tracing in WinForms Spread Designer

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.

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.

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.

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.

Spread.NET v12 - December 5, 2018

Exchangeable file format enhances performance

Spread.NET 12 features a new exchangeable XLSX file format enhancement. The new ExcelSaveFlag and ExcelOpenFlag enumeration, available when saving or loading XLSX files using the Spread Designer or code in run-time, includes all custom Spread objects that previously would have been lost on export to Excel format (like cell types and column footers). Using the new Exchangeable XLSX file format, all custom Spread settings are preserved inside the XLSX as custom streams that are loaded back with the other contents on reload. Going forward, this new file format replaces the XML serialization supported by the control and will be vastly more efficient to use with much smaller file sizes.

Making Spread.NET more Excel-like

New default instances of Spread.NET Windows Forms 12 support:

• Drag-drop cell ranges
• Drag-fill cell ranges
• Moving sheet tabs in the tab strip
• Selecting multiple ranges
• Typing formulas into cells
• Automatically resizing rows as in Excel
• Calculating formulas as in Excel (Date-related functions return double values)
• Collapsing cell borders to render the border lines like Excel
• New core-style integration and DefaultCellType rendering for enhanced cell styles
• Protect setting for worksheets initialized to false, and Locked setting for all cells initialized to True, as in Excel
• Hidden rows and columns with the zero-size indicator as in Excel
• Tab strip always as in Excel
• Enhanced filter user interface for sheets and tables as in Excel
• New keyboard mappings with more built-in actions such as the new AutoSum action [Alt]+[=]

To make these changes, we've changed the default values of many properties for new instances created using version 12.

In addition, we've released several new features and dialogs that make the end-user experience even more familiar, including:

• More Excel-like built-in cell styles
• Excel-like Format Dialog
• Number formatting
• Gradient and pattern fill
• Enhanced sort and filtering on a cell range
• Import, export, and modify Office document properties

Learn more about new Excel-like features in Spread's .NET spreadsheet

Improving the Upgrade Experience - Backwards Compatibility For Upgrades to Version 12

In order to keep backwards compatibility with previous releases of Spread.NET, we've implemented support for a new design-time property named LegacyBehaviors, which enables a new set of special compatibility modes in run-time. These legacy modes are designed for upgrade users, and by default, control instances upgraded from earlier versions of Spread.NET will automatically use the LegacyBehaviors to maximize backwards compatibility for the upgraded instances. The default constructor for the spreadsheet control now creates the new instance using LegacyBehaviors. All, which enables all the legacy modes of operation, so all upgraded code using older versions that use that constructor will have maximum backwards compatibility.

New Legacy Modes

Four separate LegacyBehavior modes are available in Spread.NET Windows Forms 12, each of which can be enabled or disabled independently: AutoRowHeight, CalculationEngine, PropertyDefaults, and Style.

• The AutoRowHeight flag disables the new automatic row height behavior.
• The Calculation flag enables the legacy calculation mode, which makes all Date-related functions return the .NET DateTime data type as in previous releases, instead of the double data type as in Excel.
• The PropertyDefaults flag disables all of the new default values for properties to enable the new Excel-like behaviors described above. When this flag is used, all legacy properties implemented in earlier releases of Spread.NET keep their old default values, including the worksheet Protect defaulting to True and the cell Locked defaulting to False.
• The Style flag disables the new core-style model integration and new DefaultCellType rendering in the cells, and instead uses the legacy style models and GeneralCellType rendering of previous releases.

Upgrading to Spread.NET 12 is easy

1. First, change the project references to use the version 12 DLLs.
2. Then update the licenses.licx in the project to reference the new version.

All instances in your project will automatically use LegacyBehaviors.All, and your spreadsheets should operate as before. You can immediately start enhancing your projects to take advantage of the new APIs and features! Turn off LegacyBehaviors to enable the new features on a case-by-case basis.

New design-time behavior

In design time, new instances are now created using LegacyBehaviors.None, which disables all legacy modes of operation and enables all new features of the control.

Read more about the new legacy modes and backwards compatibility

New VSTO-based APIs and feature enhancements

Many new APIs based on the Visual Studio Tools for Office API are exposed in GrapeCity.Spreadsheet.dll in version 12.

This exciting new API can operate separately from the control, enabling applications to create and manipulate workbooks in memory with great efficiency using the new Factory class. This can be used to power UI-less server-side use cases on web servers or in Azure. You can also attach instances created using the Factory class to an instance of the control:

GrapeCity.Spreadsheet.IWorkbookSet workbookSet = GrapeCity.Spreadsheet.Win.Factory.CreateWorkbookSet();
fpSpread1.Attach(workbookSet.Workbooks.Add()) 

Read more about the flat-style VSTO API

Reference cells and ranges in external workbooks

You can now reference cells and ranges in external workbooks and import/export those external references to XLSX. A WorkbookSet can contain many related workbooks that reference one another and recalculate in real time, and those workbooks can be attached to live instances of the control on the same form or on different forms. External references to unloaded workbooks work as in Excel.

Learn more about external references

Apply 18 language packages for calculations

You can now apply one of 18 new language packages to the calculation engine, providing localized function names and syntax for structured references.

Learn more about the new language packages

Reference dynamic headers and footers in tables and worksheets

Reference header and footer cells in tables and worksheets with dynamic headers! When you use formulas in the header and/or footer cell, you can refer to those formulas in other cells. In addition, you can import and export your workbooks with headers and/or footers intact.

fpSpread1.ActiveSheet.Cells[7, 3].Formula = "Sheet1[[#Headers],[$C$2]]"; 
fpSpread1.ActiveSheet.Cells[8, 3].Formula = "Sheet1[[#Totals],[$C$2]]"; 
fpSpread1.ActiveSheet.Cells[9, 3].Formula = "Sheet1[[#RowHeaders],[$A$3]]";

Learn more about header and footer references

Update every formula in an app with one variable

External variables are essentially custom classes that integrate directly with the calculation engine. With external variables, you'll be able to easily pass any variable--for instance, from a web service, stock value, instrument reading, or user input--as a value, and automatically recalculate all formulas on a page when the value changes.

The code inside the class can do anything to define the value – call a web service, get some scientific instrument state for a lab book, whatever the customer needs. And it can notify the calculation engine when the value has changed, and dependent formulas need recalculated.

Here's another example, where we'd use a dashboard. Say I'm building a business dashboard that includes a spreadsheet with sparklines, a treemap, a line chart, a pie chart, and a filter.

1. The user selects a filter.
2. The filter selection gets sent to the calculation engine.
3. The engine immediately recalculates all of the charts and spreadsheets using the filter variable.
4. All controls on the dashboard update with the new data.

Learn more about external variables

Apply Office themes and create custom themes

The new API includes full support for Office Themes, which means developers and end users can easily apply *.THMX files from existing Excel files with only a line of code. You can also create custom themes, and the API supports ColorScheme, FontScheme, and EffectScheme. Corporate users can easily apply their brand standards across spreadsheets and apps.

GrapeCity.Spreadsheet.Drawing.Theme theme1 = fpSpread1.AsWorkbook().Theme;  
            theme1.ColorScheme.Load( @"C:\Program Files (x86)\Microsoft Office\Root\Document Themes 16\Theme Colors\Office 2007 - 2010.xml");  
            theme1.FontScheme.Load(@"C:\Program Files (x86)\Microsoft Office\Root\Document Themes 16\Theme Fonts\Corbel.xml");  
            theme1.EffectScheme.Load(@"C:\Program Files (x86)\Microsoft Office\Root\Document Themes 16\Theme Effects\Office 2007 - 2010.eftx");
            

Learn more about Office themes