IWorkbook Interface

A workbook loaded into the Spreadsheet control (for WinForms, WPF, and ASP.NET).

Assembly : DevExpress.Spreadsheet.v25.2.Core.dll

NuGet Package : DevExpress.Spreadsheet.Core

Declaration

C#
VB.NET

csharp

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

Public Interface IWorkbook
    Inherits ISpreadsheetComponent,
             IBatchUpdateable,
             IServiceContainer,
             IServiceProvider,
             ISupportsContentChanged,
             IBasePrintable,
             ExternalWorkbook,
             IDisposable

The following members return IWorkbook objects:

Library	Related API Members
WinForms Controls	SpreadsheetControl.Document
WPF Controls	SpreadsheetControl.Document
Office File API	ChartSheet.Workbook
IWorkbookMergeResult.Workbook
Worksheet.Workbook
ASP.NET Web Forms Controls	ASPxSpreadsheet.Document
SpreadsheetInitializeDocumentEventArgs.Document
ASP.NET MVC Extensions	SpreadsheetExtension.GetCurrentDocument(String)

Remarks

The IWorkbook interface contains properties and methods designed to create, load, modify, print, and save spreadsheet documents in code.

Workbook Content

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

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

IWorkbook.ChartSheets

Returns the collection of chart sheets in a document. A chart sheet contains only a chart and does not have any other data.

IWorkbook.SheetsReturns the collection that stores all sheets (worksheets and chart sheets) in the document. Use this collection to obtain different sheet types.IWorkbook.CustomXmlPartsReturns the collection that contains custom XML data embedded in the document.

Access a Spreadsheet Document

Use the SpreadsheetControl.Document property to obtain a workbook loaded into the Spreadsheet control.

C#
VB.NET

csharp

using DevExpress.Spreadsheet;
// ...

// Access an IWorkbook object.
IWorkbook workbook = spreadsheetControl.Document;

Imports DevExpress.Spreadsheet
' ...

' Access an IWorkbook object.
Dim workbook As IWorkbook = spreadsheetControl.Document

Load a Workbook into the Spreadsheet Control

Use the SpreadsheetControl.LoadDocument or IWorkbook.LoadDocument method to load an existing document from a file, a stream, or byte array into the Spreadsheet control. Pass a DocumentFormat enumeration member to the method to define the document format.

When the file format is not specified, the Spreadsheet control uses the built-in IFormatDetectorService service implementation to detect the format of the loaded document. If it cannot detect the format, the SpreadsheetControl.InvalidFormatException event fires.

Handle the SpreadsheetControl.DocumentLoaded event to ensure the document is loaded and you can modify it.

C#
VB.NET

csharp

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

// Access a workbook.
IWorkbook workbook = spreadsheetControl.Document;

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

Imports DevExpress.Spreadsheet
Imports System.IO
' ...

' Access a workbook.
Dim workbook As IWorkbook = spreadsheetControl.Document

' Load a document from a stream.
Using stream As New FileStream("Document.xlsx", FileMode.Open)
    workbook.LoadDocument(stream, DocumentFormat.Xlsx)
End Using

Save a Workbook

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

C#
VB.NET

csharp

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

// Access a workbook.
IWorkbook workbook = spreadsheetControl.Document;
// ...
// 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);
}

Imports DevExpress.Spreadsheet
Imports System.IO
' ...

' Access a workbook.
Dim workbook As IWorkbook = spreadsheetControl.Document
' ...
' Edit document content.
' ...

' Save the modified document to the stream.
Using stream As New FileStream("Documents\SavedDocument1.xlsx", FileMode.Create, FileAccess.ReadWrite)
    workbook.SaveDocument(stream, DocumentFormat.Xlsx)
End Using

Export a Workbook to PDF

The SpreadsheetControl.ExportToPdf and IWorkbook.ExportToPdf methods allow you to export a workbook to PDF format. You can pass a PdfExportOptions class instance to these methods to define export options.

C#
VB.NET

csharp

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

IWorkbook workbook = spreadsheetControl.Document
// ...
// 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);

Imports DevExpress.Spreadsheet
Imports DevExpress.XtraPrinting
' ...

Dim workbook As IWorkbook = spreadsheetControl.Document
' ...
' Specify export options.
Dim options As 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 a Workbook to HTML

The SpreadsheetControl.ExportToHtml and IWorkbook.ExportToHtml methods allow you to export an individual worksheet or specific cell range to HTML format. These methods can accept an HtmlDocumentExporterOptions parameter that allows you to define export options.

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

C#
VB.NET

csharp

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

IWorkbook workbook = spreadsheetControl.Document
// ...
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);

Imports DevExpress.Spreadsheet
Imports DevExpress.XtraSpreadsheet.Export
' ...

Dim workbook As IWorkbook = spreadsheetControl.Document
' ...
Dim options As 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)

Protect the Workbook Structure

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

C#
VB.NET

csharp

using DevExpress.Spreadsheet;
// ...

IWorkbook workbook = spreadsheetControl.Document
// Protect the workbook structure with a password.
if (!workbook.IsProtected)
    workbook.Protect("password", true, false);

Imports DevExpress.Spreadsheet
' ...

Dim workbook As IWorkbook = spreadsheetControl.Document
' Protect the workbook structure with a password.
If Not workbook.IsProtected Then
    workbook.Protect("password", True, False)
End If

Call the IWorkbook.Unprotect method to remove protection.

Encrypt a Workbook

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

C#
VB.NET

csharp

using DevExpress.Spreadsheet;
// ...

IWorkbook workbook = spreadsheetControl.Document
// Specify encryption settings.
var encryptionSettings = new EncryptionSettings();
encryptionSettings.Type = EncryptionType.Strong;
encryptionSettings.Password = "password";

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

Imports DevExpress.Spreadsheet
' ...

Dim workbook As IWorkbook = spreadsheetControl.Document
' Specify encryption settings.
Dim encryptionSettings As New EncryptionSettings()
encryptionSettings.Type = EncryptionType.Strong
encryptionSettings.Password = "password"

' Save the document.
workbook.SaveDocument("Document.xlsx", DocumentFormat.Xlsx, encryptionSettings)

Print a Workbook

Call the SpreadsheetControl.Print method to print a workbook. 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.

C#
VB.NET

csharp

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

// Load a document into Spreadsheet control.
spreadsheetControl.Document.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";

// Specify that the first three pages should be printed.
printerSettings.PrintRange = PrintRange.SomePages;
printerSettings.FromPage = 1;
printerSettings.ToPage = 3;

// Print the document.
spreadsheetControl.Print(printerSettings);

Imports DevExpress.Spreadsheet
Imports System.Drawing.Printing
' ...

' Load a document into Spreadsheet control.
spreadsheetControl.Document.LoadDocument("Document.xlsx")

' Create an object that contains printer settings.
Dim printerSettings As New PrinterSettings()

' Define the printer to use.
printerSettings.PrinterName = "Microsoft Print to PDF"
printerSettings.PrintToFile = True
printerSettings.PrintFileName = "PrintedDocument.pdf"

' Specify that the first three pages should be printed.
printerSettings.PrintRange = PrintRange.SomePages
printerSettings.FromPage = 1
printerSettings.ToPage = 3

' Print the document.
spreadsheetControl.Print(printerSettings)

View and Edit Document Properties

Built-In Document Properties

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

C#
VB.NET

csharp

// 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;

' 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.
Dim worksheet As 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

Certain document properties are updated automatically when a document is created (DocumentProperties.Author, DocumentProperties.Created), modified, saved (DocumentProperties.LastModifiedBy, DocumentProperties.Modified), or printed (DocumentProperties.Printed).

Custom Document Properties

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

C#
VB.NET

csharp

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

' Set the custom document properties.
workbook.DocumentProperties.Custom("Revision") = 3
workbook.DocumentProperties.Custom("Completed") = True
workbook.DocumentProperties.Custom("Published") = Date.Now

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

Document Settings

Use the SpreadsheetControl.Options or IWorkbook.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

Contains culture-specific settings.

| |

DocumentOptions.Events

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

| |

DocumentOptions.CalculationMode

Specifies the calculation mode for the Spreadsheet control.

| |

DocumentOptions.CalculationEngineType

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

| |

DocumentOptions.RealTimeData

Returns 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.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 IWorkbook.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.

Extension Methods

The WorkbookExtensions class contains the following extension methods for the IWorkbook interface:

WorkbookExtensions.Clone—Creates a workbook copy.
WorkbookExtensions.Append—Appends all worksheets from the specified workbooks to the current workbook.

Important

To enable workbook extensions, add a reference to the DevExpress.Docs.v25.2.dll assembly and import the DevExpress.Spreadsheet namespace into your code with a using directive ( Imports in Visual Basic). You need a license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this assembly in production code.

Clone a Workbook

The following example demonstrates how to create a copy of the Spreadsheet control’s document:

C#
VB.NET

csharp

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

// Create a copy of the document loaded into the Spreadsheet control.
Workbook copy = spreadsheetControl.Document.Clone();

' Add a reference to the DevExpress.Docs.dll assembly.
Imports DevExpress.Spreadsheet
' ... 

' Create a copy of the document loaded into the Spreadsheet control.
Dim copy As Workbook = spreadsheetControl.Document.Clone()

Merge Multiple Workbooks Into One Document

The following example demonstrates how to copy all worksheets from a specific workbook to the Spreadsheet control’s document:

C#
VB.NET

csharp

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

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

// Load a document into the Spreadsheet control.
IWorkbook book2 = spreadsheetControl.Document;
book2.LoadDocument("Document2.xlsx", DocumentFormat.Xlsx);

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

' Add a reference to the DevExpress.Docs.dll assembly.
Imports DevExpress.Spreadsheet
' ...

' Create a new Workbook object.
Dim book1 As New Workbook()
book1.LoadDocument("Document1.xlsx", DocumentFormat.Xlsx)

' Load a document into the Spreadsheet control.
Dim book2 As IWorkbook = spreadsheetControl.Document
book2.LoadDocument("Document2.xlsx", DocumentFormat.Xlsx)

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

Extension Methods

Clone()

Clone(Boolean)

Merge(WorkbookMergeOptions, IWorkbook[])

Append(IWorkbook[])

IWorkbook Interface

IWorkbook Interface

Declaration

Related API Members

Remarks

Workbook Content

Access a Spreadsheet Document

Load a Workbook into the Spreadsheet Control

Save a Workbook

Export a Workbook to PDF

Export a Workbook to HTML

Protect the Workbook Structure

Encrypt a Workbook

Print a Workbook

View and Edit Document Properties

Built-In Document Properties

Custom Document Properties

Document Settings

Extension Methods

Clone a Workbook

Merge Multiple Workbooks Into One Document

Extension Methods