springboot + easyexecl 实现execl数据导出功能

springboot + easyexecl 实现execl数据导出功能

近期在做execl数据导出个功能,原有的数据导出都是用poi来操作,18年阿里巴巴开源了 easyexecl,这里在springboot中进行了简单应用.GitHub链接 为 https://github.com/alibaba/easyexcel .
具体 easyexecl 的优点,请查看github内容介绍.

1. 先介绍一下springboot 的文件导出流程.

使用poi导出流程为(easyexecl导出类似,可参考使用).

1. 创建Excel文档

HSSFWorkbook workbook = new HSSFWorkbook();

2. 设置文档的基本信息，这一步是可选的

//获取文档信息，并配置
DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
//文档类别
dsi.setCategory("员工信息");
//设置文档管理员
dsi.setManager("江南一点雨");
//设置组织机构
dsi.setCompany("XXX集团");
//获取摘要信息并配置
SummaryInformation si = workbook.getSummaryInformation();
//设置文档主题
si.setSubject("员工信息表");
//设置文档标题
si.setTitle("员工信息");
//设置文档作者
si.setAuthor("XXX集团");
//设置文档备注
si.setComments("备注信息暂无");

3. 创建一个Excel表单,参数为sheet的名字

HSSFSheet sheet = workbook.createSheet("XXX集团员工信息表");

4. 创建一行

HSSFRow headerRow = sheet.createRow(0);
0表示第一行。

5 .在第一行中创建第一个单元格，并设置数据

HSSFCell cell0 = headerRow.createCell(0);
cell0.setCellValue("编号");

5. 将Excel写到ByteArrayOutputStream中

baos = new ByteArrayOutputStream();
workbook.write(baos);

6. 创建ResponseEntity并返回

return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);

2. springboot 控制层代码

代码示例:

 @GetMapping("/acctSerialsDownload")
public ResponseEntity<byte[]> acctSerialsDownload(AcctSerialBean dto) {
  
        ResponseEntity<byte[]> result;
        //定义返回报文头
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        //获取execl字节流
        byte[] export = ExeclDownloadHelper.exportExcel(arrayListResultModel.getData());
        String fileName = "ACCT_SERIALS_" + DateUtil.getSysTime() + ".xlsx";
        try {
            headers.setContentDispositionFormData("attachment", fileName);
            result = new ResponseEntity<>(export, headers, HttpStatus.OK);
        } catch (Exception e) {
            LogUtil.error("下载异常", e);
            return new ResponseEntity<>(null, headers, HttpStatus.INTERNAL_SERVER_ERROR);
        }
        return result;
    }

AcctSerialBean 为请求参数

@Data
public class AcctSerialBean extends BasePageRequest implements Serializable {

    /**
     * 账户类型
     */
    public String acctType;
    /**
     * 账户业务类型
     */
    public String busiType;
    /**
     * 出入账标识 0 入账 1出账
     */
    public String sendOrReceiveMoney;

    @Override
    public String validateLogic() {
        return null;
    }

3. 页面访问

页面访问为get请求方式
举例:

http://localhost:8080/xxx/acctSerialsDownload?endTime=20190222&limit=10&offset=0&page=1&startTime=20190221&acctType=1

easyexecl 生成execl部分

pom坐标为:

 <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.1</version>
            <scope>compile</scope>
        </dependency>

注意事项:easyexecl使用了poi,会自动引入poi的jar包进来,以下代码基于easyexcel 的1.1.1,后续版本代码有可能会调整而不适用.

文件导出工具类(工具类为简单使用,请酌情参考)

public class ExeclDownloadHelper {

    public static void exportExcel(HttpServletResponse response, ArrayList<AcctSerialDownloadVO> list) {
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
            String fileName = new String(("acctserail " + new SimpleDateFormat("yyyy-MM-dd").format(new Date())).getBytes(), "UTF-8");
            Sheet sheet1 = new Sheet(1, 0, AcctSerialDownloadVO.class);
            sheet1.setSheetName("sheet");
            writer.write(list, sheet1);
            writer.finish();
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            out.flush();
        } catch (Exception e) {
            LogUtil.error("下载文件异常,异常原因为:", e);
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static byte[] exportExcel(ArrayList<AcctSerialDownloadVO> list) {
        ByteArrayOutputStream out = null;
        try {
            out = new ByteArrayOutputStream();
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
            Sheet sheet1 = new Sheet(1, 0, AcctSerialDownloadVO.class);
            sheet1.setSheetName("资金明细");
            writer.write(list, sheet1);
            writer.finish();
            byte[] bytes = out.toByteArray();
            return bytes;
        } catch (Exception e) {
            LogUtil.error("生成文件,异常原因为:", e);
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return new byte[0];
    }
}

其中特殊说明,AcctSerialDownloadVO 需要继承 BaseRowModel .

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value = "账户流水导出参数")
public class AcctSerialDownloadVO extends BaseRowModel implements Serializable {
    /**
     * 资金流水号
     */
    @ExcelProperty(value = "外部订单号", index = 0)
    private String no_order;
    /**
     * 账务日期
     */
    @ExcelProperty(value = "账务日期", index = 0)
    private String date_acct;
    /**
     * 支付单号
     */
    private String oid_billno;
    /**
     * 流水创建时间
     */
    @ExcelProperty(value = "创建时间", index = 0)
    @ApiModelProperty(value = "创建时间")
    private String dt_sys;
    /**
     * 交易后余额(元)
     */
    @ExcelProperty(value = "交易后余额(元)", index = 0)
    @ApiModelProperty(value = "交易后余额(元)")
    private String amt_bal;
    /**
     * 备注
     */
    @ExcelProperty(value = "备注", index = 0)
    @ApiModelProperty(value = "备注")
    private String memo;
    /**
     * 入账金额(元)
     */
    @ExcelProperty(value = "入账金额(元)", index = 0)
    @ApiModelProperty(value = "入账金额(元)")
    private String amt_inoccur;
    /**
     * 出账金额(元)
     */
    @ExcelProperty(value = "出账金额(元)", index = 0)
    @ApiModelProperty(value = "出账金额(元)")
    private String amt_outoccur;

    /**
     * 出入账类型 0入账,1出账 转义为中文
     */
    @ExcelProperty(value = "出入账类型", index = 0)
    @ApiModelProperty(value = "出入账类型 0入账,1出账")
    private String flag_dc;
    /**
     * 业务类型 转义为中文
     */
    @ExcelProperty(value = "业务类型", index = 0)
    private String busiType;
}

生成的文件样例

参考文档

• https://segmentfault.com/a/1190000013036679
• https://github.com/alibaba/easyexcel