使用hutool根据模板导出数据

一.根据模板下载excel

依赖

   		<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

Resources

controller层导出模板

        @ApiOperation(value = "下载人员档案模板")
    @GetMapping(value = "/downloadRecordTemplate")
    public void downLoadRecordTemplate(HttpServletResponse response) {

        log.info("人员档案模板下载--------");
        ServletOutputStream out =null;
        try {
            String fileName = URLEncoder.encode("template.xls", "utf-8");
            InputStream fis = ExcelUtil.getResourcesFileInputStream("template/template.xls");
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            response.setHeader("Content-disposition", "attachment;fileName=" + fileName);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            out = response.getOutputStream();
            out.write(buffer);
        } catch (Exception ex) {
            log.error("文件下载失败 :", ex);
        }finally {
            try {
                assert out != null;
                out.flush();
                out.close();
            }catch (Exception e){
                e.printStackTrace();
            }

        }
    }

public class ExcelUtil {
    public static InputStream getResourcesFileInputStream(String fileName) {
        return Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName);
    }

}

从service层导出数据以及无数据时空数据模板
service

    @Override
    public void exportRecordPoint(String idCard, HttpServletResponse response) {
        List<DataPointAddrVo> dataPointAddrVos = getDataRecordPoint(idCard);
        if (dataPointAddrVos.size() < GeneralTypes.ONE) {
            String name = "record.xls";
            String filePath = "/template/record.xls";
            this.dataNullTemplate(response, name, filePath);
        } else {
            ExcelWriter writer = ExcelUtil.getWriter();
            writer.addHeaderAlias("createdTime", "时间");
            writer.addHeaderAlias("pointDate", "时间段");
            writer.addHeaderAlias("siteName", "名称");
            writer.addHeaderAlias("siteAddress", "地址");
            writer.addHeaderAlias("countDate", "时长");
            writer.merge(4, "标题");
            writer.write(dataPointAddrVos, true);
            String fileName = "recordPoint";
            this.servletResponse(writer, fileName, response);
        }
    }


        private void servletResponse(ExcelWriter writer, String fileName, HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            writer.close();
        }
        IoUtil.close(out);
    }

  private void dataNullTemplate(HttpServletResponse response, String name, String filePath) {
        log.info("-----空数据模板下载-----");
        ServletOutputStream out = null;
        try {
            String fileName = URLEncoder.encode(name, "utf-8");
            InputStream fis = getResourcesFileInputStream(filePath);
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            response.setHeader("Content-disposition", "attachment;fileName=" + fileName);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            out = response.getOutputStream();
            out.write(buffer);
        } catch (Exception ex) {
            log.error("文件导出失败 :", ex);
        } finally {
            try {
                assert out != null;
                out.flush();
                out.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }
        private InputStream getResourcesFileInputStream(String filePath) {
        return this.getClass().getResourceAsStream(filePath);
    }

有数据则按照Writer构建excel,无数据则按照空模板导出