java基于模板导出Excel

1.添加easyexcel依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.4</version>
</dependency>

2.编写工具类

这边的StoreInfo是我的需求中用到的业务类，改成自己的即可。

public static void exportExcelByTemplate(String filePath, String templatePath, List<StoreInfo> list){
    ExcelWriter excelWriter = EasyExcel.write(filePath).withTemplate(templatePath).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
    if (list.size() > 0){
        Map<String, Object> map = new HashMap<>(64);
        String rkDate = list.get(0).getInTime().toString();
        String rkDateFormat = rkDate.substring(0, 4) + "年" + rkDate.substring(5, 7) + "月" + rkDate.substring(8, 10) + "日";
        map.put("rkDate", rkDateFormat);
        excelWriter.fill(map, writeSheet);
        excelWriter.fill(new FillWrapper("data", list), fillConfig, writeSheet);
    }
    excelWriter.finish();
}

3.编写接口代码

    @RequestMapping(value="/exportStoreList")
    @ResponseBody
    public Object exportStoreSignList(HttpServletResponse response) throws Exception{
        //随机生成文件名称
        SimpleDateFormat simpleDate = new SimpleDateFormat("yyyyMMdd");
        Date date = new Date();
        String str = simpleDate.format(date);
        Random rand = new Random();
        int rannum = (int) (rand.nextDouble()*(99999-10000+1)+10000);
        String fileName =str+rannum+".xlsx";
        //服务器
//        String localFilePath = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest().getSession().getServletContext()
//                .getRealPath("//downloadFiles");
        //本地
      String localFilePath = "d:\\";
      //获取模板路径
        String templatePath = this.getClass().getClassLoader().getResource("templates/store_template.xlsx").getPath();
        List<StoreInfo> storeList = new ArrayList<>();
        StoreInfo storeInfo = new StoreInfo();
        storeInfo.setCardNum("321321199905071209");
        storeInfo.setCardType("身份证");
        storeInfo.setInReason("测试");
        storeInfo.setInTime(date);
        storeInfo.setOrgName("百度");
        storeInfo.setUserName("小王");
        storeList.add(storeInfo);
        EasyExportUtil.exportExcelByTemplate(localFilePath+fileName, templatePath, storeList);
        response.setCharacterEncoding("utf-8");
        //返回的数据类型
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //响应头
        response.setHeader("Content-Disposition", "attachment;fileName="
                + fileName);
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(localFilePath+"//"+fileName);
            byte[] data = new byte[fis.available()];
            fis.read(data);
            response.getOutputStream().write(data);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            //关闭输入输出流
            if(fis!=null) {
                fis.close();
            }
        }
        return null;
    }

4.模板文件

模板文件放进resource下面

 

 5.如果有不清晰的可以到我的博客下面下载源码参考