easyexcel实现自定义的策略类, 最后追加错误提示列, 自适应列宽,自动合并重复单元格, 美化表头

在后台管理开发的工作中,离不开的就是导出excel了. 如果是简单的导出, 直接easyexcel三四行代码就可以, 但是如果产品业务需要更美观的话, 就需要我们自己去做一些改造
以下代码为自己反复调试后暂用的代码, 如果后面还有优化的话会更新.

原版

首先看下效果对比

• 原版
  
  乍一看还行, 但是有几个问题, 表头字体大了点, 列宽一样,要自己每个去调整. ,重复单元格想要合并
  以及我们有时候, 需要校验导入的模板是否正确, 错误的话想在后面加提示. 所以不得不自己自动手了

表头和表体字体美化

直接上代码

 
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;import java.util.List;
import java.util.Map;/*** ExcelStyleTool** @author zgd* @date 2024/3/13 17:16*/
public class ExcelStyleTool {/*** 设置excel样式*/public static HorizontalCellStyleStrategy getStyleStrategy() {// 头的策略  样式调整WriteCellStyle headWriteCellStyle = new WriteCellStyle();//设置头部样式setHeadStyle(headWriteCellStyle, true, true);// 设置细边框setBorder(headWriteCellStyle);//表头字体样式WriteFont headWriteFont = getHeadFont(IndexedColors.SKY_BLUE.getIndex());headWriteCellStyle.setWriteFont(headWriteFont);// 内容的策略WriteCellStyle contentStyle = new WriteCellStyle();//设置内容样式setHeadStyle(headWriteCellStyle, false, false);//设置边框
//        setBorder(contentStyle);//内容字体WriteFont contentWriteFont = getContentFont();contentStyle.setWriteFont(contentWriteFont);// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现return new HorizontalCellStyleStrategy(headWriteCellStyle, contentStyle);}/*** 获取表头字体* @param color* @return*/private static WriteFont getHeadFont(Short color){//表头字体样式WriteFont headWriteFont = new WriteFont();// 头字号headWriteFont.setFontHeightInPoints((short) 10);// 字体样式headWriteFont.setFontName("微软雅黑");// 字体颜色headWriteFont.setColor(color);// 字体加粗headWriteFont.setBold(true);return headWriteFont;}/*** 获取内容字体* @return*/private static WriteFont getContentFont(){//内容字体WriteFont contentWriteFont = new WriteFont();contentWriteFont.setFontHeightInPoints((short) 9);contentWriteFont.setFontName("Arial");contentWriteFont.setBold(false);return contentWriteFont;}/**** @param cellStyle* @param wrappedFlag   自动换行标识，true:开启自动换行* @param centerFlag    水平居中开关，true:开启水平居中*/private static void setHeadStyle(WriteCellStyle cellStyle, boolean wrappedFlag, boolean centerFlag){// 头背景 白色cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());if(wrappedFlag){// 自动换行cellStyle.setWrapped(true);}if(centerFlag){// 水平对齐方式cellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);}// 垂直对齐方式cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);}/*** 设置边框* @param cellStyle*/private static void setBorder(WriteCellStyle cellStyle){// 设置细边框cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);// 设置边框颜色 25灰度cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());}/*** 得到自定义单元格策略, 内容居中* @return*/public static BeautyStyleStrategy getBeautyCellStyleStrategyCenter(){//灰色表头样式WriteCellStyle headWriteCellStyle = new WriteCellStyle();setHeadStyle(headWriteCellStyle, true, true);setBorder(headWriteCellStyle);WriteFont headWriteFontBlue = getHeadFont(IndexedColors.BLACK.getIndex());headWriteCellStyle.setWriteFont(headWriteFontBlue);//背景色headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//居中对其内容样式WriteFont contentWriteFont2 = getContentFont();WriteCellStyle contentStyleCenter = new WriteCellStyle();contentStyleCenter.setHorizontalAlignment(HorizontalAlignment.CENTER);contentStyleCenter.setVerticalAlignment(VerticalAlignment.CENTER);
//        setBorder(contentStyleCenter);contentStyleCenter.setWriteFont(contentWriteFont2);return new BeautyStyleStrategy(headWriteCellStyle,null,contentStyleCenter);}/**得到内容左对齐的策略* @return*/public static BeautyStyleStrategy getBeautyCellStyleStrategyLeft(){//灰色表头样式WriteCellStyle headWriteCellStyle = new WriteCellStyle();setHeadStyle(headWriteCellStyle, true, true);setBorder(headWriteCellStyle);WriteFont headWriteFont = getHeadFont(IndexedColors.BLACK.getIndex());headWriteCellStyle.setWriteFont(headWriteFont);//背景色headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//内容文字WriteFont contentWriteFont = getContentFont();//左对齐内容样式WriteCellStyle contentStyleLeft = new WriteCellStyle();contentStyleLeft.setHorizontalAlignment(HorizontalAlignment.LEFT);contentStyleLeft.setVerticalAlignment(VerticalAlignment.CENTER);
//        setBorder(contentStyleLeft);contentStyleLeft.setWriteFont(contentWriteFont);return new BeautyStyleStrategy(headWriteCellStyle,contentStyleLeft,null);}public static CustomColumnWidthStyleStrategy getColumnWidthStrategy(int minBytes, int maxBytes){return new CustomColumnWidthStyleStrategy(minBytes,maxBytes);}public static CustomColumnWidthStyleStrategy getAutoBeautyColumnWidthStrategy(){//比较合适的自适应宽度return new CustomColumnWidthStyleStrategy(8,50);}/*** @param headIdx  标题行* @param colIdx   错误所在列. 下标从0开始. 如果没指定,自动取标题行的下一列* @param errTitle* @param errMap   错误信息,key是内容list的下标(为了方便list遍历时传值),最终它的行是 headIdx+errMap+1*/public static AddErrColWriteHandler getAddErrColWriteHandler(Integer headIdx, Integer colIdx, String errTitle, Map<Integer, String> errMap){return new AddErrColWriteHandler(headIdx, colIdx, errTitle, errMap);}/***  @param headIdx  标题行*   @param errTitle*    @param errMap   错误信息,key是内容list的下标,从0开始(为了方便list遍历时传值),最终它的行是 headIdx+errMap+1*/public static AddErrColWriteHandler getAddErrColWriteHandler( Integer headIdx, String errTitle, Map<Integer, String> errMap){return new AddErrColWriteHandler(headIdx,  errTitle, errMap);}/*** 获取合并单元格处理器* @return*/public static CustomMergeCellWriteHandler getMergeHandler() {return new CustomMergeCellWriteHandler();}/*** 获取合并单元格处理器* @param firstRow* @param lastRow* @param firstCol* @param lastCol* @return*/public static CustomMergeCellWriteHandler getMergeHandler(int firstRow, int lastRow, int firstCol, int lastCol) {return new CustomMergeCellWriteHandler(firstRow, lastRow, firstCol, lastCol);}/*** 获取 按列 相同的值进行合并的处理器* @param colList* @return*/public static LoopColRangeWriteHandler getLoopColRangeWriteHandler(List<Integer> colList,int fromRow,int toRow) {return new LoopColRangeWriteHandler(colList,fromRow,toRow);}
}

策略类

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;import java.util.ArrayList;
import java.util.List;/*** 自定义拦截器*/
@Slf4j
public class BeautyStyleStrategy extends AbstractCellStyleStrategy {//这个是 easyexcel 的蓝色表头样式private WriteCellStyle headWriteCellStyle;//这个是 easyexcel 的左对齐内容样式private List<WriteCellStyle> contentWriteCellStyleListLeft;//这个是 easyexcel 的居中对其内容样式private List<WriteCellStyle> contentWriteCellStyleListCenter;//这个是 poi 的表头样式，中间会有一步转换private CellStyle headCellStyle;//这个是 poi 的左对齐内容样式，中间会有一步转换private List<CellStyle> contentCellStyleListLeft;//这个是 poi 的居中对齐内容样式，中间会有一步转换private List<CellStyle> contentCellStyleListCenter;//斑马纹构造方法public BeautyStyleStrategy(WriteCellStyle headWriteCellStyle,List<WriteCellStyle> contentWriteCellStyleListLeft,List<WriteCellStyle> contentWriteCellStyleListCenter) {this.headWriteCellStyle = headWriteCellStyle;this.contentWriteCellStyleListLeft = contentWriteCellStyleListLeft;this.contentWriteCellStyleListCenter = contentWriteCellStyleListCenter;}//统一样式的构造方法public BeautyStyleStrategy(WriteCellStyle headWriteCellStyle,WriteCellStyle contentWriteCellStyleLeft, WriteCellStyle contentWriteCellStyleCenter) {this.headWriteCellStyle = headWriteCellStyle;contentWriteCellStyleListLeft = new ArrayList<>();if (contentWriteCellStyleLeft != null){contentWriteCellStyleListLeft.add(contentWriteCellStyleLeft);}contentWriteCellStyleListCenter = new ArrayList<>();if (contentWriteCellStyleCenter != null){contentWriteCellStyleListCenter.add(contentWriteCellStyleCenter);}}//实例化后进行 easyexcel -> poi 样式的转换@Overrideprotected void initCellStyle(Workbook workbook) {if (headWriteCellStyle != null) {headCellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);}if (contentWriteCellStyleListLeft != null && !contentWriteCellStyleListLeft.isEmpty()) {contentCellStyleListLeft = new ArrayList<CellStyle>();for (WriteCellStyle writeCellStyle : contentWriteCellStyleListLeft) {if (writeCellStyle == null){continue;}contentCellStyleListLeft.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));}}if (contentWriteCellStyleListCenter != null && !contentWriteCellStyleListCenter.isEmpty()) {contentCellStyleListCenter = new ArrayList<CellStyle>();for (WriteCellStyle writeCellStyle : contentWriteCellStyleListCenter) {if (writeCellStyle == null){continue;}contentCellStyleListCenter.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));}}}//设置表头样式@Overrideprotected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {int colIndex = cell.getColumnIndex();//同样，根据不同的列编号选择使用不同的内容样式if (headCellStyle == null) {return;}cell.setCellStyle(headCellStyle);}//设置内容样式@Overrideprotected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {int rowIndex = cell.getRowIndex();//同样，根据不同的列编号选择使用不同的内容样式
//        if (rowIndex > 0) {
//            if (contentCellStyleListCenter == null || contentCellStyleListCenter.isEmpty()) {
//                return;
//            }
//            cell.setCellStyle(contentCellStyleListCenter.get(relativeRowIndex % contentCellStyleListCenter.size()));
//        }if (contentCellStyleListCenter != null && !contentCellStyleListCenter.isEmpty()) {cell.setCellStyle(contentCellStyleListCenter.get(0));}else if(contentCellStyleListLeft != null && !contentCellStyleListLeft.isEmpty()){cell.setCellStyle(contentCellStyleListLeft.get(0));}}}

  EasyExcel.write(os, KnowledgePanoramaActivityExcelVO.class).sheet("配置情况")
//美化表头和表体字体.registerWriteHandler(ExcelStyleTool.getBeautyCellStyleStrategyLeft()).doWrite(records);

1. 如果希望表体内容能够居中显示，只需简单地使用ExcelStyleTool.getBeautyCellStyleStrategyCenter()方法。这只是一个基础框架，可以根据实际需求自由调整颜色、字体等样式。为此，可以在ExcelStyleTool类中新建一个方法，并在该方法中创建一个新的BeautyStyleStrategy对象进行返回。

2. 如果还希望实现每行颜色间隔不同的效果，只需在contentWriteCellStyleListLeft或contentWriteCellStyleListCenter集合中，添加多个不同的样式。随后，只需取消setContentCellStyle方法中的注释即可应用这些样式。

自动拼接错误提示列

效果:

可以在原excel文件的基础上(即便是有丰富样式的模板文件), 在最后加一列, 然后填充我们的错误提示信息

 
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;import java.util.Map;/*** 自定义拦截器.新增注释,第一行头加批注*/
@Slf4j
public class AddErrColWriteHandler implements SheetWriteHandler {/*** 第几行*/private final Integer headIdx;private Integer colIdx;private final String errTitle;private Map<Integer, String> errMap;/*** @param headIdx  标题行* @param colIdx   错误所在列. 下标从0开始. 如果没指定,自动取标题行的下一列* @param errTitle* @param errMap   错误信息,key是内容list的下标(为了方便list遍历时传值),最终它的行是 headIdx+errMap+1*/public AddErrColWriteHandler(Integer headIdx, Integer colIdx, String errTitle, Map<Integer, String> errMap) {this.headIdx = headIdx  ;this.colIdx = colIdx;this.errTitle = errTitle;this.errMap = errMap;}/*** @param headIdx  标题行* @param errTitle* @param errMap   错误信息,key是内容list的下标,从0开始(为了方便list遍历时传值),最终它的行是 headIdx+errMap+1*/public AddErrColWriteHandler(Integer headIdx, String errTitle, Map<Integer, String> errMap) {this.headIdx = headIdx  ;this.errTitle = errTitle;this.errMap = errMap;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {int lastCellNum = 0;Workbook workbook = writeWorkbookHolder.getWorkbook();CellStyle cellStyleHeader = getCellHeaderStyle(workbook);for (Integer i = 0; i <= headIdx; i++) {Row row = writeSheetHolder.getCachedSheet().getRow(headIdx);if (row == null) {row = writeSheetHolder.getCachedSheet().createRow(headIdx);}if (colIdx == null) {lastCellNum = Math.max(row.getLastCellNum(),lastCellNum);colIdx = (int) lastCellNum;}}int titleRowNum = headIdx;if (headIdx > 0){//合并单元格, easyExcel从第1行开始. 然后lastRow和lastCol都不包含自身所以加一
//            writeSheetHolder.getCachedSheet().addMergedRegion(new CellRangeAddress(0,headIdx,colIdx,colIdx));//单元格只保留最上面的,所以指定第一行titleRowNum = 0;}Row row = writeSheetHolder.getCachedSheet().getRow(titleRowNum);Cell cell = row.getCell(colIdx);if (cell == null) {cell = row.createCell(colIdx);}cell.setCellStyle(cellStyleHeader);cell.setCellValue(errTitle);// 内容样式CellStyle  cellStyle =  getCellStyle(workbook);Sheet sheet = writeSheetHolder.getCachedSheet();for (Map.Entry<Integer, String> en : errMap.entrySet()) {int rowIdx = en.getKey() + headIdx + 1;Row row0 = sheet.getRow(rowIdx);if (row0 == null) {row0 = sheet.createRow(rowIdx);}// 第几列。我这里是1.正常业务根据需求传递Cell cell0 = row0.getCell(colIdx);if (cell0 == null) {cell0 = row0.createCell(colIdx);}cell0.setCellStyle(cellStyle);cell0.setCellValue(en.getValue());}}private CellStyle getCellStyle(Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();
//            cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());Font font = workbook.createFont();font.setFontName("微软雅黑");font.setFontHeightInPoints((short) 10);cellStyle.setFont(font);return cellStyle;}private CellStyle getCellHeaderStyle(Workbook workbook) {// 表头样式CellStyle cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.LEFT);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());Font font = workbook.createFont();font.setFontName("微软雅黑");font.setColor((short) 10);font.setFontHeightInPoints((short) 12);cellStyle.setFont(font);return cellStyle;}
}

使用, 可以使用ExcelStyleTool的静态方法, 也可以自己直接new

int headIdx = 1;
//这个key是内容list的下标,从0开始(为了方便list遍历时传值),最终它的行是 headIdx+errMap+1,value是错误信息
Map<Integer,String> errMsgMap = new HashMap<>();
for (int i = 0; i < data.length; i++, dataRow++) {errMsgMap.put(i,"这是错误");
}
//file是上传的文件InputStream is = file.getInputStream();//os是response的输出流或者file文件的输出流EasyExcel.write(os).withTemplate(is).registerWriteHandler(new AddErrColWriteHandler(headIdx, "错误信息(重新导入请删除此列)", errMsgMap)).sheet().doWrite(new ArrayList());

自适应宽度

效果图:

可以看到可以根据列的文本长度(字体默认11的情况), 列宽有一个比较好的适应效果. 如果字体不一样, 修改calWidth方法里的计算方法.

 
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;import java.util.HashMap;
import java.util.List;
import java.util.Map;public class CustomColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {private static final int MAX_COLUMN_WIDTH = 255 * 256;private static final int MIN_COLUMN_WIDTH = 2 * 256;private final int maxColumnWidth;private final int minColumnWidth;public CustomColumnWidthStyleStrategy(int minBytes,int maxBytes) {this.maxColumnWidth = Math.min(calWidth(maxBytes), MAX_COLUMN_WIDTH);this.minColumnWidth = Math.max(calWidth(minBytes), MIN_COLUMN_WIDTH);}private static int calWidth(int bytes) {return bytes * 256 * 2 / 3;}public CustomColumnWidthStyleStrategy() {this.maxColumnWidth = MAX_COLUMN_WIDTH;this.minColumnWidth = MIN_COLUMN_WIDTH;}private Map<Integer, Map<Integer, Integer>> cache = new HashMap<Integer, Map<Integer, Integer>>(8);@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,Integer relativeRowIndex, Boolean isHead) {boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);if (!needSetWidth) {return;}Map<Integer, Integer> maxColumnWidthMap = cache.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<Integer, Integer>(16));Integer columnWidth = dataLength(cellDataList, cell, isHead);if (columnWidth < 0) {return;}if (columnWidth > maxColumnWidth) {columnWidth = maxColumnWidth;}if (columnWidth < minColumnWidth) {columnWidth = minColumnWidth;}Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());if (maxColumnWidth == null || columnWidth > maxColumnWidth) {maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth);}}private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {if (isHead) {//不考虑标题return -1;}CellData cellData = cellDataList.get(0);CellDataTypeEnum type = cellData.getType();if (type == null) {return minColumnWidth;}switch (type) {case STRING:return calWidth(cellData.getStringValue().getBytes().length);case BOOLEAN:return calWidth(cellData.getBooleanValue().toString().getBytes().length);case NUMBER:return calWidth(cellData.getNumberValue().toString().getBytes().length );default:return minColumnWidth;}}
}

使用方法和上面差不多, 不赘述了

自动合并单元格

效果,可以按列,将同样的内容的单元格自动合并, 如果需要按行合并, 差不多的思路实现, 这里没这个需求就没有做:

首先写一个公共方法. 用于针对某一列的同样内容进行合并单元格;

public static void mergeByCol(Sheet sheet, int colIdx, int fromRowIdx,int toRowIdx) {mergeByCol(sheet, colIdx, fromRowIdx,toRowIdx,false,VerticalAlignment.CENTER,HorizontalAlignment.CENTER);}/*** 根据列,将同样的单元格合并* @param sheet* @param colIdx* @param fromRowIdx*/public static void mergeByCol(Sheet sheet, int colIdx, int fromRowIdx,int toRowIdx,boolean wrap,VerticalAlignment verticalAlignment,HorizontalAlignment alignment) {Iterator<Row> it = sheet.rowIterator();int rows = -1;String lastVal = null;int lastRows = 0;while (it.hasNext()){Row row = it.next();rows++;if (fromRowIdx > rows){continue;}if (lastVal == null){lastRows = rows;lastVal = row.getCell(colIdx).getStringCellValue();} else {Cell cell = row.getCell(colIdx);String curVal = cell == null ? null : cell.getStringCellValue();if (lastVal.equals(curVal)){continue;} else {if (rows - 1 > lastRows) {//合并sheet.addMergedRegion(new CellRangeAddress(lastRows, rows - 1, colIdx, colIdx));//设置格式Cell topLeftCell = sheet.getRow(lastRows).getCell(colIdx);CellStyle originStyle = topLeftCell.getCellStyle();CellStyle cellStyle = sheet.getWorkbook().createCellStyle();cellStyle.cloneStyleFrom(originStyle);cellStyle.setVerticalAlignment(verticalAlignment);cellStyle.setAlignment(alignment);if (wrap){cellStyle.setWrapText(true);}}lastRows = rows;lastVal = curVal;}}}//遍历所有行以后,最后判断一次Cell cell = sheet.getRow(toRowIdx).getCell(colIdx);if (cell != null && lastVal != null && toRowIdx > lastRows ){String curVal = cell.getStringCellValue();if (Objects.equals(lastVal, curVal) ){sheet.addMergedRegion(new CellRangeAddress(lastRows, toRowIdx, colIdx, colIdx));}}}

使用Easyexcel

 
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import javafx.util.Pair;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;/*** 添加合并区Handler*/
public class LoopColRangeWriteHandler extends AbstractCellWriteHandler {private final Map<Integer, Pair<Integer,String>> mergeMap;/**从哪行开始,主要是跳过表头*/private final Integer fromRow;/*** 标志在哪一行结束合并,最重要的是放在最后一行的时候,让系统知道将前面的合并*/private final Integer toRow;public LoopColRangeWriteHandler(List<Integer> colList, Integer fromRow,Integer toRow) {mergeMap = colList.stream().collect(Collectors.toMap(i -> i, i -> new Pair<>(fromRow, "")));this.fromRow = fromRow;this.toRow = toRow;}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {String cellValue = cell.getStringCellValue();int columnIndex = cell.getColumnIndex();int rowIndex = cell.getRowIndex();if ((fromRow < 0 && isHead) || rowIndex < fromRow
//                || !mergeMap.containsKey(columnIndex)){return;}if (rowIndex == toRow){for (Integer colIndex : colList) {if (colIndex == columnIndex){ExcelUtil.mergeByCol(writeSheetHolder.getSheet(),colIndex,fromRow,toRow,true,VerticalAlignment.CENTER,HorizontalAlignment.LEFT);}}}}
}

然后用的话, 需要指定开始行和结束行, 结束行是为了让系统知道这是最后一行,需要对前面相同的内容进行合并了.

 EasyExcel.write(os, KnowledgePanoramaActivityExcelVO.class).sheet("配置情况")//这里结束行注意别搞错了,因为下标从0开始,所以要－1.registerWriteHandler(ExcelStyleTool.getLoopColRangeWriteHandler(CollectionUtil.toList( 3,4),titleRow,records.size()+titleRow-1)).doWrite(records);

使用poi导出

   Sheet studyPointSheet = workbook.getSheetAt(0);
... 填充内容
//填充完内容后使用ExcelUtil.mergeByCol(studyPointSheet, 2,2,studyPointSheet.getLastRowNum());ExcelUtil.mergeByCol(studyPointSheet, 3,2,studyPointSheet.getLastRowNum());workbook.write(os);