XlsExportOptionsEx.CustomizeCell Event

When exporting to XLS format, this event allows you to customize a cell in the output document. Only available in data-aware export mode.

Namespace: DevExpress.XtraPrinting

Assembly: DevExpress.Printing.v20.2.Core.dll

NuGet Packages: DevExpress.Printing.Core, DevExpress.WindowsDesktop.Printing.Core

Declaration

C#

public event CustomizeCellEventHandler CustomizeCell

VB.NET

Public Event CustomizeCell As CustomizeCellEventHandler

Remarks

The CustomizeCell event allows you to

• Substitute a cell value (CustomizeCellEventArgs.Value).
• Insert a hyperlink in the cell (CustomizeCellEventArgs.Hyperlink).
• Customize the cell’s format, font and background color(CustomizeCellEventArgsBase.Formatting).

For changes made in your CustomizeCell event handler to be in effect, set the event’s Handled parameter to true. Otherwise, a cell will be exported using the default settings.

You can get additional information on the source control’s row/node and column/band to which the currently processed cell belongs. Typecast the event’s e parameter to the CustomizeCellEventArgsExtended class and then access the CustomizeCellEventArgsExtended.Row and CustomizeCellEventArgsExtended.Column properties. You may also need to typecast these properties to dedicated classes to obtain details on the row/node and column/band being processed. See CustomizeCellEventArgsExtended.Row and CustomizeCellEventArgsExtended.Column to learn more.

Example

This example uses the XlsxExportOptionsEx.CustomizeCell event to replace values in the Discontinued column in an XLSX document (a result of data exporting from a Grid Control) with special symbols. The ColumnName event parameter allows recognizing the desired column. The Value parameter is utilized to substitute certain cell values. The Handled parameter is set to true to apply the changes made.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-customize-the-gridcontrols-data-aware-export-output-t247610

Form1.cs

// Specify the value alignment for Discontinued field.
XlCellAlignment aligmentForDiscontinuedColumn = new XlCellAlignment() {
    HorizontalAlignment = XlHorizontalAlignment.Center,
    VerticalAlignment = XlVerticalAlignment.Center
};

void options_CustomizeCell(CustomizeCellEventArgs e){
    // Substitute Boolean values within the Discontinued column by special symbols.
    if(e.ColumnFieldName == "Discontinued"){
        if(e.Value is bool){
            e.Handled = true;
            e.Formatting.Alignment = aligmentForDiscontinuedColumn;
            e.Value = ((bool) e.Value) ? "☑" : "☐";
        }
    }
}

Form1.vb

' Specify the value alignment for Discontinued field.
Private aligmentForDiscontinuedColumn As New XlCellAlignment() With {.HorizontalAlignment = XlHorizontalAlignment.Center, .VerticalAlignment = XlVerticalAlignment.Center}

Private Sub options_CustomizeCell(ByVal e As CustomizeCellEventArgs)
    ' Substitute Boolean values within the Discontinued column by special symbols.
    If e.ColumnFieldName = "Discontinued" Then
        If TypeOf e.Value Is Boolean Then
            e.Handled = True
            e.Formatting.Alignment = aligmentForDiscontinuedColumn
            e.Value = If(CBool(e.Value), "☑", "☐")
        End If
    End If
End Sub

Example

The following example exports data from a GridControl to XLS format. The XlsExportOptionsEx.CustomizeCell event is used to change the background of the grid control’s City column in the output document, and to provide hyperlinks for this column’s cells. Note that the Handled event parameter is set to true to apply the changes made.

C#

using DevExpress.XtraPrinting;

// Ensure that the data-aware export mode is enabled.
DevExpress.Export.ExportSettings.DefaultExportType = DevExpress.Export.ExportType.DataAware;

private void button1_Click(object sender, EventArgs e) {
    string file = "c:\\work\\grid-export.xls";
    XlsExportOptionsEx op = new XlsExportOptionsEx();
    op.CustomizeCell += op_CustomizeCell;
    gridView1.ExportToXls(file, op);
    System.Diagnostics.Process.Start(file);
}

void op_CustomizeCell(DevExpress.Export.CustomizeCellEventArgs ea) {
    if (ea.ColumnFieldName == "City") {
        ea.Hyperlink = "https://www.google.com/search?q=" + ea.Value.ToString();
        ea.Formatting.BackColor = Color.Pink;
        ea.Handled = true;
    }
}

VB.NET

Imports DevExpress.XtraPrinting

' Ensure that the data-aware export mode is enabled.
DevExpress.Export.ExportSettings.DefaultExportType = DevExpress.Export.ExportType.DataAware

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim file As String = "c:\work\grid-export.xls"
    Dim op As New XlsExportOptionsEx()
    AddHandler op.CustomizeCell, AddressOf op_CustomizeCell
    GridView1.ExportToXls(file, op)
    System.Diagnostics.Process.Start(file)
End Sub

Private Sub op_CustomizeCell(ea As DevExpress.Export.CustomizeCellEventArgs)
    If ea.ColumnFieldName = "City" Then
        ea.Hyperlink = "https://www.google.com/search?q=" + ea.Value.ToString()
        ea.Formatting.BackColor = Color.Pink
        ea.Handled = True
    End If
End Sub