What's New in Spread.NET v15

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:

1. .NET 6.0 Support and Nuget package
2. 17 Sparkline Visual Function
3. LAMBDA Function Enables Creating Custom Functions Using Formulas
4. 7 LAMBDA Helper Functions and Related Enhancements
5. Freeform: Shape, Freeform: Scribble, and Camera Shape Enhancements
6. Table Binding Enhancements
7. Rich Text: Import XLSX With Cells Containing Multiple Fonts, Text Styles, Colors, Superscript, Subscript, and more
8. Enhanced Border Rendering and Printing Support
9. Enhanced Conditional Format Dialog
10. Insert Cut/Copied Cells
11. Enhanced Excel-Compatible Keyboard Shortcuts and Actions
12. Export ComboBoxCellType as Data Validation
13. Display Image From File Path Using ImageCellType
14. 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:

1. IRange.AutoFill – auto-fills the range to the specified target range
2. IRange.HasRichDataType – returns true if the range contains a rich cell data type object
3. IRange.Resize – resizes the range
4. IRange.Width – returns the width of the range in points
5. IRange.Top – returns the top coordinate of the range relative to the top of the worksheet in points
6. IRange.Height – returns the height of the range in points
7. IRange.Left – returns the left coordinate of the range relative to the left of the worksheet in points
8. IRange.UseStandardWidth – sets or returns whether the columns in the range use standard width
9. IRange.UseStandardHeight – sets or returns whether the rows in the range ise standard height
10. IRange.ShowCard – shows the data card for the rich cell data type object in the range
11. IRange.Show – scrolls the worksheet to show the specified cell (IRange must be one cell)
12. IRange.Next – emulates the TAB key to navigate to the next editable cell
13. IRange.Previous – emulates the SHIFT + TAB key to navigate to the previous editable cell
14. IRange.FillDown – fills the range down using the values in the top cells
15. IRange.FillLeft – fills the range left using the values in the right cells
16. IRange.FillRight – fills the range right using the values in the left cells
17. IRange.FillUp – fills the range up using the values in the bottom cells
18. IRange.Dirty – designates the cells in the range to be recalculated in the next calculation cycle
19. IRange.BorderAround – sets the specified borders around the cells in the range (outline border)
20. IRange.Characters – gets the Characters for applying formatting to part of the cell value
21. IRange.SpecialCells – gets the cells in the range of the specified type
22. IWorksheet.UsedRange – returns the range in the worksheet containing values
23. IWorksheet.StandardWidth – sets or returns the standard column width in the worksheet
24. IWorksheet.StandardHeight – sets or returns the standard row height in the worksheet
25. IWorksheet.Next – emulates the TAB key to navigate to the next editable cell
26. IWorksheet.Previous – emulates the SHIFT + TAB key to navigate to the previous editable cell

Discover Spread.NET v15!