[]
在Excel中准备好模板配置(包含绑定字段、表达式、公式、工作表字段)后,需要将这些字段绑定一个数据源。这时候可以通过 AddDataSource 方法来添加一个数据源,并通过 ProcessTemplate 方法将数据绑定在模板上。这样做会将数据源中的数据填充到模板中,从而生成带有数据的Excel报告。
此外,你也可以将多个数据源或者多个数据表整合到一个数据源中,并通过它们填充数据。该语法要求遵循数据字段来定义数据源对象。例如,下面的模板布局将两个数据源合并成了一个,雇员信息在一个数据表中,部门信息在另一个数据表中。
GcExcel 模板功能时支持下面的数据源:
ResultSet
Custom Object
JSON
Variable
Array or List
支持任何数据库类型的行列集合形成的单表
模板语法:
[Alias of data source].[Table name].[Column name]
示例:
{{ds.Table1.ID}}
{{ds.Table2.Team}}
相关代码:
//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);
代码中用户定义的对象或者String/File/XML序列化的JSON对象。GcExcel模板支持任何可以被序列化成自定义对象的数据源。
模板语法:
[Alias of data source].[Field name] or [Alias of data source].[Property name]
示例:
{{ds.Records.Area}}
{{ds.Records.Product}}
相关代码:
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();
student2.family.add(family3);
//Add data source
workbook.addDataSource("ds", student2);
GcExcel允许您创建JsonDataSource类的新实例作为自定义对象。因此,使用json作为数据源的用户可以直接从json文件中获取数据,并从json文本中构造JsonDataSource,然后使用JsonDataSource作为模板。
这样就不需要创建映射类来从Json获取数据,用户可以直接使用Json的字段或成员,如下面的模板语法所示:
模板语法:
[Alias of data source].[Field name]
示例:
{{ds.student.family.father.name}}
{{ds.student.family.father.occupation}}
{{ds.student.family.mother.name}}
示例JSON供参考:
{
"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"
}
}
]
}
]
}
相关代码:
//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);
代码中用户定义的变量。
模板语法:
[Alias of data source]
示例:
{{cName}}
{{count}}
{{owner}}
相关代码:
String className = "Class 3";
int count = 500;
//Add data source
workbook.addDataSource("cName", datasource);
workbook.addDataSource("count", count);
workbook.addDataSource("owner", "Hunter Liu");
代码中用户定义的数组或列表。
模板语法:
基本类型的数组或列表(字符串,整型,双精度浮点型等。):[数据源别名]
自定义对象类型的数组或列表:[数据源别名].[字段名称] 或者 [数据源别名].[属性名称]
示例:
{{p.Name}}
{{p.Age}}
{{countries}}
{{numbers}}
相关代码:
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);