Spread.NET v16 is Released!

We are pleased to announce the release of GrapeCity Spread.NET v16!

This release includes many new features and enhancements:

1. Rich Text Editing
2. 19 New Built-in Calc Functions
3. Worksheet Protection Options Dialog
4. Forecast Sheet Dialog
5. Goal Seek Dialog
6. DataTable Dialog
7. Text to Columns Dialog
8. Remove Duplicates Dialog
9. Miscellaneous Enhancements

Download 30-day Free Trial for Spread.NET v16 Today!

Rich Text Editing

Spread.NET v16 introduces new support for Rich Text Editing directly in cells, so you can select text in the cell and apply changes using the Format Cells Dialog.

This new support requires the modern flat-style model (so you cannot use FpSpread.LegacyBehaviors.Style) and explicit enabling with FpSpread.Features.RichTextEdit.

When this feature is enabled, you can implement code that applies changes to the selected text in the cell.

You can also show the built-in Format Cells Dialog to apply font settings to the selected text in the cell.

You can enable Excel-compatible keyboard shortcuts using FpSpread.Features.ExcelCompatibleKeyboardShortcuts.

Then the user can invoke the Format Cells Dialog in run-time to change font settings for the selected text in the cell. This dialog can be invoked in several ways – if the Excel-compatible keyboard shortcuts are enabled, as shown above, then Ctrl+1 will invoke it, and it can also be invoked using the context menu on the cell editor (right-click in edit mode and select Format Cells…):

Figure 1 Format Cells Font Dialog for selected text

Using Spread Designer, you can also apply Font changes to the selected text in the cell using the ribbon controls in the Home tab - Font section:

Figure 2 Spread Designer Home tab - Font section

You can also click the arrow in the bottom-right of the Home tab - Font section to show the Format Cells Dialog.

Several other really nice side benefits come with enabling this new functionality:

1. Intellisense and Text Tips with formula function descriptions and arguments are supported now, with full support for in-cell formula editing, reference range highlighting, and coloring, without requiring FormulaTextBox to be attached to FpSpread as in previous releases.
2. AutoComplete Drop-down List is supported (Alt+Up), which shows a filtered list of the allowed data validation list values as the user starts typing in the cell (if a data validation list is set for that cell).
3. Pick From Drop-down List is supported (Alt+Down), which shows a list of unique column values from which the user can select to avoid any potential typos.
4. Insert Function is supported (Shift+F3), which shows the Insert Function Dialog or the Function Arguments dialog, depending on the context (e.g., caret position in the cell formula).
5. Insert Name is supported (F3), which shows the Paste Name Dialog listing all the defined names available for the worksheet.

These enhancements significantly improve the end-user experience while editing cell values or formulas.

19 New Built-In Calc Functions

A major focus for Spread.NET v16 is supporting many new built-in Formula Functions for Calculations. This release introduces support for many new Excel functions that are already available now in the Insiders builds and coming soon in the official release versions of Excel for text and dynamic array manipulation:

1. TEXTBEFORE - Returns text that’s before delimiting characters.
2. TEXTAFTER - Returns text that’s after delimiting characters.
3. TEXTSPLIT - Splits text into rows or columns using delimiters.
4. VSTACK - Stacks arrays vertically.
5. HSTACK- Stacks arrays horizontally.
6. TOROW - Returns the array as one row.
7. TOCOL - Returns the array as one column.
8. WRAPROWS - Wraps a row array into a 2D array.
9. WRAPCOLS - Wraps a column array into a 2D array.
10. TAKE - Returns rows or columns from array start or end.
11. DROP - Drops rows or columns from array start or end.
12. CHOOSEROWS - Returns the specified rows from an array.
13. CHOOSECOLS - Returns the specified columns from an array.
14. EXPAND - Expands an array to the specified dimensions.

Spread.NET v16 also adds support for the new Excel IMAGE function:

15. IMAGE - Inserts an image in the cell from a source location.

Also new in Spread.NET v16 are the advanced forecasting functions using Exponential Triple Smoothing (ETS):

16. FORECAST.ETS - Returns a forecast of the timeline values to the target date.
17. FORECAST.ETS.SEASONALITY - Returns the length of the detected seasonal pattern in the timeline values.
18. FORECAST.ETS.CONFINT - Returns a confidence interval for the forecast value at the target date.
19. FORECAST.ETS.STAT - Returns a statistical value as a result of time series forecasting.

Worksheet Protection Options Dialog

Spread.NET v16 now fully enforces all supported worksheet protection options in the user interface and has a new Protect Sheet Dialog for specifying the worksheet protection options and optional password:

Figure 3 Protect Sheet Dialog

This dialog is available in the worksheet tab area context menu when the TabStrip is enabled for editing.

Then the Protect Sheet… menu item is available on right-click in a worksheet tab:

Figure 4 Protect Sheet… context menu item

Forecast Sheet

Forecast Sheet automates the creation of a forecast worksheet to forecast future values using Exponential Triple Smoothing (ETS), which can account for seasonality and include optional forecast statistics. To create a forecast worksheet, you must first select the data to forecast, which must be selected in the active worksheet and in parallel ranges (e.g., the date value in the Timeline Range and the associated value in the Values Range must be in the same row or column, so the ranges must be either parallel row ranges of 1 column or parallel column ranges or 1 row). Then you can invoke the Forecast Sheet Dialog using simple code with the new BuiltInDialogs class:

Figure 5 Forecast Sheet Dialog

You can select the Forecast End date (if necessary) and accept the default options to generate the forecast worksheet by selecting Create, or you can open the Options and optimize the forecast with a specific seasonality period, check the box to generate optional forecast statistics, or modify other available parameters such as Confidence Interval, Timeline Range, or Values Range:

Figure 6 Forecast Sheet Dialog Options

The dialog shows a preview of the forecast chart, and you can modify the parameters of the forecast until you’re ready, then select Create to generate the forecast worksheet.

Goal Seek Dialog

Goal Seek is an analytic feature for finding a value that calculates a particular result. To use this feature, you must have a cell formula that depends on another cell value. The cell with the formula is called the Set Cell, and the other cell is called the Changing Cell because that is the cell whose value will be changed during the Goal Seek operation to find the optimal value that calculates to the particular result, which is called the To Value.

Using Spread.NET v16 and the new BuiltInDialogs class, you can easily show the Goal Seek Dialog:

Figure 7 Goal Seek Dialog

Goal Seek can also be performed in code without using the dialog with the IRange.GoalSeek Method:

This is useful in cases where you want to automate the Goal Seek operation, for example, in a button click event.

DataTable Dialog

Data Table is an analytic feature for generating a table of calculated values for a cell formula that uses one or two other cell values as inputs to the calculation (e.g., dependent cells of the formula). A Data Table can have one or two dimensions, depending on how many inputs to the calculation are in the cell formula. For example, when calculating the loan payment using the PMT function, there are three inputs (parameters) to the calculation:

1. Interest Rate
2. Term in Months
3. Loan Amount

From those inputs, you can calculate the loan payment – here is an example Loan Payment Calculator:

Figure 8 Loan Payment Calculator

The formula in cell B4 calculates the loan payment for the given Loan Amount, Term in Months, and Interest Rate using the PMT function. Using a Data Table, you can repeat this calculation and vary one or two of the inputs in each cell to generate a table of calculated results. Following the above example, suppose you are considering various properties for purchase that range in price between $80,000 and $120,000. You are also considering various banks offering interest rates. You are also considering various banks offering interest rates between 7% and 11%. You can use a Data Table to repeat the above calculation.

First, you must prepare the cells where you want to generate the Data Table, and create the row and column headings that contain the values to use for the formula calculation. For example, here is a range prepared for generating a new Data Table for the Loan Payment Calculator above for this example:

Figure 9 Loan Payment Data Table Prepared

The top-left cell in the range (E2 in this example) should contain the formula for generating the table (or a reference to the cell containing that formula, as in this example, will also work). The row headings in the range (E3:E7 in this example) must be initialized with the values that will vary in the columns of the range, and the column headings in the range (F2:J2 in this example) must be initialized with the values that will vary in the rows of the range. Then you can generate the Data Table by selecting the entire range and invoking the Data Table Dialog. Using the Spread Designer, you can invoke the Data Table Dialog using the ribbon bar by opening the Data tab and opening the drop-down for What-If Analysis in the Forecast group, and selecting Data Table…:

Figure 10 Data Table in Spread Designer Data Tab

Or, in code, the Data Table dialog can be invoked using the new BuiltInDialogs class.

This will display the Data Table Dialog, where you can select the Row Input Cell and Column Input Cell for the calculation. Note that the Row Input Cell is the cell that contains the value that varies in the table rows (B1 with Loan Amount), which is the column header value in each column. The Column Input Cell is the cell that contains the value that varies in the table columns (B3 with Interest Rate), which is the row header value in each row:

Figure 11 Data Table Dialog

The generated Data Table will show the TABLE array function for the cell values:

Figure 12 Generated Data Table Example with TABLE formula

Text to Columns Dialog

Figure 13 Text-To-Columns Dialog Step 1

Text-To-Columns convert the delimited text in cells into text split across columns in a target range. In this example, the text is delimited with Comma delimiters:

Figure 14 Text-To-Columns Dialog Step 2

In this example, the fifth column contains date values – in Step 3, you can format values in a column using a specified format:

Figure 15 Text-To-Columns Step 3

Text-To-Columns Dialog can be shown using BuiltInDialogs with simple code.

To automate Text-To-Columns operations without using the dialog, you can use the TextToColumns Method in code.

Remove Duplicates Dialog

Remove Duplicates is a useful new tool available in the Spread Designer ribbon in the Data tab in the Data Tools group:

Figure 16 Remove Duplicates Tool in Spread Designer

You can select a range of cells; this tool will check for duplicate rows in the range and remove any duplicate rows. A duplicate row must match each column value in the row.

To show the dialog in code, you can use the BuiltInDialogs.RemoveDuplicates Method.

If the selection has no values in it, then BuiltInDialogs.RemoveDuplicates will return null (Nothing in VB), and no dialog will show. If there are other values in the cells adjacent to the selected cells, then a dialog will show to confirm whether or not to expand the range to include those adjacent values:

Figure 17 Remove Duplicates Warning Dialog

You can also remove duplicates in code to automate the removal of duplicates using the new IRange.RemoveDuplicates Method.

Note that the Remove Duplicates Warning dialog does not show when using the RemoveDuplicates Method.

Miscellaneous Enhancements

Other important miscellaneous enhancements include:

Center Across Selection

Instead of using Merge and Center, you can select the range and use Center Across Selection to get the same appearance without the restrictions on selecting cells that merged cells can cause as an unwanted side-effect. Before using Center Across Selection, you must first set two properties:

1. AllowCellOverflow: this property must be true for cell text to overflow into adjacent cells.
2. BorderCollapse: this property must be set to Enhanced to support Center Across Selection.

This setting is available in the Format Cells Dialog on the Alignment tab under Horizontal:

Figure 18 Center Across Selection

You can also apply Center Across Selection in the code using the HorizontalAlignment.CenterAcrossSelection enumeration option from GrapeCity.Spreadsheet namespace. 

Multiple Range Actions

To enhance and optimize the use of cell formats, you can select multiple cells or ranges (after enabling it with OperationMode and SelectionPolicy) and apply a cell format to all of the selected cells at once using the Format Cells Dialog:

Figure 19 Apply Cell Format to Multiple Selections

You can also Enable Multi-Range Selection for Copy/Paste:

Figure 20 Multi-Range Selection and Copy/Paste

Threaded Comments

Threaded Comments in imported workbooks can be edited directly or Added in Code:

Figure 21 Threaded Comments UI

You can add threaded comments by using the IRange.AddCommentThreaded method.

Comment Gradient Fill

Cell comments can have Gradient Fill Effects applied:

Figure 22 Comment Gradient Fill Effect

To add the gradient effects, use the Fill property of the IShapeBase interface, which gets an object of the IFillFormat interface for a specified shape that contains the fill formatting properties for the shape.

Aggregation Format with Flat Style

Set Column Footer Format when using the flat-style rendering mode (e.g., not using FpSpread.LegacyBehaviors.Style):

Figure 23 Column Footer Aggregation Format

Picture Adjustments

You can apply Picture Adjustments to get various effects affecting brightness, sharpness, contrast, saturation, and more; here are some examples showing the Film Grain and Blur effects:

You can apply these effects in code using the PictureEffectType enumeration.

Frozen Line Width

You can Set Frozen Line Width using FrozenLineThickness:

Figure 27 Set Frozen Line Width

Download 30-day Free Trial for Spread.NET v16 Today!