springboot + poi 解析 excel 喜欢ヅ旅行 2022-05-16 15:52 204阅读 0赞 [Apache POI][] 是用 [Java][] 编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对 [Microsoft Office][] 格式档案读和写的功能。 本文使用 springboot + mybatis + poi ,来介绍如何解析 excel 、并将 excel 内的数据添加到数据库 ## 一、编写代码 ## ### 1、添加依赖 ### 因为 excel 分 2003 以前版本和 2007 以上版本,所以需要引入两个 poi 依赖jar包,以对它们分别解析 <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.dmeo</groupId> <artifactId>springbootpoi</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>springbootpoi</name> <description>Demo project for Spring Boot</description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.0.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-freemarker</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!--poi对excel2007以上版本的支持--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <!--poi对excel2003以下版本的支持--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project> ### 2、配置mybatis ### 由于本文重点是 poi 解析 excel ,这里就不详细谈如何配置 mybatis,如果读者不会这部分内容,可以参看博主的另一篇文章,[springboot 整合 mybatis][springboot _ mybatis] mybatis 配置成功后、yml文件如下 server: port: 9001 spring: datasource: url: jdbc:mysql://10.42.12.67:3306/orcl?useUnicode=true&characterEncoding=utf-8&useSSL=false driver-class-name: com.mysql.jdbc.Driver username: root password: 123456 mybatis: mapper-locations: classpath:mapper/*Mapper.xml config-location: classpath:mapper/config/sqlMapConfig.xml ### 3、数据库建表 ### 建表sql如下 DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `user_id` double DEFAULT NULL, `user_name` varchar(64) DEFAULT NULL, `age` int(11) DEFAULT NULL, `user_addr` varchar(64) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 表格创建后如下 ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70][] ### 4、创建实体类 ### package com.demo.bean; public class User { private double userId; private String userName; private Integer age; private String userAddr; public double getUserId() { return userId; } public void setUserId(double userId) { this.userId = userId; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getUserAddr() { return userAddr; } public void setUserAddr(String userAddr) { this.userAddr = userAddr; } @Override public String toString() { return "User [userId=" + userId + ", userName=" + userName + ", age=" + age + ", userAddr=" + userAddr + "]"; } } ### 5、创建mapper接口层 ### package com.demo.mapper; import java.util.List; import org.apache.ibatis.annotations.Mapper; import com.demo.bean.User; @Mapper public interface UserMapper { //批量将数据添加到数据库 int insertForeach(List<User> list); } ### 6、创建 mapper.xml 文件 ### <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.demo.mapper.UserMapper"> <insert id="insertForeach" parameterType="java.util.List" useGeneratedKeys="false"> insert into user ( user_id, user_name, age, user_addr) values <foreach collection="list" item="item" index="index" separator=","> (#{item.userId}, #{item.userName}, #{item.age}, #{item.userAddr}) </foreach> </insert> </mapper> ### 7、编写上传 excel 页面 ### 此处使用 freemarker 模板引擎 <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>excel上传解析</title> </head> <body> <form action="/importExcel" method="post" enctype="multipart/form-data"> <input type="file" name="file" /> <input type="submit" value="EXCEL上传" /> </form> </body> </html> ### 8、创建 excel 值处理类 ### package com.demo.util; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.xssf.usermodel.XSSFCell; public class ExcelUtils { /** * excel值处理 * @param hssfCell * @return */ public static Object getXSSFValue(XSSFCell hssfCell) { if(hssfCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { return hssfCell.getNumericCellValue(); //数字 }else if(hssfCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) { return hssfCell.getBooleanCellValue(); //boolean }else if(hssfCell.getCellType() == XSSFCell.CELL_TYPE_ERROR){ return hssfCell.getErrorCellValue(); //故障 }else if(hssfCell.getCellType() == XSSFCell.CELL_TYPE_FORMULA){ return hssfCell.getCellFormula(); //公式 }else if(hssfCell.getCellType() == XSSFCell.CELL_TYPE_BLANK) { return ""; //空值 }else { return hssfCell.getStringCellValue(); //字符串 } } /** * excel值处理 * @param hssfCell * @return */ public static Object getValue(Cell hssfCell) { if(hssfCell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC) { return hssfCell.getNumericCellValue(); //数字 }else if(hssfCell.getCellType()==XSSFCell.CELL_TYPE_BOOLEAN) { return hssfCell.getBooleanCellValue(); //boolean }else if(hssfCell.getCellType()==XSSFCell.CELL_TYPE_ERROR){ return hssfCell.getErrorCellValue(); //故障 }else if(hssfCell.getCellType()==XSSFCell.CELL_TYPE_FORMULA){ return hssfCell.getCellFormula(); //公式 }else if(hssfCell.getCellType() == XSSFCell.CELL_TYPE_BLANK) { return ""; //空值 }else { return hssfCell.getStringCellValue(); //字符串 } } } ### 9、创建controller 及解析 excel 主要代码 ### 具体注释已经写到代码中、相信读者能够看懂 package com.demo.controller; import java.io.IOException; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; 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.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.servlet.ModelAndView; import org.springframework.web.servlet.mvc.support.RedirectAttributes; import com.demo.bean.User; import com.demo.mapper.UserMapper; import com.demo.util.ExcelUtils; @Controller public class ExcelController { private static Logger logger = LoggerFactory.getLogger(ExcelController.class); @Autowired private UserMapper userMapper; @RequestMapping("/") public ModelAndView index() { ModelAndView mav = new ModelAndView(); mav.setViewName("index"); return mav; } @RequestMapping("/importExcel") public ModelAndView importExcel(MultipartFile file) throws IOException { String fileName = file.getOriginalFilename(); //获得上传的excel文件名 String fileSuffix = fileName.substring(fileName.lastIndexOf(".")+1); //获取上传的excel文件名后缀 List<User> list = null; if("xlsx".equals(fileSuffix)) { logger.info("excel2007及以上版本"); XSSFWorkbook xwb = new XSSFWorkbook(file.getInputStream()); //获取excel工作簿 XSSFSheet xssfSheet = xwb.getSheetAt(0); //获取excel的sheet if(xssfSheet == null) { return null; } list = new ArrayList<User>(); //循环获取excel每一行 for(int rowNum = 1; rowNum < xssfSheet.getLastRowNum()+1; rowNum++) { XSSFRow xssfRow = xssfSheet.getRow(rowNum); if(xssfRow == null) { continue; } User user = new User(); //循环获取excel每一行的每一列 for(int cellNum = 0; cellNum < xssfRow.getLastCellNum(); cellNum++) { XSSFCell xssCell = xssfRow.getCell(cellNum); if(xssCell == null) { continue; } if(cellNum == 0) { user.setUserId((Double)ExcelUtils.getXSSFValue(xssCell)); }else if(cellNum == 1) { user.setUserName((String)ExcelUtils.getXSSFValue(xssCell)); }else if(cellNum == 2) { //Integer类型需要自行处理 String res = String.valueOf((Double)ExcelUtils.getXSSFValue(xssCell)); Integer.parseInt(res.substring(0, res.length()-2)); user.setAge(Integer.parseInt(res.substring(0, res.length()-2))); }else if(cellNum == 3) { user.setUserAddr((String)ExcelUtils.getXSSFValue(xssCell)); } System.out.print(" "+ExcelUtils.getXSSFValue(xssCell)); } list.add(user); //将excel每一行的数据封装到user对象,并将user对象添加到list System.out.println(""); } }else if("xls".equals(fileSuffix)) { logger.info("excel2003版本"); Workbook wb=new HSSFWorkbook(file.getInputStream()); //获取excel工作簿 Sheet sheet=wb.getSheetAt(0); //获取excel的sheet if(sheet==null) { return null; } list = new ArrayList<User>(); //循环获取excel每一行 for(int rowNum=1;rowNum<sheet.getLastRowNum()+1;rowNum++) { Row row=sheet.getRow(rowNum); if(row==null) { continue; } User user = new User(); //循环获取excel每一行的每一列 for(int cellNum=0;cellNum<row.getLastCellNum();cellNum++) { Cell cell=row.getCell(cellNum); if(cell==null) { continue; } if(cellNum==0) { user.setUserId((Double)ExcelUtils.getValue(cell)); }else if(cellNum==1) { user.setUserName((String)ExcelUtils.getValue(cell)); }else if(cellNum==2) { //Integer类型需要自行处理 String res = String.valueOf((Double)ExcelUtils.getValue(cell)); Integer.parseInt(res.substring(0, res.length()-2)); user.setAge(Integer.parseInt(res.substring(0, res.length()-2))); }else if(cellNum==3) { user.setUserAddr((String)ExcelUtils.getValue(cell)); } System.out.print(" "+ExcelUtils.getValue(cell)); } list.add(user); //将excel每一行的数据封装到user对象,并将user对象添加到list System.out.println(""); } } //将list批量添加到数据库 int count = userMapper.insertForeach(list); System.out.println(count); return null; } } ### 10、总结 ### 经过以上9个步骤、项目结构如下 ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 1][] ## 二、测试 ## 浏览器访问 [http://localhost:9001/][http_localhost_9001] ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 2][] excel xlsx 、xls 文件分别如下 ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 3][] ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 4][] 先上传 红楼梦人物.xls 文件 ,效果如下 ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 5][] 再上传 西游记.xlsx 文件 ,效果如下 ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 6][] [Apache POI]: https://mvnrepository.com/artifact/org.apache.poi/poi [Java]: https://baike.baidu.com/item/Java/85979 [Microsoft Office]: https://baike.baidu.com/item/Microsoft%20Office [springboot _ mybatis]: https://blog.csdn.net/wsjzzcbq/article/details/81563515 [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70]: /images/20220503/be9d37cf154a434ea276d340c4347a70.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 1]: /images/20220503/469a0056f6e14b7e843715cc9920dbc3.png [http_localhost_9001]: http://localhost:9001/ [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 2]: /images/20220503/18feee32d0774c3aab60752e3e833416.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 3]: /images/20220503/92e45e3b502e40958e04ab544227e4cd.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 4]: /images/20220503/46695c7afcbc46339d50b592a09c64bf.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 5]: /images/20220503/551816dfff9f4d0f90db2071672b1efe.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 6]: /images/20220503/1542a5ac47104226bdc4e41d4ab367ca.png
还没有评论,来说两句吧...