WinForms & WPF Spreadsheet Control - Extended Pivot Table Support (Coming soon in v16.1)

Thinking Out Loud
31 May 2016

In our last release (v15.2), we added Pivot Table functionality to both our WinForms & WPF Spreadsheet Controls. As is always the case with a major feature like this, we received a number of requests and with our upcoming release (v16.1), we've done our best to add as much capability as possible so you could better serve your customer's needs.

The Spreadsheet's Pivot Table now supports the following: Calculated fields and items, Grouping data in the Pivot Table, and Sorting by values in a data field.

Calculated Fields and Items

If your Pivot Table requires calculations that are not available using predefined aggregate functions or the Show Values As calculation option, you can create your custom formulas by inserting calculated fields and calculated items. For instance, to create a field that calculates a 10% sales tax for each value in the Amount field, you'd use the following formula: = Amount*10%.

For those of you creating documents in code, these new features are available via the Spreadsheet's API. The code below should demonstrate how easy it is to create a calculated field in code. 


A single line of code adds the calculated item to the field...


Grouping Field Items

To help you create data subsets, the Spreadsheet control allows you to group items in the Pivot Table field. As you'll see in the animation below, this feature is extremely useful when presenting date/time and numeric values. For example, if a row field in your Pivot Table contains a long list of dates, you can group them by quarters or months so that your documents are easier to read and understand.  

The following types of grouping are available:

  • Group items in a numeric field into numeric intervals by specifying the interval length.
  • Group date/time fields by selecting one or more predefined date/ time periods to group by.
  • Group a selection of specific field items. This option can be useful to group text values in a field. Imagine that you have a State field whose items you wish to consolidate into four regions. 

And yes, when creating spreadsheets in code, the API is a piece of cake to use...


Sorting by Data Field

Finally, this release allows you to sort items in a Pivot Table field by values of a data field.

Here's some sample code when using the Spreadsheet's API...


Catching up to Excel is not easy - we have to take one big bite at a time. We'd love to read how well you think we're doing...

FREE DevExpress Xamarin UI Controls

Deliver high-impact native mobile apps with our feature-rich Data Grid, Scheduler, Chart, TabView, Editors, and utility controls. Reserve your free copy today!
Jim Foye
Jim Foye

Recently I had to prototype something that was modeled on Excel, but without using Excel. I was able to repro the Excel look and functionality very quickly with the DevExpress control. The only problem I had was that, like the grid control, it sometimes swallows exceptions (an issue I reported, but went nowhere).

I'm currently try to get another customer to replace Excel on a much larger scale with your controls.

Keep up the good work.

2 June 2016
Mark Couvaras
Mark Couvaras

More spreadsheet stuff, are you guys going to have any new controls or anything other spreadsheet improvements. How about sorting out your wpf and winforms theming engines:)

2 June 2016

Please login or register to post comments.