We are happy to announce the new Spread.NET v14.1 (Service Pack 1) release! This new version has customer-requested enhancements, performance optimizations, and bug fixes for reported issues (see Release Notes), including, but not limited to, the following:
- New CalculationOnDemandMode and CellValueChanged Event
- Performance Improvements for Lookup and Match Functions
- New VALUETOTEXT and ARRAYTOTEXT Functions
- New GetFullDependents and GetFullPrecedents
- Percentage labels in Pie Charts
- Support Hide/Unhide in Tab Strip Context Menu of Spread Designer
- Pixel Scrolling by a Set Number of Pixels
New CalculationOnDemandMode and CellValueChanged Event
This enhancement gives application developers control over the calculation of on-demand behavior and recalculation of dependent cells.
Setting CalculationOnDemandMode to CalculationOnDemandMode.Off will force dependent cells to immediately recalculate–triggering the new CellValueChanged event for each of those cells. This event is used to handle changes to calculated cells caused by formulas, which is useful for tracking all workbook changes.
Performance Improvements for Lookup and Match Functions
This enhancement introduces a new API specifying CalculationEngine.CacheOptions to optimize the performance of lookup and match functions. It improves performance using many LOOKUP, HLOOKUP, VLOOKUP, XLOOKUP, MATCH, and XMATCH functions in a workbook. This enhancement is modeled after the recent Excel enhancements to lookup and match functions. To enable the new optimizations, set CalculationEngine.CacheOptions to CacheOptions.On, or CacheOptions.Aggressive, and instantly make your workbook calculate faster than before.
New VALUETOTEXT and ARRAYTOTEXT Functions
Utilize some of the new calculation functions introduced in v14.1 to match the new Excel Functions for VALUETOTEXT and ARRAYTOTEXT–currently available in beta to Microsoft Excel users in the insider's program. These functions operate as in Excel and are handy for formulas and operations on dynamic arrays.
In future releases, we will continue to introduce new Excel Functions, such as LAMBDA, as well as new enhanced sparkline functions. For full details, please see the Spread.NET 2021 Roadmap Blog.
New GetFullDependents and GetFullPrecedents
This enhancement introduces the new APIs, IRange.GetFullDependents and IRange.GetFullPrecedents, which return all dependent or precedent cells, including cross-workbook and external references. Since these emulate the VSTO API, the APIs for IRange.GetDependents and IRange.GetPrecedents will continue to operate within the worksheet to return only the references on the active worksheet.
Percentage Labels in Pie Charts
Users can now enable setting percentage labels in pie charts, similar to Excel. After creating a Pie chart and adding Data Labels for the series, you can use Format Data Labels... to specify the percentage type for the pie slice labels as follows:
Figure 1: Format Pie Chart Data Labels as Percentage
Support Hide/Unhide in Tab Strip Context Menu of Spread Designer
This enhancement enables hiding and unhiding worksheets in the Spread Designer tool. Previously, users could hide worksheets using only the PropertyGrid to set the Visible property of the worksheet. That setting would be overridden in the Spread Designer to always show the hidden worksheets. You now have the option to specify whether to show hidden sheets in the Spread Designer Preferences:
Figure 2: New Show hidden sheets option in Spread Designer Preferences
When this option is false, the new Hide and Unhide... context menu options will appear in the worksheet tab context menu:
Figure 3: New Hide/Unhide items in worksheet tab context menu
Pixel Scrolling by a Set Number of Pixels
This enhancement enables setting the vertical scroll bar to scroll in increments of a specific number of pixels using the mouse wheel. A new API VerticalScrollBarMode.PixelEnhanced enables this behavior–using the value of the property VerticalScrollBarSmallChange to determine the number of pixels to scroll.