How to: Customize a Cell in the Exported Excel Document

• Dec 17, 2020
• 2 minutes to read

When exporting an ASPxPivotGrid control to XLSX (or XLS) format, you can customize a cell appearance in the exported document using the PivotXlsxExportOptions.CustomizeCell (or PivotXlsExportOptions.CustomizeCell) event.

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. The CustomizePivotCellEventArgs.ExportArea property is used to identify cell location in the exported Excel document. The cell format is set by the CustomizePivotCellEventArgs.Formatting property.

Default.aspx.cs

using System;
using System.Drawing;
using DevExpress.Web.ASPxPivotGrid;

namespace WebPivotExportCustomizeCell
{
    public partial class Default : System.Web.UI.Page {
        protected void Page_Load(object sender, EventArgs e)
        {
            ASPxPivotGridExporter1.ASPxPivotGridID = "ASPxPivotGrid1";
        }

        protected void ASPxButton1_Click(object sender, EventArgs e)
        {
            var exportOptions = new PivotXlsxExportOptions();
            exportOptions.CustomizeCell += 
                new CustomizePivotCellEventHandler(exportOptions_CustomizeCell);
            ASPxPivotGridExporter1.ExportXlsxToResponse("PivotGrid", exportOptions);
        }

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

Default.aspx.vb

Imports System
Imports System.Drawing
Imports DevExpress.Web.ASPxPivotGrid

Namespace WebPivotExportCustomizeCell
    Partial Public Class [Default]
        Inherits System.Web.UI.Page

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            ASPxPivotGridExporter1.ASPxPivotGridID = "ASPxPivotGrid1"
        End Sub

        Protected Sub ASPxButton1_Click(ByVal sender As Object, ByVal e As EventArgs)
            Dim exportOptions = New PivotXlsxExportOptions()
            AddHandler exportOptions.CustomizeCell, AddressOf exportOptions_CustomizeCell
            ASPxPivotGridExporter1.ExportXlsxToResponse("PivotGrid", exportOptions)
        End Sub

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