java后台实现Excel的导出：easypoi实现

作为一个稀有的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);
			}
		}

	}