Workbook Class
A non-visual component that allows you to generate and modify spreadsheet documents.
Namespace: DevExpress.Spreadsheet
Assembly: DevExpress.Docs.v23.2.dll
NuGet Package: DevExpress.Document.Processor
Declaration
public class Workbook :
IWorkbook,
ISpreadsheetComponent,
IBatchUpdateable,
IServiceContainer,
IServiceProvider,
ISupportsContentChanged,
IBasePrintable,
ExternalWorkbook,
IDisposableRelated API Members
The following members return Workbook objects:
Remarks
The Workbook class is the root object of the non-visual spreadsheet engine. It contains properties and methods designed to create, load, modify, print, and save workbooks.
Important
The Workbook class is defined in the DevExpress.Docs.v23.2.dll assembly. Add this assembly to your project to use the Workbook API. You need a license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this assembly in production code.
Workbook Content
Use the following Workbook properties to access basic elements of a spreadsheet document:
- Workbook.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.
- Workbook.ChartSheets
- Returns the document’s collection of chart sheets. A chart sheet contains only a chart and does not have any other data.
- Workbook.Sheets
- Returns the collection that stores all sheets (worksheets and chart sheets) in the document. Use this collection to obtain a sheet of any type.
- Workbook.CustomXmlParts
- Returns the collection that contains custom XML data embedded in the document.
Create a Workbook
Create a Workbook class instance to start working with a spreadsheet document in code.
// Add a reference to the DevExpress.Docs.dll assembly.
using DevExpress.Spreadsheet;
// ...
// Create a new Workbook object.
Workbook workbook = new Workbook();
A new workbook contains one empty worksheet. You can also call the Workbook.CreateNewDocument method to load a blank document into a Workbook instance.
Optimize Performance
To improve performance when you apply multiple modifications to a document, wrap your code in the Workbook.BeginUpdate-Workbook.EndUpdate method calls.
When you finish working with a workbook, you are advised to call the Workbook.Dispose method to release all the resources used by the object. This method call allows you to avoid memory leaks and speed up system performance. You can also work with a Workbook instance within the using statement (Using block in Visual Basic).
Load a Document into a Workbook Instance
Use the Workbook.LoadDocument method to load an existing spreadsheet document from a file, a stream, or a byte array into a Workbook instance. Pass a DocumentFormat enumeration member to the method to define the document format.
When the file format is not specified, the Spreadsheet Document API uses the built-in IFormatDetectorService service implementation to detect the format of the loaded document. If format detection fails, the Workbook.InvalidFormatException event fires.
Handle the Workbook.DocumentLoaded event to determine when you can safely modify the loaded document.
using DevExpress.Spreadsheet;
using System.IO;
// ...
Workbook book1 = new Workbook();
Workbook book2 = new Workbook();
// Load a workbook from a file.
book1.LoadDocument(@"Documents\Document1.xlsx", DocumentFormat.Xlsx);
// Load a workbook from a stream.
using (FileStream stream = new FileStream(@"Documents\Document2.xlsx", FileMode.Open)) {
book2.LoadDocument(stream, DocumentFormat.Xlsx);
}
Note
The Spreadsheet component does not recalculate formulas when you load a document. Calculate all formulas in the workbook after you load the document or change the workbook’s calculation mode.
Load a Document Asynchronously
Use the Workbook.LoadDocumentAsync method to asynchronously load a document from a file, a stream, or a byte array into a Workbook instance. The method overloads allow you to implement progress notifications or cancel the operation if needed.
The following example demonstrates how to asynchronously load a document and cancel the operation if it takes longer than 30 seconds:
using DevExpress.Spreadsheet;
using System;
using System.Threading;
using System.Threading.Tasks;
// ...
static async Task Main(string[] args)
{
var cancellationSource = new CancellationTokenSource(TimeSpan.FromSeconds(30));
var cancellationToken = cancellationSource.Token;
try
{
using (Workbook workbook = new Workbook())
{
await workbook.LoadDocumentAsync("Document.xlsx", DocumentFormat.Xlsx,
cancellationToken);
}
}
catch (OperationCanceledException)
{
Console.WriteLine("Cancelled by timeout.");
}
finally
{
cancellationSource.Dispose();
}
}
Save a Workbook
Use the Workbook.SaveDocument method to save a workbook to a file, a byte array, or a stream.
using DevExpress.Spreadsheet;
using System.IO;
// ...
Workbook workbook = new Workbook();
// ...
// 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);
}
Note
The Spreadsheet component does not recalculate formulas when you save a workbook. Calculate all formulas in the document before the save operation or change the workbook’s calculation mode.
Save a Workbook Asynchronously
Use the Workbook.SaveDocumentAsync method to asynchronously save a spreadsheet document to a file, a byte array, or a stream. The method overloads allow you to implement progress notifications or cancel the operation if needed.
The following code asynchronously saves a document and displays the operation progress in the console window:
using DevExpress.Spreadsheet;
using System;
using System.Threading.Tasks;
// ...
static async Task Main(string[] args)
{
using (Workbook workbook = new Workbook())
{
// Edit document content.
// ...
// Save the document.
await workbook.SaveDocumentAsync("SavedDocument.xlsx", DocumentFormat.Xlsx,
new Progress<int>((progress) => Console.WriteLine($"{progress}%")));
}
}
Export a Workbook to PDF
The Workbook.ExportToPdf method allows you to export a workbook or individual worksheets to PDF format. You can pass a PdfExportOptions class instance to this method to define export options.
using DevExpress.Spreadsheet;
using DevExpress.XtraPrinting;
// ...
using (Workbook workbook = new Workbook())
{
// ...
// 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 the specified worksheets to PDF.
workbook.ExportToPdf(@"Documents\Output_Worksheets.pdf", options, "Sheet1", "Sheet2");
}
Note
The Spreadsheet component does not recalculate formulas when you export a workbook. Calculate all formulas in the workbook before the export operation or change the workbook’s calculation mode.
Export a Workbook to PDF Asynchronously
Use the Workbook.ExportToPdfAsync method to asynchronously export a workbook or individual worksheets to PDF format. The method overloads allow you to define export options, implement progress notifications, or cancel the operation if needed.
The following code asynchronously saves a document as a PDF and displays the operation progress in the console window:
using DevExpress.Spreadsheet;
using DevExpress.XtraPrinting;
using System;
using System.Threading.Tasks;
// ...
static async Task Main(string[] args)
{
using (Workbook workbook = new Workbook())
{
// ...
// Specify export options.
var options = new PdfExportOptions();
options.DocumentOptions.Author = "John Smith";
options.ImageQuality = PdfJpegImageQuality.Medium;
// Export the workbook to PDF.
await workbook.ExportToPdfAsync("Output_Workbook.pdf", options,
new Progress<int>((progress) => Console.WriteLine($"{progress}%")));
}
}
Export a Workbook to HTML
The Workbook.ExportToHtml method allows you to export an individual worksheet or a specific cell range to HTML format. This method can accept an HtmlDocumentExporterOptions parameter that allows you to define export options.
The following example exports a given cell range in the first worksheet to HTML:
using DevExpress.Spreadsheet;
using DevExpress.XtraSpreadsheet.Export;
// ...
using (Workbook workbook = new Workbook())
{
// ...
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);
}
Note
The Spreadsheet component does not recalculate formulas when you export a workbook. Calculate all formulas in the workbook before the export operation or change the workbook’s calculation mode.
Export a Workbook to HTML Asynchronously
Use the Workbook.ExportToHtmlAsync method to asynchronously export an individual worksheet or a specific cell range to HTML format. The method overloads allow you to define export options, implement progress notifications, or cancel the operation if needed.
The following code asynchronously saves the first worksheet as HTML and displays the operation progress in the console window:
using DevExpress.Spreadsheet;
using System;
using System.Threading.Tasks;
// ...
static async Task Main(string[] args)
{
using (Workbook workbook = new Workbook())
{
// ...
// Export the first worksheet to HTML.
await workbook.ExportToHtmlAsync("Output_Workbook.html", 0,
new Progress<int>((progress) => Console.WriteLine($"{progress}%")));
}
}
Clone a Workbook
Use the WorkbookExtensions.Clone method to create a workbook copy. The method’s copyFormulas parameter allows you to specify whether to copy the source workbook’s formulas or to replace formulas with their calculated values.
// Create a new Workbook object.
Workbook workbook = new Workbook();
workbook.LoadDocument("Document.xlsx", DocumentFormat.Xlsx);
// Create a copy of the Workbook object.
Workbook copy = workbook.Clone(false);
Merge Multiple Workbooks
Use the following methods to combine data from multiple workbooks into a single document:
- WorkbookExtensions.Append
- Appends all worksheets from the specified workbooks to the current workbook.
- Workbook.Merge
- Combines specified workbooks into a new document.
Copy Document Content to Another Workbook
// Create the first workbook.
Workbook book1 = new Workbook();
book1.LoadDocument("Document1.xlsx", DocumentFormat.Xlsx);
// Create the second workbook.
Workbook book2 = new Workbook();
book2.LoadDocument("Document2.xlsx", DocumentFormat.Xlsx);
// Copy all worksheets from "Document1" to "Document2".
book2.Append(book1);
Merge Workbooks into a New Document
// Create the first workbook.
Workbook book1 = new Workbook();
book1.LoadDocument("Document1.xlsx", DocumentFormat.Xlsx);
// Create the second workbook.
Workbook book2 = new Workbook();
book2.LoadDocument("Document2.xlsx", DocumentFormat.Xlsx);
// Combine two documents into a new document.
Workbook result = Workbook.Merge(book1, book2);
Protect the Workbook Structure
Use the Workbook.Protect method to protect the workbook structure with a password. When a workbook is protected, other users cannot add, move, delete, rename, or hide existing worksheets or view hidden worksheets.
using DevExpress.Spreadsheet;
// ...
using (Workbook workbook = new Workbook())
{
// Protect the workbook structure with a password.
if (!workbook.IsProtected)
workbook.Protect("password", true, false);
}
Call the Workbook.Unprotect method to remove protection.
Encrypt a Workbook
To encrypt your document with a password, call the Workbook.SaveDocument or Workbook.SaveDocumentAsync 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;
// ...
using (Workbook workbook = new Workbook())
{
// Specify encryption settings.
var encryptionSettings = new EncryptionSettings();
encryptionSettings.Type = EncryptionType.Strong;
encryptionSettings.Password = "password";
// Save the document.
workbook.SaveDocument("Document.xlsx", DocumentFormat.Xlsx, encryptionSettings);
}
Write-Protect a Workbook
The Workbook.DocumentSettings.WriteProtection property allows you to specify write-protection options for a workbook to prevent modifications from unauthorized users. These options include:
WriteProtectionOptions.ReadOnlyRecommended—Allows you to make the document read-only.
WriteProtectionOptions.SetPassword—Specifies a password used to modify a workbook.
using(var workbook = new Workbook())
{
var wpOptions = workbook.DocumentSettings.WriteProtection;
wpOptions.SetPassword("Password");
wpOptions.UserName = "John Smith";
workbook.SaveDocument("WriteProtectedDocument.xlsx");
}
When users open this workbook in Microsoft® Excel®, it prompts them to enter a password to modify the document.
Print a Workbook
Call the Workbook.Print method to print the entire workbook or individual sheets. 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;
// ...
using (Workbook workbook = new Workbook())
{
// 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";
// Print specific worksheets in the document.
workbook.Print(printerSettings, "Sheet1", "Sheet2");
}
You can handle the Workbook.BeforePrintSheet event to cancel printing for specific worksheets.
Note
The Spreadsheet component does not recalculate formulas when you print a workbook. Calculate all formulas in the workbook before you print it or change the workbook’s calculation mode.
Calculate Formulas in a Workbook
The default calculation mode for a Workbook is Manual. This mode implies that the Spreadsheet component does not recalculate formulas when you load, save, export, or print a document. Call the Workbook.Calculate or Workbook.CalculateFull method to recalculate all formulas in the workbook.
using (Workbook workbook = new Workbook()) {
// Load a document.
workbook.LoadDocument("Document.xlsx", DocumentFormat.Xlsx);
// Calculate formulas in the document.
workbook.Calculate();
// ...
}
Change Calculation Mode
Use the Workbook.Options.CalculationMode property to specify when to calculate formulas in a Workbook.
The following calculation modes are available:
Manual (default)—Recalculates formulas only on demand (after the Calculate method call). It allows you to improve document generation speed for large workbooks with multiple formulas.
UseDocumentSettings —Uses the calculation mode specified in the loaded document (the Workbook.DocumentSettings.Calculation.Mode property value).
Automatic —Recalculates formulas each time a cell value, formula, or defined name changes.
using (Workbook workbook = new Workbook()) {
// Change the calculation mode.
workbook.Options.CalculationMode = WorkbookCalculationMode.UseDocumentSettings;
// ...
// Load a document.
// ...
}
View and Edit Document Properties
Built-In Document Properties
Document properties are metadata associated and stored with a workbook. Use the Workbook.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
Some of the document properties are updated automatically when a document is created (DocumentProperties.Author, DocumentProperties.Created), last modified and saved (DocumentProperties.LastModifiedBy, DocumentProperties.Modified), or printed (DocumentProperties.Printed).
Custom Document Properties
Use the DocumentProperties.Custom property to create 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.
Load Document Properties Only
The Workbook.LoadDocumentProperties method allows you to load the document properties without loading the workbook itself. You can use retrieved metadata to search for a specific document, to organize files, or to collect statistics.
// Load metadata from a document.
ReadOnlyDocumentProperties docProperties = workbook.LoadDocumentProperties("Document.xlsx");
string title = docProperties.Title;
DateTime date = docProperties.Modified;
string author = docProperties.Author;
// Display document metadata in the console window.
Console.WriteLine($"Document Title: {title}, Last Modified: {date}, Author: {author}");
Document Settings
Use the Workbook.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 performed via the API. | |
Specifies the calculation mode for a | |
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. | |
Returns compatibility settings that allow you to turn off the new layout engine and its features. | |
Returns localization options for a workbook. | |
Contains workbook layout options. Use the WorkbookLayoutOptions.Dpi property to specify the resolution (DPI) for document layout generation. | |
Specifies whether to apply the SheetView.Zoom setting to all worksheet views or the current view only. |
Other workbook settings are available from the Workbook.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. |