当前位置： 首页 > java >正文

EasyExcel复杂Excel导出

java 2025/7/19 4:35:26

效果图展示

在这里插入图片描述

1、引入依赖

<!-- easyExcel -->
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>4.0.2</version>
</dependency>

2、实体类

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;/*** @Author: Harris* @Date: 2025/5/29* @Description:**/
@Data
public class AbroadDeptCommBo {@ApiModelProperty(value = "单位名称")@ExcelProperty(value = {"单位名称", "单位名称", "单位名称"})private String unitName;@ApiModelProperty(value = "线路类型-国际")@ExcelProperty(value = {"通信情况", "线路情况", "线路类型"})private String lineTypeInternational;@ApiModelProperty(value = "速率-国际")@ExcelProperty(value = {"通信情况", "线路情况", "速率"})private String rateInternational;@ApiModelProperty(value = "数量-国际")@ExcelProperty(value = {"通信情况", "线路情况", "数量"})private String quantityInternational;@ApiModelProperty(value = "备份方式-国际")@ExcelProperty(value = {"通信情况", "线路情况", "备份方式"})private String backupTypeInternational;@ApiModelProperty(value = "数量-国际")@ExcelProperty(value = {"通信情况", "站数", "站数"})private String cmacastReceiveNumInternational;@ApiModelProperty(value = "线路类型-国内")@ExcelProperty(value = {"通信情况", "线路情况", "线路类型"})private String lineTypeDomestic;@ApiModelProperty(value = "速率-国内")@ExcelProperty(value = {"通信情况", "线路情况", "速率"})private String rateDomestic;@ApiModelProperty(value = "数量-国内")@ExcelProperty(value = {"通信情况", "线路情况", "数量"})private String quantityDomestic;@ApiModelProperty(value = "备份方式-国内")@ExcelProperty(value = {"通信情况", "线路情况", "备份方式"})private String backupTypeDomestic;@ApiModelProperty(value = "站数量-国内")@ExcelProperty(value = {"通信情况", "站数", "站数"})private String cmacastReceiveNumDomestic;
}

3、excel 生成

@Testpublic void exportExcel() {String fileName = "./data/tmp/out.xlsx";WriteCellStyle writeCellStyle = getWriteCellStyle();//头策略使用默认WriteCellStyle headWriteCellStyle = new WriteCellStyle();List<AbroadDeptCommBo> dataList = new ArrayList<>(10);dataList.add(initData("中心"));dataList.add(initData("中心"));dataList.add(initData("中心"));dataList.add(initData("集团"));dataList.add(initData("集团"));dataList.add(initData("集团"));try (ExcelWriter build = EasyExcel.write(fileName).build()) {WriteSheet sheet0 = EasyExcel.writerSheet(0, "sheet0").head(AbroadDeptCommBo.class)//设置拦截器或自定义样式.registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, writeCellStyle)).registerWriteHandler(new ExcelMergeHandler(3, new int[]{0})).useDefaultStyle(true).build();build.write(dataList, sheet0);WriteSheet sheet1 = EasyExcel.writerSheet(1, "sheet1").head(AbroadDeptCommBo.class)//设置拦截器或自定义样式.registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, writeCellStyle)).registerWriteHandler(new ExcelMergeHandler(3, new int[]{0})).useDefaultStyle(true).build();build.write(dataList, sheet1);build.finish();} catch (Exception e) {// TODO catch block}}/*** 单元格样式设置* * @return WriteCellStyle*/private static WriteCellStyle getWriteCellStyle() {WriteCellStyle writeCellStyle = new WriteCellStyle();writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);writeCellStyle.setBorderLeft(BorderStyle.THIN);writeCellStyle.setBorderTop(BorderStyle.THIN);writeCellStyle.setBorderRight(BorderStyle.THIN);writeCellStyle.setBorderBottom(BorderStyle.THIN);//设置 自动换行writeCellStyle.setWrapped(true);// 字体策略WriteFont contentWriteFont = new WriteFont();// 字体大小contentWriteFont.setFontHeightInPoints((short) 12);writeCellStyle.setWriteFont(contentWriteFont);return writeCellStyle;}/*** 初始化数据* * @param unitName 单位名称* @return AbroadDeptCommBo*/private static AbroadDeptCommBo initData(String unitName) {AbroadDeptCommBo abroadDeptCommBo = new AbroadDeptCommBo();abroadDeptCommBo.setUnitName(unitName);abroadDeptCommBo.setLineTypeInternational("1");abroadDeptCommBo.setRateInternational("1");abroadDeptCommBo.setQuantityInternational("1");abroadDeptCommBo.setBackupTypeInternational("1");abroadDeptCommBo.setCmacastReceiveNumInternational("1");abroadDeptCommBo.setLineTypeDomestic("1");abroadDeptCommBo.setRateDomestic("1");abroadDeptCommBo.setQuantityDomestic("1");abroadDeptCommBo.setBackupTypeDomestic("1");abroadDeptCommBo.setCmacastReceiveNumDomestic("1");return abroadDeptCommBo;}

合并单元格拦截器（纵向合并）


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.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;/*** @Author: Harris* @Date: 2025/5/30* @Description:**/
public class ExcelMergeHandler implements CellWriteHandler {// 要合并的列索引数组private final int[] mergeColumnIndex;// 合并开始的行索引private final int mergeRowIndex;/*** 构造函数** @param mergeRowIndex     合并开始的行索引* @param mergeColumnIndex  要合并的列索引数组*/public ExcelMergeHandler(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {// 单元格创建前的处理（这里不需要处理）}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {// 单元格创建后的处理（这里不需要处理）}@Overridepublic 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 (curRowIndex > mergeRowIndex && isMergeColumn(curColIndex)) {// 进行合并操作mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);}}/*** 检查当前列是否在需要合并的列中** @param curColIndex 当前列索引* @return 如果是需要合并的列返回true，否则返回false*/private boolean isMergeColumn(int curColIndex) {for (int columnIndex : mergeColumnIndex) {if (curColIndex == columnIndex) {return true;}}return false;}/*** 当前单元格向上合并** @param writeSheetHolder 当前工作表持有者* @param cell             当前单元格* @param curRowIndex      当前行索引* @param curColIndex      当前列索引*/private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {// 获取当前单元格的数据Object curData = getCellData(cell);// 获取前一个单元格的数据Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);Object preData = getCellData(preCell);// 判断当前单元格和前一个单元格的数据以及主键是否相同if (curData.equals(preData) && isSamePrimaryKey(cell, curRowIndex)) {// 获取工作表Sheet sheet = writeSheetHolder.getSheet();// 合并单元格mergeCells(sheet, curRowIndex, curColIndex);}}/*** 获取单元格的数据** @param cell 单元格* @return 单元格数据*/private Object getCellData(Cell cell) {return cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();}/*** 判断当前单元格和前一个单元格的主键是否相同** @param cell         当前单元格* @param curRowIndex  当前行索引* @return 如果主键相同返回true，否则返回false*/private boolean isSamePrimaryKey(Cell cell, int curRowIndex) {String currentPrimaryKey = cell.getRow().getCell(0).getStringCellValue();String previousPrimaryKey = cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue();return currentPrimaryKey.equals(previousPrimaryKey);}/*** 合并单元格** @param sheet        工作表* @param curRowIndex  当前行索引* @param curColIndex  当前列索引*/private void mergeCells(Sheet sheet, int curRowIndex, int curColIndex) {// 获取已合并的区域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);}}
}

最主要的方法其实就是下面这段代码，可以通过这段合并任意单元格

CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);

参考文档：
https://www.cnblogs.com/better-farther-world2099/articles/16106085.html
https://blog.csdn.net/ManGooo0/article/details/128094925

http://www.xdnf.cn/news/9994.html

相关文章：

  • 测试用例篇章
  • C语言创意编程：用趣味实例玩转基础语法（4）
  • CIO大会， AI课笔记手稿分享
  • VScode ios 模拟器安装cocoapods
  • Java Spring Boot 自定义注解详解与实践
  • `docker commit` 和 `docker save`区别
  • 每日c/c++题 备战蓝桥杯（P1011 [NOIP 1998 提高组] 车站）
  • 论文速读《UAV-Flow Colosseo: 自然语言控制无人机系统》
  • If possible, you should set the Secure flag for these cookies 修复方案
  • 操作系统原理第8章：文件管理 重点内容
  • 2025.05.30【转录组】|Ribo-seq数据流程详解（一 质量控制）
  • split_conversion将json转成yolo训练用的txt,在直接按照8：1：1的比例分成训练集，测试集，验证集
  • RuoYi前后端分离框架集成手机短信验证码（二）之前端篇
  • 学习vue3阶段性复习（插槽，Pinia,生命周期）
  • VSCode+Cline 安装配置及使用说明
  • vue+threeJs 绘制3D圆形
  • Linux 的主要时钟类型
  • 菜鸟之路Day36一一Web开发综合案例（部门管理）
  • ARXML解析与可视化工具
  • 硬件学习笔记--64 MCU的ARM核架构发展及特点
  • CentOS 7 环境中部署 LNMP（Linux + Nginx + MySQL 5.7 + PHP）
  • AI科技前沿动态：5.26 - 5.30 一周速览
  • Jetson Orin Nano - SONY imx415 camera驱动开发
  • 2025年5月24号高项综合知识真题以及答案解析(第1批次)
  • redis未授权(CVE-2022-0543)
  • Jvm 元空间大小分配原则
  • LeetCode 高频 SQL 50 题（基础版）之 【连接】部分 · 下
  • SolidWorks 文件打开时电脑卡顿问题分析与解决
  • 脱发因素机器学习数据分析
  • Windows10下使用QEMU安装Ubuntu20.04虚拟机，并启用硬件加速