How to: Customize a Cell in the Exported Excel Document

  • 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.

View Example

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