Exporting

• Nov 06, 2023
• 7 minutes to read

The Pivot Grid control allows you to export Pivot Grid data to a file or stream in various formats - HTML, MHT, PDF, RTF, TXT, CSV, XLS or XLSX. When you export, you copy data from a pivot grid into a new file formatted for use in another application. This document describes exporting in detail.

To export pivot grid data, use one of the following export methods.

Export settings can be customized when calling the ExportTo… method overloads that take an options parameter. This parameter can be specified with a descendant of the ExportOptionsBase class, which defines how a document is exported to a specific format.

Note

Results of custom drawing and images cannot be exported.

Export Modes

Two export modes are supported when exporting data from a Pivot Grid control to table formats (*.xls(x), *.csv).

• Data-aware Export - The export mode, optimized for subsequent analysis of pivot grid data within Microsoft Excel. Various data shaping options that are applied within the pivot grid are retained in the output XLS(X) and CSV documents.
• WYSIWYG Export - In this export mode, the layout of pivot grid cells is retained in resulting XLS(X) and CSV documents. Specific data shaping options are not retained compared to data-aware export.

Data exported using other formats (PDF, RTF, TXT, etc.) is always exported in the WYSIWYG mode.

Choosing Export Mode

You can specify export mode in two ways.

• Set the static ExportSettings.DefaultExportType property that allows you to choose the required export mode used by default by all XLS(X) and CSV export methods.

• Specify export mode with each call of the XLS(X) or CSV export methods (when using these method overloads with an options parameter). Create an PivotXlsExportOptions object (or PivotXlsxExportOptions, CsvExportOptionsEx objects), set its ExportType property and pass this object to the XLS(X)/CSV export method.

Data-Aware Export

Data-aware is the default export mode for XLS and XLSX formats, and is optimized for subsequent analysis of pivot grid data within Microsoft Excel.

The following data shaping options, which are applied within the pivot grid control, are retained in the output XLS-XLSX documents.

• Data Grouping - with the capability to collapse/expand groups within a worksheet.
• Fixed Columns – allow column and row areas to stay in sight.
• Cell Formatting – allows you to export the number format.
• Display Text/Value export - allows you to select whether to export display text or values.

Data-Aware Export Settings

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 options supported in data-aware mode are listed in the following table.

|

Property

|

Description

| | --- | --- | |

XlExportOptionsBase.SheetName

|

Gets or sets the name of a sheet in a created XLS file to which a document is exported.

| |

XlsExportOptionsEx.AllowFixedColumns

XlsxExportOptionsEx.AllowFixedColumns

|

Gets or sets whether or not the row area in a pivot grid control is fixed in the exported document.

| |

XlsExportOptionsEx.AllowFixedColumnHeaderPanel

XlsxExportOptionsEx.AllowFixedColumnHeaderPanel

|

Gets or sets whether the column area is anchored to the top of the export document, and thus is not scrolled vertically.

| |

XlsExportOptionsEx.AllowGrouping

XlsxExportOptionsEx.AllowGrouping

|

Gets or sets whether or not data groups are exported from the source control to the output document.

| |

XlsExportOptionsEx.ShowPageTitle

XlsxExportOptionsEx.ShowPageTitle

|

Gets or sets whether or not a title is displayed for each print preview page of the exported document.

| |

PivotXlsExportOptionsBase.ExportColumnAreaHeaders, PivotXlsxExportOptionsBase.ExportColumnAreaHeaders

|

Gets or sets whether to include the column field headers in the exported document.

| |

PivotXlsExportOptionsBase.ExportDataAreaHeaders, PivotXlsxExportOptionsBase.ExportDataAreaHeaders

|

Gets or sets whether to include the data field headers in the exported document.

| |

PivotXlsExportOptionsBase.ExportFilterAreaHeaders, PivotXlsxExportOptionsBase.ExportFilterAreaHeaders

|

Gets or sets whether to include the filter field headers in the exported document.

| |

PivotXlsExportOptionsBase.ExportRowAreaHeaders, PivotXlsxExportOptionsBase.ExportRowAreaHeaders

|

Gets or sets whether to include the row field headers in the exported document.

| |

XlsExportOptionsEx.ExportType, XlsxExportOptionsEx.ExportType

|

Gets or sets export mode. You can specify the WYSIWYG or data-aware export mode.

| |

XlsExportOptionsEx.GroupState, XlsxExportOptionsEx.GroupState

|

Gets or sets whether groups will be collapsed or expanded in the exported document.

| |

XlExportOptionsBase.RawDataMode

|

Enables the export mode, which when used, exports only a report’s actual data to XLS(X). Note that options like XlsExportOptionsEx.AllowGrouping and XlsxExportOptionsEx.AllowGrouping are disabled and not in effect in the exported document. Note that the parent cell values in the exported document are exported for every child cell value when the RawDataMode property is set to true.

| |

XlExportOptionsBase.TextExportMode

|

Gets or sets a value indicating whether the cells in the resulting XLS(X) document should use the same formatting as the original document.

|

Custom Export

You can customize a PivotGrid’s cell in the exported XLS or XLSX document using the PivotXlsExportOptions.CustomizeCell and PivotXlsxExportOptions.CustomizeCell events. Use the CustomizePivotCellEventArgs.RowType and CustomizePivotCellEventArgs.ColumnType parameters to identify a row and column containing the cell. To specify cell location in the exported Excel document, use the CustomizePivotCellEventArgs.ExportArea property. To customize the cell format, use the CustomizePivotCellEventArgs.Formatting property.

Note

Set the CustomizePivotCellEventArgs.Handled parameter to true to export cells with custom formatting.

Example

The following example shows how to customize cell appearance in the exported document when you export the Pivot Grid to XLSX (or XLS) format. In this example, custom appearance settings (the azure background and italic font) are applied to the cells that correspond to the Pivot Grid’s data area.

View Example

• Form1.cs
• Form1.vb

using System;
using System.Drawing;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;
using DevExpress.XtraBars;

namespace WinPivotExportCustomizeCell {
    public partial class Form1 : DevExpress.XtraBars.Ribbon.RibbonForm {
        public Form1() {
            InitializeComponent();
            // This line of code is generated by Data Source Configuration Wizard
            salesPersonTableAdapter1.Fill(nwindDataSet.SalesPerson);
        }

        private void Form1_Load(object sender, EventArgs e) {
            this.salesPersonTableAdapter1.Fill(this.nwindDataSet.SalesPerson);
        }

        private void barButtonItem1_ItemClick(object sender, ItemClickEventArgs e) {
            #region #filepathCreating
            string filePath;
            var saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xlsx";
            saveDialog.FileName = "PivotGrid.xlsx";
            saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
            saveDialog.ShowDialog();
            filePath = saveDialog.FileName;
            #endregion

            var exportOptions = new PivotXlsxExportOptions();
            exportOptions.CustomizeCell += 
                new CustomizePivotCellEventHandler(exportOptions_CustomizeCell);
            if (filePath != "") {
                pivotGridControl1.ExportToXlsx(filePath, exportOptions);
            }
        }

        void exportOptions_CustomizeCell(CustomizePivotCellEventArgs e) {
            if (e.ExportArea == PivotExportArea.Data) {
                e.Formatting.BackColor = Color.Azure;
                e.Formatting.Font.Italic = true; 
            }
            e.Handled = true;
        }
    }
}

Imports System
Imports System.Drawing
Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid
Imports DevExpress.XtraBars

Namespace WinPivotExportCustomizeCell
    Partial Public Class Form1
        Inherits DevExpress.XtraBars.Ribbon.RibbonForm

        Public Sub New()
            InitializeComponent()
            ' This line of code is generated by Data Source Configuration Wizard
            salesPersonTableAdapter1.Fill(nwindDataSet.SalesPerson)
        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
            Me.salesPersonTableAdapter1.Fill(Me.nwindDataSet.SalesPerson)
        End Sub

        Private Sub barButtonItem1_ItemClick(ByVal sender As Object,
                                ByVal e As ItemClickEventArgs) Handles barButtonItem1.ItemClick
            ' #Region "#filepathCreating"
            Dim filePath As String
            Dim saveDialog = New SaveFileDialog()
            saveDialog.DefaultExt = "xlsx"
            saveDialog.FileName = "PivotGrid.xlsx"
            saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"
            saveDialog.ShowDialog()
            filePath = saveDialog.FileName
            ' #End Region

            Dim exportOptions = New PivotXlsxExportOptions()
            AddHandler exportOptions.CustomizeCell, AddressOf exportOptions_CustomizeCell
            If filePath <> "" Then
                pivotGridControl1.ExportToXlsx(filePath, exportOptions)
            End If
        End Sub

        Private Sub exportOptions_CustomizeCell(ByVal e As CustomizePivotCellEventArgs)
            If e.ExportArea = PivotExportArea.Data Then
                e.Formatting.BackColor = Color.Azure
                e.Formatting.Font.Italic = True
            End If
            e.Handled = True
        End Sub
    End Class
End Namespace

See Also

How to Create a Custom Exporter for PivotGridControl with the XtraReport Suite

Document Post-Processing