From c3bb4d2a6181e27b58cca56def0f392d6df7d7a1 Mon Sep 17 00:00:00 2001
From: zhujie <693337446@qq.com>
Date: 星期五, 26 十一月 2021 13:01:00 +0800
Subject: [PATCH] add 新增导入支持开启Validator数据验证，支持开启导入异常行继续读取，支持返回导入回执
---
ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java | 14 ++++
ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelListener.java | 116 ++++++++++++++++++++++++++++++++++++++
ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelResult.java | 40 +++++++++++++
3 files changed, 170 insertions(+), 0 deletions(-)
diff --git a/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelListener.java b/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelListener.java
new file mode 100644
index 0000000..5705e94
--- /dev/null
+++ b/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelListener.java
@@ -0,0 +1,116 @@
+package com.ruoyi.common.utils.poi;
+
+import cn.hutool.core.collection.CollUtil;
+import cn.hutool.core.util.StrUtil;
+import com.alibaba.excel.context.AnalysisContext;
+import com.alibaba.excel.event.AnalysisEventListener;
+import com.alibaba.excel.exception.ExcelAnalysisException;
+import com.alibaba.excel.exception.ExcelDataConvertException;
+import com.alibaba.fastjson.JSON;
+import com.ruoyi.common.utils.ValidatorUtils;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import javax.validation.*;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * 鍏叡excel鐩戝惉绫�
+ * @param <T>
+ */
+public class ExcelListener<T> extends AnalysisEventListener<T> {
+
+ private static final Logger LOGGER = LoggerFactory.getLogger(ExcelListener.class);
+ /** 鏁版嵁瀵硅薄list */
+ private final List<T> list = new ArrayList<>();
+ /** 閿欒淇℃伅鍒楄〃 */
+ private final List<String> errorList = new ArrayList<>();
+ /** 閬囧埌寮傚父鏄惁璺冲嚭瀵煎叆锛岄粯璁や负鏄� */
+ private Boolean skipException = Boolean.TRUE;
+ /** 鏄惁Validator妫�楠岋紝榛樿涓烘槸 */
+ private Boolean isValidate = Boolean.TRUE;
+ /**
+ * 瀵煎叆鍥炴墽
+ */
+ private final ExcelResult<T> excelResult = new ExcelResult<>();
+
+ public ExcelListener() {
+
+ }
+
+ public ExcelListener(boolean isValidate, boolean skipException) {
+ this.isValidate = isValidate;
+ this.skipException = skipException;
+ }
+
+ /**
+ * 澶勭悊寮傚父
+ *
+ * @param exception ExcelDataConvertException
+ * @param context excel涓婁笅鏂�
+ */
+ @Override
+ public void onException(Exception exception, AnalysisContext context) throws Exception {
+ // 濡傛灉鏄煇涓�涓崟鍏冩牸鐨勮浆鎹㈠紓甯� 鑳借幏鍙栧埌鍏蜂綋琛屽彿
+ // 濡傛灉瑕佽幏鍙栧ご鐨勪俊鎭� 閰嶅悎doAfterAllAnalysedHeadMap浣跨敤
+ String errMsg = null;
+ if (exception instanceof ExcelDataConvertException) {
+ ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
+ errMsg = StrUtil.format("绗瑊}琛�-绗瑊}鍒楄В鏋愬紓甯�<br/>", excelDataConvertException.getRowIndex() + 1,
+ excelDataConvertException.getColumnIndex() + 1);
+ LOGGER.error(errMsg);
+ }
+ if (exception instanceof ConstraintViolationException) {
+ ConstraintViolationException constraintViolationException = (ConstraintViolationException)exception;
+ Set<ConstraintViolation<?>> constraintViolations = constraintViolationException.getConstraintViolations();
+ String constraintViolationsMsg= CollUtil.join(constraintViolations
+ .stream()
+ .map(ConstraintViolation::getMessage)
+ .collect(Collectors.toList()),
+ ",");
+ errMsg = StrUtil.format("绗瑊}琛屾暟鎹牎楠屽紓甯�:{}", context.readRowHolder().getRowIndex() + 1,
+ constraintViolationsMsg);
+ LOGGER.error(errMsg);
+ }
+ errorList.add(errMsg);
+ if (!skipException){
+ throw new ExcelAnalysisException(errMsg);
+ }
+ }
+
+ @Override
+ public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
+ LOGGER.debug("瑙ｆ瀽鍒颁竴鏉″ご鏁版嵁:{}", JSON.toJSONString(headMap));
+ }
+
+ @Override
+ public void invoke(T data, AnalysisContext context) {
+ if (isValidate) {
+ ValidatorUtils.validate(data);
+ }
+ list.add(data);
+ }
+
+ @Override
+ public void doAfterAllAnalysed(AnalysisContext context) {
+ excelResult.setList(list);
+ excelResult.setErrorList(errorList);
+ LOGGER.debug("鎵�鏈夋暟鎹В鏋愬畬鎴愶紒");
+ }
+
+ /**
+ * 鑾峰彇瀵煎叆鏁版嵁
+ * @return 瀵煎叆鏁版嵁
+ */
+ public List<T> getList() {
+ return list;
+ }
+
+ public ExcelResult<T> getExcelResult() {
+ return excelResult;
+ }
+
+}
diff --git a/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelResult.java b/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelResult.java
new file mode 100644
index 0000000..3e17548
--- /dev/null
+++ b/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelResult.java
@@ -0,0 +1,40 @@
+package com.ruoyi.common.utils.poi;
+
+import cn.hutool.core.collection.CollUtil;
+import cn.hutool.core.util.StrUtil;
+import lombok.Data;
+
+import java.util.ArrayList;
+import java.util.List;
+
+@Data
+public class ExcelResult<T> {
+
+ /** 鏁版嵁瀵硅薄list
+ */
+ private List<T> list;
+ /** 閿欒淇℃伅鍒楄〃 */
+ private List<String> errorList;
+
+ /**
+ * 鑾峰彇瀵煎叆鍥炴墽
+ * @return 瀵煎叆鍥炴墽
+ */
+ public String getAnalysis() {
+ int successCount = list.size();
+ int errorCount = errorList.size();
+ if (successCount == 0) {
+ return "璇诲彇澶辫触锛屾湭瑙ｆ瀽鍒版暟鎹�";
+ } else {
+ if (errorList.size() == 0) {
+ return StrUtil.format("鎭枩鎮紝鍏ㄩ儴璇诲彇鎴愬姛锛佸叡{}鏉�", successCount);
+ } else {
+ return StrUtil.format("閮ㄥ垎璇诲彇鎴愬姛锛屽叾涓垚鍔焮}鏉★紝澶辫触{}鏉★紝閿欒淇℃伅濡備笅锛�<br/>{}",
+ successCount,
+ errorCount,
+ CollUtil.join(errorList, "<br/>"));
+ }
+
+ }
+ }
+}
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 0775ce5..8802a54 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
@@ -6,6 +6,7 @@
import com.ruoyi.common.convert.ExcelBigNumberConvert;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.file.FileUtils;
+import org.apache.poi.ss.formula.functions.T;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
@@ -30,6 +31,19 @@
return EasyExcel.read(is).head(clazz).autoCloseStream(false).sheet().doReadSync();
}
+
+ /**
+ * 瀵筫xcel琛ㄥ崟榛樿绗竴涓储寮曞悕杞崲鎴恖ist锛圗asyExcel锛�
+ *
+ * @param is 杈撳叆娴�
+ * @return 杞崲鍚庨泦鍚�
+ */
+ public static <T> ExcelResult<T> importExcel(InputStream is, Class<T> clazz, boolean isValidate, boolean skipException) {
+ ExcelListener<T> listener = new ExcelListener<>(isValidate, skipException);
+ EasyExcel.read(is, clazz, listener).sheet().doRead();
+ return listener.getExcelResult();
+ }
+
/**
* 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟锛圗asyExcel锛�
*
--
Gitblit v1.9.3