WinForms Grid: Excel Data Export Customization

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.MergeCells(imageToHeaderRange);
        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){
    e.ExportContext.AddRow();
    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.


1 comment(s)
Crono

I can't wait to use this!

Unfortunately, for now I must stick with 14.2. We never move up to a major version at this time of the year due to business requirements.

11 June, 2015

Please login or register to post comments.