wpf-405525-controls-and-libraries-spreadsheet-import-and-export.md
This help topic describes how to load and save documents from UI and code, and includes links to code samples.
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.
Use the SpreadsheetControl.DocumentSource property to bind the SpreadsheetControl to a document source from XAML. You can use the following data sources:
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()'}"/>
You can use the CommandProvider class to access commands used in the integrated ribbon.
The code snippet below performs the following actions:
<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>
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";
}
}
Public Partial Class MainWindow
Inherits ThemedWindow
' ...
Private Sub spreadsheetControl1_DocumentLoaded(sender As Object, e As EventArgs) Handles spreadsheetControl1.DocumentLoaded
spreadsheetControl1.Options.Save.CurrentFileName =
Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "result.xlsx")
End Sub
Private Sub spreadsheetControl1_EmptyDocumentCreated(sender As Object, e As EventArgs) Handles spreadsheetControl1.EmptyDocumentCreated
spreadsheetControl1.Options.Save.CurrentFileName = "newSpreadsheet.xlsx"
End Sub
End Class
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);
}
Private Sub ThemedWindow_Loaded(ByVal sender As Object, ByVal e As EventArgs)
Using stream As Stream = New FileStream("Table.xlsx", FileMode.Open)
spreadsheetControl.LoadDocument(stream)
End Using
End Sub
'...
Private Sub ThemedWindow_Unloaded(ByVal sender As Object, ByVal e As FormClosedEventArgs)
spreadsheetControl.SaveDocument("Result.xlsx", DocumentFormat.Xlsx)
End Sub
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:
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.
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;
}
}
Private Sub SpreadsheetControl_BeforeImport(ByVal sender As Object, ByVal e As BeforeImportEventArgs)
If e.DocumentFormat = DocumentFormat.PlainText Then
'Detects plain text encoding automatically
(CType(e.Options, SpreadsheetTxtImportOptions)).AutoDetectEncoding = True
End If
If e.DocumentFormat = DocumentFormat.Doc Then
'Disables removing all leading and trailing whitespace characters
(CType(e.Options, SpreadsheetCsvImportOptions)).TrimBlanks = False
End If
If e.DocumentFormat = DocumentFormat.Html Then
'Sets manual formula calculation mode
(CType(e.Options, SpreadsheetOpenXmlImportOptions)).OverrideCalculationMode = DevExpress.XtraSpreadsheet.Import.CalculationModeOverride.Manual
End If
End Sub
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";
}
}
Private Sub SpreadsheetControl_BeforeExport(ByVal sender As Object, ByVal e As BeforeExportEventArgs)
If e.DocumentFormat = DocumentFormat.Text Then
'Specifies the formula export mode
Dim plainTextOptions As SpreadsheetTxtExportOptions = TryCast(e.Options, SpreadsheetTxtExportOptions)
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 SpreadsheetCsvExportOptions = TryCast(e.Options, SpreadsheetCsvExportOptions)
csvOptions.SkipHiddenColumns = True
csvOptions.WorksheetName = "Results"
End If
End Sub