Import and Export in Spreadsheet for WinForms
- 5 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: Get Started with the WinForms Spreadsheet Control.
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
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. |
| WorkbookSaveOptions | Provides access to options which define the file name and file format that are used when saving and loading the workbook. |
The following code snippet uses the WorkbookSaveOptions.CurrentFileName property to specify the saved document file name (including the path and extension):
public partial class Form1 : Form {
// ...
public Form1() {
InitializeComponent();
spreadsheetControl1.DocumentLoaded += spreadsheetControl1_DocumentLoaded;
spreadsheetControl1.EmptyDocumentCreated += spreadsheetControl1_EmptyDocumentCreated;
//...
}
private void spreadsheetControl1_DocumentLoaded(object sender, EventArgs e) {
IWorkbook workbook = (sender as SpreadsheetControl).Document;
workbook.Options.Save.CurrentFileName =
Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "result.xlsx");
}
private void spreadsheetControl1_EmptyDocumentCreated(object sender, EventArgs e) {
IWorkbook workbook = (sender as SpreadsheetControl).Document;
workbook.Options.Save.CurrentFileName = "newSpreadsheet.xlsx";
}
}
How to: Load and Save a Document
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:
private void Form1_Load(object sender, EventArgs e) {
using (Stream stream = new FileStream("FirstLook.xlsx", FileMode.Open)) {
spreadsheetControl1.LoadDocument(stream);
}
}
//...
private void Form1_FormClosed(object sender, FormClosedEventArgs e) {
spreadsheetControl1.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: Store a Workbook in the Database
- 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.
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
((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) {
//Sets manual formula calculation mode
((OpenXmlDocumentImporterOptions)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
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";
}
}