# -*- coding: utf-8 -*-
|
"""货运月报「持续在规企业」分市州 1-8 月累计运距及同比 台账生成（v2：同比基期改为官方全口径）
|
|
数据源：traffic_audit.h2032_enterprise_monthly（《道路货物运输月度生产情况》H203-2 企业月报）
|
导出件 _tmp_persist_scope.tsv（企业级累计）、_tmp_persist_scope_monthly.tsv（企业级分月）
|
口径（v2 修订）：
|
· 持续在规企业 = 2026 年 1-8 月连续 8 个月均上报月报的企业（636 家，用户 2026-09-18 确认）；
|
· 累计平均运距（公里）= 累计货物周转量 ÷ 累计货运量（加权平均）；
|
· 同比 = 2026 年 1-8 月累计值 ÷ 2025 年 1-8 月累计值 − 1，**基期为 2025 年 1-8 月全部规上企业上报数**
|
（全省 664 家），与汇总表「货运」页 T 列 `=S/SUM(V7,X7,…,AJ7)-1` 同一口径；
|
v1 误用「两年均持续在规的 545 家」作基期，导致同比偏小，已修正。
|
"""
|
import io, os
|
from openpyxl import Workbook
|
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
|
from openpyxl.utils import get_column_letter
|
|
SRC = r'D:\文档\ChatGPT\traffic-audit\_tmp_persist_scope.tsv'
|
MONTHLY_SRC = r'D:\文档\ChatGPT\traffic-audit\_tmp_persist_scope_monthly.tsv'
|
OUT_DIR = r'D:\文档\ChatGPT\traffic-audit\outputs'
|
OUT = os.path.join(OUT_DIR, '货运月报_持续在规企业_分市州1-8月累计运距及同比_2026-09-18.xlsx')
|
|
PROV = {
|
'vol26': 31399.82, 'turn26': 4927161.77, # 已发布：2026 年 1-8 月累计
|
'vol25': 33994.45, 'turn25': 5102518.95, # 汇总表「货运」页 2025 年 1-8 月（同比基期）
|
'vol26_08': 3665.36, 'turn26_08': 570004.44, # 已发布：2026 年 8 月当月
|
'yoy_vol': -0.0763, 'yoy_turn': -0.0344, # 已发布：1-8 月累计同比
|
}
|
|
CITY_ORDER = ['武汉市', '黄石市', '十堰市', '宜昌市', '襄阳市', '鄂州市', '荆门市', '孝感市', '荆州市',
|
'黄冈市', '咸宁市', '随州市', '恩施州', '仙桃市', '潜江市', '天门市', '神农架林区']
|
CITY = {'4201': '武汉市', '4202': '黄石市', '4203': '十堰市', '4205': '宜昌市', '4206': '襄阳市',
|
'4207': '鄂州市', '4208': '荆门市', '4209': '孝感市', '4210': '荆州市', '4211': '黄冈市',
|
'4212': '咸宁市', '4213': '随州市', '4228': '恩施州',
|
'429004': '仙桃市', '429005': '潜江市', '429006': '天门市', '429021': '神农架林区'}
|
MONTHS26 = ['2026-%02d' % m for m in range(1, 9)]
|
|
|
def city_of(rc):
|
rc = (rc or '').strip()
|
return CITY[rc] if rc in CITY else CITY.get(rc[:4], '未识别(%s)' % rc)
|
|
|
def num(x):
|
try:
|
return float(x)
|
except Exception:
|
return 0.0
|
|
|
def dist(f, t):
|
return (t / f) if f else None
|
|
|
def growth(now, base):
|
return (now / base - 1) if (now is not None and base) else None
|
|
|
# ---------- 读企业级累计数据 ----------
|
rows = []
|
with io.open(SRC, 'r', encoding='utf-8') as f:
|
hdr = f.readline().rstrip('\r\n').split('\t')
|
for line in f:
|
p = line.rstrip('\r\n').split('\t')
|
if len(p) < len(hdr):
|
continue
|
d = dict(zip(hdr, p))
|
if int(num(d['m26'])) != 8:
|
continue
|
rows.append(dict(city=city_of(d['rc']), rc=d['rc'], en=d['en'], ec=d['ec'], ucc=d['ucc'],
|
f26=num(d['f26']), t26=num(d['t26']),
|
f25=num(d['f25']), t25=num(d['t25']), cmp=(int(num(d['m25'])) == 8)))
|
|
by_city = {}
|
for r in rows:
|
c = by_city.setdefault(r['city'], dict(n=0, f26=0.0, t26=0.0))
|
c['n'] += 1
|
c['f26'] += r['f26']; c['t26'] += r['t26']
|
|
tot = dict(n=0, f26=0.0, t26=0.0)
|
for c in by_city.values():
|
for k in tot:
|
tot[k] += c[k]
|
by_city_cmp = {}
|
for r in rows:
|
if not r['cmp']:
|
continue
|
c = by_city_cmp.setdefault(r['city'], dict(n=0, f26c=0.0, t26c=0.0, f25c=0.0, t25c=0.0))
|
c['n'] += 1
|
c['f26c'] += r['f26']; c['t26c'] += r['t26']
|
c['f25c'] += r['f25']; c['t25c'] += r['t25']
|
tot_cmp = dict(n=0, f26c=0.0, t26c=0.0, f25c=0.0, t25c=0.0)
|
for c in by_city_cmp.values():
|
for k in tot_cmp:
|
tot_cmp[k] += c[k]
|
unknown = sorted(k for k in by_city if k.startswith('未识别'))
|
persist_names = set(r['en'] for r in rows)
|
|
# ---------- 读企业级分月数据 ----------
|
cust = {}
|
with io.open(MONTHLY_SRC, 'r', encoding='utf-8') as f:
|
for line in f:
|
p = line.rstrip('\r\n').split('\t')
|
if len(p) < 5 or p[0] == 'report_period':
|
continue
|
per, rc, en, fr, tu = p[0], p[1], p[2], p[3], p[4]
|
cust.setdefault((per, city_of(rc)), {})[en] = (num(fr), num(tu))
|
|
|
def city_month(period, city, names=None):
|
d = cust.get((period, city))
|
if not d:
|
return None, 0.0, 0.0
|
fr = tu = 0.0
|
for en, v in d.items():
|
if names is None or en in names:
|
fr += v[0]; tu += v[1]
|
return dist(fr, tu), fr, tu
|
|
|
# ---------- 2025 年 1-8 月全口径基期 ----------
|
B25 = {}
|
for (per, city), d in cust.items():
|
if not ('2025-01' <= per <= '2025-08'):
|
continue
|
c = B25.setdefault(city, dict(ents=set(), f=0.0, t=0.0))
|
for en, v in d.items():
|
c['ents'].add(en)
|
c['f'] += v[0]; c['t'] += v[1]
|
tot25 = dict(ents=set(), f=0.0, t=0.0)
|
for c in B25.values():
|
tot25['ents'] |= c['ents']
|
tot25['f'] += c['f']; tot25['t'] += c['t']
|
|
# ---------- 样式 ----------
|
THIN = Side(style='thin', color='BFBFBF')
|
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
|
HDR_FILL = PatternFill('solid', fgColor='DDEBF7')
|
TOT_FILL = PatternFill('solid', fgColor='FFF2CC')
|
TITLE_FILL = PatternFill('solid', fgColor='1F4E79')
|
CT = Alignment(horizontal='center', vertical='center', wrap_text=True)
|
LT = Alignment(horizontal='left', vertical='center', wrap_text=True)
|
RT = Alignment(horizontal='right', vertical='center')
|
NF2, NF1, NFP = '#,##0.00', '#,##0.0', '0.00%'
|
|
wb = Workbook()
|
|
# ---------------- Sheet1 说明与口径 ----------------
|
ws = wb.active
|
ws.title = '说明与口径'
|
ws.sheet_view.showGridLines = False
|
ws.column_dimensions['A'].width = 16
|
ws.column_dimensions['B'].width = 106
|
t = ws.cell(1, 1, '货运月报「持续在规企业」分市州 1-8 月累计运距及同比情况表')
|
t.font = Font(bold=True, size=15, color='FFFFFF')
|
t.fill = TITLE_FILL
|
t.alignment = Alignment(horizontal='center', vertical='center')
|
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=2)
|
ws.row_dimensions[1].height = 30
|
|
info = [
|
('数据来源', '系统数据库 traffic_audit.h2032_enterprise_monthly《道路货物运输月度生产情况》（H203-2）企业月报，'
|
'取 2025 年 1 月—2026 年 8 月全部企业级上报记录。'),
|
('持续在规企业', '2026 年 1—8 月连续 8 个月均上报月报的企业，共 %d 家（2026 年各月上报企业数均为 636 家，全期一致；'
|
'口径经用户 2026-09-18 确认）。' % tot['n']),
|
('累计运距口径', '累计平均运距（公里）= 1—8 月累计货物周转量 ÷ 1—8 月累计货运量（加权平均）。源表单位为吨、吨公里，'
|
'本表折算为万吨、万吨公里，比值不变。'),
|
('同比口径', '同比 = 2026 年 1—8 月累计值 ÷ 2025 年 1—8 月累计值 − 1。**基期为 2025 年 1—8 月全部规上企业上报数**'
|
'（全省 %d 家），与汇总表「货运」页 T 列公式 =S/SUM(V7,X7,…,AJ7)-1 同一口径，可与发布报告直接对齐：'
|
'全省累计货运量同比 −7.63%%、周转量同比 −3.44%%、平均运距同比 +4.54%%。' % len(tot25['ents'])),
|
('全省合计', '一律为 17 个市州之和（不含跨市州重叠口径）。'),
|
('报表期', '本期累计 2026-01-01—2026-08-31；同比基期 2025-01-01—2025-08-31。'),
|
('页签说明', '「分市州汇总」按市州汇总 1-8 月累计运距与同比；「分市州分月」为各市州 2026 年逐月平均运距；'
|
'「企业明细」为 636 家持续在规企业逐户数据（企业级同比仅对两年均连续上报的 545 家给出）；'
|
'「口径对比」并列官方口径与同企业口径的同比；「口径校验」为明细与汇总对账，以及与汇总表、已发布报告的比对。'),
|
('注意事项', '① 有 5 家企业 1—8 月月报货运量、周转量均为 0，计入企业数但不影响运距；'
|
'② 武汉石化交通运输有限公司、湖北捷龙交通运业有限公司 2026 年 1 月的地区码与其后月份不同（420100 → 420107 / 420103），'
|
'但同属武汉市，不影响市州归集；③ 累计货运量为 0 的企业累计运距留空。'),
|
('版本', 'v2（2026-09-18）：修正同比基期口径——v1 误用「两年均持续在规的 545 家」作基期，全省累计运距同比算得 +2.90%%，'
|
'官方口径应为 +4.54%%。'),
|
('生成时间', '2026-09-18'),
|
]
|
r = 3
|
for k, v in info:
|
a = ws.cell(r, 1, k); a.font = Font(bold=True); a.alignment = LT; a.border = BORDER
|
b = ws.cell(r, 2, v); b.alignment = LT; b.border = BORDER
|
r += 1
|
|
# ---------------- Sheet2 分市州汇总 ----------------
|
ws2 = wb.create_sheet('分市州汇总')
|
heads = ['序号', '市州', '2026年1-8月持续在规企业数(家)', '2025年1-8月规上企业数(家)',
|
'2026年1-8月累计货运量(万吨)', '2026年1-8月累计货物周转量(万吨公里)', '2026年1-8月累计平均运距(公里)',
|
'2025年1-8月累计货运量(万吨)', '2025年1-8月累计货物周转量(万吨公里)', '2025年1-8月累计平均运距(公里)',
|
'累计平均运距同比(%)', '累计货运量同比(%)', '累计货物周转量同比(%)']
|
widths = [6, 14, 20, 18, 20, 24, 20, 20, 24, 20, 15, 15, 17]
|
ws2.cell(1, 1, '货运月报持续在规企业分市州 1-8 月累计平均运距及同比情况（同比基期：2025 年 1-8 月全部规上企业）').font = Font(bold=True, size=13, color='FFFFFF')
|
ws2.cell(1, 1).fill = TITLE_FILL
|
ws2.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
|
ws2.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(heads))
|
ws2.row_dimensions[1].height = 26
|
ws2.cell(2, 1, '注：第 5—7 列为 2026 年持续在规企业（636 家）累计值；第 8—10 列为 2025 年 1-8 月全部规上企业（全省 664 家）累计值，'
|
'即汇总表「货运」页 T 列同比公式的基期；第 11—13 列同比口径与发布报告一致。')
|
ws2.cell(2, 1).font = Font(size=9, italic=True, color='808080')
|
ws2.merge_cells(start_row=2, start_column=1, end_row=2, end_column=len(heads))
|
for j, h in enumerate(heads, 1):
|
c = ws2.cell(3, j, h)
|
c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
|
ws2.column_dimensions[get_column_letter(j)].width = widths[j - 1]
|
ws2.row_dimensions[3].height = 46
|
|
rr = 3
|
idx = 0
|
for city in CITY_ORDER:
|
c = by_city.get(city)
|
if not c or c['n'] == 0:
|
continue
|
idx += 1
|
rr += 1
|
b = B25.get(city, dict(ents=set(), f=0.0, t=0.0))
|
d26, d25 = dist(c['f26'], c['t26']), dist(b['f'], b['t'])
|
vals = [idx, city, c['n'], len(b['ents']),
|
c['f26'] / 10000.0, c['t26'] / 10000.0, d26,
|
b['f'] / 10000.0, b['t'] / 10000.0, d25,
|
growth(d26, d25), growth(c['f26'], b['f']), growth(c['t26'], b['t'])]
|
for j, v in enumerate(vals, 1):
|
cell = ws2.cell(rr, j, v)
|
cell.border = BORDER
|
cell.alignment = CT if j <= 4 else RT
|
if j in (5, 6, 7, 8, 9, 10):
|
cell.number_format = NF2
|
elif j in (11, 12, 13):
|
cell.number_format = NFP
|
rr += 1
|
d26p, d25p = dist(tot['f26'], tot['t26']), dist(tot25['f'], tot25['t'])
|
vals = ['', '全省合计', tot['n'], len(tot25['ents']),
|
tot['f26'] / 10000.0, tot['t26'] / 10000.0, d26p,
|
tot25['f'] / 10000.0, tot25['t'] / 10000.0, d25p,
|
growth(d26p, d25p), growth(tot['f26'], tot25['f']), growth(tot['t26'], tot25['t'])]
|
for j, v in enumerate(vals, 1):
|
cell = ws2.cell(rr, j, v)
|
cell.border = BORDER
|
cell.fill = TOT_FILL
|
cell.font = Font(bold=True)
|
cell.alignment = CT if j <= 4 else RT
|
if j in (5, 6, 7, 8, 9, 10):
|
cell.number_format = NF2
|
elif j in (11, 12, 13):
|
cell.number_format = NFP
|
ws2.freeze_panes = 'C4'
|
|
# ---------------- Sheet3 分市州分月 ----------------
|
ws3 = wb.create_sheet('分市州分月')
|
h3 = ['序号', '市州', '持续在规企业数(家)'] + ['%s月平均运距(公里)' % m for m in range(1, 9)] + \
|
['2026年1-8月累计平均运距(公里)', '2025年1-8月累计平均运距(公里)', '累计运距同比(%)']
|
w3 = [6, 14, 18] + [15] * 8 + [24, 24, 15]
|
ws3.cell(1, 1, '分市州 2026 年逐月平均运距（持续在规企业口径；同比基期：2025 年 1-8 月全部规上企业）').font = Font(bold=True, size=13, color='FFFFFF')
|
ws3.cell(1, 1).fill = TITLE_FILL
|
ws3.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
|
ws3.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(h3))
|
ws3.row_dimensions[1].height = 26
|
for j, h in enumerate(h3, 1):
|
c = ws3.cell(2, j, h)
|
c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
|
ws3.column_dimensions[get_column_letter(j)].width = w3[j - 1]
|
ws3.row_dimensions[2].height = 40
|
|
rr = 2
|
idx = 0
|
for city in CITY_ORDER:
|
c = by_city.get(city)
|
if not c or c['n'] == 0:
|
continue
|
idx += 1
|
rr += 1
|
b = B25.get(city, dict(ents=set(), f=0.0, t=0.0))
|
d26, d25 = dist(c['f26'], c['t26']), dist(b['f'], b['t'])
|
vals = [idx, city, c['n']] + [city_month(p, city, persist_names)[0] for p in MONTHS26] + \
|
[d26, d25, growth(d26, d25)]
|
for j, v in enumerate(vals, 1):
|
cell = ws3.cell(rr, j, v)
|
cell.border = BORDER
|
cell.alignment = CT if j <= 3 else RT
|
if j >= 4:
|
cell.number_format = NFP if j == 14 else NF2
|
rr += 1
|
vals = ['', '全省合计', tot['n']] + [city_month(p, None)[0] for p in MONTHS26] + \
|
[d26p, d25p, growth(d26p, d25p)]
|
for j, v in enumerate(vals, 1):
|
cell = ws3.cell(rr, j, v)
|
cell.border = BORDER
|
cell.fill = TOT_FILL
|
cell.font = Font(bold=True)
|
cell.alignment = CT if j <= 3 else RT
|
if j >= 4:
|
cell.number_format = NFP if j == 14 else NF2
|
ws3.freeze_panes = 'D3'
|
|
# ---------------- Sheet4 企业明细 ----------------
|
ws4 = wb.create_sheet('企业明细')
|
h4 = ['序号', '市州', '企业名称', '企业代码', '统一社会信用代码',
|
'2026年1-8月货运量(万吨)', '2026年1-8月周转量(万吨公里)', '2026年1-8月累计平均运距(公里)',
|
'2025年1-8月货运量(万吨)', '2025年1-8月周转量(万吨公里)', '2025年1-8月累计平均运距(公里)',
|
'平均运距同比(%)', '是否两年均持续在规']
|
w4 = [6, 12, 42, 22, 24, 20, 24, 22, 20, 24, 22, 14, 18]
|
ws4.cell(1, 1, '持续在规企业逐户累计平均运距（2026 年 1-8 月；同期值与同比仅对两年均持续在规的 545 家给出）').font = Font(bold=True, size=13, color='FFFFFF')
|
ws4.cell(1, 1).fill = TITLE_FILL
|
ws4.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
|
ws4.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(h4))
|
ws4.row_dimensions[1].height = 26
|
for j, h in enumerate(h4, 1):
|
c = ws4.cell(2, j, h)
|
c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
|
ws4.column_dimensions[get_column_letter(j)].width = w4[j - 1]
|
ws4.row_dimensions[2].height = 40
|
|
order = {c: i for i, c in enumerate(CITY_ORDER)}
|
rows_sorted = sorted(rows, key=lambda x: (order.get(x['city'], 99), -(x['t26'])))
|
rr = 2
|
for i, x in enumerate(rows_sorted, 1):
|
rr += 1
|
d26, d25 = dist(x['f26'], x['t26']), dist(x['f25'], x['t25'])
|
vals = [i, x['city'], x['en'], x['ec'], x['ucc'],
|
x['f26'] / 10000.0, x['t26'] / 10000.0, d26,
|
(x['f25'] / 10000.0) if x['cmp'] else None,
|
(x['t25'] / 10000.0) if x['cmp'] else None,
|
d25 if x['cmp'] else None,
|
growth(d26, d25) if x['cmp'] else None,
|
'是' if x['cmp'] else '否']
|
for j, v in enumerate(vals, 1):
|
cell = ws4.cell(rr, j, v)
|
cell.border = BORDER
|
cell.alignment = LT if j in (3, 4, 5) else (CT if j in (1, 2, 13) else RT)
|
if j in (6, 7, 8, 9, 10, 11):
|
cell.number_format = NF2
|
elif j == 12:
|
cell.number_format = NFP
|
if j == 12 and isinstance(v, float) and v > 0.5:
|
cell.font = Font(color='C00000', bold=True)
|
ws4.freeze_panes = 'D3'
|
ws4.auto_filter.ref = 'A2:M%d' % rr
|
|
# ---------------- Sheet5 口径对比 ----------------
|
ws6 = wb.create_sheet('口径对比')
|
h6 = ['序号', '市州', '2026年1-8月累计平均运距(公里)\n[持续在规企业636家]',
|
'2025年1-8月累计平均运距(公里)\n[全部规上企业]', '官方口径同比(%)',
|
'2025年1-8月累计平均运距(公里)\n[同企业545家]', '同企业口径同比(%)', '两者差异(百分点)', '说明']
|
w6 = [6, 14, 26, 24, 15, 24, 15, 16, 46]
|
ws6.cell(1, 1, '两种同比口径对比（官方口径 = 与汇总表「货运」页 T 列一致；同企业口径 = 仅两年均持续在规企业）').font = Font(bold=True, size=13, color='FFFFFF')
|
ws6.cell(1, 1).fill = TITLE_FILL
|
ws6.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
|
ws6.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(h6))
|
ws6.row_dimensions[1].height = 26
|
ws6.cell(2, 1, '注：交付主表（分市州汇总）采用官方口径；本页两个口径并列，差异大的市州多为两年间规上企业名单变动（停产、新增、拆分）所致，供业务核对参考。')
|
ws6.cell(2, 1).font = Font(size=9, italic=True, color='808080')
|
ws6.merge_cells(start_row=2, start_column=1, end_row=2, end_column=len(h6))
|
for j, h in enumerate(h6, 1):
|
c = ws6.cell(3, j, h)
|
c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
|
ws6.column_dimensions[get_column_letter(j)].width = w6[j - 1]
|
ws6.row_dimensions[3].height = 48
|
|
rr = 3
|
idx = 0
|
for city in CITY_ORDER:
|
c = by_city.get(city)
|
if not c or c['n'] == 0:
|
continue
|
idx += 1
|
rr += 1
|
b = B25.get(city, dict(ents=set(), f=0.0, t=0.0))
|
g = by_city_cmp.get(city)
|
d26, d25 = dist(c['f26'], c['t26']), dist(b['f'], b['t'])
|
g_off = growth(d26, d25)
|
if g:
|
dc26, dc25 = dist(g['f26c'], g['t26c']), dist(g['f25c'], g['t25c'])
|
g_same = growth(dc26, dc25)
|
else:
|
dc25, g_same = None, None
|
diff = (g_off - g_same) if (g_off is not None and g_same is not None) else None
|
note = ''
|
if diff is not None and abs(diff) > 0.10:
|
note = '两口径差异超 10 个百分点，主要因两年规上企业名单变动，建议核对该市州企业变动情况'
|
vals = [idx, city, d26, d25, g_off, dc25, g_same, (diff * 100 if diff is not None else None), note]
|
for j, v in enumerate(vals, 1):
|
cell = ws6.cell(rr, j, v)
|
cell.border = BORDER
|
cell.alignment = LT if j in (1, 9) else (CT if j == 2 else RT)
|
if j in (3, 4, 6):
|
cell.number_format = NF2
|
elif j in (5, 7):
|
cell.number_format = NFP
|
elif j == 8:
|
cell.number_format = NF1
|
rr += 1
|
g_cmp = growth(dist(tot_cmp['f26c'], tot_cmp['t26c']), dist(tot_cmp['f25c'], tot_cmp['t25c']))
|
vals = ['', '全省合计', d26p, d25p, growth(d26p, d25p), dist(tot_cmp['f25c'], tot_cmp['t25c']), g_cmp,
|
(growth(d26p, d25p) - g_cmp) * 100 if g_cmp is not None else None, '']
|
for j, v in enumerate(vals, 1):
|
cell = ws6.cell(rr, j, v)
|
cell.border = BORDER
|
cell.fill = TOT_FILL
|
cell.font = Font(bold=True)
|
cell.alignment = CT if j == 2 else RT
|
if j in (3, 4, 6):
|
cell.number_format = NF2
|
elif j in (5, 7):
|
cell.number_format = NFP
|
elif j == 8:
|
cell.number_format = NF1
|
ws6.freeze_panes = 'C4'
|
|
# ---------------- Sheet5 口径校验 ----------------
|
ws5 = wb.create_sheet('口径校验')
|
ws5.sheet_view.showGridLines = False
|
ws5.column_dimensions['A'].width = 46
|
for col, w in (('B', 20), ('C', 20), ('D', 14), ('E', 46)):
|
ws5.column_dimensions[col].width = w
|
ws5.cell(1, 1, '口径校验与对账').font = Font(bold=True, size=13, color='FFFFFF')
|
ws5.cell(1, 1).fill = TITLE_FILL
|
ws5.merge_cells(start_row=1, start_column=1, end_row=1, end_column=5)
|
sum_f = sum(r['f26'] for r in rows) / 10000.0
|
sum_t = sum(r['t26'] for r in rows) / 10000.0
|
aug_f = sum(v[0] for c in CITY_ORDER for en, v in cust.get(('2026-08', c), {}).items() if en in persist_names) / 10000.0
|
aug_t = sum(v[1] for c in CITY_ORDER for en, v in cust.get(('2026-08', c), {}).items() if en in persist_names) / 10000.0
|
chk = [
|
('校验项', '本表数值', '对照数值', '差异', '说明'),
|
('明细表企业数 = 汇总表企业数', len(rows), tot['n'], len(rows) - tot['n'], '636 家持续在规企业逐户一行'),
|
('明细表货运量合计(万吨)', round(sum_f, 2), round(tot['f26'] / 10000.0, 2), round(sum_f - tot['f26'] / 10000.0, 2), '明细求和 = 17 市州汇总合计'),
|
('明细表周转量合计(万吨公里)', round(sum_t, 2), round(tot['t26'] / 10000.0, 2), round(sum_t - tot['t26'] / 10000.0, 2), '明细求和 = 17 市州汇总合计'),
|
('2026年1-8月累计货运量(万吨)', round(tot['f26'] / 10000.0, 2), PROV['vol26'], round(tot['f26'] / 10000.0 - PROV['vol26'], 2), '对照已发布报告 1-8 月累计'),
|
('2026年1-8月累计周转量(万吨公里)', round(tot['t26'] / 10000.0, 2), PROV['turn26'], round(tot['t26'] / 10000.0 - PROV['turn26'], 2), '同上'),
|
('2026年8月全省货运量(万吨)', round(aug_f, 2), PROV['vol26_08'], round(aug_f - PROV['vol26_08'], 2), '验证市州归集无误'),
|
('2026年8月全省周转量(万吨公里)', round(aug_t, 2), PROV['turn26_08'], round(aug_t - PROV['turn26_08'], 2), '同上'),
|
('2025年1-8月累计货运量(万吨)【同比基期】', round(tot25['f'] / 10000.0, 2), PROV['vol25'], round(tot25['f'] / 10000.0 - PROV['vol25'], 2), '对照汇总表「货运」页 V7..AJ7（缓存值）'),
|
('2025年1-8月累计周转量(万吨公里)【同比基期】', round(tot25['t'] / 10000.0, 2), PROV['turn25'], round(tot25['t'] / 10000.0 - PROV['turn25'], 2), '对照汇总表「货运」页 V8..AJ8（缓存值）'),
|
('2025年1-8月全省规上企业数(家)', len(tot25['ents']), 664, len(tot25['ents']) - 664, '同比基期企业范围'),
|
('累计货运量同比', growth(tot['f26'], tot25['f']), PROV['yoy_vol'], round(growth(tot['f26'], tot25['f']) - PROV['yoy_vol'], 4), '对照已发布报告 1-8 月累计同比'),
|
('累计周转量同比', growth(tot['t26'], tot25['t']), PROV['yoy_turn'], round(growth(tot['t26'], tot25['t']) - PROV['yoy_turn'], 4), '同上'),
|
('2025年1-8月累计平均运距(公里)', round(d25p, 2), None, None, '= 2025 累计周转量 ÷ 累计货运量'),
|
('2026年1-8月累计平均运距(公里)', round(d26p, 2), None, None, '= 2026 累计周转量 ÷ 累计货运量'),
|
('全省累计平均运距同比', growth(d26p, d25p), None, None, '= 156.92 ÷ 150.10 − 1'),
|
('覆盖市州数', len([c for c in CITY_ORDER if by_city.get(c, {}).get('n')]), 17, None, '17 个市州全覆盖'),
|
('未识别地区码', len(unknown), 0, None, ('、'.join(unknown) if unknown else '无')),
|
]
|
for i, row in enumerate(chk, 1):
|
for j, v in enumerate(row, 1):
|
cell = ws5.cell(i + 2, j, v)
|
cell.border = BORDER
|
cell.alignment = CT if i == 1 else (LT if j in (1, 5) else RT)
|
if i == 1:
|
cell.font = Font(bold=True); cell.fill = HDR_FILL
|
elif j in (2, 3, 4):
|
cell.number_format = NFP if row[0].endswith('同比') else NF2
|
|
os.makedirs(OUT_DIR, exist_ok=True)
|
wb.save(OUT)
|
print('OK ->', OUT)
|
print('2026 1-8月: %.4f 万吨 / %.4f 万吨公里 / %.4f 公里' % (tot['f26'] / 10000.0, tot['t26'] / 10000.0, d26p))
|
print('2025 1-8月: %.4f 万吨 / %.4f 万吨公里 / %.4f 公里 (%d 家)' % (tot25['f'] / 10000.0, tot25['t'] / 10000.0, d25p, len(tot25['ents'])))
|
print('全省同比: 货运量 %.2f%% 周转量 %.2f%% 平均运距 %.2f%%' %
|
(growth(tot['f26'], tot25['f']) * 100, growth(tot['t26'], tot25['t']) * 100, growth(d26p, d25p) * 100))
|
for city in CITY_ORDER:
|
c = by_city.get(city)
|
if c and c['n']:
|
b = B25.get(city, dict(ents=set(), f=0.0, t=0.0))
|
d1, d0 = dist(c['f26'], c['t26']), dist(b['f'], b['t'])
|
print('%-8s n=%-4d 2026=%8.2f 2025=%8s 同比=%s' % (
|
city, c['n'], d1, ('%.2f' % d0) if d0 else '-', ('%.1f%%' % (growth(d1, d0) * 100)) if d0 else '-'))
|
