officefileapi-405569-spreadsheet-document-api-import-and-export.md
This document describes how to load and save spreadsheet documents in different 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
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.
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. |
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);
}
' Add a reference to the DevExpress.Docs.dll assembly.
Imports DevExpress.Spreadsheet
Imports System.IO
' ...
Dim workbook As New Workbook()
' Load a workbook from the stream.
Using stream As New FileStream("Documents\Document.xlsx", FileMode.Open)
workbook.LoadDocument(stream, DocumentFormat.Xlsx)
workbook.SaveDocument("Documents\SavedDocument.xlsx", DocumentFormat.Xlsx)
End Using
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.
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.
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;
}
}
Private Sub Workbook_BeforeImport(ByVal sender As Object, ByVal e As BeforeImportEventArgs)
If e.DocumentFormat = DocumentFormat.PlainText Then
'Detects plain text encoding automatically
(CType(e.Options, TxtDocumentImporterOptions)).AutoDetectEncoding = True
End If
If e.DocumentFormat = DocumentFormat.Doc Then
'Disables removing all leading and trailing whitespace characters
(CType(e.Options, CsvDocumentImporterOptions)).TrimBlanks = False
End If
If e.DocumentFormat = DocumentFormat.Html Then
(CType(e.Options, OpenXmlDocumentImporterOptions)).IgnoreDeletedText = True
End If
End Sub
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";
}
}
Private Sub Workbook_BeforeExport(ByVal sender As Object, ByVal e As BeforeExportEventArgs)
If e.DocumentFormat = DocumentFormat.Text Then
'Specifies the formula export mode
Dim plainTextOptions As TxtDocumentExporterOptions = TryCast(e.Options, TxtDocumentExporterOptions)
plainTextOptions.FormulaExportMode = DevExpress.XtraSpreadsheet.Export.FormulaExportMode.CalculatedValue
End If
If e.DocumentFormat = DocumentFormat.Csv Then
'Specifies CSV document hidden column behavior and the worksheet name
Dim csvOptions As CsvDocumentExporterOptions = TryCast(e.Options, CsvDocumentExporterOptions)
csvOptions.SkipHiddenColumns = True
csvOptions.WorksheetName = "Results"
End If
End Sub