作为一个稀有的Java妹子,所写的所有博客都只是当作自己的笔记,留下证据自己之前是有用心学习的~哈哈哈哈(如果有不对的地方,也请大家指出,不要悄悄咪咪的不告诉我)
1.概述
在企业的后台系统中,报表导出是经常会有的一个功能,使用easypoi可以非常方便的实现Excel的导出。
2.实现步骤
2.1 引入依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
2.2 导出报表工具类
public class ExcelExportUtil {
//大数据量时,使用追加的方式将数据填充到Excel中
public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass,
Collection<?> dataSet) {
ExcelBatchExportServer batachServer = ExcelBatchExportServer
.getExcelBatchExportServer(entity, pojoClass);
return batachServer.appendData(dataSet);
}
public static void closeExportBigExcel() {
ExcelBatchExportServer batachServer = ExcelBatchExportServer.getExcelBatchExportServer(null,
null);
batachServer.closeExportBigExcel();
}
}
2.3 表格实体类
@ExcelTarget:表明该类是表格实体类
@Excel:该列的列头的值
@Data
@ExcelTarget("orderExcelModel")
public class OrderExcelModel implements Serializable {
private static final long serialVersionUID =1L;
@Excel(name = "订单号")
private String xmbzNo;
@Excel(name = "团购码")
private String platformOrderId;
@Excel(name = "渠道")
private String channelName;
@Excel(name = "订单状态")
private String status;
@Excel(name = "送货地址")
private String receiverAddress;
@Excel(name = "送货日期")
private String deliveryDate;
@Excel(name = "送货开始时间")
private String deliveryStartTime;
@Excel(name = "送货结束时间")
private String deliveryEndTime;
@Excel(name = "商品Sku")
private String skuId;
@Excel(name = "蛋糕名称")
private String goodsName;
@Excel(name = "规格")
private String goodsSpecs;
@Excel(name = "数量")
private String num;
@Excel(name = "收货人手机号")
private String receiverPhone;
@Excel(name = "配送员")
private String delivery;
@Excel(name = "财务需求")
private String merchantName;
@Excel(name = "配送需求")
private String deliveryRemark;
@Excel(name = "生产需求")
private String generateRemark;
@Excel(name = "客户备注")
private String consumerRemark;
@Excel(name = "巧克力牌内容")
private String blessings;
@Excel(name = "订单总价")
private BigDecimal originalPrice;
@Excel(name = "付款金额")
private BigDecimal totalPrice;
@Excel(name = "配送费")
private BigDecimal postPrice;
@Excel(name = "优惠金额")
private BigDecimal discountPrice;
@Excel(name = "总仓")
private String mainWarehouse;
@Excel(name = "分仓")
private String branchWarehouse;
@Excel(name = "下单时间")
private String orderTime;
@Excel(name = "签收时间")
private String receivedTime;
@Excel(name = "操作人")
private String operator;
@Excel(name = "城市")
private String deptName;
}
2.4 查询数据分页导出到excel
public void downLoadOrderExcel(StatisticsQueryRequest statisticsQueryRequest, HttpServletResponse response) {
dealTime(statisticsQueryRequest);
Workbook workbook = null;
ExportParams params = new ExportParams("订单统计报表", "订单统计报表");
//获取数据
R orderInfoCount = iOrderClient.getOrderInfoCount(statisticsQueryRequest);
Integer flag = 0;
if (orderInfoCount != null && orderInfoCount.getCode() == HttpStatus.OK.value()) {
//设置分页参数
Integer size = (Integer) orderInfoCount.getData();
BigDecimal divide = new BigDecimal(size).divide(new BigDecimal(excelPageSize), 0, BigDecimal.ROUND_UP);
flag = divide.intValue();
for (int i = 0; i < flag; i++) {
statisticsQueryRequest.setPage(i);
statisticsQueryRequest.setSize(Integer.parseInt(excelPageSize));
List<OrderExcelModel> execlDataOrder = getExeclDataOrder(statisticsQueryRequest);
//分页导出数据
workbook = ExcelExportUtil.exportBigExcel(params, OrderExcelModel.class, execlDataOrder);
}
}else {
log.error("es服务中断!");
}
if (workbook != null) {
ExcelExportUtil.closeExportBigExcel();
}
dealResponse(response, workbook);
}
//返回报表,直接下载
void dealResponse(HttpServletResponse response, Workbook workbook) {
if (workbook != null) {
try {
response.setHeader("Content-disposition", "attachment; filename=" + (new SimpleDateFormat("yyyy-MM-dd")).format(new Date()) + "_" + UUID.randomUUID() + ".xls");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
ServletOutputStream exportXls = response.getOutputStream();
workbook.write(exportXls);
exportXls.close();
} catch (Exception e) {
e.printStackTrace();
log.info("导出报表出错," + e);
}
}
}