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;
|
}
|
}
|
}
|
