Spreadsheet – Early Access Preview (v19.1)

We are two months away from our next major update (v19.1). The purpose of this post is to share details and give users the opportunity to test new functionality before we wrap up our current dev cycle. If you own an active Universal or DXperience subscription, you can download the CTP build from the DevExpress Download Manager and test the features described in this blog post. Let us know how well the new features address your requirements. This will help us fine-tune our code before the launch.

If you are using a trial version and want immediate access to the features we mention in this blog post, purchase a Universal or DXperience subscription license and you will automatically receive access to the CTP version. If you are ready to upgrade to Universal or DXperience from another subscription level, email us at clientservices@devexpress.com for preferential upgrade pricing.

New Layout Engine and Performance Enhancements

The new Spreadsheet layout calculation engine helped us improve layout accuracy, increase render and scroll performance, and enhance printing capabilities.

Merged Cells

We have optimized how we store merged cell ranges. The change brings significant performance benefits for documents with a high number of merged cells. The chart below shows the time needed to delete 800 rows in a document with 14,000 merged cells:

Formula Calculation

We have significantly increased formula calculation performance. The change comes as the result of the following optimizations:

1. We have improved calculation process for time-consuming functions (SUMIFS, SUMIF, COUNTIFS, COUNTIF, AVERAGEIF, AVERAGEIFS, MAXIFS, and MINIFS).
2. When we aggregate data in a cell range, we first evaluate most popular aggregate functions for this range: SUM, MAX, MIN, COUNT, COUNTA, AVERAGE. We store these results in a separate collection and use them on demand.
3. After the calculation process stops or fails, we save the results and use them to perform consequent calculations.

We continue to optimize formula calculations and plan to implement multi-threaded operations in the upcoming v19.1 release. Below are the results for the CTP version. We ran our test on a set of complex files with multiple formulas. The chart shows the total formula calculation time for all files within the set:

Render Performance

The new layout engine noticeably increases the render speed for the visible cell area. The following chart compares “before” and “after” performance using a sample worksheet with 43 visible rows and 17 columns and a total of 25000 rows and 28 columns:

Scrolling

To improve scroll performance, we now calculate the layout beyond the visible area. This helps us avoid excessive recalculations when a user scrolls a document. We make a snapshot of the visible area, move it, and render new parts of the document that were not visible before.

The following chart shows the change in scroll performance for a worksheet from the previous example. The sheet is moved one row down:

This is not the complete list of usage scenarios where you might notice performance enhancements. If you’re working with large documents, please check the CTP version of our Spreadsheet controls and Spreadsheet Document API and let us know about your results.

New Excel 2016 Functions

Our Spreadsheet controls and Spreadsheet Document API now support the following Excel 2016 functions:

• CONCAT
• TEXTJOIN
• IFS
• SWITCH
• MAXIFS
• MINIFS

Simplified Formula Creation

You can now use arrow keys to select referenced cells in the current worksheet when you create and edit formulas.

Black and White Print Mode

The WinForms and WPF Spreadsheet controls and Spreadsheet Document API allow you to print your documents in black and white.

We Need Your Feedback!

We appreciate any thoughts you share to the comments section below, the Support Center tickets or our team email officeteam@devexpress.com. Thank you very much for your help!

Free DevExpress Products - Get Your Copy Today