328 lines
13 KiB
Python
328 lines
13 KiB
Python
#!/usr/bin/env python3
|
|
"""Generate 租赁任务考核_2026年5月_干预情况说明.xlsx based on updated source data."""
|
|
import os, openpyxl
|
|
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
|
|
from openpyxl.utils import get_column_letter
|
|
from collections import defaultdict
|
|
from copy import copy
|
|
|
|
os.chdir('/Users/kkfluous/Downloads')
|
|
|
|
SRC = '租赁任务考核_2026年5月.xlsx'
|
|
BAK = '租赁任务考核_2026年5月_原版备份.xlsx'
|
|
|
|
# ── Read source ──
|
|
wb_s = openpyxl.load_workbook(SRC, data_only=True)
|
|
ws_s = wb_s['业务考核视图']
|
|
src_headers = [c.value for c in next(ws_s.iter_rows(min_row=1, max_row=1))]
|
|
src_data = list(ws_s.iter_rows(min_row=2, values_only=True))
|
|
wb_s.close()
|
|
|
|
# ── Read backup ──
|
|
wb_b = openpyxl.load_workbook(BAK, data_only=True)
|
|
ws_b = wb_b['业务考核视图']
|
|
bak_data = list(ws_b.iter_rows(min_row=2, values_only=True))
|
|
wb_b.close()
|
|
|
|
print(f"Source: {len(src_data)} records")
|
|
print(f"Backup: {len(bak_data)} records")
|
|
|
|
# ── Compare ──
|
|
# Build plate -> row map for backup (keep all, including duplicates)
|
|
def make_key(row):
|
|
plate = str(row[0]).strip() if row[0] else ''
|
|
sales = str(row[4]).strip() if len(row) > 4 and row[4] else ''
|
|
target = str(row[9]).strip() if len(row) > 9 and row[9] else ''
|
|
return (plate, sales, target)
|
|
|
|
bak_map = {}
|
|
for r in bak_data:
|
|
k = make_key(r)
|
|
bak_map[k] = r
|
|
|
|
src_map = {}
|
|
for r in src_data:
|
|
k = make_key(r)
|
|
src_map[k] = r
|
|
|
|
# Find new/changed/removed
|
|
new_records = []
|
|
changed_records = []
|
|
removed_records = []
|
|
|
|
for k, sr in src_map.items():
|
|
if k not in bak_map:
|
|
new_records.append(sr)
|
|
else:
|
|
br = bak_map[k]
|
|
old_pass = str(br[16]).strip() if len(br) > 16 else ''
|
|
new_pass = str(sr[16]).strip() if len(sr) > 16 else ''
|
|
old_km = float(br[14] or 0) if len(br) > 14 else 0
|
|
new_km = float(sr[14] or 0) if len(sr) > 14 else 0
|
|
old_rate = float(br[15] or 0) if len(br) > 15 else 0
|
|
new_rate = float(sr[15] or 0) if len(sr) > 15 else 0
|
|
if old_pass != new_pass or abs(old_km - new_km) > 0.01 or abs(old_rate - new_rate) > 0.01:
|
|
changed_records.append((br, sr))
|
|
|
|
for k, br in bak_map.items():
|
|
if k not in src_map:
|
|
removed_records.append(br)
|
|
|
|
print(f"New: {len(new_records)}, Changed: {len(changed_records)}, Removed: {len(removed_records)}")
|
|
|
|
# Count达标 changes
|
|
old_pass_count = sum(1 for r in bak_data if str(r[16]).strip() == '达标' if len(r) > 16)
|
|
new_pass_count = sum(1 for r in src_data if str(r[16]).strip() == '达标' if len(r) > 16)
|
|
print(f"达标: {old_pass_count} -> {new_pass_count}")
|
|
|
|
# Flip analysis (达标 → 未达标)
|
|
flipped = []
|
|
for old_r, new_r in changed_records:
|
|
old_p = str(old_r[16]).strip() if len(old_r) > 16 else ''
|
|
new_p = str(new_r[16]).strip() if len(new_r) > 16 else ''
|
|
if old_p == '达标' and new_p == '未达标':
|
|
flipped.append((old_r, new_r))
|
|
# Reverse flip (未达标 → 达标)
|
|
reversed_flip = []
|
|
for old_r, new_r in changed_records:
|
|
old_p = str(old_r[16]).strip() if len(old_r) > 16 else ''
|
|
new_p = str(new_r[16]).strip() if len(new_r) > 16 else ''
|
|
if old_p == '未达标' and new_p == '达标':
|
|
reversed_flip.append((old_r, new_r))
|
|
|
|
print(f"Flipped (达标→未达标): {len(flipped)}")
|
|
print(f"Reversed (未达标→达标): {len(reversed_flip)}")
|
|
|
|
# ── Generate Excel ──
|
|
wb = openpyxl.Workbook()
|
|
|
|
# Styles
|
|
TITLE_FONT = Font(bold=True, size=14)
|
|
SECTION_FONT = Font(bold=True, size=12, color='1F4E78')
|
|
HEADER_FILL = PatternFill('solid', fgColor='4472C4')
|
|
HEADER_FONT = Font(bold=True, color='FFFFFF', size=10)
|
|
SUB_FILL = PatternFill('solid', fgColor='D9E1F2')
|
|
ALT_FILL = PatternFill('solid', fgColor='F2F2F2')
|
|
NOTE_FONT = Font(size=10)
|
|
BOLD_FONT = Font(bold=True, size=10)
|
|
THIN = Side('thin', color='B4B4B4')
|
|
BORDER = Border(THIN, THIN, THIN, THIN)
|
|
CC = Alignment(horizontal='center', vertical='center', wrap_text=True)
|
|
LL = Alignment(horizontal='left', vertical='center', wrap_text=True)
|
|
NUM_FMT = '#,##0.00'
|
|
|
|
def style_header_row(ws, row, ncols, fill=HEADER_FILL, font=HEADER_FONT):
|
|
for c in range(1, ncols + 1):
|
|
cell = ws.cell(row=row, column=c)
|
|
cell.fill = fill
|
|
cell.font = font
|
|
cell.alignment = CC
|
|
cell.border = BORDER
|
|
|
|
def style_data_row(ws, row, ncols, alt=False):
|
|
for c in range(1, ncols + 1):
|
|
cell = ws.cell(row=row, column=c)
|
|
cell.border = BORDER
|
|
cell.font = Font(size=10)
|
|
cell.alignment = CC
|
|
if alt:
|
|
cell.fill = ALT_FILL
|
|
|
|
# ── Sheet 1: 测试与考核情况说明 ──
|
|
ws1 = wb.active
|
|
ws1.title = '测试与考核情况说明'
|
|
ws1.sheet_properties.tabColor = '4472C4'
|
|
|
|
ws1.merge_cells('A1:G1')
|
|
ws1.cell(row=1, column=1, value='2026年5月 业务考核 — 数据更新说明').font = TITLE_FONT
|
|
ws1.cell(row=1, column=1).alignment = CC
|
|
ws1.row_dimensions[1].height = 30
|
|
|
|
ws1.merge_cells('A2:G2')
|
|
desc = (
|
|
'5月业务考核源数据已于近期更新。'
|
|
f'共{len(src_data)}条考核记录，{new_pass_count}条达标（原{old_pass_count}条）。\n'
|
|
f'本次数据更新：新增{len(new_records)}条，变更{len(changed_records)}条，删除{len(removed_records)}条。\n'
|
|
f'其中达标→未达标翻转{len(flipped)}条，未达标→达标翻转{len(reversed_flip)}条。\n'
|
|
'本文件记录更新前后的数据对比，供核查参考。'
|
|
)
|
|
ws1.cell(row=2, column=1, value=desc).font = NOTE_FONT
|
|
ws1.cell(row=2, column=1).alignment = Alignment(horizontal='left', vertical='top', wrap_text=True)
|
|
ws1.row_dimensions[2].height = 80
|
|
|
|
r = 4
|
|
ws1.merge_cells(start_row=r, start_column=1, end_row=r, end_column=7)
|
|
ws1.cell(row=r, column=1, value='一、5月业务考核概况').font = SECTION_FONT
|
|
ws1.cell(row=r, column=1).alignment = LL
|
|
r += 1
|
|
|
|
summary_data = [
|
|
('项目', '5月业务考核车辆', '变更记录', '占比/变化'),
|
|
('考核记录数(条)', len(src_data), len(changed_records), f'{len(changed_records)/len(src_data)*100:.1f}%' if len(src_data) > 0 else '0%'),
|
|
('达标记录数(条)', new_pass_count, new_pass_count - old_pass_count, f'{"+" if new_pass_count >= old_pass_count else ""}{new_pass_count - old_pass_count}'),
|
|
('未达标记录数(条)', len(src_data) - new_pass_count, (len(src_data) - new_pass_count) - (len(bak_data) - old_pass_count), ''),
|
|
('达标率', f'{new_pass_count/len(src_data)*100:.1f}%' if len(src_data) > 0 else '0%', '', ''),
|
|
]
|
|
|
|
for i, row_data in enumerate(summary_data):
|
|
for c, val in enumerate(row_data, 1):
|
|
ws1.cell(row=r, column=c, value=val)
|
|
if i == 0:
|
|
style_header_row(ws1, r, 4)
|
|
else:
|
|
style_data_row(ws1, r, 4, alt=(i % 2 == 0))
|
|
ws1.cell(row=r, column=1).alignment = LL
|
|
r += 1
|
|
|
|
r += 1
|
|
ws1.merge_cells(start_row=r, start_column=1, end_row=r, end_column=7)
|
|
ws1.cell(row=r, column=1, value='二、考核记录变更明细').font = SECTION_FONT
|
|
ws1.cell(row=r, column=1).alignment = LL
|
|
r += 1
|
|
|
|
if flipped:
|
|
ws1.merge_cells(start_row=r, start_column=1, end_row=r, end_column=7)
|
|
ws1.cell(row=r, column=1, value=f'2.1 达→未达标翻转（{len(flipped)}条）').font = Font(bold=True, color='C00000')
|
|
r += 1
|
|
|
|
flip_headers = ['车牌号', '部门', '销售经理', '客户名称', '考核目标', '原实际里程', '新实际里程', '原是否达标', '新是否达标']
|
|
for c, h in enumerate(flip_headers, 1):
|
|
ws1.cell(row=r, column=c, value=h)
|
|
style_header_row(ws1, r, len(flip_headers))
|
|
r += 1
|
|
|
|
for i, (old_r, new_r) in enumerate(flipped):
|
|
vals = [
|
|
str(old_r[0]) if old_r[0] else '',
|
|
str(old_r[3]) if len(old_r) > 3 else '',
|
|
str(old_r[4]) if len(old_r) > 4 else '',
|
|
str(old_r[5]) if len(old_r) > 5 else '',
|
|
str(old_r[9]) if len(old_r) > 9 else '',
|
|
round(float(old_r[14] or 0), 2) if len(old_r) > 14 and old_r[14] else 0,
|
|
round(float(new_r[14] or 0), 2) if len(new_r) > 14 and new_r[14] else 0,
|
|
str(old_r[16]) if len(old_r) > 16 else '',
|
|
str(new_r[16]) if len(new_r) > 16 else '',
|
|
]
|
|
for c, val in enumerate(vals, 1):
|
|
ws1.cell(row=r, column=c, value=val)
|
|
style_data_row(ws1, r, len(flip_headers), alt=(i % 2 == 1))
|
|
r += 1
|
|
r += 1
|
|
|
|
if reversed_flip:
|
|
ws1.merge_cells(start_row=r, start_column=1, end_row=r, end_column=7)
|
|
ws1.cell(row=r, column=1, value=f'2.2 未达标→达标翻转（{len(reversed_flip)}条）').font = Font(bold=True, color='008000')
|
|
r += 1
|
|
|
|
for c, h in enumerate(flip_headers, 1):
|
|
ws1.cell(row=r, column=c, value=h)
|
|
style_header_row(ws1, r, len(flip_headers))
|
|
r += 1
|
|
|
|
for i, (old_r, new_r) in enumerate(reversed_flip):
|
|
vals = [
|
|
str(old_r[0]) if old_r[0] else '',
|
|
str(old_r[3]) if len(old_r) > 3 else '',
|
|
str(old_r[4]) if len(old_r) > 4 else '',
|
|
str(old_r[5]) if len(old_r) > 5 else '',
|
|
str(old_r[9]) if len(old_r) > 9 else '',
|
|
round(float(old_r[14] or 0), 2) if len(old_r) > 14 and old_r[14] else 0,
|
|
round(float(new_r[14] or 0), 2) if len(new_r) > 14 and new_r[14] else 0,
|
|
str(old_r[16]) if len(old_r) > 16 else '',
|
|
str(new_r[16]) if len(new_r) > 16 else '',
|
|
]
|
|
for c, val in enumerate(vals, 1):
|
|
ws1.cell(row=r, column=c, value=val)
|
|
style_data_row(ws1, r, len(flip_headers), alt=(i % 2 == 1))
|
|
r += 1
|
|
r += 1
|
|
|
|
if other_changes := [(o, n) for o, n in changed_records
|
|
if not (str(o[16]).strip() == '达标' and str(n[16]).strip() == '未达标')
|
|
and not (str(o[16]).strip() == '未达标' and str(n[16]).strip() == '达标')]:
|
|
ws1.merge_cells(start_row=r, start_column=1, end_row=r, end_column=7)
|
|
ws1.cell(row=r, column=1, value=f'2.3 其他变更（{len(other_changes)}条，里程变化但达标状态不变）').font = BOLD_FONT
|
|
r += 1
|
|
|
|
col_widths = [14, 12, 12, 30, 22, 16, 16, 12, 12]
|
|
for i, w in enumerate(col_widths, 1):
|
|
ws1.column_dimensions[get_column_letter(i)].width = w
|
|
|
|
# ── Sheet 2: 业务考核视图 (current data) ──
|
|
ws2 = wb.create_sheet('业务考核视图')
|
|
ws2.sheet_properties.tabColor = '70AD47'
|
|
|
|
for c, h in enumerate(src_headers, 1):
|
|
ws2.cell(row=1, column=c, value=h)
|
|
style_header_row(ws2, 1, len(src_headers))
|
|
|
|
for i, row in enumerate(src_data):
|
|
for c, val in enumerate(row, 1):
|
|
ws2.cell(row=i + 2, column=c, value=val)
|
|
style_data_row(ws2, i + 2, len(src_headers), alt=(i % 2 == 1))
|
|
|
|
for i, w in enumerate([12, 8, 8, 12, 10, 28, 20, 24, 10, 22, 12, 12, 10, 16, 16, 10, 10, 10, 12, 12, 12], 1):
|
|
ws2.column_dimensions[get_column_letter(i)].width = w
|
|
|
|
# ── Sheet 3: 5月业务考核车辆总览 ──
|
|
ws3 = wb.create_sheet('5月业务考核车辆总览')
|
|
ws3.sheet_properties.tabColor = 'ED7D31'
|
|
|
|
for c, h in enumerate(src_headers, 1):
|
|
ws3.cell(row=1, column=c, value=h)
|
|
style_header_row(ws3, 1, len(src_headers))
|
|
|
|
for i, row in enumerate(src_data):
|
|
for c, val in enumerate(row, 1):
|
|
ws3.cell(row=i + 2, column=c, value=val)
|
|
style_data_row(ws3, i + 2, len(src_headers), alt=(i % 2 == 1))
|
|
|
|
for i, w in enumerate([12, 8, 8, 12, 10, 28, 20, 24, 10, 22, 12, 12, 10, 16, 16, 10, 10, 10, 12, 12, 12], 1):
|
|
ws3.column_dimensions[get_column_letter(i)].width = w
|
|
|
|
# ── Sheet 4: 涉及变更的业务考核车辆 ──
|
|
ws4 = wb.create_sheet('涉及变更的业务考核车辆')
|
|
ws4.sheet_properties.tabColor = 'FFC000'
|
|
|
|
ws4.merge_cells('A1:U1')
|
|
ws4.cell(row=1, column=1, value=f'变更记录汇总 — 新增{len(new_records)}条 / 变更{len(changed_records)}条 / 删除{len(removed_records)}条').font = Font(bold=True, size=11)
|
|
ws4.cell(row=1, column=1).alignment = CC
|
|
ws4.row_dimensions[1].height = 24
|
|
|
|
# Source headers + change type col
|
|
ext_headers = list(src_headers) + ['变更类型', '原实际里程', '原是否达标']
|
|
for c, h in enumerate(ext_headers, 1):
|
|
ws4.cell(row=3, column=c, value=h)
|
|
style_header_row(ws4, 3, len(ext_headers))
|
|
|
|
r = 4
|
|
# New records
|
|
for i, rec in enumerate(new_records):
|
|
for c, val in enumerate(rec, 1):
|
|
ws4.cell(row=r, column=c, value=val)
|
|
ws4.cell(row=r, column=len(src_headers) + 1, value='新增')
|
|
style_data_row(ws4, r, len(ext_headers), alt=(i % 2 == 1))
|
|
ws4.row_dimensions[r].height = 20
|
|
r += 1
|
|
|
|
# Changed records
|
|
for i, (old_r, new_r) in enumerate(changed_records):
|
|
for c, val in enumerate(new_r, 1):
|
|
ws4.cell(row=r, column=c, value=val)
|
|
ws4.cell(row=r, column=len(src_headers) + 1, value='变更')
|
|
old_km = round(float(old_r[14] or 0), 2) if len(old_r) > 14 and old_r[14] else 0
|
|
old_pass = str(old_r[16]) if len(old_r) > 16 else ''
|
|
ws4.cell(row=r, column=len(src_headers) + 2, value=old_km)
|
|
ws4.cell(row=r, column=len(src_headers) + 3, value=old_pass)
|
|
style_data_row(ws4, r, len(ext_headers), alt=(i % 2 == 1))
|
|
ws4.row_dimensions[r].height = 20
|
|
r += 1
|
|
|
|
for i, w in enumerate([12, 8, 8, 12, 10, 28, 20, 24, 10, 22, 12, 12, 10, 16, 16, 10, 10, 10, 12, 12, 12, 10, 14, 12], 1):
|
|
ws4.column_dimensions[get_column_letter(i)].width = w
|
|
|
|
# ── Save ──
|
|
out_path = 'new-kpi-202605_1/租赁任务考核_2026年5月_干预情况说明.xlsx'
|
|
wb.save(out_path)
|
|
print(f'\n✅ {out_path} ({len(wb.sheetnames)} sheets)')
|