Export to Tabular Formats (CSV, XLS, XLSX)

• Jun 05, 2023
• 7 minutes to read

The Export to tabular data formats (CSV, XLS, XLSX) feature uses the data engine the ExportType enumeration specifies. The default engine uses the Excel Export Library to perform a Data-Aware export with improved performance and reduced memory usage. The resultant file is optimized for subsequent analysis in Microsoft Excel.

Data-Aware Export

The following features are retained in XLS and XLSX documents:

• Data Grouping - you can collapse/expand groups within a worksheet.
• Fixed Headers - column and row areas stay in sight.
• Cells Formatting - retains the number format.
• Display Text/Value export - you can select whether to export display text or values.

Methods

The following table lists the methods that perform data-aware export:

Options

The PivotGridXlsExportOptions and PivotGridXlsxExportOptions classes inherits properties from the XlsExportOptionsEx and XlsxExportOptionsEx classes, respectively. The data-aware export engine uses only a subset of available properties. The table below lists the properties which are in effect in the Data Aware mode.

Runtime Customization

Handle the following events to customize exported pivot grid elements individually. Note that these events are in effect only in the Data Aware mode.

WYSIWYG Export

The WYSIWYG export mode uses the XtraPrinting library engine. It reproduces control elements’ layout in XLS and XLSX formats, omitting data shaping characteristics available in the DataAware export mode.

In CSV format, the WYSIWYG mode can export data area and filter area headers, and add empty columns. When a spreadsheet loads CSV data, it looks similar to the original Pivot Grid control.

|

WYSIWYG Mode

|

Data Aware Mode

| |

|

|

The following table lists the methods that perform the export in WYSIWYG mode:

Options

The PivotXlsExportOptions and PivotXlsxExportOptions classes inherits properties from the XlsExportOptionsEx and XlsxExportOptionsEx classes, respectively. The WYSIWYG mode (the Printing Library export engine) uses only a subset of available properties. The table below lists the properties which are in effect in the WYSIWYG mode.

Custom Export

You can customize a PivotGrid’s cell in the exported XLS, XLSX, or CSV document with the following events:

PivotGridCsvExportOptions.CustomizeCellAllows you to substitute a cell value in the output document. Only available in data-aware export mode.PivotGridXlsExportOptions.CustomizeCellAllows you to customize a PivotGrid cell in the exported XLS document. Only available in data-aware export mode.PivotGridXlsxExportOptions.CustomizeCellAllows you to customize a PivotGrid’s cell in the exported XLSX document. Only available in data-aware export mode.

The following example exports data from a PivotGrid Control to Xlsx format. The PivotGridXlsxExportOptions.CustomizeCell event is used to change the background color of the “Category Name” column in the output document.

• C#
• VB.NET

//...
void op_CustomizeCell(CustomizePivotCellEventArgs e){
       if (e.ValueItemInfo != null && e.ValueItemInfo.Field == fieldCategoryName) {
          e.Formatting.BackColor = Color.AliceBlue;
          e.Handled = true;
      }

  }
void Button_Click(object sender, RoutedEventArgs e) {
    PivotGridXlsxExportOptions op = new PivotGridXlsxExportOptions();
    op.CustomizeCell += op_CustomizeCell;
    pivorGrid1.ExportToXlsx(file, op);
}

'...
Private Sub op_CustomizeCell(ByVal e As CustomizePivotCellEventArgs)
      If e.ValueItemInfo IsNot Nothing AndAlso e.ValueItemInfo.Field = fieldCategoryName Then
          e.Formatting.BackColor = Color.AliceBlue
          e.Handled = True
      End If

End Sub
Private Sub Button_Click(ByVal sender As Object, ByVal e As RoutedEventArgs)
    Dim op As New PivotGridXlsxExportOptions()
    AddHandler op.CustomizeCell, AddressOf op_CustomizeCell
    pivorGrid1.ExportToXlsx(file, op)
End Sub