| | |
|---|
| | | // 2026-10-13ï¼ç´¯è®¡å / 累计åæ¯åæ¯æ表头é建ï¼æ ¹æ²»æ©åå¨æ©æâ累计æ¼æâçåå²æ¬ è´¦ï¼ |
|---|
| | | int cumRebuilt = summaryWorkbookFiller.repairCumulativeFormulas(wb, year, effMonth); |
|---|
| | | reportProgress(progress, 45, "å·æ°æå页ä¸è·¨é¡µåæ页"); |
|---|
| | | int rankRefreshed = refreshSummaryRankSheets(wb, effPeriod, "month"); |
|---|
| | | int rankRefreshed = refreshSummaryRankSheets(wb, effPeriod, "month", dbProblems); |
|---|
| | | int cumulativeRefreshed = refreshSummaryCumulativeSheets(wb, year, effMonth); // åå¼ è·¨é¡µåæ页éæ¥è¡¨æå·æ° |
|---|
| | | // 2026-10-12ï¼åç¨ãå¥ç¨å¤é¨åç
§ä»¶ãçæ_éè·¯è¿è¾éæ±æ»è¡¨.xlsxãå宽ãââçå¼ä¸å¾ä»¥å½ææ¯ç为åï¼ |
|---|
| | | // é¿å
æ¯çä¸åç
§ä»¶ä¸¤å¤ç»´æ¤ï¼æ¯ç已并å
¥åç
§ä»¶çå宽/éèï¼ã |
|---|
| | |
|---|
| | | * çç»æå·æ°æ°å¼ï¼é¿å
æ±æ»è¡¨éçæå页åçå¨æ§æ份ã |
|---|
| | | * æ°å¼æ ¼æ¬å¼ãå
¬å¼æ ¼æå移平移å
¬å¼ååå
¥ï¼ç®æ æ ¼æ¬èº«å·²æ¯å
¬å¼çï¼SUM/RANK/å æ¯/è¡¥æ°ï¼ä¿çä¸å¨ï¼æå¼æ¶ææ°æ°æ®éç®ã |
|---|
| | | */ |
|---|
| | | private int refreshSummaryRankSheets(XSSFWorkbook wb, String period, String mode) throws Exception { |
|---|
| | | private int refreshSummaryRankSheets(XSSFWorkbook wb, String period, String mode, List<String> problems) throws Exception { |
|---|
| | | int n = 0; |
|---|
| | | n += copyRankValues(wb, "è´§è¿éæå", exportFreightRank(period, mode), new int[][]{{0, 0}, {22, 0}}); |
|---|
| | | n += copyRankValues(wb, "è´§è¿å¨è½¬éæå", exportTurnoverRank(period, mode), new int[][]{{0, 0}, {22, 0}}); |
|---|
| | | n += copyRankValues(wb, "ä¸å£å¾æå", exportPassengerMidRank(period, mode, wb), new int[][]{{0, 0}, {0, 10}}); |
|---|
| | | n += copyRankValues(wb, "è´§è¿éæå", exportFreightRank(period, mode), new int[][]{{0, 0}, {22, 0}}, problems); |
|---|
| | | n += copyRankValues(wb, "è´§è¿å¨è½¬éæå", exportTurnoverRank(period, mode), new int[][]{{0, 0}, {22, 0}}, problems); |
|---|
| | | n += copyRankValues(wb, "ä¸å£å¾æå", exportPassengerMidRank(period, mode, wb), new int[][]{{0, 0}, {0, 10}}, problems); |
|---|
| | | return n; |
|---|
| | | } |
|---|
| | | |
|---|
| | |
|---|
| | | * srcBlocks[i] = æºå i çèµ·å§ {è¡, å}ï¼0 åºï¼ï¼ç®æ åææ é¢é¡ºåºï¼ç´¯è®¡åå¨åãå½æåå¨åï¼å¯¹åºã |
|---|
| | | * æ°å¼æ ¼ç´æ¥æ¬å¼ï¼æºä¾§æ¯å
¬å¼çï¼æå/å æ¯çï¼æç®æ åå移æ´ä½å¹³ç§»ååå
¥å
¬å¼ï¼æå¼æ¶ææ±æ»è¡¨èªèº«æ°æ®éç®ã |
|---|
| | | */ |
|---|
| | | private int copyRankValues(XSSFWorkbook dstWb, String dstSheetName, byte[] srcBytes, int[][] srcBlocks) throws Exception { |
|---|
| | | private int copyRankValues(XSSFWorkbook dstWb, String dstSheetName, byte[] srcBytes, int[][] srcBlocks, |
|---|
| | | List<String> problems) throws Exception { |
|---|
| | | XSSFSheet dst = dstWb.getSheet(dstSheetName); |
|---|
| | | if (dst == null) return 0; |
|---|
| | | List<XSSFCell> dstTitles = rankTitleCells(dst); |
|---|
| | | if (dstTitles.isEmpty()) return 0; |
|---|
| | | int copied = 0; |
|---|
| | | FormulaEvaluator dstEv = dstWb.getCreationHelper().createFormulaEvaluator(); |
|---|
| | | List<int[]> anchors = new ArrayList<>(); // {æºå
¨çè¡, ç®æ å
¨çè¡, åé¦å, åå移, å宽} |
|---|
| | | try (InputStream sin = new ByteArrayInputStream(srcBytes); |
|---|
| | | XSSFWorkbook srcWb = new XSSFWorkbook(sin)) { |
|---|
| | | XSSFSheet src = srcWb.getSheetAt(0); |
|---|
| | |
|---|
| | | int sr0 = srcBlocks[b][0]; |
|---|
| | | int sc0 = srcBlocks[b][1]; |
|---|
| | | XSSFCell dstTitle = dstTitles.get(b); |
|---|
| | | int rowOff = dstTitle.getRowIndex() - sr0; |
|---|
| | | int colOff = dstTitle.getColumnIndex() - sc0; |
|---|
| | | int rowOff = dstTitle.getRowIndex() - sr0; |
|---|
| | | // 2026-10-13ï¼ãè´§è¿å¨è½¬éæåãæ¯çåæ é¢æ¾å 两è¡ï¼å并ï¼ï¼"æ é¢è¡+åºå®å移"ä¼ææºè¡ |
|---|
| | | // æ´ä½åéä½ä¸è¡ï¼å
¨çå¼è½å°è¡¨å¤´è¡ãå¸å·å¼ä¸å¸å·åéå¼ï¼ãæ¹ä¸ºç¨ãå
¨çãè¡åéç¹å¯¹é½ï¼ |
|---|
| | | // rowOff = ç®æ å
¨çè¡ - æºå
¨çè¡ï¼è¯å«ä¸å°éç¹ååéæ é¢è¡å移并åè¦ã |
|---|
| | | Integer srcAnchor = rankAnchorRow(src, sr0, sc0); |
|---|
| | | Integer dstAnchor = rankAnchorRow(dst, dstTitle.getRowIndex(), dstTitle.getColumnIndex()); |
|---|
| | | if (srcAnchor != null && dstAnchor != null) { |
|---|
| | | int anchored = dstAnchor - srcAnchor; |
|---|
| | | if (anchored != rowOff) { |
|---|
| | | log.info("æå页ã{}ã第 {} åï¼æ é¢è¡å移 {} ä¸ãå
¨çãéç¹å移 {} ä¸ä¸è´ï¼æéç¹å¯¹é½ï¼æºå
¨çè¡={}ï¼ç®æ å
¨çè¡={}ï¼", |
|---|
| | | dstSheetName, b + 1, rowOff, anchored, srcAnchor + 1, dstAnchor + 1); |
|---|
| | | } |
|---|
| | | rowOff = anchored; |
|---|
| | | } else { |
|---|
| | | log.warn("æå页ã{}ã第 {} åï¼æªè¯å«å°ãå
¨çãéç¹ï¼æº={}ï¼ç®æ ={}ï¼ï¼åéææ é¢è¡å移 {} 对é½", |
|---|
| | | dstSheetName, b + 1, srcAnchor, dstAnchor, rowOff); |
|---|
| | | } |
|---|
| | | int h = rankBlockHeight(src, sr0, sc0); |
|---|
| | | int w = rankBlockWidth(src, sr0, sc0); |
|---|
| | | anchors.add(new int[]{srcAnchor == null ? -1 : srcAnchor, dstAnchor == null ? -1 : dstAnchor, sc0, colOff, w}); |
|---|
| | | // 2026-10-12ï¼æ´å缺æ°ï¼æºä¾§ä¸ä¸ªæ°å¼é½æ²¡æï¼å¦ 9 æ缺â¡â¢æ¶çå¨è½¬éæåï¼â ç®æ ååªçæ é¢ä¸æ ç¾ï¼ |
|---|
| | | // æ°æ®/æå/å æ¯ä¸å¾ç空ï¼å¦åæ¯çç RANK/å æ¯å
¬å¼ä¼å¨ç©ºåä¸ç®åºãå
¨ 1 + #DIV/0!ãã |
|---|
| | | boolean blockHasData = false; |
|---|
| | |
|---|
| | | } |
|---|
| | | } |
|---|
| | | } |
|---|
| | | // 2026-10-13 èªæ£ï¼ç®æ ãå
¨çãè¡åºä¸æºä¾§ä¸è´ï¼å移éä½ãæ±æ»åºé´ä¸å¯¹æ¶æ¥è¦å¹¶åè¿ç¼ºæ°æ¸
åï¼ |
|---|
| | | for (int[] an : anchors) { |
|---|
| | | if (an[0] < 0 || an[1] < 0) continue; |
|---|
| | | Row srr = src.getRow(an[0]); |
|---|
| | | Row drr = dst.getRow(an[1]); |
|---|
| | | if (srr == null || drr == null) continue; |
|---|
| | | int badCols = 0; |
|---|
| | | StringBuilder ex = new StringBuilder(); |
|---|
| | | for (int c = 0; c < an[4]; c++) { |
|---|
| | | Cell sCell = srr.getCell(an[2] + c); |
|---|
| | | Cell dCell = drr.getCell(an[2] + c + an[3]); |
|---|
| | | Double sv = sCell == null ? null : rankNumeric(sCell, ev); |
|---|
| | | Double dv = dCell == null ? null : rankNumeric(dCell, dstEv); |
|---|
| | | if (sv == null && dv == null) continue; |
|---|
| | | if (sv == null || dv == null || Math.abs(sv - dv) > Math.max(1.0, Math.abs(sv) * 1e-4)) { |
|---|
| | | badCols++; |
|---|
| | | if (badCols <= 3) { |
|---|
| | | if (ex.length() > 0) ex.append("ï¼"); |
|---|
| | | ex.append(colLetter(an[2] + c)).append(" æº=").append(sv).append(" ç®æ =").append(dv); |
|---|
| | | } |
|---|
| | | } |
|---|
| | | } |
|---|
| | | if (badCols >= 2) { |
|---|
| | | String msg = "æå页ã" + dstSheetName + "ããå
¨çãè¡ä¸æºä¾§ä¸ä¸è´ï¼" + badCols + " åï¼ï¼" + ex; |
|---|
| | | log.warn(msg); |
|---|
| | | if (problems != null) problems.add("çææ ¡éªï¼" + msg); |
|---|
| | | } |
|---|
| | | } |
|---|
| | | } |
|---|
| | | return copied; |
|---|
| | | } |
|---|
| | |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** æå页éçâåæ é¢âåå
æ ¼ï¼å«âå
¨çåå¸å·âç说ææåï¼ï¼æè¡ãå顺åºè¿å */ |
|---|
| | | /** å¨ [fromRow, fromRow+40) èå´å
ãåèµ·å§ä¸¤åéæ¾æ´æ ¼ææ¬ä¸ºãå
¨çãçè¡å·ï¼0 åºï¼ï¼æ¾ä¸å°è¿å nullã |
|---|
| | | * 2026-10-13ï¼æå页åç»æå¯è½å æ¯ççå¼ä¸åèæ´ä½ä¸/ä¸ç§»ä¸è¡ï¼ç¨ãå
¨çãè¡åéç¹å¯èªå¨å¯¹é½ã */ |
|---|
| | | private Integer rankAnchorRow(Sheet sh, int fromRow, int col0) { |
|---|
| | | int last = Math.min(sh.getLastRowNum(), fromRow + 40); |
|---|
| | | for (int r = fromRow; r <= last; r++) { |
|---|
| | | Row row = sh.getRow(r); |
|---|
| | | if (row == null) continue; |
|---|
| | | for (int c = Math.max(0, col0); c <= col0 + 1; c++) { |
|---|
| | | Cell cell = row.getCell(c); |
|---|
| | | if (cell == null || cell.getCellType() != CellType.STRING) continue; |
|---|
| | | String v = cell.getStringCellValue(); |
|---|
| | | if (v != null && v.replace(" ", "").equals("å
¨ç")) return r; |
|---|
| | | } |
|---|
| | | } |
|---|
| | | return null; |
|---|
| | | } |
|---|
| | | |
|---|
| | | private List<XSSFCell> rankTitleCells(Sheet sh) { |
|---|
| | | List<XSSFCell> out = new ArrayList<>(); |
|---|
| | | for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) { |
|---|
