WHCSRL 技术网

使用EasyExcel将本地excel数据读取后导入mysql数据库中_Ivy

EasyExcel 是做什么的?

首先看下EasyExcel解释:
EasyExcel是阿里巴巴开源的一个excel处理框架,是一个基于Java的简单、省内存的读写Excel的开源项目。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。

开源地址:
github地址:https://github.com/alibaba/easyexcel

	光看代码我还是有点懵懵,到小破站搜了相关视频,狂神的一个视频直接解释得一清二楚。几乎直接照着他说的做就好了,当然了要根据自己的实际情况进行适当改动。
  • 1

背景:我做的一个小项目需要把excel表中的数据导入到数据库中,然而有些数据列中还包含了很多数组对象,而这里面的内容是要当成一个一个字段分别储存到数据库里的,因为人工或者excel都不太现实。所以就去了解了一下这个框架的使用。

本篇文章只记录EasyExcel的读(read)

总的步骤来说就是:

  1. 创建excel对应的实体对象Dao类;
  2. 监听器:由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器(根据自己储存的excel位置或者读取需要将监听器配置成能够读取到excel表的样子);
  3. 根据需要选择是否对读取到的数据进行二次处理,然后通过mapper层存入数据库;

表格如下:

注意:直接从实体类对应的参数列开始读取,不要有任何表头

此excel对应的实体类:
使用 @ExcelProperty 注解来说明对应的excel列
在这里插入图片描述
"电阻测试数据"对应的实体类
使用 @ExcelProperty 注解来说明对应的excel列
在这里插入图片描述
如果数据库结构和excel对应的实体类有出入(比如读取到的数组数据还需要经过处理拆分成多个字段),那么还需要额外建立一个与数据库对应的实体类。
数据库创建的表:
在这里插入图片描述
pom.xml 配置:

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

Controller层

@RestController
@Controller
@RequestMapping("/product/charging_station")
public class ChargingStationController {

    @Resource
    CharingStationService charingStationService;
    /**
     * 提取excel表格数据的接口
     */
    @PostMapping()
    public void saveExcel(){
        charingStationService.saveData();
    	}
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

Service层

@Service
public class CharingStationServiceImpl implements CharingStationService {

    @Resource
    ChargingStationMapper chargingStationMapper;
    @Resource
    ExcelUtil excelUtil;
    @Resource
    ChargingStationAcceptanceValueMapper chargingStationAcceptanceValueMapper;

    /**
     * 调用读取excel数据的方法
     */
    @Override
    public void saveData() {
        excelUtil.excelRead();
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

监听器入口
配置好文件地址、文件名、excel对应的实体类

@Component
public class ExcelUtil {

    @Autowired
    private CharingStationServiceImpl charingStationServiceImpl;


    /**
     * 最简单的读
     * 1. 创建excel对应的实体对象 ExcelModel
     * 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link DemoDataListener}
     * 3. 直接读即可
     */

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);
    //文件位置
    String PATH = "D:\excelTest\";


    public void excelRead() {

        // 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        String fileName = PATH + "充电桩验收测试数据.xlsx";
        // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, ExcelModel.class, new DemoDataListener(charingStationServiceImpl)).sheet().doRead();

    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

监听器具体实现

/**
 * Date:2021/10/20
 * Description: excel一行一行的回调监听器
 *
 * @author ivyhu
 */
@Component
public class DemoDataListener extends AnalysisEventListener<ExcelModel> {

    @Resource
    ChargingStationMapper charingStationResistanceReportMapper;
    @Resource
    CharingStationServiceImpl charingStationServiceImpl;

    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);

    /**
     * 有参构造
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     * @param serviceImpl
     */
    public DemoDataListener(CharingStationServiceImpl serviceImpl) {
        this.charingStationServiceImpl = serviceImpl;
    }
    //无参构造
    public DemoDataListener() {
    }
    /**
     * 读取数据或执行这个invoke()方法
     * @param excelModel 类型
     * @param context    分析器
     */
    @Override
    public void invoke(ExcelModel excelModel, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(excelModel));
        //最终要保存到数据库的数据
        ChargingStationModel reportModelData = new ChargingStationModel();

        String productModelNum = "";
        String itemNo = "";
        Double groundResistance = 0.0;
        Double insulationResistance = 0.0;
        Byte type = 1;
        Integer singlePilePower = 0;
        /**
         * 获取excel表中充电设施产品信息
         */
        String str1 = excelModel.getInfoData();
        JSONArray array1 = JSON.parseArray(str1);
        //如果不为空,就解析[{},{},{},{}]类型的数组对象并获取值
        if (!ObjectUtils.isEmpty(array1)) {
            for (Object obj : array1) {
                JSONObject jsonObject = (JSONObject) obj;
                ChargingStationInfoDataExcelModel infoExcelModel = JSONObject.parseObject(String.valueOf((JSONObject) obj), ChargingStationInfoDataExcelModel.class);
                type = infoExcelModel.getType();
                singlePilePower = infoExcelModel.getSinglePilePower();
            }
        }
        /**
         * 获取excel表中电阻测试数据
         */
        String str = excelModel.getResistanceData();
        JSONArray array = JSON.parseArray(str);
        //电阻不为空,就解析[{},{},{},{}]类型的数组对象并获取值
        if (!ObjectUtils.isEmpty(array)) {
            for (Object obj : array) {
                JSONObject jsonObject = (JSONObject) obj;
                ChargingStationResistanceDataExcelModel reportModel = JSONObject.parseObject(String.valueOf((JSONObject) obj), ChargingStationResistanceDataExcelModel.class);
                productModelNum = reportModel.getProductModelNum();
                itemNo = reportModel.getItemNo();
                groundResistance = reportModel.getGroundResistance();
                insulationResistance = reportModel.getInsulationResistance();
                //插入数据到数据库
                reportModelData = new ChargingStationModel(excelModel.getReportNo(), excelModel.getModelNum(), productModelNum, itemNo, groundResistance, insulationResistance, type, singlePilePower,
                        excelModel.getOutputVoltageControlErrorOfCharger(), excelModel.getOutputCurrentControlErrorOfCharger(), excelModel.getActiveStopChargeTestOutputCurrentStopRate(), excelModel.getPassiveStopChargeTestOutputCurrentStopRate(),
                        excelModel.getChargerOutputCurrentAdjustmentTimeAbove20A(), excelModel.getChargerOutputCurrentAdjustmentTimeUnder20A(), excelModel.getImpulseCurrent());
                saveData(reportModelData);
            }
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

        // 这里也要保存数据,确保最后遗留的数据也存储到数据库
        /*saveData();*/
        LOGGER.info("所有数据解析完成!");
    }

    /**
     * 加上存储数据库
     */
    private void saveData(ChargingStationModel resistanceReportModel) {
        //调用mapper插入数据库
        charingStationServiceImpl.save(resistanceReportModel);
    }


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104

知识点:

  1. 转化Json,解析[{},{},{},{}]类型的数组对象获取里面的单个内容
JSONObject jsonObject = (JSONObject) obj;
ChargingStationResistanceDataExcelModel reportModel = JSONObject.parseObject(String.valueOf((JSONObject) obj);
  • 1
  • 2
  1. 使用注解@Component将DemoDataListener和监听器入口交给Spring来管
@Component
public class DemoDataListener extends AnalysisEventListener<ExcelModel> {
...
}
  • 1
  • 2
  • 3
  • 4
  1. 配置好要读取的文件位置
//文件位置
    String PATH = "D:\excelTest\";

    public void excelRead() {

        // 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        String fileName = PATH + "充电桩验收测试数据.xlsx";

        // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, ExcelModel.class, new DemoDataListener(charingStationServiceImpl)).sheet().doRead();

    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

一开始运行的时候报了几个错:

  1. mapper报空指针。两个可能,查了一下发现一是因为没有将DemoDataListener和监听器入口类注入Spring,导致无法找到bean,需要使用@Component。二是没有通过Spring mvc的模型调用, 只是套用了开源框架中的@Test注解运行。
  2. 读取文件失败,因为没有把文件头那栏删掉。

此文只是小菜鸡的一点点记录,有错望各位大佬指正!

推荐阅读