使用EasyExcel合并单元格

原创

wade0622 2024-05-10 10:43:20 博主文章分类：java ©著作权

文章标签 java List apache 文章分类 Java 后端开发 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者wade0622的原创作品，请联系作者获取转载授权，否则将追究法律责任

前言

在此，我想分享一些个人的经验和见解，希望能为同样追求进步的您提供一些启示和帮助。每一篇文章都是我深思熟虑的结果，欢迎您阅读、收藏并关注。在未来的日子里，我会继续分享更多的经验，与您共同探索更多未知的领域。请相信，每一次的分享都是我真挚的心意，期待与您共同成长，共创美好未来。

1、创建实体

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author jhb
 * @desc 合并单元格 实体
 * @date 2024-01-23
 */
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class MergeRange {
    /**
     * 列索引
     */
    private int columnIndex;
    /**
     * 开始行
     */
    private int startRow;
    /**
     * 结束行
     */
    private int endRow;
}

2、编写合并策略类

package net.jhb.utils;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import net.qihoo.mss.model.excel.MergeRange;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

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

/**
 * @author jhb
 * @desc 合并单元格策略类
 * @date 2024-1-23
 */
public class MergeStrategy extends AbstractMergeStrategy {
    /**
     * 合并数据的信息
     */
    private final Map<Integer, List<MergeRange>> mergeRangesMap;

    public MergeStrategy(List<MergeRange> mergeRanges) {
        this.mergeRangesMap = new HashMap<>();

        for (MergeRange mergeRange : mergeRanges) {
            int columnIndex = mergeRange.getColumnIndex();

            if (!mergeRangesMap.containsKey(columnIndex)) {
                mergeRangesMap.put(columnIndex, new ArrayList<>());
            }

            mergeRangesMap.get(columnIndex).add(mergeRange);
        }
    }





    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        int columnIndex = cell.getColumnIndex();
        int rowIndex = cell.getRowIndex();
        // 设置水平居中对齐
        CellStyle cellStyle = cell.getCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cell.setCellStyle(cellStyle);
        if (mergeRangesMap.containsKey(columnIndex)) {
            for (MergeRange mergeRange : mergeRangesMap.get(columnIndex)) {
                int startRow = mergeRange.getStartRow();
                int endRow = mergeRange.getEndRow();

                if (rowIndex >= startRow && rowIndex <= endRow) {
                    // 检查是否与现有的合并区域冲突
                    for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
                        if (mergedRegion.isInRange(rowIndex, columnIndex)) {
                            return; // 如果冲突，则跳过合并操作
                        }
                    }

                    // 取消现有的合并区域
                    for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
                        if (mergedRegion.getFirstColumn() == columnIndex && mergedRegion.getLastRow() >= rowIndex) {
                            sheet.removeMergedRegion(sheet.getMergedRegions().indexOf(mergedRegion));
                        }
                    }

                    // 合并单元格
                    CellRangeAddress newMergedRegion = new CellRangeAddress(startRow, endRow, columnIndex, columnIndex);
                    sheet.addMergedRegion(newMergedRegion);

                    break; // 找到对应的合并范围后，停止继续查找
                }
            }
        }
    }
}

3、编写样式的工具类

package net.jhb.utils;

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

/**
 * @author jhb
 * @desc 合并单元格策略类
 * @date 2024-1-23
 */
public class CellStyleUtil {
    /**
     * 设置头样式
     * @return
     */
    public static WriteCellStyle getHeadStyle() {
        // 创建一个新的 WriteCellStyle 对象
        WriteCellStyle style = new WriteCellStyle();

        // 设置水平居中对齐
        style.setHorizontalAlignment(HorizontalAlignment.CENTER);

        // 设置垂直居中对齐
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        return style;
    }

    /**
     * 设置内容样式
     * @return
     */
    public static WriteCellStyle getContentStyle() {
        // 创建一个新的 WriteCellStyle 对象
        WriteCellStyle style = new WriteCellStyle();

        // 设置水平居中对齐
        style.setHorizontalAlignment(HorizontalAlignment.CENTER);

        // 设置垂直居中对齐
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        return style;
    }
}

4、测试类

package net.jhb.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import net.qihoo.mss.model.excel.MergeRange;
import org.springframework.util.ResourceUtils;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.List;

public class Main {
    public static void main(String[] args) throws IOException {
        File file = ResourceUtils.getFile(ResourceUtils.CLASSPATH_URL_PREFIX + "templates/01-HLWBLM/v3/test.xlsx");
        //
        OutputStream out = Files.newOutputStream(file.toPath());

        // 生成一些测试数据
        List<MergeRange> mergeRanges = new ArrayList<>();
        mergeRanges.add(new MergeRange(0, 0, 3)); // 合并第1列的第1行到第3行
        mergeRanges.add(new MergeRange(0, 4, 7)); // 合并第1列的第5行到第7行
        mergeRanges.add(new MergeRange(0, 8, 12)); // 合并第1列的第9行到第12行
        mergeRanges.add(new MergeRange(1, 0, 3)); // 合并第2列的第0行到第4行
        mergeRanges.add(new MergeRange(1, 4, 7)); // 合并第2列的第6行到第9行

        // 写入Excel文件
        EasyExcel.write(out)
                .sheet("Sheet1")
                .head(TestData.class)
                .registerWriteHandler(new MergeStrategy(mergeRanges))
                .registerWriteHandler(new HorizontalCellStyleStrategy(CellStyleUtil.getHeadStyle(),CellStyleUtil.getContentStyle()))
                .doWrite(generateTestData());

    }



    private static class TestData {
        @ExcelProperty(value = "第一列", index = 0)
        @ColumnWidth(value = 22)
        private Integer col1;
        @ExcelProperty(value = "第二列", index = 1)
        @ColumnWidth(value = 22)
        private Integer col2;
        @ExcelProperty(value = "第三列", index = 2)
        @ColumnWidth(value = 22)
        private Integer col3;

        public TestData(Integer col1, Integer col2, Integer col3) {
            this.col1 = col1;
            this.col2 = col2;
            this.col3 = col3;
        }

        public Integer getCol1() {
            return col1;
        }

        public Integer getCol2() {
            return col2;
        }

        public Integer getCol3() {
            return col3;
        }
    }

    private static List<TestData> generateTestData() {
        List<TestData> testDataList = new ArrayList<>();
        testDataList.add(new TestData(1, 2, 3));
        testDataList.add(new TestData(4, 5, 6));
        testDataList.add(new TestData(7, 8, 9));
        testDataList.add(new TestData(7, 8, 9));
        testDataList.add(new TestData(7, 8, 9));
        testDataList.add(new TestData(7, 8, 9));
        testDataList.add(new TestData(7, 8, 9));
        return testDataList;
    }
}