通过 hutool 工具实现Excel模版下载

导入依赖

       <!-- hutool -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>${hutool.version}</version>
        </dependency>
       <!--******** 处理excel文件使用poi包 start********-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.ooxml.version}</version>
        </dependency>
        <!--******** 处理excel文件使用poi包 end********-->

Controller 层编写

/**
  * 人口信息模版下载
 */
@RequestMapping("/download")
public BaseResponse populationDown(@RequestBody PopulationImport population,HttpServletResponse response) {
     //1、获取你传的参数
     String addressCode = population.getAddressCode();
     String year = population.getYear();
     //2、获取选中区划代码下的所有子集
     List<MdmAddress> mdmAddressList=addressService.listMdmAddressChildren(addressCode);
     //3、下载模版
     BaseResponse responseMessage = ExcelUtils.downLoadFile(mdmAddressList, year,response);
     return responseMessage;
    }

模版下载

/**
 * @author by LMGD
 * @date 2021-09-13 10:31
 * @description
 */
public class ExcelUtils {

    /**
     * Excel模版下载
     *
     * @param response
     */
    public static BaseResponse downLoadFile(List<MdmAddress> mdmAddressList, String year, HttpServletResponse response) {
        String outFileName = "老年人口信息" + System.currentTimeMillis() + ".xlsx";
        String ENCODING = "UTF-8";
        //空判断
        if (mdmAddressList.isEmpty()) {
            return BaseResponse.returnFault("行政区划信息为空!");
        }
        //year为空默认取系统当前年
        if (StringUtils.isEmpty(year)) {
            year = Calendar.getInstance().get(Calendar.YEAR) + "";
        }
        ExcelWriter writer = ExcelUtil.getWriter();
        // 1、通过工具类创建writer并且进行别名
        assembleWriter(writer);
        //2、封装Excel模版下载的数据
        List<ExcelInfo> excelInfoList = getExcelInfoList(mdmAddressList, year);
        //3、准备将对象写入我们的 List
        writer.write(excelInfoList, true);
        try {
            // 获取我们的输出流
            final OutputStream output = response.getOutputStream();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + outFileName + ";filename*=utf-8''"
                    + URLEncoder.encode(outFileName, ENCODING));
            writer.flush(output, true);
            writer.close();
            // 这里可以自行关闭资源或者写一个关闭资源的工具类
            IoUtil.close(output);
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return BaseResponse.returnFault("用户导出失败!");
        }
    }

    private static void assembleWriter(ExcelWriter writer) {
        writer.addHeaderAlias("year", "年度");
        writer.addHeaderAlias("addressCode", "行政区划编码");
        writer.addHeaderAlias("addressName", "行政区划名称");
        writer.addHeaderAlias("populationNum", "老年人口数");
    }

    /**
     * 封装模版下载的数据
     */
    private static List<ExcelInfo> getExcelInfoList(List<MdmAddress> mdmAddressList, String year) {
        List<ExcelInfo> list = new ArrayList<>();
        ExcelInfo info = null;
        for (MdmAddress address : mdmAddressList) {
            info = new ExcelInfo();
            info.setYear(year);
            info.setAddressCode(address.getAddressCode());
            info.setAddressName(address.getAddressName());
            list.add(info);
        }
        return list;
    }
}