import os
import openpyxl
from openpyxl.styles import Font, Alignment
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.utils import get_column_letter
from datetime import datetime, timedelta
import pandas as pd
# 测试计数器
test_counter = {"font_success": 0, "font_failure": 0, "region_found": 0, "region_not_found": 0}
# 1. 获取前一天的日期并创建新文件名
today = datetime.now()
yesterday = today - timedelta(days=1)
yesterday_str = yesterday.strftime("%Y%m%d")
new_file_name = f"D:/质检/系统质检日报{yesterday_str}.xlsx"
wb_new = openpyxl.Workbook()
# 删除默认sheet
if "Sheet" in wb_new.sheetnames:
wb_new.remove(wb_new["Sheet"])
# 定义文件夹路径
folder_negative = "D:/质检/负向违规率"
folder_sop = "D:/质检/SOP执行率"
# 辅助函数：提取文件名括号中的内容
def extract_bracket_content(filename):
if '(' in filename and ')' in filename:
start = filename.find('(') + 1
end = filename.find(')')
return filename[start:end]
return os.path.splitext(filename)[0]
# 辅助函数：查找区域范围（基于标题关键词）
def find_table_range(ws, keyword, max_search_rows=50):
"""
智能定位表格区域 (支持合并单元格标题)
返回: (列头行, 数据结束行, 起始列, 结束列)
"""
# 1. 搜索标题行 (支持合并单元格)
title_cell = None
for row in range(1, max_search_rows + 1):
for col in range(1, ws.max_column + 1):
cell = ws.cell(row=row, column=col)
# 检查合并单元格
if cell.value and keyword in str(cell.value):
title_cell = cell
break
# 检查是否为合并区域的一部分
for merged in ws.merged_cells.ranges:
if (row, col) in merged:
merged_cell = ws.cell(merged.min_row, merged.min_col)
if merged_cell.value and keyword in str(merged_cell.value):
title_cell = merged_cell
break
if title_cell:
break
if not title_cell:
print(f"⚠️ 未找到包含'{keyword}'的标题")
test_counter["region_not_found"] += 1
return None
test_counter["region_found"] += 1
# 2. 确定标题区域范围
start_col = title_cell.column
end_col = start_col
# 处理合并单元格
merged_range = None
for merged in ws.merged_cells.ranges:
if (title_cell.row, title_cell.column) in merged:
merged_range = merged
start_col = merged.min_col
end_col = merged.max_col
break
# 未合并则向右扫描
if not merged_range:
while end_col < ws.max_column:
next_cell = ws.cell(row=title_cell.row, column=end_col + 1)
# 允许空白单元格但必须存在列名
if next_cell.value is None and ws.cell(row=title_cell.row + 1, column=end_col + 1).value is None:
break
end_col += 1
# 3. 定位列头行和数据区域
header_row = title_cell.row + 1 # 列名在标题下一行
# 4. 查找数据结束行 (考虑空行)
data_end_row = header_row
has_data = False
for row in range(header_row + 1, ws.max_row + 1):
row_has_data = any(ws.cell(row=row, column=c).value is not None for c in range(start_col, end_col + 1))
if row_has_data:
data_end_row = row
has_data = True
elif has_data: # 已有数据后遇到空行，结束
break
print(f"✅ 定位成功: {keyword}")
print(f" 标题位置: {title_cell.coordinate} (合并区域: {f'{start_col}-{end_col}' if merged_range else '无'})")
print(f" 列头行: {header_row}, 数据行: {header_row + 1}-{data_end_row}")
print(f" 列范围: {start_col}-{end_col}")
return (header_row, data_end_row, start_col, end_col)
# 2. 遍历文件夹并处理文件
file_count = 0
folders = [folder_negative, folder_sop]
for folder in folders:
files = [f for f in os.listdir(folder) if f.endswith(('.xlsx', '.xls'))]
print(f"\n处理文件夹: {folder}")
print(f"找到 {len(files)} 个文件")
for file_idx, file in enumerate(files, 1):
file_path = os.path.join(folder, file)
print(f"\n处理文件 {file_idx}/{len(files)}: {file}")
try:
wb_source = openpyxl.load_workbook(file_path, data_only=True)
if "统计" in wb_source.sheetnames:
ws_source = wb_source["统计"]
sheet_name = "统计-" + extract_bracket_content(file)
ws_new = wb_new.create_sheet(title=sheet_name)
print(f"创建新工作表: {sheet_name}")
# 复制内容和样式
for row in ws_source.iter_rows():
for cell in row:
new_cell = ws_new.cell(row=cell.row, column=cell.column, value=cell.value)
if cell.has_style:
new_cell.font = cell.font.copy()
new_cell.border = cell.border.copy()
new_cell.fill = cell.fill.copy()
new_cell.number_format = cell.number_format
new_cell.alignment = cell.alignment.copy()
# 复制合并单元格和居中格式
for merged_range in ws_source.merged_cells.ranges:
min_row = merged_range.min_row
min_col = merged_range.min_col
max_row = merged_range.max_row
max_col = merged_range.max_col
ws_new.merge_cells(start_row=min_row, start_column=min_col, end_row=max_row, end_column=max_col)
# 复制合并区域的居中格式
merged_cell = ws_source.cell(min_row, min_col)
if merged_cell.alignment.horizontal == 'center' or merged_cell.alignment.vertical == 'center':
for r in range(min_row, max_row + 1):
for c in range(min_col, max_col + 1):
cell = ws_new.cell(row=r, column=c)
cell.alignment = Alignment(
horizontal='center' if merged_cell.alignment.horizontal == 'center' else None,
vertical='center' if merged_cell.alignment.vertical == 'center' else None
)
# 应用全局格式：字体微软雅黑，字号10
font = Font(name='微软雅黑', size=10)
for row in ws_new.iter_rows():
for cell in row:
cell.font = font
# 5. 根据文件夹类型处理特定区域
if folder == folder_negative:
print("\n处理负向违规率文件")
# 坐席维度处理
range_seat = find_table_range(ws_new, "负向-坐席维度统计")
if range_seat:
header_row, end_row, start_col, end_col = range_seat
headers = [ws_new.cell(row=header_row, column=c).value for c in range(start_col, end_col + 1)]
if "汇总" in headers:
col_idx = headers.index("汇总") + start_col
print(f" 按'汇总'列排序 (列{col_idx})")
# 提取数据
data = []
for r in range(header_row + 1, end_row + 1):
row_data = [ws_new.cell(row=r, column=c).value for c in range(start_col, end_col + 1)]
data.append(row_data)
# 创建DataFrame并排序
df = pd.DataFrame(data, columns=headers)
df["汇总"] = pd.to_numeric(df["汇总"], errors='coerce')
df_sorted = df.sort_values(by="汇总", ascending=True)
# 写回排序后的数据
for r_idx, r in enumerate(range(header_row + 1, end_row + 1)):
for c_idx, c in enumerate(range(start_col, end_col + 1)):
ws_new.cell(row=r, column=c, value=df_sorted.iloc[r_idx, c_idx])
# 处理主任维度统计
range_director = find_table_range(ws_new, "负向-主任维度统计")
if range_director:
header_row, end_row, start_col, end_col = range_director
headers = [ws_new.cell(row=header_row, column=c).value for c in range(start_col, end_col + 1)]
if "汇总" in headers:
col_idx = headers.index("汇总") + start_col
col_letter = get_column_letter(col_idx)
# 添加条件格式
rule = ColorScaleRule(start_type='min', start_color='00FF00', # 绿
mid_type='percentile', mid_value=50, mid_color='FFFFFF', # 白
end_type='max', end_color='FF0000') # 红
ws_new.conditional_formatting.add(f"{col_letter}{header_row + 1}:{col_letter}{end_row}", rule)
print(f" 已设置色阶条件格式: {col_letter}{header_row + 1}:{col_letter}{end_row}")
# 如果文件名包含"说辞合规性-非成功单"，删除汇总列
if "说辞合规性-非成功单" in file:
print("\n处理非成功单文件，删除汇总列")
keywords = ["负向-模型维度统计", "负向-科室维度统计", "负向-主任维度统计", "负向-坐席维度统计"]
for keyword in keywords:
region_range = find_table_range(ws_new, keyword)
if region_range:
header_row, end_row, start_col, end_col = region_range
headers = [ws_new.cell(row=header_row, column=c).value for c in range(start_col, end_col + 1)]
if "汇总" in headers:
col_idx = headers.index("汇总") + start_col
ws_new.delete_cols(col_idx, 1)
print(f" 已删除 '{keyword}' 的汇总列 (列{col_idx})")
elif folder == folder_sop:
print("\n处理SOP执行率文件")
# 处理坐席维度统计
range_seat = find_table_range(ws_new, "SOP-坐席维度统计")
if range_seat:
header_row, end_row, start_col, end_col = range_seat
headers = [ws_new.cell(row=header_row, column=c).value for c in range(start_col, end_col + 1)]
if "需关联外部话术的执行率" in headers:
col_idx = headers.index("需关联外部话术的执行率") + start_col
# 提取数据
data = []
for r in range(header_row + 1, end_row + 1):
row_data = [ws_new.cell(row=r, column=c).value for c in range(start_col, end_col + 1)]
data.append(row_data)
# 创建DataFrame并排序
df = pd.DataFrame(data, columns=headers)
df_sorted = df.sort_values(by="需关联外部话术的执行率", ascending=True)
# 写回排序后的数据
for r_idx, r in enumerate(range(header_row + 1, end_row + 1)):
for c_idx, c in enumerate(range(start_col, end_col + 1)):
ws_new.cell(row=r, column=c, value=df_sorted.iloc[r_idx, c_idx])
# 处理主任维度统计
range_director = find_table_range(ws_new, "SOP-主任维度统计")
if range_director:
header_row, end_row, start_col, end_col = range_director
headers = [ws_new.cell(row=header_row, column=c).value for c in range(start_col, end_col + 1)]
if "汇总" in headers:
col_idx = headers.index("汇总") + start_col
col_letter = get_column_letter(col_idx)
# 添加条件格式
rule = ColorScaleRule(start_type='min', start_color='FF0000', # 红
mid_type='percentile', mid_value=50, mid_color='FFFFFF', # 白
end_type='max', end_color='00FF00') # 绿
ws_new.conditional_formatting.add(f"{col_letter}{header_row + 1}:{col_letter}{end_row}", rule)
print(f" 已设置色阶条件格式: {col_letter}{header_row + 1}:{col_letter}{end_row}")
file_count += 1
else:
print(f" 文件 {file} 中缺少'统计'工作表")
except Exception as e:
print(f"处理文件 {file} 时出错: {str(e)}")
import traceback
traceback.print_exc()
# 保存新文件
wb_new.save(new_file_name)
print(f"\n文件已创建: {new_file_name}")
print(f"处理文件总数: {file_count}")
# 最终测试报告
print("\n==== 测试报告 ====")
print(f"字体设置成功: {test_counter['font_success']} 次")
print(f"区域成功定位: {test_counter['region_found']} 次")
print(f"区域未找到: {test_counter['region_not_found']} 次")
print("==================")
line 137，263，54，146出错'''TypeError:except string or byte-like object
最新发布
本文介绍了一段VBA代码，该代码用于在Excel中将当前工作簿的名字进行递增更新，例如从xyzhd000001.xlsm更新到xyzhd000002.xlsm。通过解析文件名并增加特定部分的数值实现这一目标。
扫一扫
1156
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
