java 根据excel模板导出excel
由于项目需求,最近做了一个需要根据查询接口导出excel表格数据的需求
pom依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>1.0.5</version>
</dependency>
excel模板如下:
模板存放的位置为resource目录下,创建了一个template目录,存放
后台接口:
public void export(@RequestHeader("Authorization") String token, @RequestParam("equipId") Long equipId,
@RequestParam("year") Integer year, HttpServletRequest request, HttpServletResponse response) throws Exception {
//根据查询条件获取查询数据
List<EquipmentStatisticsNormRespVo> equipStatisticsList = equipmentStatisticsNormService.getEquipStatisticsList(equipId, year);
// 循环数据
List<Object> list = new ArrayList<>();
equipStatisticsList.forEach(equipmentStatisticsNormRespVo -> {
Map<String, Object> data = new HashMap<>();
//数据处理
data.put("yearly", equipmentStatisticsNormRespVo.getYearly() + "年");
list.add(data);
});
// 表格使用的数据
Map map = new HashMap();
map.put("data", list);
map.put("title", year + "年" + "导出数据");
Date date = new Date();
SimpleDateFormat simpl = new SimpleDateFormat("yyyyMMddHHmmss");
String currntTime = simpl.format(date);
//导出列表名
String fileName = "导出数据" + "_" + currntTime;
//生成的导出文件
File destFile = File.createTempFile(fileName, ".xlsx");
//transformer转到Excel
XLSTransformer transformer = new XLSTransformer();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
InputStream resourceAsStream = this.getClass().getResourceAsStream("/template/equipmentStaticsNormTemplate.xlsx");
XLSTransformer xlsTransformer = new XLSTransformer();
Workbook workbook = xlsTransformer.transformXLS(resourceAsStream, map);
OutputStream os = new BufferedOutputStream(new FileOutputStream(destFile));
workbook.write(os);
resourceAsStream.close();
os.flush();
os.close();
//将文件输入
InputStream inputStream = new FileInputStream(destFile);
// 设置response参数,可以打开下载页面
response.reset();
//设置响应文本格式
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
//将文件输出到页面
ServletOutputStream out = response.getOutputStream();
bis = new BufferedInputStream(inputStream);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// 根据读取并写入
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (ParsePropertyException e) {
logger.debug("导出失败:{}", e.getMessage());
} catch (InvalidFormatException e) {
logger.debug("导出失败:{}", e.getMessage());
} finally {
//使用完成后关闭流
try {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
} catch (IOException e) {
logger.debug("导出失败:{}",e.getMessage());
}
}
}
导出结果:
踩过得坑:
刚刚开始的时候,采用的获取模板的方式如下:
URL resource = this.getClass().getClassLoader().getResource("template/equipmentStaticsNormTemplate.xlsx");
String path = resource.getPath();
调用的方法如下:
public void transformXLS(String srcFilePath, Map beanParams, String destFilePath) throws ParsePropertyException, IOException, InvalidFormatException {
InputStream is = new BufferedInputStream(new FileInputStream(srcFilePath));
org.apache.poi.ss.usermodel.Workbook workbook = transformXLS(is, beanParams);
OutputStream os = new BufferedOutputStream(new FileOutputStream(destFilePath));
workbook.write(os);
is.close();
os.flush();
os.close();
}
由于项目使用的前后端分离的,后端服务直接在 maven
构建后打包成 jar
包的方式部署发版,如上调用方法获取模板路径时,获取到的路径会变成file:/e:/.../ResourceJar.jar!/resource/template.xlsx
,注意此时的路径变成了 xxx.jar!
这样,导致后台服务在调用方法transformXLS
时,会出现文件路径错误找不到指定文件
的错误。