目录
使用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