[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Collapse]Data Grid
   [Expand]Getting Started With Data Grid and Views
   [Expand]Data Binding
    Unbound Columns
   [Expand]Views
   [Expand]Data Editing and Validation
   [Expand]Grouping
   [Expand]Sorting
   [Expand]Filter and Search
   [Expand]Summaries
   [Expand]Focus and Selection Handling
    Formatting Cell Values
   [Expand]Master-Detail Relationships
   [Expand]Asynchronous Image Load
   [Collapse]Export and Printing
     Printing Overview
     Member Table: Printing
     Export Overview
     Export Methods and Settings
     Export to XLS and XLSX Formats
     Advanced Grid Printing and Exporting
   [Expand]Appearance and Conditional Formatting
    Split Presentation
    Row Preview Sections
   [Expand]Scrolling
   [Expand]Batch Modifications
   [Expand]Hit Information
    Hints
    Popup Menus
   [Expand]Saving and Restoring Layouts
   [Expand]Visual Elements
   [Expand]Design-Time Features
   [Expand]Examples
   [Expand]End-User Capabilities
    Included Components
  [Expand]Vertical Grid
  [Expand]Property Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Expand]Spreadsheet
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Export to XLS and XLSX Formats

The Grid Control supports two modes (engines) when exporting data from Grid Views and Banded Grid Views to MS Excel format (*.XLS(x)): Data-aware export and WYSIWYG export. This topic describes the data-aware export in more detail.

  • Data-aware export - The new export engine features improved performance and memory usage. Choose this mode if you need to process and analyze exported data in MS Excel. Data shaping options in an exported control (for example, data collapsing/expanding, summary formulas, format rules; see below for a complete list) are persisted in the output XLS-XLSX document. Grid elements' layout may not match the original layout in the exported document.
  • WYSIWYG export - This export engine retains the grid elements' layout in the output document. However, the data shaping options are not retained. For instance, the grid control exports summary values as text strings instead of formulas.

    The Grid Control's Card View, Layout View, Tile View and WinExplorer View always export their data in WYSIWYG mode.

    You can also use the PrintableComponentLink and XtraReports library's methods to export a Grid Control to MS Excel format. These approaches always employ the WYSIWYG export engine.

Expanded Data-aware Export

The data-aware export engine retains the following grid data shaping features in the resulting XLS-XLSX documents:

  • Data grouping (you can collapse/expand groups within a worksheet).
  • Column sorting and filtering.
  • Total and group summary formulas.
  • Conditional cell formatting (format rules)
    Note

    Limitations apply. See a note in the Export Specifics section below.

  • Lookup values (for Lookup and Combo-box columns).
  • Fixed columns.
  • Expressions (for expression-based unbound columns).
    Note

    Limitations apply. See a note in the Export Specifics section below.

The following image illustrates an MS Excel worksheet containing data from a sample Grid control that has grouping and summaries enabled:

Expanded Data-aware Export Limitations and Specifics

Grid Features that are not Exported

  • Images, charts, gauges and RTF text contained in grid cells.

    If a Grid Control contains a column with this data, the resulting worksheet contains a column with a header, but without any data.

  • Preview sections
  • Detail Views (in master-detail mode)
  • Appearance settings applied to rows and individual cells.

    The resulting document does contain appearance settings applied to column cells (GridColumn.AppearanceCell).

  • Custom painting, alpha blending, and color gradient customizations.
  • Custom text the ColumnView.CustomColumnDisplayText event provides.

Export Specifics

  • Some 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. Refer to the Criteria Language Syntax document for details on which functions can be exported to XLS(X) format.
  • Columns anchored to the grid control's right edge are not fixed in the output document.
  • Custom summaries implemented using grid events are exported as plain text.
  • The GridColumn.Width property specifies columns' width in XLS(X) documents. The GridColumn.Width property value may not match the actual column width in column auto-width mode (see GridOptionsView.ColumnAutoWidth).
  • 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.
  • The sorting and filtering functionality is disabled in the resulting document when exporting data from Advanced Banded Grid Views (these Views arrange columns one under another). The export engine ignores the XlsExportOptionsEx.AllowSortingAndFiltering and XlsxExportOptionsEx.AllowSortingAndFiltering settings for Advanced Banded Grid Views.
  • The GridView.RowHeight and AdvBandedGridOptionsView.RowAutoHeight settings do not affect row height in the resulting worksheets.
  • Column values are exported using the "[h]:mm:ss" format string for TimeSpan columns with no explicit formatting.
  • Values of numeric columns with a "P" display format string are exported using the "0.00\\%" format string.
  • MS Excel specifications and limits (see this online article to learn about the limiting factors of MS Excel, e.g., row count and column count).

Expanded Export Methods

The following grid methods allow you to export data to MS Excel format:

Method Description
GridControl.ExportToXls Exports a View's data to a file/stream in XLS format.
GridControl.ExportToXlsx Exports a View's data to a file/stream in XLSX format.

Expanded Choosing Export Mode

The ExportSettings.DefaultExportType static property allows you to choose the default export mode when you use the Grid Control's ExportToXls and ExportToXlsx methods.

You can also specify the required export mode with each ExportToXls(x) method call when you use these method overloads with an options parameter. Create an XlsExportOptionsEx object (or XlsxExportOptionsEx object), set its ExportType property to ExportType.DataAware or ExportType.WYSIWYG and pass this object to the ExportToXls(x) method.

Expanded Export Settings

You can use the following settings to customize the resulting documents' data:

  • Base settings - available from the GridView.OptionsPrint and BandedGridView.OptionsPrint objects.

    These properties specify which grid elements should be included in the resulting document.

  • Advanced settings - available by calling the ExportToXls(x) method overloads that take an options parameter.

    Set the options parameter to an XlsExportOptionsEx (when using the ExportToXls method) or XlsxExportOptionsEx object (when using the ExportToXlsx method).

The following table lists these options, grouped by categories:

Worksheet Settings
XlExportOptionsBase.SheetName Gets or sets a name of the sheet in the created XLS file to which a document is exported.
XlsExportOptionsEx.ShowPageTitle
XlsxExportOptionsEx.ShowPageTitle
Gets or sets whether a title is displayed for each print preview page of the exported document.
XlsExportOptionsEx.DocumentCulture
XlsxExportOptionsEx.DocumentCulture
Gets or sets the export document's culture which defines numeric and date-time data formatting settings.
XlsxExportOptionsEx.SuppressMaxColumnsWarning Gets or sets whether to suppress the warning that appears if the resulting XLSX file has more than 16,384 columns.
XlsxExportOptionsEx.SuppressMaxRowsWarning Gets or sets whether to suppress the warning that appears if the resulting XLSX file has more than 1,048,576 rows.
Layout
XlsExportOptionsEx.LayoutMode
XlsxExportOptionsEx.LayoutMode
Gets or sets whether data is exported in regular mode or as a native Excel table.
XlsExportOptionsEx.AllowBandHeaderCellMerge
XlsxExportOptionsEx.AllowBandHeaderCellMerge
Gets or sets whether cell merging is enabled for band headers in the exported document.
XlsExportOptionsEx.AllowCellMerge
XlsxExportOptionsEx.AllowCellMerge
Gets or sets whether cell merging is enabled in the exported document.

The cell merging feature is active in the resulting worksheet if the Xls(x)ExportOptionsEx.AllowCellMerge property is set to Default, and the Grid View allows cell merging (see GridOptionsView.AllowCellMerge).

If cell merging is disabled in the Grid View, the Xls(x)ExportOptionsEx.AllowCellMerge property is not in effect, and cell merging is disabled in the resulting worksheet.
XlsExportOptionsEx.AllowFixedColumns
XlsxExportOptionsEx.AllowFixedColumns
Gets or sets whether left fixed columns enabled in a grid control are fixed in the exported document.
XlsExportOptionsEx.AllowFixedColumnHeaderPanel
XlsxExportOptionsEx.AllowFixedColumnHeaderPanel
Gets or sets whether the column header panel is anchored to the top of the export document and not scrolled vertically.
XlsExportOptionsEx.BandedLayoutMode
XlsxExportOptionsEx.BandedLayoutMode
Gets or sets how bands and columns are arranged in the output worksheet (when exporting from Banded Views).
XlsExportOptionsEx.ShowBandHeaders
XlsxExportOptionsEx.ShowBandHeaders
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.
XlsExportOptionsEx.ShowColumnHeaders
XlsxExportOptionsEx.ShowColumnHeaders
Gets or sets whether column headers are visible in the exported document.
GridOptionsPrint.PrintFooter Gets or sets whether to display the view footer in the print/export output.
GridOptionsPrint.PrintHeader Gets or sets whether to display column headers in the print/export output.
GridOptionsPrint.PrintHorzLines Gets or sets whether horizontal grid lines are printed/exported.
GridOptionsPrint.PrintVertLines Gets or sets whether vertical grid lines are displayed in the print/export output
Data Grouping, Sorting and Filtering
XlsExportOptionsEx.AllowGrouping
XlsxExportOptionsEx.AllowGrouping
Gets or sets whether data groups are exported from the source control to the output document.
XlsExportOptionsEx.AllowSortingAndFiltering
XlsxExportOptionsEx.AllowSortingAndFiltering
Gets or sets whether columns' sorting and filtering functionality is enabled in the exported document.
XlsExportOptionsEx.GroupState
XlsxExportOptionsEx.GroupState
Gets or sets data groups' expanded state in the exported document.
XlsExportOptionsEx.ShowGroupSummaries
XlsxExportOptionsEx.ShowGroupSummaries
Gets or sets whether group summaries are enabled in the exported document.
Summaries
XlsExportOptionsEx.ShowTotalSummaries
XlsxExportOptionsEx.ShowTotalSummaries
Gets or sets whether total summaries are enabled in the exported document.
XlsExportOptionsEx.ShowGroupSummaries
XlsxExportOptionsEx.ShowGroupSummaries
Gets or sets whether group summaries are enabled in the exported document.
XlsExportOptionsEx.SummaryCountBlankCells
XlsxExportOptionsEx.SummaryCountBlankCells
Gets or sets whether the Count summary function takes all cells into account or only non-blank cells when exporting to Excel format.
Cell Values
XlsExportOptionsEx.AllowHyperLinks
XlsxExportOptionsEx.AllowHyperLinks
Gets or sets whether hyperlinks are exported.
XlsExportOptionsEx.AllowSparklines
XlsxExportOptionsEx.AllowSparklines
Gets or sets whether sparklines are exported.
XlsExportOptionsEx.AllowLookupValues
XlsxExportOptionsEx.AllowLookupValues
Gets or sets whether combo-box and lookup columns' lookup values are exported.
XlsExportOptionsEx.SuppressEmptyStrings
XlsxExportOptionsEx.SuppressEmptyStrings
Gets or sets whether the source control cells with Empty Strings are exported as Blank cells in Excel format.
XlsExportOptionsEx.UnboundExpressionExportMode
XlsxExportOptionsEx.UnboundExpressionExportMode
Gets or sets whether column values or column expressions are exported for unbound (calculated) columns.
Appearance and Styles
XlsExportOptionsEx.AllowConditionalFormatting
XlsxExportOptionsEx.AllowConditionalFormatting
Gets or sets whether conditional formatting rules applied to columns are preserved in the exported document.
XlsExportOptionsEx.ApplyFormattingToEntireColumn
XlsxExportOptionsEx.ApplyFormattingToEntireColumn
Gets or sets whether cell formatting (cell appearance and borders) is applied to the entire sheet columns (faster) or individual sheet cells in the range of exported rows (slower).
GridOptionsPrint.PrintHorzLines Gets or sets whether horizontal grid lines are printed/exported.
GridOptionsPrint.PrintVertLines Gets or sets whether vertical grid lines are displayed in the print/export output.

Expanded Export-aware Events

You can use the following events to customize the resulting document while it is being generated:

Event Description
XlsExportOptionsEx.AfterAddRow
XlsxExportOptionsEx.AfterAddRow
Fires immediately after a row is added to the output document.
XlsExportOptionsEx.BeforeExportTable
XlsxExportOptionsEx.BeforeExportTable
Allows you to customize a native Excel table's settings before export.
XlsExportOptionsEx.CustomizeCell
XlsxExportOptionsEx.CustomizeCell
Allows you to customize a cell in the output document.
XlsExportOptionsEx.CustomizeSheetHeader
XlsxExportOptionsEx.CustomizeSheetHeader
Allows you to customize the header in the output document.
XlsExportOptionsEx.CustomizeSheetFooter
XlsxExportOptionsEx.CustomizeSheetFooter
Allows you to customize the footer in 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.ExportProgress
XlsxExportOptionsEx.ExportProgress
Fires repeatedly while the data is being exported.
XlsExportOptionsEx.SkipFooterRow
XlsxExportOptionsEx.SkipFooterRow
Allows you to hide certain summary footers (or certain multi-line summary footers' lines) from the exported document.

Is this topic helpful?​​​​​​​