From 54e10aee11aa4a10926df02782ca07d41eeb9b98 Mon Sep 17 00:00:00 2001
From: xyc <jc_xiong@hotmail.com>
Date: 星期二, 06 十月 2026 16:19:07 +0800
Subject: [PATCH] fix(holiday): 加大折线图纵向间距并同步 Word 尺寸
---
traffic-audit-server/src/main/java/com/trafficaudit/holiday/service/HolidayWorkbookService.java | 251 +++++++++++++++++++++++++++++++++++++++++++++----
1 files changed, 227 insertions(+), 24 deletions(-)
diff --git a/traffic-audit-server/src/main/java/com/trafficaudit/holiday/service/HolidayWorkbookService.java b/traffic-audit-server/src/main/java/com/trafficaudit/holiday/service/HolidayWorkbookService.java
index 3fc5d2e..cdf5e8a 100644
--- a/traffic-audit-server/src/main/java/com/trafficaudit/holiday/service/HolidayWorkbookService.java
+++ b/traffic-audit-server/src/main/java/com/trafficaudit/holiday/service/HolidayWorkbookService.java
@@ -46,6 +46,7 @@
private static final Map<String, String> PASSENGER_SHEET_MODE = new LinkedHashMap<>();
private static final int FIRST_DATA_ROW_INDEX = 2;
+ private static final int DAY_ROW_SCAN_LIMIT = 30;
private static final int[] REFERENCE_YEARS = {2024, 2025, 2026};
static {
@@ -115,7 +116,7 @@
ImportSummary summary = new ImportSummary();
try (InputStream in = file.getInputStream(); Workbook workbook = WorkbookFactory.create(in)) {
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
- importPassengerSheets(workbook, evaluator, year, dateByDay, summary);
+ importPassengerSheets(workbook, evaluator, year, holidayType, dateByDay, summary);
importVehicleSheets(workbook, evaluator, year, dateByDay, summary);
} catch (Exception e) {
throw new IllegalArgumentException("宸ヤ綔绨胯В鏋愬け璐�: " + e.getMessage(), e);
@@ -161,7 +162,7 @@
if (sheet == null) continue;
Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
if (yearColumn == null) continue;
- Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + dayIndex - 1);
+ Row row = findDayRow(sheet, evaluator, dayIndex);
if (row == null) continue;
BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
if (value != null) return true;
@@ -174,7 +175,7 @@
if (expressway != null) {
Integer yearColumn = findYearColumn(expressway, evaluator, year, 2, 7);
if (yearColumn != null) {
- Row row = expressway.getRow(FIRST_DATA_ROW_INDEX + dayIndex - 1);
+ Row row = findDayRow(expressway, evaluator, dayIndex);
if (row != null && numberValue(row.getCell(yearColumn), evaluator) != null) return true;
}
}
@@ -182,7 +183,7 @@
if (nationalProvincial != null) {
Integer yearColumn = findYearColumn(nationalProvincial, evaluator, year, 3, 8);
if (yearColumn != null) {
- Row row = nationalProvincial.getRow(FIRST_DATA_ROW_INDEX + dayIndex - 1);
+ Row row = findDayRow(nationalProvincial, evaluator, dayIndex);
if (row != null && numberValue(row.getCell(yearColumn), evaluator) != null) return true;
}
}
@@ -200,7 +201,7 @@
if (yearColumn == null) continue;
for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
if (day.getKey() >= latestDay) continue;
- Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ Row row = findDayRow(sheet, evaluator, day.getKey());
if (row == null) continue;
BigDecimal incoming = numberValue(row.getCell(yearColumn), evaluator);
if (incoming == null) continue;
@@ -236,7 +237,7 @@
if (yearColumn == null) return;
for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
if (day.getKey() >= latestDay) continue;
- Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ Row row = findDayRow(sheet, evaluator, day.getKey());
if (row == null) continue;
BigDecimal incoming = numberValue(row.getCell(yearColumn), evaluator);
if (incoming == null) continue;
@@ -261,7 +262,7 @@
if (day.getKey() >= latestDay) continue;
Map<String, BigDecimal> existingDay = existingVehicle.get(day.getValue());
if (existingDay == null || existingDay.get(HolidayConstants.ROAD_NATIONAL_PROVINCIAL) == null) continue;
- Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ Row row = findDayRow(sheet, evaluator, day.getKey());
if (row == null) continue;
BigDecimal incoming;
try {
@@ -278,8 +279,8 @@
}
}
- Map<LocalDate, Map<String, BigDecimal>> result = new LinkedHashMap<>();
private Map<LocalDate, Map<String, BigDecimal>> loadPassengerByDate(int year) {
+ Map<LocalDate, Map<String, BigDecimal>> result = new LinkedHashMap<>();
List<HolidayPassengerFlow> rows = passengerFlowMapper.selectList(
new LambdaQueryWrapper<HolidayPassengerFlow>().eq(HolidayPassengerFlow::getYear, year));
for (HolidayPassengerFlow row : rows) {
@@ -337,6 +338,7 @@
try (InputStream in = new FileInputStream(reference); Workbook workbook = WorkbookFactory.create(in)) {
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
seedNationalDayCalendar();
+ ensurePreviousDayCalendarIfMissing();
for (int year : REFERENCE_YEARS) {
if (passengerFlowMapper.selectCount(new LambdaQueryWrapper<HolidayPassengerFlow>()
.eq(HolidayPassengerFlow::getYear, year)) == 0) {
@@ -351,6 +353,8 @@
seedCoefficientYear(workbook, evaluator, year);
}
}
+ seedPreviousDayPassengerIfMissing(workbook, evaluator);
+ seedRoad2019IfMissing(workbook, evaluator);
} catch (Exception e) {
log.error("Failed to seed holiday reference data", e);
}
@@ -381,7 +385,8 @@
}
private void importPassengerSheets(Workbook workbook, FormulaEvaluator evaluator, int year,
- Map<Integer, LocalDate> dateByDay, ImportSummary summary) {
+ String holidayType, Map<Integer, LocalDate> dateByDay,
+ ImportSummary summary) {
for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
Sheet sheet = workbook.getSheet(entry.getKey());
if (sheet == null) {
@@ -394,7 +399,7 @@
continue;
}
for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
- Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ Row row = findDayRow(sheet, evaluator, day.getKey());
if (row == null) {
summary.addError(entry.getKey() + " 绗�" + day.getKey() + "鏃ワ細宸ヤ綔琛ㄧ己灏戣琛�");
continue;
@@ -418,6 +423,39 @@
upsertPassenger(year, day.getValue(), entry.getValue(), value);
summary.incrementPassenger();
}
+ }
+ importPreviousDayPassengerRows(workbook, evaluator, year, holidayType, dateByDay, summary);
+ }
+
+ private void importPreviousDayPassengerRows(Workbook workbook, FormulaEvaluator evaluator, int year,
+ String holidayType, Map<Integer, LocalDate> dateByDay,
+ ImportSummary summary) {
+ LocalDate previousDate = previousDayDate(year, holidayType, dateByDay);
+ if (previousDate == null) return;
+ for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
+ if (!HolidayConstants.MODE_RAIL.equals(entry.getValue())
+ && !HolidayConstants.MODE_CIVIL_AVIATION.equals(entry.getValue())) {
+ continue;
+ }
+ Sheet sheet = workbook.getSheet(entry.getKey());
+ if (sheet == null) continue;
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
+ if (yearColumn == null) continue;
+ Row row = findPreviousDayRow(sheet, evaluator);
+ if (row == null) continue;
+ BigDecimal value;
+ try {
+ value = numberValue(row.getCell(yearColumn), evaluator);
+ } catch (Exception e) {
+ summary.addError(entry.getKey() + " 鍓嶄竴鏃ヨ锛�" + e.getMessage());
+ continue;
+ }
+ if (value == null) {
+ summary.incrementSkipped();
+ continue;
+ }
+ upsertPassenger(year, previousDate, entry.getValue(), value);
+ summary.incrementPassenger();
}
}
@@ -445,7 +483,7 @@
return;
}
for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
- Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ Row row = findDayRow(sheet, evaluator, day.getKey());
if (row == null) {
summary.addError(sheetName + " 绗�" + day.getKey() + "鏃ワ細宸ヤ綔琛ㄧ己灏戣琛�");
continue;
@@ -491,7 +529,17 @@
Map<LocalDate, BigDecimal> values = loadPassengerValues(year, entry.getValue());
for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
if (!values.containsKey(day.getValue())) continue;
- setNumeric(sheet, FIRST_DATA_ROW_INDEX + day.getKey() - 1, yearColumn, values.get(day.getValue()));
+ Row row = findDayRow(sheet, evaluator, day.getKey());
+ if (row == null) continue;
+ setNumeric(sheet, row.getRowNum(), yearColumn, values.get(day.getValue()));
+ }
+ LocalDate previousDate = previousDayDate(year, holidayType, dayDates);
+ Row previousRow = findPreviousDayRow(sheet, evaluator);
+ if (previousDate != null && previousRow != null) {
+ BigDecimal previousValue = values.get(previousDate);
+ if (previousValue != null) {
+ setNumeric(sheet, previousRow.getRowNum(), yearColumn, previousValue);
+ }
}
}
}
@@ -515,22 +563,42 @@
Map<LocalDate, BigDecimal> values = loadVehicleValues(year, roadCategory);
for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
if (!values.containsKey(day.getValue())) continue;
- setNumeric(sheet, FIRST_DATA_ROW_INDEX + day.getKey() - 1, yearColumn, values.get(day.getValue()));
+ Row row = findDayRow(sheet, evaluator, day.getKey());
+ if (row == null) continue;
+ setNumeric(sheet, row.getRowNum(), yearColumn, values.get(day.getValue()));
}
if (comparisonBaseColumn != null && HolidayConstants.supportsComparisonBase(year)) {
+ // Column C (comparison base) is written as an operator backup value only. The ring
+ // ratio in column E always compares the current day with the previous day row of
+ // the same year, so the base never changes the calculated percentage.
Map<LocalDate, BigDecimal> bases = loadComparisonBaseValues(year);
for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
if (!bases.containsKey(day.getValue())) continue;
- setNumeric(sheet, FIRST_DATA_ROW_INDEX + day.getKey() - 1, comparisonBaseColumn, bases.get(day.getValue()));
+ Row row = findDayRow(sheet, evaluator, day.getKey());
+ if (row == null) continue;
+ setNumeric(sheet, row.getRowNum(), comparisonBaseColumn, bases.get(day.getValue()));
}
+ Integer previousRowIndex = null;
for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
- if (!bases.containsKey(day.getValue())) continue;
- int rowIndex = FIRST_DATA_ROW_INDEX + day.getKey() - 1;
- int excelRow = rowIndex + 1;
- setFormula(sheet, rowIndex, 4, "=(D" + excelRow + "-C" + excelRow + ")/C" + excelRow);
+ Row row = findDayRow(sheet, evaluator, day.getKey());
+ if (row == null) {
+ previousRowIndex = null;
+ continue;
+ }
+ if (previousRowIndex != null) {
+ setFormula(sheet, row.getRowNum(), 4,
+ ringRatioFormula(row.getRowNum() + 1, previousRowIndex + 1));
+ }
+ previousRowIndex = row.getRowNum();
}
}
}
+ }
+
+ // Ring ratio = (current day value - previous day value) / previous day value, always taken
+ // from the active year column (D). This intentionally ignores the comparison base column.
+ static String ringRatioFormula(int excelRow, int previousExcelRow) {
+ return "=(D" + excelRow + "-D" + previousExcelRow + ")/D" + previousExcelRow;
}
private void fillCoefficients(XSSFWorkbook workbook, FormulaEvaluator evaluator, int selectedYear, String holidayType) {
@@ -560,9 +628,10 @@
if (!configService.listHolidays(null, null).isEmpty()) {
return;
}
- int[] lengths = {7, 8, 8};
- for (int i = 0; i < REFERENCE_YEARS.length; i++) {
- int year = REFERENCE_YEARS[i];
+ int[] calendarYears = {2019, 2024, 2025, 2026};
+ int[] lengths = {8, 7, 8, 8};
+ for (int i = 0; i < calendarYears.length; i++) {
+ int year = calendarYears[i];
List<HolidayCalendar> existing = configService.findHolidayDays(year, HolidayConstants.TYPE_NATIONAL_DAY);
Map<Integer, HolidayCalendar> byDay = new LinkedHashMap<>();
for (HolidayCalendar row : existing) {
@@ -579,6 +648,87 @@
calendar.setUpdatedAt(LocalDateTime.now());
configService.saveHoliday(toMap(calendar));
}
+ if (year == 2019) {
+ continue;
+ }
+ HolidayCalendar previousDay = new HolidayCalendar();
+ previousDay.setYear(year);
+ previousDay.setHolidayType(HolidayConstants.TYPE_NATIONAL_DAY);
+ previousDay.setHolidayDate(LocalDate.of(year, 10, 1).minusDays(1));
+ previousDay.setDayIndex(0);
+ previousDay.setCreatedAt(LocalDateTime.now());
+ previousDay.setUpdatedAt(LocalDateTime.now());
+ configService.saveHoliday(toMap(previousDay));
+ }
+ }
+
+ private void ensurePreviousDayCalendarIfMissing() {
+ for (int year : REFERENCE_YEARS) {
+ if (configService.findPreviousDay(year, HolidayConstants.TYPE_NATIONAL_DAY) != null) {
+ continue;
+ }
+ LocalDate firstHolidayDate = firstDate(dayDates(year, HolidayConstants.TYPE_NATIONAL_DAY));
+ if (firstHolidayDate == null) {
+ continue;
+ }
+ HolidayCalendar previousDay = new HolidayCalendar();
+ previousDay.setYear(year);
+ previousDay.setHolidayType(HolidayConstants.TYPE_NATIONAL_DAY);
+ previousDay.setHolidayDate(firstHolidayDate.minusDays(1));
+ previousDay.setDayIndex(0);
+ previousDay.setCreatedAt(LocalDateTime.now());
+ previousDay.setUpdatedAt(LocalDateTime.now());
+ configService.saveHoliday(toMap(previousDay));
+ }
+ }
+
+ private void seedPreviousDayPassengerIfMissing(Workbook workbook, FormulaEvaluator evaluator) {
+ for (int year : REFERENCE_YEARS) {
+ Map<Integer, LocalDate> dayDates = dayDates(year, HolidayConstants.TYPE_NATIONAL_DAY);
+ LocalDate previousDate = previousDayDate(year, HolidayConstants.TYPE_NATIONAL_DAY, dayDates);
+ if (previousDate == null) continue;
+ for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
+ if (!HolidayConstants.MODE_RAIL.equals(entry.getValue())
+ && !HolidayConstants.MODE_CIVIL_AVIATION.equals(entry.getValue())) {
+ continue;
+ }
+ if (passengerFlowMapper.selectCount(new LambdaQueryWrapper<HolidayPassengerFlow>()
+ .eq(HolidayPassengerFlow::getYear, year)
+ .eq(HolidayPassengerFlow::getFlowDate, previousDate)
+ .eq(HolidayPassengerFlow::getTransportMode, entry.getValue())) > 0) {
+ continue;
+ }
+ Sheet sheet = workbook.getSheet(entry.getKey());
+ if (sheet == null) continue;
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
+ if (yearColumn == null) continue;
+ Row row = findPreviousDayRow(sheet, evaluator);
+ if (row == null) continue;
+ BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
+ if (value != null) upsertPassenger(year, previousDate, entry.getValue(), value);
+ }
+ }
+ }
+
+ private void seedRoad2019IfMissing(Workbook workbook, FormulaEvaluator evaluator) {
+ if (passengerFlowMapper.selectCount(new LambdaQueryWrapper<HolidayPassengerFlow>()
+ .eq(HolidayPassengerFlow::getYear, 2019)
+ .eq(HolidayPassengerFlow::getTransportMode, HolidayConstants.MODE_ROAD)) > 0) {
+ return;
+ }
+ Sheet sheet = workbook.getSheet("閬撹矾");
+ if (sheet == null) return;
+ Integer yearColumn = findYearColumn(sheet, evaluator, 2019, 2, 20);
+ if (yearColumn == null) return;
+ for (int rowIndex = 0; rowIndex <= Math.min(sheet.getLastRowNum(), DAY_ROW_SCAN_LIMIT); rowIndex++) {
+ Row row = sheet.getRow(rowIndex);
+ if (row == null) continue;
+ Integer dayIndex = parseDayIndex(row.getCell(0), evaluator);
+ if (dayIndex == null) continue;
+ BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
+ if (value != null) {
+ upsertPassenger(2019, LocalDate.of(2019, 10, dayIndex), HolidayConstants.MODE_ROAD, value);
+ }
}
}
@@ -591,11 +741,22 @@
Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
if (yearColumn == null) continue;
for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
- Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ Row row = findDayRow(sheet, evaluator, day.getKey());
if (row == null) continue;
BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
if (value != null) {
upsertPassenger(year, day.getValue(), entry.getValue(), value);
+ }
+ }
+ if (HolidayConstants.MODE_RAIL.equals(entry.getValue())
+ || HolidayConstants.MODE_CIVIL_AVIATION.equals(entry.getValue())) {
+ Row previousRow = findPreviousDayRow(sheet, evaluator);
+ LocalDate previousDate = previousDayDate(year, HolidayConstants.TYPE_NATIONAL_DAY, dayDates);
+ if (previousRow != null && previousDate != null) {
+ BigDecimal value = numberValue(previousRow.getCell(yearColumn), evaluator);
+ if (value != null) {
+ upsertPassenger(year, previousDate, entry.getValue(), value);
+ }
}
}
}
@@ -618,7 +779,7 @@
Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
if (yearColumn == null) return;
for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
- Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ Row row = findDayRow(sheet, evaluator, day.getKey());
if (row == null) continue;
BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
if (value != null) {
@@ -657,6 +818,46 @@
// Skip cells that are not numeric coefficients.
}
}
+ }
+
+ private LocalDate previousDayDate(int year, String holidayType, Map<Integer, LocalDate> dateByDay) {
+ HolidayCalendar configured = configService.findPreviousDay(year, holidayType);
+ if (configured != null && configured.getHolidayDate() != null) {
+ return configured.getHolidayDate();
+ }
+ LocalDate firstDate = firstDate(dateByDay);
+ return firstDate == null ? null : firstDate.minusDays(1);
+ }
+
+ private LocalDate firstDate(Map<Integer, LocalDate> dateByDay) {
+ LocalDate result = null;
+ for (LocalDate date : dateByDay.values()) {
+ if (date != null && (result == null || date.isBefore(result))) result = date;
+ }
+ return result;
+ }
+
+ private Row findDayRow(Sheet sheet, FormulaEvaluator evaluator, int dayIndex) {
+ int lastRow = Math.min(sheet.getLastRowNum(), DAY_ROW_SCAN_LIMIT);
+ for (int rowIndex = 0; rowIndex <= lastRow; rowIndex++) {
+ Row row = sheet.getRow(rowIndex);
+ if (row == null) continue;
+ Integer rowDay = parseDayIndex(row.getCell(0), evaluator);
+ if (rowDay != null && rowDay.intValue() == dayIndex) return row;
+ }
+ return null;
+ }
+
+ private Row findPreviousDayRow(Sheet sheet, FormulaEvaluator evaluator) {
+ int lastRow = Math.min(sheet.getLastRowNum(), DAY_ROW_SCAN_LIMIT);
+ DataFormatter formatter = new DataFormatter();
+ for (int rowIndex = 0; rowIndex <= lastRow; rowIndex++) {
+ Row row = sheet.getRow(rowIndex);
+ if (row == null || row.getCell(0) == null) continue;
+ String text = formatter.formatCellValue(row.getCell(0), evaluator).trim();
+ if (text.contains("鍓嶄竴鏃�")) return row;
+ }
+ return null;
}
private Map<Integer, LocalDate> dayDates(int year, String holidayType) {
@@ -826,7 +1027,9 @@
if (row == null) row = sheet.createRow(rowIndex);
Cell cell = row.getCell(columnIndex);
if (cell == null) cell = row.createCell(columnIndex);
- cell.setCellFormula(formula);
+ // POI's setCellFormula rejects a leading '='; the helper accepts either form.
+ String normalized = formula.startsWith("=") ? formula.substring(1) : formula;
+ cell.setCellFormula(normalized);
}
private Map<String, Object> toMap(HolidayCalendar calendar) {
--
Gitblit v1.9.1