在jxls操作excel文件一文中,提到可以使用<jx:forEach>进行迭代处理,JXLS中还有另一种循环方式,且看如下示例。
Excel模板如下所示:
现有类Staff,内容如下所示:
- /**
- *
- */
- package com.geloin.jxls.beans;
- /**
- * @author Geloin
- *
- */
- public class Staff {
- public Staff(String name, Double payment, Double bonus) {
- super();
- this.name = name;
- this.payment = payment;
- this.bonus = bonus;
- }
- /**
- * 名称
- */
- private String name;
- /**
- * 薪资
- */
- private Double payment;
- /**
- * 年终奖
- */
- private Double bonus;
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public Double getPayment() {
- return payment;
- }
- public void setPayment(Double payment) {
- this.payment = payment;
- }
- public Double getBonus() {
- return bonus;
- }
- public void setBonus(Double bonus) {
- this.bonus = bonus;
- }
- }
有类Department,如下所示:
- /**
- *
- */
- package com.geloin.jxls.beans;
- import java.util.List;
- /**
- * @author Geloin
- *
- */
- public class Department {
- /**
- * 名称
- */
- private String name;
- /**
- * 职员
- */
- private List<Staff> staffs;
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public List<Staff> getStaffs() {
- return staffs;
- }
- public void setStaffs(List<Staff> staffs) {
- this.staffs = staffs;
- }
- }
如上所示,Department中有一个属性staffs,表示的是本部门的职员列表,这个属性是一个List<Staff>类型的对象,现在我们为上文中的模板填充内容:
- /**
- *
- */
- package com.geloin.jxls.main;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import net.sf.jxls.transformer.XLSTransformer;
- import com.geloin.jxls.beans.Department;
- import com.geloin.jxls.beans.Staff;
- /**
- * @author Geloin
- *
- */
- public class DepartmentTest {
- /**
- * @param args
- */
- public static void main(String[] args) throws Exception {
- String tplPath = "d:/work/proTmp/jxls/部门信息表模板.xlsx";
- String destPath = "d:/work/proTmp/jxls/部门信息表.xlsx";
- Department dept1 = new Department();
- dept1.setName("信息部");
- List<Staff> staffs = new ArrayList<Staff>();
- staffs.add(new Staff("张三", 5000D, 2000D));
- staffs.add(new Staff("李四", 6000D, 3000D));
- dept1.setStaffs(staffs);
- Department dept2 = new Department();
- dept2.setName("信息部");
- List<Staff> staffs2 = new ArrayList<Staff>();
- staffs2.add(new Staff("王五", 7000D, 4000D));
- staffs2.add(new Staff("何六", 8000D, 5000D));
- dept2.setStaffs(staffs2);
- Map<String, Department> beanParams = new HashMap<String, Department>();
- beanParams.put("dept", dept1);
- XLSTransformer former = new XLSTransformer();
- former.transformXLS(tplPath, beanParams, destPath);
- }
- }
你会看到生成的Excel表格如下所示:
如上文中所描述,通过${dept.staffs.name},我们取到了dept对象中,每个员工的名称,显然,通过dept.staffs.proName,我们迭代了Department中的staffs对象。
模板中还有一个特殊的地方——$[sum(C2:E2)]//dept.staffs,我们已经知道,$[sum(c2:e2)]计算的是第2行C至E列的和,而//dept.staffs可以理解为per dept's staffs,也就是说,$[sum(C2:E2)]//dept.staffs表示的是“统计部门的每个staffs对象的从C到E列的和”。