SpringBoot整合POI实现文件导出Excel，导入Excel更新Mysql数据库数据

最新推荐文章于 2024-06-10 16:27:56 发布

置顶 lu5211019

最新推荐文章于 2024-06-10 16:27:56 发布

阅读量2.8w

点赞数 20

文章标签： SpringBoot POI JAVA

本文链接：https://blog.csdn.net/qq_38593865/article/details/80888229

版权

上传功能转载自https://blog.csdn.net/xyy1028/article/details/79054749

原创写的非常好，但是每个人都有自己的业务逻辑；所以在研究了一点之后，打上注释，方便新手理解，同时也方便自己记忆；

项目目录

application.properties文件

spring.mvc.view.prefix=/WEB-INF/view/

spring.mvc.view.suffix=.jsp

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test
spring.datasource.username=root
spring.datasource.password=admin
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

##加載mapper配置文件
mybatis.mapper-locations=classpath:mapper/*.xml

pom.xml文件

<?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>


    </dependencies>

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


</project>

1.自定义异常类

package com.poi.testpoi.common;

public class MyException extends RuntimeException {

    private static final long serialVersionUID = 1L;

    /**
     * 错误编码
     */
    private String errorCode;

    /**
     * 消息是否为属性文件中的Key
     */
    private boolean propertiesKey = true;

    /**
     * 构造一个基本异常.
     *
     * @param message
     *            信息描述
     */
    public MyException(String message)
    {
        super(message);
    }

    /**
     * 构造一个基本异常.
     *
     * @param errorCode
     *            错误编码
     * @param message
     *            信息描述
     */
    public MyException(String errorCode, String message)
    {
        this(errorCode, message, true);
    }

    /**
     * 构造一个基本异常.
     *
     * @param errorCode
     *            错误编码
     * @param message
     *            信息描述
     */
    public MyException(String errorCode, String message, Throwable cause)
    {
        this(errorCode, message, cause, true);
    }

    /**
     * 构造一个基本异常.
     *
     * @param errorCode
     *            错误编码
     * @param message
     *            信息描述
     * @param propertiesKey
     *            消息是否为属性文件中的Key
     */
    public MyException(String errorCode, String message, boolean propertiesKey)
    {
        super(message);
        this.setErrorCode(errorCode);
        this.setPropertiesKey(propertiesKey);
    }

    /**
     * 构造一个基本异常.
     *
     * @param errorCode
     *            错误编码
     * @param message
     *            信息描述
     */
    public MyException(String errorCode, String message, Throwable cause, boolean propertiesKey)
    {
        super(message, cause);
        this.setErrorCode(errorCode);
        this.setPropertiesKey(propertiesKey);
    }

    /**
     * 构造一个基本异常.
     *
     * @param message
     *            信息描述
     * @param cause
     *            根异常类（可以存入任何异常）
     */
    public MyException(String message, Throwable cause)
    {
        super(message, cause);
    }

    public String getErrorCode()
    {
        return errorCode;
    }

    public void setErrorCode(String errorCode)
    {
        this.errorCode = errorCode;
    }

    public boolean isPropertiesKey()
    {
        return propertiesKey;
    }

    public void setPropertiesKey(boolean propertiesKey)
    {
        this.propertiesKey = propertiesKey;
    }

}

2.IndexController

package com.poi.testpoi.controller;

import com.poi.testpoi.pojo.User;
import com.poi.testpoi.service.UserService;
import com.poi.testpoi.util.ExcelImportUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

@Controller
public class IndexController {

   @Autowired
   private UserService userService;


   @RequestMapping("/index")
   public String showUser(Model model) {
      List<User> users = userService.selectUsers();
      model.addAttribute("user", users);
      return "index";
   }


   @RequestMapping(value = "/export")
   @ResponseBody
   public void export(HttpServletResponse response) throws IOException {
      List<User> users = userService.selectUsers();

      HSSFWorkbook wb = new HSSFWorkbook();

      HSSFSheet sheet = wb.createSheet("获取excel测试表格");

      HSSFRow row = null;

      row = sheet.createRow(0);//创建第一个单元格
      row.setHeight((short) (26.25 * 20));
      row.createCell(0).setCellValue("用户信息列表");//为第一行单元格设值

      /*为标题设计空间
      * firstRow从第1行开始
      * lastRow从第0行结束
      *
      *从第1个单元格开始
      * 从第3个单元格结束
      */
      CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 2);
      sheet.addMergedRegion(rowRegion);

      /*CellRangeAddress columnRegion = new CellRangeAddress(1,4,0,0);
      sheet.addMergedRegion(columnRegion);*/


      /*
      * 动态获取数据库列 sql语句 select COLUMN_NAME from INFORMATION_SCHEMA.Columns where table_name='user' and table_schema='test'
      * 第一个table_name 表名字
      * 第二个table_name 数据库名称
      * */
      row = sheet.createRow(1);
      row.setHeight((short) (22.50 * 20));//设置行高
      row.createCell(0).setCellValue("用户Id");//为第一个单元格设值
      row.createCell(1).setCellValue("用户名");//为第二个单元格设值
      row.createCell(2).setCellValue("用户密码");//为第三个单元格设值

      for (int i = 0; i < users.size(); i++) {
         row = sheet.createRow(i + 2);
         User user = users.get(i);
         row.createCell(0).setCellValue(user.getUid());
         row.createCell(1).setCellValue(user.getUsername());
         row.createCell(2).setCellValue(user.getPassword());
      }
      sheet.setDefaultRowHeight((short) (16.5 * 20));
      //列宽自适应
      for (int i = 0; i <= 13; i++) {
         sheet.autoSizeColumn(i);
      }

      response.setContentType("application/vnd.ms-excel;charset=utf-8");
      OutputStream os = response.getOutputStream();
      response.setHeader("Content-disposition", "attachment;filename=user.xls");//默认Excel名称
      wb.write(os);
      os.flush();
      os.close();


   }


   @RequestMapping(value = "/import")
   public String exImport(@RequestParam(value = "filename")MultipartFile file, HttpSession session) {

      boolean a = false;

      String fileName = file.getOriginalFilename();

      try {
         a = userService.batchImport(fileName, file);
      } catch (Exception e) {
         e.printStackTrace();
      }
      return "redirect:index";
   }
}

3.UserMapper

package com.poi.testpoi.mapper;

import com.poi.testpoi.pojo.User;

import java.util.List;

public interface UserMapper {


   List<User> selectUsers();

   void updateUserByName(User user);

   void addUser(User user);

   int selectByName(String username);
}

4.User实体类

package com.poi.testpoi.pojo;

public class User {

   private Integer uid;

   private String username;

   private String password;


   public Integer getUid() {
      return uid;
   }

   public void setUid(Integer uid) {
      this.uid = uid;
   }

   public String getUsername() {
      return username;
   }

   public void setUsername(String username) {
      this.username = username;
   }

   public String getPassword() {
      return password;
   }

   public void setPassword(String password) {
      this.password = password;
   }  
}

5.UserService

package com.poi.testpoi.service;

import com.poi.testpoi.pojo.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;
}

6.UserServiceImpl

package com.poi.testpoi.service.Impl;

import com.poi.testpoi.common.MyException;
import com.poi.testpoi.mapper.UserMapper;
import com.poi.testpoi.pojo.User;
import com.poi.testpoi.service.UserService;
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.Date;
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).getCellType() !=1){//循环时，得到每一行的单元格进行判断
            throw new MyException("导入失败(第"+(r+1)+"行,用户名请设为文本格式)");
         }

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




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


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


         if(password==null || password.isEmpty()){
            throw new MyException("导入失败(第"+(r+1)+"行,密码未填写)");
         }


         //完整的循环一次 就组成了一个对象
         user.setUsername(username);
         user.setPassword(password);
         userList.add(user);
      }
      for (User userResord : userList) {
         String name = userResord.getUsername();
         int cnt = userMapper.selectByName(name);
         if (cnt == 0) {
            userMapper.addUser(userResord);
            System.out.println(" 插入 "+userResord);
         } else {
            userMapper.updateUserByName(userResord);
            System.out.println(" 更新 "+userResord);
         }
      }
      return notNull;
   }

}

7.ExcelImportUtils工具类

package com.poi.testpoi.util;

public class ExcelImportUtils {
    
    
    // @描述：是否是2003的excel，返回true是2003 
    public static boolean isExcel2003(String filePath)  {  
        return filePath.matches("^.+\\.(?i)(xls)$");  
    }  
   
    //@描述：是否是2007的excel，返回true是2007 
    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;
  }
}

8.UserMapper配置文件

<?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.poi.testpoi.mapper.UserMapper">

    <resultMap id="BaseResultMap" type="com.poi.testpoi.pojo.User">
        <id column="uid" property="uid" jdbcType="INTEGER"/>
        <result column="username" property="username" jdbcType="VARCHAR"/>
        <result column="password" property="password" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="column_List">
        uid,username,password
    </sql>

    <select id="selectUsers" resultMap="BaseResultMap">
        SELECT
        <include refid="column_List"/>
        FROM user
    </select>

    <update id="updateUserByName" parameterType="com.poi.testpoi.pojo.User">
      update user set password=#{password} where username = #{username}
    </update>

    <insert id="addUser" parameterType="com.poi.testpoi.pojo.User">
        INSERT INTO USER (username,password) VALUES (#{username},#{password})
    </insert>

    <select id="selectByName" resultType="Integer">
        SELECT count(*) FROM user WHERE username=#{username}
    </select>


</mapper>

页面代码

<%@include file="common.jsp" %>
<body>
<div class="table-responsive">
    <table class="table">
        <thead class="Table cell">
            <td>ID</td>
            <td>用户名</td>
            <td>密码</td>
        </thead>
        <tbody>
            <c:forEach var="user" items="${user}">
                <tr class="success">
                    <td>${user.uid}</td>
                    <td>${user.username}</td>
                    <td>${user.password}</td>
                </tr>
            </c:forEach>>
        </tbody>
    </table>
    <a href="/export"><button type="button" class="btn btn-primary">导出</button></a>
    <form class="form-horizontal" id="form_table" action="/import" enctype="multipart/form-data" method="post">
        <br/>
        <br/>
        <button type="submit" class="btn btn-primary">导入</button>
        <input class="form-input" type="file" name="filename"></input>
    </form>
</div>
</body>

页面效果

当点击导出的时候

当点击导入的时候，需要的Excel表格中的内容是这样的(看自己的功能需要)

浏览上述Excel文件之后，点击导入，就会批量把数据导入MYSQL中

总之这个项目使用POI完成了导出Excel，和上传Excel文件更新数据库数据的功能；以后的项目中绝对能用得到；

代码已经上传到百度网盘

链接：https://pan.baidu.com/s/13ckWrZ-cx7yQ13lOt0VxgQ
提取码：qack

项目整个的核心也就是上传和下载两个模块，主要代码看懂，基本就没有问题；此项目只是实现了基本功能，如有错误，多多指正；

lu5211019

关注

20
点赞
踩
163

收藏

觉得还不错? 一键收藏
50
评论
SpringBoot整合POI实现文件导出Excel，导入Excel更新Mysql数据库数据

上传功能转载自https://blog.csdn.net/xyy1028/article/details/79054749原创写的非常好，但是每个人都有自己的业务逻辑；所以在研究了一点之后，打上注释，方便新手理解，同时也方便自己记忆；项目目录application.properties文件spring.mvc.view.prefix=/WEB-I...
复制链接

扫一扫