VCL Spreadsheet Control (Coming soon in v14.1)

ctodx
09 June 2014

We’ve now released v14.1 of DevExpress Universal and, although we shall continue to discuss the new improvements found in there for a while, it’s now time to turn to v14.1 of the DevExpress VCL Subscription. And do we have lots of good news for you on that front.

In this first post about VCL 14.1, I’m going to reveal what the team have been working on for at least the past 9 months: a completely redesigned and rewritten spreadsheet control. We understand that in the business arena there are certain applications you’d like to write that would be enhanced by giving your users the ability to model and analyze data in a familiar spreadsheet environment. Shelling out to Excel is to be avoided, especially as you must make sure that every PC your application runs on has Microsoft Office installed. What you’d like is to somehow provide a spreadsheet panel within your application that can read and write XLS and XLSX files, that works just like Excel does, where you can format cells, insert images, calculate with the standard functions, and so on.

Well, inspired by Microsoft Office and Excel, we have totally re-engineered the VCL Spreadsheet, starting from scratch.

A Simple Invoice as a worksheet

Here is an example of the spreadsheet control displaying a simple invoice, read from an XLSX file. Or is it? I get so confused. Here it is again:

Another Simple Invoice

I can’t quite remember which image is from the VCL Spreadsheet demo in the beta and which is from Excel. Sheesh.

Seriously, these two images should show you how well we’ve been inspired by Microsoft Excel. This image (OK, the top one) shows such features as cell formatting (the lines and the grey bars in the grid to help separate the rows). Notice also the formatting of values. I’m more familiar with monetary values being right-adjusted rather than center-adjusted.

Formatting Cells in VCL

So, select the cells, right-click, then select Format Cells…

Align Cell Values in VCL

And then select right horizontal alignment.

Cell Values Aligned in VCL

Also, the VCL Spreadsheet Control supports embedding images into the worksheet:

Triangle Demo worksheet in VCL

Here we see two images: the first is the triangle figure on the right and the other the mathematical formula on the left. The spreadsheet is also displaying an error message alongside the calculated value (whose formula by the way is =0.5*F9*F10*SIN(F11*PI()/180)) because one of the sides is negative. This is done (along with the special coloring of the background) through the spreadsheet API.

Talking of special APIs, how about the ability to create custom functions? You know, because the standard ones just don’t cover your business needs. With this demo above, you can create a special function in your Delphi or C++Builder code, register it with the Spreadsheet API, and then have a formula like this:  =TRIANGLEAREA(F9,F10,F11). Obviously, should you save this worksheet, you won’t be able to open the XLSX file with Excel, but you will be able to with your application containing your special registered functions. It’s a way of protecting your business logic and IP and yet all the time using a widely-used and understood file format.

So, do you think you’ll be able to use this new control? Let me and the team know what you think.

13 comment(s)
Alexander Elagin
Alexander Elagin

Superb! Just two questions to make it all clear:

1) Can menus and dialogs be localized as usual?

2) What about ODS import/export? This is essential for Europe and Russia where OpenDocument format is accepted as a formal standard.

9 June, 2014
Elias Zurschmiede
Elias Zurschmiede

Impressive! This adds value to our product for sure :)

A important thing for me is, that the "spreadsheet engine" is thread-safe so we can use it to execute some calculations in a background-thread (load some values into some cells and read the results from other cells - like that a user can define his own complex calculation formulas in a familiar way using a spreadsheet). Or to use your simple invoice example above: it would be a nice idea if we can create such documents in a background thread.

BTW: I really like the idea to add custom functions by API.

10 June, 2014
Michael Thuma
Michael Thuma

Good ...

10 June, 2014
Teksdata Yazılım ve Otomasyon Sistemleri Ltd. Sti.
Teksdata Yazılım ve Otomasyon Sistemleri Ltd. Sti.

I'm waiting registration custom functions...

I want to use like this register method;

----------------------------------------------------

GetOrderShippingCity('OrderNo');

Regards,

FK

10 June, 2014
Alex M (DevExpress)
Alex M (DevExpress)

Hello guys,

Thank you for the feedback!

@Alexander: Yes, the captions, labels, and messages in all Spreadsheet popup menus and dialogs are initialized with resource strings. So you can localize them using the TcxLocalizer component and standard methods (as usual). As for the ODS file format support, we made a spike solution and it seemed to be viable. Most likely, we'll add this functionality in future Spreadsheet updates.

@Elias: The current implementation and calculations are not thread-safe as they use Variant types, which are not thread-safe. Great idea though!

10 June, 2014
Richard Stevens
Richard Stevens

Yes, this will be very useful for us - our clients love Excel, and the more we can integrate or share data with it the better.

10 June, 2014
Marco Cittadini
Marco Cittadini

These are greta news! We'll use this component for sure! Thanks!

10 June, 2014
Julian Bucknall (DevExpress)
Julian Bucknall (DevExpress)

Michael: Are you really the same guy who said, "They [DevExpress] are getting old."? Ah well, plus ça change...

Cheers, Julian

10 June, 2014
Stuart Clennett
Stuart Clennett

Excellent work DX. My area is healthcare, and using the spreadsheet control with data from a database means users won't have to save sensitive data to disk before manipulating it. That's a (another) big selling point for me :-D

17 June, 2014
Eric Buescher
Eric Buescher

Will the new library support formulas that need 2 cell arrays, such as =SUMPRODUCT(A1:A20,B1:B20)

I've been manually modifying your libraries to allow this function (per DX instructions) and also have complex code to add images to my spreadsheets, so I am excited about what you might have for me in the new update.

27 June, 2014
Emmanuel Lion
Emmanuel Lion

Some questions about the new Spreadsheet control:

1) does the spreadsheet control include the whole GUI ? (menus, buttons, context menus) ?

   or is it the sole spreadsheet grid, and all GUI have to be designed ?

2) is the GUI available in other languages ? German ? french ?

   or is it possible to localize the GUI ?

3) can graphics/shapes be inserted programmatically and be saved in a Excel file ?

4) Is there a list of Excel functions which are available in SpreadSheet control ?

  (or an exhaustive Excel functions list, with a yes/no column) ?

5) Can it be used in Delphi 2007 ?

3 July, 2014
David Brennan
David Brennan

Looks very impressive. I'm not sure whether we can find a use for it but we'll keep our thinking caps on and I'm sure it will be useful for others anyway.

5 July, 2014
ARNULFO A. SUAREZ Q.
ARNULFO A. SUAREZ Q.

How is the API Performance compared with the previous version?, What are the improvements in this area?

24 July, 2014

Please login or register to post comments.