Templates / Data Source Binding
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

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

Java
Copy Code
//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

Java
Copy Code
        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);

Custom Data Table

A user-defined custom table is a collection of rows and columns from any type of database. DsExcel 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]

or

[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:

Java
Copy Code
public class CustomDataTable {

    public static void main(String[] args) throws Exception {
        // Initialize Workbook.
        Workbook workbook = new Workbook();
        
        // Open template file.
        workbook.open("ComplexMultiDataSource.xlsx");
        
        // Create table data sources from JSON.
        InputStream order_json;
        order_json = new FileInputStream("order.json");
        InputStream customer_json;
        customer_json = new FileInputStream("customer.json");
        InputStream product_json;
        product_json = new FileInputStream("product.json"); 
        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.getWorksheets().get(0).getRange("A:F").setColumnWidth(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.
class JsonTable implements ITableDataSource {
    private final JsonArray _jsonArray;
    private final HashMap<Integer, String> _columnsMap;
    private final HashMap<String, Integer> _reversedColumnsMap;

    public JsonTable(InputStream inputStream) {
        String jsonContent;
        try {
            jsonContent = convertToString(inputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        _jsonArray = JsonParser.parseString(jsonContent).getAsJsonArray();
        JsonObject jsonObject = _jsonArray.get(0).getAsJsonObject();
        _columnsMap = new HashMap<>();
        _reversedColumnsMap = new HashMap<>();
        int index = 0;
        for (Map.Entry<String, JsonElement> entry : jsonObject.entrySet()) {
            String next = entry.getKey();
            _columnsMap.put(index, next);
            _reversedColumnsMap.put(next, index);
            index++;
        }
    }

    private String convertToString(InputStream inputStream) throws IOException {
        StringBuilder stringBuilder = new StringBuilder();
        String line;
        try (BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8))) {
            while ((line = bufferedReader.readLine()) != null) {
                stringBuilder.append(line);
            }
        }
        return stringBuilder.toString();
    }

    // Get value from data source.
    @Override
    public Object getValue(int row, int column) {
        return _jsonArray.get(row).getAsJsonObject().get(_columnsMap.get(column)).getAsString();
    }

    // Get row count from data source.
    @Override
    public int getRowCount() {
        return _jsonArray.size();
    }

    // Get column count from data source.
    @Override
    public int getColumnCount() {
        return _columnsMap.size();
    }

    // Get column name from data source.
    @Override
    public String getColumnName(int column) {
        if (_columnsMap.containsKey(column)) {
            return _columnsMap.get(column);
        }
        return null;
    }

    // Get column index from data source.
    @Override
    public int getColumnIndex(String columnName) {
        if (_reversedColumnsMap.containsKey(columnName)) {
            return _reversedColumnsMap.get(columnName);
        }

        return -1;
    }
}

You can also view the demo to learn more about Custom Data Table.

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

JSON
Copy Code
{
    "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

Java
Copy Code
//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

Java
Copy Code
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

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

Cancel Template Processing

DsExcel allows you to cancel processTemplate method of Workbook class and IWorkbook interface by using an overload of processTemplate method that takes a parameter of CancellationToken type. The cancellation is thread-safe, i.e., the cancellation request is sent by a thread that does not own the workbook. You can use the following methods of CancellationTokenSource to send signals to CancellationToken:

Methods Description
cancel Cancels the method immediately.
cancelAfter Cancels the method after specific delay time.
close Releases all resources used by current instance.

The overload of processTemplate method cancels the process when the following conditions are met:

Note: You must decide whether to accept the partially expanded template or revert to the previous state. If you want to revert to the previous state, you must serialize the workbook before calling the processTemplate method and then deserialize after canceling the operation.

Refer to the following example code to cancel template processing on request or after a timeout is reached:

Java
Copy Code
// Create a new workbook.
Workbook workbook = new Workbook();
        
// Load template file from resource.
workbook.open("Template_SalesDataGroup_DataTable.xlsx");

// Add data to DataTable.
SalesData datasource = new SalesData();
datasource.sales = new ArrayList<SalesRecord>();

System.out.println("Creating test data.");

String[] areas = new String[]{"North America", "South America"};
String[] cities = new String[]{"Chicago", "New York", "Santiago", "Quito", "Fremont", "Buenos Aires", "Medillin", "Minnesota"};
String[] categories = new String[]{"Consumer Electronics", "Mobile"};
String[] category1NamePrefixes = new String[]{"Bose ", "Canon ", "Haier ", "IFB ", "Mi ", "Sennheiser "};
String[] category2NamePrefixes = new String[]{"iPhone ", "OnePlus ", "Redmi ", "Samsung "};

Random rand = new Random();
        
// You can increase the loop count if the demo is too fast on your computer.
for (int i = 0; i < 50000; i++) {
    SalesRecord item = new SalesRecord();
    item.area = areas[rand.nextInt(areas.length)];
    item.city = cities[rand.nextInt(cities.length)];
    int categoryId = rand.nextInt(categories.length);
    item.category = categories[categoryId];
    String[] names = (categoryId == 0) ? category1NamePrefixes : category2NamePrefixes;
    item.name = names[rand.nextInt(names.length)] + (10 + rand.nextInt(10000 - 10));
    item.revenue = 10000 + rand.nextInt(100000 - 10000);
    datasource.sales.add(item);
}

// Add template global settings.
workbook.getNames().add("TemplateOptions.KeepLineSize", "true");

// Add data source.
workbook.addDataSource("ds", datasource.sales);

// Cancel data source binding when cancel key is pressed or timeout is reached.
try (CancellationTokenSource cancellation = new CancellationTokenSource()) {
    /* Important:To handle the cancel key event of the console, we are using `sun.misc.Signal` in this sample.
       However, the classes of `sun.misc` might be unavailable in your JDK, because they are restricted APIs.
       You need to either use `jdk.internal.misc.*` (Java 9+) or use
       3rd-party components (such as `javax.realtime.POSIXSignalHandler`) to handle
       the console cancel key event if `sum.misc` is unavailable.*/

     final sun.misc.SignalHandler[] oldSignal = new sun.misc.SignalHandler[1];
     sun.misc.SignalHandler cancelHandler = sig -> {
        
         // Exit the process.
         cancellation.cancel();
         // Invoke rest of the handlers of Console.CancelKeyPress.
         if (oldSignal[0] != null) {
             oldSignal[0].handle(sig);
         }
     };

     sun.misc.Signal cancelKeyPressSignal = new sun.misc.Signal("INT");
     oldSignal[0] = sun.misc.Signal.handle(cancelKeyPressSignal, cancelHandler);

  // Cancel when timeout is reached.
    cancellation.cancelAfter(Duration.ofSeconds(10));
    System.out.println("Start ProcessTemplate.");
    try {
        workbook.processTemplate(cancellation.getToken());
        System.out.println("ProcessTemplate finished.");
    } catch (java.util.concurrent.CancellationException ex) {
        System.out.println("ProcessTemplate was canceled.");
    }

     sun.misc.Signal.handle(cancelKeyPressSignal, oldSignal[0]);
} catch (Exception e) {
    throw new RuntimeException(e);
}
    
// Save the Workbook.
workbook.save("CancelTemplateProcessing.xlsx");