Import and Export Spreadsheet Documents
- 5 minutes to read
This document describes how to load and save spreadsheet documents in different formats.
Supported Formats
The Spreadsheet Document API supports the following file formats for importing and exporting data:
- XLSX
- Microsoft Office Open XML format - the default file format starting with Microsoft Excel 2007.
- XLSM
- Microsoft Office Open XML macro-enabled format (limited support - macros cannot be executed or modified).
- XLSB
- Microsoft Excel 2007-2010 binary file format (BIFF12).
- XLS
- Microsoft Excel 97-2003 binary file format (import/export).
- Microsoft Excel 5.0/95 binary file format (import only).
- Microsoft Excel 2 Worksheets / BIFF2 binary file format (import only).
- XLTX
- Microsoft Office Open XML template file.
- XLTM
- Microsoft Office Open XML macro-enabled template file (limited support - macros cannot be executed or modified).
- XLT
- Microsoft Excel 97-2003 template file.
- XML
- XML Spreadsheet 2003 file format (limited support - pivot tables cannot be loaded or saved).
- CSV
- Comma Separated Values - the plain text format that uses commas as separators between cell values.
- TXT
- Tab Delimited Text - the plain text format that uses tab characters as separators between cell values.
- PDF (export only)
- Portable Document Format.
- HTML (export only)
- Web Page.
Initiate Load and Save Operations
The Workbook class includes the following methods to load or save a document and specify its options:
| Member | Description |
|---|---|
| Workbook.LoadDocument | Loads a document from a file or stream. You can specify the document format with the DocumentFormat enum. |
| Workbook.LoadDocumentAsync | Asynchronously loads a document from a file or stream. You can specify the document format with the DocumentFormat enum. |
| Workbook.CreateNewDocument | Creates a new empty workbook. |
| Workbook.SaveDocument | Saves the control’s document to a file or stream and specifies the document’s format and encryption settings. |
| Workbook.SaveDocumentAsync | Asynchronously saves the control’s document to a file or stream and specifies the document’s format and encryption settings. |
| DocumentOptions.Import | Provides access to options used to import workbooks in different formats. |
| DocumentOptions.Save | Provides access to the workbook save options. |
How to: Load and Save a Document
The following code snippet loads a document from a stream and saves the result to a file:
// Add a reference to the DevExpress.Docs.dll assembly.
using DevExpress.Spreadsheet;
using System.IO;
// ...
Workbook workbook = new Workbook();
// Load a workbook from the stream.
using (FileStream stream = new FileStream("Documents\\Document.xlsx", FileMode.Open)) {
workbook.LoadDocument(stream, DocumentFormat.Xlsx);
workbook.SaveDocument("Documents\\SavedDocument.xlsx", DocumentFormat.Xlsx);
}
Refer to the following examples for more code samples:
Note
We do not recommend that you use the DocumentFormat.Undefined field as the SaveDocument method parameter. Otherwise, the document is saved with an invalid format.
Basic Format-Specific API
The table below lists the document formats the Workbook class supports, and the API used to set format-specific import and export options. You can specify these options in the Workbook.BeforeImport or Workbook.BeforeExport event handlers.
Perform Actions Before Import
The following code snippet handles the Workbook.BeforeImport event for different document formats:
private void Workbook_BeforeImport(object sender, BeforeImportEventArgs e) {
if (e.DocumentFormat == DocumentFormat.Text) {
//Detects plain text encoding automatically
((TxtDocumentImporterOptions)e.Options).AutoDetectEncoding = true;
}
if (e.DocumentFormat == DocumentFormat.Csv) {
//Disables removing all leading and trailing whitespace characters
((CsvDocumentImporterOptions)e.Options).TrimBlanks = false;
}
if (e.DocumentFormat == DocumentFormat.OpenXml) {
((OpenXmlDocumentImporterOptions)e.Options).IgnoreDeletedText = true;
}
}
Perform Actions Before Export
The following code snippet specifies export options for different formats in the Workbook.BeforeExport event handler:
private void Workbook_BeforeExport(object sender, BeforeExportEventArgs e) {
if (e.DocumentFormat == DocumentFormat.Text) {
//Specifies the formula export mode
TxtDocumentExporterOptions plainTextOptions = e.Options as TxtDocumentExporterOptions;
plainTextOptions.FormulaExportMode = DevExpress.XtraSpreadsheet.Export.FormulaExportMode.CalculatedValue;
}
if (e.DocumentFormat == DocumentFormat.Csv) {
//Specifies CSV document hidden column behavior and the worksheet name
CsvDocumentExporterOptions csvOptions = e.Options as CsvDocumentExporterOptions;
csvOptions.SkipHiddenColumns = True;
csvOptions.WorksheetName = "Results";
}
}