generate_november_customer_excel.py
4.71 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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
生成2025年12月客户开单数据Excel文件
包含字段：客户档案号、姓名、注册时间、来源、开单业绩、时间、归属门店
"""
import pymysql
import pandas as pd
from datetime import datetime
import os
# 数据库配置（从appsettings.json读取）
DB_CONFIG = {
'host': 'rm-2vccze142rc9a8f58bo.mysql.cn-chengdu.rds.aliyuncs.com',
'port': 3306,
'user': 'lvqiansql',
'password': 'LvQ1@n!20251211',
'database': 'lqerp_dev',
'charset': 'utf8mb4'
}
def query_data():
"""查询2025年12月的客户开单数据"""
connection = None
try:
connection = pymysql.connect(**DB_CONFIG)
sql = """
SELECT
COALESCE(kh.dah, '') AS 客户档案号,
COALESCE(kh.khmc, '') AS 姓名,
kh.zcsj AS 注册时间,
COALESCE(kd.khly, '') AS 来源,
CAST(kd.sfyj AS DECIMAL(18,2)) AS 开单业绩,
kd.kdrq AS 时间,
COALESCE(md.dm, '') AS 归属门店
FROM lq_kd_kdjlb kd
INNER JOIN lq_khxx kh ON kd.kdhy = kh.F_Id
LEFT JOIN lq_mdxx md ON kh.gsmd = md.F_Id
WHERE kd.F_IsEffective = 1
AND kh.F_IsEffective = 1
AND YEAR(kd.kdrq) = 2025
AND MONTH(kd.kdrq) = 12
ORDER BY kd.kdrq DESC
"""
df = pd.read_sql(sql, connection)
# 格式化时间字段
if '注册时间' in df.columns:
df['注册时间'] = pd.to_datetime(df['注册时间']).dt.strftime('%Y-%m-%d %H:%M:%S')
df['注册时间'] = df['注册时间'].replace('NaT', '')
if '时间' in df.columns:
df['时间'] = pd.to_datetime(df['时间']).dt.strftime('%Y-%m-%d %H:%M:%S')
# 确保开单业绩为数值类型
df['开单业绩'] = pd.to_numeric(df['开单业绩'], errors='coerce').fillna(0)
return df
except Exception as e:
print(f"查询数据时发生错误: {e}")
raise
finally:
if connection:
connection.close()
def generate_excel(df, output_file='2025年12月客户开单数据.xlsx'):
"""生成Excel文件"""
try:
# 创建Excel写入器
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='客户开单数据', index=False)
# 获取工作表对象进行格式设置
worksheet = writer.sheets['客户开单数据']
# 设置列宽
column_widths = {
'A': 20, # 客户档案号
'B': 15, # 姓名
'C': 20, # 注册时间
'D': 15, # 来源
'E': 15, # 开单业绩
'F': 20, # 时间
'G': 20 # 归属门店
}
for col, width in column_widths.items():
worksheet.column_dimensions[col].width = width
# 设置表头样式
from openpyxl.styles import Font, Alignment, PatternFill
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
header_font = Font(bold=True, color='FFFFFF', size=11)
for cell in worksheet[1]:
cell.fill = header_fill
cell.font = header_font
cell.alignment = Alignment(horizontal='center', vertical='center')
# 设置数据对齐方式
for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row):
for cell in row:
if cell.column_letter == 'E': # 开单业绩列右对齐
cell.alignment = Alignment(horizontal='right', vertical='center')
else:
cell.alignment = Alignment(horizontal='left', vertical='center')
print(f"Excel文件已生成: {output_file}")
print(f"共 {len(df)} 条记录")
return output_file
except Exception as e:
print(f"生成Excel文件时发生错误: {e}")
raise
def main():
"""主函数"""
print("开始查询2025年12月客户开单数据...")
df = query_data()
if df.empty:
print("未查询到数据")
return
print(f"查询到 {len(df)} 条记录")
print("\n前5条数据预览:")
print(df.head())
# 生成Excel文件
output_file = f"2025年12月客户开单数据_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
generate_excel(df, output_file)
print(f"\n文件保存路径: {os.path.abspath(output_file)}")
if __name__ == '__main__':
main()