Spreadsheet (WinForms, WPF, Office File API) – API Enhancements (v19.2)

The following is a summary of all major Spreadsheet-related features/capabilities we introduced in this release cycle (v19.2). This post applies to our Spreadsheet Document API and our WinForms Spreadsheet and WPF Spreadsheet controls.

Our Range Interface Is Now CellRange (Breaking Change)

As you may already know, we renamed our Range interface to CellRange in v19.2. We had to make this breaking change to avoid name conflicts with the System.Range structure introduced in .NET Standard 2.1.

You can find more information on this breaking change in the following blog post published earlier this year:
Spreadsheet – Breaking Change in v19.2

New Document Formats

We extended the list of supported document formats for our Spreadsheet Document API and WinForms/WPF Spreadsheet controls. New formats include:

• Excel Binary Workbook (XLSB)

  workbook.LoadDocument("BinaryWorkbook.xlsb", DocumentFormat.Xlsb);

• Excel 5.0/95 Workbook (BIFF5)

  You can only import documents saved using this format.

Asynchronous Load and Export

The Spreadsheet Document API allows you to load, save, and export documents to PDF and HTML asynchronously. Use the following new methods of the Workbook class as needed:

• Workbook.LoadDocumentAsync
• Workbook.SaveDocumentAsync
• Workbook.ExportToPdfAsync
• Workbook.ExportToHtmlAsync

These methods can accept a CancellationToken as a parameter. This allows you to terminate the operation when needed.

The example below demonstrates how to asynchronously merge two workbooks and save results.

private async void MergeWorkbooks() {
    using (Workbook workbook1 = new Workbook())
        using (Workbook workbook2 = new Workbook()) {
            await Task.WhenAll(new Task[] {
                // Load the first workbook.
                workbook1.LoadDocumentAsync("Book1.xlsx"),
                // Load the second workbook.
                workbook2.LoadDocumentAsync("Book2.xlsx")
            });
            // Merge two workbooks and save the result.
            workbook1.Append(workbook2);
            await workbook1.SaveDocumentAsync("Result.xlsx");
        }
}

Print / Export to PDF Individual Sheets

Use the new Print and ExportToPdf method overloads of the Workbook class to print or export to PDF sheets with the specified names. To use these methods in production code, you need an active license for our Office File API or the DevExpress Universal Subscription.

// Create a new Workbook object.
Workbook workbook = new Workbook();

// Load a document from a file.
workbook.LoadDocument("Document.xlsx");

// Create an object that contains printer settings.
PrinterSettings printerSettings = new PrinterSettings();

// Define the printer to use.
printerSettings.PrinterName = "Microsoft Print to PDF";
printerSettings.PrintToFile = true;
printerSettings.PrintFileName = "PrintedDocument.pdf";

// Print specific worksheets in the document.
workbook.Print(printerSettings, "Sheet1", "Sheet2");

Resize a Cell Range

The new CellRange.Resize method allows you to enlarge or reduce a cell range within a worksheet. The method’s rowCount and columnCount parameters specify the number of rows and columns in the new range. The Resize method always takes the range’s top left cell as its starting point.

The following example demonstrates how to extend selection within the Spreadsheet control by one row and one column:

CellRange selection = spreadsheetControl1.Selection;
selection.Resize(selection.RowCount + 1, selection.ColumnCount + 1).Select();

Cell Reference Validation

Use the Worksheet.Range.ValidateReference method to check whether a specified cell reference is valid.

worksheet.Range.ValidateReference("A1:C4", ReferenceStyle.A1); // returns true
worksheet.Range.ValidateReference("R[-5]C[4]:R1C[-2]", ReferenceStyle.R1C1); // returns true
worksheet.Range.ValidateReference("A1:B1:C1", ReferenceStyle.A1); // returns false
worksheet.Range.ValidateReference("Sheet1!R12C7", ReferenceStyle.R1C1); // returns false

New ColumnCollection Methods

Our ColumnCollection interface ships with new methods designed to address columns by their headings using the A1 reference style.

For example, you can now write the following code to group columns within a worksheet:

// Group four columns from C to F and expand the group.
worksheet.Columns.Group("C", "F", false);

Improved Clipboard Support

With this release, you can paste drawing objects (shapes, pictures, and charts) from the clipboard onto the Spreadsheet Control (WinForms and WPF).

The new SpreadsheetClipboardOptions.PasteFormat option allows you to specify the format to use when pasting data from the clipboard. The following data formats are supported: BIFF12, BIFF8, BIFF5, CSV, tab-delimited text, drawing objects, metafiles, and images.

// Specify that the Spreadsheet control should paste data 
// from the clipboard in BIFF12 format.
spreadsheetControl1.Options.Clipboard.PasteFormat = 
    DevExpress.XtraSpreadsheet.ClipboardPasteFormat.Biff12;

Your Feedback Matters

As always, we’d love to hear from you. If you’re using our Spreadsheet Controls or Spreadsheet Document API, feel free to comment below and share your experiences with the entire DevExpress community.

Free DevExpress Products - Get Your Copy Today