XlsxExportOptionsEx.CustomizeSheetHeader Event

Allows you to customize the header in the output document. Only available in data-aware export mode.

Namespace: DevExpress.XtraPrinting

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

Declaration

C#

public event CustomizeSheetHeaderEventHandler CustomizeSheetHeader

VB.NET

Public Event CustomizeSheetHeader As CustomizeSheetHeaderEventHandler

Remarks

To learn more about the functionality provided by this event, see XlsExportOptionsEx.CustomizeSheetHeader.

Example

The XlsxExportOptionsEx.CustomizeSheetHeader event is used to add a header to an XLSX document (a result of data exporting from a Grid Control). In the event handler, the AddRow and MergeCells methods of the event’s ExportContext parameter are used to add rows and merge specific cells. Cell formatting is specified using an object of the XlFormattingObject class.

C#

using DevExpress.XtraPrinting;
using DevExpress.Printing.ExportHelpers;
using DevExpress.Export;

private void simpleButton1_Click(object sender, EventArgs e) {
    // Ensure that the data-aware export mode is enabled.
    DevExpress.Export.ExportSettings.DefaultExportType = ExportType.DataAware;           
    // Create a new object defining how a document is exported to the XLSX format.
    XlsxExportOptionsEx options = new XlsxExportOptionsEx();
    // Subscribe to the CustomizeSheetHeader event. 
    options.CustomizeSheetHeader += options_CustomizeSheetHeader;
    // Export the grid data to the XLSX format.
    string file = "grid-export.xlsx";
    gridControl.ExportToXlsx(file, options);
    // Open the created document.
    System.Diagnostics.Process.Start(file);           
}

void options_CustomizeSheetHeader(DevExpress.Export.ContextEventArgs e) {
    // Create a new row.
    CellObject row = new CellObject();
    // Specify row values.
    row.Value = "The document is exported from the IssueList database.";
    // Specify row formatting.
    XlFormattingObject rowFormatting = new XlFormattingObject();
    rowFormatting.Font = new XlCellFont { Bold = true, Size = 14 };
    rowFormatting.Alignment = new DevExpress.Export.Xl.XlCellAlignment { HorizontalAlignment = DevExpress.Export.Xl.XlHorizontalAlignment.Center, VerticalAlignment = DevExpress.Export.Xl.XlVerticalAlignment.Top };
    row.Formatting = rowFormatting;
    // Add the created row to the output document.
    e.ExportContext.AddRow(new [] {row});
    // Add an empty row to the output document.
    e.ExportContext.AddRow();
    // Merge cells of two new rows. 
    e.ExportContext.MergeCells(new DevExpress.Export.Xl.XlCellRange(new DevExpress.Export.Xl.XlCellPosition(0, 0), new DevExpress.Export.Xl.XlCellPosition(5, 1)));
}

VB.NET

Imports DevExpress.XtraPrinting
Imports DevExpress.Printing.ExportHelpers
Imports DevExpress.Export

Private Sub simpleButton1_Click(sender As Object, e As EventArgs)
    ' Ensure that the data-aware export mode is enabled.
    DevExpress.Export.ExportSettings.DefaultExportType = ExportType.DataAware
    ' Create a new object defining how a document is exported to the XLSX format.
    Dim options As New XlsxExportOptionsEx()
    ' Subscribe to the CustomizeSheetHeader event. 
    AddHandler options.CustomizeSheetHeader, AddressOf options_CustomizeSheetHeader
    ' Export the grid data to the XLSX format.
    Dim file As String = "grid-export.xlsx"
    gridControl.ExportToXlsx(file, options)
    ' Open the created document.
    System.Diagnostics.Process.Start(file)
End Sub

Private Sub options_CustomizeSheetHeader(e As DevExpress.Export.ContextEventArgs)
    ' Create a new row.
    Dim row As New CellObject()
    ' Specify row values.
    row.Value = "The document is exported from the IssueList database."
    ' Specify row formatting.
    Dim rowFormatting As New XlFormattingObject()
    rowFormatting.Font = New XlCellFont() With { _
        Key .Bold = True, _
        Key .Size = 14 _
    }
    rowFormatting.Alignment = New DevExpress.Export.Xl.XlCellAlignment() With { _
        Key .HorizontalAlignment = DevExpress.Export.Xl.XlHorizontalAlignment.Center, _
        Key .VerticalAlignment = DevExpress.Export.Xl.XlVerticalAlignment.Top _
    }
    row.Formatting = rowFormatting
    ' Add the created row to the output document.
    e.ExportContext.AddRow(New () {row})
    ' Add an empty row to the output document.
    e.ExportContext.AddRow()
    ' Merge cells of two new rows. 
    e.ExportContext.MergeCells(New DevExpress.Export.Xl.XlCellRange(New DevExpress.Export.Xl.XlCellPosition(0, 0), New DevExpress.Export.Xl.XlCellPosition(5, 1)))
End Sub

Example

This example uses the XlsxExportOptionsEx.CustomizeSheetHeader event to add a header displaying custom information to an XLSX document (a result of data exporting from a Grid Control). In the event handler, the AddRow, InsertImage and MergeCells methods of the event’s ExportContext parameter are used to add rows with the company name and contact information, insert the company logo and merge specific cells. Cell formatting is specified using objects of the XlFormattingObject class.

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

delegate void AddCells(ContextEventArgs e, XlFormattingObject formatFirstCell, XlFormattingObject formatSecondCell);

Dictionary<int, AddCells> methods = CreateMethodSet();

static Dictionary<int, AddCells> CreateMethodSet(){
    var dictionary = new Dictionary<int, AddCells>();
    dictionary.Add(9, AddAddressRow);
    dictionary.Add(10, AddAddressLocationCityRow);
    dictionary.Add(11, AddPhoneRow);
    dictionary.Add(12, AddFaxRow);
    dictionary.Add(13, AddEmailRow);
    return dictionary;
}
void options_CustomizeSheetHeader(ContextEventArgs e){
    // Specify cell formatting. 
    var formatFirstCell = CreateXlFormattingObject(true, 24);
    var formatSecondCell = CreateXlFormattingObject(true, 18);
    // Add new rows displaying custom information. 
    for(var i = 0; i < 15; i++){
        AddCells addCellMethod;
        if(methods.TryGetValue(i, out addCellMethod))
            addCellMethod(e, formatFirstCell, formatSecondCell);
        else e.ExportContext.AddRow();
    }
    // Merge specific cells.
    MergeCells(e);
    // Add an image to the top of the document.
    var file = System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("GridDataAwareExportCustomization.Resources.1.jpg");
    if(file != null){
        var imageToHeader = new Bitmap(Image.FromStream(file));
        var imageToHeaderRange = new XlCellRange(new XlCellPosition(0, 0), new XlCellPosition(5, 7));
        e.ExportContext.MergeCells(imageToHeaderRange);
        e.ExportContext.InsertImage(imageToHeader, imageToHeaderRange);
    }
    e.ExportContext.MergeCells(new XlCellRange(new XlCellPosition(0, 8), new XlCellPosition(5, 8)));           
}

static void AddEmailRow(ContextEventArgs e, XlFormattingObject formatFirstCell,
    XlFormattingObject formatSecondCell){
    var emailCellName = CreateCell("Email :", formatFirstCell);
    var emailCellLocation = CreateCell("[e-mail]", formatSecondCell);
    emailCellLocation.Hyperlink = "[e-mail]";
    e.ExportContext.AddRow(new[]{ emailCellName, null, emailCellLocation });
}
static void AddFaxRow(ContextEventArgs e, XlFormattingObject formatFirstCell,
    XlFormattingObject formatSecondCell){
    var faxCellName = CreateCell("Fax :", formatFirstCell);
    var faxCellLocation = CreateCell("+ 1 [phone]", formatSecondCell);
    e.ExportContext.AddRow(new[]{ faxCellName, null, faxCellLocation });
}
static void AddPhoneRow(ContextEventArgs e, XlFormattingObject formatFirstCell,
    XlFormattingObject formatSecondCell){
    var phoneCellName = CreateCell("Phone :", formatFirstCell);
    var phoneCellLocation = CreateCell("+ 1 [phone]", formatSecondCell);
    e.ExportContext.AddRow(new[]{ phoneCellName, null, phoneCellLocation });
}
static void AddAddressLocationCityRow(ContextEventArgs e, XlFormattingObject formatFirstCell,
    XlFormattingObject formatSecondCell){
    var AddressLocationCityCell = CreateCell("Los Angeles CA 90731 USA", formatSecondCell);
    e.ExportContext.AddRow(new[]{ null, null, AddressLocationCityCell });
}
static void AddAddressRow(ContextEventArgs e, XlFormattingObject formatFirstCell,
    XlFormattingObject formatSecondCell){
    var AddressCellName = CreateCell("Address: ", formatFirstCell);
    var AddresssCellLocation = CreateCell("807 West Paseo Del Mar", formatSecondCell);
    e.ExportContext.AddRow(new[]{ AddressCellName, null, AddresssCellLocation });
}

// Create a new cell with a specified value and format settings.
static CellObject CreateCell(object value, XlFormattingObject formatCell){
    return new CellObject{ Value = value, Formatting = formatCell };
}

// Merge specific cells.
static void MergeCells(ContextEventArgs e){
    MergeCells(e, 2, 9, 5, 9);
    MergeCells(e, 0, 9, 1, 10);
    MergeCells(e, 2, 10, 5, 10);
    MergeCells(e, 0, 11, 1, 11);
    MergeCells(e, 2, 11, 5, 11);
    MergeCells(e, 0, 12, 1, 12);
    MergeCells(e, 2, 12, 5, 12);
    MergeCells(e, 0, 13, 1, 13);
    MergeCells(e, 2, 13, 5, 13);
    MergeCells(e, 0, 14, 5, 14);
}
static void MergeCells(ContextEventArgs e, int left, int top, int right, int bottom){
    e.ExportContext.MergeCells(new XlCellRange(new XlCellPosition(left, top), new XlCellPosition(right, bottom)));
}

// Specify a cell's alignment and font settings. 
static XlFormattingObject CreateXlFormattingObject(bool bold, double size){
    var cellFormat = new XlFormattingObject{
        Font = new XlCellFont{
            Bold = bold,
            Size = size
        },
        Alignment = new XlCellAlignment{
            RelativeIndent = 10,
            HorizontalAlignment = XlHorizontalAlignment.Center,
            VerticalAlignment = XlVerticalAlignment.Center
        }
    };
    return cellFormat;
}

Form1.vb

Private Delegate Sub AddCells(ByVal e As ContextEventArgs, ByVal formatFirstCell As XlFormattingObject, ByVal formatSecondCell As XlFormattingObject)

Private methods As Dictionary(Of Integer, AddCells) = CreateMethodSet()

Private Shared Function CreateMethodSet() As Dictionary(Of Integer, AddCells)
    Dim dictionary = New Dictionary(Of Integer, AddCells)()
    dictionary.Add(9, AddressOf AddAddressRow)
    dictionary.Add(10, AddressOf AddAddressLocationCityRow)
    dictionary.Add(11, AddressOf AddPhoneRow)
    dictionary.Add(12, AddressOf AddFaxRow)
    dictionary.Add(13, AddressOf AddEmailRow)
    Return dictionary
End Function
Private Sub options_CustomizeSheetHeader(ByVal e As ContextEventArgs)
    ' Specify cell formatting. 
    Dim formatFirstCell = CreateXlFormattingObject(True, 24)
    Dim formatSecondCell = CreateXlFormattingObject(True, 18)
    ' Add new rows displaying custom information. 
    For i = 0 To 14
        Dim addCellMethod As AddCells = Nothing
        If methods.TryGetValue(i, addCellMethod) Then
            addCellMethod(e, formatFirstCell, formatSecondCell)
        Else
            e.ExportContext.AddRow()
        End If
    Next i
    ' Merge specific cells.
    MergeCells(e)
    ' Add an image to the top of the document.
    Dim file = System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("Resources.1.jpg")
    If file IsNot Nothing Then
        Dim imageToHeader = New Bitmap(Image.FromStream(file))
        Dim imageToHeaderRange = New XlCellRange(New XlCellPosition(0, 0), New XlCellPosition(5, 7))
        e.ExportContext.MergeCells(imageToHeaderRange)
        e.ExportContext.InsertImage(imageToHeader, imageToHeaderRange)
    End If
    e.ExportContext.MergeCells(New XlCellRange(New XlCellPosition(0, 8), New XlCellPosition(5, 8)))
End Sub

Private Shared Sub AddEmailRow(ByVal e As ContextEventArgs, ByVal formatFirstCell As XlFormattingObject, ByVal formatSecondCell As XlFormattingObject)
    Dim emailCellName = CreateCell("Email :", formatFirstCell)
    Dim emailCellLocation = CreateCell("[e-mail]", formatSecondCell)
    emailCellLocation.Hyperlink = "[e-mail]"
    e.ExportContext.AddRow({ emailCellName, Nothing, emailCellLocation })
End Sub
Private Shared Sub AddFaxRow(ByVal e As ContextEventArgs, ByVal formatFirstCell As XlFormattingObject, ByVal formatSecondCell As XlFormattingObject)
    Dim faxCellName = CreateCell("Fax :", formatFirstCell)
    Dim faxCellLocation = CreateCell("+ 1 [phone]", formatSecondCell)
    e.ExportContext.AddRow({ faxCellName, Nothing, faxCellLocation })
End Sub
Private Shared Sub AddPhoneRow(ByVal e As ContextEventArgs, ByVal formatFirstCell As XlFormattingObject, ByVal formatSecondCell As XlFormattingObject)
    Dim phoneCellName = CreateCell("Phone :", formatFirstCell)
    Dim phoneCellLocation = CreateCell("+ 1 [phone]", formatSecondCell)
    e.ExportContext.AddRow({ phoneCellName, Nothing, phoneCellLocation })
End Sub
Private Shared Sub AddAddressLocationCityRow(ByVal e As ContextEventArgs, ByVal formatFirstCell As XlFormattingObject, ByVal formatSecondCell As XlFormattingObject)
    Dim AddressLocationCityCell = CreateCell("Los Angeles CA 90731 USA", formatSecondCell)
    e.ExportContext.AddRow({ Nothing, Nothing, AddressLocationCityCell })
End Sub
Private Shared Sub AddAddressRow(ByVal e As ContextEventArgs, ByVal formatFirstCell As XlFormattingObject, ByVal formatSecondCell As XlFormattingObject)
    Dim AddressCellName = CreateCell("Address: ", formatFirstCell)
    Dim AddresssCellLocation = CreateCell("807 West Paseo Del Mar", formatSecondCell)
    e.ExportContext.AddRow({ AddressCellName, Nothing, AddresssCellLocation })
End Sub

' Create a new cell with a specified value and format settings.
Private Shared Function CreateCell(ByVal value As Object, ByVal formatCell As XlFormattingObject) As CellObject
    Return New CellObject With {.Value = value, .Formatting = formatCell}
End Function

' Merge specific cells.
Private Shared Sub MergeCells(ByVal e As ContextEventArgs)
    MergeCells(e, 2, 9, 5, 9)
    MergeCells(e, 0, 9, 1, 10)
    MergeCells(e, 2, 10, 5, 10)
    MergeCells(e, 0, 11, 1, 11)
    MergeCells(e, 2, 11, 5, 11)
    MergeCells(e, 0, 12, 1, 12)
    MergeCells(e, 2, 12, 5, 12)
    MergeCells(e, 0, 13, 1, 13)
    MergeCells(e, 2, 13, 5, 13)
    MergeCells(e, 0, 14, 5, 14)
End Sub
Private Shared Sub MergeCells(ByVal e As ContextEventArgs, ByVal left As Integer, ByVal top As Integer, ByVal right As Integer, ByVal bottom As Integer)
    e.ExportContext.MergeCells(New XlCellRange(New XlCellPosition(left, top), New XlCellPosition(right, bottom)))
End Sub

' Specify a cell's alignment and font settings. 
Private Shared Function CreateXlFormattingObject(ByVal bold As Boolean, ByVal size As Double) As XlFormattingObject
    Dim cellFormat = New XlFormattingObject With { _
        .Font = New XlCellFont With {.Bold = bold, .Size = size}, _
        .Alignment = New XlCellAlignment With {.RelativeIndent = 10, .HorizontalAlignment = XlHorizontalAlignment.Center, .VerticalAlignment = XlVerticalAlignment.Center} _
    }
    Return cellFormat
End Function

Related GitHub Examples

The following code snippet (auto-collected from DevExpress Examples) contains a reference to the CustomizeSheetHeader event.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

how-to-customize-the-gridcontrols-data-aware-export-output-t247610/CS/GridDataAwareExportCustomization/Form1.cs#L48

options.CustomizeSheetSettings += options_CustomizeSheetSettings;
options.CustomizeSheetHeader += options_CustomizeSheetHeader;
options.CustomizeCell += options_CustomizeCell;

VB.NET

how-to-customize-the-gridcontrols-data-aware-export-output-t247610/VB/GridDataAwareExportCustomization/Form1.vb#L51

AddHandler options.CustomizeSheetSettings, AddressOf options_CustomizeSheetSettings
AddHandler options.CustomizeSheetHeader, AddressOf options_CustomizeSheetHeader
AddHandler options.CustomizeCell, AddressOf options_CustomizeCell