jxls专题之多Sheet页回填-外链论坛 - Powered by Discuz! Archiver

wrjc1hod 发表于 2024-8-17 10:12:24

jxls专题之多Sheet页回填

版本信息：Java8、jxls 2.14.0
本文介绍下根据模板生成多Sheet页的内容。运用到的jxls命令是 jx:each ,看名叫作跟java的for-each的each是有点那味了。
Sheet页内的多行数据回填和按模板回填多Sheet页用的都是 jx:each 命令，经过命令内区别的参数指定数据，配置多Sheet页重点指定 multisheet 的属性。
示例jx:each(items="sheets", var="sheet", multisheet="sheetNames", lastCell="H4")需求场景：
导出各个班级的学生的考试成绩，包括班级、班专家、学生、学生各科成绩。
jxls excel模版：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/1704a5d64c1b425cb7f7784ed0d4cbcd~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723889708&x-signature=8NHbUDoK%2FHLneBRhziLDEWHOWM0%3D" style="width: 50%; margin-bottom: 20px;">
 多sheet页到处模板
</div>
模版文件有两个页循环、行循环两个批注：jx:area(lastCell="H4")
jx:each(items="sheets", var="sheet", multisheet="sheetNames", lastCell="H4")jx:each(items=”sheet.students”var=”student” lastCell=”H4”) 重视不要忘记添加 jx:area(lastCell="H4") ，要先指定模版范围才能够正确解析！
直接导入测试数据，测试的数据样例放在文末，避免栈太多篇幅，核心代码是：public static void main(String[] args) {
try (InputStream in = new FileInputStream("/yourPath/multiSheet.xlsx");
FileOutputStream out = new FileOutputStream("/yourPath/多页导出.xlsx")) {
Context context =new Context();
context.putVar("sheetNames", Arrays.asList("一班", "二班"));
context.putVar("sheets", Arrays.asList(getClassOne(), getClassTwo()));
JxlsHelper.getInstance().processTemplate(in, out, context);
} catch (Exception e) {
throw newRuntimeException(e);
}
}Context经过putVar设置的key为 sheetNames 和 sheets ，与模版配置中所选取的key对应：jx:each(items="sheets", var="sheet", multisheet="sheetNames", lastCell="H4")查看导出的效果：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/4a1ad4eb8ddd46c2851c9a3c7ea8ff21~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723889708&x-signature=sQYK%2Fod01VsTxnhRlK%2FJ3rO6ik4%3D" style="width: 50%; margin-bottom: 20px;">
 一班成绩
</div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/8312629b096a43c1b86d9dcbeecff9ed~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723889708&x-signature=Z3M89V0MPLUz8y1o3wMCDl4MTl8%3D" style="width: 50%; margin-bottom: 20px;">
 二班成绩
</div>
必须重视的是，多页的配置通常意见和 area指定放在同一处，都放在sheet的第1个单元格（A1）上，避免显现不符合预期的导出文件。
如示例，把多页的配置放到A3位置：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/bbe7127aed7644a08f439d4a0ca9f7ae~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723889708&x-signature=95RZWRYzdWarkizg%2FuwpCvm%2BIjw%3D" style="width: 50%; margin-bottom: 20px;"></div>
看下导出来的数据：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/7489c697d7574bbb8c45d87120d28266~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723889708&x-signature=P0HywNeWCLVyAevGhr%2FG6Do9KdY%3D" style="width: 50%; margin-bottom: 20px;"></div>
能够看到前两行包含固定数据和配置数据都被清空了，说明默认状况下，多sheet页是从多sheet页配置的单元格的那一行起始生效的，之前行的模版数据和配置将会被清空，不会有数据。
完整测试代码：public class MultiSheetDemo {

public static void main(String[] args) {

try (InputStream in = new FileInputStream("/yourPath/multiSheet.xlsx");
FileOutputStream out = new FileOutputStream("/yourPath/多页导出.xlsx")) {
Context context = new Context();
context.putVar("sheetNames", Arrays.asList("一班", "二班"));
context.putVar("sheets", Arrays.asList(getClassOne(), getClassTwo()));
JxlsHelper.getInstance().processTemplate(in, out, context);
} catch (Exception e) {
throw new RuntimeException(e);
}
}

private static Class getClassOne() {
Class bClass = newClass();
bClass.setClassName("三年一班");
bClass.setMaster("一班张老师");
Student studentB = new Student();
studentB.setName("一班狗蛋");
studentB.setSex("男");
studentB.setA(33);
studentB.setB(20);
studentB.setC(10);
studentB.setD(5);
studentB.setE(8);
studentB.setF(20);
Student studentB1 =new Student();
studentB1.setName("一班铁柱");
studentB1.setSex("男");
studentB1.setA(10);
studentB1.setB(22);
studentB1.setC(31);
studentB1.setD(21);
studentB1.setE(3);
studentB1.setF(45);
bClass.setStudents(Arrays.asList(studentB,studentB1));
returnbClass;
}private static Class getClassTwo() {
Class aClass = new Class();
aClass.setClassName("三年二班");
aClass.setMaster("二班李老师");
Student studentA =new Student();
studentA.setName("二班小明");
studentA.setSex("男");
studentA.setA(100);
studentA.setB(100);
studentA.setC(90);
studentA.setD(80);
studentA.setE(95);
studentA.setF(100);
Student studentA1 = new Student();
studentA1.setName("二班李华");
studentA1.setSex("男");
studentA1.setA(100);
studentA1.setB(100);
studentA1.setC(100);
studentA1.setD(100);
studentA1.setE(95);
studentA1.setF(100);
aClass.setStudents(Arrays.asList(studentA, studentA1));
return aClass;
}

@Data
public static class Class{
private String className;
private String master;
private List<Student> students;
}

@Data
public static class Student{
private String name;
private String sex;
private int a;
private int b;
private int c;
private int d;
private int e;
private int f;

}
}

maqg 发表于 2024-8-29 05:24:48

期待楼主的下一次分享！”

页: [1]

外链论坛's Archiver

jxls专题之多Sheet页回填