# ===================== 替换说明 ===================== # 找到 class SettlementParseFunction(BaseFunction): 整个类全部删掉，用下面替换 # 文件顶部确认有：from docx import Document, from docx.oxml.ns import qn # ==================================================== import re class SettlementParseFunction(BaseFunction): FUND_COLS = [ '期初结存', '基础保证金', '出入金', '期末结存', '平仓盈亏', '质押金', '持仓盯市盈亏', '客户权益', '期权执行盈亏', '货币质押保证金占用', '手续费', '保证金占用', '行权手续费', '交割保证金', '交割手续费', '多头期权市值', '货币质入', '空头期权市值', '货币质出', '市值权益', '质押变化金额', '可用资金', '权利金收入', '风险度', '权利金支出', '应追加资金', '交割盈亏', '货币质押变化金额' ] DETAIL_COLS = [ '投资单元', '交易所', '交易编码', '品种', '合约', '开仓日期', '投/保', '买/卖', '持仓量', '开仓价格', '昨结算价', '结算价', '浮动盈亏', '盯市盈亏', '保证金', '期权市值' ] SUMMARY_COLS = [ '投资单元', '交易所', '交易编码', '品种', '合约', '买持', '买开仓均价', '卖持', '卖开仓均价', '昨结算价', '结算价', '持仓盯市盈亏', '保证金占用', '投/保', '多头期权市值', '空头期权市值' ] NOISE_KEYWORDS = [ '---', '买---Buy', '卖---Sell', 'Speculation', 'Hedge', 'Arbitrage', 'Market Maker', '能源中心---', '上期所---', '中金所---', '大商所---', '郑商所---', '广期所---', '持仓汇总 Positions', '持仓明细 Positions', '制表人', '第页', ] EXCHANGES = {'中金所', '大商所', '广期所', '能源中心', '郑商所', '上期所'} def __init__(self, log_func): super().__init__(name="结算单解析", log_func=log_func, category="做表", exec_button_text="解析并生成", allowed_extensions=('.docx', '.xlsx')) # =================== 值校验 =================== def _value_matches(self, value, col_name): v = value.strip().replace(' ', '') if not v or v in ('--', '—', '-'): return False if col_name == '交易所': return v in self.EXCHANGES if col_name == '品种': return bool(re.search(r'[\u4e00-\u9fff]', v)) and v not in self.EXCHANGES if col_name == '合约': return bool(re.match(r'^[a-zA-Z]+\d{3,}', v)) if col_name == '投/保': return bool(re.match(r'^[\u4e00-\u9fff]{2,3}$', v)) and v not in self.EXCHANGES if col_name == '买/卖': return v in ('买', '卖', 'Buy', 'Sell') if col_name in ('持仓量', '买持', '卖持'): return bool(re.match(r'^\d+$', v)) if col_name in ('投资单元', '交易编码'): return bool(re.match(r'^\d{1,8}$', v)) if col_name == '开仓日期': return bool(re.match(r'^\d{8}$', v)) if col_name in ('开仓价格', '昨结算价', '结算价', '买开仓均价', '卖开仓均价'): return bool(re.match(r'^[\d,]+\.\d{3}$', v)) if col_name in ('浮动盈亏', '盯市盈亏', '持仓盯市盈亏', '保证金', '期权市值', '保证金占用', '多头期权市值', '空头期权市值'): return bool(re.match(r'^-?[\d,]+\.?\d{0,2}$', v)) return False def _clean_value(self, value): if not value: return '' v = value.strip() v = re.sub(r'(\d),(\d{3})\. (\d)', r'\1,\2.\3', v) v = re.sub(r'(\d)\.(\d{2}) (\d)', r'\1.\2\3', v) v = re.sub(r'(\.\d)\s+(\d)', r'\1\2', v) v = re.sub(r'([\u4e00-\u9fff])\s+([\u4e00-\u9fff\d])', r'\1\2', v) v = v.replace('能源中 心', '能源中心') return v # =================== 大户报告：检查达标 =================== def _check_big_threshold(self, df_summary, contract_configs): """ 检查持仓汇总表中是否有合约达标。 contract_configs: [(合约代码, 限仓手数), ...] 返回: [(客户号, 合约代码, 方向, 保证金占用, 买持, 卖持), ...] """ results = [] for contract, limit in contract_configs: threshold = limit * 4 // 5 contract_lower = contract.lower() for _, row in df_summary.iterrows(): if str(row.get('合约', '')).strip().lower() != contract_lower: continue buy_qty = int(row.get('买持', 0) or 0) sell_qty = int(row.get('卖持', 0) or 0) margin = row.get('保证金占用', 0) try: margin = float(str(margin).replace(',', '').replace(' ', '')) except: margin = 0 if buy_qty >= threshold: results.append({ '客户号': str(row.get('交易编码', '')).strip(), '合约代码': str(row.get('合约', '')).strip(), '方向': '买', '持仓占用保证金': margin / max(buy_qty, sell_qty) if max(buy_qty, sell_qty) > 0 else 0, '买持': buy_qty, '卖持': sell_qty, '投资单元': str(row.get('投资单元', '')).strip(), }) if sell_qty >= threshold: results.append({ '客户号': str(row.get('交易编码', '')).strip(), '合约代码': str(row.get('合约', '')).strip(), '方向': '卖', '持仓占用保证金': margin * sell_qty / (buy_qty + sell_qty) if (buy_qty + sell_qty) > 0 else 0, '买持': buy_qty, '卖持': sell_qty, '投资单元': str(row.get('投资单元', '')).strip(), }) return results # =================== 大户报告：生成模板 =================== def _generate_big_report(self, df_fund, df_detail, df_summary, matched_rows, output_path): """根据达标数据生成大户报告批量导入模板""" from openpyxl import Workbook from openpyxl.styles import Alignment wb = Workbook() ws = wb.active ws.title = 'Sheet1' # 表头 headers = ['交易日期', '客户号', '合约代码', '持仓性质', '建仓时间', '持仓占用保证金（元）', '可动用资金（元）', '预报交割数量', '申请交割数量', '持仓意向', '资金来源说明'] ws.append(headers) for c in range(1, len(headers) + 1): ws.cell(1, c).alignment = Alignment(horizontal='center', vertical='center') # 日期 date_str = datetime.datetime.now().strftime('%Y-%m-%d') for mr in matched_rows: client_id = mr['客户号'] contract = mr['合约代码'] direction = mr['方向'] # 建仓时间：从明细表中找最后一个匹配的开仓日期 build_time = '' detail_filtered = df_detail[ (df_detail['交易编码'].astype(str).str.strip() == client_id) & (df_detail['合约'].astype(str).str.strip().str.lower() == contract.lower()) & (df_detail['买/卖'].astype(str).str.strip() == direction) ] if not detail_filtered.empty: build_time = str(detail_filtered.iloc[-1].get('开仓日期', '')).strip() # 可动用资金：从资金状况表中匹配客户号 usable_fund = 0 fund_filtered = df_fund[df_fund['客户号'].astype(str).str.strip() == client_id] if not fund_filtered.empty: try: usable_fund = float(str(fund_filtered.iloc[0].get('可用资金', 0)).replace(',', '').replace(' ', '')) except: usable_fund = 0 ws.append([ date_str, client_id, contract, '投机', build_time, round(mr['持仓占用保证金'], 2), round(usable_fund, 2), 0, 0, '投机', '自有资金', ]) wb.save(output_path) self.log(f"大户报告模板已生成: {output_path}，共 {len(matched_rows)} 条达标记录") # =================== 主执行入口 =================== def run(self, values): if not self.file_list: sg.popup_error("请先选择至少一个结算单文件！"); return settlement_files = [] big_report_file = None for f in self.file_list: if os.path.basename(f).startswith('大户报告批量导入'): big_report_file = f else: settlement_files.append(f) if not settlement_files: sg.popup_error("未选择任何结算单文件！"); return # 合约配置 contract_configs = [] if big_report_file: self.log("已检测到大户报告模板文件") contract_configs = self._get_contract_config_ui() if not contract_configs: self.log("已取消大户报告生成，仅生成结算单解析结果。") big_report_file = None timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S") output_dir = os.path.dirname(settlement_files[0]) success_count = 0 # 累积所有文件的解析结果（用于大户报告） all_funds = [] all_details = [] all_summaries = [] for file_path in settlement_files: try: base = os.path.splitext(os.path.basename(file_path))[0] out_path = os.path.join(output_dir, f"{base}_clean_{timestamp}.xlsx") self.log(f"正在解析：{file_path}") df_fund, df_detail, df_summary = self.parse_and_write_clean(file_path, out_path) self.log(f"✓ 已生成：{out_path}") success_count += 1 if big_report_file: all_funds.append(df_fund) all_details.append(df_detail) all_summaries.append(df_summary) except Exception as e: self.log(f"✕ 处理 {file_path} 失败：{e}") import traceback; self.log(traceback.format_exc()) # 生成大户报告 if big_report_file and success_count > 0 and contract_configs: import pandas as pd acc_fund = pd.concat(all_funds, ignore_index=True) if all_funds else pd.DataFrame() acc_detail = pd.concat(all_details, ignore_index=True) if all_details else pd.DataFrame() acc_summary = pd.concat(all_summaries, ignore_index=True) if all_summaries else pd.DataFrame() self.log(f"{'='*40}") self.log(f"检查大户达标（共{len(contract_configs)}组合约）:") for contract, limit in contract_configs: threshold = limit * 4 // 5 self.log(f" {contract}: 限仓{limit}手, 达标线{threshold}手") matched = self._check_big_threshold(acc_summary, contract_configs) if matched: self.log(f"共发现 {len(matched)} 条达标记录:") for mr in matched: self.log(f" {mr['客户号']} {mr['合约代码']} {mr['方向']} " f"(买持{mr['买持']}, 卖持{mr['卖持']})") big_out = os.path.join(output_dir, f"大户报告批量导入_{timestamp}.xlsx") self._generate_big_report(acc_fund, acc_detail, acc_summary, matched, big_out) self.log(f"✓ 已生成大户报告：{big_out}") else: self.log("未发现达标记录，不生成大户报告。") sg.popup(f"解析完成", f"成功处理 {success_count}/{len(settlement_files)} 个结算单文件。") # =================== UI：合约配置对话框 =================== # =================== UI：合约配置对话框 =================== def _get_contract_config_ui(self): """弹出UI让用户输入合约和限仓手数，返回 [(合约, 限仓手数), ...]""" MAX_PAIRS = 5 pairs = [] # [(合约, 限仓手数, 达标手数), ...] def refresh_listbox(): display = [f"{c} : {l} (达标:{t})" for c, l, t in pairs] window['-PAIRS-'].update(values=display) layout = [ [sg.Text("请配置合约和限仓手数（最多5对）：", font=("微软雅黑", 12))], [sg.Text("合约"), sg.Input(key='-CONTRACT-', size=(15, 1)), sg.Text("限仓手数"), sg.Input(key='-LIMIT-', size=(10, 1), enable_events=True), sg.Text("大户达标手数"), sg.Input(key='-THRESHOLD-', size=(10, 1), disabled=True, text_color='blue')], [sg.Button("添加对", key='-ADD_PAIR-'), sg.Button("删除选中", key='-DEL_PAIR-')], [sg.Listbox(values=[], size=(40, 6), key='-PAIRS-', select_mode='single')], [sg.Button("确定", key='-OK-'), sg.Button("取消", key='-CANCEL-')] ] window = sg.Window("配置大户报告条件", layout, finalize=True, modal=True) while True: event, values = window.read() if event in (sg.WINDOW_CLOSED, '-CANCEL-'): window.close() return None # 限仓手数实时计算达标手数 if event == '-LIMIT-': try: limit = int(values['-LIMIT-']) threshold = limit * 4 // 5 window['-THRESHOLD-'].update(str(threshold)) except ValueError: window['-THRESHOLD-'].update('') # 添加合约对 if event == '-ADD_PAIR-': contract = values['-CONTRACT-'].strip() limit_str = values['-LIMIT-'].strip() if not contract or not limit_str: sg.popup("请填写合约和限仓手数") continue try: limit = int(limit_str) except ValueError: sg.popup("限仓手数必须是整数") continue if limit <= 0: sg.popup("限仓手数必须大于0") continue if len(pairs) >= 5: sg.popup("最多只能添加5对") continue threshold = limit * 4 // 5 pairs.append((contract, limit, threshold)) refresh_listbox() window['-CONTRACT-'].update('') window['-LIMIT-'].update('') window['-THRESHOLD-'].update('') # 删除选中 if event == '-DEL_PAIR-': selected = values['-PAIRS-'] if not selected: sg.popup("请先选中要删除的对") continue display = [f"{c} : {l} (达标:{t})" for c, l, t in pairs] idx = display.index(selected[0]) pairs.pop(idx) refresh_listbox() # 确定 if event == '-OK-': if not pairs: sg.popup("至少添加一对合约") continue window.close() return [(c, l) for c, l, t in pairs] window.close() return [] # =================== 解析相关方法（不变） =================== def _parse_to_dataframes(self, docx_path): doc = Document(docx_path) tables = doc.tables paragraphs = doc.paragraphs body = doc.element.body children = list(body) elements = [] para_idx = table_idx = 0 for child in children: if child.tag.endswith('p'): elements.append(('para', para_idx)); para_idx += 1 elif child.tag.endswith('tbl'): elements.append(('table', table_idx)); table_idx += 1 title_positions = [] for idx, (typ, num) in enumerate(elements): if typ == 'para': if '交易结算单' in paragraphs[num].text and '盯市' in paragraphs[num].text: title_positions.append(idx) elif typ == 'table': found = False for row in tables[num].rows: for cell in row.cells: if '交易结算单' in cell.text and '盯市' in cell.text: title_positions.append(idx); found = True; break if found: break if not title_positions: self.log("未找到'交易结算单(盯市)'") return pd.DataFrame(), pd.DataFrame(), pd.DataFrame() customer_starts = [] for elem_idx in title_positions: cid = cname = '' for j in range(elem_idx, min(elem_idx + 15, len(elements))): typ, num = elements[j] text = '' if typ == 'para': text = paragraphs[num].text.strip() elif typ == 'table': for row in tables[num].rows: for cell in row.cells: text += cell.text.strip() + ' ' if not cid: m = re.search(r'客户号[：:]\s*(\d+)', text) if m: cid = m.group(1) if not cname: m2 = re.search(r'客户名称[：:]\s*(\S+)', text) if m2: cname = m2.group(1) if cid and cname: break customer_starts.append((elem_idx, cid, cname)) customer_starts.sort(key=lambda x: x[0]) all_fund, all_detail, all_summary = [], [], [] for i, (start_elem, cid, cname) in enumerate(customer_starts): end_elem = customer_starts[i+1][0] if i+1 < len(customer_starts) else len(elements) self.log(f"======== 客户 {cid} {cname} ========") fund_dict = {} all_detail_rows = [] all_summary_rows = [] table_info = [] j = start_elem while j < end_elem: typ, num = elements[j] if typ == 'para': j += 1; continue table = tables[num] if self._is_fund_table(table): self.log(f" [表{num}] 资金表 ✓") fund_dict.update(self._extract_fund_table(table)) table_info.append(f"表{num}:资金表") j += 1; continue raw_rows = self._get_xml_rows(table) if not raw_rows: j += 1; continue d_rows, s_rows, info = self._parse_mixed_table(raw_rows, num) all_detail_rows.extend(d_rows) all_summary_rows.extend(s_rows) table_info.append(info) j += 1 merged_summary = self._merge_summary_rows(all_summary_rows) self.log(f" 明细合计: {len(all_detail_rows)}行") self.log(f" 汇总合计: {len(merged_summary)}行") for ti in table_info: self.log(f" {ti}") if merged_summary: blank_cols = [] for col in self.SUMMARY_COLS: blanks = sum(1 for r in merged_summary if not r.get(col, '')) if blanks > 0: blank_cols.append(f"{col}({blanks}行空)") if blank_cols: self.log(f" ⚠ 汇总缺失: {', '.join(blank_cols)}") else: self.log(f" ✓ 汇总数据完整") if fund_dict: f = {k: v for k, v in fund_dict.items() if k in self.FUND_COLS} f['客户号'] = cid; f['客户名称'] = cname; all_fund.append(f) if all_detail_rows: all_detail.extend(all_detail_rows) if merged_summary: all_summary.extend(merged_summary) df_fund = pd.DataFrame(all_fund) df_detail = pd.DataFrame(all_detail) df_summary = pd.DataFrame(all_summary) self.log(f"======== 总计: 资金{len(df_fund)}行，明细{len(df_detail)}行，汇总{len(df_summary)}行 ========") return df_fund, df_detail, df_summary # ======================== XML行提取 ======================== def _get_xml_rows(self, table): rows_data = [] for r, tr in enumerate(table._tbl.findall(qn('w:tr'))): xml_cells = tr.findall(qn('w:tc')) parts = [] for tc in xml_cells: gs = tc.find('.//' + qn('w:gridSpan')) span = int(gs.get(qn('w:val'))) if gs is not None else 1 nested_tbl = tc.find(qn('w:tbl')) if nested_tbl is not None: nested_data = self._extract_nested_table(nested_tbl) if nested_data: parts.extend(nested_data) continue all_paragraphs = tc.findall(qn('w:p')) para_texts = [] for p in all_paragraphs: t_nodes = p.findall('.//' + qn('w:t')) tokens = [t.text or '' for t in t_nodes if t.text] if tokens: para_texts.append(''.join(tokens)) full_text = ' '.join(para_texts).strip() if span == 1: parts.append(full_text) else: parts.extend(self._split_merged_cell(full_text, span)) rows_data.append((parts, f"行{r}")) return rows_data def _extract_nested_table(self, nested_tbl): result = [] for tr in nested_tbl.findall(qn('w:tr')): row_vals = [] for tc in tr.findall(qn('w:tc')): t_nodes = tc.findall('.//' + qn('w:t')) text = ''.join([t.text or '' for t in t_nodes if t.text]).strip() row_vals.append(text) if any(v for v in row_vals): result.extend(row_vals) return result def _split_merged_cell(self, text, expected_count): if not text or expected_count <= 1: return [text] if expected_count == 1 else [''] * expected_count tokens = text.split() if len(tokens) >= expected_count: return tokens[:expected_count] return tokens + [''] * (expected_count - len(tokens)) if tokens else [''] * expected_count # ======================== 表格解析 ======================== def _parse_mixed_table(self, raw_rows, table_num=0): detail_rows = [] summary_rows = [] info = f"表{table_num}" if len(raw_rows) < 2: return detail_rows, summary_rows, f"{info}:行数不足跳过" table_type, header_idx = self._identify_table_type(raw_rows) if table_type is None: return detail_rows, summary_rows, f"{info}:未识别跳过" standard_cols = self.DETAIL_COLS if table_type == 'detail' else self.SUMMARY_COLS summary_start = None for check_idx in range(header_idx + 1, len(raw_rows)): cells = raw_rows[check_idx][0] combined = ' '.join(cells).replace('\n', ' ').replace(' ', '') if '持仓汇总' in combined or ('投资单元' in combined and '买持' in combined): summary_start = check_idx break if table_type == 'summary' and summary_start is None: summary_start = header_idx + 1 detail_end = summary_start if summary_start else len(raw_rows) def collect_data_rows(start, end): rows = [] for di in range(start, end): cells, src = raw_rows[di] if all(c == '' for c in cells): continue combined = ' '.join(cells) if '总计' in combined or '合计' in combined: continue if self._is_noise_row(cells): continue if self._is_noise_row(cells): continue has_english_header = False for c in cells: cc = c.replace(' ', '').lower() if cc in ('investunit', 'exchange', 'tradingcode', 'product', 'instrument', 'openprice', 'prev.sttl', 'sttltoday', 'mtmp/l', 'margin', 'marketval', 'position', 'b/s', 's/h', 'avgbuyprice', 'avgsellprice', 'longpos.', 'spos.', 'accum.p/l', 'marginoccupied', 'long', 'short'): has_english_header = True; break if has_english_header: continue cc_all = ''.join(c.replace(' ', '') for c in cells) if '投资单元' in cc_all and ('持仓量' in cc_all or '买持' in cc_all or '买/卖' in cc_all): continue non_empty = [c for c in cells if c.strip()] if len(non_empty) < 3: continue has_chinese = any(re.search(r'[\u4e00-\u9fff]', c) for c in cells if c.strip()) if not has_chinese: continue rows.append(cells) return rows if table_type == 'detail': d_data = collect_data_rows(header_idx + 1, detail_end) col_map = self._build_header_col_map(raw_rows, header_idx, standard_cols) for cells in d_data: row_dict = {} used_positions = set() for col in standard_cols: if col in col_map and col_map[col] < len(cells): val = self._clean_value(cells[col_map[col]]) if val and self._value_matches(val, col): row_dict[col] = val used_positions.add(col_map[col]) else: row_dict[col] = '' else: row_dict[col] = '' for col in standard_cols: if not row_dict[col]: for pos in range(len(cells)): if pos in used_positions: continue val = self._clean_value(cells[pos]) if val and self._value_matches(val, col): row_dict[col] = val used_positions.add(pos) break detail_rows.append(row_dict) if summary_start is not None: s_data = collect_data_rows(summary_start, len(raw_rows)) s_col_map = self._build_header_col_map(raw_rows, summary_start, self.SUMMARY_COLS) for cells in s_data: row_dict = {} used_positions = set() for col in self.SUMMARY_COLS: if col in s_col_map and s_col_map[col] < len(cells): val = self._clean_value(cells[s_col_map[col]]) if val and self._value_matches(val, col): row_dict[col] = val used_positions.add(s_col_map[col]) else: row_dict[col] = '' else: row_dict[col] = '' for col in self.SUMMARY_COLS: if not row_dict[col]: for pos in range(len(cells)): if pos in used_positions: continue val = self._clean_value(cells[pos]) if val and self._value_matches(val, col): row_dict[col] = val used_positions.add(pos) break summary_rows.append(row_dict) info = f"表{table_num}:明细{len(detail_rows)}行+汇总{len(summary_rows)}行" else: info = f"表{table_num}:{table_type} {len(detail_rows if table_type=='detail' else summary_rows)}行" return detail_rows, summary_rows, info def _build_header_col_map(self, raw_rows, header_idx, standard_cols): header_cells = raw_rows[header_idx][0] col_map = {} for i, ct in enumerate(header_cells): cc = ct.replace('\n', '').replace(' ', '').strip() if not cc: continue for std in standard_cols: std_clean = std.replace(' ', '') if std_clean in cc and std not in col_map: col_map[std] = i; break return col_map def _identify_table_type(self, raw_rows): if len(raw_rows) < 2: return None, -1 for ci in range(min(5, len(raw_rows))): c = ' '.join(raw_rows[ci][0]).replace('\n', ' ').replace(' ', '') if '投资单元' in c and ('买/卖' in c or 'B/S' in c or '持仓量' in c): return 'detail', ci if '投资单元' in c and ('买持' in c or 'LongPos' in c or '持仓汇总' in c): return 'summary', ci c = ' '.join(raw_rows[0][0]).replace('\n', ' ').replace(' ', '') for kw in ['买/卖', 'B/S', '持仓量']: if kw in c: return 'detail', 0 for kw in ['买持', '卖持', 'LongPos', 'SPos']: if kw.lower() in c.lower(): return 'summary', 0 return None, -1 def _is_noise_row(self, cells): combined = ' '.join(cells) for kw in self.NOISE_KEYWORDS: if kw in combined: return True non_empty = [c for c in cells if c.strip()] if len(non_empty) == 0: return True if len(non_empty) == 1 and re.match(r'^\d{1,6}$', non_empty[0].strip()): return True return False def _merge_summary_rows(self, summary_rows): if not summary_rows: return summary_rows groups = {} for row in summary_rows: key = (row.get('投资单元', ''), row.get('品种', ''), row.get('合约', '')) if key not in groups: groups[key] = [] groups[key].append(row) merged = [] for key, rows in groups.items(): if len(rows) == 1: merged.append(rows[0]); continue base = max(rows, key=lambda r: sum(1 for c in self.SUMMARY_COLS if r.get(c, ''))) for other in rows: if other is base: continue for col in self.SUMMARY_COLS: if not base.get(col) and other.get(col): base[col] = other[col] merged.append(base) return merged def _is_fund_table(self, table): if len(table.rows[0].cells) != 4: return False for row in table.rows[:3]: for cell in row.cells: if any(k in cell.text.strip() for k in ['投资单元', '持仓量', '买持', '卖持', 'LongPos']): return False ht = '' for row in table.rows[1:]: for cell in row.cells: ht += cell.text.strip() + ' ' return any(k in ht for k in ['期初结存', '期末结存', '平仓盈亏', '持仓盯市盈亏']) def _extract_fund_table(self, table): data = {} for r, row in enumerate(table.rows): cells = row.cells if len(cells) < 2: continue ft = cells[0].text.strip() if r == 0 and ('制表日期' in ft or '交易结算单' in ft): continue if '资金状况' in ft or 'Account Summary' in ft: continue for i in range(0, len(cells) - 1, 2): key = ''.join(re.findall(r'[\u4e00-\u9fff]+', cells[i].text.strip())) val = cells[i + 1].text.strip() if key and val: data[key] = val return data def parse_and_write_clean(self, input_path, output_path): df_fund, df_detail, df_summary = self._parse_to_dataframes(input_path) if '客户号' not in df_fund.columns: df_fund['客户号'] = '' if '客户名称' not in df_fund.columns: df_fund['客户名称'] = '' for col in self.FUND_COLS: if col not in df_fund.columns: df_fund[col] = '' df_fund = df_fund[['客户号', '客户名称'] + self.FUND_COLS] for col in self.DETAIL_COLS: if col not in df_detail.columns: df_detail[col] = '' df_detail = df_detail[self.DETAIL_COLS] for col in self.SUMMARY_COLS: if col not in df_summary.columns: df_summary[col] = '' df_summary = df_summary[self.SUMMARY_COLS] with pd.ExcelWriter(output_path, engine='openpyxl') as w: df_fund.to_excel(w, sheet_name='资金状况', index=False) df_detail.to_excel(w, sheet_name='持仓明细', index=False) df_summary.to_excel(w, sheet_name='持仓汇总', index=False) return df_fund, df_detail, df_summary