From 791a12762b4b3ab8b781e85d99c4316089f2a3f0 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期一, 21 九月 2026 10:20:46 +0800
Subject: [PATCH] docs(工作日结): 补记 2026-09-21 全库数据备份与同事交付包
---
docs/投资/工具/格式探测.py | 178 +++++++++++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 161 insertions(+), 17 deletions(-)
diff --git "a/docs/\346\212\225\350\265\204/\345\267\245\345\205\267/\346\240\274\345\274\217\346\216\242\346\265\213.py" "b/docs/\346\212\225\350\265\204/\345\267\245\345\205\267/\346\240\274\345\274\217\346\216\242\346\265\213.py"
index fbe1240..a6ed4dd 100644
--- "a/docs/\346\212\225\350\265\204/\345\267\245\345\205\267/\346\240\274\345\274\217\346\216\242\346\265\213.py"
+++ "b/docs/\346\212\225\350\265\204/\345\267\245\345\205\267/\346\240\274\345\274\217\346\216\242\346\265\213.py"
@@ -13,7 +13,12 @@
2) 宸ヤ綔琛ㄥ厛鎸夆�滆〃澶村尯鏄惁甯︽湰鏈熷勾鏈堟爣璁扳�濅紭鍏堬紝鍐嶆寜鍛戒腑瀛楁鏁版墦鍒嗭紝
閬垮厤鍙栧埌绌烘ā鏉胯〃 / 鍘嗗彶鏈堣〃锛堝娼滄睙銆佹灄鍖恒�佽闃崇殑澶氭湀宸ヤ綔绨匡級锛�
3) 椤圭洰琛屽垽瀹氾細鍚嶇О鍒楅潪绌轰笖涓嶆槸灏忚/鍚堣/鍒嗙被琛岋紝涓旀湁鏁板�奸敋鐐癸紙鑷紑濮嬪缓璁剧疮璁＄瓑锛夛紝
- 鍏煎鈥滄棤搴忓彿鍒椻�濓紙闅忓窞锛変笌鈥滅己搴忓彿鈥濓紙鏋楀尯 7 鏈堬級涓ょ鎯呭喌銆�
+ 鍏煎鈥滄棤搴忓彿鍒椻�濓紙闅忓窞锛変笌鈥滅己搴忓彿鈥濓紙鏋楀尯 7 鏈堬級涓ょ鎯呭喌锛�
+ 4) 甯傚窞鍒嗘锛氬厛鎵惧嚭銆屽競宸炶銆嶏紙棣� 5 鍒楅噷鎭板ソ鍙湁 1 涓崟鍏冩牸鏄競宸炲悕锛屽悓琛屽叾浣欏垪鍏ㄧ┖锛夛紝
+ 鍐嶅彧鍙栫洰鏍囧競宸炴鍐呯殑椤圭洰琛屻�傛箹鍖楃渷鍗佸牥浜ら�氱墿娴佲�﹁〃鐨� 88 琛岄噷锛孯11-R63 鏄殣钘忕殑
+ 姝︽眽鍘嗗彶鍖哄潡锛岃嫢涓嶆寜甯傚窞鍒嗘灏变細鎶婃姹夌殑鍘嗗彶椤圭洰褰撴垚鍗佸牥鐨勫彇杩涙潵锛�
+ 5) 闅愯棌琛� / 闅愯棌宸ヤ綔琛ㄤ笉浣滀负鍒ゅ畾渚濇嵁锛氶粍鐭崇墿娴� R10 鏄殣钘忚锛屼絾瀹冩槸涓�鏉＄湡瀹為」鐩
+ 锛堥槼鏂板幙鐒﹀北鐗╂祦浠撳偍涓績锛夛紝涓斿凡璁″叆榛勭煶甯傚窞姹囨�昏锛屽繀椤诲彇銆�
"""
import sys, os, io, re, argparse, datetime as _dt
@@ -55,6 +60,28 @@
BAD_NAME = ('濉姤鍗曚綅', '绛剧珷', '鍗曚綅璐熻矗浜�', '缁熻璐熻矗浜�', '瀹℃牳', '璇存槑', '娉細', '鏈堟姤', '鎶ヨ〃',
'搴� 鍙�', '搴忓彿', '椤圭洰鍚嶇О', '椤圭洰涓氫富')
+CITIES = ('姝︽眽', '榛勭煶', '鍗佸牥', '瀹滄槍', '瑗勯槼', '閯傚窞', '鑽嗛棬', '瀛濇劅', '鑽嗗窞', '榛勫唸',
+ '鍜稿畞', '闅忓窞', '鎭╂柦', '浠欐', '娼滄睙', '澶╅棬', '绁炲啘鏋�', '鏋楀尯')
+
+
+def city_segments(cell, nrows, ncols, maxc=5):
+ # 甯傚窞琛� = 棣� maxc 鍒楅噷鎭板ソ鍙湁 1 涓崟鍏冩牸鏄競宸炲悕锛堝悓琛屽叾浣欏垪涓虹┖锛夈��
+ # 杩斿洖 [(琛屽彿1鍩�, 甯傚窞鍚�), ...]锛屽凡鎸夎鍙锋帓搴忋��
+ segs = []
+ for r in range(min(nrows, 800)):
+ vals = [norm(cell(r, c)) for c in range(min(ncols, maxc))]
+ hit = [i for i, v in enumerate(vals) if v in CITIES]
+ if len(hit) == 1 and sum(1 for v in vals if v) == 1:
+ segs.append((r + 1, vals[hit[0]]))
+ return segs
+
+
+def city_of_text(text):
+ for c in CITIES:
+ if c in str(text or ''):
+ return c
+ return None
+
def norm(s):
s = str(s or '')
@@ -79,8 +106,50 @@
return n - 1
+def sheet_merges(path):
+ """杩斿洖 {sheet_name: [(rlo, rhi, clo, chi), ...]}锛�0 鍩恒�佸彸寮�锛夈��
+
+ 鐢ㄤ簬璇嗗埆銆屼袱绾ц〃澶淬�嶏細缁勮〃澶达紙璺ㄥ鍒楃殑鍚堝苟鍗曞厓鏍硷級涓嶈兘鐩存帴褰撴垚鏌愬瓧娈电殑鍒椼��
+ 璇讳笉鍒板悎骞朵俊鎭椂杩斿洖宸茶В鏋愬埌鐨勯儴鍒嗭紙涓嶆姏寮傚父锛夈��
+ """
+ with open(path, 'rb') as fh:
+ magic = fh.read(4)
+ out = {}
+ try:
+ if magic[:4] == b'PK\x03\x04':
+ if openpyxl is None:
+ return out
+ wb = openpyxl.load_workbook(path, data_only=True)
+ for nm in wb.sheetnames:
+ ws = wb[nm]
+ out[nm] = [(r.min_row - 1, r.max_row, r.min_col - 1, r.max_col)
+ for r in ws.merged_cells.ranges]
+ wb.close()
+ else:
+ if xlrd is None:
+ return out
+ bk = xlrd.open_workbook(path, formatting_info=True)
+ for si in range(bk.nsheets):
+ sh = bk.sheet_by_index(si)
+ out[sh.name] = list(getattr(sh, 'merged_cells', []) or [])
+ except Exception:
+ return out
+ return out
+
+
+def merge_span(merges, row1, col0):
+ """璇ュ崟鍏冩牸锛�1 鍩鸿鍙枫��0 鍩哄垪鍙凤級鎵�鍦ㄥ悎骞跺尯鐨� (鍒�0璧�, 鍒�0姝㈠彸寮�)锛涗笉鍦ㄥ悎骞跺尯杩斿洖 None銆�"""
+ for (rlo, rhi, clo, chi) in (merges or ()):
+ if rlo <= row1 - 1 < rhi and clo <= col0 < chi:
+ return (clo, chi)
+ return None
+
+
def open_sheets(path):
- """杩斿洖 [(sheet_name, nrows, ncols, cell_fn), ...]锛�.et 璇锋敼鐢� Excel COM銆�"""
+ """杩斿洖 [(sheet_name, nrows, ncols, cell_fn), ...]銆�
+
+ 娉ㄦ剰锛歚.et`锛圵PS锛夊疄涓� OLE2/BIFF8 澶嶅悎鏂囨。锛寈lrd 鍙洿鎺ヨ锛屼笉蹇呰蛋 Excel COM銆�
+ """
with open(path, 'rb') as fh:
magic = fh.read(4)
if magic[:4] == b'PK\x03\x04':
@@ -124,17 +193,85 @@
return bool(re.match(r'^-?\d+(\.\d+)?$', norm(v)))
-def profile(cell, nrows, ncols, hits):
- namecol = None
- if '椤圭洰鍚嶇О' in hits:
- namecol = hits['椤圭洰鍚嶇О'][0][1]
+def _num_count(cell, nrows, col, start_row1):
+ """缁熻璇ュ垪鍦ㄨ〃澶磋浠ヤ笅鐨勬暟鍊煎崟鍏冩牸涓暟锛堢敤浜庡尯鍒嗗悓鍚嶅垪锛屽銆岃嚜寮�濮嬪缓璁剧疮璁″畬鎴愭姇璧勩�嶄笌銆屸�︽柊澧炲缓绛戦潰绉�嶏級銆�"""
+ n = 0
+ for r in range(start_row1, min(nrows, 800)):
+ v = cell(r, col)
+ if isinstance(v, (int, float)) and not isinstance(v, bool):
+ n += 1
+ return n
+
+
+def pick_columns(cell, nrows, ncols, merges=None, fields=None):
+ """鎸夎〃澶村叧閿瓧瀹氫綅鍒� 鈫� ({瀛楁: 0 鍩哄垪鍙穧, hits)銆�
+
+ 涓ょ骇琛ㄥご娑堟锛氳嫢鏌愬瓧娈电殑鍛戒腑钀藉湪**璺ㄥ鍒楃殑鍚堝苟鍗曞厓鏍�**閲岋紝璇存槑瀹冩槸缁勮〃澶达紝涓嶈兘鐩存帴褰撹瀛楁鐨勫垪锛�
+ 姝ゆ椂鍦ㄦ湰鍚堝苟鍖哄唴鎺掗櫎銆屽叾瀹冨瓧娈垫洿娣辩殑瀛愯〃澶淬�嶆墍鍗犵殑鍒楋紝鑻ュ彧鍓╁敮涓�涓�鍒楋紝鍒欒鍒楁墠鏄湰瀛楁鐨勫垪銆�
+ 渚嬶細閯傚窞銆婁紒涓氭湀鎶ユ眹鎬汇�婻5 缁勮〃澶淬�岃嚜骞村垵绱瀹屾垚鎶曡祫锛堜竾鍏冿級銆嶅悎骞� I5:K5锛�
+ 瀛愯〃澶� I=鏈勾璁″垝鎶曡祫銆丣=鍚堣銆並=鍏朵腑锛氭湰鏈堝畬鎴愭姇璧� 鈫�
+ 銆岃嚜骞村垵绱銆嶈惤鍦� J锛堜笌鎶曡祫绯荤粺 G 鍒椾竴鑷达級锛岃�屼笉鏄� I锛堥偅鏄湰骞磋鍒掓姇璧勶級銆�
+ """
+ hits = scan_fields(cell, nrows, ncols)
+ fields = list(fields) if fields else [k for k, _ in FIELD_PAT]
+ best = {}
+ for f in fields:
+ if f not in hits:
+ continue
+ uniq, seen = [], set()
+ for h in sorted(hits[f], key=lambda h: (h[0], colindex(h[1]))):
+ if h[1] in seen:
+ continue
+ seen.add(h[1]); uniq.append(h)
+ # 鍚屽悕鍒楁秷姝э細浼樺厛銆岃〃澶翠笅鏂圭湡鐨勬湁鏁板瓧銆嶇殑閭ｄ竴鍒椼��
+ # 渚嬶細姝︽眽鐗╂祦銆岃嚜寮�濮嬪缓璁剧疮璁″畬鎴愭姇璧勶紙涓囧厓锛夈�岺 涓庛�岃嚜寮�濮嬪缓璁剧疮璁℃柊澧炲缓绛戦潰绉�峆 鍚屽悕锛�
+ # 鍙湁 H 鍒楁湁鏁板瓧 鈫� 鍙� H锛堟寜鈥滃懡涓瘝鏈�鐭�濅細閿欏彇 P锛岄偅鏄缓绛戦潰绉級銆�
+ withnum = [h for h in uniq if _num_count(cell, nrows, colindex(h[1]), h[0]) > 0]
+ pool = withnum or uniq
+ best[f] = sorted(pool, key=lambda h: (h[0], colindex(h[1])))[0]
+ cols = {}
+ for f, h in best.items():
+ row1, letter, _txt = h
+ c0 = colindex(letter)
+ span = merge_span(merges, row1, c0)
+ if span and (span[1] - span[0]) > 1:
+ taken = set()
+ for f2, h2 in best.items():
+ if f2 == f or h2[0] <= row1:
+ continue
+ c2 = colindex(h2[1])
+ if span[0] <= c2 < span[1]:
+ taken.add(c2)
+ free = [c for c in range(span[0], span[1]) if c not in taken]
+ if len(free) == 1:
+ cols[f] = free[0]
+ continue
+ cols[f] = c0
+ return cols, hits
+
+
+def profile(cell, nrows, ncols, hits, city=None, merges=None):
+ cols, _ = pick_columns(cell, nrows, ncols, merges)
+ namecol = colname(cols['椤圭洰鍚嶇О']) if '椤圭洰鍚嶇О' in cols else None
data_start = data_end = None
project_rows = 0
groups = []
+ segs = city_segments(cell, nrows, ncols)
+ if city is None and len({s[1] for s in segs}) == 1:
+ city = segs[0][1]
+ lo, hi, city_row = 1, 10 ** 9, None
+ if city:
+ for i, (rr, cc) in enumerate(segs):
+ if cc == city:
+ city_row, lo = rr, rr
+ hi = segs[i + 1][0] if i + 1 < len(segs) else 10 ** 9
+ break
if namecol is not None:
- nc = colindex(namecol)
- anchors = [colindex(hits[k][0][1]) for k in ('鑷紑濮嬪缓璁剧疮璁�', '鑷勾鍒濈疮璁�', '鎬绘姇璧�') if k in hits]
+ nc = cols['椤圭洰鍚嶇О']
+ anchors = [cols[k] for k in ('鑷紑濮嬪缓璁剧疮璁�', '鑷勾鍒濈疮璁�', '鎬绘姇璧�') if k in cols]
for r in range(min(nrows, 800)):
+ if not (lo <= r + 1 < hi):
+ continue
a = norm(cell(r, 0))
name = norm(cell(r, nc))
is_group = bool(re.match(r'^(涓�|浜寍涓墊鍥泑浜攟鍏�)銆�', a + name)) \
@@ -154,8 +291,9 @@
data_start = data_start or r + 1
data_end = r + 1
project_rows += 1
- return dict(namecol=namecol, data_start=data_start, data_end=data_end,
+ return dict(namecol=namecol, cols=cols, data_start=data_start, data_end=data_end,
project_rows=project_rows, groups=groups[:6],
+ city=city or '', city_row=city_row or '', segments=segs[:8],
header_rows=sorted({h[0] for v in hits.values() for h in v}))
@@ -214,28 +352,34 @@
for f in files:
p = os.path.join(d, f)
rec = dict(鍝佺被=body.split('锛�')[0], 鏂囦欢=f, 宸ヤ綔琛�='', 琛ㄥご琛�='', 鏁版嵁璧�='', 鏁版嵁姝�='',
- 椤圭洰琛屾暟=0, 鍚嶇О鍒�='', 鍒嗙粍琛�='')
+ 椤圭洰琛屾暟=0, 鍚嶇О鍒�='', 鍒嗙粍琛�='', 甯傚窞='', 甯傚窞琛�='', 甯傚窞娈�='')
+ file_city = city_of_text(f)
try:
sel = pick_sheet(open_sheets(p), ym=ym)
if sel:
nm, nr, nc, cell, hits = sel
- pr = profile(cell, nr, nc, hits)
+ pr = profile(cell, nr, nc, hits, city=file_city,
+ merges=sheet_merges(p).get(nm, []))
rec.update(宸ヤ綔琛�=nm, 琛ㄥご琛�=','.join(map(str, pr['header_rows'])),
鏁版嵁璧�=pr['data_start'], 鏁版嵁姝�=pr['data_end'],
椤圭洰琛屾暟=pr['project_rows'], 鍚嶇О鍒�=pr['namecol'] or '',
- 鍒嗙粍琛�=';'.join('R%s:%s' % g for g in pr['groups']))
+ 鍒嗙粍琛�=';'.join('R%s:%s' % g for g in pr['groups']),
+ 甯傚窞=pr['city'] or (file_city or ''), 甯傚窞琛�=pr['city_row'],
+ 甯傚窞娈�=';'.join('R%s:%s' % s for s in pr['segments']))
for k in KEY:
- if k in hits:
- rec[k + '鍒�'] = hits[k][0][1]
+ if k in pr['cols']:
+ rec[k + '鍒�'] = colname(pr['cols'][k])
except Exception as e:
rec['閿欒'] = '%s: %s' % (type(e).__name__, e)
rows.append(rec)
- report.append(' %-46s sheet=%-12s 琛ㄥご=%-12s 鏁版嵁=%s~%s 椤圭洰琛�=%-3s 鍚嶇О鍒�=%s %s'
- % (rec['鏂囦欢'][:46], str(rec['宸ヤ綔琛�'])[:12], rec['琛ㄥご琛�'], rec['鏁版嵁璧�'],
+ report.append(' %-46s sheet=%-12s 甯傚窞=%s@R%s 琛ㄥご=%-12s 鏁版嵁=%s~%s 椤圭洰琛�=%-3s 鍚嶇О鍒�=%s %s'
+ % (rec['鏂囦欢'][:46], str(rec['宸ヤ綔琛�'])[:12], rec['甯傚窞'], rec['甯傚窞琛�'],
+ rec['琛ㄥご琛�'], rec['鏁版嵁璧�'],
rec['鏁版嵁姝�'], rec['椤圭洰琛屾暟'], rec['鍚嶇О鍒�'],
''.join('%s=%s ' % (k, rec.get(k + '鍒�', '-')) for k in KEY)
+ (' [%s]' % rec['閿欒'] if rec.get('閿欒') else '')))
- cols = ['鍝佺被', '鏂囦欢', '宸ヤ綔琛�', '琛ㄥご琛�', '鏁版嵁璧�', '鏁版嵁姝�', '椤圭洰琛屾暟', '鍚嶇О鍒�'] + [k + '鍒�' for k in KEY] + ['鍒嗙粍琛�', '閿欒']
+ cols = ['鍝佺被', '鏂囦欢', '宸ヤ綔琛�', '甯傚窞', '甯傚窞琛�', '甯傚窞娈�', '琛ㄥご琛�', '鏁版嵁璧�', '鏁版嵁姝�', '椤圭洰琛屾暟',
+ '鍚嶇О鍒�'] + [k + '鍒�' for k in KEY] + ['鍒嗙粍琛�', '閿欒']
tsv = os.path.join(out, '鏍煎紡鎺㈡祴_%s.tsv' % args.month)
with io.open(tsv, 'w', encoding='utf-8-sig') as fh:
fh.write('\t'.join(cols) + '\n')
--
Gitblit v1.9.1