Java使用EasyExcel自定义合并（横纵合并）、自定义行高列宽、自适应行高列宽工具Excel导出

echola_mendes

已于 2024-07-30 15:42:28 修改

阅读量539

点赞数 19

分类专栏： Excel 工具类文章标签： java 开发语言

于 2024-07-30 14:00:42 首次发布

本文链接：https://blog.csdn.net/qq_36451127/article/details/140787999

版权

Excel 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

工具类

2 篇文章 0 订阅

订阅专栏

最近又开始写Excel导出的业务，之前写的自适应行高列宽工具类并不满足现下的需求

需求是导出一份测试报告Excel：

从上图看出需要横向合并、纵向合并、自定列宽、自定义行高以及边框、换行、字体等自定义样式等等基本功能

整理了几个开箱即用的工具类使用方式，以及踩过坑的注意事项

一、自适应行高列宽工具类

主要是让单元格根据Excel中内容自适应对应的行高和列宽

1、自适应行高

public class CustomCellWriteHeightStrategy extends AbstractRowHeightStyleStrategy {
    /**
     * 默认高度
     */
    private static final Integer DEFAULT_HEIGHT = 350;

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        Iterator<Cell> cellIterator = row.cellIterator();
        if (!cellIterator.hasNext()) {
            return;
        }
        // 默认为 1行高度
        int maxHeight = 1;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getCellTypeEnum() == CellType.STRING) {
                String value = cell.getStringCellValue();
                int len = value.length();
                int num = 0;
                if (len > 50) {
                    num = len % 50 > 0 ? len / 50 : len / 2 - 1;
                }
                if (num > 0) {
                    for (int i = 0; i < num; i++) {
                        value = value.substring(0, (i + 1) * 50 + i) + "\n" + value.substring((i + 1) * 50 + i, len + i);
                    }
                }
                if (value.contains("\n")) {
                    int length = value.split("\n").length;
                    maxHeight = Math.max(maxHeight, length) + 1;
                }
            }
        }
        row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT));
    }
}

使用方式

WriteSheet sheet = EasyExcel.writerSheet(1, sheetName).head(headList)
                //自适应行高
                .registerWriteHandler(new CustomCellWriteHeightStrategy()) 
                .build();

2、自适应列宽

public class CustomCellWriteWidthStrategy extends AbstractColumnWidthStyleStrategy {
    private static final int MAX_COLUMN_WIDTH = 60;
    private static final int BASE_COLUMN_WIDTH = 256;
    private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            // 单元格文本长度大于60换行
            if (columnWidth >= 0) {
                if (columnWidth > MAX_COLUMN_WIDTH) {
                    columnWidth = MAX_COLUMN_WIDTH;
                }
                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    Sheet sheet = writeSheetHolder.getSheet();
                    sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * BASE_COLUMN_WIDTH);
                }
            }
        }
    }

    /**
     * 计算长度
     *
     * @param cellDataList
     * @param cell
     * @param isHead
     * @return
     */
    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (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:
                        // 换行符（数据需要提前解析好）
                        int index = cellData.getStringValue().indexOf("\n");
                        return index != -1 ?
                                cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

使用方式：

WriteSheet sheet = EasyExcel.writerSheet(1, sheetName).head(headList)
                //自适应列宽
                .registerWriteHandler(new CustomCellWriteWidthStrategy())
                .build();

二、自定义行高列宽工具类

已经有了自适应行高列宽工具，那为什么还需要自定义行高列宽呢？

自适应行高列宽工具只能根据单元格中的文本内容做自适应，而且会受上下单元格的影响，效果有时候不尽如人意。

例如14行客户签章，需要盖章的地方肯定需要留足够的空间大小，14行单元格的宽度相当于5行单元格。可以合并5行，也可以定义14行的单元格的行高，那肯定是自定义行高方便

4、5行的序号的列宽肯定是需要小于改造前后的列宽等等

1、自定义行高

public class CustomRowHeightStrategy extends AbstractRowHeightStyleStrategy {

    private final Map<Integer, Integer> rowHeights;

    public CustomRowHeightStrategy(Map<Integer, Integer> rowHeights) {
        this.rowHeights = rowHeights;
    }

    @Override
    protected void setHeadColumnHeight(Row row, int i) {
        //默认表头高度
        row.setHeightInPoints((30));
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        //默认内容高度
        Integer height = rowHeights.get(relativeRowIndex);
        if (height != null) {
            row.setHeightInPoints(height);
        }
    }
}

这个工具类中setHeadColumnHeight是设置表头行高默认高度为30，setContentColumnHeight是设置内容行高的高度，rowHeights是自定义的行高，可进行传参进行，如果设置某一行的高度则 row.setHeightInPoints(height)否则默认原始内容的行高

使用方式：

场景：设置第13行的行高为90，行的序列号是从内容开始，不包括前3行，所以13行的序列号为10

//设置Excel内容第11行的行高为90
Map<Integer, Integer> rowHeightMap = new HashMap<>();
        rowHeightMap.put(10, 90);

WriteSheet sheet = EasyExcel.writerSheet(1, sheetName).head(headList)
                //自适应行高
                .registerWriteHandler(new CustomCellWriteHeightStrategy())
                //自定义行高
                .registerWriteHandler(new CustomRowHeightStrategy(rowHeightMap)) 
                .build();

注意事项：行的序号是从表头和内容的，当前Excel表头3行，内容11行，都是从0开始，使用自定义行高工具类时需要使用自适应行高工具类，不然其他未设置行高的单元行会是原始单元行宽度

2、自定义列宽

public class CustomColumnWidthStrategy extends AbstractHeadColumnWidthStyleStrategy {
    private final Map<Integer, Integer> columnWidths;

    public CustomColumnWidthStrategy(Map<Integer, Integer> columnWidths) {
        this.columnWidths = columnWidths;
    }


    @Override
    protected Integer columnWidth(Head head, Integer columnIndex) {
        if (columnWidths.get(columnIndex) != null) {
            return columnWidths.get(columnIndex);
        }
        //默认列宽15
        return 15;
    }
    
}

原理和自定义行高一样，未设置自定义列宽，则默认列宽为15

使用方式：

场景：设置第4列改造前和第5列改造后列宽50，其他列默认15

//设置Excel第4、5行的列宽为90
Map<Integer, Integer> colWidthMap = new HashMap<>();
        colWidthMap.put(3, 50);
        colWidthMap.put(4, 50);
WriteSheet sheet = EasyExcel.writerSheet(1, sheetName).head(headList)
                //自适应列宽
                //.registerWriteHandler(new CustomCellWriteWidthStrategy())
                //自定义列宽
                .registerWriteHandler(new CustomColumnWidthStrategy(colWidthMap)) 
                .build();

注意事项：列的序号是从0开始，使用了自定义列宽工具类不能使用自适应列宽工具类，会影响自定义列宽的效果，具体视效果而选择对应的工具类

三、自定义合并工具类

在Excel如果横向内容一样，Excel会自定合并单元格，但上下内容一致或相邻内容不一致时，Excel是不会自行合并，因此就需要自定义合并工具类

public class CustomCellMergeStrategy implements CellWriteHandler {

    //横向合并
    private int[] mergeRowIndexRange;
    private int mergeColumnIndexStart;
    private int mergeColumnIndexEnd;
    //纵向合并
    private int[] mergeColumnIndexRange;
    private int mergeRowIndexStart;
    private int mergeRowIndexEnd;

    public CustomCellMergeStrategy() {
    }

    /**
     * 纵向合并入参
     *
     * @param mergeRowIndexStart 开始合并的行索引
     * @param mergeRowIndexEnd   结束合并的行索引
     * @param mergeColumnIndex   要合并的列索引数组
     */
    public CustomCellMergeStrategy(int mergeRowIndexStart, int mergeRowIndexEnd, int[] mergeColumnIndex) {
        this.mergeRowIndexStart = mergeRowIndexStart;
        this.mergeRowIndexEnd = mergeRowIndexEnd;
        this.mergeColumnIndexRange = mergeColumnIndex;
    }


    /**
     * 横向合并入参
     *
     * @param mergeRowIndexStart    开始合并的行索引
     * @param mergeRowIndexEnd      结束合并的行索引
     * @param mergeColumnIndexStart 开始合并的列索引
     * @param mergeColumnIndexEnd   结束合并的列索引
     */
    public CustomCellMergeStrategy(int mergeRowIndexStart, int mergeRowIndexEnd, int mergeColumnIndexStart, int mergeColumnIndexEnd) {
        this.mergeRowIndexRange = new int[]{mergeRowIndexStart, mergeRowIndexEnd};
        this.mergeColumnIndexStart = mergeColumnIndexStart;
        this.mergeColumnIndexEnd = mergeColumnIndexEnd;
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 当前行索引
        int curRowIndex = cell.getRowIndex();
        // 当前列索引
        int curColIndex = cell.getColumnIndex();
        //横向合并
        if (mergeRowIndexRange != null && curRowIndex >= mergeRowIndexRange[0] && curRowIndex <= mergeRowIndexRange[1]) {
            if (curColIndex >= mergeColumnIndexStart && curColIndex <= mergeColumnIndexEnd) {
                mergeHorizontally(writeSheetHolder, cell, curRowIndex, curColIndex);
            }
        }
        //纵向合并
        if (mergeColumnIndexRange != null && mergeRowIndexStart != -1 && curRowIndex >= mergeRowIndexStart && curRowIndex <= mergeRowIndexEnd) {
            for (int columnIndex : mergeColumnIndexRange) {
                if (curColIndex == columnIndex) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }

    }

    /**
     * 当前单元格纵向合并
     *
     * @param writeSheetHolder 当前工作表持有者
     * @param cell             当前单元格
     * @param curRowIndex      当前行索引
     * @param curColIndex      当前列索引
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        // 获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        // 获取前一个单元格的数据
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        if (preCell != null) {
            Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
            if (curData.equals(preData)) {
                // 获取工作表
                Sheet sheet = writeSheetHolder.getSheet();
                // 获取已合并的区域
                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);
                        isMerged = true;
                    }

                }
                // 如果前一个单元格未被合并，则新增合并区域
                if (!isMerged) {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                    sheet.addMergedRegion(cellRangeAddress);
                }
            }
        }
    }


    private void mergeHorizontally(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Sheet sheet = writeSheetHolder.getSheet();
        // 移除与新合并区域可能重叠的所有现有合并区域
        removeOverlappingMerges(sheet, new CellRangeAddress(curRowIndex, curRowIndex, mergeColumnIndexStart, mergeColumnIndexEnd));
        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, curColIndex)) {
                sheet.removeMergedRegion(i);
                cellRangeAddr.setLastColumn(curColIndex);
                sheet.addMergedRegion(cellRangeAddr);
                isMerged = true;
            }
        }

        if (!isMerged) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, mergeColumnIndexStart, mergeColumnIndexEnd);
            sheet.addMergedRegion(cellRangeAddress);
        }
    }

    private void removeOverlappingMerges(Sheet sheet, CellRangeAddress newMergeArea) {
        List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
        List<Integer> regionsToRemove = new ArrayList<>();

        for (int i = 0; i < mergeRegions.size(); i++) {
            CellRangeAddress existingMerge = mergeRegions.get(i);
            if (isOverlapping(newMergeArea, existingMerge)) {
                regionsToRemove.add(i);
            }
        }

        for (int i = regionsToRemove.size() - 1; i >= 0; i--) {
            sheet.removeMergedRegion(regionsToRemove.get(i));
        }
    }

    /**
     * 判断两个合并区域是否重叠
     */
    private boolean isOverlapping(CellRangeAddress area1, CellRangeAddress area2) {
        return !(area1.getFirstRow() > area2.getLastRow() ||
                area1.getLastRow() < area2.getFirstRow() ||
                area1.getFirstColumn() > area2.getLastColumn() ||
                area1.getLastColumn() < area2.getFirstColumn());
    }
    
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

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

    }
}

使用方式：

场景：从1-3行的单元格需横向合并为一行

则代码如下：

//从第1-3行、1-5列开始合并
WriteSheet sheet = EasyExcel.writerSheet(1, sheetName).head(headList)
                .registerWriteHandler(new CustomCellMergeStrategy(0, 2, 0, 4)) 
                .build();

场景：从4-5行的单元格的序号、测试内容、单位单元格需纵向合并为一列

//从第1-3列
int[] mergeColumnIndex = {0, 1, 2};
WriteSheet sheet = EasyExcel.writerSheet(1, sheetName).head(headList)
               .registerWriteHandler(new CustomCellMergeStrategy(3, 4, mergeColumnIndex))
                .build();

横向合并和纵向合并只是传入参数不同，可以将以上自定义合并工具类拆分成两个工具类：自定义横向合并工具类和自定义纵向合并工具类，懒得拆分，只是纵向合并看起不太美观

四、自定义样式

可以看Excel的表头是没有自带的背景颜色，且只有首行字体大，有些单元格居中、靠左、靠上、黑色边框的自定义样式

首先需要去除Excel表头的默认样式，因为/后面还有一个拦截器 FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到cell里面去，会导致样式与自定义的样式设置的不一样，清空默认样式

WriteSheet sheet = EasyExcel.writerSheet(1, sheetName).head(headList)
               .registerWriteHandler(new CellWriteHandler() {
                        @Override
                        public void afterCellDispose(CellWriteHandlerContext context) {
                            context.getFirstCellData().setWriteCellStyle(null);
                        }
                    })
                .build();

然后就可以在这个处理器中实现自定义的样式，以当前Excel为列

WriteSheet sheet = EasyExcel.writerSheet(1, sheetName).head(headList)
               .registerWriteHandler(new CellWriteHandler() {
                    @Override
                    public void afterCellDispose(CellWriteHandlerContext context) {
                        context.getFirstCellData().setWriteCellStyle(null);
                        Cell cell = context.getCell();
                        Workbook workbook = cell.getSheet().getWorkbook();
                        CellStyle cellStyle = workbook.createCellStyle();
                        //内容换行
                        cellStyle.setWrapText(true);
                        //水平居中
                        cellStyle.setAlignment(HorizontalAlignment.CENTER);
                        //垂直居中
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                        int rowIndex = cell.getRowIndex();
                        if (rowIndex == 0) {
                            //第1行设置字体
                            setFont(cellStyle, workbook);
                        }
                        //第2、3、12……行水平靠左
                        if (rowIndex == 1 || rowIndex == 2 || rowIndex >= 12) {
                            cellStyle.setAlignment(HorizontalAlignment.LEFT);
                        }
                        //第14行垂直靠上
                        if (rowIndex == 13) {
                            setFont(cellStyle, workbook);
                            cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
                        }
                        //从第3行开始添加黑色边框
                        if (rowIndex > 2) {
                            cellStyle.setBorderTop(BorderStyle.THIN);
                            cellStyle.setBorderBottom(BorderStyle.THIN);
                            cellStyle.setBorderLeft(BorderStyle.THIN);
                            cellStyle.setBorderRight(BorderStyle.THIN);
                        }
                        //添加自定义样式
                        cell.setCellStyle(cellStyle);
                    }
                })
                .build();

五、实现Excel的完整代码

实现该Excel的完整代码，仅供参考：

public void downUseEnergyComparedTime(UseEnergyComparedTimeDTO param, HttpServletResponse response) throws IOException {
        UseEnergyComparedTimeVO vo = this.useEnergyComparedTime(param);
        String fileName = "测试报告" + DateUtil.format(new Date(), DatePattern.PURE_DATE_PATTERN);
        try {
            EasyExcelUtils.setResponse(response, fileName);
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            String reportSheetName = "测试报告";
            List<List<String>> reportHeadList = new ArrayList<>();
            List<List<String>> reportDataList = new ArrayList<>();
            getReportHeadList(reportHeadList, vo);
            getReportDataList(reportDataList, vo, param);
            WriteSheet reportSheet = reportSheet(reportSheetName, reportHeadList);
            excelWriter.write(reportDataList, reportSheet);
            excelWriter.finish();
        } catch (Exception e) {
            log.error("导出统计异常", e.getMessage());
        }
    }

/**
 * 测试报告表头
 */
private void getReportHeadList(List<List<String>> headList, UseEnergyComparedTimeVO vo) {
        headList.add(Arrays.asList("空调节能项目测试报告", "客户名称：                                              测试日期：" + DateUtil.format(vo.getEarliestTime(), DatePattern.CHINESE_DATE_PATTERN) + "-" + DateUtil.format(vo.getLatestTime(), DatePattern.CHINESE_DATE_PATTERN), "地址：                                                      联系人及联系方式："));
    }

/**
 * 测试报告数据
 */
private void getReportDataList(List<List<String>> dataList, UseEnergyComparedTimeVO vo, UseEnergyComparedTimeDTO param) {
        List<UseEnergyComparedTimeVO.DeviceDate> deviceList = vo.getList();
        UseEnergyComparedTimeVO.DeviceDate beforeData = null;
        UseEnergyComparedTimeVO.DeviceDate afterData = null;
        if (CollectionUtil.isNotEmpty(deviceList)) {
            beforeData = deviceList.stream().findFirst().orElse(null);
            afterData = deviceList.stream().reduce((first, second) -> second).orElse(null);
        }
        List<String> timeList = new ArrayList<>();
        param.getComparisonTimes().forEach(e -> {
            AtomicReference<String> timeStr = new AtomicReference<>();
            e.getPeriodList().forEach(time -> {
                String period = DateUtil.format(time.getStartTime(), DatePattern.NORM_DATETIME_PATTERN) + "-" + DateUtil.format(time.getEndTime(), DatePattern.NORM_DATETIME_PATTERN);
                timeStr.set(timeStr.get() == null ? period : timeStr.get() + "\n" + period);
            });
            timeList.add(timeStr.get());
        });
        if (beforeData != null) {
            dataList.add(Arrays.asList("序号", "测试内容", "单位", vo.getMeasuringArea(), "B位置"));
            dataList.add(Arrays.asList("序号", "测试内容", "单位", "改造前", "改造后"));
            dataList.add(Arrays.asList("1", "测试A", "/", timeList.stream().findFirst().orElse(""), timeList.stream().reduce((first, second) -> second).orElse("")));
            dataList.add(Arrays.asList("2", "测试B", "小时", beforeData.getRunTime().stripTrailingZeros().toPlainString(), afterData.getRunTime().stripTrailingZeros().toPlainString()));
            dataList.add(Arrays.asList("3", "测试C", "小时", beforeData.getStartValue().stripTrailingZeros().toPlainString(), afterData.getStartValue().stripTrailingZeros().toPlainString()));
            dataList.add(Arrays.asList("4", "测试D", "小时", beforeData.getEndValue().stripTrailingZeros().toPlainStrCing(), afterData.getEndValue().stripTrailingZeros().toPlainString()));
            dataList.add(Arrays.asList("5", "测试E", "小时", beforeData.getAllValue().stripTrailingZeros().toPlainString(), afterData.getAllValue().stripTrailingZeros().toPlainString()));
            dataList.add(Arrays.asList("6", "测试F", "小时", beforeData.getAvgValue().stripTrailingZeros().toPlainString(), afterData.getAvgValue().stripTrailingZeros().toPlainString()));
            dataList.add(Arrays.asList("7", "测试J", "小时", param.getComputeType().equals(CommonKeys.ZERO) ? vo.getAvgEnergySavingRate().stripTrailingZeros().toPlainString() : vo.getAllEnergySavingRate().stripTrailingZeros().toPlainString(), ""));
            dataList.add(Arrays.asList("备注：测试方式：", "", "", "", ""));
            dataList.add(Arrays.asList("客户签章（公章）：", "", "", "", "日期： "));
        }
}

/**
 * 测试报告Sheet
 */
private WriteSheet reportSheet(String sheetName, List<List<String>> headList) {
        int[] mergeColumnIndex = {0, 1, 2};
        Map<Integer, Integer> colWidthMap = new HashMap<>();
        colWidthMap.put(3, 50);
        colWidthMap.put(4, 50);
        Map<Integer, Integer> rowHeightMap = new HashMap<>();
        rowHeightMap.put(10, 90);
        WriteSheet sheet = EasyExcel.writerSheet(1, sheetName).head(headList)
                //.registerWriteHandler(new CustomCellWriteWidthStrategy())
                .registerWriteHandler(new CustomCellWriteHeightStrategy())
                .registerWriteHandler(new CustomRowHeightStrategy(rowHeightMap)) 
                .registerWriteHandler(new CustomColumnWidthStrategy(colWidthMap)) 
                .registerWriteHandler(new CustomCellMergeStrategy(0, 2, 0, 4))
                .registerWriteHandler(new CustomCellMergeStrategy(3, 3, 3, 4))
                .registerWriteHandler(new CustomCellMergeStrategy(11, 11, 3, 4))
                .registerWriteHandler(new CustomCellMergeStrategy(12, 12, 0, 4))
                .registerWriteHandler(new CustomCellMergeStrategy(13, 13, 0, 3))
                .registerWriteHandler(new CustomCellMergeStrategy(3, 4, mergeColumnIndex))
                .registerWriteHandler(new CellWriteHandler() {
                    @Override
                    public void afterCellDispose(CellWriteHandlerContext context) {
                        context.getFirstCellData().setWriteCellStyle(null);
                        Cell cell = context.getCell();
                        Workbook workbook = cell.getSheet().getWorkbook();
                        CellStyle cellStyle = workbook.createCellStyle();
                        cellStyle.setWrapText(true);
                        cellStyle.setAlignment(HorizontalAlignment.CENTER);
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                        int rowIndex = cell.getRowIndex();
                        if (rowIndex == 0) {
                            setFont(cellStyle, workbook);
                        }
                        if (rowIndex == 1 || rowIndex == 2 || rowIndex >= 12) {
                            cellStyle.setAlignment(HorizontalAlignment.LEFT);
                        }
                        if (rowIndex == 13) {
                            setFont(cellStyle, workbook);
                            cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
                        }
                        if (rowIndex > 2) {
                            cellStyle.setBorderTop(BorderStyle.THIN);
                            cellStyle.setBorderBottom(BorderStyle.THIN);
                            cellStyle.setBorderLeft(BorderStyle.THIN);
                            cellStyle.setBorderRight(BorderStyle.THIN);
                        }
                        cell.setCellStyle(cellStyle);
                    }
                })
                .build();
        return sheet;
}

/**
 * 设置单元格字体样式
 */
private CellStyle createTitleCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 14); // 设置字体大小
        cellStyle.setFont(font);
        return cellStyle;
}

echola_mendes

关注

19
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Java使用EasyExcel自定义合并（横纵合并）、自定义行高列宽、自适应行高列宽工具Excel导出

之前有些过自适应行高列宽工具，在往期的文章里：那为什么还需要自定义列宽呢？自适应行高列宽工具只能根据单元格中的文本内容做自适应，而且会受上下单元格的影响，效果有时候不尽如人意。例如14行客户签章，需要盖章的地方肯定需要留足够的空间大小，14行单元格的宽度相当于5行单元格。可以合并5行，也可以定义14行的单元格的行高，那肯定是自定义行高方便4、5行的序号的列宽肯定是需要小于改造前后的列宽等等@Override//默认表头高度@Override//默认内容高度if (height!
复制链接

扫一扫

专栏目录