easyexcel基于模板导出行列合并处理器代码和简单讲解

1.只需要合并列，已经给出了第一行，剩余行生成按照第一行格式进行合并

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.util.List;

public class MyExcelHandler extends AbstractMergeStrategy {


    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        if(relativeRowIndex==null ||relativeRowIndex==0){
            return;
        }
        int rowIndex = cell.getRowIndex();
        int colIndex = cell.getColumnIndex();
        sheet=cell.getSheet();
        Row preRow = sheet.getRow(rowIndex - 1);
        Cell preCell = preRow.getCell(colIndex);//获取上一行的该格
        List<CellRangeAddress> list = sheet.getMergedRegions();
        CellStyle cs = cell.getCellStyle();
        cell.setCellStyle(cs);
        for (int i = 0; i < list.size(); i++) {
            CellRangeAddress cellRangeAddress = list.get(i);
            if (cellRangeAddress.containsRow(preCell.getRowIndex()) && cellRangeAddress.containsColumn(preCell.getColumnIndex())) {
                int lastColIndex = cellRangeAddress.getLastColumn();
                int firstColIndex = cellRangeAddress.getFirstColumn();
                CellRangeAddress cra = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), firstColIndex, lastColIndex);
                sheet.addMergedRegion(cra);
                RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
                return;
            }
        }

    }

}

2.既需要合并行也需要合并列，首先模板需要按照最细的行来设计，例如：

那么我们的模板应该设计成：

处理器如下：

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.math.BigDecimal;
import java.util.List;

public class ExcelFillCellMergeStrategy implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelFillCellMergeStrategy() {
    }

    public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        int curRowIndex = cell.getRowIndex();
        int curColIndex = cell.getColumnIndex();
        if (curRowIndex >= mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(cell.getSheet(), cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     *
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(Sheet sheet, Cell cell, int curRowIndex, int curColIndex) {
        Object curData=getCellValue(cell);
        Object preData = getCellValue(cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex));
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        //此处需要注意：因为我是按照序号确定是否需要合并的，所以获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并
        Boolean bool = getCellValue(cell.getRow().getCell(0)).equals(getCellValue(cell.getSheet().getRow(curRowIndex - 1).getCell(0)));
        if (curRowIndex!=5&&curColIndex==7){
            CellRangeAddress    cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, 11);
            sheet.addMergedRegion(cellRangeAddress);
            RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
            RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
            return;
        }
        if (dataBool && bool) {
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddr, sheet);
                    RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddr, sheet);
                    RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddr, sheet);
                    RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddr, sheet);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                if (curColIndex!=7){
                    CellRangeAddress cellRangeAddress=null;
                    cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                    sheet.addMergedRegion(cellRangeAddress);
                    RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
                    RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
                    RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
                    RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
                }
            }
        }
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
            } else {
                return new BigDecimal(cell.getNumericCellValue()).toString();
            }
        } else if (cell.getCellTypeEnum() == CellType.STRING) {
            return StringUtils.trimToEmpty(cell.getStringCellValue());
        } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
            return StringUtils.trimToEmpty(cell.getCellFormula());
        } else if (cell.getCellTypeEnum() == CellType.BLANK) {
            return "";
        } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellTypeEnum() == CellType.ERROR) {
            return "ERROR";
        } else {
            return cell.toString().trim();
        }

    }
}

首先我们在创建处理器对象时，需要传入两个参数，一个是，需要合并的开始行下标，另一个是需要合并的列下标数组

这块代码是用来处理，我们需要合并这个列，但是我们只想合并列，不想合并行

 

这块代码的意思就是，如果两行数据，他们第一列序号一样的话，就会把我们传入的列进行合并，具体合并的列和行，我们在实际开发中自行更改

以下附上表格导出基本样式：

    /**
     * 创建基础样式
     * @return WriteCellStyle 基础样式
     */
    private static WriteCellStyle createBaseStyle() {
        WriteCellStyle writeCellStyle = new WriteCellStyle();

        // 设置边框
        writeCellStyle.setBorderBottom(BorderStyle.THIN);
        writeCellStyle.setBottomBorderColor((short) 0);
        writeCellStyle.setBorderLeft(BorderStyle.THIN);
        writeCellStyle.setLeftBorderColor((short) 0);
        writeCellStyle.setBorderRight(BorderStyle.THIN);
        writeCellStyle.setRightBorderColor((short) 0);
        writeCellStyle.setBorderTop(BorderStyle.THIN);
        writeCellStyle.setTopBorderColor((short) 0);

        // 设置对齐方式
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置其他样式属性
        writeCellStyle.setWrapped(true);
        writeCellStyle.setShrinkToFit(true);

        writeCellStyle.setFillPatternType(FillPatternType.NO_FILL);

        return writeCellStyle;
    }

 具体使用的话，我这里给出案例

        WriteSheet build = EasyExcel.writerSheet("sheet1")
//                .registerWriteHandler(new MyExcelHandler())
                .registerWriteHandler(new ExcelFillCellMergeStrategy(5, new int[]{0,1,2,6,7}))
                .registerWriteHandler(new HorizontalCellStyleStrategy(new WriteCellStyle(),createBaseStyle())).build();