Spring-boot框架使用poi完成excel导入导出

最近接了个小需求，要求excel的导入导出，以前写过servlet的，ssh的，ssm的，这次来个spring-boot的小demo

1、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.poi</groupId>
    <artifactId>testpoi</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>testpoi</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.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-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
            <!--<scope>provided</scope>-->
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</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-devtools</artifactId>
            <optional>true</optional>
            <scope>true</scope>
        </dependency>
        <!--POI-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>

        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/fr.myprysm.vertx/vertx-json -->
        <dependency>
            <groupId>fr.myprysm.vertx</groupId>
            <artifactId>vertx-json</artifactId>
            <version>0.1.3</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/net.sf.json-lib/json-lib -->
        <dependency>
            <groupId>net.sf.json-lib</groupId>
            <artifactId>json-lib</artifactId>
            <version>2.4</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
2、实体类：

package com.ultrapower.monitor.model;

public class User {

   private Integer id;

   private String uname;

   private String phone;

   private String address;

   private String enrol_date;

   private String des;

   public Integer getId() {
      return id;
   }

   public void setId(Integer id) {
      this.id = id;
   }

   public String getPhone() {
      return phone;
   }

   public void setPhone(String phone) {
      this.phone = phone;
   }

   public String getAddress() {
      return address;
   }

   public void setAddress(String address) {
      this.address = address;
   }


   public String getDes() {
      return des;
   }

   public void setDes(String des) {
      this.des = des;
   }

   public String getUname() {
      return uname;
   }

   public void setUname(String uname) {
      this.uname = uname;
   }

   public String getEnrol_date() {
      return enrol_date;
   }

   public void setEnrol_date(String enrol_date) {
      this.enrol_date = enrol_date;
   }
}

3、dao层也可以说是mapper层：

package com.ultrapower.monitor.dao;


import com.ultrapower.monitor.model.User;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Component
public interface UserMapper {


   List<User> selectUsers();

   void updateUserByName(User user);

   void addUser(User user);

   int selectByName(String name);


}

4、service层：

package com.ultrapower.monitor.service;

import com.ultrapower.monitor.model.User;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

public interface UserService {

   List<User> selectUsers();


   boolean batchImport(String fileName, MultipartFile file) throws Exception;
}

5、serviceImpl:

package com.ultrapower.monitor.service;

import com.ultrapower.monitor.common.MyException;
import com.ultrapower.monitor.dao.UserMapper;
import com.ultrapower.monitor.model.User;
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.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

@Service
public class UserServiceImpl implements UserService {

   @Autowired
   private UserMapper userMapper;


   @Override
   public List<User> selectUsers() {
      return userMapper.selectUsers();
   }


   @Transactional(readOnly = false,rollbackFor = Exception.class)
   @Override
   public boolean batchImport(String fileName, MultipartFile file) throws Exception {
      boolean notNull = false;
      List<User> userList = new ArrayList<>();
      if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
         throw new MyException("上传文件格式不正确");
      }
      boolean isExcel2003 = true;
      if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
         isExcel2003 = false;
      }
      InputStream is = file.getInputStream();
      Workbook wb = null;
      if (isExcel2003) {
         wb = new HSSFWorkbook(is);
      } else {
         wb = new XSSFWorkbook(is);
      }
      Sheet sheet = wb.getSheetAt(0);
      if(sheet!=null){
         notNull = true;
      }
      User user;
      for (int r = 2; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据
         Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象
         if (row == null) {
            continue;
         }

         //sheet.getLastRowNum() 的值是 10，所以Excel表中的数据至少是10条；不然报错 NullPointerException

         user = new User();

         if (row.getCell(0) != null) {
            row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);

            if (row.getCell(0).getCellType() != 1) {//循环时，得到每一行的单元格进行判断
               throw new MyException("导入失败(第" + (r + 1) + "行,用户名请设为文本格式)");
            }

            String name = row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值


            if (name == null || name.isEmpty()) {//判断是否为空
               throw new MyException("导入失败(第" + (r + 1) + "行,用户名未填写)");
            }

            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
            String phone = row.getCell(1).getStringCellValue();

            if (phone == null || phone.isEmpty()) {
               throw new MyException("导入失败(第" + (r + 1) + "行,电话未填写)");
            }
            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
            String address = row.getCell(2).getStringCellValue();

            i