easypoi实现数据导出与动态表头

小杨同学~

于 2024-07-14 20:26:55 发布

阅读量22

点赞数

文章标签： python 人工智能开发语言 java

本文链接：https://blog.csdn.net/weixin_38937840/article/details/140384667

版权

话不多说，直接上代码，这里也包含一些代源码重写的方式

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

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 cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import lombok.Data;

import static cn.afterturn.easypoi.excel.ExcelExportUtil.USE_SXSSF_LIMIT;

// 主表实体类
@ExcelTarget("person")
@Data
class Person {
    @Excel(name = "姓名", needMerge = true)
    private String name;
    @Excel(name = "年龄", needMerge = true)
    private int age;
    @ExcelCollection(name = "订单列表")
    private List<Order> orders = new ArrayList<>();

    // 省略 getter 和 setter 方法
    // ... (确保这里包含getter和setter方法)
}

// 子表实体类
@Data
@ExcelTarget("order")
class Order {
    @Excel(name = "订单名称")
    private String orderName;
    @Excel(name = "价格")
    private double price;

    // 省略 getter 和 setter 方法
    // ... (确保这里包含getter和setter方法)
}

public class EasyPoiExportExample {

    public static void main(String[] args) {
        // 准备数据
        Person person = new Person();
        person.setName("张三");
        person.setAge(30);

        Order order1 = new Order();
        order1.setOrderName("订单1");
        order1.setPrice(100.0);

        Order order2 = new Order();
        order2.setOrderName("订单2");
        order2.setPrice(200.0);

        person.getOrders().add(order1   );
        person.getOrders().add(order2);

        List<Person> personList = new ArrayList<>();

        for (int i = 0; i < 5; i++) {
            personList.add(person);
        }



        // 导出操作
        long millis = System.currentTimeMillis();
        ExportParams exportParams = new ExportParams("人员订单表", "人员订单表", ExcelType.XSSF);
        // 排除不想要的列，实现动态的表头
        exportParams.setExclusions(new String[]{"价格"});
        //Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Person.class, personList);
        // 存在大批量的数据merge时可以重写以提高速度
         Workbook workbook = exportExcel(exportParams, Person.class, personList);
        System.out.println(System.currentTimeMillis() - millis);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream("PersonOrderList.xlsx");
            workbook.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * @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 ExcelExportServiceMerge().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();
        }
    }
}

重写 ExcelExportService


import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;

import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
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 cn.afterturn.easypoi.util.PoiMergeCellUtil;
import cn.afterturn.easypoi.util.PoiPublicUtil;

/**
 * Excel导出服务
 *
 */
public class ExcelExportServiceMerge extends BaseExportServiceMerge {

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

    protected int createHeaderAndTitle(ExportParams entity, Sheet sheet, Workbook workbook,
                                       List<ExcelExportEntity> excelParams) {
        int rows = 0, fieldLength = getFieldLength(excelParams);
        if (entity.getTitle() != null) {
            rows += createTitle2Row(entity, sheet, workbook, fieldLength);
        }
        createHeaderRow(entity, sheet, workbook, rows, excelParams, 0);
        rows += getRowNums(excelParams, true);
        if (entity.isFixedTitle()) {
            sheet.createFreezePane(0, rows, 0, rows);
        }
        return rows;
    }

    /**
     * 创建表头
     */
    private int createHeaderRow(ExportParams title, Sheet sheet, Workbook workbook, int index,
                                List<ExcelExportEntity> excelParams, int cellIndex) {
        Row row  = sheet.getRow(index) == null ? sheet.createRow(index) : sheet.getRow(index);
        int rows = getRowNums(excelParams, true);
        row.setHeight(title.getHeaderHeight());
        Row listRow = null;
        if (rows >= 2) {
            listRow = sheet.getRow(index + 1);
            if (listRow == null) {
                listRow = sheet.createRow(index + 1);
                listRow.setHeight(title.getHeaderHeight());

            }
        }
        int       groupCellLength = 0;
        CellStyle titleStyle      = getExcelExportStyler().getTitleStyle(title.getColor());
        for (int i = 0, exportFieldTitleSize = excelParams.size(); i < exportFieldTitleSize; i++) {
            ExcelExportEntity entity = excelParams.get(i);
            // 加入换了groupName或者结束就，就把之前的那个换行
            if (StringUtils.isBlank(entity.getGroupName()) || i == 0 || !entity.getGroupName().equals(excelParams.get(i - 1).getGroupName())) {
                if (groupCellLength > 1) {
                    sheet.addMergedRegion(new CellRangeAddress(index, index, cellIndex - groupCellLength, cellIndex - 1));
                }
                groupCellLength = 0;
            }
            if (StringUtils.isNotBlank(entity.getGroupName())) {
                createStringCell(row, cellIndex, entity.getGroupName(), titleStyle, entity);
                createStringCell(listRow, cellIndex, entity.getName(), titleStyle, entity);
                groupCellLength++;
            } else if (StringUtils.isNotBlank(entity.getName())) {
                createStringCell(row, cellIndex, entity.getName(), titleStyle, entity);
            }
            if (entity.getList() != null) {
                // 保持原来的
                int tempCellIndex = cellIndex;
                cellIndex = createHeaderRow(title, sheet, workbook, rows == 1 ? index : index + 1, entity.getList(), cellIndex);
                List<ExcelExportEntity> sTitel = entity.getList();
                if (StringUtils.isNotBlank(entity.getName()) && sTitel.size() > 1) {
                    PoiMergeCellUtil.addMergedRegion(sheet, index, index, tempCellIndex, tempCellIndex + sTitel.size() - 1);
                }
                /*for (int j = 0, size = sTitel.size(); j < size; j++) {

                    createStringCell(rows == 2 ? listRow : row, cellIndex, sTitel.get(j).getName(),
                            titleStyle, entity);
                    cellIndex++;
                }*/
                cellIndex--;
            } else if (rows > 1 && StringUtils.isBlank(entity.getGroupName())) {
                createStringCell(listRow, cellIndex, "", titleStyle, entity);
                PoiMergeCellUtil.addMergedRegion(sheet, index, index + rows - 1, cellIndex, cellIndex);
            }
            cellIndex++;
        }
        if (groupCellLength > 1) {
            PoiMergeCellUtil.addMergedRegion(sheet, index, index, cellIndex - groupCellLength, cellIndex - 1);
        }
        return cellIndex;

    }

    /**
     * 创建 表头改变
     */
    public int createTitle2Row(ExportParams entity, Sheet sheet, Workbook workbook,
                               int fieldWidth) {

        Row row = sheet.createRow(0);
        row.setHeight(entity.getTitleHeight());
        createStringCell(row, 0, entity.getTitle(),
                getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
        for (int i = 1; i <= fieldWidth; i++) {
            createStringCell(row, i, "",
                    getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
        }
        PoiMergeCellUtil.addMergedRegion(sheet, 0, 0, 0, fieldWidth);
        if (entity.getSecondTitle() != null) {
            row = sheet.createRow(1);
            row.setHeight(entity.getSecondTitleHeight());
            CellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.RIGHT);
            createStringCell(row, 0, entity.getSecondTitle(), style, null);
            for (int i = 1; i <= fieldWidth; i++) {
                createStringCell(row, i, "",
                        getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
            }
            PoiMergeCellUtil.addMergedRegion(sheet, 1, 1, 0, fieldWidth);
            return 2;
        }
        return 1;
    }

    public void createSheet(Workbook workbook, ExportParams entity, Class<?> pojoClass,
                            Collection<?> dataSet) {
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Excel export start ,class is {}", pojoClass);
            LOGGER.debug("Excel version is {}",
                    entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
        }
        if (workbook == null || entity == null || pojoClass == null || dataSet == null) {
            throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
        }
        try {
            List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
            // 得到所有字段
            Field[]     fileds   = PoiPublicUtil.getClassFields(pojoClass);
            ExcelTarget etarget  = pojoClass.getAnnotation(ExcelTarget.class);
            String      targetId = etarget == null ? null : etarget.value();
            getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass,
                    null, null);
            //获取所有参数后,后面的逻辑判断就一致了
            createSheetForMap(workbook, entity, excelParams, dataSet);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
        }
    }

    public void createSheetForMap(Workbook workbook, ExportParams entity,
                                  List<ExcelExportEntity> entityList, Collection<?> dataSet) {
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Excel version is {}",
                    entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
        }
        if (workbook == null || entity == null || entityList == null || dataSet == null) {
            throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
        }
        super.type = entity.getType();
        if (type.equals(ExcelType.XSSF)) {
            MAX_NUM = 1000000;
        }
        if (entity.getMaxNum() > 0) {
            MAX_NUM = entity.getMaxNum();
        }
        Sheet sheet = null;
        try {
            sheet = workbook.createSheet(entity.getSheetName());
        } catch (Exception e) {
            // 重复遍历,出现了重名现象,创建非指定的名称Sheet
            sheet = workbook.createSheet();
        }
        if (entity.isReadonly()) {
            sheet.protectSheet(UUID.randomUUID().toString());
        }
        if (dataSet.getClass().getClass().getName().contains("Unmodifiable")) {
            List dataTemp = new ArrayList<>();
            dataTemp.addAll(dataSet);
            dataSet = dataTemp;
        }
        insertDataToSheet(workbook, entity, entityList, dataSet, sheet);
    }

    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);
            createAddressList(sheet, index, excelParams, 0);
            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());
        }
    }

    /**
     * 插入下拉
     *
     * @param sheet
     * @param index
     * @param excelParams
     */
    private int createAddressList(Sheet sheet, int index, List<ExcelExportEntity> excelParams, int cellIndex) {
        for (int i = 0; i < excelParams.size(); i++) {
            if (excelParams.get(i).isAddressList()) {
                ExcelExportEntity entity = excelParams.get(i);
                CellRangeAddressList regions = new CellRangeAddressList(index,
                        this.type.equals(ExcelType.XSSF) ? 1000000 : 65535, cellIndex, cellIndex);
                sheet.addValidationData(sheet.getDataValidationHelper().createValidation(sheet.getDataValidationHelper().createExplicitListConstraint(getAddressListValues(entity)), regions));
            }
            if (excelParams.get(i).getList() != null) {
                cellIndex = createAddressList(sheet, index, excelParams.get(i).getList(), cellIndex);
            } else {
                cellIndex++;
            }
        }
        return cellIndex;
    }

    private String[] getAddressListValues(ExcelExportEntity entity) {
        if (StringUtils.isNotEmpty(entity.getDict())) {
            String[] arr = new String[this.dictHandler.getList(entity.getDict()).size()];
            for (int i = 0; i < arr.length; i++) {
                arr[i] = this.dictHandler.getList(entity.getDict()).get(i).get("dictValue").toString();
            }
            return arr;
        } else if (entity.getReplace() != null && entity.getReplace().length > 0) {
            String[] arr = new String[entity.getReplace().length];
            for (int i = 0; i < arr.length; i++) {
                arr[i] = entity.getReplace()[i].split("_")[0];
            }
            return arr;
        }
        throw new ExcelExportException(entity.getName() + "没有可以创建下来的数据,请addressList不要设置为true");
    }


}

重写 BaseExportService


package com.javayh.async.task.easy;

import cn.afterturn.easypoi.cache.ImageCache;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.vo.BaseEntityTypeConstants;
import cn.afterturn.easypoi.excel.entity.vo.PoiBaseConstants;
import cn.afterturn.easypoi.excel.export.base.ExportCommonService;
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 cn.afterturn.easypoi.util.PoiPublicUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.builder.ReflectionToStringBuilder;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import java.text.DecimalFormat;
import java.util.*;

/**
 * 提供POI基础操作服务
 *
 * @author JueYue 2014年6月17日 下午6:15:13
 */
@SuppressWarnings("unchecked")
public abstract class BaseExportServiceMerge extends ExportCommonService {

    private int currentIndex = 0;

    protected ExcelType type = ExcelType.HSSF;

    private Map<Integer, Double> statistics = new HashMap<Integer, Double>();

    private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");

    protected IExcelExportStyler excelExportStyler;

    /**
     * 创建 最主要的 Cells
     */
    public int[] createCells(Drawing patriarch, int index, Object t,
                             List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook,
                             short rowHeight, int cellNum) {
        try {
            ExcelExportEntity entity;
            Row               row = sheet.getRow(index) == null ? sheet.createRow(index) : sheet.getRow(index);
            if (rowHeight != -1) {
                row.setHeight(rowHeight);
            }
            int maxHeight = 1, listMaxHeight = 1;
            // 合并需要合并的单元格
            int margeCellNum = cellNum;
            int indexKey     = 0;
            if (excelParams != null && !excelParams.isEmpty()) {
                indexKey = createIndexCell(row, index, excelParams.get(0));
            }
            cellNum += indexKey;
            for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
                entity = excelParams.get(k);
                //不论数据是否为空都应该把该列的数据跳过去
                if (entity.getList() != null) {
                    Collection<?> list          = getListCellValue(entity, t);
                    int           tmpListHeight = 0;
                    if (list != null && list.size() > 0) {
                        int tempCellNum = 0;
                        for (Object obj : list) {
                            int[] temp = createCells(patriarch, index + tmpListHeight, obj, entity.getList(), sheet, workbook, rowHeight, cellNum);
                            tempCellNum = temp[1];
                            tmpListHeight += temp[0];
                        }
                        cellNum = tempCellNum;
                        listMaxHeight = Math.max(listMaxHeight, tmpListHeight);
                    } else {
                        cellNum = cellNum + getListCellSize(entity.getList());
                    }
                } else {
                    Object value = getCellValue(entity, t);

                    if (entity.getType() == BaseEntityTypeConstants.STRING_TYPE) {
                        createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                                index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity),
                                entity);

                    } else if (entity.getType() == BaseEntityTypeConstants.DOUBLE_TYPE) {
                        createDoubleCell(row, cellNum++, value == null ? "" : value.toString(),
                                index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity),
                                entity);
                    } else {
                        createImageCell(patriarch, entity, row, cellNum++,
                                value == null ? "" : value.toString(), t);
                    }
                    if (entity.isHyperlink()) {
                        row.getCell(cellNum - 1)
                                .setHyperlink(dataHandler.getHyperlink(
                                        row.getSheet().getWorkbook().getCreationHelper(), t,
                                        entity.getName(), value));
                    }
                }
            }
            maxHeight += listMaxHeight - 1;
            if (indexKey == 1 && excelParams.get(1).isNeedMerge()) {
                excelParams.get(0).setNeedMerge(true);
            }
            for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
                entity = excelParams.get(k);
                if (entity.getList() != null) {
                    margeCellNum += entity.getList().size();
                } else if (entity.isNeedMerge() && maxHeight > 1) {
                    for (int i = index + 1; i < index + maxHeight; i++) {
                        if (sheet.getRow(i) == null) {
                            sheet.createRow(i);
                        }
                        sheet.getRow(i).createCell(margeCellNum);
                        sheet.getRow(i).getCell(margeCellNum).setCellStyle(getStyles(false, entity));
                    }
                    PoiMergeCellUtilMerge.addMergedRegion(sheet, index, index + maxHeight - 1, margeCellNum, margeCellNum);
                    margeCellNum++;
                }
            }
            return new int[]{maxHeight, cellNum};
        } catch (Exception e) {
            LOGGER.error("excel cell export error ,data is :{}", ReflectionToStringBuilder.toString(t));
            LOGGER.error(e.getMessage(), e);
            throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e);
        }

    }

    /**
     * 获取集合的宽度
     *
     * @param list
     * @return
     */
    protected int getListCellSize(List<ExcelExportEntity> list) {
        int cellSize = 0;
        for (ExcelExportEntity ee : list) {
            if (ee.getList() != null) {
                cellSize += getListCellSize(ee.getList());
            } else {
                cellSize++;
            }
        }
        return cellSize;
    }

    /**
     * 图片类型的Cell
     */
    public void createImageCell(Drawing patriarch, ExcelExportEntity entity, Row row, int i,
                                String imagePath, Object obj) throws Exception {
        Cell   cell  = row.createCell(i);
        byte[] value = null;
        if (entity.getExportImageType() != 1) {
            value = (byte[]) (entity.getMethods() != null
                    ? getFieldBySomeMethod(entity.getMethods(), obj)
                    : entity.getMethod().invoke(obj, new Object[]{}));
        }
        createImageCell(cell, 50 * entity.getHeight(), entity.getExportImageType() == 1 ? imagePath : null, value);

    }


    /**
     * 图片类型的Cell
     */
    public void createImageCell(Cell cell, double height,
                                String imagePath, byte[] data) throws Exception {
        if (height > cell.getRow().getHeight()) {
            cell.getRow().setHeight((short) height);
        }
        ClientAnchor anchor;
        if (type.equals(ExcelType.HSSF)) {
            anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1),
                    cell.getRow().getRowNum() + 1);
        } else {
            anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1),
                    cell.getRow().getRowNum() + 1);
        }
        if (StringUtils.isNotEmpty(imagePath)) {
            data = ImageCache.getImage(imagePath);
        }
        if (data != null) {
            PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
                    cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
        }

    }

    /**
     * 图片类型的Cell
     */
    public void createImageCell(Cell cell, double height, int rowspan, int colspan,
                                String imagePath, byte[] data) throws Exception {
        if (height > cell.getRow().getHeight()) {
            cell.getRow().setHeight((short) height);
        }
        ClientAnchor anchor;
        if (type.equals(ExcelType.HSSF)) {
            anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + colspan),
                    cell.getRow().getRowNum() + rowspan);
        } else {
            anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + colspan),
                    cell.getRow().getRowNum() + rowspan);
        }
        if (StringUtils.isNotEmpty(imagePath)) {
            data = ImageCache.getImage(imagePath);
        }
        if (data != null) {
            PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
                    cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
        }

    }

    private int createIndexCell(Row row, int index, ExcelExportEntity excelExportEntity) {
        if (excelExportEntity.getFormat() != null && excelExportEntity.getFormat().equals(PoiBaseConstants.IS_ADD_INDEX)) {
            createStringCell(row, 0, currentIndex + "",
                    index % 2 == 0 ? getStyles(false, null) : getStyles(true, null), null);
            currentIndex = currentIndex + 1;
            return 1;
        }
        return 0;
    }

    /**
     * 创建List之后的各个Cells
     */
    public void createListCells(Drawing patriarch, int index, int cellNum, Object obj,
                                List<ExcelExportEntity> excelParams, Sheet sheet,
                                Workbook workbook, short rowHeight) throws Exception {
        ExcelExportEntity entity;
        Row               row;
        if (sheet.getRow(index) == null) {
            row = sheet.createRow(index);
            if (rowHeight != -1) {
                row.setHeight(rowHeight);
            }
        } else {
            row = sheet.getRow(index);
            if (rowHeight != -1) {
                row.setHeight(rowHeight);
            }
        }
        for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
            entity = excelParams.get(k);
            Object value = getCellValue(entity, obj);
            if (entity.getType() == BaseEntityTypeConstants.STRING_TYPE) {
                createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                        row.getRowNum() % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity),
                        entity);
                if (entity.isHyperlink()) {
                    row.getCell(cellNum - 1)
                            .setHyperlink(dataHandler.getHyperlink(
                                    row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(),
                                    value));
                }
            } else if (entity.getType() == BaseEntityTypeConstants.DOUBLE_TYPE) {
                createDoubleCell(row, cellNum++, value == null ? "" : value.toString(),
                        index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
                if (entity.isHyperlink()) {
                    row.getCell(cellNum - 1)
                            .setHyperlink(dataHandler.getHyperlink(
                                    row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(),
                                    value));
                }
            } else {
                createImageCell(patriarch, entity, row, cellNum++,
                        value == null ? "" : value.toString(), obj);
            }
        }
    }

    /**
     * 创建文本类型的Cell
     */
    public void createStringCell(Row row, int index, String text, CellStyle style,
                                 ExcelExportEntity entity) {
        Cell cell = row.createCell(index);
        if (style != null && style.getDataFormat() > 0 && style.getDataFormat() < 12) {
            cell.setCellValue(Double.parseDouble(text));
            cell.setCellType(CellType.NUMERIC);
        } else {
            RichTextString rtext;
            if (type.equals(ExcelType.HSSF)) {
                rtext = new HSSFRichTextString(text);
            } else {
                rtext = new XSSFRichTextString(text);
            }
            cell.setCellValue(rtext);
        }
        if (style != null) {
            cell.setCellStyle(style);
        }
        addStatisticsData(index, text, entity);
    }

    /**
     * 创建数字类型的Cell
     */
    public void createDoubleCell(Row row, int index, String text, CellStyle style,
                                 ExcelExportEntity entity) {
        Cell cell = row.createCell(index);
        cell.setCellType(CellType.NUMERIC);
        if (text != null && text.length() > 0) {
            try {
                cell.setCellValue(Double.parseDouble(text));
            } catch (NumberFormatException e) {
                cell.setCellType(CellType.STRING);
                cell.setCellValue(text);
            }
        }

        if (style != null) {
            cell.setCellStyle(style);
        }
        addStatisticsData(index, text, entity);
    }

    /**
     * 创建统计行
     */
    public void addStatisticsRow(CellStyle styles, Sheet sheet) {
        if (statistics.size() > 0) {
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug("add statistics data ,size is {}", statistics.size());
            }
            Row          row  = sheet.createRow(sheet.getLastRowNum() + 1);
            Set<Integer> keys = statistics.keySet();
            createStringCell(row, 0, "合计", styles, null);
            for (Integer key : keys) {
                createStringCell(row, key, DOUBLE_FORMAT.format(statistics.get(key)), styles, null);
            }
            statistics.clear();
        }

    }

    /**
     * 合计统计信息
     */
    private void addStatisticsData(Integer index, String text, ExcelExportEntity entity) {
        if (entity != null && entity.isStatistics()) {
            Double temp = 0D;
            if (!statistics.containsKey(index)) {
                statistics.put(index, temp);
            }
            try {
                temp = Double.valueOf(text);
            } catch (NumberFormatException e) {
            }
            statistics.put(index, statistics.get(index) + temp);
        }
    }

    /**
     * 获取图片类型,设置图片插入类型
     *
     * @author JueYue 2013年11月25日
     */
    public int getImageType(byte[] value) {
        String type = PoiPublicUtil.getFileExtendName(value);
        if ("JPG".equalsIgnoreCase(type)) {
            return Workbook.PICTURE_TYPE_JPEG;
        } else if ("PNG".equalsIgnoreCase(type)) {
            return Workbook.PICTURE_TYPE_PNG;
        }

        return Workbook.PICTURE_TYPE_JPEG;
    }

    private Map<Integer, int[]> getMergeDataMap(List<ExcelExportEntity> excelParams) {
        Map<Integer, int[]> mergeMap = new HashMap<Integer, int[]>();
        // 设置参数顺序,为之后合并单元格做准备
        int i = 0;
        for (ExcelExportEntity entity : excelParams) {
            if (entity.isMergeVertical()) {
                mergeMap.put(i, entity.getMergeRely());
            }
            if (entity.getList() != null) {
                for (ExcelExportEntity inner : entity.getList()) {
                    if (inner.isMergeVertical()) {
                        mergeMap.put(i, inner.getMergeRely());
                    }
                    i++;
                }
            } else {
                i++;
            }
        }
        return mergeMap;
    }

    /**
     * 获取样式
     */
    public CellStyle getStyles(boolean needOne, ExcelExportEntity entity) {
        return excelExportStyler.getStyles(needOne, entity);
    }

    /**
     * 合并单元格
     */
    public void mergeCells(Sheet sheet, List<ExcelExportEntity> excelParams, int titleHeight) {
        Map<Integer, int[]> mergeMap = getMergeDataMap(excelParams);
        PoiMergeCellUtilMerge.mergeCells(sheet, mergeMap, titleHeight);
    }

    public void setCellWith(List<ExcelExportEntity> excelParams, Sheet sheet) {
        int index = 0;
        for (int i = 0; i < excelParams.size(); i++) {
            if (excelParams.get(i).getList() != null) {
                List<ExcelExportEntity> list = excelParams.get(i).getList();
                for (int j = 0; j < list.size(); j++) {
                    sheet.setColumnWidth(index, (int) (256 * list.get(j).getWidth()));
                    index++;
                }
            } else {
                sheet.setColumnWidth(index, (int) (256 * excelParams.get(i).getWidth()));
                index++;
            }
        }
    }


    public void setColumnHidden(List<ExcelExportEntity> excelParams, Sheet sheet) {
        int index = 0;
        for (int i = 0; i < excelParams.size(); i++) {
            if (excelParams.get(i).getList() != null) {
                List<ExcelExportEntity> list = excelParams.get(i).getList();
                for (int j = 0; j < list.size(); j++) {
                    sheet.setColumnHidden(index, list.get(j).isColumnHidden());
                    index++;
                }
            } else {
                sheet.setColumnHidden(index, excelParams.get(i).isColumnHidden());
                index++;
            }
        }
    }

    public void setCurrentIndex(int currentIndex) {
        this.currentIndex = currentIndex;
    }

    public void setExcelExportStyler(IExcelExportStyler excelExportStyler) {
        this.excelExportStyler = excelExportStyler;
    }

    public IExcelExportStyler getExcelExportStyler() {
        return excelExportStyler;
    }

}

重写 PoiMergeCellUtil

package com.javayh.async.task.easy;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCells;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.afterturn.easypoi.excel.entity.params.MergeEntity;
import cn.afterturn.easypoi.exception.excel.ExcelExportException;
import cn.afterturn.easypoi.util.PoiCellUtil;

/**
 * 纵向合并单元格工具类
 *
 * @author JueYue
 * 2015年6月21日 上午11:21:40
 */
public final class PoiMergeCellUtilMerge {

    private static final Logger LOGGER = LoggerFactory.getLogger(PoiMergeCellUtilMerge.class);

    private PoiMergeCellUtilMerge() {
    }

    /**
     * 纵向合并相同内容的单元格
     *
     * @param sheet
     * @param startRow 开始行
     * @param columns  需要处理的列
     */
    public static void mergeCells(Sheet sheet, int startRow, Integer... columns) {
        if (columns == null) {
            throw new ExcelExportException("至少需要处理1列");
        }
        Map<Integer, int[]> mergeMap = new HashMap<Integer, int[]>();
        for (int i = 0; i < columns.length; i++) {
            mergeMap.put(columns[i], null);
        }
        mergeCells(sheet, mergeMap, startRow, sheet.getLastRowNum());
    }

    /**
     * 纵向合并相同内容的单元格
     *
     * @param sheet
     * @param mergeMap key--列,value--依赖的列,没有传空
     * @param startRow 开始行
     */
    public static void mergeCells(Sheet sheet, Map<Integer, int[]> mergeMap, int startRow) {
        mergeCells(sheet, mergeMap, startRow, sheet.getLastRowNum());
    }

    /**
     * 纵向合并相同内容的单元格
     *
     * @param sheet
     * @param mergeMap key--列,value--依赖的列,没有传空
     * @param startRow 开始行
     * @param endRow   结束行
     */
    public static void mergeCells(Sheet sheet, Map<Integer, int[]> mergeMap, int startRow,
                                  int endRow) {
        Map<Integer, MergeEntity> mergeDataMap = new HashMap<Integer, MergeEntity>();
        if (mergeMap.size() == 0) {
            return;
        }
        Row row;
        Set<Integer> sets = mergeMap.keySet();
        String text;
        for (int i = startRow; i <= endRow; i++) {
            row = sheet.getRow(i);
            for (Integer index : sets) {
                if (row == null || row.getCell(index) == null) {
                    if (mergeDataMap.get(index) == null) {
                        continue;
                    }
                    if (mergeDataMap.get(index).getEndRow() == 0) {
                        mergeDataMap.get(index).setEndRow(i - 1);
                    }
                } else {
                    text = PoiCellUtil.getCellValue(row.getCell(index));
                    if (StringUtils.isNotEmpty(text)) {
                        handlerMergeCells(index, i, text, mergeDataMap, sheet, row.getCell(index),
                            mergeMap.get(index));
                    } else {
                        mergeCellOrContinue(index, mergeDataMap, sheet);
                    }
                }
            }
        }
        if (mergeDataMap.size() > 0) {
            for (Integer index : mergeDataMap.keySet()) {
                if (mergeDataMap.get(index).getEndRow() > mergeDataMap.get(index).getStartRow()) {
                    PoiMergeCellUtilMerge.addMergedRegion(sheet, mergeDataMap.get(index).getStartRow(),
                        mergeDataMap.get(index).getEndRow(), index, index);
                }
            }
        }

    }

    /**
     * 处理合并单元格
     *
     * @param index
     * @param rowNum
     * @param text
     * @param mergeDataMap
     * @param sheet
     * @param cell
     * @param delys
     */
    private static void handlerMergeCells(Integer index, int rowNum, String text,
                                          Map<Integer, MergeEntity> mergeDataMap, Sheet sheet,
                                          Cell cell, int[] delys) {
        if (mergeDataMap.containsKey(index)) {
            if (checkIsEqualByCellContents(mergeDataMap.get(index), text, cell, delys, rowNum)) {
                mergeDataMap.get(index).setEndRow(rowNum);
            } else {
                if (mergeDataMap.get(index).getEndRow() > mergeDataMap.get(index).getStartRow()) {
                    PoiMergeCellUtilMerge.addMergedRegion(sheet, mergeDataMap.get(index).getStartRow(),
                        mergeDataMap.get(index).getEndRow(), index, index);
                }
                mergeDataMap.put(index, createMergeEntity(text, rowNum, cell, delys));
            }
        } else {
            mergeDataMap.put(index, createMergeEntity(text, rowNum, cell, delys));
        }
    }

    /**
     * 字符为空的情况下判断
     *
     * @param index
     * @param mergeDataMap
     * @param sheet
     */
    private static void mergeCellOrContinue(Integer index, Map<Integer, MergeEntity> mergeDataMap,
                                            Sheet sheet) {
        if (mergeDataMap.containsKey(index)
            && mergeDataMap.get(index).getEndRow() != mergeDataMap.get(index).getStartRow()) {
            try {
                PoiMergeCellUtilMerge.addMergedRegion(sheet, mergeDataMap.get(index).getStartRow(),
                    mergeDataMap.get(index).getEndRow(), index, index);
            } catch (Exception e) {

            }
            mergeDataMap.remove(index);
        }
    }

    private static MergeEntity createMergeEntity(String text, int rowNum, Cell cell, int[] delys) {
        MergeEntity mergeEntity = new MergeEntity(text, rowNum, rowNum);
        // 存在依赖关系
        if (delys != null && delys.length != 0) {
            List<String> list = new ArrayList<String>(delys.length);
            mergeEntity.setRelyList(list);
            for (int i = 0; i < delys.length; i++) {
                list.add(getCellNotNullText(cell, delys[i], rowNum));
            }
        }
        return mergeEntity;
    }

    private static boolean checkIsEqualByCellContents(MergeEntity mergeEntity, String text,
                                                      Cell cell, int[] delys, int rowNum) {
        // 没有依赖关系
        if (delys == null || delys.length == 0) {
            return mergeEntity.getText().equals(text);
        }
        // 存在依赖关系 测试
        if (mergeEntity.getText().equals(text)) {
            for (int i = 0; i < delys.length; i++) {
                if (mergeEntity.getRelyList().get(i) == null || !mergeEntity.getRelyList().get(i)
                    .equals(getCellNotNullText(cell, delys[i], rowNum))) {
                    return false;
                }
            }
            return true;
        }
        return false;
    }

    /**
     * 获取一个单元格的值,确保这个单元格必须有值,不然向上查询
     *
     * @param cell
     * @param index
     * @param rowNum
     * @return
     */
    private static String getCellNotNullText(Cell cell, int index, int rowNum) {
        if (cell == null || cell.getRow() == null) {
            return null;
        }
        if (cell.getRow().getCell(index) != null
            && StringUtils.isNotEmpty(PoiCellUtil.getCellValue(cell.getRow().getCell(index)))) {
            return PoiCellUtil.getCellValue(cell.getRow().getCell(index));
        }
        return getCellNotNullText(cell.getRow().getSheet().getRow(--rowNum).getCell(index), index,
            rowNum);
    }


    public static void addMergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        try {

            add(sheet, new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        } catch (Exception e) {
            LOGGER.debug("发生了一次合并单元格错误,{},{},{},{}", new Integer[]{
                firstRow, lastRow, firstCol, lastCol
            });
            // 忽略掉合并的错误,不打印异常
            LOGGER.debug(e.getMessage(), e);
        }
    }

    private static void add(Sheet sheet, CellRangeAddress cellAddresses) {
        XSSFSheet xssfSheet = (XSSFSheet) sheet;
        CTWorksheet worksheet = xssfSheet.getWorkbook().getSheetAt(0).getCTWorksheet();
        CTMergeCells ctMergeCells = worksheet.isSetMergeCells() ? worksheet.getMergeCells() : worksheet.addNewMergeCells();
        CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
        ctMergeCell.setRef(cellAddresses.formatAsString());
    }

}