本文共 9370 字,大约阅读时间需要 31 分钟。
摘要:本文主要讲解,在SpringBoot项目中如何使用poi上传excel文件。
SpringBoot整合poi和springmvc整合poi差别不大,springmvc整合poi也可以借鉴此文;由于后面打算写springboot异步处理任务,和多线程处理任务,所以先写此篇,可以为后面准备数据场景。
目录:
1.pom.xml引入依赖
2.application.properties配置属性
3.写上传页面
4.ExcelParserUtil工具类
5.ExcelController写接口
6.ExcelServiceImpl写Excel处理逻辑
1.pom.xml引入依赖
org.apache.poi poi-ooxml 3.9
2.application.properties配置属性
这个属性不是必须的,如果不配置此属性,当文件大于1Mb时,会报错,因为springboot默认的上传单个文件大小为1Mb;
报错如下:
org.apache.tomcat.util.http.fileupload.FileUploadBase$FileSizeLimitExceededException: The field file exceeds its maximum permitted size of 1048576 bytes.
配置如下,这里配置为10Mb:
# 设置上传文件大小 默认是1Mb spring.servlet.multipart.max-file-size=10Mb spring.servlet.multipart.max-request-size=10Mb
3.写上传页面
我们复用之前,只是把请求接口修改为我们解析excel文件的接口。
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8" %>
4.ExcelParserUtil工具类
我们写个简单的工具类,方便后面调用。
package com.java4all.utils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * Author: momo * Date: 2018/4/11 * Description:Excel解析工具类 */ public class ExcelParserUtil { private static Logger logger = LoggerFactory.getLogger(""); /** * 是否是2003的excel,返回true 是2003 * @param filePath * @return */ public static boolean isExcel2003(String filePath) { return filePath.matches("^.+\\.(?i)(xls)$"); } /** * 是否是2007的excel,返回true 是2007 * @param filePath * @return */ public static boolean isExcel2007(String filePath) { return filePath.matches("^.+\\.(?i)(xlsx)$"); } /** * 验证是否为EXCEL文件 * @param filePath * @return */ public static boolean validateExcel(String filePath){ if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){ return false; } return true; } }
5.ExcelController写接口
写个接口,页面访问此接口上传文件。
package com.java4all.controller; import com.java4all.service.ExcelService; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile; /** * Author: momo * Date: 2018/4/11 * Description:表格处理 */ @RestController @RequestMapping("excel") public class ExcelController { private static Logger logger = LoggerFactory.getLogger(""); @Autowired private ExcelService excelService; @RequestMapping(value = "parseExcel",method = RequestMethod.POST) public String parseExcel(MultipartFile file){ try { String ss = excelService.parseExcel(file); return ss; }catch (Exception ex){ logger.info("文件解析出错",ex); return "文件解析出错"; } } }
6.ExcelServiceImpl写Excel处理逻辑
文件上传后,excel的解析代码很简单;我这里的逻辑是:接收文件-->对文件做判断-->解析文件,将数据转为User对象列表-->对列表数据做处理:将列表中省份为陕西省的user挑选出来。
文末附上User实体,也可以参考一文,excel表格读者自行创建。
此过程无特殊之处,如果只是想学习springboot如何整合poi,此步骤可以省略;筛选陕西省只是为了后面改造为异步或者多线程提供基础。
ExcelService.java
package com.java4all.service; import org.springframework.web.multipart.MultipartFile; /** * Author: momo * Date: 2018/4/11 * Description:表格处理 */ public interface ExcelService { /** * 解析Excel文件 * @param file * @return * @throws Exception */ String parseExcel(MultipartFile file) throws Exception; }
ExcelServiceImpl.java
package com.java4all.serviceImpl; import com.java4all.entity.User; import com.java4all.service.ExcelService; import com.java4all.utils.ExcelParserUtil; 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.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.util.ArrayList; import java.util.List; /** * Author: momo * Date: 2018/4/11 * Description: */ @Service public class ExcelServiceImpl implements ExcelService{ private static Logger logger = LoggerFactory.getLogger(""); /** * 分析数据 * @param list * @return */ public List getUserByProvince(Listlist) { List list1 = new ArrayList(); for (int i = 0,length = list.size();i < length;i++){ User user = list.get(i); if("陕西省".equals(user.getProvince())){ list1.add(user); logger.info("==========>符合条件:"+user.getRealName()); } } return list1; } @Override public String parseExcel(MultipartFile file) throws Exception{ String fileName = file.getOriginalFilename(); logger.info("====>文件名:"+fileName); //判断是否为null if(null == file){ return "文件为空,请重新上传"; } //判断是否为Excel格式 if(!ExcelParserUtil.validateExcel(fileName)){ return "文件必须是Excel格式,请重新上传"; } //判断内容是否为空 long size = file.getSize(); if(0 == size){ return "文件内容为空,请重新上传"; } //将文件备份到服务器/本地 File fileDir = new File("E:\\fileDir"); if(!fileDir.exists()){ fileDir.mkdirs(); } //String filePath = fileDir+"\\"; File fileNew = new File(fileDir,System.currentTimeMillis()+ "-"+ fileName); file.transferTo(fileNew); //创建输入流 InputStream is = new FileInputStream(fileNew); Workbook wb = null; //根据文件名判断文件是2003还是2007 if(ExcelParserUtil.isExcel2003(fileName)){ wb = new HSSFWorkbook(is); }else { wb = new XSSFWorkbook(is); } List users = readExcel(wb); List listPS = this.getUserByProvince(users); logger.info("=======>处理完毕,符合条件数据有:"+listPS.size()+"条"); return "处理完毕"; } /** * 解析Excel表格数据 * @param wb * @return */ public List readExcel(Workbook wb){ //错误信息 String errorMessage = ""; //获取第一个sheet Sheet sheet0 = wb.getSheetAt(0); //行数 int totalRows = sheet0.getPhysicalNumberOfRows(); //列数 int totalCells = 0; //第二行算起且不为空 if(totalRows >= 2 && null != sheet0.getRow(1)){ totalCells = sheet0.getRow(1).getPhysicalNumberOfCells(); } //解析数据转为user对象 List list = new ArrayList<>(); for(int i = 1,length = totalRows;i < totalRows;i ++){ Row row = sheet0.getRow(i); if(null == row){ logger.info("===========>第"+i+"行数据有问题,请核查数据!"); } User user = new User(); user.setUserName(row.getCell(1).getStringCellValue()); user.setRealName(row.getCell(2).getStringCellValue()); user.setPassword(row.getCell(3).getStringCellValue()); user.setCountry(row.getCell(4).getStringCellValue()); user.setProvince(row.getCell(5).getStringCellValue()); user.setCity(row.getCell(6).getStringCellValue()); user.setPhone(row.getCell(7).getNumericCellValue()+""); user.setDevLanguage(row.getCell(9).getStringCellValue()); user.setSchoolName(row.getCell(10).getStringCellValue()); user.setSchoolMajor(row.getCell(11).getStringCellValue()); list.add(user); } return list; } }
附:user实体类
package com.java4all.entity; import javax.persistence.*; import java.io.Serializable; import java.util.Date; /** * Author: momo * Date: 2018/3/26 * Description: */ @Entity @Table(name="user") public class User extends PageEntity implements Serializable{ @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "id",nullable = false) private Integer id ; @Column(name = "userName",nullable = true) private String userName; @Column(name = "real_name",nullable = true) private String real_name; @Column(name = "password",nullable = true) private String password; @Column(name = "country",nullable = true) private String country; @Column(name = "province",nullable = true) private String province; @Column(name = "city",nullable = true) private String city; @Column(name = "phone",nullable = true) private String phone; @Column(name = "createTime",nullable = true) private Date createTime; @Column(name = "devLanguage",nullable = true) private String devLanguage; @Column(name = "schoolName",nullable = true) private String schoolName; @Column(name = "schoolMajor",nullable = true) private String schoolMajor; }
下一篇,我们会将此处理数据的过程改为异步处理。
↓↓点击 阅读原文 ,查看完整源码。与其相忘江湖,不如点赞关注
本文为 java4all (公众号:java4all)原创
欢迎转载,请注明出处或文末给出二维码
谢谢!
转载地址:https://it4all.blog.csdn.net/article/details/89037686 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!