EasyPoi如何使用注解导出，并且添加自增序号？

EasyPoi如何使用注解导出，添加自增序号？

1.先看一下导出的实体和接口写法

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.github.wxiaoqi.security.common.dto.BaseDTO;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

/**
 * <p>
 * 销售订单跟踪
 * </p>
 *
 */
@Data
@ApiModel
public class SalesOrderTrackDTO extends BaseDTO {

    private static final long serialVersionUID = 1L;

    @Excel(name = "单据编号",width = 20)
    @ApiModelProperty("单据编号")
    private String billNo;

//    @Excel(name = "客户id")
    @ApiModelProperty("客户id")
    private String customerId;

    @Excel(name = "客户编号")
    @ApiModelProperty("客户编号")
    private String customerCode;

    @Excel(name = "客户名称",width = 30)
    @ApiModelProperty("客户名称")
    private String customerName;

    @ApiModelProperty("订单类型(1商城订单、2普通订单、3赊欠订单、4项目订单、5返利订单)")
    private String billType;

    @Excel(name = "销售员编号")
    @ApiModelProperty("销售员编号")
    private String salerCode;

    @Excel(name = "销售员")
    @ApiModelProperty("销售员")
    private String salerName;

    @Excel(name = "销售员部门",width = 30)
    @ApiModelProperty("销售员部门")
    private String departName;

    @Excel(name = "订单数量")
    @ApiModelProperty("订单数量")
    private Integer allBillQuantity;

    @Excel(name = "已锁定数量")
    @ApiModelProperty("已锁定数量")
    private Integer allLockedQty;

    @Excel(name = "订单金额",width = 12)
    @ApiModelProperty("订单金额")
    private BigDecimal allBillAmount;

    @Excel(name = "采购申请数量",width = 12)
    @ApiModelProperty("采购申请数量")
    private Integer allApplyQty;

//    @Excel(name = "在途数量")
    @ApiModelProperty("在途数量")
    private Integer allOrderQty;

    @Excel(name = "出库数量")
    @ApiModelProperty("出库数量")
    private Integer allStockOutQuantity;

    @Excel(name = "出库金额",width = 12)
    @ApiModelProperty("出库金额")
    private BigDecimal allStockOutAmount;

//    @Excel(name = "出库状态")
    @ApiModelProperty("出库状态")
    private String storageStatus;

    @Excel(name = "退货数量")
    @ApiModelProperty("退货数量")
    private Integer allStockReturnQty;

    @Excel(name = "退货金额",width = 12)
    @ApiModelProperty("退货金额")
    private BigDecimal allStockReturnAmount;

    @Excel(name = "收款金额",width = 12)
    @ApiModelProperty("收款金额=实际出库数量*出库单价")
    private BigDecimal shouldReceiveAmount;

    @Excel(name = "已开票数量")
    @ApiModelProperty("已开票数量")
    private Integer shouldPurchaseInvoiceQty;

    @Excel(name = "已开票金额",width = 12)
    @ApiModelProperty("已开票金额")
    private BigDecimal invoiceBillAmount;

//    @Excel(name = "已收款金额")
    @ApiModelProperty("已收款金额")
    private BigDecimal receivableAmount;

//    @Excel(name = "应收余额")
    @ApiModelProperty("应收余额")
    private BigDecimal notReceivableAmount;

//    @Excel(name = "已退款金额")
    @ApiModelProperty("已退款金额")
    private BigDecimal allReturnAmount;

//    @Excel(name = "已退票数量")
    @ApiModelProperty("已退票数量")
    private BigDecimal allReturnInvoiceQty;

//    @Excel(name = "已退票金额")
    @ApiModelProperty("已退票金额")
    private BigDecimal allReturnInvoiceAmount;

//    @Excel(name = "开票状态")
    @ApiModelProperty("开票状态")
    private String invoiceStatus;

    @Excel(name = "单据日期",exportFormat="yyyy-MM-dd",width = 15)
    @ApiModelProperty("单据日期")
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date billDate;

    /**
     * 接收查询条件用
     */
    private String startBillDate;
    private String endBillDate;
    @ApiModelProperty("客户省份")
    private String customerProvince;
    @ApiModelProperty("客户城市")
    private String customerCity;
    @ApiModelProperty("客户等级")
    private String customerGradeName;
    private String receiveBillNo;

    /**
     * 金额折扣小数位
     */
    private Integer value;

}

接口：

    @ApiOperation("销售订单跟踪导出")
    @GetMapping("orderTrackExport")
    @IgnoreUserToken
    @IgnoreClientToken
    public void orderTrackExport(HttpServletResponse response, @RequestParam Map<String, Object> params) {
        try {
            ExportParams exportParams = new ExportParams();
            exportParams.setTitle("销售订单跟踪报表");
            exportParams.setSheetName("销售订单跟踪报表");
            exportParams.setStyle(ExcelExportStylerImpl.class);
            exportParams.setAddIndex(true);
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, SalesOrderTrackDTO.class,salesOrderService.orderTrackExport(params));
            // 设置响应输出的头类型
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("销售订单跟踪报表.xls", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

这里使用ExportParams exportParams = new ExportParams();这个对象，通过设置他来添加序号，点来里面的方法，可以看到

private String[]  exclusions;
/**
 * 是否添加需要需要
 */
private boolean   addIndex;
/**
 * 是否添加需要需要
 */
private String    indexName         = "序号";

ExportParams的这个setAddIndex();方法，可以自增序号，

2.但是添加后，控制台报NullPointer Exception空指针异常，

点开ExcelExportUtil工具类，发现该类使用final修饰，不能被继承

解决方法：

1.重写ExcelExportUtil类：

package com.meishi.manager.util;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelBatchExportService;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil;
import cn.afterturn.easypoi.handler.inter.IExcelExportServer;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * @ClassName ExcelExportUtil
 * @description:
 * @author: qsong
 * @create: 2021-04-13 09:37
 * @Version 1.0
 **/
public class ExcelExportUtil {
    public static       int    USE_SXSSF_LIMIT = 100000;
    public static final String SHEET_NAME      = "sheetName";

    private ExcelExportUtil() {
    }

    /**
     * 大数据量导出
     *
     * @param entity      表格标题属性
     * @param pojoClass   Excel对象Class
     * @param server      查询数据的接口
     * @param queryParams 查询数据的参数
     */
    public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass,
                                          IExcelExportServer server, Object queryParams) {
        ExcelBatchExportService batchServer = new ExcelBatchExportService();
        batchServer.init(entity, pojoClass);
        return batchServer.exportBigExcel(server, queryParams);
    }

    /**
     * 大数据量导出
     *
     * @param entity
     * @param excelParams
     * @param server      查询数据的接口
     * @param queryParams 查询数据的参数
     * @return
     */
    public static Workbook exportBigExcel(ExportParams entity, List<ExcelExportEntity> excelParams,
                                          IExcelExportServer server, Object queryParams) {
        ExcelBatchExportService batchServer = new ExcelBatchExportService();
        batchServer.init(entity, excelParams);
        return batchServer.exportBigExcel(server, queryParams);
    }


    /**
     * @param entity    表格标题属性
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass,
                                       Collection<?> dataSet) {
        Workbook workbook = getWorkbook(entity.getType(), dataSet.size());
        new SubExcelExportService().createSheet(workbook, entity, pojoClass, dataSet);
        return workbook;
    }

    private static Workbook getWorkbook(ExcelType type, int size) {
        if (ExcelType.HSSF.equals(type)) {
            return new HSSFWorkbook();
        } else if (size < USE_SXSSF_LIMIT) {
            return new XSSFWorkbook();
        } else {
            return new SXSSFWorkbook();
        }
    }

    /**
     * 根据Map创建对应的Excel
     *
     * @param entity     表格标题属性
     * @param entityList Map对象列表
     * @param dataSet    Excel对象数据List
     */
    public static Workbook exportExcel(ExportParams entity, List<ExcelExportEntity> entityList,
                                       Collection<?> dataSet) {
        Workbook workbook = getWorkbook(entity.getType(), dataSet.size());
        ;
        new SubExcelExportService().createSheetForMap(workbook, entity, entityList, dataSet);
        return workbook;
    }

    /**
     * 根据Map创建对应的Excel(一个excel 创建多个sheet)
     *
     * @param list 多个Map key title 对应表格Title key entity 对应表格对应实体 key data
     *             Collection 数据
     * @return
     */
    public static Workbook exportExcel(List<Map<String, Object>> list, ExcelType type) {
        Workbook workbook = getWorkbook(type, 0);
        for (Map<String, Object> map : list) {
            ExcelExportService service = new ExcelExportService();
            service.createSheet(workbook, (ExportParams) map.get("title"),
                    (Class<?>) map.get("entity"), (Collection<?>) map.get("data"));
        }
        return workbook;
    }

    /**
     * 导出文件通过模板解析,不推荐这个了,推荐全部通过模板来执行处理
     *
     * @param params    导出参数类
     * @param pojoClass 对应实体
     * @param dataSet   实体集合
     * @param map       模板集合
     * @return
     */
    @Deprecated
    public static Workbook exportExcel(TemplateExportParams params, Class<?> pojoClass,
                                       Collection<?> dataSet, Map<String, Object> map) {
        return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, pojoClass, dataSet,
                map);
    }

    /**
     * 导出文件通过模板解析只有模板,没有集合
     *
     * @param params 导出参数类
     * @param map    模板集合
     * @return
     */
    public static Workbook exportExcel(TemplateExportParams params, Map<String, Object> map) {
        return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, null, null, map);
    }

    /**
     * 导出文件通过模板解析只有模板,没有集合
     * 每个sheet对应一个map,导出到处,key是sheet的NUM
     *
     * @param params 导出参数类
     * @param map    模板集合
     * @return
     */
    public static Workbook exportExcel(Map<Integer, Map<String, Object>> map,
                                       TemplateExportParams params) {
        return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, map);
    }

    /**
     * 导出文件通过模板解析只有模板,没有集合
     * 每个sheet对应一个list,按照数量进行导出排序,key是sheet的NUM
     *
     * @param params 导出参数类
     * @param map    模板集合
     * @return
     */
    public static Workbook exportExcelClone(Map<Integer, List<Map<String, Object>>> map,
                                            TemplateExportParams params) {
        return new ExcelExportOfTemplateUtil().createExcelCloneByTemplate(params, map);
    }
}

注意：重写ExcelExportUtil这个类，去掉final修饰符，同时ExcelExportService需要改成自己自定义的SubExcelExportService，也就是下面这个类

2.发现cn.afterturn.easypoi.excel.export.ExcelExportService没有被final修饰，所以我们定义一个类来继承它，然后重写insertDataToSheet方法，改变的地方就是注释掉排序的代码，具体代码如下：

package com.meishi.manager.util;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import cn.afterturn.easypoi.exception.excel.ExcelExportException;
import cn.afterturn.easypoi.exception.excel.enums.ExcelExportEnum;
import cn.afterturn.easypoi.util.PoiExcelGraphDataUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.util.*;

/**
 * @ClassName SubExcelExportServiceImpl
 * @description:
 * @author: qsong
 * @create: 2021-04-13 09:39
 * @Version 1.0
 **/
public class SubExcelExportService extends ExcelExportService {


    /**
     * 最大行数,超过自动多Sheet
     */
    private static int MAX_NUM = 60000;

    @Override
    protected void insertDataToSheet(Workbook workbook, ExportParams entity,
                                     List<ExcelExportEntity> entityList, Collection<?> dataSet, Sheet sheet) {
        try {
            dataHandler = entity.getDataHandler();
            if (dataHandler != null && dataHandler.getNeedHandlerFields() != null) {
                needHandlerList = Arrays.asList(dataHandler.getNeedHandlerFields());
            }
            dictHandler = entity.getDictHandler();
            i18nHandler = entity.getI18nHandler();
            // 创建表格样式
            setExcelExportStyler(
                    (IExcelExportStyler) entity.getStyle().getConstructor(Workbook.class).newInstance(workbook));
            Drawing patriarch = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet);
            List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
            if (entity.isAddIndex()) {
                excelParams.add(indexExcelEntity(entity));
            }
            excelParams.addAll(entityList);
//       sortAllParams(excelParams);
            int index = entity.isCreateHeadRows() ? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0;
            int titleHeight = index;
            setCellWith(excelParams, sheet);
            setColumnHidden(excelParams, sheet);
            short rowHeight = entity.getHeight() != 0 ? entity.getHeight() : getRowHeight(excelParams);
            setCurrentIndex(1);
            Iterator<?> its = dataSet.iterator();
            List<Object> tempList = new ArrayList<Object>();
            while (its.hasNext()) {
                Object t = its.next();
                index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0];
                tempList.add(t);
                if (index >= MAX_NUM) {
                    break;
                }
            }
            if (entity.getFreezeCol() != 0) {
                sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0);
            }

            mergeCells(sheet, excelParams, titleHeight);

            its = dataSet.iterator();
            for (int i = 0, le = tempList.size(); i < le; i++) {
                its.next();
                its.remove();
            }
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug("List data more than max ,data size is {}", dataSet.size());
            }
            // 发现还有剩余list 继续循环创建Sheet
            if (dataSet.size() > 0) {
                createSheetForMap(workbook, entity, entityList, dataSet);
            } else {
                // 创建合计信息
                addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet);
            }

        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
        }
    }
}

注意：只需要注释掉这个方法即可// sortAllParams(excelParams);

3.然后，改变接口中com.meishi.manager.util.ExcelExportUtil的导入

@ApiOperation("销售订单跟踪导出")
@GetMapping("orderTrackExport")
@IgnoreUserToken
@IgnoreClientToken
public void orderTrackExport(HttpServletResponse response, @RequestParam Map<String, Object> params) {
    try {
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle("销售订单跟踪报表");
        exportParams.setSheetName("销售订单跟踪报表");
        exportParams.setStyle(ExcelExportStylerImpl.class);
        exportParams.setAddIndex(true);
        Workbook workbook = com.meishi.manager.util.ExcelExportUtil.exportExcel(exportParams, SalesOrderTrackDTO.class,salesOrderService.orderTrackExport(params));
        // 设置响应输出的头类型
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("销售订单跟踪报表.xls", "UTF-8"));
        workbook.write(response.getOutputStream());
    } catch (IOException e) {
        e.printStackTrace();
    }
}

4.也可以自定义单元格样式

package com.meishi.crm.util;

import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;

/**
 * @ClassName ExcelExportStylerImpl
 * @description:
 * @author: qsong
 * @create: 2021-04-12 17:44
 * @Version 1.0
 **/
public class ExcelExportStylerImpl extends AbstractExcelExportStyler
        implements IExcelExportStyler {
    public ExcelExportStylerImpl(Workbook workbook) {
        super.createStyles(workbook);
    }

    @Override
    public CellStyle getTitleStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setWrapText(true);
        //设置字体
        Font font = this.workbook.createFont();
        font.setColor(IndexedColors.RED.index);
        titleStyle.setFont(font);
        return titleStyle;
    }

    @Override
    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

    @Override
    public CellStyle getHeaderStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setColor(IndexedColors.RED.index);
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        return titleStyle;
    }

    @Override
    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }
}