SpringBoot整合SpringBoot-POI：将excel导入到mysql中

POI:  Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 .NET的开发人员则可以利用NPOI (POI for .NET) 来存取 Microsoft Office文档r的功能。

poi的相关api也挺多的，这里介绍怎么将Excel导入Mysql

场景：需要将一个用户信息的excel表格导入到msql中的user表中，采用poi自动导入。

思想：

• 将excel表格数据的每一行读出
• 读取一行中的每一列，用来构建实体类
• 将实体类添加的集合中去
• 依次保存到数据库中

第一步：在pom文件中配置poi的依赖

      <!-- excel 导入 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

第二步骤：添加与数据库关联的实体类

package com.whut.entity;

import javax.persistence.*;

@Entity
@Table(name = "test_users")//映射数据库表名
public class TestUser {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)//自增生成策略
    @Column(name = "id")
    private Integer id;

    @Column(name = "username")
    private String username;

    @Column(name = "password")
    private String password;

    @Column(name = "phone")
    private String phone;

    @Column(name = "sex")
    private String sex;

    public TestUser() {
    }


    public TestUser(String username, String password, String phone, String sex) {
        this.username = username;
        this.password = password;
        this.phone = phone;
        this.sex = sex;
    }

    public Integer getId() {
        return id;
    }

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

    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;
    }

    public String getPhone() {
        return phone;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "TestUser{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", phone='" + phone + '\'' +
                ", sex='" + sex + '\'' +
                '}';
    }
}

第三步：编写Jpa接口来保存到数据库

package com.whut.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import com.whut.entity.TestUser;

public interface TestUserRepository extends JpaRepository<TestUser,Integer> {

}

第四步：编写service层（重点层）

package com.whut.service;

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.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import com.whut.entity.TestUser;
import com.whut.repository.TestUserRepository;
import com.whut.utils.ExcelImportUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class ExcelServiceImp implements ExcelService{

    @Autowired
    private TestUserRepository testUserRepository;

    private static final String TGA = "ExcelService : ";

    private String result = "";//返回的结果

    private String errorMsg = "";//记录错误信息


    private int getTotalRows(Workbook wb) {
        //获取第一个sheet的所有行数
        return wb.getSheetAt(0).getPhysicalNumberOfRows();
    }

    private Sheet getSheet(Workbook wb) {
        //将第一个sheet返回回去
        return wb.getSheetAt(0);
    }

    private int getTotalCell(Workbook wb) {
        Sheet sheet = getSheet(wb);

        int tempTotalRows = getTotalRows(wb);

        //得到Excel的列数(前提是有行数)，从第二行算起
        //这个可以自定义，从真实数据的一行开始读取
        if (tempTotalRows >= 2 && sheet.getRow(1) != null) {
            return sheet.getRow(1).getPhysicalNumberOfCells();
        }
        return -1;
    }

    /**
     * 分批处理导入
     * @param fileName
     * @param mfile
     * @param userName
     * @param tableName 要导入得数据库表名
     * @return
     */
    public String batchImport(String fileName, MultipartFile mfile, String userName, String tableName) {


        File uploadDir = new File("E:\\fileupload");
        //创建一个目录 （它的路径名由当前 File 对象指定，包括任一必须的父路径。）
        if (!uploadDir.exists()) uploadDir.mkdirs();
        //新建一个文件
        File tempFile = new File("E:\\fileupload\\" + new Date().getTime() + ".xlsx");
        //初始化输入流
        InputStream is = null;
        try {
            //将上传的文件写入新建的文件中
            mfile.transferTo(tempFile);

            //根据新建的文件实例化输入流
            is = new FileInputStream(tempFile);

            //根据版本选择创建Workbook的方式
            Workbook wb = null;
            //根据文件名判断文件是2003版本还是2007版本
            if (ExcelImportUtils.isExcel2007(fileName)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = new HSSFWorkbook(is);
            }
            //根据excel里面的内容读取知识库信息
            return readExcel(tableName, wb, userName, tempFile);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    e.printStackTrace();
                }
            }
        }
        return "导入出错！请检查数据格式！";


    }//batchImport

    /**
     *
     * 返回对应表的读取实列
     *
     * 根据表名来确定要上传到得数据库
     * @param tableName 要上传到数据库的表名
     * @param wb
     * @param userName
     * @param file
     * @return
     */
    private String readExcel(String tableName, Workbook wb, String userName, File file) {

        switch (tableName) {
            case "test_users":
                return readTestUserExcelValue(wb, userName, file);
//            case "db_research_group2":
//                return readResearchGroup2ExcelValue(wb, userName, file);
//            case "db_research_group3":
//                return readResearchGroup3ExcelValue(wb,userName,file);
//            case "db_research_group4":
//                return readResearchGroup4ExcelValue(wb,userName,file);
//            case "db_research_group5":
//                return readResearchGroup5ExcelValue(wb,userName,file);
//            case "db_research_group7":
//                return readResearchGroup7ExcelValue(wb,userName,file);
//            case "db_research_group8":
//                return readResearchGroup8ExcelValue(wb,userName,file);
        }
        return null;
    }

    /**
     *
     * @param wb
     * @param userName
     * @param tempFile 临时文件，在文件读入msql后执行删除操作
     *
     * 思路：将excel表格一行行读出，
     *        循环其中得每一行，再一列列得读取出其中得每一个cell
     *         将其保存到实体类中
     *         一次保存到数据库中
     * @return
     */
    private String readTestUserExcelValue(Workbook wb, String userName, File tempFile){

        //得到第一个sheet
        Sheet sheet = getSheet(wb);
        //得到Excel的行数
        int totalRows = getTotalRows(wb);
        //总列数
        int totalCells = getTotalCell(wb);
        //得到Excel的列数(前提是有行数)，从第二行算起

        List<TestUser> testUsers = new ArrayList<>();
        TestUser testUser;

        String br = "<br/>";
        //循环excel中的每一行数据
        for(int r = 1;r<totalRows;r++){
            String rowMessage = "";
            Row row = sheet.getRow(r);
            if (row == null) {
                errorMsg += br + "第" + (r + 1) + "行数据有问题，请仔细检查！";
                continue;
            }

            testUser = new TestUser();


            //循环excel中的每一列数据
            for(int c=0;c<totalCells;c++){
                //获取每一列的一个单元格
                Cell cell = row.getCell(c);//获取每一个单元格cell

                if(cell != null){
                    switch (c){
                        case 0:
                            //设置读取的字段为字符类型
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            testUser.setUsername(cell.getStringCellValue());//读取并设置到实体类中
                            break;
                        case 1:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            testUser.setPassword(cell.getStringCellValue());
                            break;
                        case 2:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            testUser.setPhone(cell.getStringCellValue());
                            break;
                        case 3:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            testUser.setSex(cell.getStringCellValue());
                            break;
                    }//end switch
                }//end if

            }//end for遍历完每一列数据

            //拼接每行的错误提示
            if (!StringUtils.isEmpty(rowMessage)) {

                errorMsg += br + "第" + (r + 1) + "行，" + rowMessage;
                System.out.println(TGA+errorMsg);

            } else {
                //数据正常将其添加到集合中
                testUsers.add(testUser);
            }//end if

        }//遍历完每一行数据

        //删除上传的临时文件
        if (tempFile.exists()) {
            tempFile.delete();
        }

        //将数据导入到Mysql中
        for(TestUser t:testUsers){
            this.testUserRepository.save(t);
        }

        result = "导入成功，共" + testUsers.size() + "条数据！";

        return result;
    }

}

第五步：编写工具类Utils

package com.whut.utils;

/**
 * excel导入mysql的工具
 *
 * @author zm
 */
public class ExcelImportUtils {

    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;
    }

}

第六步：编写controller层(访问层)

package com.whut.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.CrossOrigin;
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 com.whut.Model.BaseModel;
import com.whut.contant.Constant;
import com.whut.service.ExcelServiceImp;
import com.whut.utils.ExcelImportUtils;

@CrossOrigin(origins = "*")//开启跨域
@Controller
@RequestMapping("/excelImportToMysql")
public class ExcelController {

    @Autowired
    private ExcelServiceImp excelServiceImp;

    private static final String TGA = "ExcelController : ";


    @RequestMapping("/batchImportExcel")
    @ResponseBody
    public BaseModel batchImportExcel(
            @RequestParam(value = "filename") MultipartFile file,
            @RequestParam(value = "tableName") String tableName
    ) {


        System.out.println("batchImportExcel");

        if (file == null) {
            return new BaseModel(
                    Constant.ERROR,
                    "文件不能为空",
                    null
            );
        }

        String fileName = file.getOriginalFilename();

        //验证文件名是否合格
        if (!ExcelImportUtils.validateExcel(fileName)) {
            return new BaseModel(
                    Constant.ERROR,
                    "文件必须是excel格式",
                    null
            );
        }

        //进一步判断文件内容是否为空（即判断其大小是否为0或其名称是否为null）
        long size = file.getSize();

        if (StringUtils.isEmpty(fileName) || size == 0) {
            return new BaseModel(
                    Constant.ERROR,
                    "文件内容不能为空不能为空",
                    TGA
            );
        }

        String result = excelServiceImp.batchImport(fileName, file, "admin", tableName);

        return new BaseModel(
                Constant.SUCCESS,
                "导入成功:  "+result,
                null
        );

    }


}

注：

BaseModel是用来封装返回数据

package com.whut.Model;

public class BaseModel {
    //状态代码 方便前端匹配错误码表
    public int code;

    //可读性错误信息
    public String msg;

    public Object data;

    public BaseModel(int code, String msg) {
        this.code = code;
        this.msg = msg;
    }

    public BaseModel(int code, String msg, Object data) {
        this.code = code;
        this.msg = msg;
        this.data = data;
    }

    public BaseModel() {
    }

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public Object getData() {
        return data;
    }

    public void setData(Object data) {
        this.data = data;
    }

    @Override
    public String toString() {
        return "BaseModel{" +
                "code=" + code +
                ", msg='" + msg + '\'' +
                '}';
    }
}

第七步：测试编写得接口

1.使用PostMan来请求

2.检查控制台日志：

 

3.检查数据库：