.NET Spreadsheet — Multi-Threaded Calculations, Rich Text Support, and More (v19.1)

In a previous blog post, we detailed enhancements to our WinForms and WPF Spreadsheet controls and .NET Spreadsheet Document API. This blog post describes other Spreadsheet specific features shipping in our most recent release (v19.1).

Multi-Threaded Calculations

With this release, we have significantly increased formula calculation speed. Our Excel compatible calculation engine is now very accurate, efficient and fast. In addition to calculation optimizations described in our previous blog post, we have also implemented multi-threaded operations for the Spreadsheet’s chain-based calculation engine. The DevExpress Spreadsheet now calculates formulas nearly 5.5 times faster than its predecessor when multi-threaded calculations are enabled. The following chart displays the total time needed to calculate all formulas in multiple complex files.

Our .NET Spreadsheet uses the Environment.ProcessorCount property to determine the optimal number of calculation threads based on the number of available logical processors. You can use the CalculationOptions.ThreadCount property to change the default number of calculation threads.

To disable multi-threaded calculations, set the CalculationOptions.EnableMultiThreading option to false.

workbook.DocumentSettings.Calculation.EnableMultiThreading = false;

Rich Text Support within Spreadsheet Cells

Both our WinForms Spreadsheet control and .NET Spreadsheet Document API use a new layout calculation engine. This change allowed us to improve layout calculation performance, layout accuracy, rendering and scroll performance. It also allowed us to deliver enhanced printing capabilities. Another advantage of the new layout engine is its ability to display rich formatted cell text. Documents with rich text can also be printed and exported to PDF.

Note: Our WinForms Spreadsheet control does not allow you to apply rich formatting to cell text via the control’s UI. Use our Rich Text API to create rich text in code and assign it to a cell.

Rich Text in Headers and Footers

Our WinForms Spreadsheet control and .NET Spreadsheet Document API allow you to print and export (to PDF) documents with rich text and inline pictures within headers and footers (available if using our new layout calculation engine).

At present, you can apply rich formatting to header or footer text in code. The Spreadsheet supports special codes to help you add dynamic data to a header or footer and format its content. These codes are also available as constant fields and static methods of the HeaderFooterCode class.

The example below shows how to add a header to odd pages. We use the following codes in this example:

  • &B — Makes the characters bold.
  • HeaderFooterCode.FontColor method overload — Applies a theme color to the text.
  • &A — Inserts the current worksheet's name
WorksheetHeaderFooterOptions options = worksheet.HeaderFooterOptions;
// Insert the rich formatted text into the header's left section.
options.OddHeader.Left = string.Format("{0}&BDev{1}AV", HeaderFooterCode.FontColor(4, -50), 
                                                        HeaderFooterCode.FontColor(4, 10));
// Insert the sheet name into the header's right section and format it as bold.
options.OddHeader.Right = "&B&A";

Skinned Dialogs

Our WinForms Spreadsheet now supports skinned Open File and Save File dialogs.

Use the WindowsFormsSettings.UseDXDialogs option on application startup to enable skinned dialogs.

static void Main()
{
    DevExpress.XtraEditors.WindowsFormsSettings.UseDXDialogs = 
                                                DevExpress.Utils.DefaultBoolean.True;
    // ...
}

API Enhancements

Our WinForms and WPF Spreadsheet controls support new events that allow you to control drag-and-drop and fill operations for a cell range. New events include:

  • BeforeDragRange — Occurs when a user starts to drag the selected cell range. Allows you to determine operation type (drag-and-drop or fill) and cancel it, if required.
  • BeforeDropRange — Occurs when a user is about to drop the selected cell range to a new location. Allows you to obtain the source and destination range, determine operation type (CopyCells or MoveCells) and cancel it, if required.
  • AfterDropRange — Occurs after a user has dropped the selected cell range to a new location.
  • BeforeFillRange — Occurs when a user is dragging the fill handle to automatically populate cells with data. Allows you to obtain the source and resulting ranges, determine operation type (CopyCells or FillSeries) and cancel it, if required.
  • AfterFillRange — Occurs after a cell range has been populated with data based on values in the source range.

The following example demonstrates how you can use the BeforeDropRange event to prevent users from moving a table or its data to a new location.

spreadsheetControl.BeforeDropRange += (s, e) => {
    if (e.OperationType == DragDropOperationType.MoveCells && 
        table.Range.IsIntersecting(e.SourceRange))
    e.Cancel = true;
};

What Do You Think?

As always, we are interested in your feedback. Please feel free to leave comments below or open Support Center ticket. In addition, we would appreciate your responses to this quick survey:

4 comment(s)
Jean-Francois

You are promoting skinned dialogs and you seem to have invested a lot of effort on it.  I understand that this may be important for some of your customers (?) but we found that using the standard Windows dialogs is superior in pretty much all cases (e.g. the native context menu appears much faster).

In our case we are much more looking for additional functionality to reach more parity with the Office apps because this is really what our customers are looking for and requesting!

For instance here please consider providing an edit interface for Rich text formatting in cell without having to rely on mapping a RTF control on top of it manually.   This would be an awesome feature n many other places (e.g. diagramming).

31 May, 2019
Ray Navasarkian (DevExpress)

@Jean-Francois: I appreciate your comments. We certainly want to do everything possible to address the needs of all our users. This year, we surveyed our customers and asked everyone to help us prioritize output. 85% of our customers told us they wanted Skinned Open/Save file dialogs...and this is ultimately the reason we introduced this capability.

You mentioned that you are looking for additional functionality to reach more parity with Office apps. Building Office-inspired components is not a trivial undertaking but we definitely want to know where you'd like us to focus next. At present, our focus is on "change tracking" for our Rich Text Editor but we will be happy to discuss things with you. Please take a moment to submit a support ticket at your earliest convenience and we can continue the discussion online...

3 June, 2019
.M.K.

Currently, RichText support and Skinned Dialogs are Forms only features. Are there plans to make this available to WPF as well (and when)?

11 June, 2019
Damon (DevExpress)

@.M.K. - We do not have immediate plans to implement RichText support in the WPF version of the Spreadsheet Control. As for Skinned Dialogs, our team will consider their use for WPF as well, but it is not clear yet in which version we will do this.

12 June, 2019

Please login or register to post comments.