easyexcel读取excel将数据存到mysql【一个简单的例子】

菜鸟fox

已于 2022-12-27 14:49:17 修改

阅读量2.1k

点赞数 1

分类专栏： excel 文章标签： excel mysql 数据库

于 2022-12-20 14:45:28 首次发布

本文链接：https://blog.csdn.net/lh155136/article/details/128383679

版权

excel 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

读取excel

1 xml里面增加maven

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

2 读取excel用到的实体

自行更改字段

package com.hahaha.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@EqualsAndHashCode
public class ReadExcelDto {

    /**
     * 账期
     * 用名字去匹配，这里需要注意，如果名字重复，会导致只有一个字段读取到数据
     */
    @ExcelProperty("账期")
    private String month;

	/**
     * 强制读取第三列 这里不建议 index 和 name 同时用，要么一个对象只用index，要么一个对象只用name去匹配
     */
    @ExcelProperty(index = 2)
    private Double doubleData;
   
    /**
     * 姓名
     */
    @ExcelProperty(value = "姓名")
    private String name;
}

3 读取excel用到的监听器

package com.hahaha.util.easyexcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.hahaha.service.CommonExcelReadService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;

/**
 * 公共的excel读取监听器
 * @param <T> 实体范型
 */
public class CommonReadListener<T> extends AnalysisEventListener<T> {
    private static final Logger LOGGER = LoggerFactory.getLogger(CommonReadListener.class);
    /**
     * 每隔1000条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
    List<T> list = new ArrayList<>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private CommonExcelReadService service;

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param service 公共 service
     */
    public CommonReadListener(CommonExcelReadService service) {
        this.service = service;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context context
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        Integer num = service.saveBatchData(list);
        LOGGER.info("存储数据库成功数量为：{}", num);
    }
}

将数据都读取到内存中的监听器

package com.hahaha.util.easyexcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;

/**
 * 公共的excel读取监听器
 * @param <T> 实体范型
 */
public class CommonReadListener2<T> extends AnalysisEventListener<T> {
    private static final Logger LOGGER = LoggerFactory.getLogger(CommonReadListener2.class);

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

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context context
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        list.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 获取excel的所有数据，数据量太大会出现内存溢出
     * @return list
     */
    public List<T> getList() {
        return list;
    }

}

4 读取excel用到的公共service

package com.hahaha.service;

import java.util.List;

/**
 *
 * 公共的excel读取接口
 * 实现接口并重写saveBatchData方法即可保存自定义的业务数据
 */
public interface CommonExcelReadService {

    /**
     * 批量保存数据
     * @param list 要保存的数据
     * @param <T> 数据类型实体，可以根据具体的业务实体转换
     * @return Integer 保存成功的条数
     */
    public <T> Integer saveBatchData(List<T> list);
}

5 读取excel用到的公共方法

package com.hahaha.util.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.hahaha.service.CommonExcelReadService;

import java.io.InputStream;

/**
 * excel工具类
 */
public class EasyExcelUtil {

    /**
     * 这种是 内存溢出出现的情况非常低，但是事务不好控制（主要是出错全部回滚）
     * @param inputStream 文件流
     * @param clazz 读取excel的实体class
     * @param commonService 将数据存储到某张表的service【需要在业务service上实现这个接口，并重写saveBatchData方法】
     * @param <T> 泛型类型
     */
    public static <T> void readExcel(InputStream inputStream, Class<?> clazz, CommonExcelReadService commonService) {
        CommonReadListener readExcelDataListener = new CommonReadListener(commonService);

        EasyExcel.read(inputStream, clazz, readExcelDataListener)
                .sheet()
                .doRead();
    }
    
    /**
     * 这种是 将数据都读取到内存中
     * 事务好控制，但是数据量太大绝对会出现内存溢出
     * @param inputStream 文件流
     * @param clazz 读取excel的实体class
     * @param <T> 泛型类型
     */
    public static <T> List<T> readExcel(InputStream inputStream, Class<?> clazz) {
        CommonReadListener2 readExcelDataListener = new CommonReadListener2();

        EasyExcel.read(inputStream, clazz, readExcelDataListener)
                .sheet()
                .doRead();

        return readExcelDataListener.getList();
    }
}

6

controller接口

package com.hahaha.controller;

import javax.annotation.Resource;
import com.hahaha.util.easyexcel.EasyExcelUtil;
import org.springframework.web.multipart.MultipartFile;

@RestController
public class MyController {

	@Resource
    private MyService myService;

	@PostMapping("upload")
    public 你的返回结构 upload(MultipartFile file) {
    	// 校验文件格式和大小的
        CommonMethod.uploadVerify(file);
        try {
        // todo ReadExcelDto是读取excel的dto，改成自己的；myService改成自己的
            EasyExcelUtil.readExcel(file.getInputStream(), ReadExcelDto.class, myService);
        } catch (IOException o){
            log.error(o.getMessage());
            返回错误信息
        }
        return 返回正确信息
    }
}

校验文件格式和大小看这个
https://blog.csdn.net/lh155136/article/details/126585728

7 业务service和serviceIml

我这个是有mybatisplus的，如果不需要可以去掉

package com.hahaha.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.hahaha.entity.MyEntity;
import com.hahaha.service.CommonExcelReadService;

// todo 这里非常重要，一定要实现CommonExcelReadService 
public interface MyService extends IService<MyEntity>, CommonExcelReadService {

}

package com.hahaha.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.hahaha.dto.ReadExcelMIITDto;
import com.hahaha.entity.MyEntity;
import com.hahaha.mapper.MyMapper;
import com.hahaha.service.MyService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

@Service
public class MyServiceImpl extends ServiceImpl<MyMapper, MyEntity> implements MyService {

    @Resource
    private MyMapper myMapper;

    @Override
    public <T> Integer saveBatchData(List<T> list) {
        List<ReadExcelDto> entityList = JSONObject.parseArray(JSONObject.toJSONString(list),
                ReadExcelDto.class);

        return myMapper.saveBatchList(entityList);
    }
}

7 mapper和mapper.xml

我是手写了一个insertbatch方法，也可以用mybaits-plus的savebatch

package com.hahaha.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hahaha.dto.ReadExcelMIITDto;
import com.hahaha.entity.MyEntity;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface MyMapper extends BaseMapper<MyEntity> {

    int saveBatchList(List<ReadExcelDto> list);
}

	<insert id="saveBatchList">
        INSERT INTO 你的表名字 (
            month,
            name
        )
        VALUES
        <foreach collection="list" item="obj" separator=",">
            (
                #{obj.month},
                #{obj.name}
            )
        </foreach>
	</insert>