Documents for Excel, Java 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 setDataSource method 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 setAutoGenerateColumns method 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 setAutoGenerateColumns method of IWorksheet interface to false and use the setBindingPath method 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 datasource to the sheet columns manually.

    Java
    Copy Code
     public class SheetBinding {
    
        // create a new workbook
        Workbook workbook = new Workbook();
        // Fetch default worksheet
        IWorksheet worksheet = workbook.getWorksheets().get(0);
    
        // create datasource
        SalesData datasource = new SalesData();
        datasource.records = new ArrayList<SalesRecord>();
    
        // Add data
        SalesRecord record1 = new SalesRecord();
        record1.area = "NorthChina";
        record1.salesman = "Hellen";
        record1.product = "Apple";
        record1.productType = "Fruit";
        record1.sales = 120;
        datasource.records.add(record1);
    
        SalesRecord record2 = new SalesRecord();
        record2.area = "NorthChina";
        record2.salesman = "Hellen";
        record2.product = "Banana";
        record2.productType = "Fruit";
        record2.sales = 143;
        datasource.records.add(record2);
    
        SalesRecord record3 = new SalesRecord();
        record3.area = "NorthChina";
        record3.salesman = "Hellen";
        record3.product = "Kiwi";
        record3.productType = "Fruit";
        record3.sales = 322;
        datasource.records.add(record3);
        
    
        // Set AutoGenerateColumns to false
        worksheet.setAutoGenerateColumns(false);
    
        // Bind columns manually
        worksheet.getRange("A:A").getEntireColumn().setBindingPath("area");
        worksheet.getRange("B:B").getEntireColumn().setBindingPath("salesman");
        worksheet.getRange("C:C").getEntireColumn().setBindingPath("product");
        worksheet.getRange("D:D").getEntireColumn().setBindingPath("productType");
        worksheet.getRange("E:E").getEntireColumn().setBindingPath("sales");
    
        // Set data source
        worksheet.setDataSource(datasource.records);
    
        // save to an excel file
        workbook.save("SheetBinding.xlsx");
    
    }
    
    public static class SalesRecord {
        public int sales;
        public String productType;
        public String product;
        public String salesman;
        public String area;
    }
    
    public static class SalesData {
        public ArrayList<SalesRecord> records;
    }

     Cell Binding

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

    The setBindingPath method 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 datasource to cells.

    Java
    Copy Code
    public static void CellBinding {
    
        // create a new workbook
        Workbook workbook = new Workbook();
        // Fetch default worksheet
        IWorksheet worksheet = workbook.getWorksheets().get(0);
    
        // Add Data
        SalesRecord record = new SalesRecord();
        record.area = "NorthChina";
        record.salesman = "Hellen";
        record.product = "Apple";
        record.productType = "Fruit";
        record.sales = 120;
    
        // Set binding path for cells
        worksheet.getRange("A1").setBindingPath("area");
        worksheet.getRange("B2").setBindingPath("salesman");
        worksheet.getRange("C2").setBindingPath("product");
        worksheet.getRange("D3").setBindingPath("productType");
    
        // Set data source
        worksheet.setDataSource(record);
    
        // save to an excel file
        workbook.save("CellBinding.xlsx");
    
    }
    
    public static class SalesRecord {
        public int sales;
        public String productType;
        public String product;
        public String salesman;
        public String area;
    }

    Table Binding

    A data source can be bound to a table by using the setDataSource method of IWorksheet interface. The data sources supported for binding a table are DataSet or custom object which contains an IEnumerable field or property. The setBindingPath method 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 setAutoGenerateColumns method 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 setAutoGenerateColumns method of IWorksheet interface to false and use the setDataField method 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 datasource to table columns manually.

    Java
    Copy Code
    public class TableBinding {
    
        
        // create a new workbook
        Workbook workbook = new Workbook();
        // Fetch default worksheet
        IWorksheet worksheet = workbook.getWorksheets().get(0);
    
        // create datasource
        SalesData datasource = new SalesData();
        datasource.records = new ArrayList<SalesRecord>();
    
        // Add data
        SalesRecord record1 = new SalesRecord();
        record1.area = "NorthChina";
        record1.salesman = "Hellen";
        record1.product = "Apple";
        record1.productType = "Fruit";
        record1.sales = 120;
        datasource.records.add(record1);
    
        SalesRecord record2 = new SalesRecord();
        record2.area = "NorthChina";
        record2.salesman = "Hellen";
        record2.product = "Banana";
        record2.productType = "Fruit";
        record2.sales = 143;
        datasource.records.add(record2);
    
        SalesRecord record3 = new SalesRecord();
        record3.area = "NorthChina";
        record3.salesman = "Hellen";
        record3.product = "Kiwi";
        record3.productType = "Fruit";
        record3.sales = 322;
        datasource.records.add(record3);
    
        // Add a table
        ITable table = worksheet.getTables().add(worksheet.getRange("B2:F5"), true);
    
        // Set not to auto generate table columns
        table.setAutoGenerateColumns(false);
    
        // Set table binding path
        table.setBindingPath("records");
    
        // Set table column data field
        table.getColumns().get(0).setDataField("area");
        table.getColumns().get(1).setDataField("salesman");
        table.getColumns().get(2).setDataField("product");
        table.getColumns().get(3).setDataField("productType");
        table.getColumns().get(4).setDataField("sales");
    
        // Set custom object as data source
        worksheet.setDataSource(datasource);
    
        // save to an excel file
        workbook.save("BindTableToCustomObject.xlsx");
    
    }
    
    public static class SalesRecord {
        public int sales;
        public String productType;
        public String product;
        public String salesman;
        public String area;
    }
    
    public static class SalesData {
        public ArrayList<SalesRecord> records;
    }

     

    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.