Sprinboot，如何接受前端传递的Excel表，并存储到数据库中

最新推荐文章于 2024-09-09 21:14:24 发布

凤返集团

最新推荐文章于 2024-09-09 21:14:24 发布

阅读量208

点赞数 4

文章标签：前端 excel 数据库

本文链接：https://blog.csdn.net/qq_60707246/article/details/140876991

版权

一、前端代码

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";
    }