Vue和poi实现Excel文件导入数据

墨蓝 2023-02-15 01:29 12阅读 0赞

**介绍**

**1.前端使用ElementUi的<el-upload>组件，前端的框架无所谓，iview也可以，upload控件里面的方法都类似**

action提交地址，自己设置；

我的里面用了cookie，不用把它去掉，后端也要修改

判断文件的大小，和格式是否是Excel

<template>
    <div style="margin: 20px 30px">
      <el-upload
        class="upload-demo"
        :action="uploadExcel"
        :before-upload="onBeforeUpload"
        :on-success="uploadSuccess"
        :on-error="uploadError"
        :on-remove="handleRemove"
        >
        <el-button slot="trigger" size="small"  icon="el-icon-upload"
     type="primary" style="font-size: 16px">选取文件</el-button>
        <div slot="tip" class="el-upload__tip">
    只能上传xls|xlsx文件，且不超过2MB</div>
      </el-upload>
    
    </div>
    
    </template>
    
    <script>
      export default {
        name: "Login",
        data() {
          return {
            uploadExcel:"/vhr/api/upload/excel?uname=",
          }
        },
        created(){
        },
        mounted(){
          this.getUser()
        },
        methods:{
          // 获取用户名信息
          getUser(){
            this.uploadExcel = this.uploadExcel + this.$cookies.get("user");
          },
          // 文件上传前的操作
          onBeforeUpload(file) {
            let fileName = file.name;
            let suffix = fileName.substring(fileName.lastIndexOf('.') + 1);
            //除以两次1024是将MB转成字节
            const isLt2M = file.size / 1024 / 1024 < 2
            if(!isLt2M){
              this.$notify({
                title: '文件太大',
                message:  file.name + '文件过大，要小于2MB',
                type: 'warning'
              });
              return false;
            }
            if (suffix == "xls" || suffix == 'xlsx') {
              return true;
            } else {
              this.$notify({
                title: '文件格式错误',
                message:  file.name + '文件格式不正确, 请选择Excel文件',
                type: 'warning'
              });
              return false;
            }
          },
          handleRemove(file, fileList) {
            console.log('移除文件'+file);
          },
          //上传成功提示
          uploadSuccess(res,file,flieist) {
            //res是上传成功后端返回的值
            if (res.indexOf('成功') != -1)
              this.$notify({
              message:  '上传成功',
              type: 'success'
              });
            else  this.$notify({
              message:  '上传成功',
              type: 'warning'
            });
            //上传成功可以进行其他操作，如刷新数据
          },
    
          // 上传失败提示
          uploadError() {
            this.$notify({
              message:  '上传失败',
              type: 'warning'
            });
          },
        }
      }
    </script>

**2.后端使用poi框架，由于Excel的版本xls和xlsx形式需要判断后缀**

**   a.pom文件导入依赖**

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.10-FINAL</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.10-FINAL</version>
            </dependency>

b.配置存储文件的路径，我的是在配置文件中方便修改，可以写死，别忘记判断目录是否存在不存在创建

# 个人
    spring:
      servlet:
        multipart:
          location: d:/temp
        # 正式
        #spring.servlet.multipart.location=/usr/local/excel

c.读取Excel的工具类，ExcelTestData是接收表格中数据的实体类，自己写，不放了

package com.ehrm.pj.common.util;
    
    import com.ehrm.pj.sys.entity.ExcelTestData;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.*;
    import org.springframework.stereotype.Component;
    
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @author xuexia
     * @description Excel工具类
     * @create 2020-06-4 10:11
     **/
    @Component
    @Slf4j
    public class ExcelHelper {
    
        /**
         * 读取Excel中数据
         * @param filePath
         * @param fileName
         * @return
         * @throws IOException
         */
        public static List<ExcelTestData> getDataFromExcel(String filePath, String fileName) throws IOException {
    
            //判断是否为excel类型文件
            if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
                log.warn("文件不是excel类型");
                return null;
            }
            //poi中的HSSF用来处理Excel文件
            FileInputStream fis = null;
            Workbook wookbook = null; //文档对象
            Sheet sheet = null; //行
            try {
                //获取一个绝对地址的流
                fis = new FileInputStream(filePath + fileName);
            } catch (Exception e) {
                log.warn("获取文件流异常：" + e.getMessage());
                return null;
            }
    
            try {
                //2003版本的excel，用.xls结尾
                    wookbook = new HSSFWorkbook(fis);//得到工作簿
                System.out.println("HSSF xls"+wookbook);
            } catch (Exception ex) {
                try {
                    //2007版本的excel，用.xlsx结尾
                    fis = new FileInputStream(filePath + fileName);
                    wookbook = new XSSFWorkbook(fis);//得到工作簿
                    System.out.println("XHSSF xlsx"+wookbook);
                } catch (IOException e) {
                    log.warn("获取工作簿异常：" + e.getMessage());
                    return null;
                }
            }
            sheet = wookbook.getSheetAt(0);
            //获得表头
            Row rowHead = sheet.getRow(0);
            //判断表头是否正确
            System.out.println(rowHead.getPhysicalNumberOfCells());
            if (rowHead.getPhysicalNumberOfCells() != 6) {
                log.warn("表头的数量不对!");
                return null;
            }
    
            //获得数据的总行数
            int totalRowNum = sheet.getLastRowNum();
    
            //要获得属性
            String name = "";
            String gender = "";
            String school = "";
            String idCard = "";
            String address = "";
            String email = "";
    
            //存储获得所有数据
            List<ExcelTestData> list = new ArrayList<>();
    
            try {
                for (int i = 1; i <= totalRowNum; i++) {
    
                    //获得第i行对象
                    Row row = sheet.getRow(i);
                    if (null == row) continue;
    
                    Cell cell = row.getCell((short) 0);
                    if (null == cell || cell.getCellType() == Cell.CELL_TYPE_BLANK) continue;
    
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    name = cell.getStringCellValue().toString();
    
                    cell = row.getCell((short) 1);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    gender = cell.getStringCellValue().toString();
    
                    cell = row.getCell((short) 2);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    school = cell.getStringCellValue().toString();
    
                    cell = row.getCell((short) 3);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    idCard = cell.getStringCellValue().toString();
    
                    cell = row.getCell((short) 4);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    address = cell.getStringCellValue().toString();
    
                    cell = row.getCell((short) 5);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    email = cell.getStringCellValue().toString();
    
    
                    ExcelTestData data = new ExcelTestData();
                    data.setName(name);
                    data.setGender(gender);
                    data.setSchool(school);
                    data.setIdCard(idCard);
                    data.setEmail(email);
                    data.setAddress(address);
                    list.add(data);
                }
    
                System.out.println("读取的数据"+list);
            } catch (Exception e) {
                log.warn("数据封装异常" + e.getMessage());
                return null;
            }
            return list;
        }
        
    }

d.controller类接收前端传递过来的文件，WjInfo实体类用来封装文件名、md5等信息，MD5防止重复上传。Service和Dao就是连  接数据库操作数据，不写了。

package com.ehrm.pj.sys.controller;
    
    
    import com.ehrm.pj.common.util.ExcelHelper;
    import com.ehrm.pj.sys.entity.ExcelTestData;
    import com.ehrm.pj.sys.entity.WjInfo;
    import com.ehrm.pj.sys.service.UploadService;
    import org.apache.commons.codec.digest.DigestUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.http.ResponseEntity;
    import org.springframework.transaction.annotation.Transactional;
    import org.springframework.transaction.interceptor.TransactionAspectSupport;
    import org.springframework.util.StringUtils;
    import org.springframework.web.bind.annotation.*;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.io.File;
    import java.util.List;
    import java.util.UUID;
    
    /**
     * @Author:xuexia
     * @Date:2020/6/2
     * @Description:接收上传的文件，并作相应处理
     */
    @RequestMapping("/upload")
    @RestController
    public class UploadController {
    
        @Value("${spring.servlet.multipart.location}")
        private String fileDir; //若目录不存在自己创建一下，我的
    
        @Autowired
        private UploadService uploadService;
    
        /**
         * 1.spring支持声明式事务管理和编程式事务管理
         * 2.声明式事务管理@Transactional(rollbackFor=Exception.class)，方法抛出异常回滚，数据库数据也会回滚。
         * 3.在@Transactional注解中如果不配置rollbackFor属性,遇到RuntimeException的时候才会回滚,
         *   加上rollbackFor=Exception.class,事物在遇到非运行时异常时也回滚
         */
        @PostMapping(value = "/excel")
        @Transactional(rollbackFor = Exception.class)
        public ResponseEntity<String> uploadExcel(@RequestParam("file") MultipartFile multipartFile,
                                                  @RequestParam("uname") String uname){
            //当cookie中没有uname时传递的uname是null
            if (StringUtils.isEmpty(uname)||"null".equals(uname)) {
                System.out.println(uname);
                return ResponseEntity.ok("账号参数错误！");
            } else if (null == multipartFile || multipartFile.isEmpty()) {
                return ResponseEntity.ok("文件参数错误！");
            }else {
                try{
                    // 判断Excel的文件名
                    String fileName = multipartFile.getOriginalFilename();
                    System.out.println(fileName);
                    if ( StringUtils.isEmpty(fileName) ) {
                        return ResponseEntity.ok("文件名参数错误！");
                    }
                    // Excel的格式后缀名判断
                    String suffix = fileName.substring(fileName.lastIndexOf("."));
                    if (StringUtils.isEmpty(suffix) || !(".xls".equals(suffix) || ".xlsx".equals(suffix))) {
                        return ResponseEntity.ok("文件格式错误，请上传后缀为xls、xlsx的文件！");
                    }
                    // 判断文件是否已经上传过
                    String md5 = DigestUtils.md5Hex(multipartFile.getBytes());
                    int result = uploadService.checkFileExist(md5);
                    if (result > 0) return ResponseEntity.ok("该文件("+fileName+")已上传过！");
                    //用于存储文件信息数据
                    WjInfo data = new WjInfo();
                    data.setMd5(md5);
                    data.setScr(uname);
                    data.setWjmc(fileName);
                    data.setWjlx("工资表");
                    result = uploadService.insertUploadWJ(data);
                    if (result > 0) {
                        // 将文件保存到指定目录
                        String filePath = fileDir + "/" + uname + "/";
                        System.out.println(filePath);
                        File file = new File(filePath);
                        if (!file.exists()) {
                            file.mkdirs();
                        }
                        String uuid = UUID.randomUUID().toString();
                        File tempFile = new File(filePath, uuid + suffix);
                        if (!tempFile.exists()) {
                            tempFile.mkdirs();
                        }
                        multipartFile.transferTo(tempFile);//转成文件
                        System.out.println("tempFile" + tempFile);
                        List<ExcelTestData> list = ExcelHelper.getDataFromExcel(filePath, tempFile.getName());
                        if (null != list && list.size() > 0) {
                            /*for (ExcelTestData mp: list) {
                                //进行插入数据库操作
                            }*/
                            System.out.println("读取数据成功");
                        } else {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            return ResponseEntity.ok(fileName+"文件内容为空或不符合要求！");
                        }
                    } else {
                        return ResponseEntity.ok(fileName+"上传失败！");
                    }
                } catch (Exception e){
                    //手动回滚
                    //默认spring事务只在发生未被捕获的 runtimeexcetpion时才回滚。
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return ResponseEntity.ok("文件上传异常！");
                }
            }
    
    
            return ResponseEntity.ok("上传成功11");
    
        }
    
    
    }

Excel的文件上传很简单，注意一些逻辑判断即可