From 397971378357db417763bd388c25cdeeaed5d33c Mon Sep 17 00:00:00 2001
From: RuoYi <yzz_ivy@163.com>
Date: Fri, 17 Apr 2026 14:47:43 +0800
Subject: [PATCH] 用户密码支持自定义配置规则
---
ruoyi-common/ruoyi-common-core/src/main/java/com/ruoyi/common/core/utils/poi/ExcelUtil.java | 221 +++++++++++++++++++++++++++++++++++++++++++++----------
1 files changed, 180 insertions(+), 41 deletions(-)
diff --git a/ruoyi-common/ruoyi-common-core/src/main/java/com/ruoyi/common/core/utils/poi/ExcelUtil.java b/ruoyi-common/ruoyi-common-core/src/main/java/com/ruoyi/common/core/utils/poi/ExcelUtil.java
index e4651f1..1320e8b 100644
--- a/ruoyi-common/ruoyi-common-core/src/main/java/com/ruoyi/common/core/utils/poi/ExcelUtil.java
+++ b/ruoyi-common/ruoyi-common-core/src/main/java/com/ruoyi/common/core/utils/poi/ExcelUtil.java
@@ -19,7 +19,7 @@
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;
-import javax.servlet.http.HttpServletResponse;
+import jakarta.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.RegExUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
@@ -78,6 +78,11 @@
public static final String FORMULA_REGEX_STR = "=|-|\\+|@";
public static final String[] FORMULA_STR = { "=", "-", "+", "@" };
+
+ /**
+ * 单元格样式缓存
+ */
+ private Map<String, CellStyle> cellStyleCache = new HashMap<String, CellStyle>();
/**
* Excel sheet最大行数，默认65536
@@ -147,12 +152,12 @@
/**
* 对象的子列表方法
*/
- private Method subMethod;
+ private Map<String, Method> subMethods;
/**
* 对象的子列表属性
*/
- private List<Field> subFields;
+ private Map<String, List<Field>> subFieldsMap;
/**
* 统计列表
@@ -225,7 +230,10 @@
int titleLastCol = this.fields.size() - 1;
if (isSubList())
{
- titleLastCol = titleLastCol + subFields.size() - 1;
+ for (List<Field> currentSubFields : subFieldsMap.values())
+ {
+ titleLastCol = titleLastCol + currentSubFields.size() - 1;
+ }
}
Row titleRow = sheet.createRow(rownum == 0 ? rownum++ : 0);
titleRow.setHeightInPoints(30);
@@ -245,16 +253,17 @@
{
Row subRow = sheet.createRow(rownum);
int column = 0;
- int subFieldSize = subFields != null ? subFields.size() : 0;
for (Object[] objects : fields)
{
Field field = (Field) objects[0];
Excel attr = (Excel) objects[1];
+ CellStyle cellStyle = styles.get(StringUtils.format("header_{}_{}", attr.headerColor(), attr.headerBackgroundColor()));
if (Collection.class.isAssignableFrom(field.getType()))
{
Cell cell = subRow.createCell(column);
cell.setCellValue(attr.name());
- cell.setCellStyle(styles.get(StringUtils.format("header_{}_{}", attr.headerColor(), attr.headerBackgroundColor())));
+ cell.setCellStyle(cellStyle);
+ int subFieldSize = subFieldsMap != null ? subFieldsMap.get(field.getName()).size() : 0;
if (subFieldSize > 1)
{
CellRangeAddress cellAddress = new CellRangeAddress(rownum, rownum, column, column + subFieldSize - 1);
@@ -266,7 +275,7 @@
{
Cell cell = subRow.createCell(column++);
cell.setCellValue(attr.name());
- cell.setCellStyle(styles.get(StringUtils.format("header_{}_{}", attr.headerColor(), attr.headerBackgroundColor())));
+ cell.setCellStyle(cellStyle);
}
}
rownum++;
@@ -338,17 +347,17 @@
Map<String, Integer> cellMap = new HashMap<String, Integer>();
// 获取表头
Row heard = sheet.getRow(titleNum);
- for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)
+ if (heard == null)
+ {
+ throw new UtilException("文件标题行为空，请检查Excel文件格式");
+ }
+ for (int i = 0; i < heard.getLastCellNum(); i++)
{
Cell cell = heard.getCell(i);
if (StringUtils.isNotNull(cell))
{
String value = this.getCellValue(heard, i).toString();
cellMap.put(value, i);
- }
- else
- {
- cellMap.put(null, i);
}
}
// 有数据时才处理 得到类的所有field.
@@ -378,7 +387,7 @@
Object val = this.getCellValue(row, entry.getKey());
// 如果不存在实例则新建.
- entity = (entity == null ? clazz.newInstance() : entity);
+ entity = (entity == null ? clazz.getDeclaredConstructor().newInstance() : entity);
// 从map中得到对应列的field.
Field field = (Field) entry.getValue()[0];
Excel attr = (Excel) entry.getValue()[1];
@@ -494,6 +503,82 @@
}
/**
+ * 多 Sheet 导出 —— 将多个不同类型的数据集合写入同一 Excel，直接输出到 HttpServletResponse
+ *
+ * @param response HTTP 响应
+ * @param sheets Sheet 描述列表
+ */
+ public static void exportMultiSheet(HttpServletResponse response, List<ExcelSheet<?>> sheets)
+ {
+ if (sheets == null || sheets.isEmpty())
+ {
+ return;
+ }
+ SXSSFWorkbook wb = buildWorkbook(sheets);
+ try
+ {
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
+ response.setCharacterEncoding("utf-8");
+ wb.write(response.getOutputStream());
+ }
+ catch (Exception e)
+ {
+ log.error("多Sheet导出Excel异常{}", e.getMessage());
+ }
+ finally
+ {
+ IOUtils.closeQuietly(wb);
+ }
+ }
+
+ /**
+ * 构建多 Sheet Workbook —— 创建 SXSSFWorkbook 并将所有 Sheet 数据写入
+ *
+ * @param sheets Sheet 描述列表
+ * @return 已写入所有 Sheet 数据的 SXSSFWorkbook
+ */
+ @SuppressWarnings({ "rawtypes", "unchecked" })
+ private static SXSSFWorkbook buildWorkbook(List<ExcelSheet<?>> sheets)
+ {
+ SXSSFWorkbook wb = new SXSSFWorkbook(500);
+ for (ExcelSheet<?> excelSheet : sheets)
+ {
+ ExcelUtil util = new ExcelUtil(excelSheet.getClazz());
+ util.initWithWorkbook(wb, excelSheet.getList(), excelSheet.getSheetName(), excelSheet.getTitle());
+ util.writeSheet();
+ }
+ return wb;
+ }
+
+ /**
+ * 使用外部传入的 Workbook 初始化（多 Sheet 导出专用）
+ * 与 init() 的区别：不新建 Workbook，而是在已有 wb 上追加新 Sheet
+ *
+ * @param wb 已有工作簿
+ * @param list 数据集合
+ * @param sheetName Sheet 名称
+ * @param title 大标题（可为空）
+ */
+ public void initWithWorkbook(SXSSFWorkbook wb, List<T> list, String sheetName, String title)
+ {
+ if (list == null)
+ {
+ list = new ArrayList<T>();
+ }
+ this.list = list;
+ this.sheetName = sheetName;
+ this.title = title != null ? title : "";
+ this.type = Type.EXPORT;
+ this.rownum = 0;
+ this.wb = wb;
+ this.sheet = wb.createSheet(sheetName);
+ createExcelField();
+ this.styles = createStyles(wb);
+ createTitle();
+ createSubHead();
+ }
+
+ /**
* 对list数据源将其里面的数据导入到excel表单
*
* @param sheetName 工作表的名称
@@ -562,7 +647,8 @@
Excel excel = (Excel) os[1];
if (Collection.class.isAssignableFrom(field.getType()))
{
- for (Field subField : subFields)
+ List<Field> currentSubFields = subFieldsMap.get(field.getName());
+ for (Field subField : currentSubFields)
{
Excel subExcel = subField.getAnnotation(Excel.class);
this.createHeadCell(subExcel, row, column++);
@@ -575,7 +661,7 @@
}
if (Type.EXPORT.equals(type))
{
- fillExcelData(index, row);
+ fillExcelData(index);
addStatisticsRow();
}
}
@@ -585,10 +671,9 @@
* 填充excel数据
*
* @param index 序号
- * @param row 单元格行
*/
@SuppressWarnings("unchecked")
- public void fillExcelData(int index, Row row)
+ public void fillExcelData(int index)
{
int startNo = index * sheetSize;
int endNo = Math.min(startNo + sheetSize, list.size());
@@ -596,7 +681,7 @@
for (int i = startNo; i < endNo; i++)
{
- row = sheet.createRow(currentRowNum);
+ Row row = sheet.createRow(currentRowNum);
T vo = (T) list.get(i);
int column = 0;
int maxSubListSize = getCurrentMaxSubListSize(vo);
@@ -609,6 +694,7 @@
try
{
Collection<?> subList = (Collection<?>) getTargetValue(vo, field, excel);
+ List<Field> currentSubFields = subFieldsMap.get(field.getName());
if (subList != null && !subList.isEmpty())
{
int subIndex = 0;
@@ -621,15 +707,15 @@
}
int subColumn = column;
- for (Field subField : subFields)
+ for (Field subField : currentSubFields)
{
Excel subExcel = subField.getAnnotation(Excel.class);
addCell(subExcel, subRow, (T) subVo, subField, subColumn++);
}
subIndex++;
}
- column += subFields.size();
}
+ column += currentSubFields.size();
}
catch (Exception e)
{
@@ -970,6 +1056,7 @@
/**
* 添加单元格
*/
+ @SuppressWarnings("deprecation")
public Cell addCell(Excel attr, Row row, T vo, Field field, int column)
{
Cell cell = null;
@@ -982,7 +1069,7 @@
{
// 创建cell
cell = row.createCell(column);
- if (isSubListValue(vo) && getListCellValue(vo).size() > 1 && attr.needMerge())
+ if (isSubListValue(vo) && getListCellValue(vo) > 1 && attr.needMerge())
{
if (subMergedLastRowNum >= subMergedFirstRowNum)
{
@@ -998,7 +1085,7 @@
String separator = attr.separator();
if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
{
- cell.getCellStyle().setDataFormat(this.wb.getCreationHelper().createDataFormat().getFormat(dateFormat));
+ cell.setCellStyle(createCellStyle(cell.getCellStyle(), dateFormat));
cell.setCellValue(parseDateToStr(dateFormat, value));
}
else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))
@@ -1026,6 +1113,28 @@
log.error("导出Excel失败{}", e);
}
return cell;
+ }
+
+ /**
+ * 使用自定义格式，同时避免样式污染
+ *
+ * @param cellStyle 从此样式复制
+ * @param format 格式匹配的字符串
+ * @return 格式化后CellStyle对象
+ */
+ private CellStyle createCellStyle(CellStyle cellStyle, String format)
+ {
+ String key = cellStyle.getIndex() + "|" + format;
+ CellStyle cached = cellStyleCache.get(key);
+ if (cached != null)
+ {
+ return cached;
+ }
+ CellStyle style = wb.createCellStyle();
+ style.cloneStyleFrom(cellStyle);
+ style.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat(format));
+ cellStyleCache.put(key, style);
+ return style;
}
/**
@@ -1079,18 +1188,36 @@
public void setXSSFValidationWithHidden(Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow, int firstCol, int endCol)
{
String hideSheetName = "combo_" + firstCol + "_" + endCol;
- Sheet hideSheet = wb.createSheet(hideSheetName); // 用于存储 下拉菜单数据
- for (int i = 0; i < textlist.length; i++)
+ Sheet hideSheet = null;
+ String hideSheetDataName = hideSheetName + "_data";
+ Name name = wb.getName(hideSheetDataName);
+ if (name != null)
{
- hideSheet.createRow(i).createCell(0).setCellValue(textlist[i]);
+ // 名称已存在，尝试从名称的引用中找到sheet名称
+ String refersToFormula = name.getRefersToFormula();
+ if (StringUtils.isNotEmpty(refersToFormula) && refersToFormula.contains("!"))
+ {
+ String sheetNameFromFormula = refersToFormula.substring(0, refersToFormula.indexOf("!"));
+ hideSheet = wb.getSheet(sheetNameFromFormula);
+ }
}
- // 创建名称，可被其他单元格引用
- Name name = wb.createName();
- name.setNameName(hideSheetName + "_data");
- name.setRefersToFormula(hideSheetName + "!$A$1:$A$" + textlist.length);
+
+ if (hideSheet == null)
+ {
+ hideSheet = wb.createSheet(hideSheetName); // 用于存储 下拉菜单数据
+ for (int i = 0; i < textlist.length; i++)
+ {
+ hideSheet.createRow(i).createCell(0).setCellValue(textlist[i]);
+ }
+ // 创建名称，可被其他单元格引用
+ name = wb.createName();
+ name.setNameName(hideSheetDataName);
+ name.setRefersToFormula(hideSheetName + "!$A$1:$A$" + textlist.length);
+ }
+
DataValidationHelper helper = sheet.getDataValidationHelper();
// 加载下拉列表内容
- DataValidationConstraint constraint = helper.createFormulaListConstraint(hideSheetName + "_data");
+ DataValidationConstraint constraint = helper.createFormulaListConstraint(hideSheetDataName);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
@@ -1202,7 +1329,7 @@
{
try
{
- Object instance = excel.handler().newInstance();
+ Object instance = excel.handler().getDeclaredConstructor().newInstance();
Method formatMethod = excel.handler().getMethod("format", new Class[] { Object.class, String[].class, Cell.class, Workbook.class });
value = formatMethod.invoke(instance, value, excel.args(), cell, this.wb);
}
@@ -1328,6 +1455,8 @@
{
List<Object[]> fields = new ArrayList<Object[]>();
List<Field> tempFields = new ArrayList<>();
+ subFieldsMap = new HashMap<>();
+ subMethods = new HashMap<>();
tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
if (StringUtils.isNotEmpty(includeFields))
@@ -1375,10 +1504,11 @@
}
if (Collection.class.isAssignableFrom(field.getType()))
{
- subMethod = getSubMethod(field.getName(), clazz);
+ String fieldName = field.getName();
+ subMethods.put(fieldName, getSubMethod(fieldName, clazz));
ParameterizedType pt = (ParameterizedType) field.getGenericType();
Class<?> subClass = (Class<?>) pt.getActualTypeArguments()[0];
- this.subFields = FieldUtils.getFieldsListWithAnnotation(subClass, Excel.class);
+ subFieldsMap.put(fieldName, FieldUtils.getFieldsListWithAnnotation(subClass, Excel.class));
}
}
@@ -1447,7 +1577,8 @@
{
this.sheet = wb.createSheet();
this.createTitle();
- wb.setSheetName(index, sheetName + index);
+ int actualIndex = wb.getSheetIndex(this.sheet);
+ wb.setSheetName(actualIndex, sheetName + index);
}
}
@@ -1572,7 +1703,7 @@
*/
public boolean isSubList()
{
- return StringUtils.isNotNull(subFields) && subFields.size() > 0;
+ return !StringUtils.isEmpty(subFieldsMap);
}
/**
@@ -1580,24 +1711,32 @@
*/
public boolean isSubListValue(T vo)
{
- return StringUtils.isNotNull(subFields) && subFields.size() > 0 && StringUtils.isNotNull(getListCellValue(vo)) && getListCellValue(vo).size() > 0;
+ return !StringUtils.isEmpty(subFieldsMap) && getListCellValue(vo) > 0;
}
/**
* 获取集合的值
*/
- public Collection<?> getListCellValue(Object obj)
+ public int getListCellValue(Object obj)
{
- Object value;
+ Collection<?> value;
+ int max = 0;
try
{
- value = subMethod.invoke(obj, new Object[] {});
+ for (String s : subMethods.keySet())
+ {
+ value = (Collection<?>) subMethods.get(s).invoke(obj);
+ if (value.size() > max)
+ {
+ max = value.size();
+ }
+ }
}
catch (Exception e)
{
- return new ArrayList<Object>();
+ return 0;
}
- return (Collection<?>) value;
+ return max;
}
/**
--
Gitblit v1.9.3