From 06fbda53248c3e4dfdb09db2bc05a957f918bfd9 Mon Sep 17 00:00:00 2001
From: RuoYi <yzz_ivy@163.com>
Date: 星期一, 07 十一月 2022 11:20:02 +0800
Subject: [PATCH] 修复sheet超出最大行数异常问题
---
ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java | 329 +++++++++++++++++++++++++++++++++++++++++++++++-------
1 files changed, 283 insertions(+), 46 deletions(-)
diff --git a/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java b/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
index e868511..3310220 100644
--- a/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
+++ b/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
@@ -7,12 +7,14 @@
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
+import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
+import java.util.Collection;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
@@ -22,7 +24,9 @@
import java.util.UUID;
import java.util.stream.Collectors;
import javax.servlet.http.HttpServletResponse;
+import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.RegExUtils;
+import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
@@ -149,6 +153,26 @@
private short maxHeight;
/**
+ * 合并后最后行数
+ */
+ private int subMergedLastRowNum = 0;
+
+ /**
+ * 合并后开始行数
+ */
+ private int subMergedFirstRowNum = 1;
+
+ /**
+ * 对象的子列表方法
+ */
+ private Method subMethod;
+
+ /**
+ * 对象的子列表属性
+ */
+ private List<Field> subFields;
+
+ /**
* 统计列表
*/
private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
@@ -163,9 +187,25 @@
*/
public Class<T> clazz;
+ /**
+ * 需要排除列属性
+ */
+ public String[] excludeFields;
+
public ExcelUtil(Class<T> clazz)
{
this.clazz = clazz;
+ }
+
+ /**
+ * 隐藏Excel中列属性
+ *
+ * @param fields 列属性名 示例[单个"name"/多个"id","name"]
+ * @throws Exception
+ */
+ public void hideColumn(String... fields)
+ {
+ this.excludeFields = fields;
}
public void init(List<T> list, String sheetName, String title, Type type)
@@ -181,6 +221,7 @@
createExcelField();
createWorkbook();
createTitle();
+ createSubHead();
}
/**
@@ -190,13 +231,48 @@
{
if (StringUtils.isNotEmpty(title))
{
+ subMergedFirstRowNum++;
+ subMergedLastRowNum++;
+ int titleLastCol = this.fields.size() - 1;
+ if (isSubList())
+ {
+ titleLastCol = titleLastCol + subFields.size() - 1;
+ }
Row titleRow = sheet.createRow(rownum == 0 ? rownum++ : 0);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
- sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(),
- this.fields.size() - 1));
+ sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), titleLastCol));
+ }
+ }
+
+ /**
+ * 创建对象的子列表名称
+ */
+ public void createSubHead()
+ {
+ if (isSubList())
+ {
+ subMergedFirstRowNum++;
+ subMergedLastRowNum++;
+ Row subRow = sheet.createRow(rownum);
+ int excelNum = 0;
+ for (Object[] objects : fields)
+ {
+ Excel attr = (Excel) objects[1];
+ Cell headCell1 = subRow.createCell(excelNum);
+ headCell1.setCellValue(attr.name());
+ headCell1.setCellStyle(styles.get(StringUtils.format("header_{}_{}", attr.headerColor(), attr.headerBackgroundColor())));
+ excelNum++;
+ }
+ int headFirstRow = excelNum - 1;
+ int headLastRow = headFirstRow + subFields.size() - 1;
+ if (headLastRow > headFirstRow)
+ {
+ sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, headFirstRow, headLastRow));
+ }
+ rownum++;
}
}
@@ -576,8 +652,20 @@
// 写入各个字段的列头名称
for (Object[] os : fields)
{
+ Field field = (Field) os[0];
Excel excel = (Excel) os[1];
- this.createCell(excel, row, column++);
+ if (Collection.class.isAssignableFrom(field.getType()))
+ {
+ for (Field subField : subFields)
+ {
+ Excel subExcel = subField.getAnnotation(Excel.class);
+ this.createHeadCell(subExcel, row, column++);
+ }
+ }
+ else
+ {
+ this.createHeadCell(excel, row, column++);
+ }
}
if (Type.EXPORT.equals(type))
{
@@ -593,21 +681,67 @@
* @param index 序号
* @param row 单元格行
*/
+ @SuppressWarnings("unchecked")
public void fillExcelData(int index, Row row)
{
int startNo = index * sheetSize;
int endNo = Math.min(startNo + sheetSize, list.size());
+ int rowNo = (1 + rownum) - startNo;
for (int i = startNo; i < endNo; i++)
{
- row = sheet.createRow(i + 1 + rownum - startNo);
+ rowNo = isSubList() ? (i > 1 ? rowNo + 1 : rowNo + i) : i + 1 + rownum - startNo;
+ row = sheet.createRow(rowNo);
// 得到导出对象.
T vo = (T) list.get(i);
+ Collection<?> subList = null;
+ if (isSubList())
+ {
+ if (isSubListValue(vo))
+ {
+ subList = getListCellValue(vo);
+ subMergedLastRowNum = subMergedLastRowNum + subList.size();
+ }
+ else
+ {
+ subMergedFirstRowNum++;
+ subMergedLastRowNum++;
+ }
+ }
int column = 0;
for (Object[] os : fields)
{
Field field = (Field) os[0];
Excel excel = (Excel) os[1];
- this.addCell(excel, row, vo, field, column++);
+ if (Collection.class.isAssignableFrom(field.getType()) && StringUtils.isNotNull(subList))
+ {
+ boolean subFirst = false;
+ for (Object obj : subList)
+ {
+ if (subFirst)
+ {
+ rowNo++;
+ row = sheet.createRow(rowNo);
+ }
+ List<Field> subFields = FieldUtils.getFieldsListWithAnnotation(obj.getClass(), Excel.class);
+ int subIndex = 0;
+ for (Field subField : subFields)
+ {
+ if (subField.isAnnotationPresent(Excel.class))
+ {
+ subField.setAccessible(true);
+ Excel attr = subField.getAnnotation(Excel.class);
+ this.addCell(attr, row, (T) obj, subField, column + subIndex);
+ }
+ subIndex++;
+ }
+ subFirst = true;
+ }
+ this.subMergedFirstRowNum = this.subMergedFirstRowNum + subList.size();
+ }
+ else
+ {
+ this.addCell(excel, row, vo, field, column++);
+ }
}
}
}
@@ -650,20 +784,6 @@
styles.put("data", style);
style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.CENTER);
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
- Font headerFont = wb.createFont();
- headerFont.setFontName("Arial");
- headerFont.setFontHeightInPoints((short) 10);
- headerFont.setBold(true);
- headerFont.setColor(IndexedColors.WHITE.getIndex());
- style.setFont(headerFont);
- styles.put("header", style);
-
- style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
Font totalFont = wb.createFont();
@@ -672,28 +792,62 @@
style.setFont(totalFont);
styles.put("total", style);
- styles.putAll(annotationStyles(wb));
+ styles.putAll(annotationHeaderStyles(wb, styles));
+
+ styles.putAll(annotationDataStyles(wb));
return styles;
}
/**
- * 根据Excel注解创建表格样式
+ * 根据Excel注解创建表格头样式
*
* @param wb 工作薄对象
* @return 自定义样式列表
*/
- private Map<String, CellStyle> annotationStyles(Workbook wb)
+ private Map<String, CellStyle> annotationHeaderStyles(Workbook wb, Map<String, CellStyle> styles)
+ {
+ Map<String, CellStyle> headerStyles = new HashMap<String, CellStyle>();
+ for (Object[] os : fields)
+ {
+ Excel excel = (Excel) os[1];
+ String key = StringUtils.format("header_{}_{}", excel.headerColor(), excel.headerBackgroundColor());
+ if (!headerStyles.containsKey(key))
+ {
+ CellStyle style = wb.createCellStyle();
+ style.cloneStyleFrom(styles.get("data"));
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFillForegroundColor(excel.headerBackgroundColor().index);
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ Font headerFont = wb.createFont();
+ headerFont.setFontName("Arial");
+ headerFont.setFontHeightInPoints((short) 10);
+ headerFont.setBold(true);
+ headerFont.setColor(excel.headerColor().index);
+ style.setFont(headerFont);
+ headerStyles.put(key, style);
+ }
+ }
+ return headerStyles;
+ }
+
+ /**
+ * 根据Excel注解创建表格列样式
+ *
+ * @param wb 工作薄对象
+ * @return 自定义样式列表
+ */
+ private Map<String, CellStyle> annotationDataStyles(Workbook wb)
{
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
for (Object[] os : fields)
{
Excel excel = (Excel) os[1];
- String key = "data_" + excel.align() + "_" + excel.color();
+ String key = StringUtils.format("data_{}_{}_{}", excel.align(), excel.color(), excel.backgroundColor());
if (!styles.containsKey(key))
{
CellStyle style = wb.createCellStyle();
- style = wb.createCellStyle();
style.setAlignment(excel.align());
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderRight(BorderStyle.THIN);
@@ -704,6 +858,8 @@
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setFillForegroundColor(excel.backgroundColor().getIndex());
Font dataFont = wb.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
@@ -718,14 +874,23 @@
/**
* 创建单元格
*/
- public Cell createCell(Excel attr, Row row, int column)
+ public Cell createHeadCell(Excel attr, Row row, int column)
{
// 创建列
Cell cell = row.createCell(column);
// 写入列信息
cell.setCellValue(attr.name());
setDataValidation(attr, row, column);
- cell.setCellStyle(styles.get("header"));
+ cell.setCellStyle(styles.get(StringUtils.format("header_{}_{}", attr.headerColor(), attr.headerBackgroundColor())));
+ if (isSubList())
+ {
+ // 填充默认样式，防止合并单元格样式失效
+ sheet.setDefaultColumnStyle(column, styles.get(StringUtils.format("data_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor())));
+ if (attr.needMerge())
+ {
+ sheet.addMergedRegion(new CellRangeAddress(rownum - 1, rownum, column, column));
+ }
+ }
return cell;
}
@@ -833,7 +998,12 @@
{
// 创建cell
cell = row.createCell(column);
- cell.setCellStyle(styles.get("data_" + attr.align() + "_" + attr.color()));
+ if (isSubListValue(vo) && getListCellValue(vo).size() > 1 && attr.needMerge())
+ {
+ CellRangeAddress cellAddress = new CellRangeAddress(subMergedFirstRowNum, subMergedLastRowNum, column, column);
+ sheet.addMergedRegion(cellAddress);
+ }
+ cell.setCellStyle(styles.get(StringUtils.format("data_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor())));
// 用于读取对象中的属性
Object value = getTargetValue(vo, field, attr);
@@ -855,7 +1025,7 @@
}
else if (value instanceof BigDecimal && -1 != attr.scale())
{
- cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());
+ cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).doubleValue());
}
else if (!attr.handler().equals(ExcelHandlerAdapter.class))
{
@@ -928,7 +1098,7 @@
for (String item : convertSource)
{
String[] itemArray = item.split("=");
- if (StringUtils.containsAny(separator, propertyValue))
+ if (StringUtils.containsAny(propertyValue, separator))
{
for (String value : propertyValue.split(separator))
{
@@ -965,7 +1135,7 @@
for (String item : convertSource)
{
String[] itemArray = item.split("=");
- if (StringUtils.containsAny(separator, propertyValue))
+ if (StringUtils.containsAny(propertyValue, separator))
{
for (String value : propertyValue.split(separator))
{
@@ -1178,28 +1348,38 @@
tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
for (Field field : tempFields)
{
- // 单注解
- if (field.isAnnotationPresent(Excel.class))
+ if (!ArrayUtils.contains(this.excludeFields, field.getName()))
{
- Excel attr = field.getAnnotation(Excel.class);
- if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
+ // 单注解
+ if (field.isAnnotationPresent(Excel.class))
{
- field.setAccessible(true);
- fields.add(new Object[] { field, attr });
- }
- }
-
- // 多注解
- if (field.isAnnotationPresent(Excels.class))
- {
- Excels attrs = field.getAnnotation(Excels.class);
- Excel[] excels = attrs.value();
- for (Excel attr : excels)
- {
+ Excel attr = field.getAnnotation(Excel.class);
if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
{
field.setAccessible(true);
fields.add(new Object[] { field, attr });
+ }
+ if (Collection.class.isAssignableFrom(field.getType()))
+ {
+ subMethod = getSubMethod(field.getName(), clazz);
+ ParameterizedType pt = (ParameterizedType) field.getGenericType();
+ Class<?> subClass = (Class<?>) pt.getActualTypeArguments()[0];
+ this.subFields = FieldUtils.getFieldsListWithAnnotation(subClass, Excel.class);
+ }
+ }
+
+ // 多注解
+ if (field.isAnnotationPresent(Excels.class))
+ {
+ Excels attrs = field.getAnnotation(Excels.class);
+ Excel[] excels = attrs.value();
+ for (Excel attr : excels)
+ {
+ if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
+ {
+ field.setAccessible(true);
+ fields.add(new Object[] { field, attr });
+ }
}
}
}
@@ -1429,4 +1609,61 @@
}
return str;
}
+
+ /**
+ * 是否有对象的子列表
+ */
+ public boolean isSubList()
+ {
+ return StringUtils.isNotNull(subFields) && subFields.size() > 0;
+ }
+
+ /**
+ * 是否有对象的子列表，集合不为空
+ */
+ public boolean isSubListValue(T vo)
+ {
+ return StringUtils.isNotNull(subFields) && subFields.size() > 0 && StringUtils.isNotNull(getListCellValue(vo)) && getListCellValue(vo).size() > 0;
+ }
+
+ /**
+ * 获取集合的值
+ */
+ public Collection<?> getListCellValue(Object obj)
+ {
+ Object value;
+ try
+ {
+ value = subMethod.invoke(obj, new Object[] {});
+ }
+ catch (Exception e)
+ {
+ return new ArrayList<Object>();
+ }
+ return (Collection<?>) value;
+ }
+
+ /**
+ * 获取对象的子列表方法
+ *
+ * @param name 名称
+ * @param pojoClass 类对象
+ * @return 子列表方法
+ */
+ public Method getSubMethod(String name, Class<?> pojoClass)
+ {
+ StringBuffer getMethodName = new StringBuffer("get");
+ getMethodName.append(name.substring(0, 1).toUpperCase());
+ getMethodName.append(name.substring(1));
+ Method method = null;
+ try
+ {
+ method = pojoClass.getMethod(getMethodName.toString(), new Class[] {});
+ }
+ catch (Exception e)
+ {
+ log.error("获取对象异常{}", e.getMessage());
+ }
+ return method;
+ }
}
--
Gitblit v1.9.2