corelibraries-devexpress-dot-xtraprinting-dot-xlsexportoptionsex-661bbdd7.md
Allows you to add a header to the output document.Only available in data-aware export mode.
Namespace : DevExpress.XtraPrinting
Assembly : DevExpress.Printing.v25.2.Core.dll
NuGet Package : DevExpress.Printing.Core
public event CustomizeSheetHeaderEventHandler CustomizeSheetHeader
Public Event CustomizeSheetHeader As CustomizeSheetHeaderEventHandler
The CustomizeSheetHeader event is raised before data is exported. It enables you to do the following:
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.
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)));
}
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
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/winforms-grid-customize-data-aware-export-output
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("[email protected]", formatSecondCell);
emailCellLocation.Hyperlink = "[email protected]";
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;
}
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("[email protected]", formatSecondCell)
emailCellLocation.Hyperlink = "[email protected]"
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 (213) 555-1824", 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 (213) 555-2828", 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
See Also