目录

使用easypoi

后端代码

前端代码

总结

使用easypoi

导入mvn依赖

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</version>
        </dependency>

后端代码

导出实体类 使用@Excel注解给需要导出的字段进行注解

@Data
public class OrderStatsticsResultVo implements Serializable {

    @Excel(name = "商品id")
    private Integer productId;

    // 商品编码
    @Excel(name = "商品编码")
    private String barCode;

    // 商品名称
    @Excel(name = "商品名称")
    private String productName;

    // 分类

    private String cat;

    // 销售数量
    @Excel(name = "销售数量")
    private Long saleCount;

    // 呐喊价格
    @Excel(name = "呐喊价格", height = 20, width = 30, isImportField = "true_st")
    private BigDecimal price;

    // 均价
    @Excel(name = "均价", height = 20, width = 30, isImportField = "true_st")
    private BigDecimal averagePrice;

    public OrderStatsticsResultVo(Integer productId,String barCode, String productName, String cat, Long saleCount,  BigDecimal price, BigDecimal averagePrice) {
        this.productId = productId;
        this.barCode = barCode;
        this.productName = productName;
        this.cat = cat;
        this.saleCount = saleCount;
        this.price = price;
        this.averagePrice = averagePrice;
    }

}

controoler层代码

 @Log("销售统计导出")
    @ApiOperation(value = "销售统计导出")
    @GetMapping(value = "/sale/statistics/export")
    @PreAuthorize("hasAnyRole('admin','YXSTOREORDER_ALL','YXSTOREORDER_SELECT')")
    public ResponseEntity statisticsExport(OrderStatisticsSearchVo vo, HttpServletResponse response){
        vo.setPageSize(99999);
        Page<OrderStatsticsResultVo> result = yxStoreOrderProductLogService.statistics(vo);
        DateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
        String name = "销售统计（"+ft.format(vo.getFrom()) + "~" + ft.format(vo.getTo()) + ").xls";

        // 需要把分页结果中的content放到另外一个list里面，不然会异常，异常信息是：导出excel失败
        List<OrderStatsticsResultVo> datas = new ArrayList<>();
        result.getContent().forEach(i->datas.add(i));

        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(name,"销售统计"),
                OrderStatsticsResultVo .class, datas);
        try {
            response.reset();
            ServletOutputStream out = response.getOutputStream();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(name, "UTF-8"));
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            throw runtimeException;
        }
        return null;
    }

前端代码

axion请求方法

export function statisticsExport(data) {
  return request({
    url: 'api/sale/statistics/export',
    method: 'get',
    params:data,
    responseType: 'arraybuffer' // 这个是关键 返回的是二进制
  })
}

调用axios方法，返回数据的处理

statisticsExport({
        from: this.formatter(this.value1[0], "yyyy-MM-dd"),
        to: this.formatter(this.value1[1], "yyyy-MM-dd"),
        page: this.page,
        pageSize: this.pageSize
      }).then(res=>{
        const url = window.URL.createObjectURL(new Blob([res],{type:"application/vnd.ms-excel;charset=utf-8"}));
        const link = document.createElement('a');
        link.href = url;
        let fileName = "销售统计.xls";
        link.setAttribute('download', fileName);
        document.body.appendChild(link);
        link.click();
      })
    }

总结

遇到2个坑

1、JpaRepository 的分页结果不能直接用于eastpoi的参数

需要这样处理一下，不然导出excel异常，异常消息：导出excel失败，而且错误信息看不懂哪里错了

List<OrderStatsticsResultVo> datas = new ArrayList<>();
result.getContent().forEach(i->datas.add(i));
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(name,"销售统计"),
        OrderStatsticsResultVo .class, datas);

2、前端需要把接受到的二进制数据转化为文件

参考地址：https://blog.csdn.net/o1993o/article/details/83863414