SSM框架利用POI把.xlsx表格文件导出成数据库文件

**一、搭建背景及准备条件
idea，maven3.6.0，jdk1.8，tomcat8，mysql5.6

本文章主要是把.xlsx表格文件导出成数据库文件，搭建SSM框架可以参考上一篇文章，本文章只给出数据库文件导出成.xlsx表格文件所需要的新依赖**

把下面配置文件放入Spring-mvc配置文件下

 <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <property name="maxUploadSize" value="10240000">  </property>
        <property name="maxInMemorySize" value="4096"></property>
    </bean>

web.xml需要添加的配置文件

<multipart-config>
      <!--临时文件的目录-->
      <location>d:/</location>
      <!-- 上传文件最大2M -->
      <max-file-size>2097152</max-file-size>
      <!-- 上传文件整个请求不超过4M -->
      <max-request-size>4194304</max-request-size>
    </multipart-config>

下面是pom.xml
导入导出所需要的依赖

  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </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.8</version>
      <exclusions>
        <exclusion>
          <artifactId>commons-codec</artifactId>
          <groupId>commons-codec</groupId>
        </exclusion>
      </exclusions>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.8</version>
    </dependency>

    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.3.3</version>
    </dependency>
    <dependency>
      <groupId>commons-io</groupId>
      <artifactId>commons-io</artifactId>
      <version>2.5</version>
    </dependency>
    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.3.1</version>
    </dependency>
    <!--开发web标配 jstl，servlet-api，junit-->
    <dependency>
      <groupId>jstl</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.1.0</version>
      <scope>provided</scope>
    </dependency>

controller控制层：

 @RequestMapping("/importExcel")
    //@ResponseBody
    public String importExcel(MultipartFile userExcel, HttpServletRequest request, HttpSession session){
        if(userExcel == null){
            session.setAttribute("excelName", "未上传文件，上传失败！");
            return "redirect:queryUserAll";
        }
        String userExcelFileName = userExcel.getOriginalFilename();
        if(!userExcelFileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){
            session.setAttribute("excelName", "文件格式不正确！请使用.xls或.xlsx后缀的文档，导入失败！");
            return "redirect:queryUserAll";
        }
        //导入
        try {
            service.importExcel(userExcel);
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
        session.setAttribute("excelName", "导入成功！");
        return "redirect:queryUserAll";
    }

dao层接口
public void addUser(User user);

service接口

public void importExcel(MultipartFile userExcel) throws IOException, InvalidFormatException;

service实现类

@Override
    public List<User> select(User user) {
        return dao.select(user);
    }

    @Override
    public void importExcel(MultipartFile userExcel) throws IOException, InvalidFormatException {
        //获取输入流
        InputStream inputStream = userExcel.getInputStream();
        //创建读取工作簿
        Workbook workbook = WorkbookFactory.create(inputStream);
        //获取工作表
        Sheet sheet = workbook.getSheetAt(0);
        //获取总行
        int rows=sheet.getPhysicalNumberOfRows();
        if(rows>2){
            //获取单元格
            for (int i = 2; i < rows; i++) {
                Row row = sheet.getRow(i);
                User user =new User();

                try {
                    String id = row.getCell(0).getStringCellValue();
                    user.setId(Integer.parseInt(id));
                } catch (IllegalStateException e) {
                    int id=(int)row.getCell(0).getNumericCellValue();
                    user.setId(id);
                }

                String name = row.getCell(1).getStringCellValue();
                user.setName(name);
                String email = row.getCell(2).getStringCellValue();
                user.setEmail(email);
                String sex = row.getCell(3).getStringCellValue();
                user.setSex(sex);

                try {
                    String age = row.getCell(4).getStringCellValue();
                    user.setAge(Integer.parseInt(age));
                } catch (IllegalStateException e) {
                    int age=(int)row.getCell(4).getNumericCellValue();
                    user.setAge(age);
                }
                //想数据库中添加新对象
                dao.addUser(user);//方法
            }

        }

        inputStream.close();
    }

xml文件

!-- 添加用户 -->
    <insert id="addUser" parameterType="com.tzy.pojo.User">
		insert into tb_employee(id,name,email,sex,age) values(
		#{id},#{name},#{email},#{sex},#{age}	)
    </insert>

index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" %>
<%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";
%>

```java
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">
    <title>My JSP '' starting page</title>
    <script type="text/javascript" src="jquery.js"></script>
</head>

<body>
<form action="${pageContext.request.contextPath}/user/importExcel" method="post" enctype="multipart/form-data">
    <input type="file" name="userExcel" />
    <input type="submit" value="导入">
</form>
</body>
</html>

浏览器运行界面

表格内容

数据库内容

到此利用SSM框架POI转成数据库文件结束啦