Java借助hutool插件导出Excel

先定义一些变量

private static final String FILE_NAME = "流水";
private static final int DEFAULT_COLUMN_SIZE = 25;
private static final int DEFAULT_ROW_HEIGHT = 22;
private static final String EXCEL_FILE_TYPE = ".xlsx";

Controller层

@ApiOperation(value = "浦发流水导出", notes = "浦发流水导出")
@PostMapping("/export")
public void export(@RequestBody PfSerialReqDto pfSerialReqDto, HttpServletResponse httpServletResponse) {
    pfCapitalSerialService.exportPfSerial(pfSerialReqDto,httpServletResponse);
}

Service层

 @Override
    public void exportPfSerial(PfSerialReqDto pfSerialReqDto, HttpServletResponse httpServletResponse) {
        ExcelWriter excelWriter = null;
        ServletOutputStream servletOutputStream = null;
        String now = DateUtils.getYYMMDDHHmmssSSS();
        try {
             excelWriter = handleFile(excelWriter);
            //reqDto转Bo
            PfSerialReqBo pfSerialReqBo = ConvertPfSerivalService.INSTANCE.convertPfSerialReqDto2Bo(pfSerialReqDto);
            //分页查询浦发资金流水余额信息
            while (true) {
                PageHelper.startPage(PageUtil.initPageNo(PAGE_NUM),
                        PageUtil.initPageSize(Constants.DEFAULT_PAGE_SIZE));
                List<PfSerialRespBo> pfSerialRespBoList = slPfCapSerialMapper.queryPfSerialList(pfSerialReqBo,
                        DateUtils.getFormate(DateUtils.getDayBegin(pfSerialReqBo.getQueryStartDt(),DateUtils.FORMAT_YYYYMMDDHHMMSS,0L)),
                        DateUtils.getFormate(DateUtils.getDayEnd(pfSerialReqBo.getQueryEndDt(),DateUtils.FORMAT_YYYYMMDDHHMMSS,0L)));
                if (CollectionUtils.isEmpty(pfSerialRespBoList)) {
                    break;
                }
                //补充流水类型信息,转换流水类型
                for (PfSerialRespBo pfSerialRespBo : pfSerialRespBoList) {
                    pfSerialRespBo.setSerialType(SerialTypeEnum.getEnum(pfSerialRespBo.getSerialType()).getSerialTypeName());
                    pfSerialRespBo.setSerialType(SerialTypeEnum.getEnum(pfSerialRespBo.getSerialType()).getSerialTypeName());
                }
                if(DEFAULT_FIRST_PAGE_NUM==PAGE_NUM){
                    // 一次性写出内容，使用默认样式，强制输出标题
                    excelWriter.write(pfSerialRespBoList, true);
                    PAGE_NUM++;
                }else {
                    // 一次性写出内容，使用默认样式，强制输出标题
                    excelWriter.write(pfSerialRespBoList, false);
                    PAGE_NUM++;
                }
            }
            //HttpServletResponse对象
            httpServletResponse.setHeader(HttpHeaders.ACCESS_CONTROL_EXPOSE_HEADERS, HttpHeaders.CONTENT_DISPOSITION);
            //response为HttpServletResponse对象
            httpServletResponse.setCharacterEncoding(Constants.CHARSET_UTF8);
            httpServletResponse.setContentType("application/vnd.ms-excel;charset=utf-8");
            //file.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            httpServletResponse.setHeader("Content-Disposition",
                    "attachment;filename=" +
                            URLEncoder.encode(FILE_NAME + now + EXCEL_FILE_TYPE, Constants.CHARSET_UTF8));
            servletOutputStream = httpServletResponse.getOutputStream();
            excelWriter.flush(servletOutputStream, true);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            if (excelWriter != null) {
                // 关闭writer，释放内存
                excelWriter.close();
            }
            if (servletOutputStream != null) {
                //此处记得关闭输出Servlet流
                IoUtil.close(servletOutputStream);
            }
        }
    }

    private ExcelWriter handleFile(ExcelWriter writer) {
        writer = ExcelUtil.getWriter(true);
        StyleSet styleSet = writer.getStyleSet();
        styleSet.setBorder(BorderStyle.NONE, IndexedColors.BLACK);
        // 重命名第一个Sheet的名称，不然会默认多出一个Sheet1的页
        writer.renameSheet(0, FILE_NAME);
        //获取实体类的所有属性，返回Field数组
        Field[] fields = PfSerialRespBo.class.getDeclaredFields();
        int fieldLength = fields.length;
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(fieldLength - 1, FILE_NAME);

        writer = getHeaderMap(writer);
        // 设置默认行高
        writer.setDefaultRowHeight(DEFAULT_ROW_HEIGHT);
        //设置列宽
        for (int i = 0; i < fieldLength; i++) {
            writer.setColumnWidth(i, DEFAULT_COLUMN_SIZE);
        }
        return writer;
    }

    //字段与列名对应关系
    private ExcelWriter getHeaderMap(ExcelWriter writer) {
        writer.addHeaderAlias("pfSno", "流水号");
        writer.addHeaderAlias("serialType", "流水类型");
        writer.addHeaderAlias("accntCode", "监管账号");
        writer.addHeaderAlias("accntName", "监管账号名称");
        writer.addHeaderAlias("happenDt", "入账日期");
        writer.addHeaderAlias("happenTm", "入账时间");
        writer.addHeaderAlias("tradeAmt", "发生额");
        writer.addHeaderAlias("balance", "当前余额");
        writer.addHeaderAlias("oppAccnt", "对方账号");
        writer.addHeaderAlias("oppName", "对方户名");
        writer.addHeaderAlias("summary", "摘要");
        writer.addHeaderAlias("note", "附言");
        writer.addHeaderAlias("usage", "用途");
        return writer;
    }