From 0a104689ed76e9ab7a98270c66f82d3fc26a9305 Mon Sep 17 00:00:00 2001
From: RuoYi <yzz_ivy@163.com>
Date: Wed, 22 Sep 2021 09:03:21 +0800
Subject: [PATCH] Excel注解支持自定义数据处理器
---
ruoyi-common/ruoyi-common-core/src/main/java/com/ruoyi/common/core/utils/poi/ExcelUtil.java | 274 +++++++++++++++++++++++++++++++++++++++++-------------
1 files changed, 207 insertions(+), 67 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 afb965f..cf5dda3 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
@@ -17,15 +17,16 @@
import java.util.Set;
import java.util.stream.Collectors;
import javax.servlet.http.HttpServletResponse;
-import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
+import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
@@ -36,7 +37,9 @@
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddressList;
+import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
+import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@@ -47,6 +50,8 @@
import com.ruoyi.common.core.text.Convert;
import com.ruoyi.common.core.utils.DateUtils;
import com.ruoyi.common.core.utils.StringUtils;
+import com.ruoyi.common.core.utils.file.FileTypeUtils;
+import com.ruoyi.common.core.utils.file.ImageUtils;
import com.ruoyi.common.core.utils.reflect.ReflectUtils;
/**
@@ -97,6 +102,11 @@
* 注解列表
*/
private List<Object[]> fields;
+
+ /**
+ * 最大高度
+ */
+ private short maxHeight;
/**
* 统计列表
@@ -171,7 +181,8 @@
throw new IOException("文件sheet不存在");
}
- int rows = sheet.getPhysicalNumberOfRows();
+ // 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1
+ int rows = sheet.getLastRowNum();
if (rows > 0)
{
@@ -211,10 +222,15 @@
}
}
}
- for (int i = 1; i < rows; i++)
+ for (int i = 1; i <= rows; i++)
{
// 从第2行开始取数据,默认第一行是表头.
Row row = sheet.getRow(i);
+ // 判断当前行是否是空行
+ if (isRowEmpty(row))
+ {
+ continue;
+ }
T entity = null;
for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet())
{
@@ -235,22 +251,30 @@
}
else
{
- val = Convert.toStr(val);
+ String dateFormat = field.getAnnotation(Excel.class).dateFormat();
+ if (StringUtils.isNotEmpty(dateFormat))
+ {
+ val = DateUtils.parseDateToStr(dateFormat, (Date) val);
+ }
+ else
+ {
+ val = Convert.toStr(val);
+ }
}
}
- else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType))
+ else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))
{
val = Convert.toInt(val);
}
- else if ((Long.TYPE == fieldType) || (Long.class == fieldType))
+ else if (Long.TYPE == fieldType || Long.class == fieldType)
{
val = Convert.toLong(val);
}
- else if ((Double.TYPE == fieldType) || (Double.class == fieldType))
+ else if (Double.TYPE == fieldType || Double.class == fieldType)
{
val = Convert.toDouble(val);
}
- else if ((Float.TYPE == fieldType) || (Float.class == fieldType))
+ else if (Float.TYPE == fieldType || Float.class == fieldType)
{
val = Convert.toFloat(val);
}
@@ -269,6 +293,10 @@
val = DateUtil.getJavaDate((Double) val);
}
}
+ else if (Boolean.TYPE == fieldType || Boolean.class == fieldType)
+ {
+ val = Convert.toBool(val, false);
+ }
if (StringUtils.isNotNull(fieldType))
{
Excel attr = field.getAnnotation(Excel.class);
@@ -280,6 +308,10 @@
else if (StringUtils.isNotEmpty(attr.readConverterExp()))
{
val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
+ }
+ else if (!attr.handler().equals(ExcelHandlerAdapter.class))
+ {
+ val = dataFormatHandlerAdapter(val, attr);
}
ReflectUtils.invokeSetter(entity, propertyName, val);
}
@@ -301,7 +333,7 @@
*/
public void exportExcel(HttpServletResponse response, List<T> list, String sheetName) throws IOException
{
- response.setContentType("application/vnd.ms-excel");
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
this.init(list, sheetName, Type.EXPORT);
exportExcel(response.getOutputStream());
@@ -315,7 +347,7 @@
*/
public void importTemplateExcel(HttpServletResponse response, String sheetName) throws IOException
{
- response.setContentType("application/vnd.ms-excel");
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
this.init(null, sheetName, Type.IMPORT);
exportExcel(response.getOutputStream());
@@ -326,32 +358,12 @@
*
* @return 结果
*/
- public void exportExcel(OutputStream outputStream)
+ public void exportExcel(OutputStream out)
{
try
{
- // 取出一共有多少个sheet.
- double sheetNo = Math.ceil(list.size() / sheetSize);
- for (int index = 0; index <= sheetNo; index++)
- {
- createSheet(sheetNo, index);
-
- // 产生一行
- Row row = sheet.createRow(0);
- int column = 0;
- // 写入各个字段的列头名称
- for (Object[] os : fields)
- {
- Excel excel = (Excel) os[1];
- this.createCell(excel, row, column++);
- }
- if (Type.EXPORT.equals(type))
- {
- fillExcelData(index, row);
- addStatisticsRow();
- }
- }
- wb.write(outputStream);
+ writeSheet();
+ wb.write(out);
}
catch (Exception e)
{
@@ -359,27 +371,35 @@
}
finally
{
- if (wb != null)
+ IOUtils.closeQuietly(wb);
+ IOUtils.closeQuietly(out);
+ }
+ }
+
+ /**
+ * 创建写入数据到Sheet
+ */
+ public void writeSheet()
+ {
+ // 取出一共有多少个sheet.
+ double sheetNo = Math.ceil(list.size() / sheetSize);
+ for (int index = 0; index <= sheetNo; index++)
+ {
+ createSheet(sheetNo, index);
+
+ // 产生一行
+ Row row = sheet.createRow(0);
+ int column = 0;
+ // 写入各个字段的列头名称
+ for (Object[] os : fields)
{
- try
- {
- wb.close();
- }
- catch (IOException e1)
- {
- e1.printStackTrace();
- }
+ Excel excel = (Excel) os[1];
+ this.createCell(excel, row, column++);
}
- if (outputStream != null)
+ if (Type.EXPORT.equals(type))
{
- try
- {
- outputStream.close();
- }
- catch (IOException e1)
- {
- e1.printStackTrace();
- }
+ fillExcelData(index, row);
+ addStatisticsRow();
}
}
}
@@ -461,6 +481,21 @@
style.setFont(totalFont);
styles.put("total", style);
+ style = wb.createCellStyle();
+ style.cloneStyleFrom(styles.get("data"));
+ style.setAlignment(HorizontalAlignment.LEFT);
+ styles.put("data1", style);
+
+ style = wb.createCellStyle();
+ style.cloneStyleFrom(styles.get("data"));
+ style.setAlignment(HorizontalAlignment.CENTER);
+ styles.put("data2", style);
+
+ style = wb.createCellStyle();
+ style.cloneStyleFrom(styles.get("data"));
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ styles.put("data3", style);
+
return styles;
}
@@ -489,14 +524,55 @@
{
if (ColumnType.STRING == attr.cellType())
{
- cell.setCellType(CellType.STRING);
cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
}
else if (ColumnType.NUMERIC == attr.cellType())
{
- cell.setCellType(CellType.NUMERIC);
- cell.setCellValue(Integer.parseInt(value + ""));
+ if (StringUtils.isNotNull(value))
+ {
+ cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
+ }
}
+ else if (ColumnType.IMAGE == attr.cellType())
+ {
+ ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
+ String imagePath = Convert.toStr(value);
+ if (StringUtils.isNotEmpty(imagePath))
+ {
+ byte[] data = ImageUtils.getImage(imagePath);
+ getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
+ cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
+ }
+ }
+ }
+
+ /**
+ * 获取画布
+ */
+ public static Drawing<?> getDrawingPatriarch(Sheet sheet)
+ {
+ if (sheet.getDrawingPatriarch() == null)
+ {
+ sheet.createDrawingPatriarch();
+ }
+ return sheet.getDrawingPatriarch();
+ }
+
+ /**
+ * 获取图片类型,设置图片插入类型
+ */
+ public int getImageType(byte[] value)
+ {
+ String type = FileTypeUtils.getFileExtendName(value);
+ if ("JPG".equalsIgnoreCase(type))
+ {
+ return Workbook.PICTURE_TYPE_JPEG;
+ }
+ else if ("PNG".equalsIgnoreCase(type))
+ {
+ return Workbook.PICTURE_TYPE_PNG;
+ }
+ return Workbook.PICTURE_TYPE_JPEG;
}
/**
@@ -512,7 +588,6 @@
{
// 设置列宽
sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
- row.setHeight((short) (attr.height() * 20));
}
// 如果设置了提示信息则鼠标放上去提示.
if (StringUtils.isNotEmpty(attr.prompt()))
@@ -537,13 +612,14 @@
try
{
// 设置行高
- row.setHeight((short) (attr.height() * 20));
+ row.setHeight(maxHeight);
// 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
if (attr.isExport())
{
// 创建cell
cell = row.createCell(column);
- cell.setCellStyle(styles.get("data"));
+ int align = attr.align().value();
+ cell.setCellStyle(styles.get("data" + (align >= 1 && align <= 3 ? align : "")));
// 用于读取对象中的属性
Object value = getTargetValue(vo, field, attr);
@@ -561,6 +637,10 @@
else if (value instanceof BigDecimal && -1 != attr.scale())
{
cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());
+ }
+ else if (!attr.handler().equals(ExcelHandlerAdapter.class))
+ {
+ cell.setCellValue(dataFormatHandlerAdapter(value, attr));
}
else
{
@@ -709,6 +789,28 @@
}
/**
+ * 数据处理器
+ *
+ * @param value 数据值
+ * @param excel 数据注解
+ * @return
+ */
+ public String dataFormatHandlerAdapter(Object value, Excel excel)
+ {
+ try
+ {
+ Object instance = excel.handler().newInstance();
+ Method formatMethod = excel.handler().getMethod("format", new Class[] { Object.class, String[].class });
+ value = formatMethod.invoke(instance, value, excel.args());
+ }
+ catch (Exception e)
+ {
+ log.error("不能格式化数据 " + excel.handler(), e.getMessage());
+ }
+ return Convert.toStr(value);
+ }
+
+ /**
* 合计统计信息
*/
private void addStatisticsData(Integer index, String text, Excel entity)
@@ -796,12 +898,12 @@
*/
private Object getValue(Object o, String name) throws Exception
{
- if (StringUtils.isNotEmpty(name))
+ if (StringUtils.isNotNull(o) && StringUtils.isNotEmpty(name))
{
Class<?> clazz = o.getClass();
- String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
- Method method = clazz.getMethod(methodName);
- o = method.invoke(o);
+ Field field = clazz.getDeclaredField(name);
+ field.setAccessible(true);
+ o = field.get(o);
}
return o;
}
@@ -835,6 +937,21 @@
}
}
this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
+ this.maxHeight = getRowHeight();
+ }
+
+ /**
+ * 根据注解获取最大行高
+ */
+ public short getRowHeight()
+ {
+ double maxHeight = 0;
+ for (Object[] os : this.fields)
+ {
+ Excel excel = (Excel) os[1];
+ maxHeight = maxHeight > excel.height() ? maxHeight : excel.height();
+ }
+ return (short) (maxHeight * 20);
}
/**
@@ -896,16 +1013,16 @@
Cell cell = row.getCell(column);
if (StringUtils.isNotNull(cell))
{
- if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)
+ if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA)
{
val = cell.getNumericCellValue();
- if (HSSFDateUtil.isCellDateFormatted(cell))
+ if (DateUtil.isCellDateFormatted(cell))
{
val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
}
else
{
- if ((Double) val % 1 > 0)
+ if ((Double) val % 1 != 0)
{
val = new BigDecimal(val.toString());
}
@@ -915,15 +1032,15 @@
}
}
}
- else if (cell.getCellTypeEnum() == CellType.STRING)
+ else if (cell.getCellType() == CellType.STRING)
{
val = cell.getStringCellValue();
}
- else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
+ else if (cell.getCellType() == CellType.BOOLEAN)
{
val = cell.getBooleanCellValue();
}
- else if (cell.getCellTypeEnum() == CellType.ERROR)
+ else if (cell.getCellType() == CellType.ERROR)
{
val = cell.getErrorCellValue();
}
@@ -936,4 +1053,27 @@
}
return val;
}
+
+ /**
+ * 判断是否是空行
+ *
+ * @param row 判断的行
+ * @return
+ */
+ private boolean isRowEmpty(Row row)
+ {
+ if (row == null)
+ {
+ return true;
+ }
+ for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
+ {
+ Cell cell = row.getCell(i);
+ if (cell != null && cell.getCellType() != CellType.BLANK)
+ {
+ return false;
+ }
+ }
+ return true;
+ }
}
\ No newline at end of file
--
Gitblit v1.9.3