| New file |
| | |
|---|
| | | # -*- coding: utf-8 -*- |
|---|
| | | """è´§è¿ææ¥ãæç»å¨è§ä¼ä¸ãåå¸å· 1-8 æ累计è¿è·ååæ¯ å°è´¦çæ |
|---|
| | | |
|---|
| | | æ°æ®æºï¼traffic_audit.h2032_enterprise_monthlyï¼ãé路货ç©è¿è¾æ度ç产æ
åµãH203-2 ä¼ä¸ææ¥ï¼ |
|---|
| | | 导åºä»¶ _tmp_persist_scope.tsvï¼ä¼ä¸çº§ç´¯è®¡ï¼ã_tmp_persist_scope_monthly.tsvï¼ä¼ä¸çº§åæï¼ |
|---|
| | | å£å¾ï¼ |
|---|
| | | · æç»å¨è§ä¼ä¸ = 2026 å¹´ 1-8 æè¿ç» 8 个æåä¸æ¥ææ¥çä¼ä¸ï¼ |
|---|
| | | · 累计平åè¿è·ï¼å
¬éï¼= 累计货ç©å¨è½¬é ÷ 累计货è¿éï¼å æå¹³åï¼ï¼ |
|---|
| | | · åæ¯ = 2026 å¹´ 1-8 æ累计平åè¿è· ÷ 2025 å¹´ 1-8 æ累计平åè¿è· â 1ï¼ |
|---|
| | | 为ä¿è¯å¯æ¯ï¼åªå¨ä¸¤å¹´åæç»å¨è§çä¼ä¸ä¸è®¡ç®ã |
|---|
| | | """ |
|---|
| | | 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_VOL_PUB = 31399.82 # ä¸å¨ï¼å·²åå¸ã2026å¹´8æè§ä¸è´§ç©å¨è½¬éä¸å¹³åè¿è·åææ¥åã1-8æ累计 |
|---|
| | | PROV_TURN_PUB = 4927161.77 # ä¸å¨å
¬éï¼åä¸ |
|---|
| | | |
|---|
| | | 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, n_cmp=0, f26=0.0, t26=0.0, f26c=0.0, t26c=0.0, f25c=0.0, t25c=0.0)) |
|---|
| | | c['n'] += 1 |
|---|
| | | c['f26'] += r['f26']; c['t26'] += r['t26'] |
|---|
| | | if r['cmp']: |
|---|
| | | c['n_cmp'] += 1 |
|---|
| | | c['f26c'] += r['f26']; c['t26c'] += r['t26'] |
|---|
| | | c['f25c'] += r['f25']; c['t25c'] += r['t25'] |
|---|
| | | |
|---|
| | | tot = dict(n=0, n_cmp=0, f26=0.0, t26=0.0, f26c=0.0, t26c=0.0, f25c=0.0, t25c=0.0) |
|---|
| | | for c in by_city.values(): |
|---|
| | | for k in tot: |
|---|
| | | tot[k] += c[k] |
|---|
| | | unknown = sorted(k for k in by_city if k.startswith('æªè¯å«')) |
|---|
| | | |
|---|
| | | persist_names = set(r['en'] for r in rows) |
|---|
| | | cmp_names = set(r['en'] for r in rows if r['cmp']) |
|---|
| | | |
|---|
| | | # ---------- 读ä¼ä¸çº§åææ°æ® ---------- |
|---|
| | | 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: |
|---|
| | | continue |
|---|
| | | per, rc, en, fr, tu = p[0], p[1], p[2], p[3], p[4] |
|---|
| | | if per == 'report_period': |
|---|
| | | continue |
|---|
| | | cust.setdefault((per, city_of(rc)), {})[en] = (num(fr), num(tu)) |
|---|
| | | |
|---|
| | | |
|---|
| | | def city_month(period, city, names): |
|---|
| | | d = cust.get((period, city)) |
|---|
| | | if not d: |
|---|
| | | return None, 0.0 |
|---|
| | | fr = tu = 0.0 |
|---|
| | | for en, v in d.items(): |
|---|
| | | if en in names: |
|---|
| | | fr += v[0]; tu += v[1] |
|---|
| | | return dist(fr, tu), fr |
|---|
| | | |
|---|
| | | |
|---|
| | | # ---------- æ ·å¼ ---------- |
|---|
| | | 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 = '#,##0.00', '#,##0.0' |
|---|
| | | |
|---|
| | | wb = Workbook() |
|---|
| | | |
|---|
| | | # ---------------- Sheet1 说æä¸å£å¾ ---------------- |
|---|
| | | ws = wb.active |
|---|
| | | ws.title = '说æä¸å£å¾' |
|---|
| | | ws.sheet_view.showGridLines = False |
|---|
| | | ws.column_dimensions['A'].width = 16 |
|---|
| | | ws.column_dimensions['B'].width = 104 |
|---|
| | | 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 家ï¼å
¨æä¸è´ï¼ã' % tot['n']), |
|---|
| | | ('累计è¿è·å£å¾', '累计平åè¿è·ï¼å
¬éï¼= 1â8 æ累计货ç©å¨è½¬é ÷ 1â8 æ累计货è¿éï¼å æå¹³åï¼ãæºè¡¨åä½ä¸ºå¨ãå¨å
¬éï¼' |
|---|
| | | 'æ¬è¡¨æç®ä¸ºä¸å¨ãä¸å¨å
¬éï¼æ¯å¼ä¸åã'), |
|---|
| | | ('åæ¯å£å¾', 'åæ¯ = 2026 å¹´ 1â8 æ累计平åè¿è· ÷ 2025 å¹´ 1â8 æ累计平åè¿è· â 1ã' |
|---|
| | | '为ä¿è¯å¯æ¯ï¼åæ¯åªå¨ã两年åæç»å¨è§ãç %d 家ä¼ä¸ä¸è®¡ç®ï¼å¦æ %d 家 2026 å¹´æç»å¨è§ä¼ä¸ï¼' |
|---|
| | | '2025 å¹´ 1â8 ææ è¿ç»ä¸æ¥è®°å½ï¼ä¸åä¸åæ¯ï¼ã' % (tot['n_cmp'], tot['n'] - tot['n_cmp'])), |
|---|
| | | ('å
¨çå计', 'ä¸å¾ä¸º 17 个å¸å·ä¹åï¼ä¸å«è·¨å¸å·éå å£å¾ï¼ã'), |
|---|
| | | ('æ¥è¡¨æ', 'æ¬æ累计 2026-01-01â2026-08-31ï¼ä¸å¹´åæ 2025-01-01â2025-08-31ã'), |
|---|
| | | ('页ç¾è¯´æ', 'ãåå¸å·æ±æ»ãæå¸å·æ±æ» 1-8 æ累计è¿è·ä¸åæ¯ï¼ãåå¸å·åæã为åå¸å· 2026 å¹´éæå¹³åè¿è·ä¸åæ对æ¯ï¼' |
|---|
| | | 'ãä¼ä¸æç»ã为 636 家æç»å¨è§ä¼ä¸éæ·æ°æ®ï¼ãå£å¾æ ¡éªã为æç»ä¸æ±æ»å¯¹è´¦ã以åä¸å·²åå¸å
¨çæ°æ®çæ¯å¯¹ã'), |
|---|
| | | ('注æäºé¡¹', 'â æ 5 家ä¼ä¸ 1â8 æææ¥è´§è¿éãå¨è½¬éå为 0ï¼è®¡å
¥ä¼ä¸æ°ä½ä¸å½±åè¿è·ï¼' |
|---|
| | | 'â¡ æ¦æ±ç³å交éè¿è¾æéå
¬å¸ãæ¹åæ·é¾äº¤éè¿ä¸æéå
¬å¸ 2026 å¹´ 1 æçå°åºç ä¸å
¶åæ份ä¸åï¼420100 â 420107 / 420103ï¼ï¼' |
|---|
| | | 'ä½åå±æ¦æ±å¸ï¼ä¸å½±åå¸å·å½éï¼â¢ 累计货è¿é为 0 çä¼ä¸ç´¯è®¡è¿è·ç空ã'), |
|---|
| | | ('çææ¶é´', '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æ累计货è¿é(ä¸å¨)', '2026å¹´1-8æ累计货ç©å¨è½¬é(ä¸å¨å
¬é)', '2026å¹´1-8æ累计平åè¿è·(å
Ž)', |
|---|
| | | '2025å¹´1-8æ累计平åè¿è·(å
¬é)[å¯æ¯å£å¾]', '累计è¿è·åæ¯(%)', |
|---|
| | | '累计货è¿éåæ¯(%)', '累计货ç©å¨è½¬éåæ¯(%)', 'å¤æ³¨'] |
|---|
| | | widths = [6, 14, 16, 18, 20, 24, 20, 22, 14, 14, 16, 40] |
|---|
| | | ws2.cell(1, 1, 'è´§è¿ææ¥æç»å¨è§ä¼ä¸åå¸å· 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 å¹´æç»å¨è§ä¼ä¸å
¨å£å¾ï¼%d 家ï¼ï¼ç¬¬ 8â11 å为两年åæç»å¨è§ä¼ä¸å¯æ¯å£å¾ï¼%d 家ï¼çåæ¯è®¡ç®åºæ°ã' |
|---|
| | | % (tot['n'], tot['n_cmp'])) |
|---|
| | | 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 |
|---|
| | | d26, d26c, d25c = dist(c['f26'], c['t26']), dist(c['f26c'], c['t26c']), dist(c['f25c'], c['t25c']) |
|---|
| | | if c['n_cmp'] == 0: |
|---|
| | | note = 'åæ¯ä¸éç¨ï¼è¯¥å¸å· 2026 å¹´æç»å¨è§ä¼ä¸å¨ 2025 å¹´ 1-8 ææ è¿ç»ä¸æ¥è®°å½' |
|---|
| | | elif c['n_cmp'] == c['n']: |
|---|
| | | note = '' |
|---|
| | | else: |
|---|
| | | note = 'åæ¯æ %d 家两年åæç»å¨è§ä¼ä¸è®¡ç®' % c['n_cmp'] |
|---|
| | | vals = [idx, city, c['n'], c['n_cmp'], c['f26'] / 10000.0, c['t26'] / 10000.0, d26, d25c, |
|---|
| | | growth(d26c, d25c), growth(c['f26c'], c['f25c']), growth(c['t26c'], c['t25c']), note] |
|---|
| | | for j, v in enumerate(vals, 1): |
|---|
| | | cell = ws2.cell(rr, j, v) |
|---|
| | | cell.border = BORDER |
|---|
| | | cell.alignment = LT if j == 12 else (CT if j <= 4 else RT) |
|---|
| | | if j in (5, 6, 7, 8): |
|---|
| | | cell.number_format = NF2 |
|---|
| | | elif j in (9, 10, 11): |
|---|
| | | cell.number_format = NF1 |
|---|
| | | rr += 1 |
|---|
| | | vals = ['', 'å
¨çå计', tot['n'], tot['n_cmp'], tot['f26'] / 10000.0, tot['t26'] / 10000.0, |
|---|
| | | dist(tot['f26'], tot['t26']), dist(tot['f25c'], tot['t25c']), |
|---|
| | | growth(dist(tot['f26c'], tot['t26c']), dist(tot['f25c'], tot['t25c'])), |
|---|
| | | growth(tot['f26c'], tot['f25c']), growth(tot['t26c'], tot['t25c']), ''] |
|---|
| | | 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): |
|---|
| | | cell.number_format = NF2 |
|---|
| | | elif j in (9, 10, 11): |
|---|
| | | cell.number_format = NF1 |
|---|
| | | 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, 16] + [14] * 8 + [24, 24, 14] |
|---|
| | | 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 |
|---|
| | | vals = [idx, city, c['n']] |
|---|
| | | for per in MONTHS26: |
|---|
| | | vals.append(city_month(per, city, persist_names)[0]) |
|---|
| | | vals += [dist(c['f26'], c['t26']), dist(c['f25c'], c['t25c']), growth(dist(c['f26c'], c['t26c']), dist(c['f25c'], c['t25c']))] |
|---|
| | | 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 = NF2 if j <= 13 else NF1 |
|---|
| | | rr += 1 |
|---|
| | | # å
¨çåæï¼æ 17 å¸å·éæå计éç® |
|---|
| | | vals = ['', 'å
¨çå计', tot['n']] |
|---|
| | | for per in MONTHS26: |
|---|
| | | fr = tu = 0.0 |
|---|
| | | for city in CITY_ORDER: |
|---|
| | | _, f0 = city_month(per, city, persist_names) |
|---|
| | | fr += f0 |
|---|
| | | d = cust.get((per, city), {}) |
|---|
| | | for en, v in d.items(): |
|---|
| | | if en in persist_names: |
|---|
| | | tu += v[1] |
|---|
| | | vals.append(dist(fr, tu)) |
|---|
| | | vals += [dist(tot['f26'], tot['t26']), dist(tot['f25c'], tot['t25c']), |
|---|
| | | growth(dist(tot['f26c'], tot['t26c']), dist(tot['f25c'], tot['t25c']))] |
|---|
| | | 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 = NF2 if j <= 13 else NF1 |
|---|
| | | 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 æï¼åæ¯åºæ 2025 å¹´ 1-8 æï¼').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 = NF1 |
|---|
| | | 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 å£å¾æ ¡éª ---------------- |
|---|
| | | ws5 = wb.create_sheet('å£å¾æ ¡éª') |
|---|
| | | ws5.sheet_view.showGridLines = False |
|---|
| | | ws5.column_dimensions['A'].width = 42 |
|---|
| | | for col, w in (('B', 20), ('C', 20), ('D', 14), ('E', 40)): |
|---|
| | | 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 å¸å·æ±æ»å计"), |
|---|
| | | ("ä¸å·²åå¸å
¨ç 1-8 æ累计货è¿éæ¯å¯¹(ä¸å¨)", round(tot["f26"] / 10000.0, 2), PROV_VOL_PUB, round(tot["f26"] / 10000.0 - PROV_VOL_PUB, 2), "对ç
§ã2026å¹´8æè§ä¸è´§ç©å¨è½¬éä¸å¹³åè¿è·åææ¥åã"), |
|---|
| | | ("ä¸å·²åå¸å
¨ç 1-8 æ累计å¨è½¬éæ¯å¯¹(ä¸å¨å
¬é)", round(tot["t26"] / 10000.0, 2), PROV_TURN_PUB, round(tot["t26"] / 10000.0 - PROV_TURN_PUB, 2), "åä¸"), |
|---|
| | | ("2026å¹´8æå
¨çè´§è¿éæ¯å¯¹(ä¸å¨)", round(aug_f, 2), 3665.36, round(aug_f - 3665.36, 2), "对ç
§å·²åå¸ 8 æå
¨çè§ä¸è´§è¿éï¼éªè¯å¸å·å½éæ 误"), |
|---|
| | | ("2026å¹´8æå
¨çå¨è½¬éæ¯å¯¹(ä¸å¨å
¬é)", round(aug_t, 2), 570004.44, round(aug_t - 570004.44, 2), "对ç
§å·²åå¸ 8 æå
¨çè§ä¸å¨è½¬é"), |
|---|
| | | ("å
¨ç 1-8 æ累计平åè¿è·(å
¬é)", round(dist(tot["f26"], tot["t26"]), 2), None, None, "ç±æ¬è¡¨ç´¯è®¡å¨è½¬é ÷ 累计货è¿é计ç®"), |
|---|
| | | ("è¦çå¸å·æ°", 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 = NF2 |
|---|
| | | |
|---|
| | | os.makedirs(OUT_DIR, exist_ok=True) |
|---|
| | | wb.save(OUT) |
|---|
| | | print('OK ->', OUT) |
|---|
| | | print('æç»å¨è§ä¼ä¸æ°: %d 两年å¯æ¯: %d' % (tot['n'], tot['n_cmp'])) |
|---|
| | | print('å
¨ç累计货è¿é(ä¸å¨): %.2f 累计å¨è½¬é(ä¸å¨å
¬é): %.2f 累计平åè¿è·: %.2f' % |
|---|
| | | (tot['f26'] / 10000.0, tot['t26'] / 10000.0, dist(tot['f26'], tot['t26']))) |
|---|
| | | print('å¯æ¯å£å¾ 2026 累计è¿è·: %.2f 2025: %.2f åæ¯: %.2f%%' % |
|---|
| | | (dist(tot['f26c'], tot['t26c']), dist(tot['f25c'], tot['t25c']), growth(dist(tot['f26c'], tot['t26c']), dist(tot['f25c'], tot['t25c'])) * 100)) |
|---|
| | | for city in CITY_ORDER: |
|---|
| | | c = by_city.get(city) |
|---|
| | | if c and c['n']: |
|---|
| | | g = growth(dist(c['f26c'], c['t26c']), dist(c['f25c'], c['t25c'])) |
|---|
| | | print('%-8s n=%-4d 2026累计=%8s 2025累计=%8s åæ¯=%s' % ( |
|---|
| | | city, c['n'], |
|---|
| | | '%.2f' % dist(c['f26'], c['t26']), |
|---|
| | | '%.2f' % dist(c['f25c'], c['t25c']) if dist(c['f25c'], c['t25c']) else '-', |
|---|
| | | '%.1f%%' % (g * 100) if g is not None else '-')) |
|---|
