Export to XLS and XLSX Formats
- 10 minutes to read
Two export modes are supported when exporting data from Grid Views and Banded Grid Views to MS Excel format (*.XLS(x)).
- Data-aware Export - This is the default export mode, optimized for subsequent analysis of grid data within Microsoft Excel. Various data shaping options that are applied within the grid are retained in output XLS-XLSX documents.
WYSIWYG Export - In this export mode, the layout of grid cells is retained in resulting XLS-XLSX documents. Specific data shaping options are not retained, compared to the data-aware export.
Data from other grid Views (Card View, Layout View, Tile View and WinExplorer View) is always exported using the WYSIWYG engine.
This document describes the data-aware export in detail and shows how to turn it off.
Data-aware Export
The data-aware export which is the default is optimized for subsequent analysis of grid data within Microsoft Excel. The following data shaping options that are applied within the grid control are retained in the output XLS-XLSX documents.
- Data grouping - with the capability to collapse/expand groups within a worksheet.
- Data sorting and filtering - allowing end-users to display relevant data in the desired order.
- Totals and group summaries - with the capability to modify/change formulas.
-
Note
See a note in the Data-aware Export Limitations and Specifics section below.
- Lookup values for columns that use Lookup and Combo-box editors.
- Fixed columns.
Expressions for expression-based unbound columns.
Note
See a note in the Data-aware Export Limitations and Specifics section below.
The following document was exported to Excel from a Grid control with grouping and summaries enabled.
Data-aware Export Limitations and Specifics
- A limited number of functions used in expression-based Excel Style Format Rules and expression-based unbound columns can only be exported to XLS(X) format in data-aware export mode. To learn if a function can be exported to XLS(X) format, refer to the Expression Operators, Functions, and Constants document.
- Images, charts, gauges and RTF text contained in grid cells are not exported, although the export document will contain headers corresponding to these grid columns.
- Preview sections are not exported.
- Detail Views (in master-detail mode) are not exported.
- Appearances applied to rows and individual cells are not exported. Only column appearances (GridColumn.AppearanceCell) are exported.
- Custom painting, alpha blending and color gradient features are not supported when grid data is exported.
- Columns anchored to the grid control’s right edge are not fixed in the export output (even if a corresponding setting is enabled).
- Custom summaries implemented using grid events are exported as plain text.
- Columns in the export output have the width specified by the GridColumn.Width property. The value retrieved by this property may not match the actual column width in the grid. This takes place when the column auto-width feature (see GridOptionsView.ColumnAutoWidth) is enabled.
- Custom text provided with the ColumnView.CustomColumnDisplayText event is not exported.
- For lookup and image-combobox columns, display values are exported by default. Use the RepositoryItem.ExportMode property of GridColumn.ColumnEdit objects to export edit values instead of display values.
- Regardless of the AllowSortingAndFiltering setting, the sorting and filtering functionality is disabled in the output document when data is exported from Advanced Banded Grid Views (these Views allow columns to be arranged one under another).
- The GridView.RowHeight and AdvBandedGridOptionsView.RowAutoHeight settings do not affect row height in the output worksheet.
- For TimeSpan columns with no explicit formatting applied, the column values are exported using the “[h]:mm:ss” format string.
- When exporting a numeric column with the display format string set to “P”, the values in the output document are exported using the “0.00\%“ format string.
When exporting data, you should also take into consideration the limitations of MS Excel (row count, column count, etc.). See the following online article to learn more:
Export Methods
The following methods allow you to export data to MS Excel format.
| Method | Description |
|---|---|
| GridControl.ExportToXls | Exports the data displayed by the GridControl.DefaultView (the GridControl.MainView or the currently maximized detail View) to a file in XLS format. |
| GridControl.ExportToXlsx | Exports the data displayed by the GridControl.DefaultView (the GridControl.MainView or the currently maximized detail View) to the specified file in XLSX (MS Excel 2007) format. |
Choosing Export Mode
The ExportSettings.DefaultExportType static property allows you to choose the required export mode used by default by all ExportToXls and ExportToXlsx methods. This property can be set to the ExportType.DataAware or ExportType.WYSIWYG value.
The required export mode can be specified with each call of the ExportToXls(x) methods (when using these method overloads with an options parameter). Create an XlsExportOptionsEx object (or an XlsxExportOptionsEx object), set its ExportType property to the ExportType.DataAware or ExportType.WYSIWYG value and pass this object to the ExportToXls(x) method.
Note that the DataAware export mode is not supported for the Card View, Layout View, Tile View and WinExplorer View.
Export Settings
Base export settings can be accessed from a grid control View’s GridView.OptionsPrint (BandedGridView.OptionsPrint) object. It provides a set of settings that specify which grid elements need to be included in the export/print output. Note that a few settings exposed by the OptionsPrint object are only supported in data-aware export mode. These are marked with special notes (see the GridOptionsPrint class member list), and are also listed in the table below. Other settings provided by the OptionsPrint objects are supported only in the WYSIWYG export mode.
Additional export settings can be customized when calling the ExportToXls(x) method overloads that take an options parameter. This parameter can be set to an XlsExportOptionsEx object (when using the ExportToXls method) and to an XlsxExportOptionsEx object (when using the ExportToXlsx method).
Note
A few options that are inherited by the XlsExportOptionsEx and XlsxExportOptionsEx classes from their base class are not supported by the data-aware export engine.
The following table lists the base and additional options, grouped by categories that are supported in data-aware export mode.
Worksheet Settings | |
|---|---|
Gets or sets a name of the sheet in the created XLS file, to which a document is exported. | |
Gets or sets whether a title is displayed for each print preview page of the exported document. | |
Gets or sets the export document’s culture, which defines the formatting settings for numeric and date-time data. | |
Gets or sets whether to suppress the warning that appears if the resulting XLSX file has more than 16,384 columns. | |
Gets or sets whether to suppress the warning that appears if the resulting XLSX file has more than 1,048,576 rows. | |
Layout | |
Gets or sets whether data is exported in regular mode or as a native Excel table. | |
Gets or sets whether cell merging is enabled for band headers in the exported document. | |
Gets or sets whether cell merging is enabled in the exported document. If the AllowCellMerge property is set to Default and cell merging is enabled in the grid View (see GridOptionsView.AllowCellMerge), the cell merging feature is enabled in the export output as well. If cell merging is disabled in the grid View, the Xls(x)ExportOptionsEx.AllowCellMerge property is not in effect. | |
Gets or sets whether left fixed columns enabled in a grid control are fixed in the exported document. | |
Gets or sets whether the column header panel is anchored to the top of the export document, and thus is not scrolled vertically. | |
Gets or sets how bands and columns are arranged in the output worksheet (when exporting from Banded Views). | |
Gets or sets whether band headers are visible in the exported document. This option is in effect when exporting from Banded Grid Views if the XlsExportOptionsEx.BandedLayoutMode option is set to Default. | |
Gets or sets whether column headers are visible in the exported document. | |
Gets or sets whether to display the view footer in the print/export output. When exporting to Xls(x) format, this property is supported in both WYSIWYG and Data-aware export mode. | |
Gets or sets whether to display column headers in the print/export output. When exporting to Xls(x) format, this property is supported in both WYSIWYG and Data-aware export mode. | |
Gets or sets whether horizontal grid lines are printed/exported. When exporting to Xls(x) format, this property is supported in both WYSIWYG and Data-aware export mode. | |
Gets or sets whether vertical grid lines are displayed in the print/export output. When exporting to Xls(x) format, this property is supported in both WYSIWYG and Data-aware export mode. | |
Data Grouping, Sorting and Filtering | |
Gets or sets whether data groups are exported from the source control to the output document. | |
Gets or sets whether the sorting and filtering functionality is enabled for columns in the exported document. | |
Gets or sets the expanded state of data groups in the output document. | |
Gets or sets whether group summaries are enabled in the exported document. | |
Summaries | |
Gets or sets whether total summaries are enabled in the exported document. | |
Gets or sets whether group summaries are enabled in the exported document. | |
Gets or sets whether the Count summary function exported to Excel format takes into account all cells or only non-blank cells. | |
Cell Values | |
Gets or sets whether hyperlinks are exported. | |
Gets or sets whether sparklines are exported. | |
Gets or sets whether lookup values of combo-box and lookup columns are exported. | |
Gets or sets whether the source control cells with Empty Strings are exported as Blank cells in Excel format. | |
Gets or sets whether column values or column expressions are exported for unbound (calculated) columns. | |
Appearance and Styles | |
Gets or sets whether conditional formatting rules applied to columns are preserved in the exported document. | |
Gets or sets whether cell formatting (cell appearance and borders) is applied to entire sheet columns (faster) or to individual sheet cells within the range of exported rows (slower). | |
Gets or sets whether horizontal grid lines are printed/exported. When exporting to Xls(x) format, this property is supported in both WYSIWYG and Data-aware export mode. | |
Gets or sets whether vertical grid lines are displayed in the print/export output. When exporting to Xls(x) format, this property is supported in both WYSIWYG and Data-aware export mode. | |
Export-aware Events
The following events help you set up the export document generation.
Event | Description |
|---|---|
When exporting to XLSX format, this event fires immediately after a row has been added to the output document. | |
Allows you to customize settings of a native Excel table prior to export. This event is in effect when the XlsxExportOptionsEx.LayoutMode property is set to Table. | |
When exporting to XLSX format, this event allows you to customize a cell in the output document. | |
When exporting to XLSX format, this event allows you to customize the header in the output document. | |
When exporting to XLSX format, this event allows you to customize the footer in the output document. | |
When exporting to XLSX format, this event allows you to customize the output document’s settings. | |
Allows you to apply filters to columns in the exported document. | |
Fires repeatedly while the data is being exported. | |
Allows you to hide certain summary footers (or certain lines of multi-line summary footers) from the export output. |