XlsExportOptionsEx.CustomizeSheetHeader Event

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

Namespace: DevExpress.XtraPrinting

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

Declaration

public event CustomizeSheetHeaderEventHandler CustomizeSheetHeader
Public Event CustomizeSheetHeader As CustomizeSheetHeaderEventHandler

Remarks

The CustomizeSheetHeader event is raised before data is exported. It enables you to do the following:

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.

CustomizeSheetHeaderEvent_ExampleResult

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

Examples

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.

GridExportOutputResult

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("corpsales@devav.com", formatSecondCell);
    emailCellLocation.Hyperlink = "corpsales@devav.com";
    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 (213) 555-1824", 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 (213) 555-2828", 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;
}

Implements

DevExpress.Export.IDataAwareExportOptions.CustomizeSheetHeader
See Also