Export Overview

• Jul 13, 2021
• 6 minutes to read

The Pivot Grid control is shipped with the ASPxPivotGridExporter component. This component allows you to export the ASPxPivotGrid’s data to a file or stream in various formats - HTML, MHT, PDF, RTF, TXT, CSV, XLS or XLSX.

Tip

Watch the video: DevExpress ASP.NET Pivot Grid: Data-Aware Export (YouTube)

Export Types

Two export types are supported when exporting data from a Pivot Grid control.

• WYSIWYG Export Type - In this export type, the pivot grid layout is retained in resulting documents. Specific data shaping options (like sorting, grouping, filtering, etc.) are not retained.
• Data-aware Export Type - The export type, 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 output documents. This type is supported for XLS(X) and CSV formats by default. For other formats (PDF, RTF, TXT, etc.) only the WYSIWYG type is available.

By using the XLS(X) and CSV formats you can specify export type in two ways.

• For all controls - Set the static ExportSettings.DefaultExportType property that allows you to choose the required export type used by default.
• For each control - Specify export type with each call of the XLS(X) or CSV export methods (when using these method overloads with an options parameter). Create an XlsExportOptionsEx object (or XlsxExportOptionsEx, CsvExportOptionsEx objects), set its ExportType property and pass this object to the XLS(X)/CSV export method.

Exporting Data

To export the Pivot Grid’s data, add the ASPxPivotGridExporter component, assign the pivot grid’s ID to the ASPxPivotGridExporter.ASPxPivotGridID property and call one of the following methods.

Note

If the ASPxPivotGridExporter.ASPxPivotGridID property is not set, the first determined pivot grid on a page will be exported.

Output Format	Methods	Data-Aware Support	Description
HTML	ASPxPivotGridExporter.ExportHtmlToResponse ASPxPivotGridExporter.ExportToHtml	n/a	Exports ASPxPivotGrid data to the specified file in HTML format, using the specified character encoding.
MHT	ASPxPivotGridExporter.ExportMhtToResponse ASPxPivotGridExporter.ExportToMht	n/a	Exports ASPxPivotGrid data to the specified file in MHT format using the specified character encoding with the specified title. The output file can be compressed (secondary characters such as spaces are removed) if required.
PDF	ASPxPivotGridExporter.ExportPdfToResponse ASPxPivotGridExporter.ExportToPdf	n/a	Exports ASPxPivotGrids data to the specified file in PDF format.
RTF	ASPxPivotGridExporter.ExportRtfToResponse ASPxPivotGridExporter.ExportToRtf	n/a	Exports ASPxPivotGrid data to the specified file in RTF format.
TXT	ASPxPivotGridExporter.ExportTextToResponse ASPxPivotGridExporter.ExportToText	n/a	Exports ASPxPivotGrid data to the specified file in TXT format, using the specified separator string and encoding settings.
CSV	ASPxPivotGridExporter.ExportCsvToResponse ASPxPivotGridExporter.ExportToCsv		Exports the ASPxPivotGrid’s data to the specified file in CSV format.
XLS	ASPxPivotGridExporter.ExportXlsToResponse ASPxPivotGridExporter.ExportToXls		Exports ASPxPivotGrid data to the specified file in XLS format using the specified options.
XLSX	ASPxPivotGridExporter.ExportXlsxToResponse ASPxPivotGridExporter.ExportToXlsx		Exports ASPxPivotGrid data to the specified file in XLSX (MS Excel 2007) format using the specified options.

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), to an XlsxExportOptionsEx object (when using the ExportToXlsx method), etc. You can also use the ASPxPivotGridExporter.OptionsPrint property to get an access to the export options.

Limitations

Note, that the ASPxPivotGridExporter component cannot export the following content.

• the content of template fields and cells;
• the results of styles customization;
• images.

Example

This example shows how to export the pivot grid’s data using the Data-Aware or WYSIWYG types and set the export options.

• Click the Export to XLSX button to export data using the Data-Aware type. In the exported XLSX file, fixed row headers are disabled and a sheet name is set to “Pivot Grid Export”.
• Click the Export to PDF button to export data using the WYSIWYG type. The Print dialog is displayed when the exported PDF file is opened.

View Example

WebForm1.aspx (C#)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" 
    Inherits="ASPPivotGridExport.WebForm1" %>

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v15.1, 
    Version=15.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web.ASPxPivotGrid" TagPrefix="dx" %>

<%@ Register 
    assembly="DevExpress.Web.v15.1, Version=15.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" 
    namespace="DevExpress.Data.Linq" tagprefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>ASP.NET Pivot Grid Export</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <div style="padding-bottom: 5px">
            <dx:ASPxButton ID="DataAwareExportButton" runat="server" 
                Text="Export to XLSX" onclick="DataAwareExportButton_Click" ToolTip="Export using the Data-Aware type."/> 
            <dx:ASPxButton ID="WysiwygExportButton" runat="server" Text="Export to PDF" 
                onclick="WysiwygExportButton_Click" ToolTip="Export using the WYSIWYG type." /> 
        </div>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" ClientIDMode="AutoID" 
            DataSourceID="SqlDataSource1">
            <Fields>
                <dx:PivotGridField ID="fieldCountry" Area="ColumnArea" AreaIndex="0" 
                    FieldName="Country">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldCategoryName" Area="RowArea" AreaIndex="0" 
                    Caption="Category" FieldName="CategoryName">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldProductName" Area="RowArea" AreaIndex="1" 
                    Caption="Product" FieldName="ProductName">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldExtendedPrice" Area="DataArea" AreaIndex="0" 
                    Caption="Extended Price" FieldName="Extended_Price">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldSalesPerson" Area="ColumnArea" AreaIndex="1" 
                    Caption="Sales Person" FieldName="Sales_Person">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldQuantity" Area="DataArea" AreaIndex="1" 
                    FieldName="Quantity">
                </dx:PivotGridField>
            </Fields>
        </dx:ASPxPivotGrid>
            <dx:ASPxPivotGridExporter ID="ASPxPivotGridExporter1" runat="server">
            </dx:ASPxPivotGridExporter>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            SelectCommand="SELECT [Country], [CategoryName], [ProductName], 
            [Extended Price] AS Extended_Price, [Sales Person] AS Sales_Person, 
            [Quantity] FROM [SalesPerson]">
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

WebForm1.aspx.cs

using System;
using DevExpress.XtraPrinting;
using DevExpress.Utils;

namespace ASPPivotGridExport
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void DataAwareExportButton_Click(object sender, EventArgs e)
        {
            // Exports using the Data-Aware type.
            ASPxPivotGridExporter1.ExportXlsxToResponse("ASPxPivotGrid", new XlsxExportOptionsEx
            {
                AllowFixedColumns = DefaultBoolean.False,
                SheetName = "Pivot Grid Export"
            }, 
            true);
        }

        protected void WysiwygExportButton_Click(object sender, EventArgs e)
        {
            // Exports using the WYSIWYG type.
            ASPxPivotGridExporter1.ExportPdfToResponse("ASPxPivotGrid", new PdfExportOptions() 
            {
                ShowPrintDialogOnOpen = true,
            }, true);
        }
    }
}

WebForm1.aspx.vb

Imports System
Imports DevExpress.XtraPrinting
Imports DevExpress.Utils

Namespace ASPPivotGridExport
    Partial Public Class WebForm1
        Inherits System.Web.UI.Page

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

        End Sub

        Protected Sub DataAwareExportButton_Click(ByVal sender As Object, ByVal e As EventArgs)
            ' Exports using the Data-Aware type.
            ASPxPivotGridExporter1.ExportXlsxToResponse("ASPxPivotGrid", New XlsxExportOptionsEx With 
            {.AllowFixedColumns = DefaultBoolean.False, .SheetName = "Pivot Grid Export"}, True)
        End Sub

        Protected Sub WysiwygExportButton_Click(ByVal sender As Object, ByVal e As EventArgs)
            ' Exports using the WYSIWYG type.
            ASPxPivotGridExporter1.ExportPdfToResponse("ASPxPivotGrid", New PdfExportOptions() With 
            {.ShowPrintDialogOnOpen = True}, True)
        End Sub
    End Class
End Namespace

WebForm1.aspx (VB.NET)

<%@ Page Language="vb" AutoEventWireup="true" CodeBehind="WebForm1.aspx.vb" 
    Inherits="ASPPivotGridExport.WebForm1" %>

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v15.1, 
    Version=15.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web.ASPxPivotGrid" TagPrefix="dx" %>

<%@ Register 
    assembly="DevExpress.Web.v15.1, Version=15.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" 
    namespace="DevExpress.Data.Linq" tagprefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>ASP.NET Pivot Grid Export</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <div style="padding-bottom: 5px">
            <dx:ASPxButton ID="DataAwareExportButton" runat="server" 
                Text="Export to XLSX" onclick="DataAwareExportButton_Click" ToolTip="Export using the Data-Aware type."/> 
            <dx:ASPxButton ID="WysiwygExportButton" runat="server" Text="Export to PDF" 
                onclick="WysiwygExportButton_Click" ToolTip="Export using the WYSIWYG type." /> 
        </div>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" ClientIDMode="AutoID" 
            DataSourceID="SqlDataSource1">
            <Fields>
                <dx:PivotGridField ID="fieldCountry" Area="ColumnArea" AreaIndex="0" 
                    FieldName="Country">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldCategoryName" Area="RowArea" AreaIndex="0" 
                    Caption="Category" FieldName="CategoryName">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldProductName" Area="RowArea" AreaIndex="1" 
                    Caption="Product" FieldName="ProductName">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldExtendedPrice" Area="DataArea" AreaIndex="0" 
                    Caption="Extended Price" FieldName="Extended_Price">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldSalesPerson" Area="ColumnArea" AreaIndex="1" 
                    Caption="Sales Person" FieldName="Sales_Person">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldQuantity" Area="DataArea" AreaIndex="1" 
                    FieldName="Quantity">
                </dx:PivotGridField>
            </Fields>
        </dx:ASPxPivotGrid>
            <dx:ASPxPivotGridExporter ID="ASPxPivotGridExporter1" runat="server">
            </dx:ASPxPivotGridExporter>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            SelectCommand="SELECT [Country], [CategoryName], [ProductName], 
            [Extended Price] AS Extended_Price, [Sales Person] AS Sales_Person, 
            [Quantity] FROM [SalesPerson]">
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>