windowsforms-17733-controls-and-libraries-data-grid-export-and-printing-export-to-xls-and-xlsx-formats.md
The Grid Control supports two modes (engines) to export data from Grid Views and Banded Grid Views in XLS, XLSX, and CSV formats:
The new export engine features improved performance and memory usage. Choose this mode if you need to process and analyze exported data in Microsoft Excel.
Data shaping options in an exported control (for example, data collapsing/expanding, summary formulas, and format rules) are persisted in the output XLS/XLSX document. The layout of grid elements may not match the original layout in the exported document.
When exporting to CSV format, the grid’s data shaping options are not included in the export document, which simplifies subsequent analysis and processing of text data.
You can export a detail View if you maximize this View.
This export engine retains the layout of grid elements 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.
In WYSIWYG export mode, the export of detail Views is supported (see GridOptionsPrint.PrintDetails).
The Grid Control’s Card, Layout, Tile, and WinExplorer Views always export their data in WYSIWYG mode.
You can also use the PrintableComponentLink and XtraReports library‘s methods to export a Grid Control to Microsoft Excel format. These techniques always use the WYSIWYG export engine.
Use the following methods to export the grid control’s data:
| 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. |
| GridControl.ExportToCsv | Exports a View’s data to a file/stream in CSV format. |
string path = "output.xlsx";
gridControl1.ExportToXlsx(path);
// Open the created XLSX file with the default application.
Process.Start(path);
Dim path As String = "output.xlsx"
GridControl1.ExportToXlsx(path)
' Open the created XLSX file with the default application.
Process.Start(path)
The static ExportSettings.DefaultExportType property allows you to choose the default export mode when you use the Grid Control’s ExportToXls, ExportToXlsx, and ExportToCsv methods.
You can also specify the required export mode with each ExportToXls (or ExportToXlsx) 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 (or ExportToXlsx) method.
The data-aware export engine retains the following grid data shaping features in the resulting XLS/XLSX documents:
The following image illustrates an Microsoft Excel worksheet that contains data from a sample Grid control with group and summary features:
Charts, gauges, and RTF text in grid cells.
Detail Views (in master-detail mode) when you export the master View. You can export a detail View when you maximize this View.
Appearance settings applied to rows and individual cells.
Custom painting, alpha blending, and color gradient customizations. Refer to the following KB article for available workarounds: Is custom drawing ignored when printing or exporting?.
Some functions used in expression-based Excel Style Format Rules and expression-based unbound columns can only be exported to XLS (or XLSX) format in data-aware export mode. Refer to the following topic for details on which functions can be exported to XLS (or XLSX) format: Criteria Language Syntax.
Use one of the following techniques to export custom text supplied by the ColumnView.CustomColumnDisplayText event:
Columns anchored to the grid control’s right edge are not fixed in the resulting document.
Custom summaries implemented with grid events are exported as plain text.
The GridColumn.Width property specifies the width of columns in XLS (or XLSX) documents. The GridColumn.Width property value may not match the actual column width in column auto width mode (see GridOptionsView.ColumnAutoWidth).
Cell images are exported to .xlsx files only (XlsxExportOptionsEx.AllowCellImages).
Lookup and image-combo box columns: the default behavior is to export display values. Use the RepositoryItem.ExportMode property of GridColumn.ColumnEdit objects to export edit values instead of display values. Images are not exported.
The sort and filter functionality is disabled in the resulting document when data is exported 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 setting does 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 with the “0.00%“ format string.
The ColumnApplyTo setting is not supported for FormatConditionRuleIconSet. The FormatConditionRuleIconSet rule should be applied to a column specified by the GridFormatRule.Column property.
Consider the recommendations for the worksheet name described here: XlExportOptionsBase.SheetName
Read the following article for more information about Microsoft Excel limitations (for example, row count and column count): Excel specifications and limits.
You can use the following settings to customize the resulting document’s data:
Base settings — accessible with the GridView.OptionsPrint and BandedGridView.OptionsPrint properties.
Advanced settings — Call the ExportToXls (or ExportToXlsx ) method overloads and pass an options parameter to access these settings.
The following table lists these options, grouped by categories:
|
Worksheet Settings
| | --- | |
|
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 format 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
| | --- | |
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 merge is enabled for band headers in the exported document.
| |
XlsExportOptionsEx.AllowCellMerge
XlsxExportOptionsEx.AllowCellMerge
|
Gets or sets whether cell merge is enabled in the exported document.
The cell merge feature is enabled in the resulting worksheet if the Xls(x)ExportOptionsEx.AllowCellMerge property is set to Default , and the Grid View allows cell merge (see GridOptionsView.AllowCellMerge).
If cell merge is disabled in the Grid View, the Xls(x)ExportOptionsEx.AllowCellMerge property is not in effect, and cell merge is disabled in the resulting worksheet.
| |
XlsExportOptionsEx.AllowFixedColumns
XlsxExportOptionsEx.AllowFixedColumns
|
Gets or sets whether columns anchored to the grid control’s left edge 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 data is exported 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 data is exported 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.
| |
|
Gets or sets whether to display the view footer in the printed/exported output.
| |
|
Gets or sets whether to display column headers in the printed/exported output.
| |
GridOptionsPrint.PrintHorzLines
|
Gets or sets whether to display horizontal grid lines in the printed/exported output.
| |
GridOptionsPrint.PrintVertLines
|
Gets or sets whether to display vertical grid lines in the printed/exported 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 column sort and filter functionality is enabled in the exported document.
| |
XlsxExportOptionsEx.GroupState
|
Gets or sets the expanded state of data groups 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 data is exported to Excel format.
| |
Cell Values/Custom Text
| | --- | |
XlsExportOptionsEx.AllowHyperLinks
XlsxExportOptionsEx.AllowHyperLinks
|
Gets or sets whether hyperlinks are exported.
| |
XlsExportOptionsEx.AllowSparklines
XlsxExportOptionsEx.AllowSparklines
|
Gets or sets whether sparklines are exported.
| |
XlsxExportOptionsEx.AllowCellImages
|
Gets or sets whether to export cell images.
| |
XlsExportOptionsEx.AllowLookupValues
XlsxExportOptionsEx.AllowLookupValues
|
Gets or sets whether the 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.
| |
XlExportOptionsBase.TextExportMode
|
Set this property to Text to export a cell’s display text (not the cell’s values) from all columns. In this mode, the export engine takes into account the custom text you specify for any column using the Data Grid’s ColumnView.CustomColumnDisplayText event.
| |
|
To export a single column ‘s display text (not the column’s values), assign an in-place editor to the column and set the editor’s RepositoryItem.ExportMode property to DisplayText. In this mode, the custom text specified in the target column by the ColumnView.CustomColumnDisplayText event is exported.
| |
Appearance and Styles
| | --- | |
XlsExportOptionsEx.AllowConditionalFormatting
XlsxExportOptionsEx.AllowConditionalFormatting
|
Gets or sets whether conditional format 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 or individual sheet cells. Formatting algorithms applied to sheet columns are faster than those applied to individual cells.
| |
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 printed/exported output.
|
string path = "output.xlsx";
//Customize export options
(gridControl1.MainView as GridView).OptionsPrint.PrintHeader = false;
XlsxExportOptionsEx advOptions = new XlsxExportOptionsEx();
advOptions.AllowGrouping = DevExpress.Utils.DefaultBoolean.False;
advOptions.ShowTotalSummaries = DevExpress.Utils.DefaultBoolean.False;
advOptions.SheetName = "Exported from Data Grid";
gridControl1.ExportToXlsx(path, advOptions);
// Open the created XLSX file with the default application (for example, Microsoft Excel)
System.Diagnostics.Process.Start(path);
Dim path As String = "output.xlsx"
' Customize export options
CType(GridControl1.MainView, GridView).OptionsPrint.PrintHeader = False
Dim advOptions As XlsxExportOptionsEx = New XlsxExportOptionsEx()
advOptions.AllowGrouping = DevExpress.Utils.DefaultBoolean.False
advOptions.ShowTotalSummaries = DevExpress.Utils.DefaultBoolean.False
advOptions.SheetName = "Exported from Data Grid"
GridControl1.ExportToXlsx(path, advOptions)
' Open the created XLSX file with the default application (for example, Microsoft Excel)
System.Diagnostics.Process.Start(path)
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.CustomizeDocumentColumn
XlsxExportOptionsEx.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.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 lines of multi-line summary footers) from the exported document.
|
See Also