| | |
|---|
| | | import org.apache.poi.hssf.usermodel.HSSFSheet; |
|---|
| | | import org.apache.poi.hssf.usermodel.HSSFWorkbook; |
|---|
| | | import org.apache.poi.ss.util.CellRangeAddress; |
|---|
| | | import org.apache.poi.openxml4j.util.ZipSecureFile; |
|---|
| | | import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType; |
|---|
| | | import org.springframework.beans.factory.annotation.Value; |
|---|
| | | import org.springframework.stereotype.Service; |
|---|
| | |
|---|
| | | if (template == null) { |
|---|
| | | throw new RuntimeException("未找到汇总表母版《" + year + "年" + month + "月道路运输量汇总表.xlsx》（docs/生成汇总大表）"); |
|---|
| | | } |
|---|
| | | double savedZipRatio = ZipSecureFile.getMinInflateRatio(); |
|---|
| | | ZipSecureFile.setMinInflateRatio(0.0001); // 新版母版 styles.xml 由 openpyxl 高压缩（0.0099<0.01）触发 POI Zip-bomb 误报，读取后还原 |
|---|
| | | try (InputStream in = new FileInputStream(template); |
|---|
| | | XSSFWorkbook wb = new XSSFWorkbook(in)) { |
|---|
| | | File donor = resolveSummaryDonor(year, month); |
|---|
| | |
|---|
| | | if (!wb.getCTWorkbook().isSetCalcPr()) wb.getCTWorkbook().addNewCalcPr(); |
|---|
| | | wb.getCTWorkbook().getCalcPr().setFullCalcOnLoad(true); // 还原/改写公式后打开即重算 |
|---|
| | | return toBytes(wb); |
|---|
| | | } finally { |
|---|
| | | ZipSecureFile.setMinInflateRatio(savedZipRatio); |
|---|
| | | } |
|---|
| | | } |
|---|
| | | |
|---|
| | |
|---|
| | | if (ds == null) continue; |
|---|
| | | Sheet bs = wb.getSheet(ds.getSheetName()); |
|---|
| | | if (bs == null) continue; |
|---|
| | | // 中口径排名页 A..H 陈旧左块已于 2026-09-09 拍板整块删除：该区域永不从备份母版回填，避免“生成又复活” |
|---|
| | | boolean skipMidRankLeftBlock = "中口径排名".equals(ds.getSheetName()); |
|---|
| | | for (int r = ds.getFirstRowNum(); r <= ds.getLastRowNum(); r++) { |
|---|
| | | Row dr = ds.getRow(r); |
|---|
| | | if (dr == null) continue; |
|---|
| | | Row br = bs.getRow(r); |
|---|
| | | for (Cell dc : dr) { |
|---|
| | | if (dc == null) continue; |
|---|
| | | if (skipMidRankLeftBlock && dc.getColumnIndex() < 8) continue; |
|---|
| | | CellType t = dc.getCellType(); |
|---|
| | | if (t == CellType.BLANK) continue; |
|---|
| | | String dtx = summaryCellText(dc); |
|---|
| | |
|---|
| | | Cell tc = (bc != null) ? bc : tr.getCell(dc.getColumnIndex()); |
|---|
| | | if (tc == null) tc = tr.createCell(dc.getColumnIndex()); |
|---|
| | | tc.setBlank(); |
|---|
| | | applyDonorNumberFormat(wb, tc, dc); |
|---|
| | | if (t == CellType.FORMULA) { |
|---|
| | | String f = dc.getCellFormula(); |
|---|
| | | if (f != null) tc.setCellFormula(f); |
|---|
| | |
|---|
| | | return restored; |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** 还原回填时同步 donor（已做版式统一）的数字格式，保证同比/数值/排名格显示格式与母版一致 */ |
|---|
| | | private void applyDonorNumberFormat(XSSFWorkbook wb, Cell dst, Cell src) { |
|---|
| | | if (wb == null || dst == null || src == null) return; |
|---|
| | | try { |
|---|
| | | String fmt = src.getCellStyle().getDataFormatString(); |
|---|
| | | if (fmt == null || fmt.isEmpty() || "General".equalsIgnoreCase(fmt)) return; |
|---|
| | | org.apache.poi.ss.usermodel.DataFormat df = wb.createDataFormat(); |
|---|
| | | short idx = df.getFormat(fmt); |
|---|
| | | CellStyle cur = dst.getCellStyle(); |
|---|
| | | if (cur != null && idx == cur.getDataFormat()) return; |
|---|
| | | CellStyle ns = wb.createCellStyle(); |
|---|
| | | if (cur != null) ns.cloneStyleFrom(cur); |
|---|
| | | ns.setDataFormat(idx); |
|---|
| | | dst.setCellStyle(ns); |
|---|
| | | } catch (Exception ignore) { |
|---|
| | | // 个别格式异常不阻塞还原 |
|---|
| | | } |
|---|
| | | } |
|---|
| | | /** 单元格内容文本化（公式带 = 前缀），用于跨簿比对 */ |
|---|
| | | private String summaryCellText(Cell c) { |
|---|
| | | if (c == null) return null; |
|---|
