This Blog


Favorite Posts


November 2014 - Posts

  • WinForms and WPF Spreadsheet: Mail-Merge, Comments, and more (Coming soon in v14.2)

    In addition to the changes already described for the WinForms and the WPF spreadsheet controls, there are some smaller improvements and new features. Buckle up: this will be a wild ride…

    Mail-Merge: Data Source Wizard (WinForms)

    The Data Source Wizard allows the user to create a new data source, configure it, and, if there is more than one data source available, to select the one that is required for a particular task. Once created, the data source configuration will be stored in the XLS/XLSX spreadsheet file so the user does not have to re-configure the data source the next time.

    WinForms Spreadsheet: Mail-Merge Data Wizard

    In the past, creating and selecting such a data source required changes in your application code, so this user wizard should help reduce your involvement in such requirements, at least for technical users. With this wizard, the user can start performing a mail merge immediately the XLSX/XLS file is loaded into the Spreadsheet control.

    Alongside this new feature, we’ve added the ability to manage queries:

    WinForms Spreadsheet: Mail-Merge Query Designer

    …and also provided the capability to define and manage the data relationships required for complex master-detail reports:

    WinForms Spreadsheet: Data Relationship Editor

    Mail-Merge: Parameters Panel (WinForms)

    The Parameters Panel allows the user to fine-tune a mail-merge report by providing a required parameter. In previous versions, this relatively simple task required a significant amount of code. Here’s what it would look like for specifying an Order Number:

    WinForms Spreadsheet: Mail-Merge Parameter Panel

    To create a formula in the mail merge template linked to a certain parameter, the end-user can simply drag the parameter from the Parameters Panel and drop it onto a worksheet.

    Along with this user-oriented improvement, there’s a corresponding addition to the mail-merge API: developers can now work with mail merge parameters in code by using the MailMergeParameters parameters collection.

    Comments (WinForms and WPF)

    With this release, you can attach additional informational text to individual cells by using comments. This feature is available to end-users:

    WinForms/WPF Spreadsheet: Spreadsheet Comments

    Comments are displayed in a floating box anchored to a cell. Users can add new comments, edit the text of existing comments, move and resize the comment box, and hide or completely delete comments if they are no longer required.

    The feature is also available to developers: they can access worksheet comments in code using the CommentCollection collection. If required, developers can also restrict the use of Insert, Edit, Delete, Show/Hide, Move, and Resize operations for their users.

    Document Properties (WinForms and WPF)

    Next up are document properties. These form a set of information about the document (named values like Author, Keywords, etc, or custom properties) that is stored along with the document. Many document workflow systems require and depend on these properties.

    Users can view and modify their document properties by using the built-in dialog. This is invoked by the Document Properties button in the Info group of the File tab.

    WinForms/WPF Spreadsheet: Document Properties

    Programmatically, developers can access document properties using the DocumentProperties interface (use the IWorkbook.DocumentProperties property) and the DocumentCustomProperties interface (use the DocumentProperties.Custom property). The following code snippet illustrates how to specify document properties.

          IWorkbook workbook = spreadsheetControl1.Document;
          workbook.DocumentProperties.Title = "Spreadsheet API: Document Properties Sample";
          workbook.DocumentProperties.Description = "Managing document properties using Spreadsheet API.";
          workbook.DocumentProperties.Author = "Gerald A. Gilbert";
          workbook.DocumentProperties.Custom["Checked by"] = "Mike Hamilton";     

    The screenshot above is from our demo app. It illustrates the use of a custom worksheet function (UDF, or user-defined function, using Excel terminology) to display the values of the document properties in worksheet cells. (Please note that this function, DOCPROP, was written specially for this app. It’s not part of the standard spreadsheet control.)

    New Spreadsheet Functions (WinForms, WPF, Spreadsheet Document Server)

    As with every release, we add to the list of supported spreadsheet functions. This time we’ve added several to help with statistical analysis, especially trend analysis. With these new functions (LINEST, LOGEST, TREND, GROWTH, and FORECAST), we can perform a linear or exponential trend analysis:

    WinForms/WPF Spreadsheet: Exponential Trend Analysis

    We’ve also added the following functions (please see the Excel documentation for descriptions and usage details for now):

    • Text: BAHTTEXT

    Support for Complex Ranges (WinForms / WPF / Spreadsheet Document Server)

    With v14.2, the spreadsheet API includes support for disjoint ranges; no longer do you have to just work with a continuous range of cells. This enhancement allows operating with collections of ranges, comprising several discontinuous or even intersecting ranges. The Range.Union method creates a complex range which can be used in the same manner as an ordinary range.

  • WinForms Data Grid: breaking change with Conditional Formatting (v14.2)

    A week or so ago, Don published a blog post about our new Microsoft Excel-inspired conditional formatting feature for the WinForms data grid, XtraGrid, that’s coming in v14.2. (Again, the release is only a few weeks away, the beta is out if you are an active DXperience or Universal customer, get it now and try it out, etc.)

    It turns out that there is a subtle breaking change with this new feature. However, it’s very easily worked around.

    First, a bit of history. With v14.1 or earlier, we had what we called “Format Style Conditions”, a feature that allows you to apply different formats to the data in your grid according to some pre-defined criteria. We did not provide any ability for run-time customization, you got what you programmed. Fast-forward to now and for v14.2 we’ve ported some work we’d been doing on the WPF Grid to the WinForms Grid. For this release we have improved the speed of the condition-matching code and also have provided full run-time customization for end-users. Crucially we have also provided the capability to convert the older conditional formatting rules to the new style. Read on…

    For v14.2, although the old conditional formatting will continue to work, there will no longer be any design time support for it. If you want to add new format rules, or modify anything via the designers, the rules you already have must be converted to the new Excel-inspired conditional formatting rules. This is what you will see when you first open the Style Format Rules designer in v14.2 on an existing control that already has some formatting rules applied:

    WinForms data grid: Converting Format Rules to v14.2

    Where are my rules?, I can hear you say, possibly with some other choice words. They’re still there: all you have to do is convert them. Press Convert and your old rules will be restructured to the new v14.2 Excel-inspired conditional formatting rules. After that, have at it: you will be able to add/modify whatever you want. No data was lost in the conversion.

    As always with this kind of change, please don’t hesitate to contact our support team should you encounter any issues. They will be only too glad to help.

  • WinForms and WPF Spreadsheet: Grouping and AutoFilter (Coming soon in v14.2)

    The premier spreadsheet control for Windows developers is getting a couple of much-requested features for v14.2: data grouping, and filtering of data using the AutoFilter feature. Let’s see what these involve.

    Data Grouping

    One of the biggest new features of our WinForms, WPF, and ASP.NET datagrids in v14.2 is the ability to export data from the grid to an XLS/XLSX file and maintain the grouping and sorting currently configured in the grid. The requirement is to allow end-users to group, sort, filter data within the grid, export the result to an XLSX file, and then open up that file in Excel to continue analyzing that data. All grouping, sort orders and filtering options transfer to the XLSX file and the user is given a similar look to the data within Excel. See here for details on this new functionality.

    Well, anything Excel can do our spreadsheet controls should be able to do too, and with v14.2 we’ve added data grouping to the control.

    You can group data by using the corresponding commands of the Outline group on the Data tab on the corresponding ribbon.

    WinForms & WPF Spreadsheet: Outline Group on the Data tab

    There are several options here:

    • The Group command allows the user to group related rows or columns manually.
    • The Auto Outline command creates an automatic outline, based on the subtotal and summary formulas contained in the user’s document.
    • The Ungroup/Clear Outline command, unsurprisingly, ungroups previously created groups.
    • The Subtotal command automatically calculates subtotals for the related rows and displays summaries above or below the detail rows.

    For the latter command, a dialog is displayed allowing the user to configure the subtotals and options needed:

    WinForms & WPF Spreadsheet: Subtotal Dialog

    Once the user has organized their data into groups, they can collapse a group and temporarily hide rows or columns to show only the significant data, or expand it again to reveal the hidden data. To collapse or expand the group, the user clicks the corresponding minus or plus icon:

    WinForms & WPF Spreadsheet: Expanding a group

    The spreadsheet control goes even further: it publishes a new function, SUBTOTAL(), to give your users even more control over their summary data. The first argument defines the function code that specifies the function to be used in calculating subtotals (SUM, COUNT, AVERAGE, etc.) and whether the hidden values in collapsed groups should be taken into account during calculations. This is the direct equivalent of the Excel SUBTOTAL function.

    WinForms & WPF Spreadsheet: Subtotal Function

    (Here, for example, the function code 9 corresponds to SUM.)

    Of course, all these features are also available via the spreadsheet control API. You can group data programmatically by using the Group() and AutoOutline() methods for the specified rows, columns, or the entire document. To automatically create subtotals for the range, you use the Worksheet.Subtotal() or RangeExtention.Subtotal() methods.

    Data Filtering

    With v14.2, we have added a new powerful filtering feature to the WinForms and WPF spreadsheet controls. Using the AutoFilter functionality, your users can analyze large amounts of data by displaying only those rows that meet some filtering criteria. Applying such a filter is quite simple: the user just needs to select the required data and then click the Filter button in the Sort & Filter ribbon group.

    WinForms and WPF spreadsheet: the Filter Button

    Once filtering is activated, a down-arrow icon appears on the right side of each column header. The user clicks this icon for each required column to select the filter type they wish to use: Text Filters, Number Filters, or Filter by Values. The user can use the built-in comparison operators, or specify their own custom criteria in the Custom AutoFilter dialog box.

    WinForms and WPF Spreadsheet: AutoFilter

    If needed, the user can sort the filtered data in ascending or descending order.

    WinForms and WPF Spreadsheet: Sorting Data


    With v14.2 we are enhancing the WinForms and WPF spreadsheet controls to include even more data analysis features: sorting, grouping and filtering. We hope you and your users will delight in these new features. Please let me know below what you think of them.

  • DevExpress v14.2 beta 1 is now ready for download

    Just in time for the weekend and all that spare time you thought you’d have, we have released beta 1 of Universal v14.2. The beta is open to all active DXperience and Universal subscribers. To get it, all you need to do is login to devexpress.com, click on the Download Your Products link at the top of the page, and look for and click Universal Subscription. Cunningly we present the latest released version by default, so click on the down arrow to select the version you’d like to download and the beta is shown in red in the dropdown list.

    Crystal ballAlthough I drew the short straw to write this blog post – just kidding! – I was allowed to choose the most significant features in this beta that I felt had the most bang for the buck. So here goes for a baker’s quartet:

    1. WinForms, ASP.NET  & WPF Grid Controls: New Excel Data Export – this is HUGE. An completely rewritten Excel export engine that has more features and is, like, 10 times faster than before. I doff my hat to this one. There’s also a lot of other spreadsheet-y news dotted around the What’s New as well, so if your bag is spreadsheets (that’s me being hip), you’d better check it all out.
    2. PDF Control: Interactive Forms and Annotations – the first step to full support for forms in PDFs with our PDF Control. This has been a much-requested feature ever since we released the first version of the PDF Control.
    3. ASP.NET Adaptive and Responsive UI Support – perhaps this has been some time coming, but it’s here now. Create ASP.NET pages with our controls that look optimal with different browser window sizes.
    4. On the data analysis and reporting side (avoiding the spreadsheet stuff, I just mentioned), there are almost too many new features to review. One, two, three, four.
    5. Lots of new features and widgets for our HTML5/JS product, DevExtreme. One, two, three, four. I love me new client-side web stuff.

    I must sound a small word of caution here: this is beta 1 of v14.2, not the release. (That’s coming at the beginning of December, plus or minus.) There is likely to be a beta 2, for example. Use within production code at your own risk.

    If you’ve let your subscription lapse and you are intrigued by some of these new features, you really should renew and try them out instead of just reading our posts about them. Screenshots are all very well, but nothing beats actually using a new feature or control in your app. Contact our Client Services team today (clientservices@devexpress.com): they are ready with some great deals on your renewal.

    Of course, if you are an existing single platform subscriber (that is, WinForms, ASP.NET, WPF) and would like to obtain access to this beta as well as begin using all the other products we ship inside DXperience and Universal, please contact our client services team to learn more about the upgrade pricing available to you.

    Above all, once you’ve installed and tried out the beta, please let us know what you think of these new features. We love feedback. It also goes without saying that our support team are ready to help you with issues you may have with the beta, so contact them in the usual way if you need to.

    Aside: If you’re a VCL subscriber, fear not, your beta and release comes some two weeks or so after the .NET one, as usual. I’ve been checking out what’s new there, and I think you’ll be well pleased. I’ll start blogging about it all after this other release is done.


Chat is one of the many ways you can contact members of the DevExpress Team.
We are available Monday-Friday between 7:30am and 4:30pm Pacific Time.

If you need additional product information, write to us at info@devexpress.com or call us at +1 (818) 844-3383


DevExpress engineers feature-complete Presentation Controls, IDE Productivity Tools, Business Application Frameworks, and Reporting Systems for Visual Studio, Delphi, HTML5 or iOS & Android development. Whether using WPF, ASP.NET, WinForms, HTML5 or Windows 10, DevExpress tools help you build and deliver your best in the shortest time possible.

Copyright © 1998-2018 Developer Express Inc.
All trademarks or registered trademarks are property of their respective owners