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.
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.
| 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"); | |
Bind sheet to a JSON string
Refer to the below example code to bind a sheet to a JSON string.
| C# |
Copy Code |
|---|---|
//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"); } | |
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.
| 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; } | |
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.
DsExcel.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.
| 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"; // 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
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.
