Data Binding

DsExcel 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 JSON string, 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.

public class Sheetbinding {

    public static void main(String[] args) throws Exception {
        // 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 and JSON string.

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.

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, JSON string 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.

DsExcel Java also provides ITable.setExpandBoundRows method to handle how a bound table should respond to the changes in data source. When the property is set to true, the bound table automatically adjusts the number of rows to accommodate data source changes. When this property is set to false (default), table behaves like Excel and only add or delete cells instead of entire rows to reflect changes of data source.

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

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 ExpandBoundRows to true.
    table.setExpandBoundRows(true);

    // 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

DsExcel 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.

Sheet Binding
Cell Binding
Table Binding