package com.trafficaudit.observation.service;
|
|
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
|
import com.trafficaudit.observation.dto.ObservationDateSummary;
|
import com.trafficaudit.observation.entity.ObservationStationFlow;
|
import com.trafficaudit.observation.mapper.ObservationStationFlowMapper;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.CellType;
|
import org.apache.poi.ss.usermodel.DataFormatter;
|
import org.apache.poi.ss.usermodel.DateUtil;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.ss.usermodel.WorkbookFactory;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.annotation.Resource;
|
import java.io.InputStream;
|
import java.math.BigDecimal;
|
import java.time.LocalDate;
|
import java.time.format.DateTimeFormatter;
|
import java.util.ArrayList;
|
import java.util.LinkedHashMap;
|
import java.util.LinkedHashSet;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Set;
|
|
/**
|
* Imports the "observation station daily survey" workbook (.xls/.xlsx).
|
* Header names are matched by text, so column order may change between releases.
|
* Re-importing the same date replaces that date's rows.
|
*/
|
@Slf4j
|
@Service
|
public class ObservationStationImportService {
|
|
private static final String H_DATE = "观测日期";
|
private static final String H_CODE = "观测站编号";
|
private static final String H_NAME = "观测站名称";
|
private static final String H_MILEAGE = "观测里程";
|
private static final String H_CITY = "地市名称";
|
private static final String H_COUNTY = "区县名称";
|
private static final String H_DIRECTION = "行驶方向";
|
private static final String H_SMALL_MEDIUM_PASSENGER = "中小客流量";
|
private static final String H_LARGE_PASSENGER = "大客车流量";
|
private static final String H_SMALL_TRUCK = "小货车流量";
|
private static final String H_MEDIUM_TRUCK = "中货车流量";
|
private static final String H_LARGE_TRUCK = "大货车流量";
|
private static final String H_EXTRA_LARGE_TRUCK = "特大货流量";
|
private static final String H_CONTAINER = "集装箱流量";
|
private static final String H_MOTORCYCLE = "摩托车流量";
|
private static final String H_TRACTOR = "拖拉机流量";
|
private static final String H_PASSENGER_TOTAL = "客车流量";
|
private static final String H_TRUCK_TOTAL = "货车流量";
|
private static final String H_CAR_TOTAL = "汽车流量";
|
private static final String H_MOTOR_TOTAL = "机动车流量";
|
private static final String H_PASSENGER_EQUIVALENT = "客车当量";
|
private static final String H_TRUCK_EQUIVALENT = "货车当量";
|
private static final String H_CAR_EQUIVALENT = "汽车当量";
|
private static final String H_MOTOR_EQUIVALENT = "机动车当量";
|
private static final String H_SPEED = "机动车速度";
|
private static final String H_CONGESTION = "拥挤度";
|
|
private static final int HEADER_SCAN_ROWS = 10;
|
private static final int MAX_REPORTED_ERRORS = 50;
|
|
private static final DateTimeFormatter[] DATE_FORMATS = {
|
DateTimeFormatter.ofPattern("yyyy-M-d"),
|
DateTimeFormatter.ofPattern("yyyy/M/d"),
|
DateTimeFormatter.ofPattern("yyyy.M.d"),
|
DateTimeFormatter.ofPattern("yyyyMMdd")
|
};
|
|
@Resource
|
private ObservationStationFlowMapper stationFlowMapper;
|
|
@Transactional
|
public Map<String, Object> importWorkbook(MultipartFile file) {
|
if (file == null || file.isEmpty()) {
|
throw new IllegalArgumentException("请选择要导入的 Excel 文件");
|
}
|
String sourceFile = file.getOriginalFilename();
|
List<ObservationStationFlow> rows = new ArrayList<>();
|
Set<LocalDate> dates = new LinkedHashSet<>();
|
Set<String> stationCodes = new LinkedHashSet<>();
|
List<String> errors = new ArrayList<>();
|
int skipped = 0;
|
|
DataFormatter formatter = new DataFormatter();
|
try (InputStream in = file.getInputStream(); Workbook workbook = WorkbookFactory.create(in)) {
|
Sheet sheet = workbook.getNumberOfSheets() > 0 ? workbook.getSheetAt(0) : null;
|
if (sheet == null) {
|
throw new IllegalArgumentException("工作簿中没有可读取的工作表");
|
}
|
int headerRowIndex = findHeaderRow(sheet, formatter);
|
if (headerRowIndex < 0) {
|
throw new IllegalArgumentException("未找到表头，请确认文件包含\"观测日期\"和\"观测站编号\"两列");
|
}
|
Map<String, Integer> headerIndex = buildHeaderIndex(sheet.getRow(headerRowIndex), formatter);
|
|
for (int r = headerRowIndex + 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) {
|
skipped++;
|
continue;
|
}
|
String code = stringCell(row, headerIndex, H_CODE, formatter);
|
LocalDate obsDate = dateCell(row, headerIndex, H_DATE, formatter);
|
if (isBlank(code) && obsDate == null) {
|
skipped++;
|
continue;
|
}
|
int rowNo = r + 1;
|
if (obsDate == null) {
|
addError(errors, "第" + rowNo + "行：观测日期无法识别");
|
continue;
|
}
|
if (isBlank(code)) {
|
addError(errors, "第" + rowNo + "行：观测站编号为空");
|
continue;
|
}
|
ObservationStationFlow entity = new ObservationStationFlow();
|
entity.setObsDate(obsDate);
|
entity.setStationCode(code);
|
entity.setStationName(stringCell(row, headerIndex, H_NAME, formatter));
|
entity.setMileage(decimalCell(row, headerIndex, H_MILEAGE, formatter));
|
entity.setCityName(stringCell(row, headerIndex, H_CITY, formatter));
|
entity.setCountyName(stringCell(row, headerIndex, H_COUNTY, formatter));
|
String direction = stringCell(row, headerIndex, H_DIRECTION, formatter);
|
entity.setDirection(direction == null ? "" : direction);
|
entity.setSmallMediumPassenger(decimalCell(row, headerIndex, H_SMALL_MEDIUM_PASSENGER, formatter));
|
entity.setLargePassenger(decimalCell(row, headerIndex, H_LARGE_PASSENGER, formatter));
|
entity.setSmallTruck(decimalCell(row, headerIndex, H_SMALL_TRUCK, formatter));
|
entity.setMediumTruck(decimalCell(row, headerIndex, H_MEDIUM_TRUCK, formatter));
|
entity.setLargeTruck(decimalCell(row, headerIndex, H_LARGE_TRUCK, formatter));
|
entity.setExtraLargeTruck(decimalCell(row, headerIndex, H_EXTRA_LARGE_TRUCK, formatter));
|
entity.setContainerTruck(decimalCell(row, headerIndex, H_CONTAINER, formatter));
|
entity.setMotorcycle(decimalCell(row, headerIndex, H_MOTORCYCLE, formatter));
|
entity.setTractor(decimalCell(row, headerIndex, H_TRACTOR, formatter));
|
entity.setPassengerTotal(decimalCell(row, headerIndex, H_PASSENGER_TOTAL, formatter));
|
entity.setTruckTotal(decimalCell(row, headerIndex, H_TRUCK_TOTAL, formatter));
|
entity.setCarTotal(decimalCell(row, headerIndex, H_CAR_TOTAL, formatter));
|
entity.setMotorTotal(decimalCell(row, headerIndex, H_MOTOR_TOTAL, formatter));
|
entity.setPassengerEquivalent(decimalCell(row, headerIndex, H_PASSENGER_EQUIVALENT, formatter));
|
entity.setTruckEquivalent(decimalCell(row, headerIndex, H_TRUCK_EQUIVALENT, formatter));
|
entity.setCarEquivalent(decimalCell(row, headerIndex, H_CAR_EQUIVALENT, formatter));
|
entity.setMotorEquivalent(decimalCell(row, headerIndex, H_MOTOR_EQUIVALENT, formatter));
|
entity.setVehicleSpeed(decimalCell(row, headerIndex, H_SPEED, formatter));
|
entity.setCongestionDegree(decimalCell(row, headerIndex, H_CONGESTION, formatter));
|
entity.setSourceFile(sourceFile);
|
rows.add(entity);
|
dates.add(obsDate);
|
stationCodes.add(code);
|
}
|
} catch (IllegalArgumentException e) {
|
throw e;
|
} catch (Exception e) {
|
throw new IllegalArgumentException("工作簿解析失败: " + e.getMessage(), e);
|
}
|
|
if (!rows.isEmpty()) {
|
stationFlowMapper.delete(new LambdaQueryWrapper<ObservationStationFlow>()
|
.in(ObservationStationFlow::getObsDate, dates));
|
for (ObservationStationFlow row : rows) {
|
stationFlowMapper.insert(row);
|
}
|
}
|
|
List<String> dateTexts = new ArrayList<>();
|
for (LocalDate date : dates) {
|
dateTexts.add(date.toString());
|
}
|
BigDecimal smallMediumTotal = BigDecimal.ZERO;
|
for (ObservationStationFlow row : rows) {
|
if (row.getSmallMediumPassenger() != null) {
|
smallMediumTotal = smallMediumTotal.add(row.getSmallMediumPassenger());
|
}
|
}
|
|
Map<String, Object> summary = new LinkedHashMap<>();
|
summary.put("totalRows", rows.size());
|
summary.put("stationCount", stationCodes.size());
|
summary.put("dates", dateTexts);
|
summary.put("skippedRows", skipped);
|
summary.put("smallMediumPassengerTotal", smallMediumTotal);
|
summary.put("errors", errors);
|
log.info("Observation station import done: file={} rows={} dates={} skipped={} failed={}",
|
sourceFile, rows.size(), dateTexts, skipped, errors.size());
|
return summary;
|
}
|
|
public List<ObservationDateSummary> listDateSummaries() {
|
return stationFlowMapper.selectDateSummary();
|
}
|
|
public List<ObservationStationFlow> listRows(LocalDate date, int limit) {
|
int effectiveLimit = limit <= 0 ? 2000 : Math.min(limit, 5000);
|
return stationFlowMapper.selectList(new LambdaQueryWrapper<ObservationStationFlow>()
|
.eq(date != null, ObservationStationFlow::getObsDate, date)
|
.orderByAsc(ObservationStationFlow::getId)
|
.last("LIMIT " + effectiveLimit));
|
}
|
|
public int deleteDate(LocalDate date) {
|
if (date == null) {
|
throw new IllegalArgumentException("请选择要删除的观测日期");
|
}
|
return stationFlowMapper.delete(new LambdaQueryWrapper<ObservationStationFlow>()
|
.eq(ObservationStationFlow::getObsDate, date));
|
}
|
|
private void addError(List<String> errors, String message) {
|
if (errors.size() < MAX_REPORTED_ERRORS) {
|
errors.add(message);
|
}
|
}
|
|
private int findHeaderRow(Sheet sheet, DataFormatter formatter) {
|
int lastRow = Math.min(sheet.getLastRowNum(), HEADER_SCAN_ROWS);
|
for (int r = 0; r <= lastRow; r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) {
|
continue;
|
}
|
Map<String, Integer> index = buildHeaderIndex(row, formatter);
|
if (index.containsKey(normalize(H_DATE)) && index.containsKey(normalize(H_CODE))) {
|
return r;
|
}
|
}
|
return -1;
|
}
|
|
private Map<String, Integer> buildHeaderIndex(Row row, DataFormatter formatter) {
|
Map<String, Integer> index = new LinkedHashMap<>();
|
if (row == null) {
|
return index;
|
}
|
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
|
Cell cell = row.getCell(c);
|
String header = normalize(formatter.formatCellValue(cell));
|
if (!header.isEmpty() && !index.containsKey(header)) {
|
index.put(header, c);
|
}
|
}
|
return index;
|
}
|
|
private String normalize(String value) {
|
if (value == null) {
|
return "";
|
}
|
StringBuilder builder = new StringBuilder(value.length());
|
for (int i = 0; i < value.length(); i++) {
|
char ch = value.charAt(i);
|
if (ch == 0xFEFF || Character.isWhitespace(ch)) {
|
continue;
|
}
|
builder.append(ch);
|
}
|
return builder.toString();
|
}
|
|
private String stringCell(Row row, Map<String, Integer> headerIndex, String header, DataFormatter formatter) {
|
Integer column = headerIndex.get(normalize(header));
|
if (column == null) {
|
return null;
|
}
|
Cell cell = row.getCell(column);
|
if (cell == null) {
|
return null;
|
}
|
String value = formatter.formatCellValue(cell).trim();
|
return value.isEmpty() ? null : value;
|
}
|
|
private LocalDate dateCell(Row row, Map<String, Integer> headerIndex, String header, DataFormatter formatter) {
|
Integer column = headerIndex.get(normalize(header));
|
if (column == null) {
|
return null;
|
}
|
return readDate(row.getCell(column), formatter);
|
}
|
|
private BigDecimal decimalCell(Row row, Map<String, Integer> headerIndex, String header, DataFormatter formatter) {
|
Integer column = headerIndex.get(normalize(header));
|
if (column == null) {
|
return null;
|
}
|
return readDecimal(row.getCell(column), formatter);
|
}
|
|
private LocalDate readDate(Cell cell, DataFormatter formatter) {
|
if (cell == null) {
|
return null;
|
}
|
CellType type = cell.getCellType();
|
if (type == CellType.FORMULA) {
|
type = cell.getCachedFormulaResultType();
|
}
|
if (type == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
|
return cell.getLocalDateTimeCellValue().toLocalDate();
|
}
|
String text = formatter.formatCellValue(cell).trim();
|
if (text.isEmpty()) {
|
return null;
|
}
|
text = text.replace("年", "-").replace("月", "-").replace("日", "").trim();
|
for (DateTimeFormatter format : DATE_FORMATS) {
|
try {
|
return LocalDate.parse(text, format);
|
} catch (Exception ignored) {
|
// Try the next supported pattern.
|
}
|
}
|
return null;
|
}
|
|
private BigDecimal readDecimal(Cell cell, DataFormatter formatter) {
|
if (cell == null) {
|
return null;
|
}
|
CellType type = cell.getCellType();
|
if (type == CellType.FORMULA) {
|
type = cell.getCachedFormulaResultType();
|
}
|
if (type == CellType.NUMERIC) {
|
return BigDecimal.valueOf(cell.getNumericCellValue());
|
}
|
if (type == CellType.BLANK) {
|
return null;
|
}
|
String text = formatter.formatCellValue(cell).trim().replace(",", "").replace(" ", "");
|
if (text.isEmpty() || "-".equals(text) || "—".equals(text) || "/".equals(text)) {
|
return null;
|
}
|
try {
|
return new BigDecimal(text);
|
} catch (NumberFormatException e) {
|
return null;
|
}
|
}
|
|
private boolean isBlank(String value) {
|
return value == null || value.trim().isEmpty();
|
}
|
}
|
