从Excel中批量读取数据

pom 文件

<!--Excel工具-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.4</version>
</dependency>
<dependency>
    <groupId>jakarta.validation</groupId>
    <artifactId>jakarta.validation-api</artifactId>
    <version>2.0.2</version>
    <scope>compile</scope>
</dependency>

监听

package com.test11.test11.util;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.Data;
import lombok.EqualsAndHashCode;
import org.apache.log4j.Logger;

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


@EqualsAndHashCode(callSuper = true)
@Data
public class ExcelListener extends AnalysisEventListener<Object> {
    private static Logger log = Logger.getLogger(ExcelListener.class);
    /**
     * 自定义用于暂时存储data
     */
    private List<JSONObject> dataList = new ArrayList<>();

    /**
     * 导入表头
     */
    // private Map<String, Integer> importHeads = new HashMap<>(16);

    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        String headStr = JSON.toJSONString(data);
        dataList.add(JSONObject.parseObject(headStr));
    }

    /**
     * 这里会一行行的返回头
     */
//    @Override
//    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
//        for (Integer key : headMap.keySet()) {
//            if (importHeads.containsKey(headMap.get(key))) {
//                continue;
//            }
//            importHeads.put(headMap.get(key), key);
//        }
//    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("Excel解析完毕");
    }
}

读取的Excel 的过程

package com.test11.test11.controler;


import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSONObject;
import com.test11.test11.entity.Build;
import com.test11.test11.entity.HouseInfo1;
import com.test11.test11.entity.Unit;
import com.test11.test11.service.HouseInfoService;
import com.test11.test11.util.ExcelListener;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/house-info")
public class ExcelCr {


    @Autowired
    HouseInfoService houseInfoService;

    @RequestMapping("addHouseInfoByExcel")
    @ApiOperation("用excel导入")
    public void insert(){

        // 生成Excel路径
        String fileName = "E:\\333.xlsx";
        ExcelListener excelListener = new ExcelListener();
        EasyExcel.read(fileName, excelListener).sheet().doRead();
        // 表格头数据
        //Map<String, Integer> importHeads = excelListener.getImportHeads();
        //System.out.println(importHeads + "1111");
        // 每一行数据
        List<JSONObject> dataList = excelListener.getDataList();

        for (JSONObject object : dataList) {
            if (object.get(0) == null) {
                continue;
            }
            String obj = object.get(0).toString();
            String[] ss = obj.split("-");
            if (ss.length <= 1) {
//                continue;
            }
            //builder表
//            Build build = new Build();
//            build.setName("缤纷西苑18幢");
//            //build.setName("缤纷西苑"+ss[0]+"幢");
//            //build.setName("缤纷小区12-1幢");
//            //build.setHoseCode("bfsq_bfxq_00"+ss[0]);
//            build.setHoseCode("bfsq_bfxy_018");
//            //build.setHoseCode("bfsq_bfxq_0012-1");
//            if (object.get(5) != null) {
//                build.setArea(Float.valueOf(object.get(5).toString()));
//            }
            //Unit表
//            Unit unit = new Unit();
//            if (ss.length < 3) {
//                unit.setName("1单元");
//                //unit.setCode("bfsq_bfxq_0"+ss[0]+"_01");
//                //unit.setCode("bfsq_bfxy_00"+ss[0]+"_01");
//                unit.setCode("bfsq_bfxy_018_01");
//                unit.setBuildId(1L);
//            } else {
//                //unit.setName(ss[1] + "单元");
//                unit.setName("1单元");
//                unit.setCode("bfsq_bfxy_018_01");
//                //unit.setCode("bfsq_bfxy_00"+ss[0]+"_0"+ss[1]);
//                //unit.setCode("bfsq_bfxq_012-1_0"+ss[1]);
//                //unit.setBuildId(Integer.valueOf(ss[1]));
//                unit.setBuildId(1L);
//            }
            //houseinfo表
            HouseInfo1 houseInfo1 = new HouseInfo1();
            if (ss.length < 3) {
                //houseInfo1.setCode("bf_xy_018_01_"+object.get(11));
                houseInfo1.setCode("bfsq_yyst_0" + ss[0] + "_01_" + ss[1]);
                //houseInfo1.setName("缤纷西苑18幢_1单元_"+object.get(11));
                houseInfo1.setName("印月尚庭" + ss[0] + "幢_1单元_" + ss[1]);
                houseInfo1.setUnitCode("bfsq_yyst_0" + ss[0] + "_01");
                //houseInfo1.setUnitCode("bfsq_bfxy_18_01");
                houseInfo1.setUnitName("1单元");
            } else {
                //houseInfo1.setCode("bf_xy_018_01_"+object.get(11));
                houseInfo1.setCode("bfsq_yyst_0" + ss[0] + "_0" + ss[1] + "_" + ss[2]);
                //houseInfo1.setCode("bf_sq_012-1_0"+ss[1]+"_"+ss[2]);
                //houseInfo1.setName("缤纷西苑18幢_1单元_"+object.get(11));
                houseInfo1.setName("印月尚庭" + ss[0] + "幢_" + ss[1] + "单元_" + ss[2]);
                //houseInfo1.setName("缤纷社区12-1幢_"+ss[1]+"单元"+ss[2]);
                //houseInfo1.setUnitCode("bfsq_bfxy_018_01");
                houseInfo1.setUnitCode("bfsq_yyst_0" + ss[0] + "_0" + ss[1]);
                //houseInfo1.setUnitCode("bfsq_bfxq_011-1_0"+ss[1]);
                //houseInfo1.setUnitCode("bfsq_bfxy_017_01");
                //houseInfo1.setUnitName("1单元");
                houseInfo1.setUnitName(ss[1] + "单元");
                //houseInfo1.setUnitName("1单元");
            }
            houseInfo1.setArea(Float.valueOf(object.get(5).toString()));
            houseInfo1.setCtyCode("bfsq_yyst");
            houseInfo1.setCtyName("印月尚庭");
            houseInfo1.setBuilderCode("bfsq_yyst_0" + ss[0]);
            houseInfo1.setBuilderName(ss[0] + "号楼");
            //houseInfo1.setBuilderName("18号楼");
            //personal表
//            Personal personal = new Personal();
//            if(object.get(2)!=null){
//                personal.setName(object.get(2).toString());
//                if (object.get(4)==null){
//                    personal.setMobile(null);
//                }else {
//                    personal.setMobile(object.get(4).toString());
//                }
//                personal.setCtyCode("bfsq_bfxq");
//                houseInfoService.addPersonal(personal);
//            }

            //System.out.println("缤纷小区"+"1"+"幢_"+ss[0]+"单元_"+ss[1]);
            //build.setName("缤纷小区"+ss[0]+"幢_"+ss[1]+"单元_"+ss[2]);


            //houseInfoService.addBuild(build);
            //houseInfoService.addUnit(unit);
            houseInfoService.addHouseInfo(houseInfo1);

        }
    }
}