Java代码读取Excel中的数据导入到MySQL中

最近在做一项小测试,将Excel中的数据导入到MySQL中去,由于数据量比较大,并且有多个Excel表,所以写Java代码将数据导入到MySQL中,查了资料,用poi读取数据并导入.
新建工程, 添加poi依赖

<dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.36</version>
        </dependency>
    </dependencies>

读取文件夹下所有Excel文件

package com.demo.service;

import com.demo.folder.ScanFilesException;
import com.demo.mapper.PersonMapper;
import com.demo.model.Person;
import com.demo.utils.PersonBuildUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

@Service
public class ReadService {
  
    private List<Person> personList = new ArrayList<>();
    @Autowired
    private PersonMapper personMapper;
    
    public void readFiles(String path) {
        logger.info("入参:{}",path);
        ArrayList<String> diretorys = new ArrayList<String>();
        File diretory = new File(path);

        if (!diretory.isDirectory()){
            try {
                throw new ScanFilesException('"' + path +'"' +"input path is not a Directory");
            } catch (ScanFilesException e) {
                logger.error("error:{}",e.getMessage());
                e.printStackTrace();
            }
        }
        if (diretory.isDirectory()){
            File[] listFiles = diretory.listFiles();
            for (int i = 0; i < listFiles.length; i++) {
                //如果当前是文件夹,进入递归扫描文件夹
                if (listFiles[i].isDirectory()){
                    logger.info("directory:{}", listFiles[i]);
                    diretorys.add(listFiles[i].getAbsolutePath());
                    readFiles(listFiles[i].getAbsolutePath());
                } else {
                    logger.info("file:{}", listFiles[i]);
                    getXlsData(listFiles[i]);

                }

            }
        }
    }



    private void getXlsData(File file) {
        logger.info("file:{}",file);
        String name = file.getName();

        InputStream is;
        try {
            is = new FileInputStream(file);
            HSSFWorkbook hfw = new HSSFWorkbook(is);
            //获取工作簿
            for (int numSheet =0; numSheet < hfw.getNumberOfSheets(); numSheet ++) {
                HSSFSheet hssfSheet = hfw.getSheetAt(numSheet);
                if (hssfSheet == null){
                    continue;
                }
                //获取当前工作簿的每一行
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    HSSFRow row = hssfSheet.getRow(rowNum);

                    if (row != null){
                        
						Person person = new Person();
						person = PersonBuildUtil.buildPerson(person, row);
						personList.add(person);
						if (personList.size() >= 2500 || rowNum == hssfSheet.getLastRowNum()){
							personMapper.insertPersons(personList);
							personList.clear();
						}
                        
                    }

                }
            }

        } catch (java.io.IOException e) {
            logger.error("error",e.getMessage());
            e.printStackTrace();

        }
    }
}

工具类

package com.demo.utils;

import com.demo.model.Person;
import org.apache.poi.hssf.usermodel.HSSFRow;

public class PersonBuildUtil {
    public static Person buildPerson(Person person,HSSFRow row){
        person.setName(String.valueOf(row.getCell(0)));
        person.setGender(Integer.valueOf(row.getCell(1).toString()));
        person.setCardType(String.valueOf(row.getCell(2)));
        person.setCardNum(String.valueOf(row.getCell(3)));
        person.setBirth(String.valueOf(row.getCell(4)));
        person.setNationality(String.valueOf(row.getCell(5)));
        return person;
    }
}

person类

package com.demo.model;

public class Person {
    private String name;
    private Integer gender;
    private String cardType;
    private String cardNum;//
    private String birth;
    private String nationality;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public String getCardType() {
        return cardType;
    }

    public void setCardType(String cardType) {
        this.cardType = cardType;
    }

    public String getCardNum() {
        return cardNum;
    }

    public void setCardNum(String cardNum) {
        this.cardNum = cardNum;
    }

    public String getBirth() {
        return birth;
    }

    public void setBirth(String birth) {
        this.birth = birth;
    }


    public String getNationality() {
        return nationality;
    }

    public void setNationality(String nationality) {
        this.nationality = nationality;
    }

}

mapper插入数据,list批量插入

package com.demo.mapper;

import com.demo.model.Person;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import java.util.List;

@Component
public interface PersonMapper {
    void insertPersons(List<Person> list);

}

xml插入数据

<?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.demo.mapper.PersonMapper" >

    <insert id="insertPersons" parameterType="java.util.List">
        insert into original_person (name,gender,card_type
		,card_num,birth,nationality)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.name},#{item.gender}, #{item.cardType},#{item.cardNum},
            #{item.birth}, #{item.nationality})
        </foreach>
    </insert>

</mapper>

在插入数据的时候报错,java.lang.NumberFormatException: empty String

排查后发现,是在性别转换那里,原始数据有空数据,获取的时候是null,null值没办法被转换.解决办法,将原始数据修复,或者在转换那里加判断.