一、概述

        当有需求下载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.*;

@Slf4j

public 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() {

XSSFRow startRow = this.mainSheet.createRow(0);//新增一行

// 创建单元格风格样式

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);

XSSFCell startCell1 = startRow.createCell(0);//行中的第“0+1”列

startCell1.setCellValue("供货日期");//列中放的值

startCell1.setCellStyle(cellStyle);

XSSFCell startCell2 = startRow.createCell(1);

startCell2.setCellValue("供应商");

startCell2.setCellStyle(cellStyle);

XSSFCell startCell3 = startRow.createCell(2);

startCell3.setCellValue("食堂");

startCell3.setCellStyle(cellStyle);

XSSFCell startCell4 = startRow.createCell(3);

startCell4.setCellValue("商品");

startCell4.setCellStyle(cellStyle);

XSSFCell startCell5 = startRow.createCell(4);

startCell5.setCellValue("规格");

startCell5.setCellStyle(cellStyle);

XSSFCell startCell6 = startRow.createCell(5);

startCell6.setCellStyle(cellStyle);

startCell6.setCellValue("计量单位");

XSSFCell startCell7 = startRow.createCell(6);

startCell7.setCellValue("单价(元)");

startCell7.setCellStyle(cellStyle);

XSSFCell startCell8 = startRow.createCell(7);

startCell8.setCellStyle(cellStyle);

startCell8.setCellValue("供货数量");//列中放的值

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")

@SneakyThrows

public 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")

@SneakyThrows

public 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;

@Slf4j

public 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;

@Slf4j

public 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;

}

}