import os
import pandas as pd
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, NamedStyle
from io import BytesIO
import win32com.client as win32
from PIL import ImageGrab
import zipfile
import time
# =================== ⚙️ 配置路径 ===================
INPUT_DIR = r'reports'
OUTPUT_DIR = r'C:\Users\Lt\Desktop\test'
TUISONG_DIR = os.path.join(OUTPUT_DIR, 'tuisong')
# 输入文件
pon_file = os.path.join(INPUT_DIR, 'PON口码化情况清单.xlsx')
obd_file = os.path.join(INPUT_DIR, 'OBD尾纤贴码分类报表.xlsx')
jiudiao_old = os.path.join(INPUT_DIR, '宽带资源情况表_旧.xlsx') # 注意：可以是 .xls
jiudiao_new = os.path.join(INPUT_DIR, '宽带资源情况表_新.xlsx') # 注意：可以是 .xls
fenguangqi_file = os.path.join(INPUT_DIR, '日调度表分光器.xlsx')
tongbao_template = 'tongbao.xlsx'
# 输出文件
final_output = os.path.join(OUTPUT_DIR, 'merged_header_file.xlsx')
beautified_file = os.path.join(OUTPUT_DIR, '通报_美化后.xlsx')
screenshot_file = os.path.join(OUTPUT_DIR, 'your_excel_file_fixed_width_with_border.xlsx')
# 区域列表
REGIONS = ["港南区", "港北区", "覃塘区", "平南县", "桂平市"]
# 创建目录
os.makedirs(OUTPUT_DIR, exist_ok=True)
os.makedirs(TUISONG_DIR, exist_ok=True)
print("🚀 开始执行自动化流程...")
# =================== 工具函数 ===================
def is_real_xlsx(file_path):
"""判断是否为真正的 .xlsx 文件"""
if not os.path.exists(file_path):
return False
try:
with zipfile.ZipFile(file_path, 'r') as z:
return '[Content_Types].xml' in z.namelist()
except:
return False
def ensure_true_xlsx(input_path, output_path=None):
"""将 .xls 或伪 .xlsx 转换为真 .xlsx"""
if output_path is None:
base = os.path.splitext(input_path)[0]
output_path = base + '_converted.xlsx'
print(f"🔄 转换: {input_path} → {output_path}")
try:
df = pd.read_excel(input_path, header=None)
df.to_excel(output_path, index=False, header=False, engine='openpyxl')
print(f"✅ 转换成功: {output_path}")
return output_path
except Exception as e:
print(f"❌ 转换失败 {input_path}: {str(e)}")
return None
def safe_load_workbook(path, max_retries=3):
"""安全加载 workbook，防止文件被占用"""
for i in range(max_retries):
try:
return load_workbook(path)
except PermissionError:
print(f"⚠️ 文件被占用，等待重试 ({i+1}/{max_retries})...")
time.sleep(1)
raise Exception(f"无法访问文件（权限错误）: {path}")
# =================== 1. 合并 PON 表 ===================
print("📌 正在合并 PON口码化情况清单...")
sheet1 = pd.read_excel(pon_file, sheet_name=0, skiprows=2, header=0)
sheet2 = pd.read_excel(pon_file, sheet_name=1, skiprows=2, header=0)
combined_df = pd.concat([sheet1, sheet2], ignore_index=True)
combined_df.to_excel(final_output, index=False)
print("✅ PON表合并完成")
# =================== 2. 合并 OBD 表 ===================
print("📌 正在合并 OBD尾纤贴码分类报表...")
sheets = [pd.read_excel(obd_file, sheet_name=i, skiprows=2, header=0) for i in range(10)]
combined_df_1 = pd.concat(sheets, ignore_index=True)
temp_obd = os.path.join(OUTPUT_DIR, 'combined_data_with_header_1.xlsx')
combined_df_1.to_excel(temp_obd, index=False)
wb = load_workbook(temp_obd)
ws = wb.active
for row in range(2, ws.max_row + 1):
ws[f'Z{row}'] = f'=X{row}&Y{row}'
ws['Z1'] = "合并"
wb.save(temp_obd)
print("✅ OBD表合并并添加公式完成")
# =================== 3. 筛选第8列为空的行 ===================
print("📌 正在筛选第8列为 null 的行...")
df_main = pd.read_excel(final_output)
blank_rows = df_main[df_main.iloc[:, 7].isna()]
blank_filtered = os.path.join(OUTPUT_DIR, 'filtered_blank_rows.xlsx')
blank_rows.to_excel(blank_filtered, index=False)
wb_filt = load_workbook(blank_filtered)
ws_filt = wb_filt.active
for row in range(2, ws_filt.max_row + 1):
ws_filt[f'K{row}'] = f'=C{row}&D{row}'
ws_filt[f'L{row}'] = f'=IF(G{row}="A","机箱异常","用户未码")'
ws_filt[f'M{row}'] = f'=XLOOKUP(K{row},机箱参考!Z:Z,机箱参考!E:E)'
ws_filt['K1'] = "合并"; ws_filt['L1'] = "状态"; ws_filt['M1'] = "参考位置"
merged_header = os.path.join(OUTPUT_DIR, 'merged_header_file.xlsx')
wb_filt.save(merged_header)
print("✅ 空值筛选与公式注入完成")
# =================== 4. 追加数据并重命名工作表 ===================
def append_and_rename(src_path, target_path, new_sheet_name):
print(f"🔧 处理: 源={src_path}, 目标={target_path}, 新表名={new_sheet_name}")
if not os.path.exists(src_path):
print(f"❌ 源文件不存在: {src_path}")
return False
# 转换为真 .xlsx（如果需要）
if not is_real_xlsx(src_path):
print(f"🟡 文件非标准 .xlsx，正在转换...")
src_path = ensure_true_xlsx(src_path)
if not src_path:
print(f"❌ 转换失败，跳过: {new_sheet_name}")
return False
try:
src_wb = safe_load_workbook(src_path)
tgt_wb = safe_load_workbook(target_path)
src_sheet = src_wb.active
if "Sheet2" in tgt_wb.sheetnames:
del tgt_wb["Sheet2"] # 删除旧 Sheet2
new_sheet = tgt_wb.create_sheet("Sheet2")
for row in src_sheet.iter_rows(values_only=True):
new_sheet.append(row)
if new_sheet_name in tgt_wb.sheetnames:
del tgt_wb[new_sheet_name]
new_sheet.title = new_sheet_name
tgt_wb.save(target_path)
print(f"✅ 成功追加 '{new_sheet_name}' 到 {target_path}")
return True
except Exception as e:
print(f"❌ 操作失败: {str(e)}")
return False
# 构建待处理列表
sources_to_append = [
(temp_obd, merged_header, "机箱参考"),
(jiudiao_old, merged_header, "日调度表1"),
(jiudiao_new, merged_header, "日调度表2"),
(fenguangqi_file, merged_header, "日调度表分光器"),
(tongbao_template, merged_header, "通报")
]
# 批量处理
for src, dst, name in sources_to_append:
append_and_rename(src, dst, name)
# =================== 5. 注入通报表公式 ===================
print("📌 正在为‘通报’表注入公式...")
wb = safe_load_workbook(merged_header)
if "通报" not in wb.sheetnames:
print("❌ 错误：找不到 '通报' 工作表！")
else:
ws = wb["通报"]
start_row = 3
max_row = ws.max_row
# 添加百分比样式
if 'pct_style' not in wb.named_styles:
pct_style = NamedStyle(name='pct_style')
pct_style.number_format = '0.00%'
wb.add_named_style(pct_style)
for row in range(start_row, max_row + 1):
ws[f'B{row}'] = f'=XLOOKUP(A{row},日调度表分光器!A:A,日调度表分光器!B:B)'
ws[f'C{row}'] = f'=XLOOKUP(A{row},日调度表分光器!A:A,日调度表分光器!C:C)'
ws[f'D{row}'] = f'=XLOOKUP(A{row},日调度表分光器!A:A,日调度表分光器!E:E)'
ws[f'E{row}'] = f'=XLOOKUP(A{row},日调度表2!B:B,日调度表2!F:F)'
ws[f'F{row}'] = f'=XLOOKUP(A{row},日调度表1!B:B,日调度表1!F:F)'
ws[f'H{row}'] = f'=COUNTIF(Sheet1!B:B,A{row})'
cell_g = ws[f'G{row}']
cell_g.value = f'=E{row}-F{row}'
cell_g.style = 'pct_style'
last_row = ws.max_row
ws[f'H{last_row}'] = f'=SUM(H{start_row}:H{last_row - 1})'
wb.save(beautified_file)
print(f"✅ 通报表公式注入完成，保存至: {beautified_file}")
# =================== 6. 美化“通报”表 ===================
print("📌 正在美化‘通报’表...")
wb = safe_load_workbook(beautified_file)
ws = wb["通报"]
title_font = Font(name='微软雅黑', size=11, bold=True, color="FFFFFF")
normal_font = Font(name='微软雅黑', size=11)
center_align = Alignment(horizontal='center', vertical='center')
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
header_fill = PatternFill(start_color='4F94CD', end_color='4F94CD', fill_type='solid')
for cell in ws[1]:
cell.font = title_font
cell.alignment = center_align
cell.fill = header_fill
cell.border = thin_border
for row in ws.iter_rows(min_row=2):
for cell in row:
cell.font = normal_font
cell.alignment = center_align
cell.border = thin_border
for col in ws.columns:
col_letter = col[0].column_letter
ws.column_dimensions[col_letter].width = 20
for row in ws.iter_rows():
ws.row_dimensions[row[0].row].height = 18
ws.freeze_panes = 'A2'
wb.save(beautified_file)
print(f"✅ 美化完成: {beautified_file}")
# =================== 7. 设置 Sheet1 格式 ===================
print("📌 正在设置 Sheet1 格式...")
wb = safe_load_workbook(beautified_file)
ws = wb["Sheet1"] if "Sheet1" in wb.sheetnames else wb.create_sheet("Sheet1")
col_widths = {
'A': 7, 'B': 7, 'C': 14, 'D': 9, 'E': 38, 'F': 38,
'G': 14, 'H': 14, 'I': 14, 'J': 30, 'K': 22, 'L': 12, 'M': 85
}
for col, w in col_widths.items():
ws.column_dimensions[col].width = w
thin_border = Border(
left=Side(style='thin'), right=Side(style='thin'),
top=Side(style='thin'), bottom=Side(style='thin')
)
for row in ws.iter_rows():
for cell in row:
cell.border = thin_border
wb.save(screenshot_file)
print(f"✅ Sheet1 格式设置完成: {screenshot_file}")
# =================== 8. 批量筛选截图 ===================
print("📌 正在批量截图各区域...")
excel_app = win32.Dispatch("Excel.Application")
excel_app.Visible = True
excel_app.DisplayAlerts = False
excel_app.ScreenUpdating = False
try:
wb_excel = excel_app.Workbooks.Open(screenshot_file)
ws_excel = wb_excel.Sheets("Sheet1")
ws_excel.Activate()
ws_excel.Rows(1).AutoFilter()
for region in REGIONS:
print(f"🔍 筛选并截图: {region}")
ws_excel.Range("B1").AutoFilter(Field=2, Criteria1=region)
time.sleep(0.5)
try:
visible_cells = ws_excel.Columns("A").SpecialCells(11)
last_row = visible_cells.Row + visible_cells.Rows.Count - 1
last_row = max(last_row, 2)
except:
last_row = 100
capture_range = ws_excel.Range(f"A1:M{last_row}")
capture_range.CopyPicture(Appearance=1, Format=2)
time.sleep(0.3)
img = ImageGrab.grabclipboard()
if img:
img.save(os.path.join(TUISONG_DIR, f'{region}.png'), "PNG")
print(f"✅ 已保存截图: {region}.png")
else:
print(f"❌ {region}: 剪贴板无图像")
wb_excel.Close()
excel_app.Quit()
print("🎉 所有区域截图完成！")
except Exception as e:
print("❌ Excel 自动化错误:", str(e))
try:
excel_app.Quit()
except:
pass
# =================== 9. 生成‘通报’总表截图 ===================
print("📌 正在生成‘通报’总表截图...")
try:
excel = win32.Dispatch("Excel.Application")
excel.Visible = False
excel.DisplayAlerts = False
excel.ScreenUpdating = False
workbook = excel.Workbooks.Open(beautified_file)
sheet = workbook.Sheets("通报")
used_range = sheet.UsedRange
used_range.CopyPicture(Appearance=1, Format=2)
chart_obj = sheet.ChartObjects().Add(0, 0, used_range.Width, used_range.Height)
chart = chart_obj.Chart
chart.Paste()
chart.Export(os.path.join(TUISONG_DIR, '通报_截图.png'), "PNG")
chart_obj.Delete()
workbook.Close(SaveChanges=False)
excel.Quit()
print("✅ 通报总表截图完成")
except Exception as e:
print("❌ 通报截图失败:", str(e))
# --- 导出各区县 Excel ---
import win32com.client as win32
from PIL import ImageGrab
import os
import time
# 启动 Excel 应用程序
excel = win32.Dispatch("Excel.Application")
excel.Visible = True # 必须设为 True 才能截图
excel.DisplayAlerts = False # 禁止弹窗
# 打开工作簿
file_path = r'C:\Users\Lt\Desktop\test\your_excel_file_fixed_width_with_border.xlsx'
wb = excel.Workbooks.Open(file_path)
sheet = wb.Sheets("Sheet1")
sheet.Activate()
# 启用筛选并筛选 B 列中 "港南区"
sheet.Rows(1).AutoFilter() # 启用筛选（第一行为标题）
filter_values = ["港南区"]
sheet.Range("B1").AutoFilter(Field=2, Criteria1=filter_values, Operator=7) # OR 多条件
# 等待刷新界面
time.sleep(1)
# 保存工作簿副本（可选）
output_xlsx_path = r'C:\Users\Lt\Desktop\test\tuisong\港南区.xlsx'
wb.SaveAs(output_xlsx_path)
wb.Close()
excel.Quit()
print(f"✅ 筛选港南区保存 Excel 文件。")
# 启动 Excel 应用程序
excel = win32.Dispatch("Excel.Application")
excel.Visible = True # 必须设为 True 才能截图
excel.DisplayAlerts = False # 禁止弹窗
# 打开工作簿
file_path = r'C:\Users\Lt\Desktop\test\your_excel_file_fixed_width_with_border.xlsx'
wb = excel.Workbooks.Open(file_path)
sheet = wb.Sheets("Sheet1")
sheet.Activate()
# 启用筛选并筛选 B 列中 "港北区"
sheet.Rows(1).AutoFilter() # 启用筛选（第一行为标题）
filter_values = ["港北区"]
sheet.Range("B1").AutoFilter(Field=2, Criteria1=filter_values, Operator=7) # OR 多条件
# 等待刷新界面
time.sleep(1)
# 保存工作簿副本（可选）
output_xlsx_path = r'C:\Users\Lt\Desktop\test\tuisong\港北区.xlsx'
wb.SaveAs(output_xlsx_path)
wb.Close()
excel.Quit()
print(f"✅ 筛选港北区保存 Excel 文件。")
# 启动 Excel 应用程序
excel = win32.Dispatch("Excel.Application")
excel.Visible = True # 必须设为 True 才能截图
excel.DisplayAlerts = False # 禁止弹窗
# 打开工作簿
file_path = r'C:\Users\Lt\Desktop\test\your_excel_file_fixed_width_with_border.xlsx'
wb = excel.Workbooks.Open(file_path)
sheet = wb.Sheets("Sheet1")
sheet.Activate()
# 启用筛选并筛选 B 列中 "覃塘区"
sheet.Rows(1).AutoFilter() # 启用筛选（第一行为标题）
filter_values = ["覃塘区"]
sheet.Range("B1").AutoFilter(Field=2, Criteria1=filter_values, Operator=7) # OR 多条件
# 等待刷新界面
time.sleep(1)
# 保存工作簿副本（可选）
output_xlsx_path = r'C:\Users\Lt\Desktop\test\tuisong\覃塘区.xlsx'
wb.SaveAs(output_xlsx_path)
wb.Close()
excel.Quit()
print(f"✅ 筛选覃塘区保存 Excel 文件。")
# 启动 Excel 应用程序
excel = win32.Dispatch("Excel.Application")
excel.Visible = True # 必须设为 True 才能截图
excel.DisplayAlerts = False # 禁止弹窗
# 打开工作簿
file_path = r'C:\Users\Lt\Desktop\test\your_excel_file_fixed_width_with_border.xlsx'
wb = excel.Workbooks.Open(file_path)
sheet = wb.Sheets("Sheet1")
sheet.Activate()
# 启用筛选并筛选 B 列中 "桂平市"
sheet.Rows(1).AutoFilter() # 启用筛选（第一行为标题）
filter_values = ["桂平市"]
sheet.Range("B1").AutoFilter(Field=2, Criteria1=filter_values, Operator=7) # OR 多条件
# 等待刷新界面
time.sleep(1)
# 保存工作簿副本（可选）
output_xlsx_path = r'C:\Users\Lt\Desktop\test\tuisong\桂平市.xlsx'
wb.SaveAs(output_xlsx_path)
wb.Close()
excel.Quit()
print(f"✅ 筛选桂平市保存 Excel 文件。")
# 启动 Excel 应用程序
excel = win32.Dispatch("Excel.Application")
excel.Visible = True # 必须设为 True 才能截图
excel.DisplayAlerts = False # 禁止弹窗
# 打开工作簿
file_path = r'C:\Users\Lt\Desktop\test\your_excel_file_fixed_width_with_border.xlsx'
wb = excel.Workbooks.Open(file_path)
sheet = wb.Sheets("Sheet1")
sheet.Activate()
# 启用筛选并筛选 B 列中 "平南县"
sheet.Rows(1).AutoFilter() # 启用筛选（第一行为标题）
filter_values = ["平南县"]
sheet.Range("B1").AutoFilter(Field=2, Criteria1=filter_values, Operator=7) # OR 多条件
# 等待刷新界面
time.sleep(1)
# 保存工作簿副本（可选）
output_xlsx_path = r'C:\Users\Lt\Desktop\test\tuisong\平南县.xlsx'
wb.SaveAs(output_xlsx_path)
wb.Close()
excel.Quit()
print(f"✅ 筛选平南县保存 Excel 文件。")
print("🎉 全部任务执行完毕！")
#最新加速
最新发布
xlwings的Range.CopyPicture方法允许开发者将选定的Excel单元格区域复制为图片，可以选择复制效果（屏幕或打印机）和图片格式（位图或矢量图）。示例展示了如何将B2:D5区域复制到F2:H5，以图片形式呈现。
最低0.47元/天 解锁文章
扫一扫
2073
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
