一、前端代码
1.1.页面显示部分
<!-- 用户导入对话框 -->
<el-dialog :title="upload.title" :visible.sync="upload.open" width="400px" append-to-body>
<el-upload
ref="upload"
:limit="1"
accept=".xls"
:headers="upload.headers"
:action="upload.url "
: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>
<!-- <div class="el-upload__tip text-center" slot="tip">-->
<!-- <span>仅允许导入xls、xlsx格式文件。</span>-->
<!-- <el-link type="primary" :underline="false" style="font-size:12px;vertical-align: baseline;" @click="importTemplate">下载模板</el-link>-->
<!-- </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>
二、后端配置:
2.1.引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
2.2创建一个表:
2.3.创建一个与表格对应的类
package com.ruoyi.product.domain;
import com.alibaba.excel.annotation.ExcelProperty;
import com.ruoyi.common.core.domain.BaseEntity;
import lombok.Data;
import java.math.BigDecimal;
@Data
public class Product extends BaseEntity {
private int ProductId;
private int StoneId;
@ExcelProperty("批号")
private String BNum;
@ExcelProperty("商品名称")
private String PName;
@ExcelProperty("商品类别")
private String PCategory;
@ExcelProperty("货品货型")
private String PModel;
@ExcelProperty("原始货号")
private String ONum;
@ExcelProperty("金属成色")
private String MPurity;
@ExcelProperty("主石")
private Integer MStone;
@ExcelProperty("主石重量")
private BigDecimal MWeight;
@ExcelProperty("副石")
private Integer AStone;
@ExcelProperty("副石重量")
private Integer AWeight;
@ExcelProperty("金料重量")
private BigDecimal GWeight;
@ExcelProperty("总重")
private BigDecimal TWeight;
@ExcelProperty("外加工费")
private BigDecimal EFee;
@ExcelProperty("成本价")
private BigDecimal CPrice;
@ExcelProperty("零售价")
private BigDecimal RPrice;
@ExcelProperty("数量")
private Integer QNum;
@ExcelProperty("证书号")
private String CNum;
@ExcelProperty("规格")
private String SSpec;
@ExcelProperty("供货商")
private String VSupplier;
@ExcelProperty("主石净度")
private String MClarity;
@ExcelProperty("主石色泽")
private String MColor;
@ExcelProperty("主石切工")
private String MCut;
@ExcelProperty("主石数量")
private Integer MNum;
@ExcelProperty("副石数量")
private Integer ANum;
@ExcelProperty("产地")
private String OPlace;
@ExcelProperty("克加工费")
private BigDecimal PFee;
@ExcelProperty("款型号")
private String KType;
@ExcelProperty("金单价")
private BigDecimal GPrice;
@ExcelProperty("证书费")
private BigDecimal CFee;
@ExcelProperty("其他费用")
private BigDecimal OExpenses;
@ExcelProperty("备注")
private String BRemark;
private Integer isMStone;
private Integer isAStone;
private String shopName;
private String shopId;
private int ifTb;
}
2.4.创建一个工具类:
package com.ruoyi.product.service;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.product.domain.Product;
import com.ruoyi.product.mapper.ProProductMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
@Component
@Scope("prototype")
public class ProductListener extends AnalysisEventListener<Product> {
@Autowired
ProProductMapper proProductMapper;
@Autowired
IProProductService productService;
@Autowired
IProStoneService proStoneService;
List<Product> list = new ArrayList<>();
@Override
// public void invoke(ProProduct data, AnalysisContext context) {
public void invoke(Product data, AnalysisContext context) {
System.out.println(data);
data.setIfTb(0);
list.add(data);
for(Product product : list){
product.setProductId(proProductMapper.getMaxProductId()+1);
System.out.println(product.getMStone());
if(product.getMStone() != null){
product.setMStone(1);
product.setIsMStone(1);
} else {
product.setMStone(0);
product.setIsMStone(null);
}
if(product.getAStone() != null){
product.setAStone(1);
product.setIsAStone(1);
} else {
product.setAStone(0);
product.setIsAStone(null);
}
System.out.println(product);
// productService.importProduct(product);
}
data.setIfTb(0);
productService.importProduct(data);
System.out.println("这有数据吗");
if(data.getIsMStone() != null ){
int num=proStoneService.sumcount();
data.setStoneId(num+1);
data.setIsMStone(0);
proStoneService.importMStone(data);
Long userId= SecurityUtils.getUserId();
proStoneService.importMStoneLog(userId,data);
}
if (data.getIsAStone() != null ){
int num=proStoneService.sumcount();
data.setProductId(data.getProductId());
data.setStoneId(num+1);
data.setIsAStone(1);
proStoneService.importAStone(data);
Long userId= SecurityUtils.getUserId();
proStoneService.importAStoneLog(userId,data);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//解析完,全部回调逻辑实现
list.clear();
}
}
2.5.编写一个Controller去接收前端传递的表,并用EasyExcel去解析
@PostMapping( "/importData")
public String importData( MultipartFile file ) throws IOException {
//插入是谁导入的数据
proProductService.insertProProduct();
Thread thread = new Thread();
thread.start();
log.info("传过来的文件:{}",file);
if (file.isEmpty()){
return "传输的数据为空";
}
isImportDate=true;
//将文件存储在本地目录
try {
byte[] bytes = file.getBytes();
//要存入本地的地址放到path里面
Path path = Paths.get( environment.getProperty("ruoyi.profile") + "/");
//如果没有files文件夹,则创建
if (!Files.isWritable(path)) {
Files.createDirectories(path);
}
String extension = FileUtils.getFileExtension(file); //获取文件后缀
FileUtils.getFileByBytes(bytes, environment.getProperty("ruoyi.profile") , "上传文件" + extension);
} catch (Exception e) {
e.printStackTrace();
}
log.info("文件路径:{}",environment.getProperty("ruoyi.profile"));
try {
System.out.println(new Date());
Thread.sleep(1000 * 5);
System.out.println(new Date());
isImportDate=false;
} catch (InterruptedException e) {
e.printStackTrace();
}
//使用EasyExcel去解析Excel
ExcelReaderBuilder readworkBook = EasyExcel.read(file.getInputStream(), Product.class, productListener);
readworkBook.sheet().doRead();
// InputStream inputStream = file.getInputStream();
//service要通过参数传进去
// EasyExcel.read(inputStream, ProProduct.class, new ProductListener(proProductService)).sheet().doRead();
return "success";
}