Documents for Excel, .NET Edition Documentation
Features / Data Binding
In This Topic
    Data Binding
    In This Topic

    GcExcel supports data binding which allows you to generate data bound reports and view them in Excel. Data binding can be achieved by binding a data source with a sheet, cell or table column. You can also perform JSON I/O of the binding path to interact with SpreadJS.  

    Sheet Binding

    A data source can be bound to a sheet by using the DataSource property of IWorksheet interface. The data sources supported for binding a sheet are DataTable or an IEnumerable collection. Each worksheet can have only one data source.

    To bind the data source fields to sheet columns automatically, you can set the AutoGenerateColumns property of IWorksheet interface to true. The default value is also true.

    To bind the data source fields to sheet columns manually, you can set the AutoGenerateColumns property of IWorksheet interface to false and use the BindingPath property of IRange interface to set the binding path of the data source field to the sheet columns.

    For eg. If you want to display the 'TeamName' field in column D, the binding path for the 'TeamName' field will be column D. 

    Refer to the below example code to bind a DataTable to the sheet columns manually.

    C#
    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
    
    DataTable teamInfo = new DataTable();
    teamInfo.Columns.Add(new DataColumn("ID", typeof(Int32)));
    teamInfo.Columns.Add(new DataColumn("Name", typeof(string)));
    teamInfo.Columns.Add(new DataColumn("Score", typeof(Int32)));
    teamInfo.Columns.Add(new DataColumn("Team", typeof(string)));
    
    teamInfo.Rows.Add(10, "Bob", 12, "Xi'An");
    teamInfo.Rows.Add(11, "Tommy", 6, "Xi'An");
    teamInfo.Rows.Add(12, "Jaguar", 15, "Xi'An");
    teamInfo.Rows.Add(12, "Lusia", 9, "Xi'An");
            
    IWorksheet worksheet = workbook.Worksheets[0];
    
    // Set AutoGenerateColumns as false
    worksheet.AutoGenerateColumns = false;
    
    //Bind columns manually.
    worksheet.Range["A:A"].EntireColumn.BindingPath = "ID";
    worksheet.Range["B:B"].EntireColumn.BindingPath = "Name";
    worksheet.Range["C:C"].EntireColumn.BindingPath = "Score";
    worksheet.Range["D:D"].EntireColumn.BindingPath = "Team";
    
    // Set data source
    worksheet.DataSource = teamInfo;
    
    //save to an excel file
    workbook.Save("SheetBindDatatable.xlsx");

     Cell Binding

    A data source can be bound to a cell by using the DataSource property of IWorksheet interface. The data source supported for binding a cell is custom object.

    The BindingPath property of IRange interface can be used to set the binding path of the data source field to a cell. For eg. If 'Area' field is to be displayed in cell A1, the binding path for the 'Area' field will be cell A1. 

    Refer to the below example code to bind custom object to cells.

    C#
    Copy Code
    public void CellBinding()
    {
    
        // create a new workbook
        Workbook workbook = new Workbook();
    
        var record = new SalesRecord
        {
            Area = "NorthChina",
            Salesman = "Hellen",
            Product = "Apple",
            ProductType = "Fruit",
            Sales = 120
        };
     
        IWorksheet worksheet = workbook.Worksheets[0];
    
        // Set binding path for cell.
        worksheet.Range["A1"].BindingPath = "Area";
        worksheet.Range["B2"].BindingPath = "Salesman";
        worksheet.Range["C2"].BindingPath = "Product";
        worksheet.Range["D3"].BindingPath = "ProductType";
    
        // Set data source.
        worksheet.DataSource = record;
    
        //save to an excel file
        workbook.Save("cellbinding.xlsx");
    
    }
    internal class SalesRecord
    {
        public string Area;
        public string Salesman;
        public string Product;
        public string ProductType;
        public int Sales;
    }

    Table Binding

    A data source can be bound to a table by using the DataSource property of IWorksheet interface. The data sources supported for binding a table are DataSet or custom object which contains an IEnumerable field or property. The BindingPath property of ITable interface can be used to set the binding path of data source to a table.

    To bind the data source fields to table columns automatically, you can set the AutoGenerateColumns property of IWorksheet interface to true. The default value is also true.

    To bind the data source fields to table columns manually, you can set the AutoGenerateColumns property of IWorksheet interface to false and use the DataField property of ITableColumn interface to set the binding path of the data source field to the table columns.

    For eg. 'T1' DataTable is bound to the first table and 'ID' field is bound to the first column of table.

    Refer to the below example code to bind a dataset to table columns manually.

    C#
    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
            
    // DataSet
    var team1 = new DataTable("T1");
    team1.Columns.Add(new DataColumn("ID", typeof(Int32)));
    team1.Columns.Add(new DataColumn("Name", typeof(string)));
    team1.Columns.Add(new DataColumn("Score", typeof(Int32)));
    team1.Columns.Add(new DataColumn("Team", typeof(string)));
    
    team1.Rows.Add(10, "Bob", 12, "Xi'An");
    team1.Rows.Add(11, "Tommy", 6, "Xi'An");
    team1.Rows.Add(12, "Jaguar", 15, "Xi'An");
    team1.Rows.Add(12, "Lusia", 9, "Xi'An");
    
    var team2 = new System.Data.DataTable("T2");
    team2.Columns.Add(new DataColumn("ID", typeof(Int32)));
    team2.Columns.Add(new DataColumn("Name", typeof(string)));
    team2.Columns.Add(new DataColumn("Score", typeof(Int32)));
    team2.Columns.Add(new DataColumn("Team", typeof(string)));
    
    team2.Rows.Add(2, "Phillip", 9, "BeiJing");
    team2.Rows.Add(3, "Hunter", 10, "BeiJing");
    team2.Rows.Add(4, "Hellen", 8, "BeiJing");
    team2.Rows.Add(5, "Jim", 9, "BeiJing");
    
    var datasource = new System.Data.DataSet();
    datasource.Tables.Add(team1);
    datasource.Tables.Add(team2);
    
    IWorksheet worksheet = workbook.Worksheets[0];
    
    // Add tables
    ITable table = worksheet.Tables.Add(worksheet.Range["B2:E6"], true);
    ITable table2 = worksheet.Tables.Add(worksheet.Range["G2:J6"], true);
    
    // Set not to auto generate table columns
    table.AutoGenerateColumns = false;
    table2.AutoGenerateColumns = false;
    
    // Set table binding path
    table.BindingPath = "T1";
    table2.BindingPath = "T2";
    
    // Set table column data field
    table.Columns[0].DataField = "ID";
    table.Columns[1].DataField = "Name";
    table.Columns[2].DataField = "Score";
    table.Columns[3].DataField = "Team";
    
    table2.Columns[0].DataField = "ID";
    table2.Columns[1].DataField = "Name";
    table2.Columns[2].DataField = "Score";
    table2.Columns[3].DataField = "Team";
    
    // Set DataSet as datasource
    worksheet.DataSource = datasource;
    
    //save to an excel file
    workbook.Save("TableBindDataset.xlsx");

     

    Limitation

    GcExcel supports one-time data binding which means that the data will be populated only the first time when data source is set, afterwards the data will not change even if the data in datasource changes.