Office File API & Office-Inspired Desktop UI Controls – Tips and Tricks (December 2021)

Office-Inspired Products
16 December 2021

This post includes a series of interesting support tickets answered throughout October and November along with a handful of enhancements made to our Office-inspired product line. We hope you find the contents of this post interesting and of business value. Should you have any questions about this post, feel free to comment below.

Breaking Change in .NET 6

As you may already know, Microsoft changed DeflateStream, GZipStream, and CryptoStream behaviors in .NET 6. See the following breaking change for more information: DeflateStream, GZipStream, and CryptoStream handling of partial and zero-byte reads.

This breaking change affects our Office File API and Office-inspired UI controls (for WinForms and WPF) as we use DeflateStream to import OpenXML documents and load data from ZIP archives. If your application targets .NET 6, you may encounter the following issues:

  • Your Word (DOCX, DOTX, DOCM, or DOTM) and Excel (XLSX, XLSM, XLTX, or XLTM) documents may be loaded incorrectly.
  • The ZipItem.Extract method can throw CrcErrorArchiveItemException.

Please upgrade to v21.1.7+ or v21.2.4+ to avoid/address these issues within your .NET 6 project (see Bug Report T1047593).

Tips & Tricks

WinForms and WPF Rich Text Editors

WinForms and WPF Spreadsheet Controls

  • T1034923 - How to display a message that prompts a user to save changes before the current spreadsheet document is closed

    Handle the SpreadsheetControl.DocumentClosing event and use the SpreadsheetControl.Modified property within the event handler to check whether the current document has changes. We use this method in our demos for the WinForms and WPF Spreadsheet controls. The following code snippet from our demos demonstrates how to display a prompt message before the document is closed:

    void spreadsheetControl1_DocumentClosing(object sender, CancelEventArgs e)
        if (spreadsheetControl1.Modified)
            string currentFileName = spreadsheetControl1.Options.Save.CurrentFileName;
            string message = !string.IsNullOrEmpty(currentFileName) ?
                $"Do you want to save the changes you made for '{currentFileName}'?" :
                "Do you want to save the changes?";
            DialogResult result = XtraMessageBox.Show(message, "Warning",
            MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning);
            if (result == DialogResult.Yes)
                e.Cancel = !spreadsheetControl1.SaveDocument();
            else e.Cancel = result == DialogResult.Cancel;
  • T1036481 - How to create total bars for a Waterfall chart via the Spreadsheet control’s UI

  • T1038552 - How to exclude chart types from the Insert Chart ribbon menu in the WPF Spreadsheet control

  • T1039579 - How to exclude chart types from the Change Chart Type dialog in the WPF Spreadsheet control

  • T1045409 - How to keep hidden columns invisible when the AutoFitColumns method is executed

    Create a union range with only visible columns and call the AutoFitColumns extension method for this range.

    private static void AutoFitVisibleColumns(Worksheet worksheet, 
        int startColumn, int endColumn)
        var range = worksheet.Range.FromLTRB(startColumn, 0, endColumn, 0);
        for (int i = startColumn; i <= endColumn; i++)
            if (!worksheet.Columns[i].Visible)
                range = range.Exclude(worksheet.Columns[i]);
  • T1045447 - How to prevent export of filtered rows when a document is saved in XLSX or XLS format

    Iterate through worksheet rows and check the Row.Visible and Row.IsFiltered properties. If a row is hidden and belongs to a filtered range, call the Worksheet.Rows.Remove method to delete this row from the worksheet.

    CellRange dataRange = worksheet.GetDataRange();
    int rowCount = 0;
    int lastRowIndex = dataRange.BottomRowIndex;
    for (int rowIndex = lastRowIndex; rowIndex >= 0; rowIndex--)
        Row row = worksheet.Rows[rowIndex];
        if (!row.Visible && row.IsFiltered)
            if (rowCount > 0)
                worksheet.Rows.Remove(rowIndex + 1, rowCount);
            rowCount = 0;
    if (rowCount > 0)
        worksheet.Rows.Remove(0, rowCount);
    spreadsheetControl.SaveDocument("Result.xlsx", DocumentFormat.Xlsx);

WinForms PDF Viewer

WinForms Spell Checker


Spreadsheet Document API and Spreadsheet Controls (for WinForms and WPF)

  • T1042763 - We implemented new API properties that allow you to link shape text to a cell

    You can now use the Shape.ShapeText.Formula property to specify a reference to the cell that contains text you want to display in your shape. Use the A1 or R1C1 cell reference type depending on reference style used in your workbook (see DocumentSettings.R1C1ReferenceStyle). External references are not supported.

    // Specify cell value.
    worksheet["B2"].Value = "Shape Text";
    // Create rectangle.
    var rectangle = worksheet.Shapes.AddShape(ShapeGeometryPreset.Rectangle,
    // Link shape text to the "B2" cell.
    rectangle.ShapeText.Formula = "=Sheet1!B2";

    The following image demonstrates the result:

    You can also use a defined name that refers to the required cell.

    // Specify cell value.
    worksheet["B2"].Value = "Shape Text";
    // Create defined name for the "B2" cell.
    worksheet.DefinedNames.Add("cellB2", "Sheet1!$B$2");
    // Create rectangle.
    var rectangle = worksheet.Shapes.AddShape(ShapeGeometryPreset.Rectangle, 
    // Use defined name to link shape text to the "B2" cell.
    rectangle.ShapeText.Formula = "=cellB2";

    The Shape.ShapeText.IsLinked property returns true if shape text is linked to a cell. Shape text is updated automatically when a value in the referenced cell changes or document formulas are recalculated.

  • T1041398 - We added a new WorksheetView.GridlineColor property to specify the color of worksheet gridlines

    The following code snippet changes the gridline color to red:

    workbook.Worksheets[0].ActiveView.GridlineColor = Color.Red;

    Assign Color.Empty to the WorksheetView.GridlineColor property to restore default gridline color. Transparent or semitransparent colors are not supported.

PDF Document API

  • T1037054 - We implemented a new PdfDocumentProcessor.DataRecognitionCacheSize property that allows you to set cache size (in pages) for data recognition

    Use the PdfDocumentProcessor.DataRecognitionCacheSize property to increase the number of pages with cached data. This may be useful if you need to accelerate FindText method execution on documents larger than 65 pages (the default cache size is 65 pages).

    If the DataRecognitionCacheSize property is set to 0, cache size is unlimited.

    pdfDocumentProcessor.DataRecognitionCacheSize = 100;
  • T1047842 – We added new APIs to create a form field with multiple widgets

    You can now add widgets to a text box (PdfAcroFormTextBoxField), check box (PdfAcroFormCheckBoxField), and list box/combo box (the PdfAcroFormChoiceField class descendants).

    Use the following methods to manage a form field’s widgets:

    • AddWidget(PdfRectangle rectangle)

      Adds a widget to the page where the form field is located.

    • AddWidget(int pageNumber, PdfRectangle rectangle)

      Adds a widget to a specific page.

    • ClearWidgets()

      Removes existing widgets from the form field.

    We also implemented a new AddButton(string name, int pageNumber, PdfRectangle rect) method for the PdfAcroFormRadioGroupField class to append an additional radio button to a radio group field.

WinForms and WPF PDF Viewers

Note: Add the DevExpress.Docs.v21.2.dll assembly to your project to use the PDF Facade API. Please note that you need an active DevExpress Office File API Subscription or DevExpress Universal Subscription to use this assembly in production code.

New Examples: WinForms and WPF Spreadsheet Controls

We created two examples that demonstrate the use of the DevExpress Spell Checker component (to check spelling in our WinForms and WPF Spreadsheet controls). When a user enters text in a cell, the spell checker indicates misspelled words with a red wavy line. A user can right-click an underlined word and select the correct spelling from the list of suggestions or invoke the Spelling dialog.

Your Feedback Matters

As always, if you’ve come across an interesting support ticket you’d like to share with the rest of the DevExpress developer community, please comment below and include the appropriate link.

Free DevExpress Products – Get Your Copy Today

The following free DevExpress product offers remain available. Should you have any questions about the free offers below, please submit a ticket via the DevExpress Support Center at your convenience. We’ll be happy to follow-up.
No Comments

Please login or register to post comments.