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

 

介绍

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文件导入依赖

        <!--Excel-->
        <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的文件上传很简单，注意一些逻辑判断即可