165 lines
6.2 KiB
Python
165 lines
6.2 KiB
Python
import openpyxl
|
||
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
|
||
from openpyxl.utils import get_column_letter
|
||
from collections import defaultdict
|
||
|
||
FP = '/Users/kkfluous/Downloads/租赁任务考核_2026年5月.xlsx'
|
||
|
||
def num(x):
|
||
try: return float(x or 0)
|
||
except: return 0.0
|
||
|
||
# 读数据
|
||
wb_r = openpyxl.load_workbook(FP, data_only=True)
|
||
ws_r = wb_r['业务考核视图']
|
||
h = [c.value for c in next(ws_r.iter_rows(min_row=1, max_row=1))]
|
||
recs = [dict(zip(h, row)) for row in ws_r.iter_rows(min_row=2, values_only=True) if row[0]]
|
||
|
||
# 备份仪表盘
|
||
import os
|
||
bak = '/Users/kkfluous/Downloads/租赁任务考核_2026年5月_原版备份.xlsx'
|
||
bak_map = {}
|
||
if os.path.exists(bak):
|
||
wb_b = openpyxl.load_workbook(bak, data_only=True)
|
||
for row in wb_b['业务考核视图'].iter_rows(min_row=2, values_only=True):
|
||
p = str(row[0]).strip() if row[0] else ''
|
||
bak_map[p] = num(row[14])
|
||
wb_b.close()
|
||
|
||
test_recs = [r for r in recs if num(r.get('测试里程(km)')) > 0]
|
||
|
||
flipped = []
|
||
for r in test_recs:
|
||
p = str(r['车牌号']).strip()
|
||
old_km = bak_map.get(p, 0)
|
||
new_km = num(r.get('实际行驶里程(km)'))
|
||
t = num(r.get('应考核里程(km)'))
|
||
if t <= 0: continue
|
||
if old_km >= t and new_km < t:
|
||
flipped.append(r)
|
||
|
||
# 按车型+部门+销售分组
|
||
by_target = defaultdict(lambda: defaultdict(lambda: defaultdict(int)))
|
||
for r in flipped:
|
||
tgt = r.get('考核目标', '未知车型')
|
||
dept = r.get('部门名称', '')
|
||
sales = r.get('销售经理', '')
|
||
by_target[tgt][dept][sales] += 1
|
||
|
||
wb_r.close()
|
||
|
||
# 写sheet
|
||
wb = openpyxl.load_workbook(FP)
|
||
ws = wb['测试与考核情况说明']
|
||
|
||
SUB = PatternFill('solid', fgColor='D9E1F2')
|
||
HEAD = PatternFill('solid', fgColor='4472C4')
|
||
ALT = PatternFill('solid', fgColor='F2F2F2')
|
||
THIN = Side('thin', color='B4B4B4')
|
||
BD = Border(THIN, THIN, THIN, THIN)
|
||
CC = Alignment(horizontal='center', vertical='center', wrap_text=True)
|
||
LL = Alignment(horizontal='left', vertical='center', wrap_text=True)
|
||
|
||
# 找到section 二所在的行
|
||
section2_row = None
|
||
for r in range(1, ws.max_row + 1):
|
||
v = ws.cell(row=r, column=1).value
|
||
if v and '二、测试车辆考核达标情况说明' in str(v):
|
||
section2_row = r
|
||
break
|
||
|
||
if section2_row:
|
||
# 删除section 二及之后的所有行（保留数据重新写）
|
||
# 找到从section2_row+1开始到section三之前的所有行
|
||
section3_row = None
|
||
for r in range(section2_row + 1, ws.max_row + 1):
|
||
v = ws.cell(row=r, column=1).value
|
||
if v and '三、里程' in str(v):
|
||
section3_row = r
|
||
break
|
||
if not section3_row:
|
||
section3_row = ws.max_row + 1
|
||
|
||
# 清除section2+1 到 section3-1 的内容
|
||
for r in range(section2_row + 1, section3_row):
|
||
for c in range(1, 7):
|
||
ws.cell(row=r, column=c).value = None
|
||
ws.cell(row=r, column=c).fill = PatternFill()
|
||
|
||
r = section2_row + 1
|
||
|
||
# 描述行
|
||
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=6)
|
||
ws.cell(row=r, column=1,
|
||
value=f'共{len(test_recs)}条考核车辆涉及测试；扣减其测试里程后，共 {len(flipped)} 条考核车辆记录由"达标"转判为"未达标"。')
|
||
ws.cell(row=r, column=1).font = Font(size=10); ws.cell(row=r, column=1).alignment = LL
|
||
ws.row_dimensions[r].height = 28
|
||
r += 2
|
||
|
||
# 按车型展示，多车型一行
|
||
targets_sorted = sorted(by_target.keys())
|
||
triples = [targets_sorted[i:i+2] for i in range(0, len(targets_sorted), 2)]
|
||
|
||
for target_group in triples:
|
||
for ti, tgt in enumerate(target_group):
|
||
dept_data = by_target[tgt]
|
||
dept_order = sorted(dept_data.keys(), key=lambda d: -sum(dept_data[d].values()))
|
||
total_flip = sum(sum(s.values()) for s in dept_data.values())
|
||
|
||
base_col = ti * 3 + 1 # 每车型占3列(车型名|部门-销售|数量)
|
||
|
||
# 车型标题行
|
||
ws.merge_cells(start_row=r, start_column=base_col, end_row=r, end_column=base_col+2)
|
||
ws.cell(row=r, column=base_col, value=f'{tgt}（{total_flip}辆）').fill = SUB
|
||
ws.cell(row=r, column=base_col).font = Font(bold=True, color='1F4E78')
|
||
ws.cell(row=r, column=base_col).alignment = CC
|
||
for j in range(base_col, base_col+3):
|
||
ws.cell(row=r, column=j).border = BD
|
||
r += 1
|
||
|
||
# 部门-销售-数量表头
|
||
for j, hh in enumerate(['部门', '销售经理', '数量'], base_col):
|
||
ws.cell(row=r, column=j, value=hh).fill = HEAD
|
||
ws.cell(row=r, column=j).font = Font(bold=True, color='FFFFFF')
|
||
ws.cell(row=r, column=j).alignment = CC
|
||
ws.cell(row=r, column=j).border = BD
|
||
r += 1
|
||
|
||
# 数据行
|
||
row_idx = 0
|
||
for dept in dept_order:
|
||
for sales in sorted(dept_data[dept].keys()):
|
||
cnt = dept_data[dept][sales]
|
||
ws.cell(row=r, column=base_col, value=dept).alignment = CC
|
||
ws.cell(row=r, column=base_col+1, value=sales).alignment = CC
|
||
ws.cell(row=r, column=base_col+2, value=cnt).alignment = CC
|
||
for j in range(base_col, base_col+3):
|
||
ws.cell(row=r, column=j).border = BD
|
||
if row_idx % 2 == 1:
|
||
ws.cell(row=r, column=j).fill = ALT
|
||
ws.row_dimensions[r].height = 22
|
||
r += 1
|
||
row_idx += 1
|
||
# 小计行
|
||
ws.cell(row=r, column=base_col, value='合计').font = Font(bold=True)
|
||
ws.cell(row=r, column=base_col).alignment = CC
|
||
ws.cell(row=r, column=base_col+2, value=total_flip).font = Font(bold=True)
|
||
ws.cell(row=r, column=base_col+2).alignment = CC
|
||
for j in range(base_col, base_col+3):
|
||
ws.cell(row=r, column=j).fill = SUB
|
||
ws.cell(row=r, column=j).border = BD
|
||
r += 2
|
||
|
||
# 跳到下一个triple的起始行
|
||
# Find max row reached in this triplet
|
||
pass # r already at right position
|
||
|
||
r += 1
|
||
|
||
# 列宽
|
||
for i, w in enumerate([18, 22, 22, 50, 18, 18], 1):
|
||
ws.column_dimensions[get_column_letter(i)].width = w
|
||
|
||
wb.save(FP)
|
||
print(f'✅ 更新完成')
|