import json from datetime import datetime, timezone, timedelta import tkinter as tk from tkinter import ttk, messagebox, filedialog import pandas as pd import os from threading import Thread def parse_records_from_text(json_text): """ 从粘贴的JSON文本中解析记录数据 """ try: # 1. 解析外层JSON if isinstance(json_text, dict): data = json_text else: data = json.loads(json_text) # 获取records字段 records_str = data.get('data', {}).get('records', '') if not records_str: return {'error': 'records字段为空', 'records': []} # 解析内层records JSON records_data = json.loads(records_str) record_map = records_data.get('recordMap', {}) if not record_map: return {'error': 'recordMap为空', 'records': []} # 获取fieldMap formula_info = records_data.get('formulaInfo', {}) field_map = formula_info.get('fieldMap', {}) # 解析所有记录 results = [] for record_key, record in record_map.items(): # 提取手机号 phone = None if 'fld0af1E3B' in record: phone_data = record['fld0af1E3B'].get('value', {}) if phone_data and isinstance(phone_data, dict): phone = phone_data.get('fullPhoneNum') # 提取批量数据编码 batch_code = None if 'fldbQDk5b7' in record: values = record['fldbQDk5b7'].get('value', []) if values and len(values) > 0: batch_code = values[0].get('number') # 提取有顾问跟进 has_advisor = None if 'fldSiIb9nZ' in field_map: field_value = field_map['fldSiIb9nZ'].get(record_key, {}).get('value') if field_value: try: field_data = json.loads(field_value) if field_data.get('data') and len(field_data['data']) > 0: has_advisor = field_data['data'][0] except: pass # 提取批量创建时间 batch_create_time = None if 'fldlzQO4tZ' in field_map: field_value = field_map['fldlzQO4tZ'].get(record_key, {}).get('value') if field_value: try: field_data = json.loads(field_value) if field_data.get('data') and len(field_data['data']) > 0: timestamp_data = field_data['data'][0] if isinstance(timestamp_data, dict) and 'value' in timestamp_data: timestamp_ms = timestamp_data['value'] if timestamp_ms and isinstance(timestamp_ms, (int, float)): if timestamp_ms > 1000000000000: timestamp_sec = timestamp_ms / 1000 else: timestamp_sec = timestamp_ms dt = datetime.fromtimestamp(timestamp_sec, tz=timezone(timedelta(hours=8))) batch_create_time = dt.strftime('%Y-%m-%d %H:%M:%S') except: pass # 提取批量创建人员 batch_creator = None if 'fldm8odlu2' in field_map: field_value = field_map['fldm8odlu2'].get(record_key, {}).get('value') if field_value: try: field_data = json.loads(field_value) if field_data.get('data') and len(field_data['data']) > 0: batch_creator = field_data['data'][0] except: pass # 提取创建人姓名（从recordMeta） created_user = None if 'recordMeta' in records_data: meta = records_data['recordMeta'].get(record_key, {}).get('recMeta', {}) if 'createdUser' in meta: # 这里存储的是用户ID，可以尝试从userMap获取姓名 user_map = records_data.get('userMap', {}) user_info = user_map.get(meta['createdUser'], {}) if user_info: created_user = user_info.get('name') or user_info.get('enName') if not created_user: created_user = meta['createdUser'] results.append({ '手机号': phone or '无数据', '批量数据编码': batch_code or '无数据', '有顾问跟进': has_advisor or '未知', '批量创建时间': batch_create_time or '无数据', '批量创建人员': batch_creator or '无数据', '创建人': created_user or '无数据' }) # 映射显示名称 advisor_map = { 'optXaXhoOh': '有顾问跟进', 'optJdspBAs': '无顾问跟进' } for record in results: if record['有顾问跟进'] in advisor_map: record['有顾问跟进'] = advisor_map[record['有顾问跟进']] return {'error': None, 'records': results, 'count': len(results)} except json.JSONDecodeError as e: return {'error': f'JSON解析错误: {str(e)}', 'records': []} except Exception as e: return {'error': f'处理错误: {str(e)}', 'records': []} def parse_and_display(): """解析粘贴的JSON数据并显示在表格中""" json_text = text_input.get("1.0", tk.END).strip() if not json_text: messagebox.showwarning("提示", "请粘贴JSON数据！") return # 禁用按钮 btn_parse.config(state='disabled') btn_parse.config(text='解析中...') btn_export.config(state='disabled') # 清空表格 tree.delete(*tree.get_children()) # 更新状态 label_status.config(text="正在解析数据...", foreground='blue') root.update() def do_parse(): try: result = parse_records_from_text(json_text) if result.get('error'): root.after(0, lambda: messagebox.showerror("错误", f"解析失败: {result['error']}")) root.after(0, lambda: label_status.config(text="解析失败", foreground='red')) else: records = result.get('records', []) count = result.get('count', 0) # 显示在表格中 for record in records: root.after(0, lambda r=record: tree.insert('', 'end', values=( r['手机号'], r['批量数据编码'], r['有顾问跟进'], r['批量创建时间'], r['批量创建人员'] ))) root.update() # 保存结果到内存，供导出使用 global last_results last_results = records # 更新状态 root.after(0, lambda: label_status.config( text=f"解析完成！共 {count} 条记录", foreground='green')) root.after(0, lambda: btn_export.config(state='normal')) if count == 0: root.after(0, lambda: messagebox.showinfo("提示", "未解析到任何记录")) except Exception as e: root.after(0, lambda: messagebox.showerror("错误", f"解析失败: {str(e)}")) root.after(0, lambda: label_status.config(text="解析失败", foreground='red')) finally: root.after(0, lambda: btn_parse.config(state='normal')) root.after(0, lambda: btn_parse.config(text='解析数据')) Thread(target=do_parse, daemon=True).start() def export_to_excel(): """导出结果到Excel""" if not tree.get_children(): messagebox.showwarning("提示", "没有数据可导出，请先解析数据！") return # 获取表格数据 data = [] for item in tree.get_children(): values = tree.item(item)['values'] data.append({ '手机号': values[0], '批量数据编码': values[1], '有顾问跟进': values[2], '批量创建时间': values[3], '批量创建人员': values[4] }) if not data: messagebox.showwarning("提示", "没有数据可导出！") return # 选择保存位置 file_path = filedialog.asksaveasfilename( defaultextension=".xlsx", filetypes=[("Excel文件", "*.xlsx"), ("CSV文件", "*.csv")], title="保存数据", initialfile=f"飞书数据_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx" ) if not file_path: return try: df = pd.DataFrame(data) df.to_excel(file_path, index=False, engine='openpyxl') messagebox.showinfo("成功", f"数据已导出到：\n{file_path}") label_status.config(text=f"已导出到：{os.path.basename(file_path)}", foreground='green') except Exception as e: messagebox.showerror("错误", f"导出失败: {str(e)}") def clear_all(): """清空所有""" text_input.delete("1.0", tk.END) tree.delete(*tree.get_children()) label_status.config(text="已清空", foreground='gray') btn_export.config(state='disabled') global last_results last_results = [] def load_example(): """加载示例数据""" example_text = """{"code":0,"msg":"","data":{"records":"{\\"tableID\\":\\"tblXQQeIwMqSFSME\\",\\"recordMap\\":{\\"recvtyUtg2C0Xl\\":{\\"fld0af1E3B\\":{\\"value\\":{\\"fullPhoneNum\\":\\"18616756877\\"}},\\"fldbQDk5b7\\":{\\"value\\":[{\\"number\\":\\"SHPLSJ202608281532312\\"}]}},\\"recvtyUtg2Z6yf\\":{\\"fld0af1E3B\\":{\\"value\\":{\\"fullPhoneNum\\":\\"13918255747\\"}},\\"fldbQDk5b7\\":{\\"value\\":[{\\"number\\":\\"SHPLSJ202608281532385\\"}]}}},\\"formulaInfo\\":{\\"fieldMap\\":{\\"fldSiIb9nZ\\":{\\"recvtyUtg2C0Xl\\":{\\"value\\":\\"{\\\\\\"bus_type\\\\\\":[3],\\\\\\"data\\\\\\":[\\\\\\"optJdspBAs\\\\\\"]}\\"},\\"recvtyUtg2Z6yf\\":{\\"value\\":\\"{\\\\\\"bus_type\\\\\\":[3],\\\\\\"data\\\\\\":[\\\\\\"optJdspBAs\\\\\\"]}\\"}},\\"fldm8odlu2\\":{\\"recvtyUtg2C0Xl\\":{\\"value\\":\\"{\\\\\\"bus_type\\\\\\":[201],\\\\\\"data\\\\\\":[\\\\\\"/\\\\\\"]}\\"},\\"recvtyUtg2Z6yf\\":{\\"value\\":\\"{\\\\\\"bus_type\\\\\\":[201],\\\\\\"data\\\\\\":[\\\\\\"/\\\\\\"]}\\"}},\\"fldlzQO4tZ\\":{\\"recvtyUtg2C0Xl\\":{\\"value\\":\\"{\\\\\\"bus_type\\\\\\":[5],\\\\\\"data\\\\\\":[null]}\\"},\\"recvtyUtg2Z6yf\\":{\\"value\\":\\"{\\\\\\"bus_type\\\\\\":[5],\\\\\\"data\\\\\\":[{\\\\\\"value\\\\\\":1787082110000}]}\\"}}}},\\"recordMeta\\":{\\"recvtyUtg2C0Xl\\":{\\"recMeta\\":{\\"createdUser\\":\\"7646228072081099729\\"}},\\"recvtyUtg2Z6yf\\":{\\"recMeta\\":{\\"createdUser\\":\\"7646228072081099729\\"}}}}}"}}""" text_input.delete("1.0", tk.END) text_input.insert("1.0", example_text) label_status.config(text="已加载示例数据，点击「解析数据」查看结果", foreground='blue') # 创建GUI窗口 root = tk.Tk() root.title("飞书JSON数据解析工具") root.geometry("950x700") root.resizable(False, False) # 设置样式 style = ttk.Style() style.theme_use('clam') # 顶部：输入区域 frame_top = ttk.LabelFrame(root, text="粘贴飞书API返回的JSON数据", padding="10") frame_top.pack(fill='x', padx=10, pady=5) text_input = tk.Text(frame_top, font=('Consolas', 9), height=10, wrap='none') text_input.pack(fill='x', expand=True) # 添加水平滚动条 scrollbar_h = ttk.Scrollbar(frame_top, orient='horizontal', command=text_input.xview) text_input.configure(xscrollcommand=scrollbar_h.set) scrollbar_h.pack(fill='x') # 提示和按钮区域 hint_frame = ttk.Frame(frame_top) hint_frame.pack(fill='x', pady=(5, 0)) ttk.Label(hint_frame, text="提示：粘贴完整的API返回JSON（包含data.records字段）", font=('微软雅黑', 9), foreground='gray').pack(side='left') btn_frame = ttk.Frame(frame_top) btn_frame.pack(fill='x', pady=(5, 0)) btn_parse = ttk.Button(btn_frame, text="解析数据", command=parse_and_display, width=12) btn_parse.pack(side='left', padx=(0, 10)) btn_example = ttk.Button(btn_frame, text="加载示例", command=load_example, width=10) btn_example.pack(side='left', padx=(0, 10)) btn_clear = ttk.Button(btn_frame, text="清空", command=clear_all, width=10) btn_clear.pack(side='left', padx=(0, 10)) btn_export = ttk.Button(btn_frame, text="导出Excel", command=export_to_excel, width=12, state='disabled') btn_export.pack(side='left') # 中间：结果显示表格 frame_table = ttk.LabelFrame(root, text="解析结果", padding="10") frame_table.pack(fill='both', expand=True, padx=10, pady=5) # 创建Treeview columns = ('手机号', '批量数据编码', '有顾问跟进', '批量创建时间', '批量创建人员') tree = ttk.Treeview(frame_table, columns=columns, show='headings', height=15) # 设置列标题和宽度 tree.heading('手机号', text='手机号') tree.heading('批量数据编码', text='批量数据编码') tree.heading('有顾问跟进', text='有顾问跟进') tree.heading('批量创建时间', text='批量创建时间') tree.heading('批量创建人员', text='批量创建人员') tree.column('手机号', width=150, anchor='center') tree.column('批量数据编码', width=200, anchor='center') tree.column('有顾问跟进', width=150, anchor='center') tree.column('批量创建时间', width=180, anchor='center') tree.column('批量创建人员', width=180, anchor='center') # 添加滚动条 scrollbar = ttk.Scrollbar(frame_table, orient='vertical', command=tree.yview) tree.configure(yscrollcommand=scrollbar.set) tree.pack(side='left', fill='both', expand=True) scrollbar.pack(side='right', fill='y') # 底部：状态栏 frame_bottom = ttk.Frame(root, padding="5") frame_bottom.pack(fill='x') label_status = ttk.Label(frame_bottom, text="请粘贴JSON数据，点击「解析数据」", font=('微软雅黑', 9), foreground='gray') label_status.pack(side='left') # 存储最近一次查询结果（用于导出） last_results = [] # 运行主循环 root.mainloop()