Documents for Excel .NET Edition | GCDocuments
Templates / Data Source Binding
In This Topic
    Data Source Binding
    In This Topic

    Once the template layout is prepared in Excel including bound fields, expressions, formula and sheet name fields, these fields need to be bound to a data source. You can add a data source using the AddDataSource method and bind the data with template using the ProcessTemplate method. This will populate the data from datasource in the template fields to generate the Excel report.

    Also, you can use multiple data sources or multiple data tables within a data source and populate data through them. The syntax requires you to define the object of the data source followed by the data field. For example, the below template layout merges data from two data sources, the employee information from one data table and Department information from another table.

    Multiple data sources

    GcExcel supports the below data sources while using templates:

    DataTable

    A single table which has collection of rows and columns from any type of database

     

    Template syntax

    [Alias of data source].[Column name]

    For example:

    {{ds.ID}}

    {{ds.Name}}

    Bind DataTable datasource

    C#
    Copy Code
    var datasource = new System.Data.DataTable();
     datasource.Columns.Add(new DataColumn("ID", typeof(Int32)));
     datasource.Columns.Add(new DataColumn("Name", typeof(string)));
     datasource.Columns.Add(new DataColumn("Score", typeof(Int32)));
     datasource.Columns.Add(new DataColumn("Team", typeof(string)));
     
     
     ...//Init data
     
     
     //Add data source
     workbook.AddDataSource("ds", datasource);

     

    DataSet

    A collection of one or more DataTables

     

    Template syntax

    [Alias of data source].[Table name].[Column name]

    For example:

    {{ds.Table1.ID}}

    {{ds.Table2.Team}}

     

    Bind DataSet datasource

    C#
    Copy Code
    var dTable1 = new System.Data.DataTable();
    var dTable2 = new System.Data.DataTable();
     
     
     ...//Init data
     
     
     var datasource = new System.Data.DataSet();
     datasource.Tables.Add(team1);
     datasource.Tables.Add(team2);
     
     
     //Add data source
     workbook.AddDataSource("ds", datasource);

    Custom Object

    A user-defined object from user code or serialized object of JSON String/File/XML, etc. GcExcel Template supports any data source that can be serialized as a custom object.

     

    Template syntax

    [Alias of data source].[Field name]

    or

    [Alias of data source].[Property name]

    For example:

    {{ds.Records.Area}}

    {{{ds.Records.Product}}

     

    Bind Custom Object datasource

    C#
    Copy Code
    var datasource = new SalesData
                 {
                     Records = new List<SalesRecord>()
                 };
     
                 var record1 = new SalesRecord
                 {
                     Area = "NorthChina",
                     Salesman = "Hellen",
                     Product = "Apple",
                     ProductType = "Fruit",
                     Sales = 120
                 };
                 datasource.Records.Add(record1);
     
                 var record2 = new SalesRecord
                 {
                     Area = "NorthChina",
                     Salesman = "Hellen",
                     Product = "Banana",
                     ProductType = "Fruit",
                     Sales = 143
                 };
                 datasource.Records.Add(record2);
     
     
     ...//Init data
     
     
     //Add data source
     workbook.AddDataSource("ds", datasource);

    JSON

    GcExcel allows you to create a new instance of JsonDataSource class as a custom object. Hence, users with json as their data source can directly fetch data from json file and construct a JsonDataSource from the json text and then use the JsonDataSource for the template.

    This eradicates the need to create a mapping class to fetch the data from Json and user can directly use a field or member of the json as given in template syntax below:

    Template Syntax

    [Alias of data source].[Field name]

    For example:

    {{ds.student.family.father.name}}

    {{ds.student.family.father.occupation}}

    {{ds.student.family.mother.name}}

    Sample JSON for Reference

    {
    
      "student": [
    
        {
    
          "name": "Jane",
    
          "address": "101, Halford Avenue, Fremont, CA",
    
          "family": [
    
            {
    
              "father": {
    
                "name": "Patrick James",
    
                "occupation": "Surgeon"
    
              },
    
              "mother": {
    
                "name": "Diana James",
    
                "occupation": "Surgeon"
    
              }
    
            },
    
            {
    
              "father": {
    
                "name": "father James",
    
                "occupation": "doctor"
    
              },
    
              "mother": {
    
                "name": "mother James",
    
                "occupation": "teacher"
    
              }
    
            }
    
          ]
    
        },
    
        {
    
          "name": "Mark",
    
          "address": "101, Halford Avenue, Fremont, CA",
    
          "family": [
    
            {
    
              "father": {
    
                "name": "Jonathan Williams",
    
                "occupation": "Product Engineer"
    
              },
    
              "mother": {
    
                "name": "Joanna Williams",
    
                "occupation": "Surgeon"
              }
    
            }
    
          ]
    
        }
    
      ]
    
    }
                    
    

    Bind JSON datasource 

    C#
    Copy Code
      // Load json text
      var jsonText = File.OpenText("Template_FamilyInfo.json").ReadToEnd();
    
      // Create a JsonDataSource
      var datasource = new JsonDataSource(jsonText);
    
      // Add data source
      workbook.AddDataSource("ds", datasource);

    Variable

    A user-defined variable in code

     

    Template Syntax

    [Alias of data source]

    For example:

    {{cName}}

    {{count}}

    {{owner}}

     

    Bind Variable datasource

    C#
    Copy Code
    var className = "Class 3";
     var count = 500;
     
     //Add data source
     workbook.AddDataSource("cName", datasource);
     workbook.AddDataSource("count", count);
     workbook.AddDataSource("owner", "Hunter Liu");

    Array or List

    A user-defined array or list in code

     

    Template syntax

    1. Array or List of base type variable(string, int , double, etc.)

    [Alias of data source]

     

    2. Array or List of custom object

    [Alias of data source].[Field name]

    or

    [Alias of data source].[Property name]

    For example:

    {{p.Name}}

    {{p.Age}}

    {{countries}}

    {{numbers}}

     

    Bind Array or List datasource

    C#
    Copy Code
    int[] numbers = new int[] { 10, 12, 8, 15};
     List<string> countries = new List<string>() { "USA", "Japan", "UK", "China" };
     
     List<Person> peoples = new List<Person>();
     
     Person p1 = new Person();
     p1.Name = "Helen";
     p1.Age = 12;
     peoples.Add(p1);
     
     Person p2 = new Person();
     p2.Name = "Jack";
     p2.Age = 23;
     peoples.Add(p2);
     
     Person p3 = new Person();
     p3.Name = "Fancy";
     p3.Age = 25;
     peoples.Add(p3);
     
     workbook.AddDataSource("p", peoples);
     workbook.AddDataSource("countries", countries);
     workbook.AddDataSource("numbers", numbers);