通过poi导出合并单元格 合并单元格 并合并相邻并相同值的单元格
poi依赖
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency>
实体类
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;@Builder@NoArgsConstructor@AllArgsConstructor@Datapublic class PoiModel {private String content;private String oldContent;private int rowIndex;private int cellIndex;}
工具类
import com.jxn.pojo.PoiModel;import press.utils.Lists;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import javax.servlet.http.HttpServletResponse;import java.io.FileNotFoundException;import java.io.IOException;import java.io.OutputStream;import java.util.List;import java.util.Map;/*** 导出excel工具类*/public class ExportExcelUtil {public ExportExcelUtil() {super();}/*** @Param [columnNames 表头, column 字段名称, rows 数据, excelName 文件名称, mergeIndex 合并列, response]**/public static void createSXSSFWorkbookHeteroideus(String[] columnNames, String[] column, List<Map<String, Object>> rows, String excelName, int[] mergeIndex, HttpServletResponse response) {SXSSFWorkbook workbook = new SXSSFWorkbook(); // 创建工作薄，相当于一个文件SXSSFSheet sheet = workbook.createSheet(); // 创建一个表// 标题样式CellStyle titleStyle = workbook.createCellStyle();// 标题字体Font titleFont = workbook.createFont();titleFont.setFontHeightInPoints((short) 12); // 字体大小titleFont.setFontName("宋体");titleStyle.setFont(titleFont);// 表头样式CellStyle headerStyle = workbook.createCellStyle();headerStyle.setWrapText(true); // 设置多行显示//这两句话是表示将表头单元格格式设置为文本型，在后面只要调用-----.setDataFormat(format.getFormat("@"))的方法就可以将数据设置为文本型。DataFormat format = workbook.createDataFormat();headerStyle.setDataFormat(format.getFormat("@"));// 表头字体Font headerFont = workbook.createFont();headerFont.setFontHeightInPoints((short) 12);headerFont.setFontName("宋体");headerFont.setBold(true);headerStyle.setFont(headerFont);// 数据样式CellStyle dataStyle = workbook.createCellStyle();// 数据字体Font dataFont = workbook.createFont();dataFont.setFontHeightInPoints((short) 9);dataFont.setFontName("宋体");dataStyle.setFont(dataFont);Row row = null;Cell cell = null;row = sheet.createRow(0);//设置表头for (int i = 0; i < columnNames.length; i++) {row.setHeight((short) (2 * 256));cell = row.createCell(i);cell.setCellValue(columnNames[i]);cell.setCellStyle(headerStyle);}List<PoiModel> poiModels = Lists.newArrayList();//设置数据行for (int i = 0; i < rows.size(); i++) {Row dataRow = sheet.createRow(i + 1);sheet.trackAllColumnsForAutoSizing();Map<String, Object> project = rows.get(i); // 当前行数据int index = i + 1; // 当前行// 循环字段名称数组for (int j = 0; j < column.length; j++) {String old = null;if (index > 1 && poiModels.size() > 0) {old = poiModels.get(j) == null ? null : poiModels.get(j).getContent();}// 循环需要合并的列listfor (int mer : mergeIndex) {// 如果是第一行 为PoiModel赋值记录数据后结束循环if (index == 1) {String oldContent = project.get(column[j]) == null ? "" : project.get(column[j]).toString();String content = project.get(column[j]) == null ? "" : project.get(column[j]).toString();// 为poiModel 赋值PoiModel poiModel = PoiModel.builder().oldContent(oldContent).content(content).rowIndex(1).cellIndex(j).build();poiModels.add(poiModel);break;}// 如果记录list中有值if (poiModels != null && poiModels.size() > 0) {// 对应列 的 PoiModelPoiModel poiModel = poiModels.get(j);// 对应列 的 字段值String content = project.get(column[j]) == null ? "" : project.get(column[j]).toString();// 如果 不是第一列 并且 合并列循环的当前值与该列下标相等if (j > 0 && mer == j) {// 如果不需要考虑当前行与上一行内容相同，但是它们的前一列内容不一样则不合并的情况，把或条件删除// || poiModel.getContent().equals(content) && !poiModels.get(j - 1).getOldContent().equals(project.get(column[j - 1]))// 如果 当前列，当前行的值与上一行的值不相同，则执行合并 合并以上相同行if (!poiModel.getContent().equals(content)) {get(poiModel, content, index, j, sheet);}}// 处理第一列的情况// 如果 该列是需要合并的，并且是第一列 并且 与上一行的值不同 则合并 合并以上相同行if (mer == j && j == 0 && !poiModel.getContent().equals(content)) {get(poiModel, content, index, j, sheet);}// 最后一行没有后续的行与之比较，所有当到最后一行时则直接合并对应列的相同内容// 如果 该列是需要合并的 并且行数为最后一行 并且 poiModel中记录的行数不等于当前行 说明有相同的if (mer == j && index == rows.size() && poiModels.get(j).getRowIndex() != index) {CellRangeAddress cra = new CellRangeAddress(poiModels.get(j).getRowIndex(), index, poiModels.get(j).getCellIndex(), poiModels.get(j).getCellIndex());sheet.addMergedRegion(cra);}}}Cell dataCell = dataRow.createCell(j);Object obj = project.get(column[j]);sheet.autoSizeColumn(j, true);dataCell.setCellValue(obj == null ? "" : obj.toString());}}OutputStream os = null;try {// 创建一个普通输出流os = response.getOutputStream();// 请求浏览器打开下载窗口response.reset();response.setCharacterEncoding("UTF-8");excelName = new String(excelName.getBytes(), "ISO8859-1");response.setHeader("Content-Disposition", "attachment; filename=" + excelName);// 要保存的文件名response.setContentType("application/octet-stream");workbook.write(os);} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {workbook.close();os.close();} catch (IOException e) {e.printStackTrace();}}}private static void get(PoiModel poiModel, String content, int index, int i, Sheet sheet) {// 如果 PoiModel记录的行数 不是 当前行 则合并if (poiModel.getRowIndex() != index - 1) {// 相同值第一次出现的行数 到当前行数-1 （下标） 合并开始列 合并结束列CellRangeAddress cra = new CellRangeAddress(poiModel.getRowIndex(), index - 1, poiModel.getCellIndex(), poiModel.getCellIndex());//在sheet里增加合并单元格sheet.addMergedRegion(cra);}/*重新记录该列的内容为当前内容，行标记改为当前行标记，列标记则为当前列*/poiModel.setContent(content);poiModel.setRowIndex(index);poiModel.setCellIndex(i);}}
调用
@SystemLog(name = "工作计划-待我审核导出", type = OperationType.UNKNOWN)public Result queryWorkPlanIReviewedExport(@RequestBody Map<String, Object> map, HttpServletRequest request, HttpServletResponse response) {try {List<Map<String, Object>> list = workPlanService.queryWorkPlanIReviewedExport(map);String[] headers = {"提交部门", "提交人", "手机号", "计划月份", "类别", "工作事项", "通知标题", "关键步骤", "工作要求", "提交日期","开始日期", "结束日期", "进度目标", "质量考核标注", "需交付内容", "抄送人", "协同部门", "协同负责人", "协同要求"};String[] column = {"deptName", "userName", "mobile", "planDate", "classes", "workMatters", "title", "step", "workRequire", "submitDate","startDate", "endDate", "targetPro", "target", "delivery", "copyUser", "synergyDept", "synergyUser", "synergyRequire"};String excelName = "测试异形导出.xlsx";ExportExcelUtil exportExcelUtil = new ExportExcelUtil();int[] mergeIndex = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18};exportExcelUtil.createSXSSFWorkbookHeteroideus(headers, column, list, excelName, mergeIndex, response);return Result.ok();} catch (Exception e) {e.printStackTrace();return Result.fail();}}
两篇文章一块合起来的
/CSDNxiaoxuan/article/details/124817923
/tang_sy/article/details/124018099
