package com.trafficaudit.reportexport.layout;
|
|
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.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.stereotype.Component;
|
|
import java.io.ByteArrayInputStream;
|
import java.io.ByteArrayOutputStream;
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.GregorianCalendar;
|
import java.util.HashSet;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Set;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
/**
|
* 汇总大表「按月扩列器」（Step 1，2026-10-09 首次实现）。
|
*
|
* <p>把 N 月母版推进到 N+1 月版式（不预留空列）：在「累计列」前插入 2 列（当月值列 + 同比列），
|
* 克隆两行纵向合并标题、重写累计枚举与累计同比分母、右移 2025/2024/2023 历史块及其公式引用，
|
* 并同步平移「其它页指向已扩列页、列号 >= 插入列」的跨页引用。
|
*
|
* <p>规则依据：{@code docs/汇总大表母版扩列配置_核对版_2026-10-09.md}（8 个可见台账页；
|
* 4 个隐藏页保留隐藏、不扩列，但其跨页引用需同步平移）。只做版式，当月数据列留空。
|
*/
|
@Slf4j
|
@Component
|
public class SummaryLayoutExpander {
|
|
private static final List<String> EXPAND_PAGES = Arrays.asList(
|
" 货运", "班线包车", "城市客运", "公交", "出租", "网约车", "轨道、轮渡", "中口径明细");
|
|
private static final int HEADER_SCAN_ROWS = 15;
|
|
/** 公式里的 A1 引用（可带工作表前缀）。 */
|
private static final Pattern REF = Pattern.compile(
|
"(?:('([^']+)'|([A-Za-z_\\u4e00-\\u9fff][A-Za-z0-9_.\\u4e00-\\u9fff\u3001]*))!)?(\\$?)([A-Z]{1,3})(\\$?)(\\d+)");
|
|
private static final Pattern MONTH_TEXT = Pattern.compile("^(\\d{4})\\s*年\\s*(\\d{1,2})\\s*月$");
|
|
public byte[] expand(byte[] source, int year, int targetMonth) {
|
if (targetMonth < 2 || targetMonth > 12) {
|
throw new IllegalArgumentException("扩列：目标月份需在 2~12 之间");
|
}
|
try (XSSFWorkbook wb = new XSSFWorkbook(new ByteArrayInputStream(source))) {
|
Set<String> expanded = new HashSet<>(EXPAND_PAGES);
|
Map<String, Integer> insertColOf = new LinkedHashMap<>();
|
for (String name : EXPAND_PAGES) {
|
Sheet sh = wb.getSheet(name);
|
if (sh == null) {
|
log.warn("扩列：找不到页签 {}", name);
|
continue;
|
}
|
insertColOf.put(name, expandSheet(sh, year, targetMonth));
|
}
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
Sheet sh = wb.getSheetAt(i);
|
if (sh == null || expanded.contains(sh.getSheetName())) continue;
|
shiftQualifiedRefs(sh, insertColOf);
|
}
|
wb.setForceFormulaRecalculation(true);
|
ByteArrayOutputStream out = new ByteArrayOutputStream();
|
wb.write(out);
|
return out.toByteArray();
|
} catch (Exception e) {
|
throw new IllegalStateException("汇总大表扩列失败: " + e.getMessage(), e);
|
}
|
}
|
|
/** 对单页扩列，返回插入位置（0 基列号）。 */
|
private int expandSheet(Sheet sh, int year, int targetMonth) {
|
int lastMonth = targetMonth - 1;
|
Map<Integer, Integer> cur = monthCols(sh, year);
|
Integer lastVal = cur.get(lastMonth);
|
if (lastVal == null) {
|
throw new IllegalStateException("扩列：" + sh.getSheetName() + " 页未按表头识别到 " + year + "年" + lastMonth + "月列");
|
}
|
Map<Integer, Integer> last = monthCols(sh, year - 1);
|
Integer lastYearSameMonth = last.get(targetMonth);
|
if (lastYearSameMonth == null) {
|
throw new IllegalStateException("扩列：" + sh.getSheetName() + " 页未识别到 " + (year - 1) + "年" + targetMonth + "月列（累计同比分母）");
|
}
|
List<Integer> headerRows = headerRowsOf(sh);
|
int insertCol = lastVal + 2; // 累计列位置（8 月值列 Q=16 → 累计 S=18）
|
int srcVal = lastVal, srcYoy = lastVal + 1;
|
int lastRow = sh.getLastRowNum();
|
|
// 1) 区域右移 2 列（自右向左搬格，保留值/公式/样式）
|
for (int r = 0; r <= lastRow; r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
int lastCell = row.getLastCellNum() - 1;
|
for (int c = lastCell; c >= insertCol; c--) {
|
Cell src = row.getCell(c);
|
if (src == null) continue;
|
copyCell(src, getOrCreate(row, c + 2));
|
src.setBlank();
|
}
|
}
|
// 2) 合并区右移（跨插入点的整体加宽）：先按下标摘除全部合并区，再按调整后的范围重新添加
|
List<CellRangeAddress> merges = new ArrayList<>(sh.getMergedRegions());
|
for (int i = merges.size() - 1; i >= 0; i--) {
|
sh.removeMergedRegion(i);
|
}
|
for (CellRangeAddress mr : merges) {
|
CellRangeAddress moved = mr;
|
if (mr.getFirstColumn() >= insertCol) {
|
moved = new CellRangeAddress(mr.getFirstRow(), mr.getLastRow(),
|
mr.getFirstColumn() + 2, mr.getLastColumn() + 2);
|
} else if (mr.getLastColumn() >= insertCol) {
|
moved = new CellRangeAddress(mr.getFirstRow(), mr.getLastRow(),
|
mr.getFirstColumn(), mr.getLastColumn() + 2);
|
}
|
sh.addMergedRegion(moved);
|
}
|
// 3) 本页公式引用整体 +2（指向已扩列页的引用同样 +2；指向未扩列页的不动）
|
Set<String> shiftable = new HashSet<>(EXPAND_PAGES);
|
shiftAllRefs(sh, insertCol, shiftable);
|
|
// 4) 新列：列宽/隐藏照抄上月；表头（每个块的表头行）克隆值+格式+合并区；数据区只留样式与公式
|
int w = sh.getColumnWidth(srcVal);
|
if (w > 0) sh.setColumnWidth(insertCol, w);
|
int w2 = sh.getColumnWidth(srcYoy);
|
if (w2 > 0) sh.setColumnWidth(insertCol + 1, w2);
|
sh.setColumnHidden(insertCol, sh.isColumnHidden(srcVal));
|
sh.setColumnHidden(insertCol + 1, sh.isColumnHidden(srcYoy));
|
for (int h : headerRows) {
|
cloneHeaderCell(sh, srcVal, insertCol, h, year, targetMonth);
|
cloneHeaderCell(sh, srcYoy, insertCol + 1, h, year, targetMonth);
|
cloneHeaderMerge(sh, srcVal, insertCol, h);
|
cloneHeaderMerge(sh, srcYoy, insertCol + 1, h);
|
}
|
for (int r = 0; r <= lastRow; r++) {
|
if (headerRows.contains(r)) continue;
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
cloneDataCell(row, srcVal, insertCol, shiftable, insertCol);
|
cloneDataCell(row, srcYoy, insertCol + 1, shiftable, insertCol);
|
}
|
// 5) 累计列补当月项；累计同比分母补去年同月列
|
int cumCol = insertCol + 2, cumYoyCol = insertCol + 3;
|
int denCol = lastYearSameMonth + 2;
|
String valLetter = colLetter(insertCol);
|
String denLetter = colLetter(denCol);
|
int cumFixed = 0, cumYoyFixed = 0;
|
for (int r = 0; r <= lastRow; r++) {
|
if (headerRows.contains(r)) continue;
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
Cell cum = row.getCell(cumCol);
|
if (cum != null && cum.getCellType() == CellType.FORMULA) {
|
String f = cum.getCellFormula();
|
if (!f.contains(valLetter + (r + 1))) {
|
cum.setCellFormula(f + "+" + valLetter + (r + 1));
|
cumFixed++;
|
}
|
}
|
Cell cumYoy = row.getCell(cumYoyCol);
|
if (cumYoy != null && cumYoy.getCellType() == CellType.FORMULA) {
|
String f = cumYoy.getCellFormula();
|
if (f.contains(denLetter + (r + 1))) continue;
|
int close = f.lastIndexOf(')');
|
if (close > 0) {
|
String inner = f.substring(0, close);
|
String sep = inner.contains(",") ? "," : "+";
|
cumYoy.setCellFormula(inner + sep + denLetter + (r + 1) + f.substring(close));
|
cumYoyFixed++;
|
} else {
|
log.warn("扩列：{} 第 {} 行累计同比公式形态未识别，未补去年同月列：{}", sh.getSheetName(), r + 1, f);
|
}
|
}
|
}
|
log.info("扩列：{} 页完成——值列 {}→{}，同比列 {}→{}，累计列 {}，累计同比列 {}，去年同月列 {}；表头行 {}，末行 {}；累计补 {} 格、累计同比补 {} 格",
|
sh.getSheetName(), colLetter(srcVal), colLetter(insertCol), colLetter(srcYoy), colLetter(insertCol + 1),
|
colLetter(cumCol), colLetter(cumYoyCol), denLetter, headerRows, lastRow + 1, cumFixed, cumYoyFixed);
|
return insertCol;
|
}
|
|
/** 表头行（0 基）：含「年+月」表头单元格的行；轨道、轮渡有两个块 → 两行。 */
|
private List<Integer> headerRowsOf(Sheet sh) {
|
List<Integer> rows = new ArrayList<>();
|
int lastRow = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + HEADER_SCAN_ROWS);
|
for (int r = sh.getFirstRowNum(); r <= lastRow; r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
|
if (yearMonthOf(row.getCell(c)) != null) {
|
rows.add(r);
|
break;
|
}
|
}
|
}
|
return rows;
|
}
|
|
private Map<Integer, Integer> monthCols(Sheet sh, int year) {
|
Map<Integer, Integer> cols = new LinkedHashMap<>();
|
int lastRow = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + HEADER_SCAN_ROWS);
|
for (int r = sh.getFirstRowNum(); r <= lastRow; r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
|
Integer ym = yearMonthOf(row.getCell(c));
|
if (ym == null || ym / 100 != year) continue;
|
int mo = ym % 100;
|
if (!cols.containsKey(mo)) cols.put(mo, c);
|
}
|
}
|
return cols;
|
}
|
|
private Integer yearMonthOf(Cell c) {
|
if (c == null) return null;
|
try {
|
CellType t = c.getCellType();
|
if (t == CellType.STRING) {
|
String v = c.getStringCellValue();
|
if (v == null) return null;
|
v = v.trim();
|
if (v.isEmpty() || v.contains("累计") || v.contains("同比") || v.contains("环比")) return null;
|
Matcher m = MONTH_TEXT.matcher(v);
|
if (!m.matches()) return null;
|
int mo = Integer.parseInt(m.group(2));
|
return mo >= 1 && mo <= 12 ? Integer.parseInt(m.group(1)) * 100 + mo : null;
|
}
|
if (t == CellType.NUMERIC || t == CellType.FORMULA) {
|
double v;
|
if (t == CellType.NUMERIC) {
|
v = c.getNumericCellValue();
|
} else {
|
if (c.getCachedFormulaResultType() != CellType.NUMERIC) return null;
|
v = c.getNumericCellValue();
|
}
|
if (v < 36526 || v > 73415) return null;
|
java.util.Calendar cal = java.util.Calendar.getInstance();
|
cal.setTime(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v));
|
if (cal.get(java.util.Calendar.DAY_OF_MONTH) != 1) return null;
|
return cal.get(java.util.Calendar.YEAR) * 100 + (cal.get(java.util.Calendar.MONTH) + 1);
|
}
|
} catch (Exception ignore) {
|
return null;
|
}
|
return null;
|
}
|
|
private void cloneHeaderCell(Sheet sh, int srcCol, int dstCol, int rowIdx, int year, int targetMonth) {
|
Row row = sh.getRow(rowIdx);
|
if (row == null) return;
|
Cell s = row.getCell(srcCol);
|
if (s == null) return;
|
Cell d = getOrCreate(row, dstCol);
|
d.setBlank();
|
d.setCellStyle(s.getCellStyle());
|
if (s.getCellType() == CellType.STRING) {
|
String text = s.getStringCellValue();
|
if (text != null && text.contains("同比")) {
|
d.setCellValue(text.replaceAll("^\\d{1,2}月", targetMonth + "月"));
|
} else if (text != null && MONTH_TEXT.matcher(text.trim()).matches()) {
|
d.setCellValue(year + "年" + targetMonth + "月");
|
} else {
|
d.setCellValue(text);
|
}
|
} else if (s.getCellType() == CellType.NUMERIC || s.getCellType() == CellType.FORMULA) {
|
d.setCellValue(new GregorianCalendar(year, targetMonth - 1, 1).getTime());
|
}
|
}
|
|
private void cloneHeaderMerge(Sheet sh, int srcCol, int dstCol, int headerRow) {
|
for (CellRangeAddress mr : new ArrayList<>(sh.getMergedRegions())) {
|
if (mr.getFirstColumn() == srcCol && mr.getLastColumn() == srcCol && mr.getFirstRow() == headerRow) {
|
sh.addMergedRegion(new CellRangeAddress(mr.getFirstRow(), mr.getLastRow(), dstCol, dstCol));
|
return;
|
}
|
}
|
}
|
|
private void cloneDataCell(Row row, int srcCol, int dstCol, Set<String> shiftable, int insertCol) {
|
Cell s = row.getCell(srcCol);
|
if (s == null) return;
|
Cell d = getOrCreate(row, dstCol);
|
d.setBlank();
|
d.setCellStyle(s.getCellStyle());
|
if (s.getCellType() == CellType.FORMULA) {
|
d.setCellFormula(shiftRefs(s.getCellFormula(), insertCol, shiftable));
|
}
|
}
|
|
private void shiftAllRefs(Sheet sh, int insertCol, Set<String> shiftable) {
|
for (int r = 0; r <= sh.getLastRowNum(); r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
for (int c = 0; c < row.getLastCellNum(); c++) {
|
Cell cell = row.getCell(c);
|
if (cell == null || cell.getCellType() != CellType.FORMULA) continue;
|
String f = cell.getCellFormula();
|
String nf = shiftRefs(f, insertCol, shiftable);
|
if (!nf.equals(f)) cell.setCellFormula(nf);
|
}
|
}
|
}
|
|
/** 只平移「显式带工作表前缀、该页已扩列、且列号 >= 该页插入列」的引用（用于未扩列的页）。 */
|
private void shiftQualifiedRefs(Sheet sh, Map<String, Integer> insertColOf) {
|
for (int r = 0; r <= sh.getLastRowNum(); r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
for (int c = 0; c < row.getLastCellNum(); c++) {
|
Cell cell = row.getCell(c);
|
if (cell == null || cell.getCellType() != CellType.FORMULA) continue;
|
String f = cell.getCellFormula();
|
String nf = shiftQualified(f, insertColOf);
|
if (!nf.equals(f)) cell.setCellFormula(nf);
|
}
|
}
|
}
|
|
private String shiftQualified(String formula, Map<String, Integer> insertColOf) {
|
Matcher m = REF.matcher(formula);
|
StringBuffer sb = new StringBuffer();
|
while (m.find()) {
|
String sheet = m.group(2) != null ? m.group(2) : m.group(3);
|
Integer ins = sheet == null ? null : insertColOf.get(sheet);
|
if (sheet == null || ins == null || isFunctionName(formula, m)) {
|
m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
|
continue;
|
}
|
int colIdx = colIndex(m.group(5));
|
if (colIdx >= ins) {
|
m.appendReplacement(sb, Matcher.quoteReplacement(rebuild(m, sheet, colIdx + 2)));
|
} else {
|
m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
|
}
|
}
|
m.appendTail(sb);
|
return sb.toString();
|
}
|
|
/** 平移公式中所有「列号 >= insertCol」的引用（sheet 为 null 或属于 shiftable 时生效）。 */
|
private String shiftRefs(String formula, int insertCol, Set<String> shiftable) {
|
Matcher m = REF.matcher(formula);
|
StringBuffer sb = new StringBuffer();
|
while (m.find()) {
|
String sheet = m.group(2) != null ? m.group(2) : m.group(3);
|
if ((sheet != null && !shiftable.contains(sheet)) || isFunctionName(formula, m)) {
|
m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
|
continue;
|
}
|
int colIdx = colIndex(m.group(5));
|
if (colIdx >= insertCol) {
|
m.appendReplacement(sb, Matcher.quoteReplacement(rebuild(m, sheet, colIdx + 2)));
|
} else {
|
m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
|
}
|
}
|
m.appendTail(sb);
|
return sb.toString();
|
}
|
|
private boolean isFunctionName(String formula, Matcher m) {
|
return m.group(1) == null && formula.indexOf('(', m.end()) == m.end();
|
}
|
|
private String rebuild(Matcher m, String sheet, int newColIdx0) {
|
String prefix = sheet == null ? ""
|
: (m.group(1).startsWith("'") ? "'" + sheet + "'" : sheet) + "!";
|
return prefix + m.group(4) + colLetter(newColIdx0) + m.group(6) + m.group(7);
|
}
|
|
private void copyCell(Cell src, Cell dst) {
|
dst.setCellStyle(src.getCellStyle());
|
CellType t = src.getCellType();
|
if (t == CellType.FORMULA) {
|
dst.setCellFormula(src.getCellFormula());
|
} else if (t == CellType.NUMERIC) {
|
dst.setCellValue(src.getNumericCellValue());
|
} else if (t == CellType.STRING) {
|
dst.setCellValue(src.getStringCellValue());
|
} else if (t == CellType.BOOLEAN) {
|
dst.setCellValue(src.getBooleanCellValue());
|
} else {
|
dst.setBlank();
|
}
|
}
|
|
private Cell getOrCreate(Row row, int col) {
|
Cell c = row.getCell(col);
|
return c == null ? row.createCell(col) : c;
|
}
|
|
private static int colIndex(String letters) {
|
int n = 0;
|
for (int i = 0; i < letters.length(); i++) n = n * 26 + (letters.charAt(i) - 'A' + 1);
|
return n - 1;
|
}
|
|
private static String colLetter(int idx0) {
|
StringBuilder sb = new StringBuilder();
|
int n = idx0 + 1;
|
while (n > 0) {
|
int rem = (n - 1) % 26;
|
sb.insert(0, (char) ('A' + rem));
|
n = (n - 1) / 26;
|
}
|
return sb.toString();
|
}
|
}
|
