We are pleased to announce the release of GrapeCity Spread.NET v15! This release includes new platform support for .NET 6 as well as many new features and enhancements. This blog will cover the following sections:
- .NET 6.0 Support and Nuget package
- 17 Sparkline Visual Function
- LAMBDA Function Enables Creating Custom Functions Using Formulas
- 7 LAMBDA Helper Functions and Related Enhancements
- Freeform: Shape, Freeform: Scribble, and Camera Shape Enhancements
- Table Binding Enhancements
- Rich Text: Import XLSX With Cells Containing Multiple Fonts, Text Styles, Colors, Superscript, Subscript, and more
- Enhanced Border Rendering and Printing Support
- Enhanced Conditional Format Dialog
- Insert Cut/Copied Cells
- Enhanced Excel-Compatible Keyboard Shortcuts and Actions
- Export ComboBoxCellType as Data Validation
- Display Image From File Path Using ImageCellType
- New VSTO-like APIs
.NET 6 Support and Nuget Package
Figure 1 Adding GrapeCity.Spread.WinForms v15 using NuGet Package Manager
Using NuGet Package Manager in Microsoft Visual Studio, you can easily add the Spread.NET Windows Forms controls in your projects targeting .NET 6, .NET 5, .NET Core 3.1, and .NET 4.5.2.
17 Sparkline Visual Functions
Figure 2 Cascade Sparkline Visual Function Example
Enhanced Sparkline Visual Functions create compelling data visualizations directly in cells using formulas or the Spread Designer tool. These 17 new sparkline functions provide a wide variety of built-in data visualizations for various data types and use cases.
The new sparkline types are Area, Box Plot, Bullet, Cascade, Gauge KPI, HBar, Histogram, Image, Month Calendar, Pareto, Pie, Scatter, Spread, Stacked, Vari, VBar, and Year Calendar.
For a full breakdown and overview of these new Sparkline Visual Functions, please see How to Use Enhanced Sparkline Functions in a .NET Excel-Like Spreadsheet.
LAMBDA Function Enables Creating Custom Functions Using Formulas
Figure 3 LAMBDA Function example
The new LAMBDA Function in Spread.NET v15 lets users create new custom functions using custom names and formulas. These new functions operate like the new Dynamic Array Functions, and return array results spill to adjacent cells.
You can copy/paste the formulas for these functions to share them and define them in any workbook, then use them in formulas just like the built-in functions.
For more details and examples of practical LAMBDA Functions in action, explore .NET Spreadsheet Tips and Tricks: LAMBDA Function.
7 LAMBDA Helper Functions and Related Enhancements
Figure 4 LAMBDA Helper Functions example
To help create advanced LAMBDA Functions for specific use cases, seven new LAMBDA Helper Functions are supported in Spread.NET v15: BYCOL, BYROW, ISOMITTED, MAKEARRAY, MAP, REDUCE, and SCAN.
These new helper functions allow users to create advanced LAMBDA Functions that would be difficult or impossible to create using formula syntax.
For more details and examples using these new LAMBDA Helper Functions, check out .NET Spreadsheet Tips and Tricks: LAMBDA Function.
Some other related enhancements include:
- Keep Whitespace In Formulas: With longer and more complex formula expressions required for creating LAMBDA Functions, it is helpful to have whitespace preserved in formulas to make them more readable and clear to understand.
- Name Manager Dialog Enhancements: The Name Manager Dialog allows resizing and expanding the text box to easily view and edit complex multi-line formulas required for LAMBDA Functions.
- Names Support Double-Byte Characters: Custom Names now support using double-byte characters to define the name.
Freeform: Shape, Freeform: Scribble, and Camera Shape Enhancements
Figure 5 Freeform: Shape and Freeform: Scribble in Spread Designer
The new Freeform: Shape and Freeform: Scribble tools allow users to create open or close custom shapes quickly and easily, just like in Excel. The Camera Shape in Spread.NET v15 is enhanced to support the new Enhanced Shape Engine.
It supports using formulas inside custom names to dynamically switch the Camera Shape range using a cell value or a Cell Type such as Check Box or Combo Box.
Table Binding Enhancements
Figure 6 Table Binding Enhancement Example
Table Binding Enhancements in Spread.NET v15 provides run-time support for table data binding directly to any .NET data source.
ITable supports AutoGenerateColumns and DataSource properties, and ITableColumn supports DataField and CellType properties. This new support provides two-way direct binding to the data source values, with changes immediately updated.
Rich Text: Import XLSX With Cells Containing Multiple Fonts, Text Styles, Colors, Superscript, Subscript, and More
Figure 7 Rich Text Import with subscripts, superscripts, and more
Rich Text Import in Spread.NET v15 is easily enabled using FpSpread.Features.RichText allows imports of XLSX documents containing rich text formatting in cells, including multiple fonts, text styles, colors, superscripts, and subscripts. T
his customer-requested feature allows loading and viewing your Excel spreadsheets with more fidelity than ever before.
Enhanced Border Rendering and Printing Support
Figure 8 Enhanced Border Rendering of imported XLSX
Enhanced Border Rendering in Spread.NET v15 is easily enabled using FpSpread.BorderCollapse = BorderCollapse.Enhanced to allow the import of XLSX files containing complex merged cells with various types and widths of cell borders exactly as in Excel.
Printing Support in Spread.NET v15 is also enhanced when using the default flat style (e.g., LegacyBehaviors.Style is not used) to support printing of all cells as they appear, including gradient fills, accounting formats, and more.
Enhanced Conditional Format Dialog
Figure 9 Enhanced Conditional Format dialog
The Enhanced Conditional Format dialog in Spread.NET v15 supports the Duplicate Rule button and resizing the dialog, making it easier to manage the conditional formats in your worksheets. This dialog is displayed in run-time using the API ConditionalFormattingUtils.ShowConditionalFormattingManagerForm.
Insert Cut/Copied Cells
Figure 10 Insert Cut/Copied Cells Enhancement
The new Insert Cut/Copied Cells Enhancement in Spread.NET v15 can be enabled using FpSpread.Features.RichClipboard and is part of a broad set of enhancements to copy/paste behaviors to make Spread behavior more Excel-like.
This includes displaying the animated dashed-line border around the copied or cut range and new context menu items for Insert Copied Cells or Insert Cut Cells when a range has been copied or cut and enhanced Insert dialog for inserting rows or columns.
Enhanced Excel-Compatible Keyboard Shortcuts and Actions
Figure 11 Enhanced Excel Compatible Keyboard Shortcuts
New enhanced Excel Compatible Keyboard Shortcuts in Spread.NET v15 can be enabled using FpSpread.Features.ExcelCompatibleKeyboardShortcuts (in addition to the shortcuts added in v14) including CTRL + A to select table cells, table ranges, worksheets, or adjacent non-empty cells.
TAB and SHIFT + TAB are used to navigate table cells or adjacent cells. FpSpread.Features.AutoExpandTable enables new behaviors when editing cells below a table without a totals row or to the right of a table to automatically expand the table, including the new rows or columns.FpSpread.Features.
AutoCreateCalculatedTableColumns enable new behavior when typing formulas in table cells, automatically applying the formula to the other table column cells. Each of these new actions is handled in a separate action inserted automatically in the undo stack. The user can undo the automatic action separately from the cell edit action that caused it.
Export ComboBoxCellType as Data Validation
Figure 12 Export ComboBoxCellType as Data Validation
A customer-requested enhancement in Spread.NET v15 allows exporting ComboBoxCellType as a Data Validation List to XLSX files, using ExcelSaveFlags.ComboAsDataValidation. Import of Data Validation Lists in XLSX files as ComboBoxCellType is also supported using ExcelOpenFlags.DataValidationAsCombo.
Display Image From File Path Using ImageCellType
Figure 13 ImageCellType using an image file path
A customer-requested enhancement to ImageCellType in Spread.NET v15 allows the image to be loaded automatically by setting the value in the cell to the image's file path. Duplicate images are automatically cached and reused across cells to optimize use cases with many images.
26 New VSTO-like APIs
Spread.NET v15 features 26 New VSTO APIs for IRange and IWorksheet:
- IRange.AutoFill – auto-fills the range to the specified target range
- IRange.HasRichDataType – returns true if the range contains a rich cell data type object
- IRange.Resize – resizes the range
- IRange.Width – returns the width of the range in points
- IRange.Top – returns the top coordinate of the range relative to the top of the worksheet in points
- IRange.Height – returns the height of the range in points
- IRange.Left – returns the left coordinate of the range relative to the left of the worksheet in points
- IRange.UseStandardWidth – sets or returns whether the columns in the range use standard width
- IRange.UseStandardHeight – sets or returns whether the rows in the range ise standard height
- IRange.ShowCard – shows the data card for the rich cell data type object in the range
- IRange.Show – scrolls the worksheet to show the specified cell (IRange must be one cell)
- IRange.Next – emulates the TAB key to navigate to the next editable cell
- IRange.Previous – emulates the SHIFT + TAB key to navigate to the previous editable cell
- IRange.FillDown – fills the range down using the values in the top cells
- IRange.FillLeft – fills the range left using the values in the right cells
- IRange.FillRight – fills the range right using the values in the left cells
- IRange.FillUp – fills the range up using the values in the bottom cells
- IRange.Dirty – designates the cells in the range to be recalculated in the next calculation cycle
- IRange.BorderAround – sets the specified borders around the cells in the range (outline border)
- IRange.Characters – gets the Characters for applying formatting to part of the cell value
- IRange.SpecialCells – gets the cells in the range of the specified type
- IWorksheet.UsedRange – returns the range in the worksheet containing values
- IWorksheet.StandardWidth – sets or returns the standard column width in the worksheet
- IWorksheet.StandardHeight – sets or returns the standard row height in the worksheet
- IWorksheet.Next – emulates the TAB key to navigate to the next editable cell
- IWorksheet.Previous – emulates the SHIFT + TAB key to navigate to the previous editable cell
Related Links
Try Our .NET Spreadsheet Components
Spread.NET Resources
Sean Lawyer