springboot + poi 解析 excel

喜欢ヅ旅行 2022-05-16 15:52 279阅读 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/> 
    	</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>
    		  
    		<dependency>
    		        <groupId>org.apache.poi</groupId>
    		        <artifactId>poi-ooxml</artifactId>
    		        <version>3.17</version>
    	        </dependency>
    	          
                   <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