Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[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
   [Expand]Data Binding
   [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
    Scrolling
   [Expand]Batch Modifications
   [Expand]Hit Information
    Hints
   [Expand]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]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]Document Server
[Expand]Reporting
[Expand]Report 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

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.

Expanded 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.
  • Excel Style Format Rules
    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:

Expanded Export Methods

The following methods allow you to export data to MS Excel format.

Method Description
GridControl.ExportToXls Exports the data displayed by the DefaultView (the MainView or the currently maximized detail View) to a file in XLS format.
GridControl.ExportToXlsx Exports the data displayed by the DefaultView (the MainView or the currently maximized detail View) to the specified stream in XLSX (MS Excel 2007) format using the specified options.

Expanded 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.

Expanded 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
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 the formatting settings for numeric and date-time data.
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.
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.
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 thus is 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 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. When exporting to Xls(x) format, this property is supported in both WYSIWYG and Data-aware export mode.
GridOptionsPrint.PrintHeader 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.
GridOptionsPrint.PrintHorzLines 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.
GridOptionsPrint.PrintVertLines 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
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 the sorting and filtering functionality is enabled for columns in the exported document.
XlsExportOptionsEx.GroupState
XlsxExportOptionsEx.GroupState
Gets or sets the expanded state of data groups in the output 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 exported to Excel format takes into account all cells or only non-blank cells.
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 lookup values of combo-box and lookup columns 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 entire sheet columns (faster) or to individual sheet cells within the range of exported rows (slower).
GridOptionsPrint.PrintHorzLines 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.
GridOptionsPrint.PrintVertLines 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.

Expanded Export-aware Events

The following events help you set up the export document generation.

Event Description
XlsExportOptionsEx.AfterAddRow
XlsxExportOptionsEx.AfterAddRow
When exporting to XLSX format, this event fires immediately after a row has been added to the output document.
XlsExportOptionsEx.BeforeExportTable
XlsxExportOptionsEx.BeforeExportTable
Allows you to customize settings of a native Excel table prior to export. This event is in effect when the LayoutMode property is set to Table.
XlsExportOptionsEx.CustomizeCell
XlsxExportOptionsEx.CustomizeCell
When exporting to XLSX format, this event allows you to customize a cell in the output document.
XlsExportOptionsEx.CustomizeSheetHeader
XlsxExportOptionsEx.CustomizeSheetHeader
When exporting to XLSX format, this event allows you to customize the header in the output document.
XlsExportOptionsEx.CustomizeSheetFooter
XlsxExportOptionsEx.CustomizeSheetFooter
When exporting to XLSX format, this event allows you to customize the footer in the output document.
XlsExportOptionsEx.CustomizeSheetSettings
XlsxExportOptionsEx.CustomizeSheetSettings
When exporting to XLSX format, this event allows you to customize the output document's settings.
XlsExportOptionsEx.DocumentColumnFiltering
XlsxExportOptionsEx.DocumentColumnFiltering
Allows you to apply filters to columns in the exported document.
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 lines of multi-line summary footers) from the export output.

How would you rate this topic?​​​​​​​