Workbook

  • 5 minutes to read

The Workbook class provides the means to create, load, edit, save and print spreadsheet documents in code. Add the DevExpress.Docs.v20.1.dll assembly to your project to use the workbook functionality.

NOTE

You need an active license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use a Workbook object in production code.

The example below shows how to create a Workbook instance.

using DevExpress.Spreadsheet;
// ...

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

Performance Optimization Tips:

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

  • When you finish working with the document, call the Workbook.Dispose method to release all the resources used by the object. This action helps you avoid memory leaks and speed up system performance. You can also modify a Workbook within the using statement (Using block in Visual Basic).

Workbook Content

Worksheets and Chart Sheets

A workbook consists of one or more worksheets stored in the Workbook.Worksheets collection. When you create a workbook, it contains an empty worksheet. Use the WorksheetCollection members to add a new worksheet, remove an existing worksheet, rename a worksheet, select an active worksheet, and so on.

Spreadsheet_Worksheet

A workbook can also contain chart sheets that display only a chart. Use the Workbook.ChartSheets property to access and modify the chart sheet collection.

Spreadsheet_Chartsheet

The Workbook.Sheets collection stores all sheets (worksheets and chart sheets) in a workbook. Use this collection to obtain a sheet of any type.

Defined Names

The Workbook.DefinedNames collection includes global defined names that are available in any of the current workbook's worksheets.

Built-in and Custom Functions

You can include functions in your formulas to perform calculations. Use the Workbook.Functions property to access the WorkbookFunctions object that stores all built-in functions and allows you to override any function via the WorkbookFunctions.OverrideFunction method.

See how to use functions in formulas.

You can also create a custom function. To add a custom function to a workbook, define a class that implements the ICustomFunction interface and add its instance to the Workbook.CustomFunctions or Workbook.GlobalCustomFunctions collection.

NOTE

Custom functions are not saved. To replace custom function definitions with calculated values when a workbook is saved, set the WorkbookExportOptions.CustomFunctionExportMode option to CalculatedValue.

Cell Styles

A workbook contains a collection of styles used to format cell appearance. Use the Workbook.Styles property to access and modify this collection.

See how to add new cell styles or modify the existing styles.

Table and Pivot Table Styles

The Workbook.TableStyles collection stores styles you can apply to tables and pivot tables. You can select a built-in style or create a custom style.

See how to apply a style to a table.

See how to apply a style to a pivot table.

Custom XML Parts

You can embed arbitrary XML data in workbooks in Excel file formats. This data is named custom XML parts and stored in the Workbook.CustomXmlParts collection. Use the collection's members to create and modify custom XML parts.

Operations with Workbooks

The following table lists operations you can perform on a workbook.

Task

API Members/Examples

Create a new document

Workbook.CreateNewDocument

How to: Create a New Workbook

Load a workbook

Workbook.LoadDocument

DocumentOptions.Import

How to: Load a Document into Workbook

Supported Formats

Save a workbook

Workbook.SaveDocument

Workbook.ExportToHtml

Workbook.ExportToPdf

DocumentOptions.Export

How to: Save a Document to a File

How to: Export a Workbook to PDF

How to: Export a Document to HTML

Supported Formats

Protect a workbook

Workbook.Protect

Workbook.Unprotect

How to: Protect a Workbook

Encrypt a workbook

Workbook.SaveDocument

DocumentSettings.Encryption

How to: Open and Save a Password Encrypted File

Print a workbook

Workbook.Print

Sheet.Print

Worksheet.PrintOptions

Printing

Trace the history of changes

Workbook.History

View and edit document properties

Workbook.DocumentProperties

DocumentProperties.Custom

How to: Specify Document Properties

Document Settings

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

DocumentOptions.Import

Specifies options used to import documents in different file formats.

DocumentOptions.Export

Specifies options used to export documents to different file formats.

DocumentOptions.Save

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

DocumentOptions.DocumentCapabilities

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

DocumentOptions.Cells

Specifies options for worksheet cells.

DocumentOptions.Copy

Contains options that allow you to control copy operations.

DocumentOptions.Culture

Provides culture-specific settings.

DocumentOptions.Events

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

DocumentOptions.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.

DocumentOptions.CalculationEngineType

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

DocumentOptions.RealTimeData

Provides access to options for real-time data (RTD) function calculation.

DocumentOptions.Protection

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

DocumentOptions.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.

DocumentOptions.Compatibility

Provides access to the following compatibility settings:

DocumentOptions.ZoomMode

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

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

DocumentSettings.Calculation

Provides access to formula calculation options.

DocumentSettings.R1C1ReferenceStyle

Specifies whether a workbook should use the R1C1 reference style.

DocumentSettings.Encryption

Provides access to document encryption options.

DocumentSettings.ShowPivotTableFieldList

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

Workbook Extensions

The WorkbookExtensions class defines extension methods for the Workbook object.

WorkbookExtensions.Append

Appends all worksheets from the specified workbooks to the current workbook.

See how to merge multiple workbooks into one document.

WorkbookExtensions.Clone

Creates a workbook's copy.

See how to clone a workbook.

See Also

File Operations

Work with Workbooks