elment UI + EasyExcel 实现导入

郭卫东 · 2023-6-2 19:21:15

前端组件

<hd-flex>
<el-dialog v-model="isUploadDialog" width="50%" lock-scroll=false>
<el-upload
class="upload-demo"
drag
:action="url"
:on-success="success"
:on-error="error"
:headers="uploadHeaders"
:limit="1"
:on-exceed="handleExceed"
:file-list="fileList"
accept=".xlsx,.xls">
点击上传
只能上传xlsx/xls文件，且不超过500kb
</el-upload>
</el-dialog>
</hd-flex> //变量

复制代码

url: `${conf.BASE_URL}/core/ssqd/importS`, isUploadDialog: false, fileList: [], // 方法

复制代码

//导入 async importS() { this.fileList=[]; this.isUploadDialog=true; }, success(response, file, fileList){ if(response.code=='500'){ this.$hd.message.error(response.errorBody.errorMessage); } if(response.code=='200'){ this.$hd.message.ok('导入成功！'); this.isUploadDialog=false; this.$refs.table.onSearch(); } }, error(err, file, fileList){ this.$hd.message.error(err); }, handleRemove(file, fileList) { console.log(file, fileList) }, handlePreview(file) { console.log(file) }, handleExceed(files, fileList) { this.$message.warning( `当前限制选择 1 个文件，本次选择了 ${files.length} 个文件，共选择了 ${ files.length + fileList.length } 个文件` ) }, beforeRemove(file, fileList) { return this.$confirm(`确定移除 ${file.name}？`) },

复制代码

Java

Controller

复制代码

@ApiOperation("上传")
@ApiImplicitParams({
@ApiImplicitParam(name = "file",value = "文件",dataTypeClass = MultipartFile.class,required = true,paramType = "")
})
@PostMapping ("/importS")
public RestResponse<String> uploadExcel(MultipartFile file)throws IOException {
String HZ = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
if(".xlsx.xls".indexOf(HZ) < 0){
throw new BaseException("500","导入的文件类型不正确，只能导入Excel文件");
}
EasyExcel.read(file.getInputStream(), SsqdVO.class,new UploadListenerBySsqd(iSsqdService)).sheet() .doRead();;
return new RestResponse<> ("ok");
}

复制代码

javaBean

package com.hopedove.coreserver.vo.sygl;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
import com.hopedove.commons.vo.BaseModel;
import lombok.Data;
/**
* 璇曠罕娓呭崟
* @TableName T_JS_SYGL_SSQD
*/
@TableName(value ="T_JS_SYGL_SSQD")
@Data
public class SsqdVO extends BaseModel implements Serializable {
/**
* 璇曠罕娓呭崟ID
*/
@TableId
private String SSQDID;
/**
* 坯布类型
*/
@ExcelProperty(value ="试纱类型", index = 0)
private String PBLX;
/**
* 布号
*/
@ExcelProperty(value ="布号", index = 1)
private String BH;
/**
* 支数
*/
@ExcelProperty(value ="支数", index = 2)
private String ZS;
/**
* 产地
*/
@ExcelProperty(value ="产地", index = 3)
private String CD;
/**
* 批号
*/
@ExcelProperty(value ="批号", index = 4)
private String PH;
/**
* 重量
*/
@ExcelProperty(value ="重量", index = 5)
private BigDecimal ZL;
/**
* 备注
*/
@ExcelProperty(value ="备注", index = 6)
private String REMARK;
@TableField(exist = false)
private String GY;
}

复制代码

　　监听器: 判断上传表格是否符合要求

package com.hopedove.coreserver.service.impl.sygl;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.nacos.common.utils.CollectionUtils;
import com.hedu.sweet.starter.utils.exception.BusinException;
import com.hopedove.coreserver.service.sygl.ISsqdService;
import com.hopedove.coreserver.vo.sygl.SsqdVO;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class UploadListenerBySsqd extends AnalysisEventListener<SsqdVO> {
private ISsqdService iSsqdService;
public UploadListenerBySsqd(ISsqdService iSsqdService) {
this.iSsqdService = iSsqdService;
}
private List<SsqdVO> list = new ArrayList<>(100);
@Override
public void invoke(SsqdVO ssqdVO, AnalysisContext analysisContext) {
//业务判断
System.out.println("***"+ssqdVO+"***");
list.add(ssqdVO);
// if (list.size() > 100) {
// wjgbpclService.saveData(list);//保存到数据库
// list = ListUtils.newArrayListWithExpectedSize(100);
// }
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
if (CollectionUtils.isNotEmpty(list)) {
System.out.println("***结束***");
System.out.println(list);
iSsqdService.saveData(list);
}else{
throw new BusinException("500","导入的文件为空,请填写信息后重新导入。");
}
}
/**
* 在这里进行模板的判断
* @param headMap 存放着导入表格的表头，键是索引，值是名称
* @param context
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
String isNull = "";
if (context.readRowHolder().getRowIndex() == 0) {
String[] headList = {"试纱类型","布号","支数","产地","批号","重量","备注"};
for (int i = 0; i < headList.length; i++) {
try {
if (!headMap.get(i).equals(headList[i])) {
isNull = "导入模板不正确，请重新导入";
break;
}
} catch (Exception e) {
isNull = "导入模板不正确，请重新导入";
break;
}
}
}
if(isNull!=""){
throw new BusinException("500",isNull);
}
}
}

复制代码

　　实现类

@Override
public void saveData(List<SsqdVO> list) {
//出现空的数据行，只有边框没有值-处理
list = tableNullLineRemove(list);
String msg = "";
if(list.size() > 0){
if (StringUtil.isEmpty(msg)) {
// 验证输入数据重复性
msg = checkMxList(list);
}
if (StringUtil.isEmpty(msg)) {
// excel数据插入数据库
List<SsqdVO> arr = new ArrayList<>(100);
for (int i = 0; i < list.size(); i++) {
SsqdVO bean= list.get(i);
bean = nullToString(bean);
arr.add(bean);
}
if (CollectionUtils.isNotEmpty(list)) {
if(!importAdd(list)){//导入
throw new BusinException("500","导入的文件有效记录数超过1000条，请分批次多次导入");
}
}
}
}
if(!StringUtil.isEmpty(msg)){
throw new BusinException("500",msg);
}
}
/**
* 验证输入数据重复性
* @param list
* @return
*/
private String checkMxList(List<SsqdVO> list) {
List<String> errMsgList = new ArrayList<String>();
String msg = "";
if (list.size() > 0) {
// 把页面的数据进行重复性检验
for (int i = 0; i < list.size(); i++) {
SsqdVO mxModel = list.get(i);
String PBLX = StringUtil.nullToSring(mxModel.getPBLX());
String BH = StringUtil.nullToSring(mxModel.getBH());
String ZS = StringUtil.nullToSring(mxModel.getZS());
String ZL = StringUtil.nullToSring(mxModel.getZL());
//当纱织类型，布号，支数和重量都为空，那么这条记录既不交验，也不添加
if(StringUtil.isEmpty(PBLX) && StringUtil.isEmpty(BH) && StringUtil.isEmpty(ZS) && StringUtil.isEmpty(ZL)){
continue;
}
for (int j = 1; j < list.size(); j++) {
if (i != j) {
SsqdVO mxModelSec = list.get(j);
String PBLXsec = mxModelSec.getPBLX();
String BHsec = StringUtil.nullToSring(mxModelSec.getBH());
String ZSsec = StringUtil.nullToSring(mxModelSec.getZS());
String ZLsec = StringUtil.nullToSring(mxModelSec.getZL());
//当纱织类型，布号，支数和重量都为空，那么这条记录既不交验，也不添加
if(StringUtil.isEmpty(PBLXsec) && StringUtil.isEmpty(BHsec) && StringUtil.isEmpty(ZSsec) && StringUtil.isEmpty(ZLsec)){
continue;
}
if(PBLX.equals(PBLXsec) && "2".equals(PBLX)){
if ((StringUtil.nullToSring(mxModelSec.getBH()))
.equals(StringUtil.nullToSring(mxModel.getBH()))
&& (StringUtil.nullToSring(mxModelSec.getZS()))
.equals(StringUtil.nullToSring(mxModel.getZS()))
&& (StringUtil.nullToSring(mxModelSec.getZL()))
.equals(StringUtil.nullToSring(mxModel.getZL()))
&& (StringUtil.nullToSring(mxModelSec.getSC()))
.equals(StringUtil.nullToSring(mxModel.getSC()))
&& (StringUtil.nullToSring(mxModelSec.getSH()))
.equals(StringUtil.nullToSring(mxModel.getSH()))
&& (StringUtil.nullToSring(mxModelSec.getGY()))
.equals(StringUtil.nullToSring(mxModel.getGY()))) {
msg="导入文档第" + (i+1) + "行数据记录与第" + (j+1)
+ "行数据记录重复";
/*errMsgList.add("导入文档第" + (i+1) + "行数据记录与第" + j
+ "行数据记录重复");*/
}
} else {
if ((StringUtil.nullToSring(mxModelSec.getBH()))
.equals(StringUtil.nullToSring(mxModel.getBH()))
&& (StringUtil.nullToSring(mxModelSec.getZS()))
.equals(StringUtil.nullToSring(mxModel.getZS()))
&& (StringUtil.nullToSring(mxModelSec.getZL()))
.equals(StringUtil.nullToSring(mxModel.getZL()))) {
msg="导入文档第" + (i+1) + "行数据记录与第" + (j+1)
+ "行数据记录重复";
/*errMsgList.add("导入文档第" + (i+1) + "行数据记录与第" + j
+ "行数据记录重复")*/;
}
}
}
}
}
}
return msg;
}
private Boolean importAdd(List<SsqdVO> list) {
UserDTO userBean = UserUtil.getUserInfo();
int index = 0 ;
List <Map <String, String>> addList = new ArrayList <Map <String, String>>();
for (int i = 0; i < list.size(); i++) {
SsqdVO entry = list.get(i);
//保存的处理逻辑
}
iSsqdDao.insertSSQD(addList);
iSsqdDao.insertSSRZGY(addList);
return true;
}
private SsqdVO nullToString(SsqdVO params) {
Map<String,Object> map = new HashMap<>();
Field[] fields = params.getClass().getDeclaredFields();
try {
for (Field field : fields
) {
//设置允许通过反射访问私有变量
field.setAccessible(true);
map.put(field.getName(),field.get(params)==null? "":field.get(params));
}
}catch (Exception e){
e.printStackTrace();
}
return MapUntil.mapToBean(map,new SsqdVO());
}
private List<SsqdVO> tableNullLineRemove(List<SsqdVO> list) {
List<SsqdVO> l = new ArrayList<>();
//当纱织类型，布号，支数和重量都为空，那么这条记录既不交验，也不添加
for (SsqdVO model:list
) {
if(model.getPBLX()==null && model.getBH() ==null&&model.getZS()==null &&model.getZL()==null){
continue;
}else{
l.add(model);
}
}
return l;
}

复制代码

　　SQL oracle数据库批量新增

<insert id="insertSSQD" parameterType="list">
insert all
<foreach collection="list" item="item">
<![CDATA[
into T_JS_SYGL_SSQD
(
SSQDID,
RSQDBH,
BH,
PBLX,
PBMC,
ZS,
CREATER,
CRENAME,
UPDATER,
BMXXID,
BMMC,
JGXXID,
JGMC,
ZTXXID,
ZTMC
]]>
<if test=" item.ZL != null and item.ZL != '' ">,ZL </if>
<if test=" item.SH != null and item.SH != '' ">,SH </if>
<if test=" item.ZFMYQ != null and item.ZFMYQ != '' ">,ZFMYQ </if>
<if test=" item.XSYQ != null and item.XSYQ != '' ">,XSYQ </if>
<if test=" item.SG != null and item.SG != '' ">,SG </if>
<if test=" item.REMARK != null and item.REMARK != '' ">,REMARK </if>
<if test=" item.CD != null and item.CD != '' ">,CD </if>
<if test=" item.PH != null and item.PH != '' ">,PH </if>
<if test=" item.SC != null and item.SC != '' ">,SC </if>
) VALUES(
<![CDATA[
#{item.SSQDID},
#{item.RSQDBH},
#{item.BH},
#{item.PBLX},
#{item.PBMC},
#{item.ZS},
#{item.CREATER},
#{item.CRENAME},
#{item.UPDATER},
#{item.BMXXID},
#{item.BMMC},
#{item.JGXXID},
#{item.JGMC},
#{item.ZTXXID},
#{item.ZTMC}
]]>
<if test=" item.ZL != null and item.ZL != '' ">,#{item.ZL} </if>
<if test=" item.SH != null and item.SH != '' ">,#{item.SH} </if>
<if test=" item.ZFMYQ != null and item.ZFMYQ != '' ">,#{item.ZFMYQ} </if>
<if test=" item.XSYQ != null and item.XSYQ != '' ">,#{item.XSYQ} </if>
<if test=" item.SG != null and item.SG != '' ">,#{item.SG} </if>
<if test=" item.REMARK != null and item.REMARK != '' ">,#{item.REMARK} </if>
<if test=" item.CD != null and item.CD != '' ">,#{item.CD} </if>
<if test=" item.PH != null and item.PH != '' ">,#{item.PH} </if>
<if test=" item.SC != null and item.SC != '' ">,#{item.SC} </if>
)
</foreach>
select * from dual
</insert>

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

		自动登录	找回密码
密码			立即注册

elment UI + EasyExcel 实现导入

0 个回复

快速回复

楼主热帖

标签云

elment UI + EasyExcel 实现 导入

0 个回复

快速回复

楼主热帖

标签云

elment UI + EasyExcel 实现导入