一、概述
当有需求下载EXCEL模板文件时，一些列的数据是想让用户选择而不是输入，此时就需要实现下拉框，当下拉框逐渐多起来之后，有层级关系的时候就涉及到了级联下拉框。
导入导出基于easyExcel实现的，学习跳转--关于Easyexcel | Easy Excel 官网
二、代码实现
（一）、相关依赖
<dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <version>5.3.7</version></dependency><dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.0.5</version></dependency>
（二）、代码实现
import cn.hutool.core.date.DateUtil;import cn.hutool.core.io.FileUtil;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddressList;import org.apache.poi.xssf.usermodel.*;import javax.servlet.http.HttpServletResponse;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileOutputStream;import java.io.OutputStream;import java.util.*;@Slf4jpublic class CascadeSelectTool { private final XSSFWorkbook workbook; private XSSFSheet mainSheet; /** * 数据 */ private Map<String, List<String>> areaList = new LinkedHashMap<>(); /** * 隐藏页名称 */ private String hiddenSheetName = "hidden"; /** * 第一行 */ private int firstRow = 1; /** * 一级名称 */ private String topName; /** * 级联集合 */ private List<Integer> selectColList; public CascadeSelectTool(XSSFWorkbook book) { this.workbook = book; } public CascadeSelectTool createSheet(String sheetName) { Sheet sheet = workbook.getSheet(sheetName); if (Objects.nonNull(sheet)) { this.mainSheet = (XSSFSheet) sheet; } else { this.mainSheet = (XSSFSheet) workbook.createSheet(sheetName); } return this; } public CascadeSelectTool createSelectDateList(Map<String, List<String>> areaList) { this.areaList = areaList; return this; } public CascadeSelectTool createTopName(String topName) { this.topName = topName; return this; } public CascadeSelectTool createSelectColList(List<Integer> selectColList) { this.selectColList = selectColList; return this; } public CascadeSelectTool createHiddenName(String hiddenSheetName) { this.hiddenSheetName = hiddenSheetName; return this; } public CascadeSelectTool createFirstRow(int firstRow) { this.firstRow = firstRow; return this; } public CascadeSelectTool createHead(List<String> heads) { XSSFRow startRow = this.mainSheet.createRow(0); startRow.setHeightInPoints(30); // 创建单元格风格样式 XSSFCellStyle cellStyle = this.workbook.createCellStyle(); // 设置样式-单元格边框 cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFFont font = this.workbook.createFont(); font.setBold(true); font.setFontHeight(14); cellStyle.setFont(font); for (int i = 0; i < heads.size(); i++) { String value = heads.get(i); XSSFCell startCell1 = startRow.createCell(i); startCell1.setCellValue(value); startCell1.setCellStyle(cellStyle); this.mainSheet.autoSizeColumn(i); this.mainSheet.setColumnWidth(i, this.mainSheet.getColumnWidth(i) * 3); } return this;} public CascadeSelectTool createEmptyList(int row ,int col) { XSSFCellStyle cellStyle = this.workbook.createCellStyle(); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); row = row + 1; for (int i = 1; i < row; i++) { XSSFRow startRow = this.mainSheet.createRow(i); startRow.setHeightInPoints(18); for (int j = 0; j < col; j++) { XSSFCell startCell1 = startRow.createCell(j); startCell1.setCellStyle(cellStyle); } } return this;} /** * 设置二级级联下拉框数据 */ public CascadeSelectTool setCascadeDropDownBox() { //获取所有sheet页个数 int sheetTotal = workbook.getNumberOfSheets(); //处理下拉数据 if (areaList != null && areaList.size() != 0) { //新建一个sheet页 XSSFSheet hiddenSheet = workbook.getSheet(hiddenSheetName); if (hiddenSheet == null) { hiddenSheet = workbook.createSheet(hiddenSheetName); sheetTotal++; } int mainStart = 2; int mainEnd = mainStart; // 获取数据起始行 int startRowNum = hiddenSheet.getLastRowNum() + 1; Set<String> keySet = areaList.keySet(); for (String key : keySet) { XSSFRow fRow = hiddenSheet.createRow(startRowNum++); fRow.createCell(0).setCellValue(key); List<String> sons = areaList.get(key); for (int i = 1; i <= sons.size(); i++) { fRow.createCell(i).setCellValue(sons.get(i - 1)); } if (Objects.equals(topName, key)) { mainEnd = sons.size(); } // 添加名称管理器 String range = getRange(1, startRowNum, sons.size()); Name name = workbook.getName(key); if (Objects.isNull(name)) { name = workbook.createName(); //key不可重复 name.setNameName(key); String formula = hiddenSheetName + "!" + range; name.setRefersToFormula(formula); } } //将数据字典sheet页隐藏掉 workbook.setSheetHidden(sheetTotal - 1, true); // 设置父级下拉 //获取新sheet页内容 String mainFormula = hiddenSheetName + "!$A$" + mainStart + ":$A$" + (mainEnd + 1); for (int i = 0; i < selectColList.size(); i++) { Integer col = selectColList.get(i); if (i == 0) { // 设置下拉列表值绑定到主sheet页具体哪个单元格起作用 mainSheet.addValidationData(setDataValidation(mainFormula, firstRow, col, col)); } else { Integer fatherCol = selectColList.get(i - 1); // 设置子级下拉 // 当前列为子级下拉框的内容受父级哪一列的影响 String indirectFormula = "INDIRECT($" + decimalToTwentyHex(fatherCol + 1) + "" + (firstRow + 1) + ")"; mainSheet.addValidationData(setDataValidation(indirectFormula, firstRow, col, col)); } } } return this; } /** * 计算formula * * @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列 * @param rowId 第几行 * @param colCount 一共多少列 * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1 */ private String getRange(int offset, int rowId, int colCount) { char start = (char) ('A' + offset); if (colCount <= 25) { char end = (char) (start + colCount - 1); return "$" + start + "$" + rowId + ":$" + end + "$" + rowId; } else { char endPrefix = 'A'; char endSuffix = 'A'; // 26-51之间，包括边界（仅两次字母表计算） if ((colCount - 25) / 26 == 0 || colCount == 51) { // 边界值 if ((colCount - 25) % 26 == 0) { endSuffix = (char) ('A' + 25); } else { endSuffix = (char) ('A' + (colCount - 25) % 26 - 1); } } else {// 51以上 if ((colCount - 25) % 26 == 0) { endSuffix = (char) ('A' + 25); endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1); } else { endSuffix = (char) ('A' + (colCount - 25) % 26 - 1); endPrefix = (char) (endPrefix + (colCount - 25) / 26); } } return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId; } } /** * 返回类型 DataValidation * * @param strFormula formula * @param firstRow 起始行 * @param firstCol 起始列 * @param endCol 终止列 * @return 返回类型 DataValidation */ private DataValidation setDataValidation(String strFormula, int firstRow, int firstCol, int endCol) { CellRangeAddressList regions = new CellRangeAddressList(firstRow, 65535, firstCol, endCol); DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) workbook.getSheet(hiddenSheetName)); DataValidationConstraint formulaListConstraint = dvHelper.createFormulaListConstraint(strFormula); return dvHelper.createValidation(formulaListConstraint, regions); } /** * 返回类型 DataValidation * @param strFormula formula * @param firstRow 起始行 * @param endRow 终止行 * @param firstCol 起始列 * @param endCol 终止列 * @return 返回类型 DataValidation */ public DataValidation setTypeListDataValidation(String strFormula, int firstRow, int endRow, int firstCol, int endCol) { CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) workbook.getSheet("typelist")); DataValidationConstraint formulaListConstraint = dvHelper.createFormulaListConstraint(strFormula); return dvHelper.createValidation(formulaListConstraint, regions); } /** * 设置下拉框数据 * @param typeName 要渲染的sheet名称 * @param values 下拉框的值 * @param col 下拉列的下标 * @author Hower Wong * @date 2022年5月27日 */ public void setDropDownBox(String typeName, String[] values, Integer col) { //获取所有sheet页个数 int sheetTotal = workbook.getNumberOfSheets(); //处理下拉数据 if (values != null && values.length != 0) { //新建一个sheet页 XSSFSheet hiddenSheet = workbook.getSheet(hiddenSheetName); if (hiddenSheet == null) { hiddenSheet = workbook.createSheet(hiddenSheetName); sheetTotal++; } // 获取数据起始行 int startRowNum = hiddenSheet.getLastRowNum() + 1; int endRowNum = startRowNum; //写入下拉数据到新的sheet页中 for (int i = 0; i < values.length; i++){ hiddenSheet.createRow(endRowNum++).createCell(0).setCellValue(values[i]); } //将新建的sheet页隐藏掉 workbook.setSheetHidden(sheetTotal - 1, true); //获取新sheet页内容 String strFormula = hiddenSheetName + "!$A$" + ++startRowNum + ":$A$" + endRowNum; // 设置下拉 XSSFSheet mainSheet = workbook.getSheet(typeName); mainSheet.addValidationData(setTypeListDataValidation(strFormula, 1, 65535, col, col)); } } /** * 十进制转二十六进制 */ private String decimalToTwentyHex(int decimalNum) { StringBuilder result = new StringBuilder(); while (decimalNum > 0) { int remainder = decimalNum % 26; //大写A的ASCII码值为65 result.append((char) (remainder + 64)); decimalNum = decimalNum / 26; } return result.reverse().toString(); } public void writeFile() { writeFile(workbook); } public static void writeFile(Workbook book) { try { String storeName = System.currentTimeMillis() + ".xlsx"; String folder = "project//cct/" + cn.hutool.core.date.DateUtil.format(DateUtil.date(), "yyMMdd") + "/"; String attachmentFolder = "E://" + File.separator; String address = folder + storeName; FileUtil.mkdir(attachmentFolder + folder); FileOutputStream fileOut = new FileOutputStream(attachmentFolder + address); book.write(fileOut); fileOut.close(); } catch (Exception e) { e.printStackTrace(); } } public XSSFWorkbook getWorkbook() { return workbook; } public void backFlow(HttpServletResponse response) { try { // 将工作簿写入输出流 ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); workbook.write(outputStream); // 设置响应头，告诉浏览器返回的是一个Excel文件 response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=.xls"); // 将Excel文件写入响应的输出流 OutputStream outStream = response.getOutputStream(); outputStream.writeTo(outStream); outStream.flush(); outStream.close(); } catch (Exception e) { log.error("导出异常-->", e); } }} 三、导出EXCEL设置下拉框
设置下拉框导出示例
/** * 商品导入模板下载 * @param response */@PostMapping("/template")@SneakyThrowspublic void excelOtherTemplate(HttpServletResponse response){ String nameStr = "商品导入模板"; List<String> heads = Arrays.asList("单号", "姓名", "供应商", "商品", "价格"); XSSFWorkbook book = new XSSFWorkbook(); CascadeSelectTool cascadeSelectTool = new CascadeSelectTool(book) .createSheet(nameStr) .createHead(heads) .createEmptyList(20, heads.size()); List<String> storeList = CollectionUtil.newArrayList("哇哈哈", "可口可乐"); String[] storeStr = storeList.toArray(new String[storeList.size()]); cascadeSelectTool.setDropDownBox(nameStr, storeStr, 2); List<String> merList = CollectionUtil.newArrayList("营养快线", "爽歪歪", "AD钙"); String[] merStr = merList.toArray(new String[merList.size()]); cascadeSelectTool.setDropDownBox(nameStr, merStr, 3); String fileName = nameStr+".xlsx"; // 将工作簿写入输出流 ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); book.write(outputStream); // 设置响应头，告诉浏览器返回的是一个Excel文件 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8")); // 将Excel文件写入响应的输出流 OutputStream outStream = response.getOutputStream(); outputStream.writeTo(outStream); outStream.flush(); outStream.close();} 对于新建list的时候用asList方法还是newArrayList，如果新建的list不涉及到新增、删除操作时用asList()，涉及的话用newArrayList()，也可统一用后者。
效果图：
设置级联下拉框导出示例
/** * 商品模板下载 * @param response */@PostMapping("/template")@SneakyThrowspublic void excelTemplate(HttpServletResponse response){ String fileName = "供货单模板.xlsx"; // 将工作簿写入输出流 ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); excelTemplate().write(outputStream); // 设置响应头，告诉浏览器返回的是一个Excel文件 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8")); // 将Excel文件写入响应的输出流 OutputStream outStream = response.getOutputStream(); outputStream.writeTo(outStream); outStream.flush(); outStream.close();}public XSSFWorkbook excelTemplate() { //业务数据模拟 请根据真实场景替换 Map<Long, String> supNameMap = new HashMap<>(); supNameMap.put(1L, "哇哈哈"); supNameMap.put(2L, "可口可乐"); Map<Long, List<String>> merMap = new HashMap<>(); merMap.put(1L, Arrays.asList("营养快线", "爽歪歪", "AD钙")); merMap.put(2L, Arrays.asList("美汁源","芬达","水动乐","健怡","零度")); //示例--供应商与商品的级联下拉框 List<String> supNameList = supNameMap.values().stream().collect(Collectors.toList()); Map<String, List<String>> areaList = new LinkedHashMap<>(); areaList.put("供应商", supNameList); List<Map<String, List<String>>> twoList = new ArrayList<>(); for (Map.Entry<Long, String> entry : supNameMap.entrySet()) { String supName = entry.getValue(); Long supId = entry.getKey(); List<String> commodityList = merMap.get(supId); List<String> merNameList = new ArrayList<>(); Map<String, List<String>> twoMap = new HashMap<>(); for (String merName : commodityList) { merNameList.add(merName); } if (merNameList.isEmpty()) { merNameList.add(""); } twoMap.put(supName, merNameList); twoList.add(twoMap); } twoList.forEach(t -> areaList.putAll(t)); List<Integer> selectColList = CollectionUtil.newArrayList(2,3); XSSFWorkbook book = new XSSFWorkbook(); List<String> heads = Arrays.asList("单号", "姓名", "供应商", "商品", "价格"); CascadeSelectTool cascadeSelectTool = new CascadeSelectTool(book) .createSheet("供货单") .createHead(heads) .createEmptyList(20, heads.size()) .createFirstRow(1) .createSelectDateList(areaList) .createTopName("供应商") .createSelectColList(selectColList) .setCascadeDropDownBox(); //示例--姓名下拉框 List<String> nameList = Arrays.asList("张三", "李四", "王五"); cascadeSelectTool.setDropDownBox( "供货单", nameList.toArray(new String[nameList.size()]), 1); return cascadeSelectTool.getWorkbook();} 参考地址：Java 导出Excel下拉框(多级级联)-CSDN博客
效果图：
正片结束~~
附：关于导入导出的杂记
附上easyExcel导入导出一些小tips：
导入导出遇到时间类型报错处理
import com.alibaba.excel.converters.Converter;import com.alibaba.excel.enums.CellDataTypeEnum;import com.alibaba.excel.metadata.GlobalConfiguration;import com.alibaba.excel.metadata.data.WriteCellData;import com.alibaba.excel.metadata.property.ExcelContentProperty;import java.time.LocalDate;import java.time.LocalDateTime;import java.time.format.DateTimeFormatter;/** * 自定义LocalDateStringConverter * 用于解决使用easyexcel导出表格时候，默认不支持LocalDateTime日期格式 * * 在需要的属性上添加注解 @ExcelProperty(value = "创建日期", converter = LocalDateStringConverter.class) */public class LocalDateStringConverter implements Converter<LocalDate> { @Override public Class supportJavaTypeKey() { return LocalDateTime.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } @Override public WriteCellData<?> convertToExcelData(LocalDate localDate, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception { WriteCellData cellData = new WriteCellData(); DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); String cellValue; cellValue=formatter.format(localDate); cellData.setType(CellDataTypeEnum.STRING); cellData.setStringValue(cellValue); cellData.setData(cellValue); return cellData; }} 表头的自动行高
设置表头的自动调整行高策略
import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import java.util.Iterator;/** * 设置表头的自动调整行高策略 */public class CellRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy { /** * 默认高度 */ private static final Integer DEFAULT_HEIGHT = 300; @Override protected void setHeadColumnHeight(Row row, int relativeRowIndex) { //设置主标题行高为10 if(relativeRowIndex == 0){ row.setHeight((short) 500); } } @Override protected void setContentColumnHeight(Row row, int relativeRowIndex) { Iterator<Cell> cellIterator = row.cellIterator(); while (!cellIterator.hasNext()) { return; } // 默认为 1行高度 int maxHeight = 1; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellTypeEnum() == CellType.STRING) { String value = cell.getStringCellValue(); int len = value.length(); int num = 0; if (len > 50) { num = len % 50 > 0 ? len / 50 : len / 2 - 1; } if (num > 0) { for (int i = 0; i < num; i++) { value = value.substring(0, (i + 1) * 50 + i) + "\n" + value.substring((i + 1) * 50 + i, len + i); } } if (value.contains("\n")) { int length = value.split("\n").length; maxHeight = Math.max(maxHeight, length) + 1; } } } row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT)); }} 设置表头和填充内容的样式
import com.alibaba.excel.metadata.data.WriteCellData;import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;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.VerticalAlignment;/** * 设置表头和填充内容的样式 */public class CellStyleStrategy extends HorizontalCellStyleStrategy { private final WriteCellStyle headWriteCellStyle; private final WriteCellStyle contentWriteCellStyle; public CellStyleStrategy(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) { this.headWriteCellStyle = headWriteCellStyle; this.contentWriteCellStyle = contentWriteCellStyle; } //设置头样式 @Override protected void setHeadCellStyle( CellWriteHandlerContext context) { // 字体 WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontName("宋体"); headWriteFont.setFontHeightInPoints((short)14); headWriteFont.setBold(true); headWriteCellStyle.setWriteFont(headWriteFont); // 样式 headWriteCellStyle.setBorderBottom(BorderStyle.THIN); headWriteCellStyle.setBottomBorderColor((short) 0); headWriteCellStyle.setBorderLeft(BorderStyle.THIN); headWriteCellStyle.setLeftBorderColor((short) 0); headWriteCellStyle.setBorderRight(BorderStyle.THIN); headWriteCellStyle.setRightBorderColor((short) 0); headWriteCellStyle.setBorderTop(BorderStyle.THIN); headWriteCellStyle.setTopBorderColor((short) 0); headWriteCellStyle.setWrapped(true); headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); headWriteCellStyle.setShrinkToFit(true); if (stopProcessing(context)) { return; } WriteCellData<?> cellData = context.getFirstCellData(); WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle()); } //设置填充数据样式 @Override protected void setContentCellStyle(CellWriteHandlerContext context) { WriteFont contentWriteFont = new WriteFont(); contentWriteFont.setFontName("宋体"); contentWriteFont.setFontHeightInPoints((short) 11); //设置数据填充后的实线边框 contentWriteCellStyle.setWriteFont(contentWriteFont); contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); contentWriteCellStyle.setBorderTop(BorderStyle.THIN); contentWriteCellStyle.setBorderRight(BorderStyle.THIN); contentWriteCellStyle.setBorderBottom(BorderStyle.THIN); contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); contentWriteCellStyle.setWrapped(true); WriteCellData<?> cellData = context.getFirstCellData(); WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle()); }} 自动设置列宽
import com.alibaba.excel.enums.CellDataTypeEnum;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.metadata.data.CellData;import com.alibaba.excel.metadata.data.WriteCellData;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Sheet;import org.springblade.core.tool.utils.Func;import org.springframework.util.CollectionUtils;import java.util.HashMap;import java.util.List;import java.util.Map;@Slf4jpublic class CellWidthStyleStrategy extends AbstractColumnWidthStyleStrategy { private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>(); @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) { boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList); if (needSetWidth) { Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>()); Integer columnWidth = this.dataLength(cellDataList, cell, isHead); columnWidth = (int) (Func.toInt(columnWidth) * 1.5); // 单元格文本长度大于60换行 if (columnWidth >= 0) { if (columnWidth > 60) { columnWidth = 60; } Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex()); if (maxColumnWidth == null || columnWidth > maxColumnWidth) { maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth); Sheet sheet = writeSheetHolder.getSheet(); sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } } } /** * 计算长度 * @param cellDataList * @param cell * @param isHead * @return */ private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { CellData<?> cellData = cellDataList.get(0); CellDataTypeEnum type = cellData.getType(); if (type == null) { return -1; } else { switch (type) { case STRING: // 换行符（数据需要提前解析好） int index = cellData.getStringValue().indexOf("\n"); return index != -1 ? cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1; case BOOLEAN: return cellData.getBooleanValue().toString().getBytes().length; case NUMBER: return cellData.getNumberValue().toString().getBytes().length; default: return -1; } } } }} 同列相同数据合并
import cn.hutool.core.collection.CollUtil;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.merge.AbstractMergeStrategy;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.ArrayList;import java.util.List;import java.util.Optional;import java.util.concurrent.atomic.AtomicInteger;/** * * easyExcel 列合并 * 同列相同数据合并 * */public class CustomMergeStrategy extends AbstractMergeStrategy { /** * 分组，每几行合并一次 */ private List<List<Integer>> mergeColDataGroupCountList; /** * 目标合并列index */ private List<Integer> targetColumnIndex; /** * 需要开始合并单元格的首行index */ private Integer rowIndex; /** * mergeColDataList为待合并目标列的值 */ public CustomMergeStrategy(List<List<String>> mergeColDataList, List<Integer> targetColumnIndex) { this.mergeColDataGroupCountList = getGroupCountList(mergeColDataList); this.targetColumnIndex = targetColumnIndex; } @Override protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) { if (null == rowIndex) { rowIndex = cell.getRowIndex(); } // 仅从首行以及目标列的单元格开始合并，忽略其他 if (cell.getRowIndex() == rowIndex && targetColumnIndex.contains(cell.getColumnIndex())) { //找到对应的需要合并的列 AtomicInteger i = new AtomicInteger(0); Optional<Integer> first = targetColumnIndex.stream().filter(col -> { i.getAndIncrement(); return col == cell.getColumnIndex(); }).findFirst(); mergeGroupColumn(sheet, first.get()); } } private void mergeGroupColumn(Sheet sheet, Integer index) { int rowCount = rowIndex; for (Integer count : mergeColDataGroupCountList.get(index)) { if (count == 1) { rowCount += count; continue; } // 合并单元格 CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex.get(index), targetColumnIndex.get(index)); sheet.addMergedRegionUnsafe(cellRangeAddress); rowCount += count; } } /** * 该方法将目标列根据值是否相同连续可合并，存储可合并的行数 */ private List<List<Integer>> getGroupCountList(List<List<String>> exportDataList) { if (CollUtil.isEmpty(exportDataList)) { return new ArrayList<>(); } List<List<Integer>> groupCountListList = new ArrayList<>(); exportDataList.forEach(dataList->{ List<Integer> groupCountList = new ArrayList<>(); int count = 1; for (int i = 1; i < dataList.size(); i++) { if (dataList.get(i).equals(dataList.get(i - 1))) { count++; } else { groupCountList.add(count); count = 1; } } // 处理完最后一条后 groupCountList.add(count); groupCountListList.add(groupCountList); }); return groupCountListList; }} 合并参考：EasyExcel合并单元格（同列相同数据合并）_easyexcel合并列单元格-CSDN博客
根据指定列合并参考：JAVA导出EXCEL根据某列自定义单元格合并（easyExcel）-CSDN博客
策略使用示例
根据实际情况使用registerWriteHandler，可以一个都不注册，也可以注册一个或多个。
// 需要合并的列 mergeColDataList // List<List<String>> mergeColDataList 需要合并的数据List<List<String>> mergeColDataList = Stream.of(Arrays.asList("张三","李四","王五").stream().collect(Collectors.toList()), Arrays.asList("哇哈哈","可口可乐").stream().collect(Collectors.toList()), Arrays.asList("芬达","零度").stream().collect(Collectors.toList())).collect(Collectors.toList());List<Integer> mergeColIndexList = Stream.of(0,1,2).collect(Collectors.toList());response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");String fileName = URLEncoder.encode(name + ".xlsx", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName);EasyExcel.write(response.getOutputStream(), ExcelMealStatisticFeeDto.class) .registerWriteHandler(new CellStyleStrategy(new WriteCellStyle(), new WriteCellStyle())) .registerWriteHandler(new CellWidthStyleStrategy()) .registerWriteHandler(new CellRowHeightStyleStrategy()) .registerWriteHandler(new CustomMergeStrategy(mergeColDataList, mergeColIndexList)) .sheet(name) .doWrite(list); 附上模板下载的工具类
exportNoModel方法是下载模板文件，生成文件的表头为传入的headMap。
download方法适用于本地已存在模板文件的场景，fileName为浏览器下载的文件名称（可传中文），filePath为文件所处的位置。
import com.alibaba.excel.EasyExcel;import com.alibaba.excel.write.metadata.style.WriteCellStyle;import lombok.extern.slf4j.Slf4j;import org.springframework.core.io.ClassPathResource;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.net.URLEncoder;import java.util.ArrayList;import java.util.Arrays;import java.util.List;@Slf4jpublic class ExcelUtil { /** * 下载模板文件 * @param response * @param headMap * @param fileName * @param dataList */ public static void exportNoModel(HttpServletResponse response, String[] headMap, String fileName, List<List<Object>> dataList) { try { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); String name = URLEncoder.encode(fileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + name + ".xlsx"); EasyExcel.write(response.getOutputStream()).head(createdHead(headMap)) .registerWriteHandler(new CellStyleStrategy(new WriteCellStyle(), new WriteCellStyle())) .registerWriteHandler(new CellWidthStyleStrategy()) .registerWriteHandler(new CellRowHeightStyleStrategy()) .sheet(fileName).doWrite(dataList); } catch (IOException e) { log.error("导出异常-->", e); } } /** * 下载模板文件 * @param response * @param fileName * @param filePath */ public static void download(HttpServletResponse response, String fileName, String filePath){ try { response.setContentType("application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8")); response.setHeader("filename", URLEncoder.encode(fileName, "UTF-8")); response.setHeader("Access-Control-Expose-Headers", "filename,Content-Disposition"); ClassPathResource resource = new ClassPathResource(filePath); InputStream input = resource.getInputStream(); OutputStream out = response.getOutputStream(); byte[] b = new byte[2048]; int len; while ((len = input.read(b)) != -1) { out.write(b, 0, len); } input.close(); } catch (Exception e) { log.error("下载模板失败 :", e); } } /** * 处理字段的表头值 * @param headMap * @return */ private static List<List<String>> createdHead(String[] headMap) { List<List<String>> headList = new ArrayList<List<String>>(); for (String head : headMap) { List<String> list = new ArrayList<String>(); list.add(head); headList.add(list); } return headList; }}