DevExpress VCL Spreadsheet control (Coming soon in v15.1)

As Ray said yesterday when talking about the new Rating Control, he and I are going to take it in turns describing the new features and functionality in v15.1 over the next few days. Today, it’s my turn, so welcome to the new features in the ultimate in data analysis controls: the DevExpress VCL Spreadsheet.

Data Export

The first new piece of functionality for the spreadsheet isn’t really about the control itself, but about how it is used with controls in the rest of the VCL Subscription. In essence, we’ve now incorporated it as a principal component of our data export engine. This means that we now have one and only one export functionality for grid or tabular data, and this has allowed us to significantly simplify the data export engine's code and avoid code duplication when implementing new features, both of which are vital to a quickly changing feature set.

In particular, this merging of code provides the following features:

  • Ability to export data to CSV files
  • Ensuring cell number formats in the exported XLSX file are based on data cell display formats shown in the Grid, Tree List, Pivot Grid, and Vertical Grid controls
  • Detail grid Views can now be exported to XLS, XLSX, or TXT files
  • The progress of export operations can now be tracked, and aborted if necessary
  • Custom encoding formats for strings exported to TXT or CSV files
  • Ability to export images to XLS files, including those displayed by cxImageComboBox grid items

One consequence of this work is that the ExpressSpreadSheet Suite is now required for products – especially the grids and treelists – that provide export capabilities and will be automatically installed with them. Since the new spreadsheet was written to support later compilers only, this meant we had to drop support for older compilers.

Once DevExpress VCL v15.1 is released, the ExpressSpreadSheet Suite will be added to the ExpressGridPack and ExpressQuantumPack subscription packs. We will provide a free upgrade to the new SKUs to all customers that own an active subscription to these packs.

New functionality

The v15.1 release includes the following new features for the DevExpress VCL Spreadsheet Control:

  • Ability to outline or group data in a worksheet. This gives your users a much better facility for analyzing data using the spreadsheet.
    VCL-Spreadsheet-Control-Grouping-Outline-15-1

  • A new built-in dialog for find and replace operations.

    VCL-Spreadsheet-Control-Find-Replace-Dialog-15-1
  • Support for Excel template files (XLT and XLTX files).
  • Ability to export an active worksheet to a TXT, XML, or HTML file.
  • Ability to iterate through all generated cell objects.
  • Support for the keyboard shortcuts that are available in Excel.

I’m sure you’ll agree these are some welcome new features (especially the data export enhancements). No doubt there are other features you’d like to see with the spreadsheet control, so please do let us know.

DevExpress VCL v15.1 – supported compilers

With v15.1 we are trimming the number of compilers (and therefore IDEs) that we are supporting with the DevExpress VCL subscription. As I’ve said several times over the years, supporting older compilers comes with a cost, perhaps intangible in many ways, but it’s still there and does have an effect.

In essence, if the compiler has XE in the name (and there are eight of these, times two for Delphi and C++Builder), we support it in both 32-bit and in 64-bit modes with our VCL Subscription. That’s the simple rule. Rule 2 is that RAD Studio 2010 is also supported (for now), although the new Rich Text Editor – you saw the beta announcement here 6 months ago, stay tuned for more news in another post – is not supported. And that’s it.

The biggest change then from v14.2 to v15.1 is that we are dropping support for what might be termed “the non-Unicode compilers”: Delphi 7, Delphi 2007 and C++ Builder 2007. As it happens, the new controls we’ve released from v14.1 onwards (spreadsheet, rich text editor, maps, gauges, etc) have not supported these compilers anyway. All we’ve provided over the past year for these compilers have been some enhancements plus bug fixes. Please note that Embarcadero do not provide support for them either (in fact, the last email I read was that XE3 is about to be dropped from their support and upgrade paths).

(Aside: it’s at this point that I did some research. Delphi 7 was released in August 2002, very nearly 13 years ago. I was using a Dell Inspiron 8000 at the time, with a Pentium III and 512MB RAM, running Windows XP (it cost me $3K). I wrote the Deflate and Inflate code for TurboPower’s Abbrevia on that machine. Delphi 2007 was released in March 2007, over 8 years ago. By any stretch of the imagination, these are old: just think of the progress made in computers, technology, apps, run-times, and programming languages over the last 8+ years.)

With v15.1 we wanted to do some major restructuring of the controls. The first part of this will be using the engine that’s part of the new spreadsheet control to provide way better XLS/XLSx export facilities to the grid, treelist, etc. This, candidly, would not be possible with the non-Unicode compilers. All in all, we felt the best business course of action was to consolidate our code to use the latest compilers and language features; it’s the only way we feel we can continue to provide deeper, broader, and, frankly, better features and functionality to our VCL customers.

Having said that, v14.2 is not going away just yet. Yes, it will be superseded by v15.1 in a couple of weeks, but we shall continue to provide bug fixes to it for the next year. Hence if you are an active customer you will still be able to download the minor releases until it, in turn, is retired.

My recommendation though is to move on. It seems to me that every month Embarcadero are providing discounts to upgraders. I would take advantage of them. Retire your legacy apps perhaps, or migrate them to the latest RAD Studio: there will come a day that we will start removing support for the early XE IDEs too. And, of course, if you’re using a later RAD Studio already, stay tuned for more news over the next few days on the new features in v15.1 of DevExpress VCL.

.NET PivotGrid and Dashboard: OLAP Performance Improvements

OK, let me state the blindingly obvious for a moment: the performance of aggregation or sorting operations (or both!) are key factors when you’re working with OLAP data sources. So, with v15.1, we took the time to do some research and design and have managed to increase the speed of the data engine used in the .NET Pivot Grid and Dashboard by optimizing some basic data processing – anything from constructing queries to the parsing of returned data.

Sorting is now performed on the client side. This allows you to speed up the process of visualizing data in complex scenarios such as sorting by summary and sorting in Tabular mode. Moreover, as an added bonus, using client sorting gives you the capability to perform custom sorting with the CustomServerModeSort events.

To compare the new Pivot Grid’s performance with other products in OLAP mode, we investigated two scenarios. With both scenarios, the pivot data and row areas display fields from the Adventure Works cube and are deployed on a local machine.

Pivot Grid Performance Improvements v15.1

 

Scenario PivotGrid v14.2 PivotGrid v15.1 Competitor 3 Competitor 4
#1 6 sec 3 sec 5 sec 4 sec
#2 1 min 20 sec 19 sec 35 sec 1 min 40 sec

The chart and table shown illustrate the performance improvements of the Pivot Grid in OLAP mode in v15.1 when sending queries to the Adventure Works cube. The data shows a comparison with the old v14.1 version and a couple of our (unnamed) competitors in this space. Note that the ADOMD provider was used to establish a connection to the OLAP cube.

Here is the data we used for each of the two scenarios.

Scenario 1

Data area: Internet Sales Amount, Internet Order Quantity
Row area: Postal Code, Product
Row count: ~ 100 000

Pivot Grid Performance Improvements Scenario 1

Scenario 2

Data area: Sales amount, Total Product Cost
Row area: Business Type, City, Product
Row count: ~ 600 000
Sorting: Product field is sorted by Total Product Cost

Pivot Grid Performance Improvements Scenario 2

I’m sure you’ll agree with me that these basic performance improvements will enhance the snappiness of the data analytics parts of your applications.

.NET data access engine–improvements in v15.1

Data access. Pretty mundane, wouldn’t you say? It’s … just there, doing its stuff, while you worry about how to best present the sea of data so that your users can glean the most important information from it. Well, in order to make your job even easier, we’ve made some major changes to our data access components in v15.1. Are you ready?

Query Builder improvements

It was in v14.1 that we first introduced our Data Access library to our product line. This library provides a set of tools intended to simplify the process of binding to different kinds of data sources.

Among this set of tools, I think the Query Builder reigns supreme. It's used by several wizards to provide you and (or?) your end users with a visual interface for constructing custom SQL queries. Unfortunately the Query Designer had some notable limitations from the outset (for example, it did not cover such SQL features as sorting and grouping).

To address this issue, we have made several improvements in v15.1 to the Query Builder’s functionality and usability, without forgetting increasing performance where necessary.

QueryBuilder v15.1: user experience and functionality

New features:

  • A columns list that allows you to preview the set of table columns before adding a table to a query.
  • Revised dialog interface.
  • Improved performance. Column information is now loaded on demand for each data table separately. The performance increase will be especially notable when binding to databases containing many tables with a massive number of columns (for example, when binding to Teradata – see below for information on this).
  • Support for aggregate functions.
  • Support for sorting. If the resulting data is sorted by more than one column, the order in which sorting is applied can be adjusted manually. Specified sorting settings correspond to the optional ORDER BY section of the resulting SQL string.
  • Support for grouping. Specified grouping settings correspond to the GROUP BY section of the resulting query string.

Adding the ability to sort, group and aggregate the data provides more comprehensive coverage of the data shaping functionality of SQL. When you apply such a shaping to a field, it will be marked with a corresponding icon, and this makes the whole process of shaping data more visual and intuitive.

Improved memory consumption

For 15.1 we have improved the internal structure of our SqlDataSource class, as well as the algorithms we use to obtain data from a database table. The upshot of this work is that we’ve drastically reduced its memory consumption in real-life scenarios.

To see the difference between the memory consumption of a SqlDataSource object (bound to the same database) between versions 15.1 and 14.2 , take a look at the chart below. This shows the aggregated profiling information we obtained as provided by the dotMemory profiler. This chart illustrates the dynamic aspect of memory allocation by SqlDataSource from the beginning to the end of the data loading process.

SqlDataSource: Memory Consumption improvements in v15.1

The raw profiling data was obtained from loading the same 3.5-million-row data table, consisting of 20 columns of differing data types.

As you can see, the memory usage has been reduced in v15.1 by about 65%. The improvement in memory consumption is even more pronounced when binding to data tables that contain mostly columns of numeric data types.

Teradata support

With v15.1 we have added support for Teradata (version 13 or later) to our Data Access library. Teradata is a massively parallel processing database management system that typically operates on extremely large amounts of data. Yes, you got it: we’re suddenly talking Big Data.

You can now find Teradata in the list of available data providers in the Data Source Wizard when creating a SQL data source. After that, the process of setting up a data connection will be the same as for any other SQL data provider: you need to specify the server address, the required database name, and the user credentials.  

Teradata support in the Data Source Wizard in v15.1

After a connection is set up, you can go to the next wizard page and construct an SQL query to obtain data from tables in the database. All the connection magic is hidden; you just use it as any other SQL data provider.

WinForms and WPF Charting–Performance improvements in v15.1

In all walks of programming life, performance matters. It is especially true when you are using third-party controls where you have no influence over how quickly the controls and their content renders on the screen. We agree and have always considered high performance as a vital feature of our products. The charting products are no exception: we are always on the lookout to makes our chart rendering engine faster and faster.

Throughout 2013 and 2014, we introduced data aggregation and other great features to our charts, which helped make the rendering of our ASP.NET, WinForms and WPF charts faster several times over, but for 2015 we faced a big challenge: what else can we improve? In v15.1, then, we spent some time analyzing our charting product for Windows applications, and have come up with a few new strategies to increase performance and, also, reduce memory consumption.

Here are some results from our internal tests.

WinForms app, Numeric data:

DataLoading Numeric: AddPoints

DataLoading Numeric: SeriesBinding

DataLoading Numeric: SeriesTemplate


WinForms app, DateTime data:

DataLoading DateTime: AddPoints

DataLoading DateTime: SeriesBinding

DataLoading DateTime: SeriesTemplate

I’m sure you’d agree, those are some really impressive results. If I were you, I’d download 15.1 now – it was released yesterday! – test the new version in your environment on your data, and share your results with us in the comments.

More Posts