.NET Framework 4.5.2+
.NET Framework 4.5.2+
.NET Standard 2.0+
Row

IWorkbook Interface

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

Namespace: DevExpress.Spreadsheet

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

Declaration

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

The following members accept/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.

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.

Chart sheets

IWorkbook.Sheets
Returns the collection that stores all sheets (worksheets and chart sheets) in the document. Use this collection to obtain different sheet types.
IWorkbook.CustomXmlParts
Returns 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.

using DevExpress.Spreadsheet;
// ...

// Access an IWorkbook object.
IWorkbook workbook = 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.

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);
}

Save a Workbook

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

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);
}

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.

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

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:

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

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.

using DevExpress.Spreadsheet;
// ...

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

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.

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

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.

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

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

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

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.

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

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:

Important

To enable workbook extensions, add a reference to the DevExpress.Docs.v21.2.dll assembly and import the DevExpress.Spreadsheet namespace into your code with a using directive (Imports in Visual Basic). You need an active 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:

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

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:

// 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);
See Also