vue+springboot上传excel文件并读取

样例

前端代码：

<div>
        <el-upload drag
                   :limit=limitNum
                   :auto-upload="false"
                   accept=".xlsx"
                   action=""
                   :before-upload="beforeUploadFile"
                   :on-change="fileChange"
                   :on-exceed="exceedFile"
                   :file-list="fileList">
          <i class="el-icon-upload"></i>
          <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
          <div class="el-upload__tip" slot="tip">只能上传xlsx文件</div>
        </el-upload>
        <br/>
        <el-button size="small" type="primary" @click="uploadFile">立即导入</el-button>
        <el-button size="small" type="primary" >下载模板</el-button>
        <el-button size="small" @click="cancelUpdate">取消</el-button>
      </div>
<script>
export default {
  name: "FacultyInformation",
  data() {
    thisVue = this;
    return {
      limitNum:1,
      fileList: [],
      }
     }
methods: {
    handleClose(done) {
      this.fileList=[]
      done();
    },
    // 文件超出个数限制时的钩子
    exceedFile(files, fileList) {
      this.$message.warning(`只能选择 ${this.limitNum} 个文件，当前共选择了 ${files.length + fileList.length} 个`);
    },
    // 文件状态改变时的钩子
    fileChange(file, fileList) {
      console.log('file:',file.raw)
      console.log('fileList:',this.fileList)
      this.fileList.pop();
      this.fileList.push(file.raw);
    },
    // 上传文件之前的钩子, 参数为上传的文件,若返回 false 或者返回 Promise 且被 reject，则停止上传
    beforeUploadFile(file) {
      let extension = file.name.substring(file.name.lastIndexOf('.') + 1);
      if (extension !== 'xlsx') {
        this.$message.warning('只能上传后缀是.xlsx的文件');
      }
    },
    //取消上传
    cancelUpdate(){
      this.dialogVisible=!this.dialogVisible
      this.fileList=[]
    },
    uploadFile() {
      if (this.fileList.length === 0) {
        this.$message.warning('请上传文件');
      } else {
      //通过formData的文件传输，必须配置头部，否则将不能正常传输
        const config = {
          headers: { "Content-Type": "multipart/form-data;boundary="+new Date().getTime() },
          isNative:true,
        };
        let form = new FormData();
        var res;
        form.append('file', this.fileList[0]);
        // 请求自己服务器上传文件的接口
        axios.post("/uploadFacultyExcel",form,config).then(function (response) {
          console.log('response:',response);
          alert(response)
          //导入成功，文件栏清空
          var res=response.toString().substr(0,5)
          if (res==="导入成功,"){
            //导入成功刷新页面
            window.location.reload()
          }
        })
      }
    },
    inputFile(){

    }
  },
  }
</script>

后端代码
controller

@RestController
public class ExcelImportController {
    @Autowired
    ExcelImportService excelImportService;

    //上传表格到服务器
    @RequestMapping("/uploadStudentExcel")
    @ResponseBody
    public String uploadStudentExcel(@RequestParam("file") MultipartFile file){
        return excelImportService.uploadStudentExcel(file);
    }
}

service

public interface ExcelImportService {
    String uploadFacultyExcel(MultipartFile file);
    String uploadStudentExcel(MultipartFile file);
}

serviceImpl

@Service
@Transactional
@Slf4j
public class ExcelImportServiceImpl implements ExcelImportService {
    static String fileName;

 

    @Override
    public String uploadStudentExcel(MultipartFile file) {
        XSSFSheet sheetAt = uploadExcel(file);
        //获取最后一行的num，即总行数
        int maxRow = sheetAt.getLastRowNum();
        System.out.println("总行数：" + maxRow);

        for (int row = 1; row <= maxRow; row++) {
            //获取最后一个单元格的num，即每行总的单元格数，此处从1开始计数
            int maxRol = sheetAt.getRow(0).getLastCellNum();

            for (int rol = 0; rol < maxRol; rol++) {
                if (sheetAt.getRow(row).getCell(rol) == null) {
                    return "表格中第"+(row+1)+"行第"+ (char)(65+rol)+"列存在空数据！请修改后重新导入！";
                }
            }
            int finalRow = row;
            XSSFRow sheetAtRow = sheetAt.getRow(row);
            //查询班级是否存在
            XSSFCell period1 = sheetAtRow.getCell(1);
            period1.setCellType(CellType.STRING);
            String period =period1.getStringCellValue();

            XSSFCell classNumber1 = sheetAtRow.getCell(2);
            classNumber1.setCellType(CellType.STRING);
            String classNumber =classNumber1.getStringCellValue();
            MStudentClass mStudentClass = (MStudentClass) HSUtil.query("select e from MStudentClass as e where e.classNumber=:classNumber and e.period=:period and e.deleted=:deleted")
                    .setParameter("classNumber", classNumber)
                    .setParameter("period", period)
                    .setParameter("deleted", false)
                    .uniqueResult();
            if (mStudentClass == null) {
                return "导入的数据中"+period+"届"+classNumber+"班不存在，请先创建班级!";
            }
            //存入数据库
            MStudent mStudent = MStudent.create(e -> {
                XSSFCell studentIDCard = sheetAtRow.getCell(0);
                studentIDCard.setCellType(CellType.STRING);
                e.setStudentIDCard(studentIDCard.getStringCellValue());
                e.setMStudentClass(mStudentClass);

                e.setName(String.valueOf(sheetAtRow.getCell(3)));
                if (String.valueOf(sheetAtRow.getCell(4)).equals("男")) {
                    e.setPersonGender(EMPersonGender.BOY);
                } else if (String.valueOf(sheetAtRow.getCell(4)).equals("女")) {
                    e.setPersonGender(EMPersonGender.GIRL);
                } else {
                    e.setPersonGender(EMPersonGender.UNKNOWN);
                }
                e.setCertificateNo(String.valueOf(sheetAtRow.getCell(5)));
                e.setGatherType(EMGatherType.UNCHECKED);
                e.setSyncState(EMSyncState.TOBESYNCHRONIZED);
            });
        }
        //使用完后删除文件
        File deleteFile = new File(fileName);
        deleteFile.delete();
        return "导入成功!";
    }

    //将excel表上传到服务器
    public XSSFSheet uploadExcel(MultipartFile file) {
        String realPath = "D:\\Download\\uploads\\upload";
        File folder = new File(realPath);
        if (!folder.exists()) {
            folder.mkdir();
        }
        String newName = UUID.randomUUID().toString() + ".xlsx";
        try {
            file.transferTo(new File(folder, newName));

            fileName = realPath + "\\" + newName;
            FileInputStream newFile = new FileInputStream(fileName);
            //创建工作簿
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(newFile);
            //创建工作表
            XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);

            return sheetAt;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}

主要是前端通过formData以键值对的形式存储表格，然后通过axios访问接口，将文件上传到服务器的制定文件夹,然后后端去这个指定的文件夹拿取表格，进行读取，