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

EasyExcel合并相同内容单元格及动态标题功能的实现

news 来源：原创 2024/5/19 3:34:29

一、最初版本
导出的结果：
在这里插入图片描述
对应实体类代码：

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.*;import java.io.Serializable;@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Data
@ContentRowHeight(30)
@HeadRowHeight(40)
@ColumnWidth(25)
public class StudentExportVo implements Serializable {private static final long serialVersionUID = -5809782578272943999L;@ExcelProperty(value = "学校", order = 1)@ContentLoopMerge(eachRow = 3)private String school;@ExcelProperty(value = "姓名", order = 2)private String name;@ExcelProperty(value = "性别", order = 3)private String sex;@ExcelProperty(value = "年龄", order = 4)private String age;@ExcelProperty(value = "城市", order = 5)private String city;@ExcelProperty(value = "备注", order = 6)private String remarks;}

对应业务代码：

@ApiOperation(value = "导出学生信息", notes = "导出学生信息")
@PostMapping("/exportStudent")
public void exportStudent(@RequestBody String str, HttpServletResponse response) {List<StudentExportVo> list = this.getStudentExportVos();ExcelUtils.writeExcel(response, StudentExportVo.class, list, "导出学生信息", "sheet1");
}
//数据制造
private List<StudentExportVo> getStudentExportVos() {List<StudentExportVo> list = new ArrayList<>();StudentExportVo v1 = new StudentExportVo();v1.setSchool("北京大学");v1.setName("张三");v1.setSex("男");v1.setAge("20");v1.setCity("北京");v1.setRemarks("无");list.add(v1);StudentExportVo v2 = new StudentExportVo();v2.setSchool("北京大学");v2.setName("李四");v2.setSex("男");v2.setAge("22");v2.setCity("上海");v2.setRemarks("无");list.add(v2);StudentExportVo v3 = new StudentExportVo();v3.setSchool("北京大学");v3.setName("王五");v3.setSex("女");v3.setAge("22");v3.setCity("青岛");v3.setRemarks("无");list.add(v3);StudentExportVo v4 = new StudentExportVo();v4.setSchool("清华大学");v4.setName("赵六");v4.setSex("女");v4.setAge("21");v4.setCity("重庆");v4.setRemarks("无");list.add(v4);StudentExportVo v5 = new StudentExportVo();v5.setSchool("武汉大学");v5.setName("王强");v5.setSex("男");v5.setAge("24");v5.setCity("长沙");v5.setRemarks("无");list.add(v5);StudentExportVo v6 = new StudentExportVo();v6.setSchool("武汉大学");v6.setName("赵燕");v6.setSex("女");v6.setAge("21");v6.setCity("深圳");v6.setRemarks("无");list.add(v6);StudentExportVo v7 = new StudentExportVo();v7.setSchool("厦门大学");v7.setName("陆仟");v7.setSex("女");v7.setAge("21");v7.setCity("广州");v7.setRemarks("无");list.add(v7);return list;
}

二、使用注解的版本
导出的结果：
在这里插入图片描述
对应实体类代码：

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Data
@ContentRowHeight(30)
@HeadRowHeight(40)
@ColumnWidth(25)
public class StudentExportVo implements Serializable {private static final long serialVersionUID = -5809782578272943999L;@ExcelProperty(value = {"学生信息","学校"}, order = 1)@ContentLoopMerge(eachRow = 3)private String school;@ExcelProperty(value = {"学生信息","姓名"}, order = 2)private String name;@ExcelProperty(value = {"学生信息","性别"}, order = 3)private String sex;@ExcelProperty(value = {"学生信息","年龄"}, order = 4)private String age;@ExcelProperty(value = {"学生信息","城市"}, order = 5)private String city;@ExcelProperty(value = {"学生信息","备注"}, order = 6)private String remarks;}

对应业务代码：同上

@ContentLoopMerge(eachRow = 3) 可以合并单元格，但是他是按指定行数合并，并不能实现内容相同的合并

@ExcelProperty(value = {“学生信息”,“备注”}，能实现多个标题，但标题是固定的，不是动态的

三、自定义改造
导出的结果：
在这里插入图片描述

对应实体类代码：

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Data
@ContentRowHeight(30)
@HeadRowHeight(40)
@ColumnWidth(25)
public class StudentExportVo implements Serializable {private static final long serialVersionUID = -5809782578272943999L;@ExcelProperty(value = {"学生信息","学校"}, order = 1)//@ContentLoopMerge(eachRow = 3)private String school;@ExcelProperty(value = {"学生信息","姓名"}, order = 2)private String name;@ExcelProperty(value = {"学生信息","性别"}, order = 3)private String sex;@ExcelProperty(value = {"学生信息","年龄"}, order = 4)private String age;@ExcelProperty(value = {"学生信息","城市"}, order = 5)private String city;@ExcelProperty(value = {"学生信息","备注"}, order = 6)private String remarks;}

对应业务代码：

@ApiOperation(value = "导出学生信息", notes = "导出学生信息")@PostMapping("/exportStudent")public void exportStudent(@RequestBody String dynamicTitle, HttpServletResponse response) {List<StudentExportVo> list = this.getStudentExportVos();try {String fileName = "学生信息";fileName = URLEncoder.encode(fileName, "UTF-8");response.setContentType("application/json;charset=utf-8");response.setCharacterEncoding("utf-8");response.addHeader("Pargam", "no-cache");response.addHeader("Cache-Control", "no-cache");response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");ServletOutputStream output = response.getOutputStream();//需要合并的列int[] mergeColumeIndex = {0};// 从第二行后开始合并int mergeRowIndex = 2;//设置动态标题List<List<String>> headers = this.getHeaders("学生信息" + dynamicTitle);// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 背景设置为白色headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);/*WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short)20);headWriteCellStyle.setWriteFont(headWriteFont);*/// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定//contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// 背景绿色//contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());//设置 自动换行contentWriteCellStyle.setWrapped(true);//设置 垂直居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置 水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);/*WriteFont contentWriteFont = new WriteFont();// 字体大小contentWriteFont.setFontHeightInPoints((short)20);contentWriteCellStyle.setWriteFont(contentWriteFont);*/// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);EasyExcel.write(output, StudentExportVo.class).sheet("学生信息").head(headers).registerWriteHandler(new ExcelMergeHandler(mergeRowIndex, mergeColumeIndex)).registerWriteHandler(horizontalCellStyleStrategy)
//                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(30)).registerWriteHandler(new AbstractColumnWidthStyleStrategy() {@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {Sheet sheet = writeSheetHolder.getSheet();int columnIndex = cell.getColumnIndex();if(columnIndex == 5){// 列宽100sheet.setColumnWidth(columnIndex, 10000);}else {// 列宽50sheet.setColumnWidth(columnIndex, 5000);}// 行高40sheet.setDefaultRowHeight((short) 4000);}}).doWrite(list);output.flush();} catch (IOException e) {log.error(e.getMessage(), e);}}private List<List<String>> getHeaders(String dynamicTitle) {List<List<String>> headers=new ArrayList<>();List<String> schoolHead=new ArrayList<>();schoolHead.add(dynamicTitle);schoolHead.add("学校");List<String> nameHead=new ArrayList<>();nameHead.add(dynamicTitle);nameHead.add("姓名");List<String> sexHead=new ArrayList<>();sexHead.add(dynamicTitle);sexHead.add("性别");List<String> ageHead=new ArrayList<>();ageHead.add(dynamicTitle);ageHead.add("年龄");List<String> cityHead=new ArrayList<>();cityHead.add(dynamicTitle);cityHead.add("城市");List<String> remarksHead=new ArrayList<>();remarksHead.add(dynamicTitle);remarksHead.add("备注");headers.add(schoolHead);headers.add(nameHead);headers.add(sexHead);headers.add(ageHead);headers.add(cityHead);headers.add(remarksHead);return headers;}//数据制造private List<StudentExportVo> getStudentExportVos() {List<StudentExportVo> list = new ArrayList<>();StudentExportVo v1 = new StudentExportVo();v1.setSchool("北京大学");v1.setName("张三");v1.setSex("男");v1.setAge("20");v1.setCity("北京");v1.setRemarks("无");list.add(v1);StudentExportVo v2 = new StudentExportVo();v2.setSchool("北京大学");v2.setName("李四");v2.setSex("男");v2.setAge("22");v2.setCity("上海");v2.setRemarks("无");list.add(v2);StudentExportVo v3 = new StudentExportVo();v3.setSchool("北京大学");v3.setName("王五");v3.setSex("女");v3.setAge("22");v3.setCity("青岛");v3.setRemarks("无");list.add(v3);StudentExportVo v4 = new StudentExportVo();v4.setSchool("清华大学");v4.setName("赵六");v4.setSex("女");v4.setAge("21");v4.setCity("重庆");v4.setRemarks("无");list.add(v4);StudentExportVo v5 = new StudentExportVo();v5.setSchool("武汉大学");v5.setName("王强");v5.setSex("男");v5.setAge("24");v5.setCity("长沙");v5.setRemarks("无");list.add(v5);StudentExportVo v6 = new StudentExportVo();v6.setSchool("武汉大学");v6.setName("赵燕");v6.setSex("女");v6.setAge("21");v6.setCity("深圳");v6.setRemarks("无");list.add(v6);StudentExportVo v7 = new StudentExportVo();v7.setSchool("厦门大学");v7.setName("陆仟");v7.setSex("女");v7.setAge("21");v7.setCity("广州");v7.setRemarks("无");list.add(v7);return list;}

合并单元格相同内容处理类：ExcelMergeHandler

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
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;
public class ExcelMergeHandler implements CellWriteHandler {private int[] mergeColumnIndex;private int mergeRowIndex;public ExcelMergeHandler() {}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 afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {//当前行int curRowIndex = cell.getRowIndex();//当前列int curColIndex = cell.getColumnIndex();if (curRowIndex > mergeRowIndex) {for (int i = 0; i < mergeColumnIndex.length; i++) {if (curColIndex == mergeColumnIndex[i]) {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);Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();// 将当前单元格数据与上一个单元格数据比较Boolean dataBool = preData.equals(curData);//此处需要注意：因为我是按照工程名称确定是否需要合并的，所以获取每一行第二列数据和上一行第一列数据进行比较，如果相等合并，getCell里面的值，是工程名称所在列的下标String s1 = cell.getRow().getCell(0).getStringCellValue();String s2 = cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue();/*BigDecimal d1 = new BigDecimal(cell.getRow().getCell(0).getNumericCellValue());BigDecimal d2 = new BigDecimal(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getNumericCellValue());*/Boolean bool = s1.compareTo(s2) == 0 ? true:false;// 原始的// Boolean bool =  cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).getStringCellValue());if (dataBool && bool) {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);}}}
}

总结：

1、使用自定义合并相同内容单元格时，实体类中的注解@ContentLoopMerge需要去掉，要不然会受到影响

2、该段自定义代码中如内容合并、设置动态标题、内容、以及每个列的宽度及字体都是可以根据自定义策略来进行设置的，具体用法可以参考代码注释说明，根据需要进行使用

相关文章：

  • 华为云CodeArts Pipeline常见问答汇总
  • 智能优化算法应用：基于学校优化算法3D无线传感器网络(WSN)覆盖优化 - 附代码
  • 【golang】go执行shell命令行的方法（ exec.Command ）
  • 机器人制作开源方案 | 智能落叶清扫机器人
  • 【unity实战】一个通用的FPS枪支不同武器射击控制脚本
  • 如何保证架构的质量
  • Android Uri scheme协议file转content
  • [最后一个月征稿、ACM独立出版】第三届密码学、网络安全和通信技术国际会议（CNSCT 2024）
  • Crocoddyl： 多接触最优控制的高效多功能框架
  • STM32_启动流程详解
  • 【数据库设计和SQL基础语法】--连接与联接--多表查询与子查询基础（一）
  • k8s中ConfigMap、Secret创建使用演示、配置文件存储介绍
  • 面试算法61：和最小的k个数对
  • 保姆级 Keras 实现 YOLO v3 三
  • Linux学习(1)——初识Linux
  • javascript 总结（常用工具类的封装）
  • MD5加密原理解析及OC版原理实现
  • Python连接Oracle
  • Python十分钟制作属于你自己的个性logo
  • React组件设计模式（一）
  • Sublime Text 2/3 绑定Eclipse快捷键
  • WebSocket使用
  • Yii源码解读－服务定位器（Service Locator）
  • 搭建gitbook 和 访问权限认证
  • 缓存与缓冲
  • 驱动程序原理
  • MyCAT水平分库
  • 说说我为什么看好Spring Cloud Alibaba
  • ​sqlite3 --- SQLite 数据库 DB-API 2.0 接口模块​
  • ​软考-高级-信息系统项目管理师教程 第四版【第23章-组织通用管理-思维导图】​
  • ​一、什么是射频识别?二、射频识别系统组成及工作原理三、射频识别系统分类四、RFID与物联网​
  • (二) Windows 下 Sublime Text 3 安装离线插件 Anaconda
  • （附源码）ssm旅游企业财务管理系统 毕业设计 102100
  • （蓝桥杯每日一题）平方末尾及补充（常用的字符串函数功能）
  • （四）Tiki-taka算法（TTA）求解无人机三维路径规划研究（MATLAB）
  • (转)Linux整合apache和tomcat构建Web服务器
  • （转）树状数组
  • .net Application的目录
  • .NET Conf 2023 回顾 – 庆祝社区、创新和 .NET 8 的发布
  • .NET 中使用 TaskCompletionSource 作为线程同步互斥或异步操作的事件
  • .net知识和学习方法系列（二十一）CLR-枚举
  • /deep/和 ＞＞＞以及 ::v-deep 三者的区别
  • /var/log/cvslog 太大
  • @Autowired多个相同类型bean装配问题
  • []AT 指令 收发短信和GPRS上网 SIM508/548
  • [ABC294Ex] K-Coloring
  • [Angular 基础] - 自定义指令，深入学习 directive
  • [BZOJ 4034][HAOI2015]T2 [树链剖分]
  • [BZOJ5250][九省联考2018]秘密袭击(DP)
  • [C#]获取指定文件夹下的所有文件名（递归）
  • [C/C++] -- 二叉树
  • [C++]命名空间等——喵喵要吃C嘎嘎
  • [COGS 622] [NOIP2011] 玛雅游戏 模拟
  • [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c
  • [FxCop.设计规则]8. 也许参数类型应该是基类型