IWorkbook Interface
A workbook loaded into the Spreadsheet control (for WinForms, WPF, and ASP.NET).
Namespace: DevExpress.Spreadsheet
Assembly: DevExpress.Spreadsheet.v23.2.Core.dll
NuGet Package: DevExpress.Spreadsheet.Core
Declaration
public interface IWorkbook :
ISpreadsheetComponent,
IBatchUpdateable,
IServiceContainer,
IServiceProvider,
ISupportsContentChanged,
IBasePrintable,
ExternalWorkbook,
IDisposableRelated API Members
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.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);
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.
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:
Specifies options used to import documents in different file formats. | |
Specifies options used to export documents to different file formats. | |
Defines the file name and file format used when a workbook is saved. | |
Allows you to disable the following document features: Formulas, Charts, Pictures, Shapes, and Sparklines. | |
Specifies options for worksheet cells. | |
Contains options that allow you to control copy operations. | |
Contains culture-specific settings. | |
Provides access to the WorkbookEventOptions.RaiseOnModificationsViaAPI option that enables you to raise events for changes made in the API. | |
Specifies the calculation mode for the Spreadsheet control. | |
Specifies the computational model used to perform calculations in a workbook. | |
Returns options for real-time data (RTD) function calculation. | |
Allows you to specify the UseStrongPasswordVerifier and SpinCount password protection options. | |
Provides access to the SpreadsheetDataSourceLoadingOptions.CustomAssemblyBehavior option that specifies whether to load a custom assembly with the Entity Framework data context during mail merge. | |
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.
Returns formula calculation options. | |
Specifies whether a workbook should use the R1C1 reference style. | |
Returns document encryption options. | |
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.v23.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:
// 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);