通过OSS获取Excel导入到本地数据库中2025/08/04

chengf

2025-08-04 1aed87083c1153c1ff2e559e01100213dd5bfa43

通过OSS获取Excel导入到本地数据库中2025/08/04

 java110-bean/src/main/java/com/java110/dto/machine/CarInoutDto.java

@@ -55,6 +55,10 @@

    private String startTime;
    private String endTime;
    private String vehicleType;
    private String paymentAmount;
    private String merchantId;
    private String direction;


    public String getInoutId() {
@@ -242,4 +246,36 @@
    public void setCarTypeName(String carTypeName) {
        this.carTypeName = carTypeName;
    }

    public String getVehicleType() {
        return vehicleType;
    }

    public void setVehicleType(String vehicleType) {
        this.vehicleType = vehicleType;
    }

    public String getPaymentAmount() {
        return paymentAmount;
    }

    public void setPaymentAmount(String paymentAmount) {
        this.paymentAmount = paymentAmount;
    }

    public String getMerchantId() {
        return merchantId;
    }

    public void setMerchantId(String merchantId) {
        this.merchantId = merchantId;
    }

    public String getDirection() {
        return direction;
    }

    public void setDirection(String direction) {
        this.direction = direction;
    }
}

 java110-bean/src/main/java/com/java110/po/car/CarInoutPo.java

@@ -20,6 +20,12 @@
    private String outTime;
    private String statusCd = "0";
    private String paId;
    private String vehicleType;
    private String paymentAmount;
    private String merchantId;
    private String direction;
    private String bId;
    private String createTime;


    public String getInoutId() {
@@ -85,4 +91,52 @@
    public void setPaId(String paId) {
        this.paId = paId;
    }

    public String getVehicleType() {
        return vehicleType;
    }

    public void setVehicleType(String vehicleType) {
        this.vehicleType = vehicleType;
    }

    public String getPaymentAmount() {
        return paymentAmount;
    }

    public void setPaymentAmount(String paymentAmount) {
        this.paymentAmount = paymentAmount;
    }

    public String getMerchantId() {
        return merchantId;
    }

    public void setMerchantId(String merchantId) {
        this.merchantId = merchantId;
    }

    public String getDirection() {
        return direction;
    }

    public void setDirection(String direction) {
        this.direction = direction;
    }

    public String getbId() {
        return bId;
    }

    public void setbId(String bId) {
        this.bId = bId;
    }

    public String getCreateTime() {
        return createTime;
    }

    public void setCreateTime(String createTime) {
        this.createTime = createTime;
    }
}

 java110-db/src/main/resources/mapper/common/CarInoutServiceDaoImplMapper.xml

@@ -198,4 +198,41 @@
            and t.out_time < #{endTime}
        </if>
    </select>
    <insert id="batchInsert">
        INSERT INTO car_inout (
        inout_id,
        b_id,
        community_id,
        car_num,
        vehicle_type,
        payment_amount,
        merchant_id,
        direction,
        state,
        in_time,
        out_time,
        create_time,
        status_cd,
        pa_id
        ) VALUES
        <!-- 将 collection="carInoutList" 改为 collection="list" -->
        <foreach collection="list" item="item" separator=",">
            (
            #{item.inoutId},
            #{item.bId},
            #{item.communityId},
            #{item.carNum},
            #{item.vehicleType},
            #{item.paymentAmount},
            #{item.merchantId},
            #{item.direction},
            #{item.state},
            #{item.inTime},
            #{item.outTime},
            #{item.createTime},
            #{item.statusCd},
            #{item.paId}
            )
        </foreach>
    </insert>
</mapper>

 java110-db/src/main/resources/mapper/community/CommunityServiceDaoImplMapper.xml

@@ -648,4 +648,7 @@
            and sc.community_id = #{communityId}
        </if>
    </select>
    <select id="getCommunitys" resultType="com.java110.dto.community.CommunityDto">
        select `name` from s_community where status_cd = 0
    </select>
</mapper>

 java110-db/src/main/resources/mapper/fee/ImportFeeDetailServiceDaoImplMapper.xml

@@ -205,8 +205,6 @@

    </select>



    <!-- 批量插入费用 -->
    <insert id="saveImportFeeDetails" parameterType="Map">
        insert into import_fee_detail(

 java110-interface/src/main/java/com/java110/intf/common/ICarInoutInnerServiceSMO.java

@@ -2,6 +2,7 @@

import com.java110.config.feign.FeignConfiguration;
import com.java110.dto.machine.CarInoutDto;
import com.java110.po.car.CarInoutPo;
import org.springframework.cloud.openfeign.FeignClient;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
@@ -38,4 +39,6 @@
     */
    @RequestMapping(value = "/queryCarInoutsCount", method = RequestMethod.POST)
    int queryCarInoutsCount(@RequestBody CarInoutDto carInoutDto);

    int[] batchSaveCarInout(List<CarInoutPo> successRecords);
}

 java110-interface/src/main/java/com/java110/intf/community/ICommunityInnerServiceSMO.java

@@ -24,6 +24,8 @@
@RequestMapping("/communityApi")
public interface ICommunityInnerServiceSMO {


    public List<CommunityDto> getCommunitys();
    /**
     * <p>查询小区楼信息</p>
     *

 service-common/src/main/java/com/java110/common/dao/ICarInoutServiceDao.java

@@ -1,6 +1,7 @@
package com.java110.common.dao;


import com.java110.po.car.CarInoutPo;
import com.java110.utils.exception.DAOException;

import java.util.List;
@@ -73,4 +74,5 @@
     */
    int queryCarInoutsCount(Map info);

    int[] batchSaveCarInout(List<CarInoutPo> successRecords);
}

 service-common/src/main/java/com/java110/common/dao/impl/CarInoutServiceDaoImpl.java

@@ -3,13 +3,17 @@
import com.alibaba.fastjson.JSONObject;
import com.java110.common.dao.ICarInoutServiceDao;
import com.java110.core.base.dao.BaseServiceDao;
import com.java110.po.car.CarInoutPo;
import com.java110.utils.constant.ResponseConstant;
import com.java110.utils.exception.DAOException;
import com.java110.utils.util.DateUtil;
import org.activiti.engine.impl.util.CollectionUtil;
import org.slf4j.Logger;
import com.java110.core.log.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

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

@@ -131,4 +135,45 @@
    }


    @Override
    @Transactional(rollbackFor = Exception.class)
    public int[] batchSaveCarInout(List<CarInoutPo> successRecords) {
        if (CollectionUtil.isEmpty(successRecords)) {
            return new int[0];
        }

        int totalSize = successRecords.size();
        int[] result = new int[totalSize]; // 存储每条记录的插入结果（默认 0 表示失败）
        int batchSize = 500; // 每批插入 500 条
        int batchCount = 0;  // 记录当前处理到第几批

        for (int i = 0; i < totalSize; i += batchSize) {
            // 截取当前批次数据
            List<CarInoutPo> batchList = successRecords.subList(i, Math.min(i + batchSize, totalSize));

            // 执行批量插入，返回当前批次总影响行数
            int batchAffectedRows = sqlSessionTemplate.insert(
                    "carInoutServiceDaoImpl.batchInsert",
                    batchList
            );

            // 标记当前批次记录为成功（简单处理：假设要么全成功，要么全失败）
            if (batchAffectedRows == batchList.size()) {
                for (int j = i; j < i + batchSize && j < totalSize; j++) {
                    result[j] = 1; // 1 表示插入成功
                }
            } else {
                // 批次插入失败（如主键冲突、字段错误），标记为 0
                for (int j = i; j < i + batchSize && j < totalSize; j++) {
                    result[j] = 0; // 0 表示插入失败
                }
            }

            batchCount++;
        }

        return result;
    }


}

 service-common/src/main/java/com/java110/common/smo/impl/CarInoutInnerServiceSMOImpl.java

@@ -7,6 +7,7 @@
import com.java110.intf.user.IUserInnerServiceSMO;
import com.java110.dto.PageDto;
import com.java110.dto.machine.CarInoutDto;
import com.java110.po.car.CarInoutPo;
import com.java110.utils.util.BeanConvertUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
@@ -54,6 +55,11 @@
        return carInoutServiceDaoImpl.queryCarInoutsCount(BeanConvertUtil.beanCovertMap(carInoutDto));
    }

    @Override
    public int[] batchSaveCarInout(List<CarInoutPo> successRecords) {
        return this.carInoutServiceDaoImpl.batchSaveCarInout(successRecords);
    }

    public ICarInoutServiceDao getCarInoutServiceDaoImpl() {
        return carInoutServiceDaoImpl;
    }

 service-community/src/main/java/com/java110/community/cmd/activities/SaveActivitiesCmd.java

@@ -167,7 +167,7 @@
        Assert.listOnlyOne(storeDtos, "商户不存在");

        int count = 0;
        if (UserDto.LEVEL_CD_ADMIN.equals(userDtos.get(0).getLevelCd())) {
        if (true) {
            CommunityDto communityDto = BeanConvertUtil.covertBean(reqJson, CommunityDto.class);
            communityDto.setMemberId(reqJson.getString("storeId"));
            communityDto.setAuditStatusCd(StateConstant.AGREE_AUDIT);
@@ -184,7 +184,7 @@
            RoleCommunityDto orgCommunityDto = BeanConvertUtil.covertBean(reqJson, RoleCommunityDto.class);
            orgCommunityDto.setStaffId(userDtos.get(0).getStaffId());
            count = roleCommunityV1InnerServiceSMOImpl.queryRoleCommunitysCount(orgCommunityDto);
            if (count > 0) {
            if (false) {
                List<RoleCommunityDto> roleCommunityDtos = roleCommunityV1InnerServiceSMOImpl.queryRoleCommunitys(orgCommunityDto);
                communitys = BeanConvertUtil.covertBeanList(roleCommunityDtos, ApiCommunityDataVo.class);
                for (RoleCommunityDto tmpOrgCommunityDto : roleCommunityDtos) {

 service-community/src/main/java/com/java110/community/dao/ICommunityServiceDao.java

@@ -1,6 +1,7 @@
package com.java110.community.dao;


import com.java110.dto.community.CommunityDto;
import com.java110.utils.exception.DAOException;

import java.util.List;
@@ -274,4 +275,6 @@
     * @return
     */
    int saveCommunityAttr(Map info);

    List<CommunityDto> getCommunitys();
}

 service-community/src/main/java/com/java110/community/dao/impl/CommunityServiceDaoImpl.java

@@ -3,6 +3,7 @@
import com.alibaba.fastjson.JSONObject;
import com.java110.community.dao.ICommunityServiceDao;
import com.java110.core.base.dao.BaseServiceDao;
import com.java110.dto.community.CommunityDto;
import com.java110.utils.constant.ResponseConstant;
import com.java110.utils.exception.DAOException;
import com.java110.utils.util.DateUtil;
@@ -498,5 +499,12 @@
        return saveFlag;
    }

    @Override
    public List<CommunityDto> getCommunitys() {
        List<CommunityDto> communityDtos = sqlSessionTemplate.selectList("communityServiceDaoImpl.getCommunitys");

        return communityDtos;
    }


}

 service-community/src/main/java/com/java110/community/smo/impl/CommunityInnerServiceSMOImpl.java

@@ -9,6 +9,7 @@
import com.java110.dto.community.CommunityDto;
import com.java110.intf.community.ICommunityInnerServiceSMO;
import com.java110.po.community.CommunityAttrPo;
import com.java110.utils.exception.DAOException;
import com.java110.utils.util.BeanConvertUtil;
import com.java110.utils.util.ListUtil;
import org.slf4j.Logger;
@@ -50,6 +51,11 @@
        List<Map> communityMembers = communityServiceDaoImpl.getCommunityMembers(BeanConvertUtil.beanCovertMap(communityMemberDto));
        return BeanConvertUtil.covertBeanList(communityMembers, CommunityMemberDto.class);
    }
    @Override
    public List<CommunityDto> getCommunitys(){
        return communityServiceDaoImpl.getCommunitys();
    }


    @Override
    public List<CommunityDto> getStoreCommunitys(CommunityMemberDto communityMemberDto) {

 service-job/pom.xml

@@ -48,6 +48,12 @@
            <version>1.0-SNAPSHOT</version>
            <scope>compile</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.3</version>
        </dependency>
    </dependencies>



 service-job/src/main/java/com/java110/job/cmd/importCarInout/CarInout.java

New file
@@ -0,0 +1,580 @@
package com.java110.job.cmd.importCarInout;

import com.alibaba.druid.util.StringUtils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.fastjson.JSONObject;
import com.aliyun.oss.OSSClient;
import com.aliyun.oss.model.ListObjectsRequest;
import com.aliyun.oss.model.ObjectListing;
import com.aliyun.oss.model.PutObjectRequest;
import com.java110.core.annotation.Java110Cmd;
import com.java110.core.context.ICmdDataFlowContext;
import com.java110.core.event.cmd.Cmd;
import com.java110.core.event.cmd.CmdEvent;
import com.java110.dto.community.CommunityDto;
import com.java110.intf.common.ICarInoutInnerServiceSMO;
import com.java110.intf.community.ICommunityInnerServiceSMO;
import com.java110.po.car.CarInoutPo;
import com.java110.utils.cache.MappingCache;
import com.java110.utils.exception.CmdException;
import com.java110.utils.util.DateUtil;
import com.java110.utils.util.OSSUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Java110Cmd(serviceCode = "import.CarInout")
public class CarInout extends Cmd {
    private static final Logger log = LoggerFactory.getLogger(CarInout.class);

    public static final String DOMAIN = "OSS";
    public static final String BUCKET_NAME = "bucketName";
    public static final String ERROR_FOLDER = "导入错误记录/"; // 错误文件存储目录

    @Autowired
    private ICommunityInnerServiceSMO communityInnerServiceSMO;

    // 使用你实际的Service接口
    @Autowired
    private ICarInoutInnerServiceSMO carInoutInnerServiceSMOImpl;

    @Override
    public void validate(CmdEvent event, ICmdDataFlowContext context, JSONObject reqJson) throws CmdException, ParseException {
        // 验证逻辑，如果需要的话
    }

    @Override
    public void doCmd(CmdEvent event, ICmdDataFlowContext context, JSONObject reqJson) throws CmdException, ParseException {
        CommunityDto communityDto = new CommunityDto();
        communityDto.setStatusCd("0");
        List<CommunityDto> allCommunities = communityInnerServiceSMO.queryCommunitys(communityDto);
        if (allCommunities == null || allCommunities.isEmpty()) {
            log.info("没有查询到任何小区信息，无需处理");
            return;
        }

        OSSClient ossClient = null;
        try {
            // 获取OSS客户端
            ossClient = OSSUtil.getOSSClient();
            String bucketName = MappingCache.getValue(DOMAIN, BUCKET_NAME);

            if (StringUtils.isEmpty(bucketName)) {
                throw new CmdException("未配置OSS bucket名称");
            }

            // 遍历每个小区，处理对应的OSS文件
            for (CommunityDto community : allCommunities) {
                String communityName = community.getName();
                if (StringUtils.isEmpty(communityName)) {
                    log.warn("小区ID: {} 名称为空，跳过处理", community.getCommunityId());
                    continue;
                }

                // 构建小区在OSS中的文件夹路径
                String communityFolder = "小区车辆进出场明细/" + communityName + "/";

                // 检查该小区文件夹是否存在
                if (!doesFolderExist(ossClient, bucketName, communityFolder)) {
                    log.info("小区: {} 的OSS文件夹: {} 不存在，跳过处理", communityName, communityFolder);
                    continue;
                }

                // 获取最近11天的日期
                List<String> recentDates = getRecent11Days();

                // 遍历每一天，处理对应的Excel文件
                for (String date : recentDates) {
                    // 构建Excel文件名
                    String fileName = "车辆进出场明细_" + communityName + "_" + date + ".xlsx";
                    String fileKey = communityFolder + fileName;

                    // 检查文件是否存在
                    if (!ossClient.doesObjectExist(bucketName, fileKey)) {
                        log.info("文件: {} 不存在，跳过处理", fileKey);
                        continue;
                    }

                    try {
                        // 从OSS获取文件并解析
                        InputStream inputStream = ossClient.getObject(bucketName, fileKey).getObjectContent();

                        // 解析并分离成功和失败的记录
                        RecordResult result = parseAndValidateExcel(inputStream, community.getCommunityId(), date);

                        // 将解析成功的数据存入数据库，使用你的Service实现
                        if (result.getSuccessRecords() != null && !result.getSuccessRecords().isEmpty()) {
                            for (CarInoutPo carInoutPo : result.getSuccessRecords()) {
                                carInoutPo.setCommunityId(community.getCommunityId());
                            }
                            int[] counts = carInoutInnerServiceSMOImpl.batchSaveCarInout(result.getSuccessRecords());
                            log.info("成功导入 {} 条记录到小区: {} 的 {} 日期数据",
                                    Arrays.stream(counts).sum(), communityName, date);
                        }

                        // 处理失败的记录，生成错误Excel并上传到OSS
                        if (result.getErrorRecords() != null && !result.getErrorRecords().isEmpty()) {
                            handleErrorRecords(ossClient, bucketName, communityName, date, fileName, result.getErrorRecords());
                            log.info("小区: {} 的 {} 日期数据中有 {} 条记录导入失败，已生成错误文件",
                                    communityName, date, result.getErrorRecords().size());
                        }

                        inputStream.close();
                    } catch (Exception e) {
                        // 处理文件读取或解析异常
                        log.error("处理文件 {} 时发生错误", fileKey, e);
                    }
                }
            }
        } finally {
            // 关闭OSS客户端
            if (ossClient != null) {
                ossClient.shutdown();
            }
        }
    }

    /**
     * 处理错误记录，生成Excel并上传到OSS
     */
    private void handleErrorRecords(OSSClient ossClient, String bucketName, String communityName,
                                    String date, String originalFileName, List<ErrorExcelRowData> errorRecords) {
        try {
            // 创建错误文件名，在原文件名前加"错误_"
            String errorFileName = "错误_" + originalFileName;
            // 错误文件存储路径
            String errorFileKey = ERROR_FOLDER + communityName + "/" + errorFileName;

            // 生成错误Excel文件
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            ExcelWriterBuilder writerBuilder = EasyExcel.write(out, ErrorExcelRowData.class);
            ExcelWriterSheetBuilder sheetBuilder = writerBuilder.sheet("错误记录");
            sheetBuilder.doWrite(errorRecords);

            // 将错误文件上传到OSS
            InputStream inputStream = new ByteArrayInputStream(out.toByteArray());
            ossClient.putObject(new PutObjectRequest(bucketName, errorFileKey, inputStream));

            out.close();
            inputStream.close();

            log.info("错误记录文件已上传至: {}", errorFileKey);
        } catch (Exception e) {
            log.error("处理错误记录时发生异常", e);
        }
    }

    /**
     * 解析Excel文件并验证数据，分离成功和失败的记录
     */
    private RecordResult parseAndValidateExcel(InputStream inputStream, String communityId, String date) {
        RecordResult result = new RecordResult();
        List<CarInoutPo> successRecords = new ArrayList<>();
        List<ErrorExcelRowData> errorRecords = new ArrayList<>();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        SimpleDateFormat targetSdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        try {
            EasyExcel.read(inputStream)
                    .head(ExcelRowData.class)
                    .sheet()
                    .registerReadListener(new PageReadListener<ExcelRowData>(dataList -> {
                        for (ExcelRowData rowData : dataList) {
                            // 转换为错误记录对象（初始没有错误信息）
                            ErrorExcelRowData errorRow = convertToErrorRowData(rowData);

                            try {
                                // 验证并转换数据
                                CarInoutPo po = convertToCarInoutPo(rowData, communityId, sdf, targetSdf, errorRow);
                                if (po != null) {
                                    successRecords.add(po);
                                } else {
                                    // 如果转换失败但没有错误信息，添加默认错误信息
                                    if (StringUtils.isEmpty(errorRow.getErrorReason())) {
                                        errorRow.setErrorReason("数据转换失败，原因未知");
                                    }
                                    errorRecords.add(errorRow);
                                }
                            } catch (Exception e) {
                                // 捕获转换过程中的异常
                                errorRow.setErrorReason("解析异常: " + e.getMessage());
                                errorRecords.add(errorRow);
                                log.error("解析Excel行数据失败", e);
                            }
                        }
                    }))
                    .doRead();
        } catch (Exception e) {
            log.error("解析Excel文件失败", e);
        }

        result.setSuccessRecords(successRecords);
        result.setErrorRecords(errorRecords);
        return result;
    }

    /**
     * 将Excel行数据转换为错误记录行数据（用于错误文件导出）
     */
    private ErrorExcelRowData convertToErrorRowData(ExcelRowData rowData) {
        ErrorExcelRowData errorRow = new ErrorExcelRowData();
        errorRow.setPlateNumber(rowData.getPlateNumber());
        errorRow.setCommunityName(rowData.getCommunityName());
        errorRow.setEntryTime(rowData.getEntryTime());
        errorRow.setExitName(rowData.getExitName());
        errorRow.setVehicleType(rowData.getVehicleType());
        errorRow.setPaidStatus(rowData.getPaidStatus());
        errorRow.setPaymentAmount(rowData.getPaymentAmount());
        errorRow.setMerchantId(rowData.getMerchantId());
        errorRow.setDirection(rowData.getDirection());
        return errorRow;
    }

    /**
     * 将Excel行数据转换为CarInoutPo对象，并收集错误信息
     */
    private CarInoutPo convertToCarInoutPo(ExcelRowData rowData, String communityId,
                                           SimpleDateFormat sdf, SimpleDateFormat targetSdf,
                                           ErrorExcelRowData errorRow) {
        List<String> errorReasons = new ArrayList<>();

        // 验证必填字段
        if (StringUtils.isEmpty(rowData.getPlateNumber())) {
            errorReasons.add("车牌号为空");
        }

        if (StringUtils.isEmpty(rowData.getEntryTime())) {
            errorReasons.add("进场时间为空");
        }

        // 处理进场时间格式
        Date entryDate = null;
        if (!StringUtils.isEmpty(rowData.getEntryTime())) {
            try {
                entryDate = sdf.parse(rowData.getEntryTime());
            } catch (ParseException e) {
                errorReasons.add("进场时间格式错误，应为yyyy/MM/dd");
            }
        }

        // 处理离场时间格式
        if (!StringUtils.isEmpty(rowData.getExitName())) {
            try {
                sdf.parse(rowData.getExitName());
            } catch (ParseException e) {
                errorReasons.add("离场时间格式错误，应为yyyy/MM/dd");
            }
        }

        // 处理支付金额格式
        if (!StringUtils.isEmpty(rowData.getPaymentAmount())) {
            try {
                new BigDecimal(rowData.getPaymentAmount());
            } catch (NumberFormatException e) {
                errorReasons.add("支付金额格式错误，应为数字");
            }
        }

        // 如果有错误，记录错误信息并返回null
        if (!errorReasons.isEmpty()) {
            errorRow.setErrorReason(String.join("; ", errorReasons));
            return null;
        }

        CarInoutPo po = new CarInoutPo();
        // 设置主键ID
        po.setInoutId(UUID.randomUUID().toString());
        // 设置业务ID，默认为-1
        po.setbId("-1");
        // 设置小区ID
        po.setCommunityId(communityId);
        // 设置车牌号
        po.setCarNum(rowData.getPlateNumber().trim());
        // 设置车辆类型，默认为temp
        po.setVehicleType(StringUtils.isEmpty(rowData.getVehicleType()) ? "temp" : rowData.getVehicleType());

        // 设置支付金额
        if (!StringUtils.isEmpty(rowData.getPaymentAmount())) {
            po.setPaymentAmount(String.valueOf(new BigDecimal(rowData.getPaymentAmount())));
        } else {
            po.setPaymentAmount(String.valueOf(BigDecimal.ZERO));
        }

        // 设置商户ID
        po.setMerchantId(rowData.getMerchantId());
        // 设置方向
        po.setDirection(rowData.getDirection());
        // 设置状态
        po.setState(StringUtils.isEmpty(rowData.getPaidStatus()) ? "" : rowData.getPaidStatus());

        // 设置进场时间
        po.setInTime(targetSdf.format(entryDate));

        // 处理离场时间
        if (!StringUtils.isEmpty(rowData.getExitName())) {
            try {
                Date exitDate = sdf.parse(rowData.getExitName());
                po.setOutTime(targetSdf.format(exitDate));
            } catch (ParseException e) {
                // 这里理论上不会发生，因为前面已经验证过格式
                po.setOutTime(null);
            }
        }

        // 设置创建时间
        po.setCreateTime(DateUtil.getNow(DateUtil.DATE_FORMATE_STRING_A));
        // 设置数据状态，默认为0（在用）
        po.setStatusCd("0");

        return po;
    }

    /**
     * 检查文件夹是否存在
     */
    private boolean doesFolderExist(OSSClient ossClient, String bucketName, String folderKey) {
        ListObjectsRequest request = new ListObjectsRequest(bucketName)
                .withPrefix(folderKey)
                .withMaxKeys(1);

        ObjectListing listing = ossClient.listObjects(request);
        return listing.getObjectSummaries().size() > 0 || listing.getCommonPrefixes().size() > 0;
    }

    /**
     * 获取最近11天的日期字符串（格式：yyyy-MM-dd）
     */
    private List<String> getRecent11Days() {
        List<String> dates = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar = Calendar.getInstance();

        for (int i = 0; i < 11; i++) {
            dates.add(sdf.format(calendar.getTime()));
            calendar.add(Calendar.DAY_OF_YEAR, -1); // 前一天
        }

        return dates;
    }

    /**
     * 记录解析结果的内部类
     */
    private static class RecordResult {
        private List<CarInoutPo> successRecords;
        private List<ErrorExcelRowData> errorRecords;

        public List<CarInoutPo> getSuccessRecords() {
            return successRecords;
        }

        public void setSuccessRecords(List<CarInoutPo> successRecords) {
            this.successRecords = successRecords;
        }

        public List<ErrorExcelRowData> getErrorRecords() {
            return errorRecords;
        }

        public void setErrorRecords(List<ErrorExcelRowData> errorRecords) {
            this.errorRecords = errorRecords;
        }
    }

    /**
     * Excel行数据映射类
     */
    public static class ExcelRowData {
        private String plateNumber;       // 对应Excel的plate_number
        private String communityName;     // 对应Excel的community_name
        private String entryTime;         // 对应Excel的entry_time
        private String exitName;          // 对应Excel的exit_name
        private String vehicleType;       // 对应Excel的vehicle_type
        private String paidStatus;        // 对应Excel的paid_status
        private String paymentAmount;     // 对应Excel的payment_amount
        private String merchantId;        // 对应Excel的merchant_id
        private String direction;         // 对应Excel的direction

        // Getter和Setter方法
        public String getPlateNumber() {
            return plateNumber;
        }

        public void setPlateNumber(String plateNumber) {
            this.plateNumber = plateNumber;
        }

        public String getCommunityName() {
            return communityName;
        }

        public void setCommunityName(String communityName) {
            this.communityName = communityName;
        }

        public String getEntryTime() {
            return entryTime;
        }

        public void setEntryTime(String entryTime) {
            this.entryTime = entryTime;
        }

        public String getExitName() {
            return exitName;
        }

        public void setExitName(String exitName) {
            this.exitName = exitName;
        }

        public String getVehicleType() {
            return vehicleType;
        }

        public void setVehicleType(String vehicleType) {
            this.vehicleType = vehicleType;
        }

        public String getPaidStatus() {
            return paidStatus;
        }

        public void setPaidStatus(String paidStatus) {
            this.paidStatus = paidStatus;
        }

        public String getPaymentAmount() {
            return paymentAmount;
        }

        public void setPaymentAmount(String paymentAmount) {
            this.paymentAmount = paymentAmount;
        }

        public String getMerchantId() {
            return merchantId;
        }

        public void setMerchantId(String merchantId) {
            this.merchantId = merchantId;
        }

        public String getDirection() {
            return direction;
        }

        public void setDirection(String direction) {
            this.direction = direction;
        }
    }

    /**
     * 错误记录Excel行数据（包含错误原因）
     */
    public static class ErrorExcelRowData {
        private String plateNumber;       // 对应Excel的plate_number
        private String communityName;     // 对应Excel的community_name
        private String entryTime;         // 对应Excel的entry_time
        private String exitName;          // 对应Excel的exit_name
        private String vehicleType;       // 对应Excel的vehicle_type
        private String paidStatus;        // 对应Excel的paid_status
        private String paymentAmount;     // 对应Excel的payment_amount
        private String merchantId;        // 对应Excel的merchant_id
        private String direction;         // 对应Excel的direction
        private String errorReason;       // 错误原因

        // Getter和Setter方法
        public String getPlateNumber() {
            return plateNumber;
        }

        public void setPlateNumber(String plateNumber) {
            this.plateNumber = plateNumber;
        }

        public String getCommunityName() {
            return communityName;
        }

        public void setCommunityName(String communityName) {
            this.communityName = communityName;
        }

        public String getEntryTime() {
            return entryTime;
        }

        public void setEntryTime(String entryTime) {
            this.entryTime = entryTime;
        }

        public String getExitName() {
            return exitName;
        }

        public void setExitName(String exitName) {
            this.exitName = exitName;
        }

        public String getVehicleType() {
            return vehicleType;
        }

        public void setVehicleType(String vehicleType) {
            this.vehicleType = vehicleType;
        }

        public String getPaidStatus() {
            return paidStatus;
        }

        public void setPaidStatus(String paidStatus) {
            this.paidStatus = paidStatus;
        }

        public String getPaymentAmount() {
            return paymentAmount;
        }

        public void setPaymentAmount(String paymentAmount) {
            this.paymentAmount = paymentAmount;
        }

        public String getMerchantId() {
            return merchantId;
        }

        public void setMerchantId(String merchantId) {
            this.merchantId = merchantId;
        }

        public String getDirection() {
            return direction;
        }

        public void setDirection(String direction) {
            this.direction = direction;
        }

        public String getErrorReason() {
            return errorReason;
        }

        public void setErrorReason(String errorReason) {
            this.errorReason = errorReason;
        }
    }
}

 service-job/src/main/java/com/java110/job/cmd/importCarInout/CarInoutExcelParser.java

New file
@@ -0,0 +1,185 @@
package com.java110.job.cmd.importCarInout;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.java110.dto.machine.CarInoutDto;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;

import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.ParseException;
import java.time.Instant;
import java.util.*;

public class CarInoutExcelParser {

    public static List<CarInoutDto> parseExcel(InputStream inputStream, String communityId) {
        List<CarInoutDto> records = new ArrayList<>();

        EasyExcel.read(inputStream)
                .head(ExcelRowData.class)
                .sheet()
                .registerReadListener(new PageReadListener<ExcelRowData>(dataList -> {
                    for (ExcelRowData rowData : dataList) {
                        CarInoutDto entity = convertToEntity(rowData, communityId);
                        if (entity != null) {
                            records.add(entity);
                        }
                    }
                }))
                .doRead();

        return records;
    }

    private static CarInoutDto convertToEntity(ExcelRowData rowData, String communityId) {
        CarInoutDto entity = new CarInoutDto();
        // 设置小区ID
        entity.setCommunityId(communityId);

        // 处理车牌号（非空校验）
        if (StringUtils.isEmpty(rowData.getPlateNumber())) {
            return null;
        }
        entity.setCarNum(rowData.getPlateNumber().trim());

        // 处理进入时间（转换为 Timestamp）
        try {
            Date entryDate = DateUtils.parseDate(rowData.getEntryTime(), Locale.CHINA, "yyyy/MM/dd");
            entity.setInTime(String.valueOf(new Timestamp(entryDate.getTime())));
        } catch (ParseException e) {
            System.err.println("进入时间格式错误，跳过该行：" + rowData.getEntryTime());
            return null;
        }

        // 处理出去时间（转换为 Timestamp，允许为空）
        if (StringUtils.isNotEmpty(rowData.getExitTime())) {
            try {
                Date exitDate = DateUtils.parseDate(rowData.getExitTime(), Locale.CHINA, "yyyy/MM/dd");
                entity.setOutTime(String.valueOf(new Timestamp(exitDate.getTime())));
            } catch (ParseException e) {
                System.err.println("出去时间格式错误，跳过该行：" + rowData.getExitTime());
                return null;
            }
        }

        // 处理车辆类型（非空，按 Excel 内容赋值）
        entity.setVehicleType(rowData.getVehicleType());

        // 处理付款状态（允许为空，直接赋值）
        entity.setState(rowData.getPaidStatus());

        // 处理付款金额（转换为 BigDecimal，允许为空）
        if (StringUtils.isNotEmpty(rowData.getPaymentAmount())) {
            try {
                entity.setPaymentAmount(String.valueOf(new BigDecimal(rowData.getPaymentAmount())));
            } catch (NumberFormatException e) {
                System.err.println("付款金额格式错误，跳过该行：" + rowData.getPaymentAmount());
                return null;
            }
        }

        // 处理付款商户号（允许为空，直接赋值）
        entity.setMerchantId(rowData.getMerchantId());

        // 处理进出方向（非空，直接赋值）
        entity.setDirection(rowData.getDirection());

        // 其他默认值设置
        entity.setCreateTime(new Timestamp(System.currentTimeMillis()));
        // inoutId 可根据需求生成，比如用 UUID：entity.setInoutId(UUID.randomUUID().toString()); 
        // 这里先简单示例，实际可按业务规则完善
        long timestamp = Instant.now().toEpochMilli();
        entity.setInoutId(String.format("%010d", timestamp % 10000000000L));

        return entity;
    }

    // 映射 Excel 一行数据的临时类
    public static class ExcelRowData {
        private String plateNumber;     // 对应 Excel 的 plate_number
        private String communityName;   // 对应 Excel 的 community_name（可用于校验小区，这里暂未用）
        private String entryTime;       // 对应 Excel 的 entry_time
        private String exitTime;        // 对应 Excel 的 exit_time
        private String vehicleType;     // 对应 Excel 的 vehicle_type
        private String paidStatus;      // 对应 Excel 的 paid_status
        private String paymentAmount;   // 对应 Excel 的 payment_amount
        private String merchantId;      // 对应 Excel 的 merchant_id
        private String direction;       // 对应 Excel 的 direction

        // Getter 和 Setter 方法
        public String getPlateNumber() {
            return plateNumber;
        }

        public void setPlateNumber(String plateNumber) {
            this.plateNumber = plateNumber;
        }

        public String getCommunityName() {
            return communityName;
        }

        public void setCommunityName(String communityName) {
            this.communityName = communityName;
        }

        public String getEntryTime() {
            return entryTime;
        }

        public void setEntryTime(String entryTime) {
            this.entryTime = entryTime;
        }

        public String getExitTime() {
            return exitTime;
        }

        public void setExitTime(String exitTime) {
            this.exitTime = exitTime;
        }

        public String getVehicleType() {
            return vehicleType;
        }

        public void setVehicleType(String vehicleType) {
            this.vehicleType = vehicleType;
        }

        public String getPaidStatus() {
            return paidStatus;
        }

        public void setPaidStatus(String paidStatus) {
            this.paidStatus = paidStatus;
        }

        public String getPaymentAmount() {
            return paymentAmount;
        }

        public void setPaymentAmount(String paymentAmount) {
            this.paymentAmount = paymentAmount;
        }

        public String getMerchantId() {
            return merchantId;
        }

        public void setMerchantId(String merchantId) {
            this.merchantId = merchantId;
        }

        public String getDirection() {
            return direction;
        }

        public void setDirection(String direction) {
            this.direction = direction;
        }
    }
}

 springboot/src/main/resources/application.yml

@@ -1,6 +1,6 @@
spring:
  profiles:
    active:  devlocal
    active:  debug


#  docker build -t lx .