1.引入pom坐标
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.0</version>
</dependency>
2.模板样式
sheet1
sheet2
3.编写代码
public void fillTemplate(HttpServletResponse response) {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
try {
String name = URLEncoder.encode("测试导出功能文档", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + name + ".xlsx");
// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
// {} 代表普通变量 {.} 代表是list的变量
// 这里模板 删除了list以后的数据,也就是统计的这一行
String templateFileName =
"D:\\java\\ruoyi\\ruoyi-admin\\src\\main\\resources\\template\\moban.xlsx";
// 注意:forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行,然后下面的数据往后移动。默认 是false,会直接使用下一行,如果没有则创建。这会把所有数据放到内存,数据量大时会很耗内存
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.FALSE).build();
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
WriteSheet writeSheet2 = EasyExcel.writerSheet(1).build();
Integer count1 = 0;
Integer count2 = 0;
Integer count3 = 0;
Integer count4 = 0;
Integer count5 = 0;
Integer count6 = 0;
Integer count7 = 0;
List<OneFillVo> data = new ArrayList<OneFillVo>();
for (int i = 0; i < 15; i++) {
OneFillVo oneFillVo = new OneFillVo();
oneFillVo.setA(String.valueOf(i));
oneFillVo.setB(String.valueOf(i));
oneFillVo.setC(String.valueOf(i));
oneFillVo.setD(String.valueOf(i));
oneFillVo.setE(String.valueOf(i));
oneFillVo.setF(String.valueOf(i));
oneFillVo.setG(String.valueOf(i));
oneFillVo.setH(String.valueOf(i));
count1+=i;
count2+=i;
count3+=i;
count4+=i;
count5+=i;
count6+=i;
count7+=i;
data.add(oneFillVo);
}
excelWriter.fill(new FillWrapper("data1", data), fillConfig, writeSheet);
HashMap<String, String> map = new HashMap<>();
map.put("date", DateUtils.getDateTimeNow());
//填充日期
excelWriter.fill(map,writeSheet);
List<List<String>> totalListList = ListUtils.newArrayList();
List<String> totalList = ListUtils.newArrayList();
totalListList.add(totalList);
totalList.add("合计");
totalList.add(String.valueOf(count1));
totalList.add(String.valueOf(count2));
totalList.add(String.valueOf(count3));
totalList.add(String.valueOf(count4));
totalList.add(String.valueOf(count5));
totalList.add(String.valueOf(count6));
totalList.add(String.valueOf(count7));
//填充合计
excelWriter.write(totalListList,writeSheet);
ArrayList<TwoFillVo> twoFillVos = new ArrayList<>();
for (int i = 0; i < 10; i++) {
TwoFillVo twoFillVo = new TwoFillVo();
twoFillVo.setAa(String.valueOf(i));
twoFillVo.setBb(String.valueOf(i));
twoFillVo.setCc(String.valueOf(i));
twoFillVo.setDd(String.valueOf(i));
twoFillVo.setEe(String.valueOf(i));
twoFillVo.setFf(String.valueOf(i * 5));
twoFillVos.add(twoFillVo);
}
excelWriter.fill(new FillWrapper("data2", twoFillVos), fillConfig, writeSheet);
excelWriter.fill(new FillWrapper(twoFillVos), fillConfig, writeSheet2);
excelWriter.finish();
} catch (Exception e) {
e.printStackTrace();
}
}
4.预览结果