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.

DsExcel supports the below data sources while using templates:

ResultSet

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

Template syntax

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

For example:

{{ds.Table1.ID}}
{{ds.Table2.Team}}

Bind DataSource

//Here in the demo, we use a mock class to generate instance of java.sql.ResultSet.
//User who use template in product, must get instance of java.sql.ResultSet from the
//related database connection.
java.sql.ResultSet datasource = new GcMockResultSet(this.getResourceStream("score.csv"));
 
//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. DsExcel 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 DataSource

        NestedDataTemplate_Student student2 = new NestedDataTemplate_Student();
        student2.name = "Mark";
        student2.address = "101, Halford Avenue, Fremont, CA";
        Family family3 = new Family();
 
        family3.father = new Guardian();
        family3.father.name = "Jonathan Williams";
        family3.father.setOccupation("Product Engineer");
        family3.mother = new Guardian();
        family3.mother.name = "Joanna Williams";
        family3.mother.setOccupation("Surgeon");
 
        student2.family = new ArrayList<Family>();
        student2.family.add(family3);
 
//Add data source
workbook.addDataSource("ds", student2);

JSON

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

//Get data from json file
String jsonText = "";
try {
    InputStream stream = getResourceStream("Template_FamilyInfo.json");

    ByteArrayOutputStream result = new ByteArrayOutputStream();
    byte[] buffer = new byte[1024];
    int length;
    while ((length = stream.read(buffer)) != -1) {
        result.write(buffer, 0, length);
    }

    jsonText = result.toString("UTF-8");
} catch (IOException e) {
    e.printStackTrace();
}

// Create a JsonDataSource
JsonDataSource 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 DataSource

String className = "Class 3";
int 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 DataSource

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