Import and Export in Spreadsheet for WPF
- 6 minutes to read
This help topic describes how to load and save documents from UI and code, and includes links to code samples.
Built-in UI
Users can click the Open button on the File ribbon tab (or press Ctrl+O) to invoke the Open dialog. Then, they can select the file and click Open.
On the File ribbon tab, users can click the Save button (or press Ctrl+S) to save the changes made to the current document. They can click Save As (or press F12) to invoke the Save As dialog used to save a document copy as a new file, and specify the document’s location, name, and format.
Refer to the following topic for more information on how to add a ribbon UI to the Spreadsheet Control: Create a Simple Spreadsheet Application.
Tip
You can prevent users from using the Create, Load or Save commands. To disable or hide these commands from the Ribbon UI and pop-up menu, set the SpreadsheetBehaviorOptions.CreateNew, SpreadsheetBehaviorOptions.Open and SpreadsheetBehaviorOptions.Save/SpreadsheetBehaviorOptions.SaveAs properties to DocumentCapability.Disabled.
Load and Save Operations in Code
XAML
Use the SpreadsheetControl.DocumentSource property to bind the SpreadsheetControl to a document source from XAML. You can use the following data sources:
- System.IO.Stream (both seekable and non-seekable);
- System.String (recognized as the file path);
- System.Byte[] array;
- System.Uri;
- SpreadsheetDocumentSource instance.
The code snippet below loads the Document.xlsx file in XAML. Note that the Document.xlsx file’s Build Action should be set to Content, and Copy to Output Directory should be set to Copy if newer (or Copy always):
<dxsps:SpreadsheetControl Name="spreadsheetControl1" DocumentSource="Document.xlsx"/>
You can also use the DXBinding extension to call a method that returns a valid document source directly in XAML, as in the following code:
<dxsps:SpreadsheetControl Name="spreadsheetControl1"
DocumentSource="{DXBinding $local:SourceHelper.GetDocumentSource()'}"/>
Commands
You can use the CommandProvider class to access commands used in the integrated ribbon.
The code snippet below performs the following actions:
- Creates the Comments ribbon group and adds it to the Insert ribbon tab.
- Creates the Comment button and adds it to the Comments ribbon group.
- Uses the CommandProvider class to bind the Comment button to the existing spreadsheet command that inserts a new comment.
<dxsps:SpreadsheetControl.RibbonActions>
<!--Create the Comments group on the Insert tab and add the Comment button to it.-->
<dxb:InsertAction Index="5" ContainerName="{x:Static dxsps:DefaultBarItemNames.RibbonPage_Insert}">
<dxr:RibbonPageGroup Caption="Comments">
<dxb:BarButtonItem Content="Comment" LargeGlyph="{dxsps:SpreadsheetSvgImage Name=InsertComment}"
Command="{Binding RelativeSource={RelativeSource Self}, Path=(dxsps:SpreadsheetControl.Spreadsheet).CommandProvider.InsertComment}"/>
</dxr:RibbonPageGroup>
</dxb:InsertAction>
</dxsps:SpreadsheetControl.RibbonActions>
API
The SpreadsheetControl includes the following methods to load or save the document and specify its options.
| Member | Description |
|---|---|
| SpreadsheetControl.LoadDocument | Loads a document from a file or stream. Optionally, you can specify the document format with the DocumentFormat enum. |
| ISpreadsheetControl.LoadDocument | Loads a document from a file or stream. Optionally, you can specify the document format with the DocumentFormat enum. |
| SpreadsheetControl.CreateNewDocument | Creates and loads a new empty workbook. |
| ISpreadsheetComponent.CreateNewDocument | Creates and loads a new empty workbook. |
| SpreadsheetControl.SaveDocument | Saves the control’s document to a file or stream and specifies the document’s format and encryption settings. |
| ISpreadsheetComponent.SaveDocument | Saves the control’s document to a file or stream and specifies the document’s format and encryption settings. |
| SpreadsheetSaveOptions | Contains options that define the file name and file format that are used when saving and loading the workbook. |
The following code snippet uses the SpreadsheetSaveOptions.CurrentFileName property to specify the saved document file name (including the path and extension):
<dxsps:SpreadsheetControl x:Name="spreadsheetControl1"
DocumentSource="Document.xlsx"
DocumentLoaded="spreadsheetControl1_DocumentLoaded"
EmptyDocumentCreated="spreadsheetControl1_EmptyDocumentCreated"/>
public partial class MainWindow : ThemedWindow {
// ...
private void spreadsheetControl1_DocumentLoaded(object sender, EventArgs e) {
spreadsheetControl1.Options.Save.CurrentFileName = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "result.xlsx");
}
private void spreadsheetControl1_EmptyDocumentCreated(object sender, EventArgs e) {
spreadsheetControl1.Options.Save.CurrentFileName = "newSpreadsheet.xlsx";
}
}
Example
The following code snippet loads the document from a file stream when the form is opened and saves the result to the file when the form is closed:
Refer to the following topics for more code samples:
private void ThemedWindow_Loaded(object sender, EventArgs e) {
using (Stream stream = new FileStream("Table.xlsx", FileMode.Open)) {
spreadsheetControl.LoadDocument(stream);
}
}
// ...
private void ThemedWindow_Unloaded(object sender, FormClosedEventArgs e) {
spreadsheetControl.SaveDocument("Result.xlsx", DocumentFormat.Xlsx);
}
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.
Refer to the following examples to get code samples for file operations:
- How to: Load a Document into SpreadsheetControl
- How to: Save a Document to a File
- How to: Export a Workbook to PDF
- How to: Export a Document to HTML
Basic Format-Specific API
The table below lists document formats the SpreadsheetControl supports, and the API used to set format-specific import and export options. You can specify these options in the SpreadsheetControl.BeforeImport or SpreadsheetControl.BeforeExport event handlers.
| Format | Accessed By | Import Options | Export Options |
|---|---|---|---|
| Plain Text | DocumentFormat.Text | SpreadsheetTxtImportOptions | SpreadsheetTxtExportOptions |
| CSV | DocumentFormat.Csv | SpreadsheetCsvImportOptions | SpreadsheetCsvExportOptions |
| Xls | DocumentFormat.Xls | SpreadsheetXlsImportOptions |  |
| Xlsm | DocumentFormat.Xlsm | SpreadsheetXlsmImportOptions | |
| OpenXML | DocumentFormat.OpenXml | SpreadsheetOpenXmlImportOptions | |
Perform Actions Before Import
The following code snippet handles the SpreadsheetControl.BeforeImport event for different document formats:
private void SpreadsheetControl_BeforeImport(object sender, BeforeImportEventArgs e) {
if (e.DocumentFormat == DocumentFormat.Text) {
//Detects plain text encoding automatically
((SpreadsheetTxtImportOptions)e.Options).AutoDetectEncoding = true;
}
if (e.DocumentFormat == DocumentFormat.Csv) {
//Disables removing all leading and trailing whitespace characters
((SpreadsheetCsvImportOptions)e.Options).TrimBlanks = false;
}
if (e.DocumentFormat == DocumentFormat.OpenXml) {
//Sets manual formula calculation mode
((SpreadsheetOpenXmlImportOptions)e.Options).OverrideCalculationMode = DevExpress.XtraSpreadsheet.Import.CalculationModeOverride.Manual;
}
}
Perform Actions Before Export
The following code snippet specifies export options for different formats in the SpreadsheetControl.BeforeExport event handler:
private void SpreadsheetControl_BeforeExport(object sender, BeforeExportEventArgs e) {
if (e.DocumentFormat == DocumentFormat.Text) {
//Specifies the formula export mode
SpreadsheetTxtExportOptions plainTextOptions = e.Options as SpreadsheetTxtExportOptions;
plainTextOptions.FormulaExportMode = DevExpress.XtraSpreadsheet.Export.FormulaExportMode.CalculatedValue;
}
if (e.DocumentFormat == DocumentFormat.Csv) {
//Specifies CSV document hidden column behavior and the worksheet name
SpreadsheetCsvExportOptions csvOptions = e.Options as SpreadsheetCsvExportOptions;
csvOptions.SkipHiddenColumns = True;
csvOptions.WorksheetName = "Results";
}
}