Spread.NET 12 is now available. Major enhancements focus on:
- Improving performance
- Making Spread.NET more Excel-like
- Improving upgrade user experience
- New APIs and features
Performance improvements in data binding, design-time, and encrypted Excel files
Last version we focused on performance improvements, especially for large Excel files. The performance of version 11 XLSX import and export was vastly faster than any previous release of Spread.NET, and we've continued to tune the performance in version 12. In this release, we've focused on three core use cases for performance improvements:
- Data Binding: Internal logic improvements that move data binding support into the core spreadsheet models enhances performance, especially for common use cases involving calculations with bound data.
- Encrypted XLSX: Internal logic improvements moving encryption/decryption support into the core spreadsheet models enhances performance when importing or exporting password-protected Excel files.
- Design Time: We switched the default serialization logic for the control when saving the workbook in the form resources. This enhances design time performance, especially when using the Spread Designer tool to apply a large or complex template.
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
Spread.NET 12 features some big changes to the control's default behavior in new instances.
New default instances of Spread.NET Windows Forms 12 support:
- Text overflow into adjacent cells
- 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
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
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:
- First, change the project references to use the version 12 DLLs.
- 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.
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 new API layer powers the new feature enhancements in version 12 and provides an unparalleled level of control over every aspect of your workbooks. The IRange interface alone features 31 method overloads and 56 properties, and that’s just one of the hundreds of new types exposed in this vast new API layer.
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())
The control instance does not need to be inside a form to be used for opening, saving, or manipulating the contents of a workbook or set of workbooks. You can also use the APIs of the control to produce password-protected PDF or XLSX files. This can be important for satisfying security requirements for personal medical, financial, or other sensitive data.
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.
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. Your applications can easily apply any of these language packages to provide localized calculations, and even create custom 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]]";
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.
For instance, if a user implements formulas in the header cells that calculate dates, that formula could be referenced further down the page in a TextBox control. Developers could write some simple code in a new class to define the external variable, which would get its value from the TextBox control, and then flow through to the header cell.
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.
- The user selects a filter.
- The filter selection gets sent to the calculation engine.
- The engine immediately recalculates all of the charts and spreadsheets using the filter variable.
- All controls on the dashboard update with the new data.
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");
For a detailed listing of fixed issues, please see the Release Notes for each platform.