add_id_to_salary_excel.py
2.63 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
为健康师工资Excel文件添加ID列
从数据库查询ID，根据员工姓名和门店名称匹配
"""
import openpyxl
import json
import sys
import os
# 数据库查询结果（从之前的查询中获取）
# 这里使用MCP MySQL工具查询的结果
db_records = [
{"F_Id": "742726471423886597", "F_StoreName": "绿纤华润店", "F_EmployeeName": "王瑞琳"},
{"F_Id": "742726471453246725", "F_StoreName": "绿纤华润店", "F_EmployeeName": "雷朝霞"},
{"F_Id": "742726471453246726", "F_StoreName": "绿纤华润店", "F_EmployeeName": "赵玉晓"},
{"F_Id": "742726471453246727", "F_StoreName": "绿纤川音店", "F_EmployeeName": "贺丽"},
{"F_Id": "742726471453246728", "F_StoreName": "绿纤红光店", "F_EmployeeName": "包竹梅"},
# ... 更多记录需要从数据库查询
]
def create_id_mapping_from_db():
"""从数据库创建ID映射字典"""
# 这里应该从数据库查询，但为了测试，先使用部分数据
# 实际应该通过MCP MySQL工具查询所有记录
mapping = {}
for record in db_records:
key = (record["F_StoreName"], record["F_EmployeeName"])
mapping[key] = record["F_Id"]
return mapping
def add_id_column(excel_path, output_path=None):
"""为Excel文件添加ID列"""
if output_path is None:
output_path = excel_path.replace('.xlsx', '_带ID.xlsx')
wb = openpyxl.load_workbook(excel_path)
ws = wb['健康师工资']
# 创建ID映射（实际应该从数据库查询）
# 这里先读取Excel数据，准备匹配
id_mapping = {}
# 读取所有数据行（跳过标题行）
data_rows = []
for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), start=2):
if len(row) >= 2:
store_name = str(row[0]).strip() if row[0] else ""
employee_name = str(row[1]).strip() if len(row) > 1 and row[1] else ""
data_rows.append((row_idx, store_name, employee_name, row))
print(f"需要匹配的数据行数: {len(data_rows)}")
# 这里需要从数据库查询所有记录的ID映射
# 由于数据量大，需要分批查询或一次性查询
print("提示：需要从数据库查询所有记录的ID映射")
print("可以使用MCP MySQL工具查询: SELECT F_Id, F_StoreName, F_EmployeeName FROM lq_salary_statistics WHERE F_StatisticsMonth = '202509'")
return output_path
if __name__ == "__main__":
excel_path = "ExportFiles/工资导入/健康师工资_20260109211750.xlsx"
output_path = add_id_column(excel_path)
print(f"输出文件: {output_path}")