Office File API & Office-Inspired UI Controls – Tips & Tricks (January 2022)

Office-Inspired Products
16 February 2022

This post includes a series of interesting support tickets answered throughout December and January along with a few 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.

Tips & Tricks

Word Processing Document API and Rich Text Editors (WinForms and WPF)

Spreadsheet Document API and Spreadsheet Controls (WinForms and WPF)

  • T1052887 - How to ignore all errors in worksheet formulas

    To ignore specific formula errors, call the Worksheet.IgnoredErrors.Add method and pass a target range and necessary error type flags as method parameters. The following code snippet demonstrates how to ignore all errors in worksheet formulas:

    sheet.IgnoredErrors.Add(sheet.GetDataRange(), IgnoredErrorType.EmptyCellReferences |
                IgnoredErrorType.EvaluateToError | IgnoredErrorType.FormulaRange |
                IgnoredErrorType.InconsistentColumnFormula | IgnoredErrorType.InconsistentFormula |
                IgnoredErrorType.ListDataValidation | IgnoredErrorType.NumberAsText |
                IgnoredErrorType.TextDate | IgnoredErrorType.UnlockedFormula);
  • T1054845 - How to determine a clicked cell in a read-only worksheet

    If the SpreadsheetControl.ReadOnly option is enabled, handle the SpreadsheetControl.SelectionChanged event and check the SpreadsheetControl.ActiveCell property to determine the clicked cell in a worksheet.

    private void SpreadsheetControl_SelectionChanged(object sender, EventArgs e)
         Cell cell = spreadsheetControl.ActiveCell;

    If a worksheet is protected and the "Select Locked Cells" and "Select Unlocked Cells" protection options are disabled, handle the SpreadsheetControl.MouseDown event and call the SpreadsheetControl.GetCellFromPoint method to obtain the clicked cell by its coordinates.

    private void SpreadsheetControl_MouseDown(object sender, MouseEventArgs e)
         Cell cell = spreadsheetControl.GetCellFromPoint(e.Location); 
  • T1054824 - How to assign cell values with line breaks and enable text wrapping

PDF Document API

  • T1055635 - How to print a PDF document as a booklet
  • T1059318 - How to add a PDF page to the background of a different PDF page

    Use the PdfGraphics.DrawPageContent(PdfPage) method to merge content from multiple PDF pages into a single page. The following code snippet merges the first pages from two PDF files and draws one PDF page in the background and another page in the foreground:

    using DevExpress.Pdf;
    using DevExpress.Pdf.Drawing;
    PdfDocumentProcessor processor = new PdfDocumentProcessor(); 
    PdfDocumentProcessor foregroundDocument = new PdfDocumentProcessor();
    PdfDocumentProcessor backgroundDocument = new PdfDocumentProcessor();
    PdfGraphics graphics = processor.CreateGraphics(); 
  • T1064863 - How to use the PDF Document API to print a PDF file to a specific printer tray
  • T339557 - How to draw text or an image in the center of a PDF page


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

T1062005 – You can now determine when a user expands/collapses a group in a worksheet or clicks an outline button

We added four new events to the WinForms and WPF Spreadsheet controls:

  • GroupStateChanging
  • GroupStateChanged
  • BeforeOutlineButtonClick
  • AfterOutlineButtonClick

The GroupStateChanging event occurs before a group is expanded or collapsed and allows you to cancel the operation. The GroupStateChanged event is raised after the group was expanded or collapsed. Event arguments allow you to determine group state (expanded or collapsed), to check group type (row or column), and to retrieve information about affected groups.
The following code snippet prevents users from expanding row groups:

using DevExpress.XtraSpreadsheet;

private void SpreadsheetControl_GroupStateChanging(object sender, 
    GroupStateChangingEventArgs e)
     if (e.IsRowGroup && e.IsExpanding)
         e.Cancel = true;

The BeforeOutlineButtonClick event fires when a user clicks an outline button and allows you to cancel the default action. The AfterOutlineButtonClick event occurs after the outline button was clicked and the default action was executed. The IsRowOutline event argument allows you to determine whether a user clicked the row or column outline button, and the Level argument returns the outline level.

The following code snippet prevents all column groups from collapsing when a user clicks the outline button 1:

using DevExpress.XtraSpreadsheet;

private void SpreadsheetControl_BeforeOutlineButtonClick(object sender, 
    BeforeOutlineButtonClickEventArgs e)
     if (e.Level == 1 && !e.IsRowOutline)
         e.Cancel = true;

PDF Document API

T1063961 – You can now create ZUGFeRD-compliant invoices with XRechnung conformance

Pass the PdfZugferdConformanceLevel.XRechnung value to the PdfDocument.AttachZugferdInvoice method, as shown below:

using (PdfDocumentProcessor pdfDocumentProcessor = new PdfDocumentProcessor())
        (File.ReadAllBytes("XRechnung-invoice.xml"), PdfZugferdVersion.Version2_1, 

WinForms and WPF PDF Viewers

T1049595 – You can now obtain printer settings selected in the Print Page Setup dialog

A new ShowPrintPageSetupDialog method allows you to open the Print Page Setup dialog in code. This method returns the PdfPrinterSettings object if a user pressed OK and closed the dialog. If a user clicked Cancel or closed the dialog, the ShowPrintPageSetupDialog method returns null.

You can use the following code to obtain printer settings selected in the Print Page Setup dialog:

PdfPrinterSettings printerSettings = pdfViewerControl.ShowPrintPageSetupDialog(); 

if (printerSettings != null) 
    // add your code here 

New Examples and Documentation Updates

As you probably know, the DevExpress PDF Document API library allows you to retrieve a certificate from a hardware device (such as Windows certificate store, SmartCard, and USB Token). We created an example that shows how to sign a PDF file using a certificate stored on a user's machine. Please note that you can also adapt this solution to sign documents with certificates from any physical store.

PDF Document API - Sign a PDF Document with a Certificate Stored on a Hardware Device

We also expanded the list of signature examples in our PDF Document API documentation and included the above-mentioned example in this list.

PDF Document Protection Examples

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.