Data Source Binding

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

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

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]

[Alias of data source].[Property name]

For example:

{{ds.Records.Area}}
{{{ds.Records.Product}}

Bind Custom Object datasource

var datasource = new SalesData
             {
                 Records = new List()
             };
 
             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);

Custom Data Table

A user-defined custom table is a collection of rows and columns from any type of database. GcExcel provides ITableDataSource interface to simplify creating custom data sources and handle them more efficiently. The following table lists all the members of ITableDataSource interface:

Members	Description
GetValue	Gets the value based on the row and column index.
GetRowCount	Gets the row count.
GetColumnCount	Gets the column count.
GetColumnName	Gets the column name based on the column index.
GetColumnIndex	Gets the column index based on the column name.

Template syntax

[Alias of data source].[Field name]

[Alias of data source].[Property name]

For example:

{{customer.name}}
{{product.name}}

Refer to the following example code to create a custom data table from a JSON stream using a class that transforms JSON stream into a structured table:

class Program
{
    static void Main(string[] args)
    {
        // Initialize Workbook.
        var workbook = new Workbook();

        // Open template file.
        workbook.Open("ComplexMultiDataSource.xlsx");

        // Create table data sources from JSON.
        Stream order_json = new FileStream("order.json", FileMode.Open);
        Stream customer_json = new FileStream("customer.json", FileMode.Open);
        Stream product_json = new FileStream("product.json", FileMode.Open);
        JsonTable order = new JsonTable(order_json);
        JsonTable customer = new JsonTable(customer_json);
        JsonTable product = new JsonTable(product_json);

        // Add data sources for template.
        workbook.AddDataSource("order", order);
        workbook.AddDataSource("customer", customer);
        workbook.AddDataSource("product", product);

        // Process the template.
        workbook.ProcessTemplate();

        // Set column width.
        workbook.Worksheets[0].Range["A:F"].ColumnWidth = 16;

        // Save to an Excel file.
        workbook.Save("CustomDataTable.xlsx");

    }
}

/*
The class is only part of the sample, not part of the product.
So it is not recommended to use this class in a production environment.
Please implement your own class that meets the requirements of the product.
*/
// Create a class to transform a JSON stream into a table and inherit ITableDataSource.
internal class JsonTable : ITableDataSource
{
    private readonly List<JsonElement> _jsonArray;
    private readonly Dictionary<int, string> _columnsMap;
    private readonly Dictionary<string, int> _reversedColumnsMap;

    public JsonTable(Stream inputStream)
    {
        string jsonContent;
        try
        {
            jsonContent = ConvertToString(inputStream);
        }
        catch (IOException e)
        {
            throw new Exception(e.Message, e);
        }

        _jsonArray = ToJsonArray(JsonDocument.Parse(jsonContent).RootElement.EnumerateArray());

        JsonElement jsonObject = _jsonArray[0];

        _columnsMap = new Dictionary<int, string>();
        _reversedColumnsMap = new Dictionary<string, int>();
        foreach (var property in jsonObject.EnumerateObject())
        {
            _columnsMap.Add(_columnsMap.Count, property.Name);
            _reversedColumnsMap.Add(property.Name, _reversedColumnsMap.Count);
        }
    }

    private List<JsonElement> ToJsonArray(ArrayEnumerator enumerator)
    {
        var jsonArray = new List<JsonElement>();
        while (enumerator.MoveNext())
        {
            jsonArray.Add(enumerator.Current);
        }
        return jsonArray;
    }

    private string ConvertToString(Stream inputStream)
    {
        using (StreamReader reader = new StreamReader(inputStream, Encoding.UTF8))
        {
            return reader.ReadToEnd();
        }
    }

    // Get column count from data source.
    public int GetColumnCount()
    {
        return _columnsMap.Count;
    }

    // Get column index from data source.
    public int GetColumnIndex(string columnName)
    {
        if (_reversedColumnsMap.TryGetValue(columnName, out int res))
        {
            return res;
        }

        return -1;
    }

    // Get column name from data source.
    public string GetColumnName(int column)
    {
        if (_columnsMap.TryGetValue(column, out string res))
        {
            return res;
        }

        return null;
    }

    // Get row count from data source.
    public int GetRowCount()
    {
        return _jsonArray.Count;
    }

    // Get value from data source.
    public object GetValue(int row, int column)
    {
        JsonElement jsonElement = _jsonArray[row].GetProperty(this.GetColumnName(column));

        switch (jsonElement.ValueKind)
        {
            case JsonValueKind.String:
                return jsonElement.GetString();
            case JsonValueKind.Number:
                return jsonElement.GetDouble();
            case JsonValueKind.True:
                return true;
            case JsonValueKind.False:
                return false;
            case JsonValueKind.Null:
            case JsonValueKind.Undefined:
            case JsonValueKind.Object:
            case JsonValueKind.Array:
            default:
                return null;
        }
    }
}

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

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

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]

[Alias of data source].[Property name]

For example:

{{p.Name}}
{{p.Age}}
{{countries}}
{{numbers}}

Bind Array or List datasource

int[] numbers = new int[] { 10, 12, 8, 15};
 List countries = new List() { "USA", "Japan", "UK", "China" };
 
 List peoples = new List();
 
 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);