关于jxls的层级循环研究

最新推荐文章于 2024-02-09 18:03:54 发布

转载最新推荐文章于 2024-02-09 18:03:54 发布 · 1.2k 阅读

4 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/xlpapapa/blog/887985

文章标签：

#java #数据库 #人工智能

本文介绍使用jXLS插件处理Excel文件的方法，包括如何通过模板实现数据的多级遍历及汇总。提供了两种实现思路，一种是构建多层次实体类，另一种是直接在模板中进行层级判断。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

最近老大叫我看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