最近老大 叫我看jxls 它是一个基于poi的一个利用excel模板生成excel的插件 其原理就是 读取模板 (里面写的类似于 jstl)再把一个装有各种list或对象的map 传入方法中 就可以输出一个 装有数据并且格式按照模板来的 excel文件 相较于传统的poi 它的优势 不仅大大减少了代码量 而且 也更直观的对样式进行修改。讲道理 这个版本应该是1.0.6,可以去maven仓库下载依赖包 有poi等。
对于分层次的遍历 后台没有处理 就放进模板上 处理 有两种思路 方法,毕竟我是超级菜鸟 而且 也是 写着理思路的 各位大神 若有不足之处 还望点出 。
方法一:
- 已省市区为例 省有很多市 作为一个list的属性 市有很多区 也是作为list的属性
- 然后 就是实体的代码
package com.icom.util.base.test.entity;
import java.util.List;
public class Province {
private String name;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Province(String name, List<City> cities) {
super();
this.name = name;
this.cities = cities;
}
public Collection<City> getCities() {
return cities;
}
public void setCities(Collection<City> cities) {
this.cities = cities;
}
private Collection<City> cities;
}
package com.icom.util.base.test.entity;
import java.util.List;
public class City {
private String name;
private List<Area> areas;
public City(String name, List<Area> areas) {
super();
this.name = name;
this.areas = areas;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Area> getAreas() {
return areas;
}
public void setAreas(List<Area> areas) {
this.areas = areas;
}
}
package com.icom.util.base.test.entity;
public class Area {
private String name;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Area(String name) {
super();
this.name = name;
}
}
3.生成excel的主函数
package com.icom.util.base.test;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import com.icom.util.base.test.entity.Area;
import com.icom.util.base.test.entity.City;
import com.icom.util.base.test.entity.Province;
import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;
public class FinalTest {
public static List<Province> gen() throws ParseException {
Area area1 = new Area("梅列");
Area area2 = new Area("三元");
Area area3 = new Area("五桂山");
List<Area> areas1 = new ArrayList<Area>();
areas1.add(area1);
areas1.add(area2);
List<Area> areas2 = new ArrayList<Area>();
areas2.add(area3);
City city1 = new City("三明市", areas1);
City city2 = new City("中山市", areas2);
List<City> cities1 = new ArrayList<City>();
cities1.add(city1);
List<City> cities2 = new ArrayList<City>();
cities2.add(city2);
Province p1 = new Province("福建省", cities1);
Province p2 = new Province("广东省", cities2);
List<Province> provinces = new ArrayList<Province>();
provinces.add(p1);
provinces.add(p2);
return provinces;
}
public static void main(String[] args)
throws ParsePropertyException, InvalidFormatException, IOException, ParseException {
// OutputStream os = new FileOutputStream("d://test//new.xls");
String templateFile = "d://test//sheng.xls";
Map<String, Object> beans = new HashMap<String, Object>();
// fruits
List<Province> data = gen();
beans.put("data", data);
XLSTransformer transformer = new XLSTransformer();
try {
transformer.transformXLS(templateFile, beans, "d://test//finalTest.xls");
} catch (IOException ie) {
ie.printStackTrace();
} catch (ParsePropertyException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
4.写好excel的模板
地区名称 | 人数 |
<jx:forEach items="${data}" var="province"> | |
${province.name} | |
<jx:forEach items="${province.cities}" var="city"> | |
${city.name} | |
<jx:forEach items="${city.areas}" var="area"> | |
${area.name} | ${area.personNum} |
</jx:forEach> | |
市人数: | $[SUM(B7)] |
</jx:forEach> | |
省人数: | $[SUM(B7)] |
</jx:forEach> | |
总人数: | $[SUM(B7)] |
就可以得到有层次感的 东东
方法二
- 方法一要事先在 后台装好就是 在数据库里得到数据后 要用逻辑拼一遍对象 装一遍属性 如果直接后台数据库传 到模板上运行 也蛮好的 就是方法二
- 这个实例 用银行来做例子 关系为 总行----分行-----支行
- 实体类:
package com.icom.util.base.test.model; public class Bank { private int level; private int id; private int parentId; private String name; private int personSum; public String getName() { return name; } public void setName(String name) { this.name = name; } public int getPersonSum() { return personSum; } public void setPersonSum(int personSum) { this.personSum = personSum; } public int getLevel() { return level; } public void setLevel(int level) { this.level = level; } public int getId() { return id; } public Bank(int level, int id, int parentId, String name, int personSum) { super(); this.level = level; this.id = id; this.parentId = parentId; this.name = name; this.personSum = personSum; } public void setId(int id) { this.id = id; } public int getParentId() { return parentId; } public void setParentId(int parentId) { this.parentId = parentId; } }
这个银行实体类 有等级 和 父节点两个属性 足以分层
4.模拟数据 生成 excel文件代码:
package com.icom.util.base.test;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import com.icom.util.base.test.entity.Area;
import com.icom.util.base.test.entity.City;
import com.icom.util.base.test.entity.Province;
import com.icom.util.base.test.model.Bank;
import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;
public class BankTest {
public static List<Bank> gen() throws ParseException {
Bank Bank1 = new Bank(1, 1, 0, "广州工商总行", 500);
Bank Bank2 = new Bank(2, 2, 1, "中山工商分行", 200);
Bank Bank3 = new Bank(3, 3, 2, "五桂山支行", 20);
Bank Bank4 = new Bank(3, 4, 2, "东区支行", 23);
Bank Bank5 = new Bank(3, 5, 2, "火炬支行", 30);
List<Bank> banks = new ArrayList<Bank>();
banks.add(Bank1);
banks.add(Bank2);
banks.add(Bank3);
banks.add(Bank4);
banks.add(Bank5);
return banks;
}
public static void main(String[] args)
throws ParsePropertyException, InvalidFormatException, IOException, ParseException {
// OutputStream os = new FileOutputStream("d://test//new.xls");
String templateFile = "d://test//bankdemo.xls";
Map<String, Object> beans = new HashMap<String, Object>();
// fruits
List<Bank> data = gen();
beans.put("data", data);
XLSTransformer transformer = new XLSTransformer();
try {
transformer.transformXLS(templateFile, beans, "d://test//bankResult.xls");
} catch (IOException ie) {
ie.printStackTrace();
} catch (ParsePropertyException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
5.excel模板
银行 | 人数 |
<jx:forEach items="${data}" var="first"> | |
<jx:if test="${first.level==1}"> | |
${first.name} | ${first.personSum} |
<jx:forEach items="${data}" var="second"> | |
<jx:if test="${second.level==2 && first.id==second.parentId}"> | |
${second.name} | ${second.personSum} |
<jx:forEach items="${data}" var="third"> | |
<jx:if test="${third.level==3 && second.id==third.parentId}"> | |
${third.name} | ${third.personSum} |
</jx:if> | |
</jx:forEach> | |
支行总人数: | $[SUM(B10)] |
</jx:if> | |
</jx:forEach> | |
分行总人数: | $[SUM(B7)+SUM(B5)] |
</jx:if> | |
</jx:forEach> | |
总行总人数: | $[SUM(B7)+SUM(B10)+SUM(B4)] |
6.生成结果:
银行 | 人数 |
广州工商总行 | 500 |
中山工商分行 | 200 |
五桂山支行 | 20 |
东区支行 | 23 |
火炬支行 | 30 |
支行总人数: | 73 |
分行总人数: | 400 |
总行总人数: | 773 |
以上 就是我的粗浅的对于 jxls 多重循环 分级 的理解 第二篇博客 fighting!