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

In a previous blog post, we detailed enhancements to our WinForms Spreadsheet 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:

Free DevExpress Products - Get Your Copy Today