# Springboo 和Mybatis 导入 Excel 文件保存到数据库

---

操作的表格信息如下：

1. 引入POI依赖

  <!--引入POI依赖-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.13</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.13</version>
        </dependency>

2. ExcelImportUtils

package com.example.fileupload.util;

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.stereotype.Repository;

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

@Repository
public class ImportExcelUtils {

    private final static String excel2003 =".xls";
    private final static String excel2007 =".xlsx";


    public  List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception{
        List<List<Object>> list = null;

        //创建Excel工作薄
        Workbook work = this.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }

        //表中的sheet
        Sheet sheet = null;
        //行數
        Row row = null;
        //列數
        Cell cell = null;

        list = new ArrayList<List<Object>>();

        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet==null){continue;}

            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if(row==null||row.getFirstCellNum()==j){continue;}

                List<Object> li = new ArrayList<Object>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    li.add(cell);
                }
                list.add(li);
            }
        }
        work.close();
        return list;
    }
    //判断excel文件的格式
    public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if(excel2003.equals(fileType)){
            //使用.xls的方法
            wb = new HSSFWorkbook(inStr);
        }else if(excel2007.equals(fileType)){
            //使用xlsx
            wb = new XSSFWorkbook(inStr);
        }else{
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }
}

3. applicationa.yml

server:
  port: 8180
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/product?useUnicode=yes&characterEncoding=UTF-8&useSSL=false
    username: root
    password: Rjxy
    driver-class-name: com.mysql.jdbc.Driver
  thymeleaf:
    mode: LEGACYHTML5
    cache: false

mybatis:
  mapper-locations: classpath:/mybatis/mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true
logging:
  level:
    com.example.fileupload.mapper: DEBUG

4. 实体类都是由Mybatis的代码生成器生成的
   
   generator-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <context id="MySQLTables" targetRuntime="MyBatis3">
        <!--Official Plugins-->
        <!--Generate equals and hash code-->
        <plugin type="org.mybatis.generator.plugins.EqualsHashCodePlugin"/>

        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/product?useUnicode=yes&amp;characterEncoding=UTF-8&amp;useSSL=false"
                        userId="root"
                        password="123456">
            <property name="useInformationSchema" value="true"/>
        </jdbcConnection>

        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>

        <javaModelGenerator targetPackage="com.example.fileupload.domain" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>

        <sqlMapGenerator targetPackage="mapper" targetProject="src/main/resources/mybatis">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>

        <javaClientGenerator type="XMLMAPPER" targetPackage="com.example.fileupload.mapper" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>

        <!--<table schema="test" tableName="pic" domainObjectName="Pic">-->
            <!--<generatedKey column="id" sqlStatement="select uuid_short()" identity="false"/>-->

        <!--</table>-->

        <!--<table schema="test" tableName="t_order" domainObjectName="Order">-->
            <!--<generatedKey column="order_id" sqlStatement="select uuid_short()" identity="false"/>-->

        <!--</table>-->

        <table schema="test" tableName="product" domainObjectName="ProductExcel">
            <generatedKey column="p_id" sqlStatement="select uuid_short()" identity="false"/>
        </table>

    </context>
</generatorConfiguration>

5. ImportExcelController

package com.example.fileupload.controller;

import com.example.fileupload.domain.ProductExcel;
import com.example.fileupload.service.ProductExcelService;
import com.example.fileupload.util.ImportExcelUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import java.io.InputStream;
import java.util.List;

@Controller
@RequestMapping("/excel")
public class ImportExcelController {

    @Autowired
    private ImportExcelUtils importExcelUtils;
    @Autowired
    private ProductExcelService productExcelService;


    @RequestMapping("/show")
    public String index(){
        return "excel";
    }

    @RequestMapping(value="/upload",method= RequestMethod.POST)
    public  String  uploadExcel(HttpServletRequest request) throws Exception {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;

        InputStream inputStream =null;
        List<List<Object>> list = null;

        MultipartFile file = multipartRequest.getFile("filename");

        System.out.println("得到的文件为"+file);

        if(file.isEmpty()){
            return "文件不能为空";
        }
        inputStream = file.getInputStream();
        System.out.println("输出的文件为"+inputStream);
        System.out.println(file.getOriginalFilename());

        list = importExcelUtils.getBankListByExcel(inputStream,file.getOriginalFilename());
        System.out.println("输出list"+list.size());

        inputStream.close();
        //连接数据库部分
        for (int i = 0; i < list.size(); i++) {
            List<Object> lo = list.get(i);

            System.out.println("遍历输出"+lo);
            System.out.println(String.valueOf(lo.get(0))+"====="+String.valueOf(lo.get(1))
            +"====="+String.valueOf(lo.get(2))+"====="+String.valueOf(lo.get(3))+"======"+String.valueOf(lo.get(4)));

            ProductExcel productExcel=new ProductExcel();
            productExcel.setpId(String.valueOf(lo.get(0)));
            productExcel.setName(String.valueOf(lo.get(1)));
            productExcel.setYuanliao(String.valueOf(lo.get(2)));
            productExcel.setMerchants(String.valueOf(lo.get(3)));
            productExcel.setPic(String.valueOf(lo.get(4)));

            //调用mapper中的insert方法
            productExcelService.insert(productExcel);

        }
        return "excel";
    }

}

6. 数据库文件

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `p_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `name` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `yuanliao` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `pic` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `merchants` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

7. 码云地址：
   https://gitee.com/Marlon_Brando/tableandupload.git