WinForms Spreadsheet Control (coming in 13.1)

Whether we like it or not, the spreadsheet has become a de facto analysis tool for small datasets. Its debut in June of 1979 as a software package called VisiCalc predated my own birth by an entire month!

VisiCalc 
Source: http://commons.wikimedia.org/wiki/File:Visicalc.png

Since then there have been a number of revisions and consolidations culminating in the widely known software package we all know and love: Microsoft Excel. At DevExpress we recognize the importance spreadsheets and wanted to empower our users to take advantage of the rich facilities this tool provides without the hassle of having to learn a complex API or interact with foreign runtimes. To give you an idea of how easy it is to work with our client agnostic API I wrote a simple console application that generates an entire Excel spreadsheet in about 5 minutes:

// 1. Creating document
Workbook book = new Workbook();
var sheet = book.Worksheets.Add("mysheet");

// 2. Adding Data
Random r = new Random(DateTime.Now.Millisecond);
for (int i = 0; i < 15; i++)
{
    for (int j = 0; j < 20; j++)
    {
        sheet.Cells[i, j].Value = r.NextDouble() * 100;
        if ((i + j) % 2 == 0)
            sheet.Cells[i, j].Font.Color = Color.Red;
        else
            sheet.Cells[i, j].Font.Color = Color.Blue;
    }
}

// 3. Saving Spreadsheet
book.SaveDocument("myfile.xls");

UPDATE: Found a better and more correct way of doing the same thing in less lines of code thanks to the development team (updated above).

As is to be expected, the largest portion of the application deals with generating data. Notice how easy it is to access, edit, and style each individual cell. During our initial usability tests I mentioned to the development team that there were a number of things we should be able to do easily and without much ceremony. I then sat down and tried to implement a couple of things I thought should be easy. Within a matter of minutes I had completed each task without assistance or intervention from the development team.

One of the tasks that proved to be the easiest was using the SpreadsheetControl inside of a WinForms application. In a couple of clicks you can create a fully functioning spreadsheet application complete with formatting, layout, forumlas, etc.:

DevExpress Spreadsheet Control

I am extremely proud of the work that has been done in order to get these great tools to you, our valued customer.

As always, if there are any comments and/or questions, feel free to get a hold of me!

Seth Juarez
Email: sethj@devexpress.com
Twitter: @SethJuarez

18 comment(s)
Gustavo Marzioni
Gustavo Marzioni

Interesting for embeding it into xaf!

10 May, 2013
George Shubin
George Shubin

This is great news for me. Hopefully, now I'll be able to jettison the old Active-X control I have been using for over 12 years in one of my applications that has an embedded spreadsheet. I hope your new control will be able to read and save .XLS and/or .XLSX files.

10 May, 2013
Neal
Neal

Which license level is this going into?  I sure hope DXperience, and I will say time and time again how upset I am as a customer that I bought into the highest licensing level and was dropped to a lower level bumped by Universal and seeing controls like Dashboard go out of my licensing level infuriates me.  So as I see these new features I'm just wondering "will I have this" or will it be something I won't use as I won't be paying more money and I hold multiple licenses with more to be purchased yet.

10 May, 2013
Ray Navasarkian (DevExpress)
Ray Navasarkian (DevExpress)

Hi Neal

It will be in DXperience - so no need to worry.

10 May, 2013
Neal
Neal

Great news Ray - thank you!

10 May, 2013
Ian Pook
Ian Pook

Hi Seth.

The usual question (which I'm sure you get fed up of answering but here goes)....any plans for ASP.NET?

11 May, 2013
Jeroen Vandezande
Jeroen Vandezande

what about a wpf version?

11 May, 2013
James Zhong
James Zhong

Extremely useful !!!

Do you have any plan to provide a light-weight ASP.NET ASPxSpreadSheet control? If yes, then XtraSpreadSheet and ASPxSpreadSheet can be integrated into XAF to make better business applications.

11 May, 2013
Mark Harby
Mark Harby

This looks slick.

Did you implement the 'add all toolbars' feature that we have with RichEdit ?

You mentioned .xls, will you be supporting .xlsx from v1.0 ?

Like George, this will signal the death of an old (sometimes cantankerous) friend.

11 May, 2013
renejdm
renejdm

Another great but useless winforms component that cannot be used with an individual WinForms license.

11 May, 2013
richard morris
richard morris

I intend to use it in an ASP.NET application as a library for creating and reading spreadsheets.  The application I am thinking of is part of a Finance workflow that has to feed transactions to a General Ledger that imports data as .XLS files, and get fed data from a Payroll system that generates output files as .XLS files.  

It will replace a $1000+ library from another vendor who has an "updates" policy of giving existing customers 15% off the FULL price of the next release as long as they make full payment in the first month of it's release.

11 May, 2013
Peter Thorpe
Peter Thorpe

I believe there were some plans to use the spreadsheet engine to improve the export routines of the grid and pivot grid. Is that still the case?

I'm particularly hoping that a pivot grid can be exported natively rather than having to export the raw data and build your own.

12 May, 2013
Dolf van den Berg - Mineware
Dolf van den Berg - Mineware

Looks great!

Would you be able to copy to clipboard and print info?

12 May, 2013
Juha
Juha

Does the API allow to manipulate image cells ? How about if I want to include macros in the sheet, too ? Could one open an empty template xlsx with macros, edit that with th API and save e new sheet with macros included from template and new content blended in from programmed sources ?

13 May, 2013
Michael Hills
Michael Hills

Cant wait to play with it

Does this now mean I can edit data on a spreadsheet page directly?

I assume there is no need for any interop assemblies?

Can I use it in WPF project?

Mike

15 May, 2013
Mehul Harry (DevExpress)
Mehul Harry (DevExpress)

Richard,

Yes, that's a very interesting scenario. Could you blog about it somewhere and post the link here. It could be helpful for others too. :)

16 May, 2013
Seth Juarez (DevExpress)
Seth Juarez (DevExpress)

Friends,

  All great questions! I will try to answer the ones I can:

1. Read/Write xls/xlsx - yes

2. ASP.NET - one could use the API portion of spreadhseets there quite easily

3. ASPxSpreadSheet viewer control - this is currently not on our 2013 roadmap but is definitely something we are seriously considering

4. 'Add all the toolbars' feature - that is absolutely in there for this next release

5. 'Useless because it's WinForms' - I don't think that is accurate. The dev team spent a great deal of time working on the API side meaning that automating the process of creating and reading spreadsheets has become easier than ever (in any .net platform)

6. Using Spreadsheet to improve export routines - yes, this is something I am advocating.

7. Copy clipboard, print info - not too sure what you mean. Feel free to email me on this one to clarify

8. Image cells, macros - What kind of manipulation? No on the macros.

9. Play with spreadsheets directly - yes if you mean load a spreadsheet into the visual control and then (in code) make changes that then become visible in the control

10. Use in WPF|? - Yes, while we do not have a visual control yet for WPF you can use the API to read/write excel spreadsheets. You can even load the data from excel into a datable, present it in the grid, edit, and save back to excel.

I hope this helps! I want to be as transparent as possible. Please don't hesitate to ask any additional questions!

-Seth

16 May, 2013
Alexander Krakhotko (Xafari team)
Alexander Krakhotko (Xafari team)

2 questions:

1. maybe ASP spreadsheet viewer?

2. use Excel template ?

2. Insert rows/columns in the Excel template with the correct copy styles cell and formulas in "aggregated" cells ?

17 May, 2013

Please login or register to post comments.