springboot实现上传并解析Excel过程解析
添加pom依赖
<!-- excel解析包 --> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <!--处理2003 excel--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.16</version> </dependency> <!--处理2007 excel--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.16</version> </dependency>
在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; /** * @Author:huang * @Date:2019-09-10 10:29 * @Description:excel文件解析类 */ public class Analysis { public Analysis() { throw new Error("工具类不允许实例化!"); } /** * 获取并解析excel文件,返回一个二维集合 * @param file 上传的文件 * @return 二维集合(第一重集合为行,第二重集合为列,每一行包含该行的列集合,列集合包含该行的全部单元格的值) */ public static ArrayList<ArrayList<String>> analysis(MultipartFile file) { ArrayList<ArrayList<String>> 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<String> 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; } /** * 判断上传的excel文件版本(xls为2003,xlsx为2017) * @param fileName 文件路径 * @return excel2007及以上版本返回true,excel2007以下版本返回false */ private static boolean judegExcelEdition(String fileName){ if (fileName.matches("^.+\\.(?i)(xls)$")){ return false; }else { return true; } } }
前台和controller层代码
html:
<input type="file" multiple name="file_0" id="input-file" class="input-file">
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; /** * @Author:huang * @Date:2019-09-09 18:41 * @Description:<描述> */ @Controller public class ImportFileController { //接受文件上传 @RequestMapping("/upload") @ResponseBody public Map uploadFile(MultipartFile file){ Map<String,Object> map = new HashMap<>(16); //解析excel文件 List<ArrayList<String>> row = Analysis.analysis(file); //打印信息 for (int i = 0;i<row.size();i++){ List<String> cell = row.get(i); for (int j = 0;j<cell.size();j++){ System.out.print(cell.get(j)+" "); } System.out.println(); } //如果要上传到服务器的话 /*String targetUrl = "D:\\MYURLFILE"; try { file.transferTo(new File(targetUrl, fileName)); } catch (IOException e) { e.printStackTrace(); map.put("status","error"); }*/ map.put("status","success"); return map; } }
测试
准备导入的excel文件
运行后的控制台打印的数据
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们。
赞 (0)