Data-Aware Export

• Oct 01, 2024
• 4 minutes to read

Run Demo: Data-Aware Export

The data-aware export is the default export mode for the XLS, XLSX, and CSV formats. This mode allows you to transfer data shaping operations (filters, groups, conditional formatting, etc.) into a document. The data-aware mode provides improved performance and memory usage compared to WYSIWYG mode.

Note

The CardView does not support the data-aware export.

Supported Data Shaping Features

The data-aware export mode retains the following grid data shaping features in the exported documents:

• Data Grouping - allows end users to collapse/expand groups within a worksheet.

  

• Data Sorting and Filtering - allows end users to display relevant data in the desired order.

  

• Total and Group Summaries - allows end users to modify/change formulas.

  

• Excel Style Conditional Formatting.

  XAML

  <dxg:TableView.FormatConditions>
      <dxg:FormatCondition    FieldName="Visits" 
                              ValueRule="Greater" 
                              Value1="6" 
                              ApplyToRow="True" >
          <dxg:Format Background="Aqua"/>
      </dxg:FormatCondition>
  </dxg:TableView.FormatConditions>
  

  

• Data Validation for lookup and combo box columns.

  

• Fixed Columns (except columns fixed to the right edge).

  

• Band Columns.

  

Export Data

The following code sample exports the GridControl to the Xls file:

C#

void Button_Click_Export(object sender, RoutedEventArgs e) {
    view.ExportToXls(@"c:\Example\grid_export.xls");
}

VB.NET

Private Sub Button_Click_Export(ByVal sender As Object, ByVal e As RoutedEventArgs)
    view.ExportToXls("c:\Example\grid_export.xls")
End Sub

The following methods export GridControl data in the data-aware mode:

Method	Description
TableView.ExportToCsv, TreeListView.ExportToCsv	Exports a grid to the specified stream in CSV format.
TableView.ExportToXls, TreeListView.ExportToXls	Exports a grid to the specified file path in XLS format.
TableView.ExportToXlsx, TreeListView.ExportToXlsx	Exports a grid to the specified stream in XLSX format.

The BaseColumn.AllowPrinting property specifies whether to export the column.

Customize Appearance

Note

The exported GridControl ignores the DataViewBase.CellTemplate and regular Template/Style properties. Refer to the Format Cell Values topic for information on what properties affect the data-aware export mode.

The following code sample changes the Birthday column’s width and the background color of odd rows in the exported document:

C#

void Button_Click_Export(object sender, RoutedEventArgs e) {
    XlsExportOptionsEx options = new XlsExportOptionsEx();
    options.CustomizeCell += Options_CustomizeCell;
    options.CustomizeDocumentColumn += Options_CustomizeDocumentColumn;
    view.ExportToXls(@"c:\Example\grid_export.xls", options);
}
void Options_CustomizeCell(CustomizeCellEventArgs e) {
    if (e.DocumentRow % 2 != 0)
        e.Formatting.BackColor = System.Drawing.Color.PeachPuff;
    e.Handled = true;
}
void Options_CustomizeDocumentColumn(CustomizeDocumentColumnEventArgs e) {
    if (e.ColumnFieldName == "Birthday")
        e.DocumentColumn.WidthInPixels = 300;
}

VB.NET

Private Sub Button_Click_Export(ByVal sender As Object, ByVal e As RoutedEventArgs)
    Dim options As XlsExportOptionsEx = New XlsExportOptionsEx()
    options.CustomizeCell += AddressOf Options_CustomizeCell
    options.CustomizeDocumentColumn += AddressOf Options_CustomizeDocumentColumn
    view.ExportToXls("c:\Example\grid_export.xls", options)
End Sub

Private Sub Options_CustomizeCell(ByVal e As CustomizeCellEventArgs)
    If e.DocumentRow Mod 2 <> 0 Then e.Formatting.BackColor = System.Drawing.Color.PeachPuff
    e.Handled = True
End Sub

Private Sub Options_CustomizeDocumentColumn(ByVal e As CustomizeDocumentColumnEventArgs)
    If e.ColumnFieldName = "Birthday" Then e.DocumentColumn.WidthInPixels = 300
End Sub

To customize the resulting document in the data-aware export mode, use members of the XlsxExportOptionsEx, XlsExportOptionsEx, or CsvExportOptionsEx classes.

View Example: Add Page Headers and Footers to Exported Data

Event	Description
XlsxExportOptionsEx.CustomizeCell, XlsExportOptionsEx.CustomizeCell, CsvExportOptionsEx.CustomizeCell	Allows you to customize a cell in the output document.
XlsxExportOptionsEx.CustomizeDocumentColumn, XlsExportOptionsEx.CustomizeDocumentColumn	Allows you to customize an individual column in the output document (for example, change its width, formatting, collapse the group containing the column or hide the column).
XlsxExportOptionsEx.CustomizeSheetFooter, XlsExportOptionsEx.CustomizeSheetFooter	Allows you to add a footer to the output document.
XlsxExportOptionsEx.CustomizeSheetHeader, XlsExportOptionsEx.CustomizeSheetHeader	Allows you to add a header to the output document.
XlsxExportOptionsEx.CustomizeSheetSettings, XlsExportOptionsEx.CustomizeSheetSettings	Allows you to customize the output document’s settings.

Limitations

• If the GridControl works in the master-detail mode, only master rows are exported.
• Printing styles and templates are not supported.
• If you set the DataViewBase.TotalSummaryPosition property to Top, the GridControl exports summaries from this Summary Panel as text strings instead of formulas.
• The data-aware export ignores blank cells when calculates Count summaries in the output document. Set the XlsxExportOptionsEx.SummaryCountBlankCells / XlsExportOptionsEx.SummaryCountBlankCells property to true to include blank cells in the summary calculation.
• Custom summary export is not supported.
• If you applied a conditional formatting filter, the data-aware export discards all the conditional formats applied to the GridControl except the FormatCondition conditional formats.
• You can export images from in-place ImageEditSettings and PopupImageEditSettings to .xlsx files only (XlsxExportOptionsEx.AllowCellImages).
• Custom masks are not exported.
• If you set XlsxExportOptionsEx.UnboundExpressionExportMode or XlsExportOptionsEx.UnboundExpressionExportMode, the GridControl does not export cell data as formulas for columns whose UnboundType/UnboundDataType is set to String or DateTime.

Refer to the following help topic for more information: Excel Export Specifications and Limits.