springboot实现上传并解析Excel过程解析

添加pom依赖

  org.apache.poi
  poi
  3.16



  org.apache.poi
  poi-ooxml
  3.16

在springboot中配置MultipartResolver

//配置文件上传
@Bean(name = "multipartResolver")
public MultipartResolver multipartResolver() {
  CommonsMultipartResolver resolver = new CommonsMultipartResolver();
  resolver.setDefaultEncoding("UTF-8");
  // resolveLazily属性启用是为了推迟文件解析，以在在UploadAction中捕获文件大小异常
  resolver.setResolveLazily(true);
  resolver.setMaxInMemorySize(40960);
  // 上传文件大小 5G
  resolver.setMaxUploadSize(5 * 1024 * 1024 * 1024);
  return resolver;
}

Excel解析工具类

package com.sanyu.tender.util.excelAnalysis;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.*;
import java.util.ArrayList;

public class Analysis {
  public Analysis() {
    throw new Error("工具类不允许实例化！");
  }
  
  public static ArrayList> analysis(MultipartFile file) {
    ArrayList> row = new ArrayList<>();
    //获取文件名称
    String fileName = file.getOriginalFilename();
    System.out.println(fileName);

    try {
      //获取输入流
      InputStream in = file.getInputStream();
      //判断excel版本
      Workbook workbook = null;
      if (judegExcelEdition(fileName)) {
 workbook = new XSSFWorkbook(in);
      } else {
 workbook = new HSSFWorkbook(in);
      }

      //获取第一张工作表
      Sheet sheet = workbook.getSheetAt(0);
      //从第二行开始获取
      for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
 //循环获取工作表的每一行
 Row sheetRow = sheet.getRow(i);
 //循环获取每一列
 ArrayList cell = new ArrayList<>();
 for (int j = 0; j < sheetRow.getPhysicalNumberOfCells(); j++) {
   //将每一个单元格的值装入列集合
   cell.add(sheetRow.getCell(j).getStringCellValue());
 }
 //将装有每一列的集合装入大集合
 row.add(cell);

 //关闭资源
 workbook.close();
      }
    } catch (FileNotFoundException e) {
      e.printStackTrace();
      System.out.println("===================未找到文件======================");
    } catch (IOException e) {
      e.printStackTrace();
      System.out.println("===================上传失败======================");
    }
    return row;
  }
  
  private static boolean judegExcelEdition(String fileName){
    if (fileName.matches("^.+\.(?i)(xls)$")){
      return false;
    }else {
      return true;
    }
  }
}

前台和controller层代码

html：

js：

function isExcel(){
  var fileInput = document.getElementById("input-file").value;
  //判断文件是否为空
  if (fileInput == null || fileInput.trim() == ''){
    //layer.msg('请上传文件！');
    return false;
  }else {
    //判断文件是否为excel文件
    var reg =/^.*.(?:xls|xlsx)$/;
    if (!reg.test(fileInput)){
      //layer.msg('请上传Excel文件！');
      return false;
    }else {
      return true;
    }
  }
}

function uplaodFile() {
  //当文件符合标准的时候才上传
  if (isExcel()){
    //将上传组件添加进临时表单
    var form = new FormData();
    form.append("file", document.getElementById("input-file").files[0]);
    //提交表单
    $.ajax({
      url: "http://localhost:8080/upload",
      data: form,
      cache: false,
      async: false,
      type: "POST",
      dataType: 'json',
      processdata: false,
      contentType: false,
      success: function (data) {
 var status = data["status"]
 if(status == "success"){
   alert("解析成功！");
 }else {
   alert("解析失败！");
 }
      },
      error: function (err) {
 console.log(err);
 alert("上传失败！");
      }
    });
  }
}

controller

package com.sanyu.tender.controller.util;
import com.sanyu.tender.util.excelAnalysis.Analysis;
import javafx.scene.control.alert;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Controller
public class importFileController {
  //接受文件上传
  @RequestMapping("/upload")
  @ResponseBody
  public Map uploadFile(MultipartFile file){
    Map map = new HashMap<>(16);
    //解析excel文件
    List> row = Analysis.analysis(file);
    //打印信息
    for (int i = 0;i cell = row.get(i);
      for (int j = 0;j

测试


准备导入的excel文件

运行后的控制台打印的数据

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持考高分网。