EasyExcel策略

合并策略

public class ExcelMergeStrategy extends AbstractMergeStrategy {
    /**
     * 合并的列编号，从0开始，指定的index或自己按字段顺序数
     */
    private Set<Integer> mergeCellIndex = new HashSet<>();
    /**
     * 数据集大小，用于区别结束行位置
     */
    private Integer maxRow = 0;
    /**
     * 禁止无参声明
     */
    private ExcelMergeStrategy() {
    }
    public ExcelMergeStrategy(Integer maxRow, int... mergeCellIndex) {
        Arrays.stream(mergeCellIndex).forEach(item -> {
            this.mergeCellIndex.add(item);
        });
        this.maxRow = maxRow;
    }
    /**
     * 记录上一次合并的信息
     */
    private Map<Integer, MergeRange> lastRow = new HashedMap();
    /**
     * 每行每列都会进入，绝对不要在这写循环
     *
     * @param sheet
     * @param cell
     * @param head
     * @param relativeRowIndex
     */
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        int currentCellIndex = cell.getColumnIndex();
        String prefix = "";
        if (mergeCellIndex.contains(currentCellIndex)) {
            if(currentCellIndex> 0){
                Row row = cell.getRow();
                List<String> prefixList = new CopyOnWriteArrayList<>();
                for (int cellIndex = currentCellIndex -1; cellIndex >= 0; cellIndex--) {
                    prefixList.add(row.getCell(cellIndex).getStringCellValue());
                }
                Collections.reverse(prefixList);
                prefix = String.join("", prefixList);
            }
            // 判断该行是否需要合并
            String currentCellValue = prefix + cell.getStringCellValue();
            int currentRowIndex = cell.getRowIndex();
            if (!lastRow.containsKey(currentCellIndex)) {
                // 记录首行起始位置
                lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
                return;
            }
            //有上行这列的值了，拿来对比.
            MergeRange mergeRange = lastRow.get(currentCellIndex);
            if (!(mergeRange.lastValue != null && (mergeRange.lastValue).equals(currentCellValue))) {
                // 结束的位置触发下合并.
                // 同行同列不能合并，会抛异常
                if (mergeRange.startRow != mergeRange.endRow || mergeRange.startCell != mergeRange.endCell) {
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRange.startRow, mergeRange.endRow, mergeRange.startCell, mergeRange.endCell));
                }
                // 更新当前列起始位置
                lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
            }
            // 合并行 + 1
            mergeRange.endRow += 1;
            // 结束的位置触发下最后一次没完成的合并
            if (relativeRowIndex.equals(maxRow - 1)) {
                MergeRange lastMergeRange = lastRow.get(currentCellIndex);
                // 同行同列不能合并，会抛异常
                if (lastMergeRange.startRow != lastMergeRange.endRow || lastMergeRange.startCell != lastMergeRange.endCell) {
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(lastMergeRange.startRow, lastMergeRange.endRow, lastMergeRange.startCell, lastMergeRange.endCell));
                }
            }
        }
    }
    public class MergeRange {
        public int startRow;
        public int endRow;
        public int startCell;
        public int endCell;
        public String lastValue;
        public MergeRange(String lastValue, int startRow, int endRow, int startCell, int endCell) {
            this.startRow = startRow;
            this.endRow = endRow;
            this.startCell = startCell;
            this.endCell = endCell;
            this.lastValue = lastValue;
        }
    }
}

表头颜色策略

public class HeadTwoColorStyleWriteStrategy extends AbstractCellStyleStrategy {
    private Workbook workbook;
    /**
     * 颜色分割点
     */
    private Integer pointIndex;
    private Integer pointIndex2;
    /**
     * 头的高度
     */
    private Integer headHeight;
    @Override
    protected void initCellStyle(Workbook workbook) {
        this.workbook=workbook;
    }
    public HeadTwoColorStyleWriteStrategy(Integer pointIndex, Integer pointIndex2){
        //设置投高度默认
        this.headHeight = 2;
        this.pointIndex = pointIndex;
        this.pointIndex2 = pointIndex2;
    }
    public HeadTwoColorStyleWriteStrategy(Integer headHeight,Integer pointIndex, Integer pointIndex2){
        this.headHeight = headHeight;
        this.pointIndex = pointIndex;
        this.pointIndex2 = pointIndex2;
    }
    /**
     * 设置表格头样式
     * @param cell
     * @param head
     * @param relativeRowIndex
     */
    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if ( relativeRowIndex < headHeight) {
            int columnIndex = cell.getColumnIndex();
            WriteCellStyle writeCellStyle=new WriteCellStyle();
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setColor(IndexedColors.WHITE.getIndex());
            writeCellStyle.setWriteFont(headWriteFont);
            // WriteCellStyle转换为CellStyle
            CellStyle headCellStyle = StyleUtil.buildHeadCellStyle(workbook, writeCellStyle);
            XSSFCellStyle cellStyle = (XSSFCellStyle)headCellStyle;
            if(columnIndex < pointIndex){
                cellStyle.setFillForegroundColor(getRGBColor(0, 51, 102));
            }else if (columnIndex < pointIndex2){
                cellStyle.setFillForegroundColor(getRGBColor(0, 112, 192));
            }else {
                cellStyle.setFillForegroundColor(getRGBColor(146, 208, 80));
            }
            cell.setCellStyle(cellStyle);
        }
    }
    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
    }
      /**
     * 得到RBG自定义颜色
     *
     * @param redNum   红色数值
     * @param greenNum 绿色数值
     * @param blueNum  蓝色数值
     * @return
     */
    public static XSSFColor getRGBColor(int redNum, int greenNum, int blueNum) {
        XSSFColor color = new XSSFColor(new byte[]{(byte) redNum, (byte) greenNum, (byte) blueNum}, new DefaultIndexedColorMap());
        return color;
    }
}

链接转跳和文本转跳策略

public class HyperlinkExcelStrategy implements CellWriteHandler {
    /**
     * 行
     */
    private Integer row;
    /**
     * 列
     */
    private Integer col;
    /**
     * type 类型 HyperlinkType 参考该枚举
     */
    private HyperlinkType type;
    /**
     * 要替换成该 链接
     */
    private String link;

    /**
     * 第几列添加链接
     * @param col
     * @param link
     * @param type 类型 HyperlinkType.DOCUMENT
     */
    public HyperlinkExcelStrategy(Integer col,String link,HyperlinkType type){
        this.col = col;
        this.link = link;
        this.type = type;
    }
    @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, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (!isHead){
            int columnIndex = cell.getColumnIndex();
            //等于当前列是添加链接 或链接文档
            if (columnIndex== this.col){
                CreationHelper helper=writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
                Hyperlink hyperlink=helper.createHyperlink(type);
                hyperlink.setAddress(this.link);
                cell.setHyperlink(hyperlink);
            }
        }
    }
}

自定义表头宽度策略策略

public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {


    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>(64);

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (Boolean.TRUE.equals(isHead)) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead || !CollectionUtils.isEmpty(cellDataList)) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (CollectionUtils.isEmpty(maxColumnWidthMap)) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }
                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
            }
        }
    }
}