Export to Tabular Formats (CSV, XLS, XLSX)
- 7 minutes to read
The Export to tabular data formats (CSV, XLS, XLSX) feature uses the data engine the ExportType enumeration specifies. The default engine uses the Excel Export Library to perform a Data-Aware export with improved performance and reduced memory usage. The resultant file is optimized for subsequent analysis in Microsoft Excel.
Data-Aware Export
The following features are retained in XLS and XLSX documents:
- Data Grouping - you can collapse/expand groups within a worksheet.
- Fixed Headers - column and row areas stay in sight.
- Cells Formatting - retains the number format.
- Display Text/Value export - you can select whether to export display text or values.
Methods
The following table lists the methods that perform data-aware export:
| Method | Parameter |
|---|---|
| ExportToCsv | Use the PivotGridCsvExportOptions instance instead of the CsvExportOptions object specified in the method signature. The ExportType property should be set to the DataAware value (default). |
| ExportToXls | Use the PivotGridXlsExportOptions instance instead of the XlsExportOptions object type specified in the method signature. The PivotXlsExportOptionsBase.ExportType (inherited DevExpress.XtraPrinting.XlsExportOptionsEx.ExportType ) property should be set to the DataAware value (default). |
| ExportToXlsx | Use the PivotGridXlsxExportOptions instance instead of the XlsxExportOptions object type specified in the method signature. The PivotXlsxExportOptionsBase.ExportType (inherited DevExpress.XtraPrinting.XlsxExportOptionsEx.ExportType ) property should be set to the DataAware value (default). |
Options
The PivotGridXlsExportOptions and PivotGridXlsxExportOptions classes inherits properties from the XlsExportOptionsEx and XlsxExportOptionsEx classes, respectively. The data-aware export engine uses only a subset of available properties. The table below lists the properties which are in effect in the Data Aware mode.
| Property | Description |
|---|---|
| PivotGridXlsExportOptions.ExportColumnAreaHeaders, PivotGridXlsxExportOptions.ExportColumnAreaHeaders | Gets or sets whether to include the column field headers in the exported document. |
| PivotGridXlsExportOptions.ExportFilterAreaHeaders, PivotGridXlsxExportOptions.ExportFilterAreaHeaders | Gets or sets whether to include the filter field headers in the exported document. |
| PivotGridXlsExportOptions.ExportRowAreaHeaders, PivotGridXlsxExportOptions.ExportRowAreaHeaders | Gets or sets whether to include the row field headers in the exported document. |
| XlExportOptionsBase.SheetName | Gets or sets a name of the sheet in the created XLS file to which a document is exported. |
| XlsExportOptionsEx.AllowFixedColumns, XlsxExportOptionsEx.AllowFixedColumns | Gets or sets whether the row area in a Pivot Grid control becomes a frozen pane in the spreadsheet. |
| AllowFixedColumnHeaderPanel, AllowFixedColumnHeaderPanel | Gets or sets whether the column area becomes a frozen pane in the spreadsheet. |
| AllowGrouping, AllowGrouping | Gets or sets whether the data in the spreadsheet are grouped as they are displayed in the Pivot Grid control. |
| ShowPageTitle, ShowPageTitle | Gets or sets whether each printed page of the exported document displays a title. |
| ExportType, ExportType | Gets or sets the export mode. You can switch between WYSIWYG and data-aware export. |
| GroupState, GroupState | Gets or sets whether groups are collapsed or expanded in the exported document. |
| XlExportOptionsBase.RawDataMode | Enables the mode that produces simple tabular data without graphic elements, style and appearance settings. |
| XlExportOptionsBase.TextExportMode | Gets or sets a value indicating whether the cells in the resulting XLS document should use the same formatting as the original document. |
Runtime Customization
Handle the following events to customize exported pivot grid elements individually. Note that these events are in effect only in the Data Aware mode.
| Event | Description |
|---|---|
| XlsExportOptionsEx.AfterAddRow, XlsxExportOptionsEx.AfterAddRow | Fires after a row is added to the output document and allows you to merge cells, insert an image or add an extra row. |
| XlsExportOptionsEx.BeforeExportTable, XlsxExportOptionsEx.BeforeExportTable | Allows you to customize a native Excel table’s settings before export. |
| XlsExportOptionsEx.CustomizeDocumentColumn, XlsExportOptionsEx.CustomizeDocumentColumn | Allows you to customize an individual column in the exported document - change its width, formatting, collapse the group containing the column or hide the column. |
| XlsExportOptionsEx.CustomizeSheetFooter, XlsxExportOptionsEx.CustomizeSheetFooter | Allows you to add a footer to the output document. |
| XlsExportOptionsEx.CustomizeSheetHeader, XlsxExportOptionsEx.CustomizeSheetHeader | Allows you to add a header to the output document. |
| XlsExportOptionsEx.CustomizeSheetSettings, XlsxExportOptionsEx.CustomizeSheetSettings | Allows you to customize the output document’s settings. |
| XlsExportOptionsEx.DocumentColumnFiltering, XlsxExportOptionsEx.DocumentColumnFiltering | Allows you to apply filters to the exported document’s columns. |
| XlsExportOptionsEx.SkipFooterRow, XlsxExportOptionsEx.SkipFooterRow | Allows you to hide certain summary footers (or certain multi-line summary footers’ lines) from the exported document. |
WYSIWYG Export
The WYSIWYG export mode uses the XtraPrinting library engine. It reproduces control elements’ layout in XLS and XLSX formats, omitting data shaping characteristics available in the DataAware export mode.
In CSV format, the WYSIWYG mode can export data area and filter area headers, and add empty columns. When a spreadsheet loads CSV data, it looks similar to the original Pivot Grid control.
WYSIWYG Mode | Data Aware Mode |
|
|
The following table lists the methods that perform the export in WYSIWYG mode:
| Method | Parameter |
|---|---|
| ExportToCsv | Use the PivotGridCsvExportOptions instance instead of the CsvExportOptions object specified in the method signature. The CsvExportOptionsEx.ExportType property should be set to the WYSIWYG value. |
| ExportToXls | Use the PivotGridXlsExportOptions instance instead of the XlsExportOptions object specified in the method signature. The XlsExportOptionsEx.ExportType property should be set to the WYSIWYG value. |
| ExportToXlsx | Use the PivotGridXlsxExportOptions instance instead of the XlsxExportOptions object specified in the method signature. The XlsxExportOptionsEx.ExportType property should be set to the WYSIWYG value. |
Options
The PivotXlsExportOptions and PivotXlsxExportOptions classes inherits properties from the XlsExportOptionsEx and XlsxExportOptionsEx classes, respectively. The WYSIWYG mode (the Printing Library export engine) uses only a subset of available properties. The table below lists the properties which are in effect in the WYSIWYG mode.
| Property | Description |
|---|---|
| XlExportOptionsBase.DocumentOptions | Provides access to options to be embedded as the resulting XLS or XLSX file’s Document Properties. |
| XlExportOptionsBase.EncryptionOptions | Provides access to the XLS and XLSX file encryption options. |
| XlsExportOptions.ExportMode, XlsxExportOptions.ExportMode | Specifies whether the source is exported as a single XLSX file or multiple files, and whether each page is exported as a separate worksheet. |
| XlsExportOptionsEx.ExportType, XlsExportOptionsEx.ExportType | Gets or sets the export type - WYSIWYG or Data Aware. |
| XlExportOptionsBase.IgnoreErrors | Specifies the document errors to be ignored in a resulting Excel file. |
| XlExportOptionsBase.RawDataMode | Enables the mode that produces simple tabular data without graphic elements, style and appearance settings. |
| XlExportOptionsBase.RightToLeftDocument | Gets or sets whether the layout of the resulting XLS document should be aligned to support locales using right-to-left fonts. |
| XlExportOptionsBase.SheetName | Gets or sets a name of the sheet in the created XLS file to which a document is exported. |
| XlExportOptionsBase.ShowGridLines | Gets or sets whether worksheet gridlines are visible in the resulting XLS file. |
| XlsExportOptions.Suppress256ColumnsWarning | Gets or sets a value indicating whether to suppress the exception that raises when trying to export a document to an XLS file with more than 256 columns. |
| XlsExportOptions.Suppress65536RowsWarning | Gets or sets a value indicating whether to suppress the exception that raises when trying to export a document to an XLS file with more than 65,536 rows. |
| XlExportOptionsBase.TextExportMode | Gets or sets a value indicating whether the cells in the resulting XLS document should use the same formatting as the original document. |
Custom Export
You can customize a PivotGrid’s cell in the exported XLS, XLSX, or CSV document with the following events:
- PivotGridCsvExportOptions.CustomizeCell
- Allows you to substitute a cell value in the output document. Only available in data-aware export mode.
- PivotGridXlsExportOptions.CustomizeCell
- Allows you to customize a PivotGrid cell in the exported XLS document. Only available in data-aware export mode.
- PivotGridXlsxExportOptions.CustomizeCell
- Allows you to customize a PivotGrid’s cell in the exported XLSX document. Only available in data-aware export mode.
The following example exports data from a PivotGrid Control to Xlsx format. The PivotGridXlsxExportOptions.CustomizeCell event is used to change the background color of the “Category Name” column in the output document.
//...
void op_CustomizeCell(CustomizePivotCellEventArgs e){
if (e.ValueItemInfo != null && e.ValueItemInfo.Field == fieldCategoryName) {
e.Formatting.BackColor = Color.AliceBlue;
e.Handled = true;
}
}
void Button_Click(object sender, RoutedEventArgs e) {
PivotGridXlsxExportOptions op = new PivotGridXlsxExportOptions();
op.CustomizeCell += op_CustomizeCell;
pivorGrid1.ExportToXlsx(file, op);
}