How to: Customize the GridControl's Data-Aware Export Output

• Aug 01, 2019
• 12 minutes to read

This example demonstrates how to customize the GridControl’s data-aware export output.

In this example, the grid’s data is exported to XLSX format using the GridControl.ExportToXlsx method with a parameter (an XlsxExportOptionsEx object) that regulates export settings and behavior. This parameter provides events that allow you to add a header and footer to the export output, to customize cells, add rows, etc. Note that you can perform similar customizations when exporting to other formats.

Export Methods	Options Class
BaseView.ExportToCsv	CsvExportOptionsEx
BaseView.ExportToXls	XlsExportOptionsEx
BaseView.ExportToXlsx	XlsxExportOptionsEx

To add a header displaying custom information to the generated MS Excel document, the XlsxExportOptionsEx.CustomizeSheetHeader event is handled. 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.

The XlsxExportOptionsEx.CustomizeCell event allows you to replace values in the Discontinued column with special symbols. Use the e.ColumnName parameter to obtain the processed column. The e.Value parameter allows you to substitute cell values. Set the e.Handled parameter to true to apply the changes made.

The XlsxExportOptionsEx.AfterAddRow event handler merges cells in rows that correspond to the grid’s group rows using the ExportContext.MergeCells method. For group rows, the DataSourceRowIndex event parameter returns negative values. The current row in the export output is specified by the DocumentRow parameter.

To add a footer to the export document, the XlsxExportOptionsEx.CustomizeSheetFooter event is handled. In this event handler, two new rows are added to the output document by calling the AddRow method, and their formatting is specified using objects of the XlFormattingObject class.

The XlsxExportOptionsEx.CustomizeSheetSettings event is handled to specify export settings. The ExportContext.SetFixedHeader event method is used to anchor the output document’s header to the top, and to set the fixed header height. The ExportContext.SetFixedHeader method is called to add the AutoFilter button to the document’s cells corresponding to the grid column headers.

The following image demonstrates the resulting export output.

View Example

Form1.cs

using DevExpress.Export;
using DevExpress.Export.Xl;
using DevExpress.Printing.ExportHelpers;
using DevExpress.XtraEditors;
using DevExpress.XtraPrinting;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Drawing;
using System.Threading;
using System.Windows.Forms;
using DevExpress.XtraGrid.Views.Base;

namespace GridDataAwareExportCustomization{
    public partial class Form1 : XtraForm{

        readonly string tblGrid = "Products";
        const string tblLookUp = "Categories";

        public Form1(){
            InitializeComponent();
            InitNWindData();
            gridView1.ExpandAllGroups();
        }

        void gridView1_CustomUnboundColumnData(object sender, CustomColumnDataEventArgs e){
            GridView view = sender as GridView;
            if(e.Column == categoryName) {
                if(e.IsGetData) {
                    int id = (int)view.GetListSourceRowCellValue(e.ListSourceRowIndex, colCategoryID);
                    e.Value = nwindDataSet.Categories.FindByCategoryID(id).CategoryName;
                }
            }
        }

        private void btn_Export_Click(object sender, EventArgs e) {
            // Ensure that the data-aware export mode is enabled.
            ExportSettings.DefaultExportType = ExportType.DataAware;
            // Create a new object defining how a document is exported to the XLSX format.
            var options = new XlsxExportOptionsEx();
            // Specify a name of the sheet in the created XLSX file.
            options.SheetName = "DevAV price";

            // Subscribe to export customization events. 
            options.CustomizeSheetSettings += options_CustomizeSheetSettings;
            options.CustomizeSheetHeader += options_CustomizeSheetHeader;
            options.CustomizeCell += options_CustomizeCell;
            options.CustomizeSheetFooter += options_CustomizeSheetFooter;
            options.AfterAddRow += options_AfterAddRow;

            // Export the grid data to the XLSX format.
            gridControl1.ExportToXlsx("grid-export.xlsx", options);
            // Open the created document.
            Process.Start("grid-export.xlsx");
        }

        #region #AfterAddRowEvent
        void options_AfterAddRow(AfterAddRowEventArgs e) {
            // Merge cells in rows that correspond to the grid's group rows.
            if (e.DataSourceRowIndex < 0) {
                e.ExportContext.MergeCells(new XlCellRange(new XlCellPosition(0, e.DocumentRow-1), new XlCellPosition(5, e.DocumentRow-1)));
            }
        }
        #endregion #AfterAddRowEvent

        #region #CustomizeCellEvent
        // Specify the value alignment for Discontinued field.
        XlCellAlignment aligmentForDiscontinuedColumn = new XlCellAlignment() {
            HorizontalAlignment = XlHorizontalAlignment.Center,
            VerticalAlignment = XlVerticalAlignment.Center
        };

        void options_CustomizeCell(CustomizeCellEventArgs e){
            // Substitute Boolean values within the Discontinued column by special symbols.
            if(e.ColumnFieldName == "Discontinued"){
                if(e.Value is bool){
                    e.Handled = true;
                    e.Formatting.Alignment = aligmentForDiscontinuedColumn;
                    e.Value = ((bool) e.Value) ? "☑" : "☐";
                }
            }
        }
        #endregion #CustomizeCellEvent

        #region #CustomizeSheetHeaderEvent
        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;
        }
        Image imageToHeader;
        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.
            if(imageToHeader == null) {
                using(var fileStream = System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("GridDataAwareExportCustomization.Resources.1.jpg")) {
                    if(fileStream != null) 
                        imageToHeader = new Bitmap(Image.FromStream(fileStream));
                }
            }
            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;
        }
        #endregion #CustomizeSheetHeaderEvent

        #region #CustomizeSheetFooterEvent
        void options_CustomizeSheetFooter(ContextEventArgs e){
            // Add an empty row to the document's footer.
            e.ExportContext.AddRow();

            // Create a new row.
            var firstRow = new CellObject();
            // Specify row values.
            firstRow.Value = @"The report is generated from the NorthWind database.";
            // Specify the cell content alignment and font settings.
            var rowFormatting = CreateXlFormattingObject(true, 18);
            rowFormatting.Alignment.HorizontalAlignment = XlHorizontalAlignment.Left;
            firstRow.Formatting = rowFormatting;
            // Add the created row to the output document. 
            e.ExportContext.AddRow(new[]{ firstRow });

            // Create one more row.
            var secondRow = new CellObject();
            // Specify the row value. 
            secondRow.Value = @"The addresses and phone numbers are fictitious.";
            // Change the row's font settings.
            rowFormatting.Font.Size = 14;
            rowFormatting.Font.Bold = false;
            rowFormatting.Font.Italic = true;  
            secondRow.Formatting = rowFormatting;
            // Add this row to the output document.
            e.ExportContext.AddRow(new[]{ secondRow });
        }
        #endregion #CustomizeSheetFooterEvent

        #region #CustomizeSheetSettingsEvent
        void options_CustomizeSheetSettings(CustomizeSheetEventArgs e) {
            // Anchor the output document's header to the top and set its fixed height. 
            const int lastHeaderRowIndex = 15;
            e.ExportContext.SetFixedHeader(lastHeaderRowIndex);
            // Add the AutoFilter button to the document's cells corresponding to the grid column headers.
            e.ExportContext.AddAutoFilter(new XlCellRange(new XlCellPosition(0, lastHeaderRowIndex), new XlCellPosition(5, 100)));
        }
        #endregion #CustomizeSheetSettingsEvent

        void InitMDBData(string connectionString){
            using(var oleDBAdapter1 = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + tblGrid, connectionString))
                oleDBAdapter1.Fill(nwindDataSet.Products);
            using(var oleDBAdapter2 = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + tblLookUp, connectionString))
                oleDBAdapter2.Fill(nwindDataSet.Categories);
        }

        void InitNWindData(){
            var dbFileName = string.Empty;
            dbFileName = DevExpress.Utils.FilesHelper.FindingFileName(Application.StartupPath, "nwind.mdb");
            if(dbFileName != string.Empty){
                InitMDBData("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbFileName);
            }
        }

        void Form1_Load(object sender, EventArgs e){
            gridView1.BestFitColumns(true);
        }

    }
}

Form1.vb

Imports DevExpress.Export
Imports DevExpress.Export.Xl
Imports DevExpress.Printing.ExportHelpers
Imports DevExpress.XtraEditors
Imports DevExpress.XtraPrinting
Imports System
Imports System.Collections.Generic
Imports System.Diagnostics
Imports System.Drawing
Imports System.Threading
Imports System.Windows.Forms
Imports DevExpress.XtraGrid.Views.Base

Namespace GridDataAwareExportCustomization
    Partial Public Class Form1
        Inherits XtraForm

        Private ReadOnly tblGrid As String = "Products"
        Private Const tblLookUp As String = "Categories"

        Public Sub New()
            InitializeComponent()
            InitNWindData()
            gridView1.ExpandAllGroups()
        End Sub

        Private Sub gridView1_CustomUnboundColumnData(ByVal sender As Object, ByVal e As CustomColumnDataEventArgs) Handles gridView1.CustomUnboundColumnData
            Dim view As GridView = TryCast(sender, GridView)
            If e.Column = categoryName Then
                If e.IsGetData Then
                    Dim id As Integer = CInt(Math.Truncate(view.GetListSourceRowCellValue(e.ListSourceRowIndex, colCategoryID)))
                    e.Value = nwindDataSet.Categories.FindByCategoryID(id).CategoryName
                End If
            End If
        End Sub

        Private Sub btn_Export_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Export.Click
            ' Ensure that the data-aware export mode is enabled.
            ExportSettings.DefaultExportType = ExportType.DataAware
            ' Create a new object defining how a document is exported to the XLSX format.
            Dim options = New XlsxExportOptionsEx()
            ' Specify a name of the sheet in the created XLSX file.
            options.SheetName = "DevAV price"

            ' Subscribe to export customization events. 
            AddHandler options.CustomizeSheetSettings, AddressOf options_CustomizeSheetSettings
            AddHandler options.CustomizeSheetHeader, AddressOf options_CustomizeSheetHeader
            AddHandler options.CustomizeCell, AddressOf options_CustomizeCell
            AddHandler options.CustomizeSheetFooter, AddressOf options_CustomizeSheetFooter
            AddHandler options.AfterAddRow, AddressOf options_AfterAddRow

            ' Export the grid data to the XLSX format.
            gridControl1.ExportToXlsx("grid-export.xlsx", options)
            ' Open the created document.
            Process.Start("grid-export.xlsx")
        End Sub

        #Region "#AfterAddRowEvent"
        Private Sub options_AfterAddRow(ByVal e As AfterAddRowEventArgs)
            ' Merge cells in rows that correspond to the grid's group rows.
            If e.DataSourceRowIndex < 0 Then
                e.ExportContext.MergeCells(New XlCellRange(New XlCellPosition(0, e.DocumentRow-1), New XlCellPosition(5, e.DocumentRow-1)))
            End If
        End Sub
        #End Region ' #AfterAddRowEvent

        #Region "#CustomizeCellEvent"
        ' Specify the value alignment for Discontinued field.
        Private aligmentForDiscontinuedColumn As New XlCellAlignment() With {.HorizontalAlignment = XlHorizontalAlignment.Center, .VerticalAlignment = XlVerticalAlignment.Center}

        Private Sub options_CustomizeCell(ByVal e As CustomizeCellEventArgs)
            ' Substitute Boolean values within the Discontinued column by special symbols.
            If e.ColumnFieldName = "Discontinued" Then
                If TypeOf e.Value Is Boolean Then
                    e.Handled = True
                    e.Formatting.Alignment = aligmentForDiscontinuedColumn
                    e.Value = If(CBool(e.Value), "☑", "☐")
                End If
            End If
        End Sub
        #End Region ' #CustomizeCellEvent

        #Region "#CustomizeSheetHeaderEvent"
        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

        Dim imageToHeader As Image
        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.
            If imageToHeader Is Nothing Then
                Using fileStream = System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("GridDataAwareExportCustomization.Resources.1.jpg")
                    If fileStream IsNot Nothing Then
                        imageToHeader = New Bitmap(Image.FromStream(fileStream))
                    End If
                End Using
            End If
            Dim 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)))
        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
        #End Region ' #CustomizeSheetHeaderEvent

        #Region "#CustomizeSheetFooterEvent"
        Private Sub options_CustomizeSheetFooter(ByVal e As ContextEventArgs)
            ' Add an empty row to the document's footer.
            e.ExportContext.AddRow()

            ' Create a new row.
            Dim firstRow = New CellObject()
            ' Specify row values.
            firstRow.Value = "The report is generated from the NorthWind database."
            ' Specify the cell content alignment and font settings.
            Dim rowFormatting = CreateXlFormattingObject(True, 18)
            rowFormatting.Alignment.HorizontalAlignment = XlHorizontalAlignment.Left
            firstRow.Formatting = rowFormatting
            ' Add the created row to the output document. 
            e.ExportContext.AddRow({ firstRow })

            ' Create one more row.
            Dim secondRow = New CellObject()
            ' Specify the row value. 
            secondRow.Value = "The addresses and phone numbers are fictitious."
            ' Change the row's font settings.
            rowFormatting.Font.Size = 14
            rowFormatting.Font.Bold = False
            rowFormatting.Font.Italic = True
            secondRow.Formatting = rowFormatting
            ' Add this row to the output document.
            e.ExportContext.AddRow({ secondRow })
        End Sub
        #End Region ' #CustomizeSheetFooterEvent

        #Region "#CustomizeSheetSettingsEvent"
        Private Sub options_CustomizeSheetSettings(ByVal e As CustomizeSheetEventArgs)
            ' Anchor the output document's header to the top and set its fixed height. 
            Const lastHeaderRowIndex As Integer = 15
            e.ExportContext.SetFixedHeader(lastHeaderRowIndex)
            ' Add the AutoFilter button to the document's cells corresponding to the grid column headers.
            e.ExportContext.AddAutoFilter(New XlCellRange(New XlCellPosition(0, lastHeaderRowIndex), New XlCellPosition(5, 100)))
        End Sub
        #End Region ' #CustomizeSheetSettingsEvent

        Private Sub InitMDBData(ByVal connectionString As String)
            Using oleDBAdapter1 = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " & tblGrid, connectionString)
                oleDBAdapter1.Fill(nwindDataSet.Products)
            End Using
            Using oleDBAdapter2 = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " & tblLookUp, connectionString)
                oleDBAdapter2.Fill(nwindDataSet.Categories)
            End Using
        End Sub

        Private Sub InitNWindData()
            Dim dbFileName = String.Empty
            dbFileName = DevExpress.Utils.FilesHelper.FindingFileName(Application.StartupPath, "nwind.mdb")
            If dbFileName <> String.Empty Then
                InitMDBData("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbFileName)
            End If
        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
            gridView1.BestFitColumns(True)
        End Sub

    End Class
End Namespace