From a92ca233aa327cc79073f4a5e5608f6e80223024 Mon Sep 17 00:00:00 2001
From: RuoYi <yzz_ivy@163.com>
Date: Wed, 10 Aug 2022 18:45:55 +0800
Subject: [PATCH] 优化导出对象的子列表判断条件
---
ruoyi-common/ruoyi-common-core/src/main/java/com/ruoyi/common/core/utils/poi/ExcelUtil.java | 230 ++++++++++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 208 insertions(+), 22 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 c813f85..f170010 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
@@ -4,12 +4,14 @@
import java.io.InputStream;
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;
@@ -20,6 +22,7 @@
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.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
@@ -127,6 +130,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>();
@@ -175,6 +198,7 @@
createExcelField();
createWorkbook();
createTitle();
+ createSubHead();
}
/**
@@ -184,19 +208,54 @@
{
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++;
}
}
/**
* 对excel表单默认第一个索引名转换成list
- *
+ *
* @param is 输入流
* @return 转换后集合
*/
@@ -207,7 +266,7 @@
/**
* 对excel表单默认第一个索引名转换成list
- *
+ *
* @param is 输入流
* @param titleNum 标题占用行数
* @return 转换后集合
@@ -219,7 +278,7 @@
/**
* 对excel表单指定表格索引名转换成list
- *
+ *
* @param sheetName 表格索引名
* @param titleNum 标题占用行数
* @param is 输入流
@@ -378,7 +437,6 @@
* @param list 导出数据集合
* @param sheetName 工作表的名称
* @return 结果
- * @throws IOException
*/
public void exportExcel(HttpServletResponse response, List<T> list, String sheetName)
{
@@ -393,7 +451,6 @@
* @param sheetName 工作表的名称
* @param title 标题
* @return 结果
- * @throws IOException
*/
public void exportExcel(HttpServletResponse response, List<T> list, String sheetName, String title)
{
@@ -431,7 +488,7 @@
/**
* 对list数据源将其里面的数据导入到excel表单
- *
+ *
* @return 结果
*/
public void exportExcel(HttpServletResponse response)
@@ -468,8 +525,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))
{
@@ -481,32 +550,71 @@
/**
* 填充excel数据
- *
+ *
* @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 = i > 1 ? rowNo + 1 : rowNo + i;
+ row = sheet.createRow(rowNo);
// 得到导出对象.
T vo = (T) list.get(i);
+ Collection<?> subList = null;
+ if (isSubListValue(vo))
+ {
+ subList = getListCellValue(vo);
+ subMergedLastRowNum = subMergedLastRowNum + subList.size();
+ }
+
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++);
+ }
}
}
}
/**
* 创建表格样式
- *
+ *
* @param wb 工作薄对象
* @return 样式列表
*/
@@ -634,7 +742,7 @@
/**
* 创建单元格
*/
- public Cell createCell(Excel attr, Row row, int column)
+ public Cell createHeadCell(Excel attr, Row row, int column)
{
// 创建列
Cell cell = row.createCell(column);
@@ -642,12 +750,21 @@
cell.setCellValue(attr.name());
setDataValidation(attr, row, column);
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;
}
/**
* 设置单元格信息
- *
+ *
* @param value 单元格值
* @param attr 注解相关
* @param cell 单元格信息
@@ -749,6 +866,11 @@
{
// 创建cell
cell = row.createCell(column);
+ 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())));
// 用于读取对象中的属性
@@ -766,7 +888,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))
{
@@ -839,7 +961,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))
{
@@ -863,7 +985,7 @@
/**
* 反向解析值 男=0,女=1,未知=2
- *
+ *
* @param propertyValue 参数值
* @param converterExp 翻译注解
* @param separator 分隔符
@@ -876,7 +998,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))
{
@@ -1049,6 +1171,13 @@
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);
+ }
}
// 多注解
@@ -1097,7 +1226,7 @@
/**
* 创建工作表
- *
+ *
* @param sheetNo sheet数量
* @param index 序号
*/
@@ -1114,7 +1243,7 @@
/**
* 获取单元格值
- *
+ *
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
@@ -1174,7 +1303,7 @@
/**
* 判断是否是空行
- *
+ *
* @param row 判断的行
* @return
*/
@@ -1227,4 +1356,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.3