From 3d6fbb93f56d5221661f7899a00c5a70072e5ee5 Mon Sep 17 00:00:00 2001
From: wugh <328043740@qq.com>
Date: 星期一, 22 三月 2021 09:32:49 +0800
Subject: [PATCH] !11 [功能] 拆分出Vo,QueryBo,AddBo,EditBo等领域对象 * [修改] 错误引入的无用导包删除 * [新增] 增加Vo,QueryBo,AddBo,EditBo等视图,并调整controller,service的调用代码 * [新增] 增加CreateAndUpdateMetaObjectHandler,配合fill注解,实现创建和修改人的自动设置,减少重复代码
---
ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java | 302 ++++++++++++++++++++++++++++++++++++--------------
1 files changed, 216 insertions(+), 86 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 d5a23b1..c1a7ca8 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
@@ -1,58 +1,34 @@
package com.ruoyi.common.utils.poi;
-import java.io.File;
-import java.io.FileOutputStream;
-import java.io.IOException;
-import java.io.InputStream;
-import java.io.OutputStream;
-import java.lang.reflect.Field;
-import java.lang.reflect.Method;
-import java.math.BigDecimal;
-import java.text.DecimalFormat;
-import java.util.ArrayList;
-import java.util.Arrays;
-import java.util.Comparator;
-import java.util.Date;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
-import java.util.UUID;
-import java.util.stream.Collectors;
-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.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.FillPatternType;
-import org.apache.poi.ss.usermodel.Font;
-import org.apache.poi.ss.usermodel.HorizontalAlignment;
-import org.apache.poi.ss.usermodel.IndexedColors;
-import org.apache.poi.ss.usermodel.Row;
-import org.apache.poi.ss.usermodel.Sheet;
-import org.apache.poi.ss.usermodel.VerticalAlignment;
-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.xssf.streaming.SXSSFWorkbook;
-import org.apache.poi.xssf.usermodel.XSSFDataValidation;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
+import cn.hutool.core.convert.Convert;
+import cn.hutool.core.lang.Validator;
+import cn.hutool.core.util.StrUtil;
import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.annotation.Excel.ColumnType;
import com.ruoyi.common.annotation.Excel.Type;
import com.ruoyi.common.annotation.Excels;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.core.domain.AjaxResult;
-import com.ruoyi.common.core.text.Convert;
import com.ruoyi.common.exception.CustomException;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.DictUtils;
-import com.ruoyi.common.utils.StringUtils;
+import com.ruoyi.common.utils.file.FileTypeUtils;
+import com.ruoyi.common.utils.file.ImageUtils;
import com.ruoyi.common.utils.reflect.ReflectUtils;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddressList;
+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;
+
+import java.io.*;
+import java.lang.reflect.Field;
+import java.math.BigDecimal;
+import java.text.DecimalFormat;
+import java.util.*;
+import java.util.stream.Collectors;
/**
* Excel鐩稿叧澶勭悊
@@ -104,6 +80,21 @@
private List<Object[]> fields;
/**
+ * 鏈�澶ч珮搴�
+ */
+ private short maxHeight;
+
+ /**
+ * 缁熻鍒楄〃
+ */
+ private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
+
+ /**
+ * 鏁板瓧鏍煎紡
+ */
+ private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
+
+ /**
* 瀹炰綋瀵硅薄
*/
public Class<T> clazz;
@@ -134,7 +125,7 @@
*/
public List<T> importExcel(InputStream is) throws Exception
{
- return importExcel(StringUtils.EMPTY, is);
+ return importExcel(StrUtil.EMPTY, is);
}
/**
@@ -150,7 +141,7 @@
this.wb = WorkbookFactory.create(is);
List<T> list = new ArrayList<T>();
Sheet sheet = null;
- if (StringUtils.isNotEmpty(sheetName))
+ if (Validator.isNotEmpty(sheetName))
{
// 濡傛灉鎸囧畾sheet鍚�,鍒欏彇鎸囧畾sheet涓殑鍐呭.
sheet = wb.getSheet(sheetName);
@@ -177,7 +168,7 @@
for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)
{
Cell cell = heard.getCell(i);
- if (StringUtils.isNotNull(cell))
+ if (Validator.isNotNull(cell))
{
String value = this.getCellValue(heard, i).toString();
cellMap.put(value, i);
@@ -224,28 +215,36 @@
if (String.class == fieldType)
{
String s = Convert.toStr(val);
- if (StringUtils.endsWith(s, ".0"))
+ if (StrUtil.endWith(s, ".0"))
{
- val = StringUtils.substringBefore(s, ".0");
+ val = StrUtil.subBefore(s, ".0",false);
}
else
{
- val = Convert.toStr(val);
+ String dateFormat = field.getAnnotation(Excel.class).dateFormat();
+ if (Validator.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) && Validator.isNumber(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);
}
@@ -264,19 +263,23 @@
val = DateUtil.getJavaDate((Double) val);
}
}
- if (StringUtils.isNotNull(fieldType))
+ else if (Boolean.TYPE == fieldType || Boolean.class == fieldType)
+ {
+ val = Convert.toBool(val, false);
+ }
+ if (Validator.isNotNull(fieldType))
{
Excel attr = field.getAnnotation(Excel.class);
String propertyName = field.getName();
- if (StringUtils.isNotEmpty(attr.targetAttr()))
+ if (Validator.isNotEmpty(attr.targetAttr()))
{
propertyName = field.getName() + "." + attr.targetAttr();
}
- else if (StringUtils.isNotEmpty(attr.readConverterExp()))
+ else if (Validator.isNotEmpty(attr.readConverterExp()))
{
val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
}
- else if (StringUtils.isNotEmpty(attr.dictType()))
+ else if (Validator.isNotEmpty(attr.dictType()))
{
val = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());
}
@@ -342,6 +345,7 @@
if (Type.EXPORT.equals(type))
{
fillExcelData(index, row);
+ addStatisticsRow();
}
}
String filename = encodingFilename(sheetName);
@@ -448,6 +452,30 @@
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();
+ totalFont.setFontName("Arial");
+ totalFont.setFontHeightInPoints((short) 10);
+ 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;
}
@@ -477,14 +505,53 @@
{
if (ColumnType.STRING == attr.cellType())
{
- cell.setCellType(CellType.STRING);
- cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
+ cell.setCellValue(Validator.isNull(value) ? attr.defaultValue() : value + attr.suffix());
}
else if (ColumnType.NUMERIC == attr.cellType())
{
- cell.setCellType(CellType.NUMERIC);
- cell.setCellValue(Integer.parseInt(value + ""));
+ cell.setCellValue(StrUtil.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 (Validator.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;
}
/**
@@ -500,10 +567,9 @@
{
// 璁剧疆鍒楀
sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
- row.setHeight((short) (attr.height() * 20));
}
// 濡傛灉璁剧疆浜嗘彁绀轰俊鎭垯榧犳爣鏀句笂鍘绘彁绀�.
- if (StringUtils.isNotEmpty(attr.prompt()))
+ if (Validator.isNotEmpty(attr.prompt()))
{
// 杩欓噷榛樿璁句簡2-101鍒楁彁绀�.
setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
@@ -525,13 +591,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);
@@ -539,15 +606,15 @@
String readConverterExp = attr.readConverterExp();
String separator = attr.separator();
String dictType = attr.dictType();
- if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
+ if (Validator.isNotEmpty(dateFormat) && Validator.isNotNull(value))
{
cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
}
- else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))
+ else if (Validator.isNotEmpty(readConverterExp) && Validator.isNotNull(value))
{
cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));
}
- else if (StringUtils.isNotEmpty(dictType) && StringUtils.isNotNull(value))
+ else if (Validator.isNotEmpty(dictType) && Validator.isNotNull(value))
{
cell.setCellValue(convertDictByExp(Convert.toStr(value), dictType, separator));
}
@@ -560,6 +627,7 @@
// 璁剧疆鍒楃被鍨�
setCellVo(value, attr, cell);
}
+ addStatisticsData(column, Convert.toStr(value), attr);
}
}
catch (Exception e)
@@ -641,7 +709,7 @@
for (String item : convertSource)
{
String[] itemArray = item.split("=");
- if (StringUtils.containsAny(separator, propertyValue))
+ if (StrUtil.containsAny(separator, propertyValue))
{
for (String value : propertyValue.split(separator))
{
@@ -660,7 +728,7 @@
}
}
}
- return StringUtils.stripEnd(propertyString.toString(), separator);
+ return StrUtil.strip(propertyString.toString(), null,separator);
}
/**
@@ -678,7 +746,7 @@
for (String item : convertSource)
{
String[] itemArray = item.split("=");
- if (StringUtils.containsAny(separator, propertyValue))
+ if (StrUtil.containsAny(separator, propertyValue))
{
for (String value : propertyValue.split(separator))
{
@@ -697,9 +765,9 @@
}
}
}
- return StringUtils.stripEnd(propertyString.toString(), separator);
+ return StrUtil.strip(propertyString.toString(), null,separator);
}
-
+
/**
* 瑙ｆ瀽瀛楀吀鍊�
*
@@ -724,6 +792,53 @@
public static String reverseDictByExp(String dictLabel, String dictType, String separator)
{
return DictUtils.getDictValue(dictType, dictLabel, separator);
+ }
+
+ /**
+ * 鍚堣缁熻淇℃伅
+ */
+ private void addStatisticsData(Integer index, String text, Excel entity)
+ {
+ if (entity != null && entity.isStatistics())
+ {
+ Double temp = 0D;
+ if (!statistics.containsKey(index))
+ {
+ statistics.put(index, temp);
+ }
+ try
+ {
+ temp = Double.valueOf(text);
+ }
+ catch (NumberFormatException e)
+ {
+ }
+ statistics.put(index, statistics.get(index) + temp);
+ }
+ }
+
+ /**
+ * 鍒涘缓缁熻琛�
+ */
+ public void addStatisticsRow()
+ {
+ if (statistics.size() > 0)
+ {
+ Cell cell = null;
+ Row row = sheet.createRow(sheet.getLastRowNum() + 1);
+ Set<Integer> keys = statistics.keySet();
+ cell = row.createCell(0);
+ cell.setCellStyle(styles.get("total"));
+ cell.setCellValue("鍚堣");
+
+ for (Integer key : keys)
+ {
+ cell = row.createCell(key);
+ cell.setCellStyle(styles.get("total"));
+ cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
+ }
+ statistics.clear();
+ }
}
/**
@@ -763,10 +878,10 @@
private Object getTargetValue(T vo, Field field, Excel excel) throws Exception
{
Object o = field.get(vo);
- if (StringUtils.isNotEmpty(excel.targetAttr()))
+ if (Validator.isNotEmpty(excel.targetAttr()))
{
String target = excel.targetAttr();
- if (target.indexOf(".") > -1)
+ if (target.contains("."))
{
String[] targets = target.split("[.]");
for (String name : targets)
@@ -792,12 +907,12 @@
*/
private Object getValue(Object o, String name) throws Exception
{
- if (StringUtils.isNotEmpty(name))
+ if (Validator.isNotNull(o) && Validator.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;
}
@@ -831,6 +946,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);
}
/**
@@ -890,18 +1020,18 @@
try
{
Cell cell = row.getCell(column);
- if (StringUtils.isNotNull(cell))
+ if (Validator.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());
}
@@ -911,15 +1041,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();
}
--
Gitblit v1.9.3