WinForms, ASP.NET and WPF Grid Controls: New Excel Data Export Engine (Now available in v14.2)

11 November 2014

One of the most requested features across the platforms we support has been to improve the capabilities of our Microsoft Excel data export engine. With this upcoming release, the Data Grid Controls we ship for WinForms, WPF and ASP.NET will all use our new data export engine - one which retains the data shaping options you've applied within the grid control prior to the export and one which creates XLS-XLSX documents that are optimized for subsequent analysis within Microsoft Excel.

This new engine will be enabled by default and will support the following DevExpress Data Grid features:

  • Data Grouping - with the ability to collapse/expand groups within a worksheet.
  • Data Sorting and Filtering - allowing end-users to display relevant data in the desired order.
  • Totals and Group Summaries - with the ability to modify/change formulas.
  • Excel Style Format Rules
  • Data Validation for Lookup and Combo-box Columns
  • Fixed Columns

Data Grouping

The following document was exported to Excel from a Grid control with grouping and summaries enabled. As you can clearly see, the data groups applied prior to data export are retained and can be expanded/collapsed within Excel.

.NET Framework Excel Export Group and Summary

Data Sorting and Filtering

Once data is exported to Excel, sorting and filtering will be available against all columns.

.NET Framework Excel Export Sort and Filter

Lookup Values

If the contents of your DevExpress Grid control include lookups, the exported Excel document will include all lookup values within it. 

.NET Framework Excel Export Lookup Values

Format Rules

All format rules that have been applied to your DevExpress Grid are automatically exported to the Excel document.

.NET Framework Excel Export Conditional Styles

Fixed Columns

If you've applied a fixed column to your DevExpress Grid control, the exported document will retain fixed column formatting within Excel.

.NET Framework Excel Export to XLS Fixed Columns

 
Beyond the features I've described above, both the performance and memory usage of this new Excel export engine has been significantly improved. The following graph should give you a sense of the improvements we've achieved with this new engine (data-aware export vs WYSIWYG export).

.NET Framework Excel Export Performance Chart

 
On a final note - We realize that many of you have existing code investments and may not want to use this new export option. Should you need to use the previous data export engine for any reason, you can do so by setting a single property.

As always, we welcome your comments and feedback. Let us know what you think.


45 comment(s)
Mark Harby

This is exciting.

When is 14.2 due ?

11 November, 2014
Jim Foye

Well, there's really no such thing as "too much support for integration with Excel", is there?

11 November, 2014
Vijay Macha

This is Great. When is the next release

11 November, 2014
Ray Navasarkian (DevExpress)

Release will be in early December.

11 November, 2014
Hans Nieuwenhuis

I think this is amazing, especially combined with the performance improvements in time and memory!

11 November, 2014
Hedi Guizani

Great feature, we have been waiting for it since a long time,

what about columns grouping?

11 November, 2014
Andrew Macdonald

Fantastic work, I hoped this was coming when work started on your spreadsheet product.

12 November, 2014
Scott G Blood

Brilliant news, will this also be supported by the PivotGrid as one of the biggest annoyances of my users is the amount of memory required when exporting data directly from the pivotgrid.

12 November, 2014
Orhan ÖCAL

This is Great.

12 November, 2014
Paul Fuller

Very much appreciated new features and performance improvements.  Well done DX.

12 November, 2014
Crono

These looks pretty awesome, indeed! :)

Quick question though: how much control are we given over the process? For example, what if I do NOT want lookup values to appear in the Excel sheet? Do I get to decide? If so, at which level? Editor? Column? Grid?

12 November, 2014
Christopher Todd

Awesome! I look forward to seeing this!

12 November, 2014
Ray Navasarkian (DevExpress)

Scott - this is something we certainly need to implement for the PivotGrid - but it's not going to be part of this release cycle.

12 November, 2014
Jens Fudickar [DX-Squad]

Any chance to see the same functionality in VCL :-)

12 November, 2014
Ray Navasarkian (DevExpress)

Jens - of course, always the possibility.

12 November, 2014
Ray Navasarkian (DevExpress)

Crono - you are certainly correct. The level of control we provide over things such as lookup values is something we are working on as we speak. Lots of moving parts - Im sure after the first beta, we'll have a number of things to address.

12 November, 2014
Carlitos

Wow!

12 November, 2014
Norbert Kustra

This is great, but what about Excel Pivot tables support?

12 November, 2014
Scott G Blood

Ray - Appreciate you taking the time to respond directly to me on here.  One of the key assets you possess as a company is deliver fantastic .Net controls, but I do seem to feel that sometimes you do leave a certain part of your libraries by the way side to concentrate on new stuff.  I remember a while back there was a single release completely dedicated to clearing bugs and just introducing the small features users have asked for over a long period of time.  Do you have any plans to do this again as I know among the DevExpress user base this went down extremely well.

13 November, 2014
Anders Wang

still expecting for pivotgrid with pivottable in excel.

13 November, 2014
Michael Schneeberger

Great!

13 November, 2014
Robert Schlesinger

I agree with Scott. I would be happy if I will not find anything new, rather old bugs/flaws will be fixed, old request solved etc.  It is not easy to follow the speed od introductions of new controls, features etc. The F1 car might go to pitstop to take a rest, make a maintenance and later go to race again :-)

14 November, 2014
Ashish Kunhipura

Cool Feature.... When is this expected?

14 November, 2014
Christopher Todd

To expand on what Scott and Robert said, I would like to see more customer involvement through voting up existing requests/fixes. I imagine that was always the intent since we can vote now. I just think there needs to be a place where we can see items grouped by product and ordered by vote count. I'm sure there are plenty of features I would like to use that I have not reported on.

15 November, 2014
Dhaval.Shah

Will the new export engine provide faster/optimized export from ASPxGridView/ ASPxPivotGrid too?

Currently grids throw an out of memory exceptions (I understand they depend on the server memory available etc.).

15 November, 2014
Raja Lakshman

Awesome! Can't wait to try this out in our production systems. Thanks

16 November, 2014
Murray

Will the Excel data engine (import and export) be available via a programming API without requiring any UI or reporting components?

16 November, 2014
Sherry Ya

Great, my customer complained about missing these features for years.

16 November, 2014
Andrew (DevExpress)

@Murray – We have the ability to load/change/create/save xls/xlsx, as well as doc/docx/… files using API, without creating any UI control, for several years from now. We have created API for pdf couple of releases ago. We will add more file formats support in the future. For some reason, even some of our users, are not aware about it and still use third-party solutions. Here is the link to the product: www.devexpress.com/.../Document-Server

16 November, 2014
Greg Brubaker

I have some clients with lots of data, and for this we use a Large Data Grid (MVC).  Sure, we encourage filtering and reducing before exporting, but it's a shame that the mvc grid export doesn't use memory more intelligently, and throws an out of memory error for large amounts to data, requiring us to consider other options.

Will this new export still throw those same out of memory exceptions, insisting on containing everything in memory just like the current MVC grid export code?

17 November, 2014
Andrew (DevExpress)

@ Greg Brubaker – The new export engine does not store data in memory. It writes the data to a stream, by using our Excel writer API (part of our Excel API library). As a result, memory usage decreases dramatically. It's actually hard to compare these two algorithms by memory usage:  create all objects in memory and then do an export to any format (including Excel) or write into stream, row by row.

We are going to release the beta shortly. You will be able to compare for yourself. I hope you find the new capabilities of value.

17 November, 2014
Greg Brubaker

Sounds like exactly what I wanted to hear.

17 November, 2014
Michael Proctor [DX-Squad]

Great work as always DevExpress, although Programming is not my bread and butter any more, I still enjoy keeping up with what is happening out in the industry. As always the improvements I see in the suite across all the technologies is nothing short of amazing. Keep it up team.

17 November, 2014
Murray

@Andrew, thanks. I'm not sure if the non-UI Excel handling is available for me. I'm still building with 9.3.4 even though I have 10.2.3.

17 November, 2014
Steve Sharkey

Excellent - but we want this for the pivot grid too. Any idea on when this might be done? 2015?

18 November, 2014
Gerry Knight

These are great features, I will be using them from day one!

19 November, 2014
John Fedak

Are there plans to implement this style of engine on the TreelList, Pivot, and VGrid in the future?

I've had to implement custom exports for all of those because the delivered ones hang on large datasets.

See also the BestFit algorithms.

19 November, 2014
Ray Navasarkian (DevExpress)

To all of those who've asked about the same capability for other controls - we certainly will consider adding this to products like our PivotGrid and TreeList. As much as I'd like to think we are superhuman, the reality is that it does take time to add these features to our products.

19 November, 2014
AntonyC

That looks super exciting! I would also add my voice to having at least the performance improvements integrated into the PivotGrid exporting engine.

Great work DevExpress!

20 November, 2014
John Fedak

> As much as I'd like to think we are superhuman, the reality is that it does take time to add these features to our products.

This is of course understood- and there look like there is quite a lot of very good stuff in this release.  

But look at this from our perspective- the customers have next to no visibility as to what is showing up in a given release.   Tickets that get marked as "Accepted" sometimes sit for 5-10 years with no additional status or comments.

The Excel exports have been a longstanding pain point with the DevEx controls.  Both with the performance, the WYSIWYG quirks, and with the very cumbersome linkage to the printing module.  

Seeing a "Direct to Excel" module in the Grid control is an enormously welcome step forward- we're just very much hoping to see this trend continue with the Pivot/TreeList in future feature releases.  And while I'm aware of the legal issues surrounding committing to future capabilities- it would be very welcome if customers were given some clearer indication as to what was upcoming.

20 November, 2014
Ray Navasarkian (DevExpress)

@John

I understand where you are coming from and like you, I want to see this new feature in all of our controls (like yesterday). We'll start out with support for our grid controls and we'll take it from there.

20 November, 2014
Peter van den Beemt

Which property controls old or new export? And how do I get rid of those ugly solid lines in the new export?

21 November, 2014
Jathan T

Great, Is the new Excel Data Export Engine available in VCL.

21 November, 2014
James Birnie

This is very exciting news, thanks DevX.

Will this also mean the RenderBrick method (which is used now to duplicate either row to cell formating/background colours within the grid), will be come redundant, i.e. by default the grid will export with the same formatting as within the grid automatically?

24 November, 2014
Carl Langlais

Can you post the performance numbers memory and time wise by number of cell items instead of number of rows?  600K rows with 5 cols is 3M cells, which is equivalent to 100K rows with 30 cols, which is not extreme case these days.  Can we see performance numbers in 1M cell increments up to say a 600K by 30 cols which is 18M cells?

3 December, 2014

Please login or register to post comments.