1.导入相关依赖
<properties> <easyexcel.version>3.1.4</easyexcel.version> </properties> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>${easyexcel.version}</version> <exclusions> <exclusion> <artifactId>poi</artifactId> <groupId>org.apache.poi</groupId> </exclusion> <exclusion> <artifactId>poi-ooxml</artifactId> <groupId>org.apache.poi</groupId> </exclusion> <exclusion> <artifactId>poi-ooxml-schemas</artifactId> <groupId>org.apache.poi</groupId> </exclusion> </exclusions> </dependency>
2.编辑模板
1.原始模板:
2.编辑模板:
3.业务代码
serviceImpl实现类:
public void exportExcelSettlement(String settSerialNo, HttpServletResponse response)throws IOException {
//获取模板路径
InputStream template = new PathMatchingResourcePatternResolver().getResource("templates/excel/settlement.xlsx").getInputStream();
if (template.available() == 0) {
log.error("【导出对xx】获取xx模板失败");
throw EXPORT_TEMPLATES_EXCEPTION_SETT;
}
//获取数据
SettlementDetailVO vo = querySettlementDetail(settSerialNo);
List<SettlementDetailGoodsVO> goodsVOS = querySettlementDetailGoods(settSerialNo);
//判断数据是否存在
if (ObjectUtils.isEmpty(vo)) {
log.error("【导出xx】xx信息不存在");
throw SETT_NOT_EXIST_EXCEPTION;
}
if (CollectionUtils.isEmpty(goodsVOS)) {
log.error("【导出xx】xx信息不存在");
throw SETT_NOT_EXIST_EXCEPTION;
}
response.setContentType("application/json;charset=utf-8");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(vo.getBuyersName() + "-" + "对账单" + ".xlsx", "UTF-8"));
//ExcelWriter该对象用于通过POI将值写入Excel
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(template).build();
//构建excel的sheet
WriteSheet writeSheet = EasyExcel.writerSheet().build();
//控制集合数据垂直填充;ps.集合中的数据需要以FillWrapper()包裹起来才能被解析
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).build();
//表格goods的数据填充
excelWriter.fill(new FillWrapper("goods", goodsVOS), fillConfig, writeSheet);
//其他数据填充
excelWriter.fill(convertObject(vo), writeSheet);
excelWriter.finish();
}
//其余数据的转换
private SettlementDetailVO convertObject(SettlementDetailVO info) {
SettlementDetailVO vo = new SettlementDetailVO();
vo.setSettAmount(info.getSettAmount());
vo.setSettName(info.getSettName());
return vo;
}