How to: Customize a Cell in the Exported Excel Document

• Nov 13, 2018
• 2 minutes to read

When exporting a Pivot Grid 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.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-customize-a-cell-in-the-exported-excel-document-t424327

Form1.cs

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;
        }
    }
}

Form1.vb

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