java hibernate 数据库 利用EasyExcel实现excel模板导出

1.导入相关依赖

3.1.4

com.alibaba

easyexcel

${easyexcel.version}

poi

org.apache.poi

poi-ooxml

org.apache.poi

poi-ooxml-schemas

org.apache.poi

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 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;

}

相关链接

 您阅读本篇文章共花了：