[]
产品首页 在线Demo 产品文档 API参考 技术社区
立即试用
(Showing Draft Content)

Data Binding

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

//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");

Bind sheet to a JSON string

Refer to the below example code to bind a sheet to a JSON string.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
        
// Get data from json file.
string jsonText = string.Empty;
using (Stream stream = GetResourceStream("json\\DataBinding.json"))
using (StreamReader reader = new StreamReader(stream))
{
    jsonText = reader.ReadToEnd();
}
        
// jsonText content.
// [
//   {"Area": "North America","City": "Chicago","Category": "Consumer Electronics","Name": "Bose 785593-0050","Revenue": 92800},
//   {"Area": "North America","City": "New York","Category": "Consumer Electronics","Name": "Bose 785593-0050","Revenue": 92800},
//   {"Area": "South America","City": "Santiago","Category": "Consumer Electronics","Name": "Bose 785593-0050","Revenue": 19550}
// ]
        
IWorksheet worksheet = workbook.ActiveSheet;
        
worksheet.DataSource = new JsonDataSource(jsonText);
        
// Save to an excel file
workbook.Save("JsonSource.xlsx");

}

Cell Binding

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

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.

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

GcExcel.NET also provides ITable.ExpandBoundRows property 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 dataset to table columns manually.

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

// Let bound table expand to accomodate data source changes 
table.ExpandBoundRows = true;

// table2 does not expand with data source
table2.ExpandBoundRows = false;

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