excel文件数据导入mysql数据库

适宜人群：Java web后台开发小白

一.背景介绍

1. 上传excel文件，自动识别文件类型，文件名。
2. 解析文件，对数据进行提取并处理。
3. 导入数据库。
4. 框架背景：spring boot+mybatis

二.工作流程

使用依赖包：

    <!-- https://mvnrepository.com/artifact/com.opencsv/opencsv -->
    <dependency>
        <groupId>com.opencsv</groupId>
        <artifactId>opencsv</artifactId>
        <version>4.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
    <dependency>
        <groupId>commons-io</groupId>
        <artifactId>commons-io</artifactId>
        <version>2.5</version>
    </dependency>

参考文章：

1. opencsv读写操作
2. java解析csv文件
3. csv文件导入mysql
4. Vue+SpringBoot实现前后端分离的文件上传

流程：

1.识别文件类型和文件名：

    private String UPLOAD_FOLDER;
    private Logger logger = LoggerFactory.getLogger(UploadController.class);
    
    @PostMapping("/singlefile")
    public Object singleFileUpload(MultipartFile file) {
        logger.debug("传入的文件参数：{}", JSON.toJSONString(file, true));
        if (Objects.isNull(file) || file.isEmpty()) {
            logger.error("文件为空");
            return "文件为空，请重新上传";
        }

        try {
            byte[] bytes = file.getBytes();
            Path path = Paths.get(UPLOAD_FOLDER + file.getOriginalFilename());
            //如果没有files文件夹，则创建
            if (!Files.isWritable(path)) {
                Files.createDirectories(Paths.get(UPLOAD_FOLDER));
            }
            //文件写入指定路径
            Files.write(path, bytes);
            logger.debug("文件写入成功...");
            return "文件上传成功";
        } catch (IOException e) {
            e.printStackTrace();
            return "后端异常...";
        }
    }

2.实体类：

此代码可实现通过web上传文件到指定路径。

    //创建bean根据位置映射字段
    //需要自己加上get和set方法与构造方法等，此处懒得写了
    public class SimpleBeanInfo {
    @CsvBindByPosition(position=0,required = false)     //required设置字段是否可以为空
    private int age;
    
    @CsvBindByPosition(position=1,required = false)
    private int sex;

    @CsvBindByPosition(position=2,required = false)
    private String name;

    @CsvBindByPosition(position=3,required = false)
    private int id;

    @CsvBindByPosition(position=4,required = false)
    private int file;
    }

3.实现类：

CsvBindByPosition注解：用于实现位置映射，还有另一种注解根据csv文件字段名映射，自行百度。

	try{
			Reader reader = new InputStreamReader(new BOMInputStream(new FileInputStream(file_full_name)), "gbk");		//与数据库的编码格式相对应，不然会乱码
            CSVReader Csvreader = new CSVReader(reader, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER);
            ColumnPositionMappingStrategy<SimpleBeanInfo> mapper = new ColumnPositionMappingStrategy<SimpleBeanInfo>();
            mapper.setType(SimpleBeanInfo.class);
            CsvToBean<SimpleBeanInfo> csvToBean = new CsvToBean<SimpleBeanInfo>();
            List<SimpleBeanInfo> list = csvToBean.parse(mapper, Csvreader);          
            csvMapper.insertList(list);	//对应mapper里面的sql语句
            Csvreader.close();
            reader.close();
}

4.sql语句:

	<!--用于批量导入数据，foreach为动态sql语句，可自行百度-->
	<insert id="insertList" parameterType="java.util.List">
        insert into test_delete1 (age, sex, name, id, file) value
        <foreach collection="list" index="index" item="item" separator=",">
            (#{item.age}, #{item.sex}, #{item.name}, #{item.id}, #{item.file})
        </foreach>
    </insert>

5.过滤器和转化器：

1)作用：

1. 过滤器：可以过滤某些行，比如page header、page footer等。
2. 转化器：类中的属性不一定都是字符串，比如数字、日期等，但是我们从csv中获取到的都是字符串，这种情况就应该使用转化器。（其实开发过程中发现可以直接获取其他数据类型，不过此处依然小本本记上）

2)实现：

a.过滤器：

过滤器需要实现CsvToBeanFilter接口：

import com.opencsv.bean.CsvToBeanFilter;

public class CsvFilter implements CsvToBeanFilter {

    @Override
    public boolean allowLine(String[] line) {
        //过滤第二列值等于123的行
        if("123".equals(line[1])){
            return false;
        }
        return true;
    }
}

接着在实现类中加入：

    //过滤器，此处过滤第二列为123的所有行
    CsvFilter filter = new CsvFilter();
    List<SimpleBeanInfo> list = csvToBean.parse(mapper, Csvreader,filter);		//SimpleBeanInfo为实体类名

b.转化器：

这个暂时没用上，以后用上了再补吧，好懒。

三.遇到的问题

1. csv导入mysql中文字符乱码问题：
   csv文件和mysql编码格式不同，一个是utf-8一个是gbk，在InputStreamReader处设置即可。
   查看数据库编码格式：打开mysql，输入status可以看到。
   修改方式：set names “gbk” （或者utf8，视情况而定）。
2. 批量导入问题：
   文中代码已经解决，如果逐条提取代码，并逐条进行insert插入操作，发现速度有点慢，因此改为批量导入。由于读取时只能一行一行读取（暂时未找到其他好的方法，望各位大神指点），所以创建List<对象>来存数据，直接向sql传入list，用动态sql实现一次性导入操作。
3. 另一种方法：
   命令行处理：mysql自带驱动引擎load data infile命令（类似于select update等增删查改的命令），可快速将csv文件导入数据库，需要提供分字段标识，分行标识，导入变量可选，对空值等可进行异常处理，可对字段的数据类型进行更改。经验证至少5.2以上版本的都可以正常使用。
4. excel分两类文件！
   excel分为.csv后缀文件和.xlsx文件，csv文件比较小，不带花里胡哨的东西，个人感觉就是单纯的数据用逗号隔开。用excel打开时可自动加工成平时看到的excel文件，用notepad打开就可以看到原型。
   下面图1位csv文件，图2为内容完全相同的xlsx文件。