Skip to main content
Row

Workbook Class

A non-visual component that allows you to generate and modify spreadsheet documents.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Docs.v24.1.dll

NuGet Package: DevExpress.Document.Processor

Declaration

public class Workbook :
    IWorkbook,
    ISpreadsheetComponent,
    IBatchUpdateable,
    IServiceContainer,
    IServiceProvider,
    ISupportsContentChanged,
    IBasePrintable,
    ExternalWorkbook,
    IDisposable

The following members return Workbook objects:

Remarks

The Workbook class is the root object of the non-visual spreadsheet engine. It contains properties and methods designed to create, load, modify, print, and save workbooks.

Important

The Workbook class is defined in the DevExpress.Docs.v24.1.dll assembly. Add this assembly to your project to use the Workbook API. You need a license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this assembly in production code.

Workbook Content

Use the following Workbook properties to access basic elements of a spreadsheet document:

Workbook.Worksheets
Returns the collection of worksheets in a workbook. A workbook can include one or more worksheets. You can create, rename, move, copy, hide, or delete worksheets.
Workbook.ChartSheets
Returns the document’s collection of chart sheets. A chart sheet contains only a chart and does not have any other data.
Workbook.Sheets
Returns the collection that stores all sheets (worksheets and chart sheets) in the document. Use this collection to obtain a sheet of any type.
Workbook.CustomXmlParts
Returns the collection that contains custom XML data embedded in the document.

Create a Workbook

Create a Workbook class instance to start working with a spreadsheet document in code.

// Add a reference to the DevExpress.Docs.dll assembly.
using DevExpress.Spreadsheet;
// ...

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

A new workbook contains one empty worksheet. You can also call the Workbook.CreateNewDocument method to load a blank document into a Workbook instance.

Optimize Performance

To improve performance when you apply multiple modifications to a document, wrap your code in the Workbook.BeginUpdate-Workbook.EndUpdate method calls.

When you finish working with a workbook, you are advised to call the Workbook.Dispose method to release all the resources used by the object. This method call allows you to avoid memory leaks and speed up system performance. You can also work with a Workbook instance within the using statement (Using block in Visual Basic).

Load a Document into a Workbook Instance

Use the Workbook.LoadDocument method to load an existing spreadsheet document from a file, a stream, or a byte array into a Workbook instance. Pass a DocumentFormat enumeration member to the method to define the document format.

When the file format is not specified, the Spreadsheet Document API uses the built-in IFormatDetectorService service implementation to detect the format of the loaded document. If format detection fails, the Workbook.InvalidFormatException event fires.

Handle the Workbook.DocumentLoaded event to determine when you can safely modify the loaded document.

using DevExpress.Spreadsheet;
using System.IO;
// ...

Workbook book1 = new Workbook();
Workbook book2 = new Workbook();

// Load a workbook from a file.
book1.LoadDocument(@"Documents\Document1.xlsx", DocumentFormat.Xlsx);

// Load a workbook from a stream.
using (FileStream stream = new FileStream(@"Documents\Document2.xlsx", FileMode.Open)) {
    book2.LoadDocument(stream, DocumentFormat.Xlsx);
}

Note

The Spreadsheet component does not recalculate formulas when you load a document. Calculate all formulas in the workbook after you load the document or change the workbook’s calculation mode.

Load a Document Asynchronously

Use the Workbook.LoadDocumentAsync method to asynchronously load a document from a file, a stream, or a byte array into a Workbook instance. The method overloads allow you to implement progress notifications or cancel the operation if needed.

The following example demonstrates how to asynchronously load a document and cancel the operation if it takes longer than 30 seconds:

using DevExpress.Spreadsheet;
using System;
using System.Threading;
using System.Threading.Tasks;
// ...

static async Task Main(string[] args)
{
  var cancellationSource = new CancellationTokenSource(TimeSpan.FromSeconds(30));
  var cancellationToken = cancellationSource.Token;
  try
  {
      using (Workbook workbook = new Workbook())
      {
          await workbook.LoadDocumentAsync("Document.xlsx", DocumentFormat.Xlsx, 
              cancellationToken);
      }
  }
  catch (OperationCanceledException)
  {
      Console.WriteLine("Cancelled by timeout.");
  }
  finally
  {
      cancellationSource.Dispose();
  }
}

Save a Workbook

Use the Workbook.SaveDocument method to save a workbook to a file, a byte array, or a stream.

using DevExpress.Spreadsheet;
using System.IO;
// ...

Workbook workbook = new Workbook();
// ...
// Edit document content.
// ...

// Save the modified document to the stream.
using (FileStream stream = new FileStream(@"Documents\SavedDocument.xlsx", 
    FileMode.Create, FileAccess.ReadWrite)) 
{
    workbook.SaveDocument(stream, DocumentFormat.Xlsx);
}

Note

The Spreadsheet component does not recalculate formulas when you save a workbook. Calculate all formulas in the document before the save operation or change the workbook’s calculation mode.

Save a Workbook Asynchronously

Use the Workbook.SaveDocumentAsync method to asynchronously save a spreadsheet document to a file, a byte array, or a stream. The method overloads allow you to implement progress notifications or cancel the operation if needed.

The following code asynchronously saves a document and displays the operation progress in the console window:

using DevExpress.Spreadsheet;
using System;
using System.Threading.Tasks;
// ...

static async Task Main(string[] args)
{
  using (Workbook workbook = new Workbook())
  {
      // Edit document content.
      // ...
      // Save the document.
      await workbook.SaveDocumentAsync("SavedDocument.xlsx", DocumentFormat.Xlsx, 
          new Progress<int>((progress) => Console.WriteLine($"{progress}%")));
  }
}

Export a Workbook to PDF

The Workbook.ExportToPdf method allows you to export a workbook or individual worksheets to PDF format. You can pass a PdfExportOptions class instance to this method to define export options.

using DevExpress.Spreadsheet;
using DevExpress.XtraPrinting;
// ...

using (Workbook workbook = new Workbook()) 
{ 
    // ...
    // Specify export options.
    PdfExportOptions options = new PdfExportOptions();
    options.DocumentOptions.Author = "John Smith";
    options.ImageQuality = PdfJpegImageQuality.Medium;

    // Export the entire workbook to PDF.
    workbook.ExportToPdf(@"Documents\Output_Workbook.pdf", options);

    // Export the specified worksheets to PDF.
    workbook.ExportToPdf(@"Documents\Output_Worksheets.pdf", options, "Sheet1", "Sheet2");
}

Note

The Spreadsheet component does not recalculate formulas when you export a workbook. Calculate all formulas in the workbook before the export operation or change the workbook’s calculation mode.

Export a Workbook to PDF Asynchronously

Use the Workbook.ExportToPdfAsync method to asynchronously export a workbook or individual worksheets to PDF format. The method overloads allow you to define export options, implement progress notifications, or cancel the operation if needed.

The following code asynchronously saves a document as a PDF and displays the operation progress in the console window:

using DevExpress.Spreadsheet;
using DevExpress.XtraPrinting;
using System;
using System.Threading.Tasks;
// ...

static async Task Main(string[] args)
{
    using (Workbook workbook = new Workbook())
    {
        // ...
        // Specify export options.
        var options = new PdfExportOptions();
        options.DocumentOptions.Author = "John Smith";
        options.ImageQuality = PdfJpegImageQuality.Medium;

        // Export the workbook to PDF.
        await workbook.ExportToPdfAsync("Output_Workbook.pdf", options,
            new Progress<int>((progress) => Console.WriteLine($"{progress}%")));
    }
}

Export a Workbook to HTML

The Workbook.ExportToHtml method allows you to export an individual worksheet or a specific cell range to HTML format. This method can accept an HtmlDocumentExporterOptions parameter that allows you to define export options.

The following example exports a given cell range in the first worksheet to HTML:

using DevExpress.Spreadsheet;
using DevExpress.XtraSpreadsheet.Export;
// ...

using (Workbook workbook = new Workbook())
{
    // ...
    var options = new HtmlDocumentExporterOptions();

    // Specify the part of the document
    // you want to save as HTML.
    options.SheetIndex = 0;
    options.Range = "B2:G7";

    // Export data to HTML format.
    workbook.ExportToHtml("Output_Workbook.html", options);
}

Note

The Spreadsheet component does not recalculate formulas when you export a workbook. Calculate all formulas in the workbook before the export operation or change the workbook’s calculation mode.

Export a Workbook to HTML Asynchronously

Use the Workbook.ExportToHtmlAsync method to asynchronously export an individual worksheet or a specific cell range to HTML format. The method overloads allow you to define export options, implement progress notifications, or cancel the operation if needed.

The following code asynchronously saves the first worksheet as HTML and displays the operation progress in the console window:

using DevExpress.Spreadsheet;
using System;
using System.Threading.Tasks;
// ...

static async Task Main(string[] args)
{
    using (Workbook workbook = new Workbook())
    {
        // ...
        // Export the first worksheet to HTML.
        await workbook.ExportToHtmlAsync("Output_Workbook.html", 0,
            new Progress<int>((progress) => Console.WriteLine($"{progress}%")));
    }
}

Clone a Workbook

Use the WorkbookExtensions.Clone method to create a workbook copy. The method’s copyFormulas parameter allows you to specify whether to copy the source workbook’s formulas or to replace formulas with their calculated values.

// Create a new Workbook object.
Workbook workbook = new Workbook();
workbook.LoadDocument("Document.xlsx", DocumentFormat.Xlsx);

// Create a copy of the Workbook object.
Workbook copy = workbook.Clone(false);

Merge Multiple Workbooks

Use the following methods to combine data from multiple workbooks into a single document:

WorkbookExtensions.Append
Appends all worksheets from the specified workbooks to the current workbook.
Workbook.Merge
Combines specified workbooks into a new document.

Copy Document Content to Another Workbook

// Create the first workbook.
Workbook book1 = new Workbook();
book1.LoadDocument("Document1.xlsx", DocumentFormat.Xlsx);

// Create the second workbook.
Workbook book2 = new Workbook();
book2.LoadDocument("Document2.xlsx", DocumentFormat.Xlsx);

// Copy all worksheets from "Document1" to "Document2".
book2.Append(book1);

Merge Workbooks into a New Document

// Create the first workbook.
Workbook book1 = new Workbook();
book1.LoadDocument("Document1.xlsx", DocumentFormat.Xlsx);

// Create the second workbook.
Workbook book2 = new Workbook();
book2.LoadDocument("Document2.xlsx", DocumentFormat.Xlsx);

// Combine two documents into a new document.
Workbook result = Workbook.Merge(book1, book2);

Protect the Workbook Structure

Use the Workbook.Protect method to protect the workbook structure with a password. When a workbook is protected, other users cannot add, move, delete, rename, or hide existing worksheets or view hidden worksheets.

using DevExpress.Spreadsheet;
// ...

using (Workbook workbook = new Workbook())
{
    // Protect the workbook structure with a password.
    if (!workbook.IsProtected)
        workbook.Protect("password", true, false);
}

Call the Workbook.Unprotect method to remove protection.

Encrypt a Workbook

To encrypt your document with a password, call the Workbook.SaveDocument or Workbook.SaveDocumentAsync method and pass an EncryptionSettings class instance as a parameter. This class allows you to select the encryption type and specify an encryption password.

using DevExpress.Spreadsheet;
// ...

using (Workbook workbook = new Workbook())
{
    // Specify encryption settings.
    var encryptionSettings = new EncryptionSettings();
    encryptionSettings.Type = EncryptionType.Strong;
    encryptionSettings.Password = "password";

    // Save the document.
    workbook.SaveDocument("Document.xlsx", DocumentFormat.Xlsx, encryptionSettings);
}

Write-Protect a Workbook

The Workbook.DocumentSettings.WriteProtection property allows you to specify write-protection options for a workbook to prevent modifications from unauthorized users. These options include:

using(var workbook = new Workbook())
{
    var wpOptions = workbook.DocumentSettings.WriteProtection;
    wpOptions.SetPassword("Password");
    wpOptions.UserName = "John Smith";
    workbook.SaveDocument("WriteProtectedDocument.xlsx");
}

When users open this workbook in Microsoft® Excel®, it prompts them to enter a password to modify the document.

Write-protection options for a workbook

Call the Workbook.Print method to print the entire workbook or individual sheets. Pass a PrinterSettings class instance to this method to specify printer settings.

Use properties of the WorksheetView and WorksheetPrintOptions objects to define page options and specify print settings.

using DevExpress.Spreadsheet;
using System.Drawing.Printing;
// ...

using (Workbook workbook = new Workbook())
{
    // 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");
}

You can handle the Workbook.BeforePrintSheet event to cancel printing for specific worksheets.

Note

The Spreadsheet component does not recalculate formulas when you print a workbook. Calculate all formulas in the workbook before you print it or change the workbook’s calculation mode.

Calculate Formulas in a Workbook

The default calculation mode for a Workbook is Manual. This mode implies that the Spreadsheet component does not recalculate formulas when you load, save, export, or print a document. Call the Workbook.Calculate or Workbook.CalculateFull method to recalculate all formulas in the workbook.

using (Workbook workbook = new Workbook()) {

  // Load a document.
  workbook.LoadDocument("Document.xlsx", DocumentFormat.Xlsx);

  // Calculate formulas in the document.
  workbook.Calculate();

  // ...
}

Change Calculation Mode

Use the Workbook.Options.CalculationMode property to specify when to calculate formulas in a Workbook.

The following calculation modes are available:

  • Manual (default)—Recalculates formulas only on demand (after the Calculate method call). It allows you to improve document generation speed for large workbooks with multiple formulas.

  • UseDocumentSettings —Uses the calculation mode specified in the loaded document (the Workbook.DocumentSettings.Calculation.Mode property value).

  • Automatic —Recalculates formulas each time a cell value, formula, or defined name changes.

using (Workbook workbook = new Workbook()) {

  // Change the calculation mode.
  workbook.Options.CalculationMode = WorkbookCalculationMode.UseDocumentSettings;

  // ...
  // Load a document.
  // ...
}

View and Edit Document Properties

Built-In Document Properties

Document properties are metadata associated and stored with a workbook. Use the Workbook.DocumentProperties property to specify standard document properties that contain basic information about the document (DocumentProperties.Title, DocumentProperties.Author, DocumentProperties.Subject, and so on).

// Set the built-in document properties.
workbook.DocumentProperties.Title = "Document properties example";
workbook.DocumentProperties.Description = "How to use the Spreadsheet API to manage document properties";

// Display the specified built-in properties in a worksheet.
Worksheet worksheet = workbook.Worksheets[0];
worksheet["B3"].Value = "Title";
worksheet["C3"].Value = workbook.DocumentProperties.Title;
worksheet["B4"].Value = "Description";
worksheet["C4"].Value = workbook.DocumentProperties.Description;

Note

Some of the document properties are updated automatically when a document is created (DocumentProperties.Author, DocumentProperties.Created), last modified and saved (DocumentProperties.LastModifiedBy, DocumentProperties.Modified), or printed (DocumentProperties.Printed).

Custom Document Properties

Use the DocumentProperties.Custom property to create your own document properties.

// Set the custom document properties.
workbook.DocumentProperties.Custom["Revision"] = 3;
workbook.DocumentProperties.Custom["Completed"] = true;
workbook.DocumentProperties.Custom["Published"] = DateTime.Now;

To remove all custom document properties from a workbook, call the DocumentCustomProperties.Clear method.

Load Document Properties Only

The Workbook.LoadDocumentProperties method allows you to load the document properties without loading the workbook itself. You can use retrieved metadata to search for a specific document, to organize files, or to collect statistics.

// Load metadata from a document.
ReadOnlyDocumentProperties docProperties = workbook.LoadDocumentProperties("Document.xlsx");
string title = docProperties.Title;
DateTime date = docProperties.Modified;
string author = docProperties.Author;

// Display document metadata in the console window.
Console.WriteLine($"Document Title: {title}, Last Modified: {date}, Author: {author}");

Document Settings

Use the Workbook.Options property to access various document options. They include:

WorkbookOptions.Import

Specifies options used to import documents in different file formats.

WorkbookOptions.Export

Specifies options used to export documents to different file formats.

WorkbookOptions.Save

Defines the file name and file format used when a workbook is saved.

WorkbookOptions.DocumentCapabilities

Allows you to disable the following document features: Formulas, Charts, Pictures, Shapes, and Sparklines.

WorkbookOptions.Cells

Specifies options for worksheet cells.

WorkbookOptions.Copy

Contains options that allow you to control copy operations.

WorkbookOptions.Culture

Contains culture-specific settings.

WorkbookOptions.Events

Provides access to the WorkbookEventOptions.RaiseOnModificationsViaAPI option that enables you to raise events for changes performed via the API.

WorkbookOptions.CalculationMode

Specifies the calculation mode for a Workbook object. The default mode is Manual. Call the Workbook.Calculate, Worksheet.Calculate, or CellRange.Calculate method to calculate formulas in the document.

WorkbookOptions.CalculationEngineType

Specifies the computational model used to perform calculations in a workbook.

WorkbookOptions.RealTimeData

Returns options for real-time data (RTD) function calculation.

WorkbookOptions.Protection

Allows you to specify the UseStrongPasswordVerifier and SpinCount password protection options.

WorkbookOptions.DataSourceLoading

Provides access to the SpreadsheetDataSourceLoadingOptions.CustomAssemblyBehavior option that specifies whether to load a custom assembly with the Entity Framework data context during mail merge.

WorkbookOptions.Compatibility

Returns compatibility settings that allow you to turn off the new layout engine and its features.

WorkbookOptions.Localization

Returns localization options for a workbook.

WorkbookOptions.Layout

Contains workbook layout options. Use the WorkbookLayoutOptions.Dpi property to specify the resolution (DPI) for document layout generation.

WorkbookOptions.ZoomMode

Specifies whether to apply the SheetView.Zoom setting to all worksheet views or the current view only.

Other workbook settings are available from the Workbook.DocumentSettings property.

DocumentSettings.Calculation

Returns formula calculation options.

DocumentSettings.R1C1ReferenceStyle

Specifies whether a workbook should use the R1C1 reference style.

DocumentSettings.Encryption

Returns document encryption options.

DocumentSettings.ShowPivotTableFieldList

Specifies whether to show the Field List for pivot tables in a workbook.

Inheritance

Object
Workbook
See Also