.NET Spreadsheet Control (Coming soon in v14.1)

ctodx
23 May 2014

Back in my younger days, I was a Lotus 1-2-3 buff. There was nothing I couldn’t do with the ‘/’ menu system and 1-2-3’s formulas. I’d take raw spreadsheets that the bank’s traders had made to price options and other derivatives and convert them to better, more secure, less error-prone DOS apps. So last year I was heartened to find that we were releasing spreadsheet controls for our major platforms: WinForms, ASP.NET (as a preview), and WPF. It’s now been a while and it’s time to see what our spreadsheet dev team have been up to for DevExpress Universal 14.1.

WinForms and WPF

The spreadsheet controls we built for WinForms and WPF (for Windows applications) were the most full-featured of the spreadsheet controls we released last year, but that doesn’t mean that there wasn’t room for some expansion.

Password Protection for Worksheet and Workbook Elements

From 14.1, you can protect worksheet data or workbook elements to make them “read-only” so that you prevent your users from modifying them. As part of this data protection feature, you can specify a password that users must enter to edit protected worksheets, to change the structure of a workbook, and even to set the size and position of worksheet windows. In addition, you can extend permissions to users in order to edit certain ranges in a protected worksheet.

Here’s the dialog to set password protection in WinForms:

win-Spreadsheet-Password-Protection-14-1

Chart Object Model and API

The Charting and DrawingML object models are now available via a public API. You can create all chart types in code, specify the layout and position of chart elements, and customize series markers and labels.

Formula Builder

With this release, we've added Insert Function and Function Arguments dialogs in order to assist end-users when creating formulas containing functions.

Text Search

No more manual scanning of data in your workbook: the spreadsheet control now allows you to find and replace text in a range, worksheet, or in the entire document.

Name Manager

Names make your formulas much easier to find, understand, and maintain. The Name Manager allows you to add, edit or delete names in a workbook.

New Worksheet Functions

Always the fun part... What's new with the available functions? Student’s t-test anyone?

  • Real-Time Data Function - retrieves real-time data (RTD) from a COM server. If the server continually updates its data, RTD formulas are automatically recalculated to reflect changes.
  • Statistical Functions - LINEST, CHISQ.DIST, CHISQ.DIST.RT, CHISQ.INV, CHISQ.INV.RT, CHISQ.TEST, GAMMA.INV, T.TEST.
  • Compatibility Functions - CHIDIST, CHIINV, CHITEST, GAMMAINV, TTEST.

Miscellaneous Improvements

Among these major improvements, there are several minor, yet still important, improvements. 

  • Export to HTML.
  • A Formula Engine that provides access to formula elements.            
  • Ability to programmatically sort data in worksheets.
  • Ability to manually paint a line that separates frozen panes on a worksheet.
  • Ability to specify the width of row headers and height of column headers, in pixels.

ASP.NET WebForms

We released the DevExpress ASP.NET Spreadsheet control as a community preview back in December, 2013. The feedback from our customers has been very beneficial to us in targeting issues and improvements; so with this update, we are officially releasing the product. It hasn’t just been bug fixes over the past half-year though, we have added the following new features for the official release:

  • Built-in file management menu items (create, open and save).
  • Ability to use an external ribbon control.
  • Built-in mail merge capabilities.
  • Touch and Theme support.
  • Support for hidden columns and hidden rows.
  • Support for the standard ASP.NET localization capabilites.
  • Dialog window design improvements.
  • Optional grid lines.
  • Additional client-side events and API.

ASP.NET MVC

Alongside the official release of the DevExpress ASP.NET WebForms spreadsheet control comes the new MVC spreadsheet extension. Everything you liked about the WebForms version is now available with ASP.NET MVC. Specifically it ships with the following features:

  • Auto-Generated UI
  • Automated Formula Calculation Engine
  • Built-in Spreadsheet Functions
  • Cell References and Formatting
  • Cell and Cell Ranges
  • Rows and Columns
  • Charting, Pictures
  • Worksheet Management
  • Built-in Menus (Create, Open and Save)
  • Mail Merge
  • Touch and Theme Support

Let me know what you think!

    6 comment(s)
    James Zhong
    James Zhong

    Good! Hope this SpreadSheet control can be integrated into XAF (out of the box) soon.

    25 May, 2014
    Dennis (DevExpress Support)
    Dennis (DevExpress Support)

    @James: If your clients request this or any other control in your XAF application right away, implement a custom PropertyEditor, ListEditor (XAF wrappers around controls to smoothly plug them into the existing XAF infrastructure) or even a custom form. These are standard and recommended integration practices for the framework and you do not need to wait for anyone to use them. In any event, you can add the https://www.devexpress.com/Support/Center/Question/Details/Q503665 ticket to your Support Center favorites list.

    26 May, 2014
    Tobias Schaller
    Tobias Schaller

    Glad to hear Spreadsheet control is enhanced in the Web version, too !

    Please consider porting your Rich Text Editor to ASP.NET. In my opinion this would be a real new killer-feature !!!

    26 May, 2014
    Konstantin B (DevExpress)
    Konstantin B (DevExpress)

    A generic answer to anyone who has an XAF application and wants to use a WinForms or ASP.NET control that is not supported in XAF by default is Using a Control that is not Integrated by Default 

    27 May, 2014
    John Whattam 1
    John Whattam 1

    Good to see the improvements to formula entry and searching is most welcomed. Little disappointed to see that filtering still isn't supported - spreadsheets are for data analysis and filtering is a big part of that analysis, so this really limits the usefulness of this component.

    Regards,

    John.

    3 June, 2014
    Anatoly P (DevExpress)
    Anatoly P (DevExpress)

    @John: You are right. The filtering is a most important feature. We are considering implementing it in future releases. You can add the www.devexpress.com/.../S171489 ticket to your Support Center favorites list.

    5 June, 2014

    Please login or register to post comments.