From 7c4a10482326ef418dd9e0713fda3761e665accb Mon Sep 17 00:00:00 2001
From: 疯狂的狮子Li <15040126243@163.com>
Date: 星期五, 26 十一月 2021 16:40:42 +0800
Subject: [PATCH] !113 add 新增 excel 导入支持开启 Validator 数据验证 Merge pull request !113 from Yjoioooo/auto-5403234-dev-1637903026810
---
ruoyi-demo/src/main/java/com/ruoyi/demo/controller/TestDemoController.java | 25 +++
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 ++++++
ruoyi-ui/src/views/demo/demo/index.vue | 67 +++++++++++
ruoyi-demo/src/main/java/com/ruoyi/demo/domain/bo/TestDemoImportVo.java | 66 +++++++++++
6 files changed, 325 insertions(+), 3 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锛�
*
diff --git a/ruoyi-demo/src/main/java/com/ruoyi/demo/controller/TestDemoController.java b/ruoyi-demo/src/main/java/com/ruoyi/demo/controller/TestDemoController.java
index 7b70360..0307d13 100644
--- a/ruoyi-demo/src/main/java/com/ruoyi/demo/controller/TestDemoController.java
+++ b/ruoyi-demo/src/main/java/com/ruoyi/demo/controller/TestDemoController.java
@@ -1,27 +1,31 @@
package com.ruoyi.demo.controller;
+import cn.hutool.core.bean.BeanUtil;
import com.ruoyi.common.annotation.Log;
import com.ruoyi.common.annotation.RepeatSubmit;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
+import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.core.page.TableDataInfo;
import com.ruoyi.common.core.validate.AddGroup;
import com.ruoyi.common.core.validate.EditGroup;
import com.ruoyi.common.core.validate.QueryGroup;
import com.ruoyi.common.enums.BusinessType;
import com.ruoyi.common.utils.ValidatorUtils;
+import com.ruoyi.common.utils.poi.ExcelResult;
import com.ruoyi.common.utils.poi.ExcelUtil;
+import com.ruoyi.demo.domain.TestDemo;
import com.ruoyi.demo.domain.bo.TestDemoBo;
+import com.ruoyi.demo.domain.bo.TestDemoImportVo;
import com.ruoyi.demo.domain.vo.TestDemoVo;
import com.ruoyi.demo.service.ITestDemoService;
-import io.swagger.annotations.Api;
-import io.swagger.annotations.ApiOperation;
-import io.swagger.annotations.ApiParam;
+import io.swagger.annotations.*;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
+import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotEmpty;
@@ -65,6 +69,21 @@
return iTestDemoService.customPageList(bo);
}
+ @ApiOperation("瀵煎叆娴嬭瘯鍗曡〃")
+ @ApiImplicitParams({
+ @ApiImplicitParam(name = "file", value = "瀵煎叆鏂囦欢", dataType = "java.io.File", required = true),
+ })
+ @Log(title = "娴嬭瘯鍗曡〃", businessType = BusinessType.IMPORT)
+ @PreAuthorize("@ss.hasPermi('demo:demo:import')")
+ @PostMapping("/importData")
+ public AjaxResult<Void> importData(@RequestPart("file") MultipartFile file) throws Exception {
+ ExcelResult<TestDemoImportVo> excelResult = ExcelUtil.importExcel(file.getInputStream(), TestDemoImportVo.class, true, true);
+ List<TestDemoImportVo> volist = excelResult.getList();
+ List<TestDemo> list = BeanUtil.copyToList(volist, TestDemo.class);
+ iTestDemoService.saveAll(list);
+ return AjaxResult.success(excelResult.getAnalysis());
+ }
+
/**
* 瀵煎嚭娴嬭瘯鍗曡〃鍒楄〃
*/
diff --git a/ruoyi-demo/src/main/java/com/ruoyi/demo/domain/bo/TestDemoImportVo.java b/ruoyi-demo/src/main/java/com/ruoyi/demo/domain/bo/TestDemoImportVo.java
new file mode 100644
index 0000000..2150cdf
--- /dev/null
+++ b/ruoyi-demo/src/main/java/com/ruoyi/demo/domain/bo/TestDemoImportVo.java
@@ -0,0 +1,66 @@
+package com.ruoyi.demo.domain.bo;
+
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.ruoyi.common.core.domain.BaseEntity;
+import com.ruoyi.common.core.validate.AddGroup;
+import com.ruoyi.common.core.validate.EditGroup;
+import io.swagger.annotations.ApiModel;
+import io.swagger.annotations.ApiModelProperty;
+import lombok.Data;
+import lombok.EqualsAndHashCode;
+
+import javax.validation.constraints.NotBlank;
+import javax.validation.constraints.NotNull;
+
+/**
+ * 娴嬭瘯鍗曡〃涓氬姟瀵硅薄 test_demo
+ *
+ * @author Lion Li
+ * @date 2021-07-26
+ */
+
+@Data
+@ApiModel("娴嬭瘯鍗曡〃涓氬姟瀵硅薄")
+public class TestDemoImportVo {
+
+
+ /**
+ * 閮ㄩ棬id
+ */
+ @ApiModelProperty("閮ㄩ棬id")
+ @NotNull(message = "閮ㄩ棬id涓嶈兘涓虹┖")
+ @ExcelProperty(value = "閮ㄩ棬id")
+ private Long deptId;
+
+ /**
+ * 鐢ㄦ埛id
+ */
+ @ApiModelProperty("鐢ㄦ埛id")
+ @NotNull(message = "鐢ㄦ埛id涓嶈兘涓虹┖")
+ @ExcelProperty(value = "鐢ㄦ埛id")
+ private Long userId;
+
+ /**
+ * 鎺掑簭鍙�
+ */
+ @ApiModelProperty("鎺掑簭鍙�")
+ @NotNull(message = "鎺掑簭鍙蜂笉鑳戒负绌�")
+ @ExcelProperty(value = "鎺掑簭鍙�")
+ private Long orderNum;
+
+ /**
+ * key閿�
+ */
+ @ApiModelProperty("key閿�")
+ @NotBlank(message = "key閿笉鑳戒负绌�")
+ @ExcelProperty(value = "key閿�")
+ private String testKey;
+
+ /**
+ * 鍊�
+ */
+ @ApiModelProperty("鍊�")
+ @NotBlank(message = "鍊间笉鑳戒负绌�")
+ @ExcelProperty(value = "鍊�")
+ private String value;
+}
diff --git a/ruoyi-ui/src/views/demo/demo/index.vue b/ruoyi-ui/src/views/demo/demo/index.vue
index 9ff0e47..c6a2cdf 100644
--- a/ruoyi-ui/src/views/demo/demo/index.vue
+++ b/ruoyi-ui/src/views/demo/demo/index.vue
@@ -73,6 +73,16 @@
</el-col>
<el-col :span="1.5">
<el-button
+ type="info"
+ plain
+ icon="el-icon-upload2"
+ size="mini"
+ @click="handleImport"
+ v-hasPermi="['demo:demo:import']"
+ >瀵煎叆</el-button>
+ </el-col>
+ <el-col :span="1.5">
+ <el-button
type="warning"
plain
icon="el-icon-download"
@@ -164,11 +174,34 @@
<el-button @click="cancel">鍙� 娑�</el-button>
</div>
</el-dialog>
+ <!-- 鐢ㄦ埛瀵煎叆瀵硅瘽妗� -->
+ <el-dialog :title="upload.title" :visible.sync="upload.open" width="400px" append-to-body>
+ <el-upload
+ ref="upload"
+ :limit="1"
+ accept=".xlsx, .xls"
+ :headers="upload.headers"
+ :action="upload.url + '?updateSupport=' + upload.updateSupport"
+ :disabled="upload.isUploading"
+ :on-progress="handleFileUploadProgress"
+ :on-success="handleFileSuccess"
+ :auto-upload="false"
+ drag
+ >
+ <i class="el-icon-upload"></i>
+ <div class="el-upload__text">灏嗘枃浠舵嫋鍒版澶勶紝鎴�<em>鐐瑰嚮涓婁紶</em></div>
+ </el-upload>
+ <div slot="footer" class="dialog-footer">
+ <el-button type="primary" @click="submitFileForm">纭� 瀹�</el-button>
+ <el-button @click="upload.open = false">鍙� 娑�</el-button>
+ </div>
+ </el-dialog>
</div>
</template>
<script>
import { listDemo, pageDemo, getDemo, delDemo, addDemo, updateDemo } from "@/api/demo/demo";
+import {getToken} from "@/utils/auth";
export default {
name: "Demo",
@@ -198,6 +231,19 @@
open: false,
// 鍒涘缓鏃堕棿鏃堕棿鑼冨洿
daterangeCreateTime: [],
+ // 鐢ㄦ埛瀵煎叆鍙傛暟
+ upload: {
+ // 鏄惁鏄剧ず寮瑰嚭灞傦紙鐢ㄦ埛瀵煎叆锛�
+ open: false,
+ // 寮瑰嚭灞傛爣棰橈紙鐢ㄦ埛瀵煎叆锛�
+ title: "",
+ // 鏄惁绂佺敤涓婁紶
+ isUploading: false,
+ // 璁剧疆涓婁紶鐨勮姹傚ご閮�
+ headers: { Authorization: "Bearer " + getToken() },
+ // 涓婁紶鐨勫湴鍧�
+ url: process.env.VUE_APP_BASE_API + "/demo/demo/importData"
+ },
// 鏌ヨ鍙傛暟
queryParams: {
pageNum: 1,
@@ -353,11 +399,32 @@
this.loading = false;
});
},
+ /** 瀵煎叆鎸夐挳鎿嶄綔 */
+ handleImport() {
+ this.upload.title = "鐢ㄦ埛瀵煎叆";
+ this.upload.open = true;
+ },
/** 瀵煎嚭鎸夐挳鎿嶄綔 */
handleExport() {
this.download('demo/demo/export', {
...this.queryParams
}, `demo_${new Date().getTime()}.xlsx`)
+ },
+ // 鏂囦欢涓婁紶涓鐞�
+ handleFileUploadProgress(event, file, fileList) {
+ this.upload.isUploading = true;
+ },
+ // 鏂囦欢涓婁紶鎴愬姛澶勭悊
+ handleFileSuccess(response, file, fileList) {
+ this.upload.open = false;
+ this.upload.isUploading = false;
+ this.$refs.upload.clearFiles();
+ this.$alert(response.msg, "瀵煎叆缁撴灉", { dangerouslyUseHTMLString: true });
+ this.getList();
+ },
+ // 鎻愪氦涓婁紶鏂囦欢
+ submitFileForm() {
+ this.$refs.upload.submit();
}
}
};
--
Gitblit v1.9.3