easypoi 的模板导出
一.确定想实现的导出样式
二.添加依赖
因为原项目中有poi 引入,这里需要排除掉
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.2</version>
<exclusions>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
<exclusion>
<groupId>org.slf4j</groupId>
<artifactId>*</artifactId>
</exclusion>
<exclusion>
<groupId>slf4j-log4j12</groupId>
<artifactId>*</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
</exclusion>
</exclusions>
</dependency>
三.制作excel 模板
1.代码中集合数据放于maplist中,模板中map的名字也需对应为maplist,注意集合开始处的写法:
{{$fe: maplist t.id
2.集合结束的写法:
t.startStation}}
3.不在集合中的数据赋值写法:
{{title}}
4.最后一个**{{remark233}}**特意赋了一个空字符串的值,目的是为了占位.
如果没有此行,集合数据与其他行数据会多处一行空白格.
将制作后的模板命名为port.xlsx,上传到服务器的某个位置,这样可以直接用url获取
四.代码实现
@Value("${easypoi.template}")
private String templat
//下载输出流
private void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
@Override
public void download(Long id,HttpServletResponse response) throws IOException {
PortPlanInfo planInfo =downInfo(id);
List<PortBoxInfoDTO> list=planInfo.getPortBoxInfoDtoList();
TemplateExportParams templateExportParams = new TemplateExportParams(template);
Map<String,Object> map = new HashMap<>();
map.put("remark",(StringUtils.isBlank(planInfo.getRemark())? "":planInfo.getRemark()));
map.put("connectPerson",planInfo.getConnectPerson()+"");
map.put("connectPhone",planInfo.getConnectPhone()+"");
map.put("title","集装箱铁路进港计划明细表");
map.put("remark233","");
List<Map<String,String>> mapList = new ArrayList<>();
int i=0;
if(list.size()>0){
for (PortBoxInfoDTO portBoxInfoDTO : list) {
Map<String,String> ml = new HashMap<>();
ml.put("id", i + 1 + "");
ml.put("carNum",portBoxInfoDTO.getCarNum()+"");
ml.put("boxNum",portBoxInfoDTO.getBoxNum()+"");
ml.put("boxGenus",portBoxInfoDTO.getBoxGenus()+"");
ml.put("boxType",portBoxInfoDTO.getBoxType()+"");
ml.put("goodsName",portBoxInfoDTO.getGoodsName()+"");
ml.put("shipNameAndVoyageNum",portBoxInfoDTO.getShipNameAndVoyageNum()+"");
ml.put("waybillNo",portBoxInfoDTO.getWaybillNo()+"");
ml.put("receiverName",portBoxInfoDTO.getReceiverName()+"");
ml.put("firstDischarge",portBoxInfoDTO.getFirstDischarge()+"");
ml.put("startStation",portBoxInfoDTO.getStartStation()+"");
mapList.add(ml);
i++;
}
}
map.put("maplist",mapList);
String fileName = "detail";
//正式环境
Workbook workbook = ExcelExportUtil.exportExcel(templateExportParams,map);
downLoadExcel(fileName, response, workbook);
//测试环境(导出到本地)
// File saveFile = new File("D:/test/");
// if (!saveFile.exists()){
// saveFile.mkdir();
// }
// FileOutputStream fileOutputStream = null;
// try {
// fileOutputStream = new FileOutputStream("D:/test/本地文件.xlsx");
// workbook.write(fileOutputStream);
// } catch (Exception e) {
// e.printStackTrace();
// }finally {
// try {
// fileOutputStream.close();
// } catch (IOException e) {
// e.printStackTrace();
// }
// }
}
在bootstrap.yml中配置模板的url
easypoi:
template: 'http://www.xxx.cn/statics/template/port.xlsx'
//下载输出流
private void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}