[Expand]General Information
[Collapse]WinForms Controls
  .NET Core Support
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications, and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Property Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Gantt Control
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Collapse]Spreadsheet Document
     Workbook
     Worksheets
     Cells and Cell Ranges
     Rows and Columns
     Comments
     Measure Units
    Supported Formats
   [Expand]Cell Basics
   [Expand]Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Tables
   [Expand]Charts and Graphics
    Printing
    Events
   [Expand]Mail Merge
    Protection
    Operation Restrictions
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Keyboard Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
  [Expand]Sunburst Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

Workbook

Use the SpreadsheetControl.Document property to obtain an IWorkbook object that allows you to modify a workbook loaded in the Spreadsheet control.

Expanded Workbook Content

Worksheets and Chart Sheets

A workbook consists of one or more Worksheets stored in the IWorkbook.Worksheets collection. When you create a SpreadsheetControl instance, it contains a workbook with 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.

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

The IWorkbook.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 IWorkbook.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 IWorkbook.Functions property to access the WorkbookFunctions (see 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 IWorkbook.CustomFunctions or IWorkbook.GlobalCustomFunctions collection.

See how to create a custom function.

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 CustomFunctionExportMode.CalculatedValue.

Cell Styles

A workbook contains a collection of styles used to format cell appearance. Use the IWorkbook.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 IWorkbook.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 XML-based file formats (.xlsx, .xlsm, .xltx, .xltm). This data is named custom XML part and stored in the IWorkbook.CustomXmlParts collection. Use the collection's members to create and modify custom XML parts.

Expanded Operations with Workbooks

Expanded Document Settings

Use the SpreadsheetControl.Options or IWorkbook.Options property to get access to 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: WorkbookCapabilitiesOptions.Formulas, WorkbookCapabilitiesOptions.Charts, WorkbookCapabilitiesOptions.Pictures, WorkbookCapabilitiesOptions.Shapes, WorkbookCapabilitiesOptions.Sparklines, and WorkbookCapabilitiesOptions.Undo.

DocumentOptions.Cells

Specifies options for worksheet cells.

DocumentOptions.Copy

Contains options that allow you to control copy operations.

DocumentOptions.Clipboard

Contains options for clipboard operations.

DocumentOptions.Events

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

DocumentOptions.Culture

Provides culture-specific settings.

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 SpreadsheetProtectionOptions.UseStrongPasswordVerifier and SpreadsheetProtectionOptions.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 SpreadsheetCompatibilityOptions.EnableLegacyLayoutEngine compatibility setting that allows you to disable the new layout calculation engine for the Spreadsheet control.

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 IWorkbook.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 Field List for pivot tables in a workbook.

Expanded Workbook Extensions

The WorkbookExtensions class defines extension methods for the IWorkbook 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.

To enable workbook extensions, add a reference to the DevExpress.Docs.v19.2.dll library and import the DevExpress.Spreadsheet namespace into your code with a using directive (Imports in Visual Basic).

Note

You need an active license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use these extension methods in production code.

Expanded See Also

Is this topic helpful?​​​​​​​