| New file |
| | |
|---|
| | | package com.trafficaudit.reportexport.calc; |
|---|
| | | |
|---|
| | | import com.trafficaudit.common.util.RegionUtil; |
|---|
| | | import org.apache.poi.ss.usermodel.Cell; |
|---|
| | | import org.apache.poi.ss.usermodel.Row; |
|---|
| | | import org.apache.poi.ss.usermodel.Sheet; |
|---|
| | | import org.apache.poi.xssf.usermodel.XSSFWorkbook; |
|---|
| | | import org.springframework.stereotype.Service; |
|---|
| | | |
|---|
| | | import javax.annotation.Resource; |
|---|
| | | import java.util.ArrayList; |
|---|
| | | import java.util.LinkedHashMap; |
|---|
| | | import java.util.List; |
|---|
| | | import java.util.Map; |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * æ±æ»æ´æ¬âæ°æ®é©±å¨åå¡«âå¼æï¼M2 å°è´¦å±ï¼2026-09-09 æ¥å
¥ï¼ã |
|---|
| | | * |
|---|
| | | * ç®æ ï¼çæä»»ææ份ãéè·¯è¿è¾éæ±æ»è¡¨ï¼æ´æ¬ï¼ãæ¶ä¸åä¾èµå½æ人工æ¯çï¼ |
|---|
| | | * æ¯çåªå½çå¼åºç¨¿ï¼å½æå¼æå·²éªè¯å£å¾ä»åºç°ç®åå
¥ï¼ç线å
车 2026-07 ä¸äººå·¥æ¯ç |
|---|
| | | * 350 æ ¼ 0 å·®å¼ãå
¬äº¤/åºç§/轨é轮渡åæ¹ 0 å·®å¼ï¼ã |
|---|
| | | * |
|---|
| | | * æ¬çè¦ç 4 å¼ å°è´¦é¡µï¼ç线å
车ãå
¬äº¤ãåºç§ã轨éè½®æ¸¡ï¼ |
|---|
| | | * ç½çº¦è½¦ä¾èµãç½çº¦è½¦è®¢ååå
¨çæ»éãæåè¾å
¥ãè´§è¿é¡µå¨ M3 æ¥å
¥ï¼åç»çæ¬è¡¥å
ã |
|---|
| | | * |
|---|
| | | * åå
¥è§åï¼ |
|---|
| | | * 1) åªåâå¸å·æç»è¡âç 2026 å¹´ m æå½æå¼åï¼m æå¼å = 第 2+2*(m-1) åï¼0 åºï¼å³ C èµ·ï¼ï¼ |
|---|
| | | * 2) å
¨çè¡ï¼=17 å¸å·æ±åå
¬å¼ï¼ä¸è·¨é¡µåæ页å为å
¬å¼ï¼ä¸å¨ï¼Excel æå¼èªå¨éç®ï¼ |
|---|
| | | * 3) 2025 åæåç
§åä¿çæ¯çç¼åå¼ï¼åæ¯å
¬å¼èªå¨å¼ç¨ï¼ï¼ |
|---|
| | | * 4) æºæ°æ®æ´æ缺失æ¶æ¶éæ示ãä¸åå¡«ï¼å¸å·å½æå
¨ä¸º 0 æ¶ä¿ææ¯ç空åå
æ ¼ã |
|---|
| | | */ |
|---|
| | | @Service |
|---|
| | | public class SummaryWorkbookFiller { |
|---|
| | | |
|---|
| | | @Resource |
|---|
| | | private MidCalc midCalc; |
|---|
| | | @Resource |
|---|
| | | private PaxCalc paxCalc; |
|---|
| | | |
|---|
| | | /** 2026 å¹´ 1 æå½æå¼åï¼0 åºï¼å³ C åï¼ */ |
|---|
| | | private static final int FIRST_MONTH_COL = 2; |
|---|
| | | |
|---|
| | | /** åå¡« 1..keepMonths æå°è´¦é¡µï¼è¿ååå
¥æ ¼æ°ï¼ç¼ºæ°æ示åå
¥ problems */ |
|---|
| | | public int fillMonthlyLedger(XSSFWorkbook wb, int year, int keepMonths, List<String> problems) { |
|---|
| | | if (wb == null || keepMonths <= 0) return 0; |
|---|
| | | if (problems == null) problems = new ArrayList<>(); |
|---|
| | | String yearPrefix = year + "-"; |
|---|
| | | int filled = 0; |
|---|
| | | filled += fillCityRows(wb, "ç线å
车", midCalc.banxianMonthly(yearPrefix, keepMonths), year, keepMonths, problems, false); |
|---|
| | | filled += fillCityRows(wb, "å
¬äº¤", paxCalc.busMonthly(yearPrefix, keepMonths), year, keepMonths, problems, false); |
|---|
| | | filled += fillCityRows(wb, "åºç§", paxCalc.taxiMonthly(yearPrefix, keepMonths), year, keepMonths, problems, false); |
|---|
| | | filled += fillTrackFerry(wb, paxCalc.railFerryMonthly(yearPrefix, keepMonths), year, keepMonths, problems); |
|---|
| | | return filled; |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** éç¨å°è´¦é¡µï¼ç线å
车/å
¬äº¤/åºç§ï¼åå¡«ï¼æâå A å¸å·å + å B ææ ææ¡âå®ä½è¡ï¼ä¸åæ»è¡å· */ |
|---|
| | | private int fillCityRows(XSSFWorkbook wb, String sheetName, |
|---|
| | | Map<Integer, Map<String, double[]>> monthly, |
|---|
| | | int year, int keepMonths, List<String> problems, boolean wyc) { |
|---|
| | | Sheet sh = wb.getSheet(sheetName); |
|---|
| | | if (sh == null) { |
|---|
| | | problems.add(sheetName + "页ä¸åå¨ï¼æ¯ç缺失该页ï¼ï¼è·³è¿èªå¨åå¡«"); |
|---|
| | | return 0; |
|---|
| | | } |
|---|
| | | int cap = monthColumnCapacity(sh); |
|---|
| | | int mMax = Math.min(keepMonths, cap); |
|---|
| | | if (mMax <= 0) { |
|---|
| | | problems.add(sheetName + "页æªè¯å«å° 2026 年累计åï¼è·³è¿èªå¨åå¡«"); |
|---|
| | | return 0; |
|---|
| | | } |
|---|
| | | // 1) å¸å·èµ·å§è¡ï¼å A 为è§èå¸å·åï¼ |
|---|
| | | Map<String, Integer> cityStart = new LinkedHashMap<>(); |
|---|
| | | for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) { |
|---|
| | | Row row = sh.getRow(r); |
|---|
| | | if (row == null) continue; |
|---|
| | | String city = cityOf(row.getCell(0)); |
|---|
| | | if (city != null && !cityStart.containsKey(city)) cityStart.put(city, r); |
|---|
| | | } |
|---|
| | | if (cityStart.isEmpty()) { |
|---|
| | | problems.add(sheetName + "页æªè¯å«å°å¸å·è¡ï¼è·³è¿èªå¨åå¡«"); |
|---|
| | | return 0; |
|---|
| | | } |
|---|
| | | List<Integer> starts = new ArrayList<>(cityStart.values()); |
|---|
| | | int filled = 0; |
|---|
| | | List<String> missingMonths = new ArrayList<>(); |
|---|
| | | for (int m = 1; m <= mMax; m++) { |
|---|
| | | if (!monthly.containsKey(m)) missingMonths.add(m + "æ"); |
|---|
| | | } |
|---|
| | | if (!missingMonths.isEmpty()) { |
|---|
| | | problems.add(sheetName + "缺 " + year + " å¹´ " + String.join("ã", missingMonths) + " æºæ°æ®ï¼æªåå¡«ï¼"); |
|---|
| | | } |
|---|
| | | for (int i = 0; i < starts.size(); i++) { |
|---|
| | | String city = findCityByStart(sh, starts.get(i)); |
|---|
| | | if (city == null) continue; |
|---|
| | | int end = (i + 1 < starts.size()) ? starts.get(i + 1) - 1 : sh.getLastRowNum(); |
|---|
| | | // 2) è¡å
åææ æå¨è¡ï¼B åææ¡ â ææ ä½ 0..3ï¼ |
|---|
| | | int[] metricRow = new int[]{-1, -1, -1, -1}; |
|---|
| | | for (int r = starts.get(i); r <= end; r++) { |
|---|
| | | Row row = sh.getRow(r); |
|---|
| | | if (row == null) continue; |
|---|
| | | int idx = metricIndexOf(text(row.getCell(1))); |
|---|
| | | if (idx >= 0 && metricRow[idx] < 0) metricRow[idx] = r; |
|---|
| | | } |
|---|
| | | if (metricRow[0] < 0 && metricRow[1] < 0) continue; |
|---|
| | | for (int m = 1; m <= mMax; m++) { |
|---|
| | | Map<String, double[]> mm = monthly.get(m); |
|---|
| | | if (mm == null) continue; |
|---|
| | | double[] arr = mm.get(city); |
|---|
| | | if (arr == null || (arr[0] == 0.0 && arr[1] == 0.0)) continue; |
|---|
| | | int col = FIRST_MONTH_COL + 2 * (m - 1); |
|---|
| | | for (int idx = 0; idx < 4; idx++) { |
|---|
| | | if (metricRow[idx] < 0) continue; |
|---|
| | | double val = arr[idx]; |
|---|
| | | if (val == 0.0) continue; |
|---|
| | | filled += setNumeric(sh, metricRow[idx], col, val); |
|---|
| | | } |
|---|
| | | } |
|---|
| | | } |
|---|
| | | return filled; |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** 轨é轮渡页ï¼è½¨é=æ¦æ±/é»ç³ï¼è½®æ¸¡ä»
æ¦æ±ï¼ä¸ç¨åå¡« */ |
|---|
| | | private int fillTrackFerry(XSSFWorkbook wb, Map<Integer, Map<String, double[]>> monthly, |
|---|
| | | int year, int keepMonths, List<String> problems) { |
|---|
| | | String sheetName = "轨éã轮渡"; |
|---|
| | | Sheet sh = wb.getSheet(sheetName); |
|---|
| | | if (sh == null) return 0; |
|---|
| | | int cap = monthColumnCapacity(sh); |
|---|
| | | int mMax = Math.min(keepMonths, cap); |
|---|
| | | if (mMax <= 0) return 0; |
|---|
| | | // 轨éåä¸è½®æ¸¡åæ é¢è¡ï¼å A ææ¡ï¼ |
|---|
| | | int trackTitle = -1, ferryTitle = -1; |
|---|
| | | for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) { |
|---|
| | | String a = text(sh.getRow(r) == null ? null : sh.getRow(r).getCell(0)); |
|---|
| | | if (a == null) continue; |
|---|
| | | if (trackTitle < 0 && a.startsWith("轨é客è¿é")) trackTitle = r; |
|---|
| | | else if (ferryTitle < 0 && a.startsWith("轮渡客è¿é")) ferryTitle = r; |
|---|
| | | } |
|---|
| | | if (trackTitle < 0 || ferryTitle < 0) { |
|---|
| | | problems.add(sheetName + "页æªè¯å«å°è½¨é/轮渡åæ é¢ï¼è·³è¿èªå¨åå¡«"); |
|---|
| | | return 0; |
|---|
| | | } |
|---|
| | | int filled = 0; |
|---|
| | | filled += fillFerryBlock(sh, monthly, trackTitle + 3, ferryTitle - 1, 0, mMax, problems, sheetName); |
|---|
| | | filled += fillFerryBlock(sh, monthly, ferryTitle + 3, sh.getLastRowNum(), 2, mMax, problems, sheetName); |
|---|
| | | return filled; |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** å¡«å
ä¸æ®µâæ é¢è¡åæ°æ®åºâï¼base 为该åææ ä½åºï¼è½¨é 0 / 轮渡 2ï¼ï¼block å
|
|---|
| | | * æ¯å¸å· 2 è¡ï¼å®¢è¿éè¡ï¼å A=å¸å·åï¼ãæ
客å¨è½¬éè¡ï¼A 空ï¼B å«âå¨è½¬éâï¼ */ |
|---|
| | | private int fillFerryBlock(Sheet sh, Map<Integer, Map<String, double[]>> monthly, |
|---|
| | | int start, int end, int base, int mMax, |
|---|
| | | List<String> problems, String sheetName) { |
|---|
| | | int filled = 0; |
|---|
| | | // æ¶éå¸å·å®¢è¿éè¡ |
|---|
| | | Map<String, Integer> paxRow = new LinkedHashMap<>(); |
|---|
| | | for (int r = start; r <= end; r++) { |
|---|
| | | Row row = sh.getRow(r); |
|---|
| | | if (row == null) continue; |
|---|
| | | String city = cityOf(row.getCell(0)); |
|---|
| | | if (city != null) paxRow.put(city, r); |
|---|
| | | } |
|---|
| | | for (Map.Entry<String, Integer> e : paxRow.entrySet()) { |
|---|
| | | String city = e.getKey(); |
|---|
| | | int rp = e.getValue(); |
|---|
| | | int rt = findTurnoverRow(sh, rp + 1, Math.min(end, rp + 6)); |
|---|
| | | if (rt < 0) continue; |
|---|
| | | for (int m = 1; m <= mMax; m++) { |
|---|
| | | Map<String, double[]> mm = monthly.get(m); |
|---|
| | | if (mm == null) continue; |
|---|
| | | double[] arr = mm.get(city); |
|---|
| | | if (arr == null) continue; |
|---|
| | | int col = FIRST_MONTH_COL + 2 * (m - 1); |
|---|
| | | double pax = arr[base]; |
|---|
| | | double turn = arr[base + 1]; |
|---|
| | | if (pax != 0.0) filled += setNumeric(sh, rp, col, pax); |
|---|
| | | if (turn != 0.0) filled += setNumeric(sh, rt, col, turn); |
|---|
| | | } |
|---|
| | | } |
|---|
| | | return filled; |
|---|
| | | } |
|---|
| | | |
|---|
| | | private int findTurnoverRow(Sheet sh, int from, int to) { |
|---|
| | | for (int r = from; r <= to; r++) { |
|---|
| | | Row row = sh.getRow(r); |
|---|
| | | if (row == null) continue; |
|---|
| | | String a = text(row.getCell(0)); |
|---|
| | | if (a != null && !a.trim().isEmpty()) return -1; // è¿å
¥ä¸ä¸å¸å·å |
|---|
| | | String b = text(row.getCell(1)); |
|---|
| | | if (b != null && b.contains("å¨è½¬é")) return r; |
|---|
| | | } |
|---|
| | | return -1; |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** åæ°å¼ï¼æ ¼ä¸åå¨åå»ºæ ¼å¹¶å¤å¶åè¡ 1 æåæ ·å¼ï¼è¿å 1 */ |
|---|
| | | private int setNumeric(Sheet sh, int r, int col, double val) { |
|---|
| | | Row row = sh.getRow(r); |
|---|
| | | if (row == null) row = sh.createRow(r); |
|---|
| | | Cell c = row.getCell(col); |
|---|
| | | if (c == null) { |
|---|
| | | c = row.createCell(col); |
|---|
| | | Cell ref = row.getCell(FIRST_MONTH_COL); |
|---|
| | | if (ref != null && ref.getCellStyle() != null) c.setCellStyle(ref.getCellStyle()); |
|---|
| | | } |
|---|
| | | c.setCellValue(val); |
|---|
| | | return 1; |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** è¡ B åææ¡ â ææ ä½ï¼0 客è¿é / 1 æ
客å¨è½¬é / 2 å
¶ä¸åå¸å
客è¿éæå
¶ä¸ä¸ªä½å®¢è¿é / 3 对åºå¨è½¬é */ |
|---|
| | | private int metricIndexOf(String b) { |
|---|
| | | if (b == null) return -1; |
|---|
| | | if (b.contains("å
¶ä¸ä¸ªä½æ
客å¨è½¬é") || b.contains("å
¶ä¸åå¸å
æ
客å¨è½¬é")) return 3; |
|---|
| | | if (b.contains("å
¶ä¸ä¸ªä½å®¢è¿é") || b.contains("å
¶ä¸åå¸å
客è¿é")) return 2; |
|---|
| | | if (b.contains("æ
客å¨è½¬é")) return 1; |
|---|
| | | if (b.contains("客è¿é")) return 0; |
|---|
| | | return -1; |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** å A ææ¡ â è§èå¸å·åï¼æ¹åç/å
¨çãéå¸å·è¡è¿å nullï¼ */ |
|---|
| | | private String cityOf(Cell a) { |
|---|
| | | String t = text(a); |
|---|
| | | if (t == null || t.trim().isEmpty()) return null; |
|---|
| | | String norm = RegionUtil.normalizeCityName(t.trim()); |
|---|
| | | return RegionUtil.cityList().contains(norm) ? norm : null; |
|---|
| | | } |
|---|
| | | |
|---|
| | | private String findCityByStart(Sheet sh, int startRow) { |
|---|
| | | Row row = sh.getRow(startRow); |
|---|
| | | return row == null ? null : cityOf(row.getCell(0)); |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** æ份容éï¼å¨è¡¨å¤´ï¼å 5 è¡ï¼æ¾â2026â¦ç´¯è®¡âåï¼å®¹é=(累计å-1 æå)/2 */ |
|---|
| | | private int monthColumnCapacity(Sheet sh) { |
|---|
| | | for (int r = 0; r <= 4 && r <= sh.getLastRowNum(); r++) { |
|---|
| | | Row row = sh.getRow(r); |
|---|
| | | if (row == null) continue; |
|---|
| | | for (Cell c : row) { |
|---|
| | | if (c == null) continue; |
|---|
| | | String t = text(c); |
|---|
| | | if (t != null && t.contains("2026") && t.contains("累计") && c.getColumnIndex() > FIRST_MONTH_COL) { |
|---|
| | | return (c.getColumnIndex() - FIRST_MONTH_COL) / 2; |
|---|
| | | } |
|---|
| | | } |
|---|
| | | } |
|---|
| | | return 7; |
|---|
| | | } |
|---|
| | | |
|---|
| | | private String text(Cell c) { |
|---|
| | | if (c == null) return null; |
|---|
| | | switch (c.getCellType()) { |
|---|
| | | case STRING: return c.getStringCellValue(); |
|---|
| | | case NUMERIC: return Double.toString(c.getNumericCellValue()); |
|---|
| | | default: return null; |
|---|
| | | } |
|---|
| | | } |
|---|
| | | } |
|---|
