From 73a05513ffaac43e440734aa8fffeea953563741 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期四, 17 九月 2026 14:14:44 +0800
Subject: [PATCH] chore(投资)：入库用户整理后的输入/模板/输出文件 + 回填口径确认（用户 2026-09-17 答复）
---
docs/投资/工具/格式探测.py | 66 ++++++++++++++++++++++++++++----
1 files changed, 57 insertions(+), 9 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..1f2cac8 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 '')
@@ -80,7 +107,10 @@
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 +154,29 @@
return bool(re.match(r'^-?\d+(\.\d+)?$', norm(v)))
-def profile(cell, nrows, ncols, hits):
+def profile(cell, nrows, ncols, hits, city=None):
namecol = None
if '椤圭洰鍚嶇О' in hits:
namecol = hits['椤圭洰鍚嶇О'][0][1]
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]
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)) \
@@ -156,6 +198,7 @@
project_rows += 1
return dict(namecol=namecol, 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 +257,33 @@
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)
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]
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