WinForms Grid: Excel Data Export Customization

Thinking Out Loud
09 June 2015

If you've used our WinForms Grid in the past, you already know that with a single call to the ExportToXlsx, method you can export the contents of the grid and generate a Microsoft Excel document.  In this post, I want to quickly discuss use of our updated Excel Data Export engine and its numerous customization options. In particular, I want to show you how to customize cell values, add a header/footer and get you some code examples so you can implement similar solutions in your WinForms apps.

Excel Data Export - Header Customization

To add a header and display personalized information, such as company name, logo and contact information, we need to handle the CustomizeSheetHeader event. The methods (AddRow, MergeCells and InsertImage) provided by its event arguments allow us to add rows to the output, merge cells, insert images, etc.

void options_CustomizeSheetHeader(ContextEventArgs e){
    // ...
    var file = System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("GridReportBlog.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.InsertImage(imageToHeader, imageToHeaderRange);
    // ...

Resulting output below...

WinForms Data Grid - Excel Export with Header Customization

Excel Data Export - Cell Customization

Let's now take a look at the CustomizeCell event and how we can use it to replace values in a Boolean column with the check mark symbols. "☑" and "☐"

XlCellAlignment aligmentForDiscontinued = new XlCellAlignment() {
    HorizontalAlignment = XlHorizontalAlignment.Center,
    VerticalAlignment = XlVerticalAlignment.Center
void options_CustomizeCell(CustomizeCellEventArgs e){
    if(e.ColumnFieldName == "Discontinued"){
        if(e.Value is bool){
            e.Handled = true;
            e.Formatting.Alignment = aligmentForDiscontinued;
            e.Value = ((bool) e.Value) ? "" : "";

Resulting output below...

WinForms Data Grid - Excel Export Cell Customization

Excel Data Export - Footer Customization

Finally, to add a footer to the exported file, we'll need to use CustomizeSheetFooter event. As you can see below, we've added two new rows to the output document and specified formatting using XlFormattingObjects.

void options_CustomizeSheetFooter(ContextEventArgs e){
    var firstRow = new CellObject();
    firstRow.Value = @"The report is generated from the NorthWind database.";
    var rowFormatting = CreateXlFormattingObject(true, 18);
    rowFormatting.Alignment.HorizontalAlignment = XlHorizontalAlignment.Left;
    firstRow.Formatting = rowFormatting;
    e.ExportContext.AddRow(new[]{ firstRow });
    // ...

WinForms Data Grid - Excel Data Export Footer Customization

Hopefully this quick tutorial will help you get started with our Excel Data Export engine and help you better integrate your WinForms app with Microsoft Excel.

Free DevExpress Products - Get Your Copy Today

The following free DevExpress product offers remain available. Should you have any questions about the free offers below, please submit a ticket via the DevExpress Support Center at your convenience. We'll be happy to follow-up.
No Comments

Please login or register to post comments.