intelligent-voice-customer-service-java/slibra-admin/src/main/java/com/slibra/web/controller/business/ExcelController.java

package com.slibra.web.controller.business;

import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson2.JSON;
import com.slibra.business.domain.*;
import com.slibra.business.mapper.*;
import com.slibra.business.req.AiChatReq;
import com.slibra.business.req.AiChatRes;
import com.slibra.business.res.BZXQLHInfo;
import com.slibra.business.res.MoreExcelInfo;
import com.slibra.business.res.UserExcelInfo;
import com.slibra.business.service.IFrontService;
import com.slibra.common.core.controller.BaseController;
import com.slibra.common.core.domain.AjaxResult;
import com.slibra.common.core.domain.entity.SysUser;
import com.slibra.common.exception.ServiceException;
import com.slibra.common.utils.StringUtils;
import com.slibra.common.utils.poi.ExcelUtil;
import com.slibra.web.controller.listener.MoreDataListener;
import com.slibra.web.controller.listener.UserDataListener;
import io.micrometer.core.annotation.Timed;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.checkerframework.checker.units.qual.C;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.nio.file.Files;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;


/**
 * 大模型的相关接口
 */
@RestController
@RequestMapping("/excel")
@Slf4j
public class ExcelController extends BaseController {

    @Autowired
    private TPumpingStationMapper tPumpingStationMapper;

    @Autowired
    private TNeighborhoodBuildingMapper tNeighborhoodBuildingMapper;

    @Autowired
    private TPumpingStationNeighbourhoodNumberMapper tPumpingStationNeighbourhoodNumberMapper;

    @Autowired
    private TNeighborhoodMapper tNeighborhoodMapper;

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Autowired
    private TUserInfoMapper tUserInfoMapper;



    public static void main(String[] args) {
//        System.out.println(getExcelFormBZXQLH());
//        moreExcel2DB();
    }

    //泵站、小区、楼号数据处理
    public static List<BZXQLHInfo>  getExcelFormBZXQLH() {
        File file = new File("/Users/wangmiaomiao/Documents/Excel/demo.xlsx");
        ExcelUtil<BZXQLHInfo> util = new ExcelUtil<BZXQLHInfo>(BZXQLHInfo.class);
        List<BZXQLHInfo> dataList;
        try {
            dataList = util.importExcel("小区泵站信息", Files.newInputStream(file.toPath()), 1);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return dataList;
    }

    /**
     * 需要操作：
     * 插入泵站、插入小区、插入楼号、
     * 插入小区和楼号关系表
     * 插入泵站和小区楼号关系表
     *
     * 操作逻辑：
     * 先按【区域】+【小区】分组，添加小区，然后获取楼号集合，添加楼号、楼号和小区的绑定关系。
     * 插入数据库。
     * 此次任务结束【可选】。
     *
     * 再次便利集合，一条一条匹配，然后再添加泵站【需要校验是否已经添加过了】，然后再绑定关系
     *
     * @return
     */
    @GetMapping("/moreExcel2DB")
    @Transactional
    public String moreExcel2DB(){
        //Excel转JSON
        List<BZXQLHInfo> excelFormBZXQLH = getExcelFormBZXQLH();
        int size = 0;
        if(!CollectionUtils.isEmpty(excelFormBZXQLH)){
            size = excelFormBZXQLH.size();
            //处理业务数据
            for (BZXQLHInfo bzxqlhInfo : excelFormBZXQLH) {
                //2024年12月30日17:27:29 将区+小区楼号拼接作为小区名称
                bzxqlhInfo.setNeighbourName(bzxqlhInfo.getArea() + bzxqlhInfo.getNeighbourName());
                //2024年12月31日10:30:16 将区+泵站名称拼接作为泵站名称
                bzxqlhInfo.setPumpingStationName(bzxqlhInfo.getArea() + bzxqlhInfo.getPumpingStationName());
                //2024年12月31日11:23:14  解决楼号不存在，无法插入数据库的问题
                if(StringUtils.isBlank(bzxqlhInfo.getBuildingName()))
                    bzxqlhInfo.setBuildingName("无");
            }
            Map<String, List<BZXQLHInfo>> map = excelFormBZXQLH.stream().collect(Collectors.groupingBy(BZXQLHInfo::getNeighbourName));
            map.forEach((k, v) -> {
//                System.out.println("小区名称:" + k + "，对应的楼号信息：" + JSON.toJSONString(v));
                //插入小区
                TNeighborhood tNeighborhood = TNeighborhood.builder().name(k).build();
                this.tNeighborhoodMapper.insertTNeighborhood(tNeighborhood);
                //插入楼号
                for (BZXQLHInfo bzxqlhInfo : v) {
                    TNeighborhoodBuilding tNeighborhoodBuilding = TNeighborhoodBuilding.builder().neighborhoodId(tNeighborhood.getId()).name(bzxqlhInfo.getBuildingName()).build();
                    this.tNeighborhoodBuildingMapper.insertTNeighborhoodBuilding(tNeighborhoodBuilding);
                }
            });
            //添加泵站（添加之前先去校验，通过名称查询泵站是否已经添加过了
            //         如果添加过了，就用该泵站ID，然后再通过小区和楼号查询对应的数据，绑定关系
            //         如果没有添加过，新增泵站，然后再通过小区和楼号查询对应的数据，绑定关系）
            for (BZXQLHInfo bzxqlhInfo : excelFormBZXQLH) {
                String pumpingStationName = bzxqlhInfo.getPumpingStationName();
                List<TPumpingStation> pumpingStations = this.tPumpingStationMapper.selectTPumpingStationList(TPumpingStation.builder().name(pumpingStationName).build());
                Long pumpingStationId = null;
                if(CollectionUtils.isEmpty(pumpingStations)){
                    log.info("A第一次添加泵站信息，泵站名称是{}", pumpingStationName);
                    //新增泵站，然后再通过小区和楼号查询对应的数据，绑定关系
                    TPumpingStation tPumpingStation = TPumpingStation.builder().name(pumpingStationName).build();
                    this.tPumpingStationMapper.insertTPumpingStation(tPumpingStation);
                    pumpingStationId = tPumpingStation.getId();
                }else{
                    log.info("Z已经添加过泵站信息，泵站名称是{}", pumpingStationName);
                    pumpingStationId = pumpingStations.get(0).getId();
                }
                //有了泵站信息，处理其他逻辑
                //再通过小区和楼号查询对应的数据，绑定关系
                //2024年12月31日18:28:22 这里不能直接通过楼号去查询，因为楼号很多都是重复的 通过小区查询、，因为小区名字是唯一的。
                List<TNeighborhood> tNeighborhoods = this.tNeighborhoodMapper.selectTNeighborhoodList(TNeighborhood.builder().name(bzxqlhInfo.getNeighbourName()).build());
                if(!CollectionUtils.isEmpty(tNeighborhoods)){
                    TNeighborhood tNeighborhood = tNeighborhoods.get(0);
                    //通过小区ID和楼号名称【2者组合是唯一的】
                    List<TNeighborhoodBuilding> tNeighborhoodBuildings = this.tNeighborhoodBuildingMapper.selectTNeighborhoodBuildingList(TNeighborhoodBuilding.builder().neighborhoodId(tNeighborhood.getId()).name(bzxqlhInfo.getBuildingName()).build());
                    if(!CollectionUtils.isEmpty(tNeighborhoodBuildings)){
                        TNeighborhoodBuilding tNeighborhoodBuilding = tNeighborhoodBuildings.get(0);
                        //泵关联小区楼号对象
                        TPumpingStationNeighbourhoodNumber tPumpingStationNeighbourhoodNumber = TPumpingStationNeighbourhoodNumber.builder().pumpingStationId(pumpingStationId).neighborhoodId(tNeighborhoodBuilding.getNeighborhoodId()).neighborhoodBuildingId(tNeighborhoodBuilding.getId()).build();
                        this.tPumpingStationNeighbourhoodNumberMapper.insertTPumpingStationNeighbourhoodNumber(tPumpingStationNeighbourhoodNumber);
                    }
                }
            }
        }

        return "操作成功，新增泵站关联小区楼号" + size + "条";
    }


    /**
     *
     * 将Excel中的用户数据 导入到数据库中
     * @return
     */
//    @GetMapping("/userExcel2DB")
    @Transactional
    public String userExcel2DB(){
        long begin = System.currentTimeMillis();
        File file = new File("/Users/wangmiaomiao/Documents/Excel/jmsuser.xlsx");
        if(!file.exists())
            throw new ServiceException("导入的Excel文件不存在！");
        ExcelUtil<UserExcelInfo> util = new ExcelUtil<UserExcelInfo>(UserExcelInfo.class);
        List<UserExcelInfo> dataList;
        try {
            dataList = util.importExcel("凑合能用的数据", Files.newInputStream(file.toPath()), 0);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        System.out.println("要处理的数据条数为：" + dataList.size());
        if (!CollectionUtils.isEmpty(dataList)) {
            /*SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
            TUserInfoMapper sqlSessionMapper = sqlSession.getMapper(TUserInfoMapper.class);*/
            //处理数据
            for (UserExcelInfo userExcelInfo : dataList) {
                TUserInfo tUserInfo = new TUserInfo();
//                BeanUtils.copyProperties(userExcelInfo, tUserInfo);
                //因为数据存在空格等 需要额外处理一下
                tUserInfo.setUserNo(userExcelInfo.getUserNo().trim());
                tUserInfo.setAmmeterNo(userExcelInfo.getAmmeterNo().trim());
                tUserInfo.setPumpingStationAddress(userExcelInfo.getPumpingStationAddress().trim());
                tUserInfo.setRemark(userExcelInfo.getRemark().trim());
                tUserInfo.setStreet(userExcelInfo.getStreet().trim());
                tUserInfo.setNeighbourhoodName(userExcelInfo.getNeighbourhoodName().trim());
                tUserInfo.setBuildingNo(userExcelInfo.getBuildingNo().trim());
                tUserInfo.setDoorNo(userExcelInfo.getDoorNo().trim());
                tUserInfo.setMeterReader(userExcelInfo.getMeterReader().trim());
                tUserInfo.setMeterReaderPhone(userExcelInfo.getMeterReaderPhone().trim());
                tUserInfoMapper.insertTUserInfo(tUserInfo);
                //2025年01月09日11:20:35 数据量太大，批量插入有问题
//                sqlSessionMapper.insertTUserInfo(tUserInfo);
            }
            /*sqlSession.commit();
            sqlSession.close();*/
        }
        return "操作成功，新增用户数据" + dataList.size() + "条;耗时：" + (System.currentTimeMillis() - begin)/1000/60 + "分钟";
    }







    /**
     *
     * 将Excel中的用户数据 导入到数据库中--减少内存使用
     * @return
     */
    @GetMapping("/userExcel2DBFetch")
    @Transactional
    public String userExcel2DBFetch(){
        String fileName = "/Users/wangmiaomiao/Documents/Excel/jmsuser.xlsx"; // Excel文件路径
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
            TUserInfoMapper sqlSessionMapper = sqlSession.getMapper(TUserInfoMapper.class);
        // 读取Excel文件
        EasyExcel.read(fileName, UserExcelInfo.class, new UserDataListener(sqlSession, sqlSessionMapper))
                .sheet("全部可用个人信息") // 读取第一个工作表
                .doRead(); // 触发实际的读取操作
        return "操作成功";
    }




    /**
     *
     * 将Excel中的[泵站 小区 楼号] 导入到数据库中--减少内存使用
     * @return
     */
    @GetMapping("/moreExcel2DBFetch")
    @Transactional
    public String moreExcel2DBFetch(){
        String fileName = "/Users/wangmiaomiao/Documents/Excel/jmsuser.xlsx"; // Excel文件路径
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        TPumpingStationMapper pumpingStationMapper = sqlSession.getMapper(TPumpingStationMapper.class);
        TNeighborhoodBuildingMapper neighborhoodBuildingMapper = sqlSession.getMapper(TNeighborhoodBuildingMapper.class);
        TPumpingStationNeighbourhoodNumberMapper pumpingStationNeighbourhoodNumberMapper = sqlSession.getMapper(TPumpingStationNeighbourhoodNumberMapper.class);
        TNeighborhoodMapper neighborhoodMapper = sqlSession.getMapper(TNeighborhoodMapper.class);
        // 读取Excel文件
        EasyExcel.read(fileName, MoreExcelInfo.class, new MoreDataListener(sqlSession, pumpingStationMapper, neighborhoodBuildingMapper, pumpingStationNeighbourhoodNumberMapper, neighborhoodMapper))
                .sheet("泵站") // 读取对应的sheet
                .doRead(); // 触发实际的读取操作
        return "操作成功";
    }









}