Python自动化办公实战：基于PyAutoGUI的跨平台桌面自动化系统开发
一、自动化办公技术选型
常见Python自动化方案对比：
- PyAutoGUI：跨平台GUI自动化
- OpenCV：图像识别辅助
- Pillow：图像处理支持
- PyWinAuto：Windows专用自动化
- SikuliX：基于图像识别的自动化
二、核心功能实现
1. 基础自动化操作
import pyautogui
import time
class OfficeAutomator:
def __init__(self):
self.screen_width, self.screen_height = pyautogui.size()
pyautogui.PAUSE = 1 # 每个操作间隔1秒
pyautogui.FAILSAFE = True # 启用安全模式
def open_application(self, app_name):
"""打开指定应用程序"""
pyautogui.hotkey('winleft', 's') # Windows搜索
pyautogui.typewrite(app_name)
pyautogui.press('enter')
time.sleep(2) # 等待应用启动
def fill_web_form(self, form_data):
"""自动填写网页表单"""
for field, value in form_data.items():
pyautogui.click(*self.locate_field(field))
pyautogui.typewrite(value)
pyautogui.press('tab')
def locate_field(self, field_name):
"""定位表单字段位置"""
try:
location = pyautogui.locateCenterOnScreen(
f'images/{field_name}.png',
confidence=0.8
)
return location
except pyautogui.ImageNotFoundException:
raise Exception(f"无法定位字段: {field_name}")2. 高级图像识别功能
import cv2
import numpy as np
class ImageRecognizer:
def __init__(self):
self.template_threshold = 0.8
def find_button(self, button_image):
"""在屏幕上查找按钮位置"""
screenshot = pyautogui.screenshot()
screenshot = cv2.cvtColor(np.array(screenshot), cv2.COLOR_RGB2BGR)
template = cv2.imread(f'images/{button_image}.png')
result = cv2.matchTemplate(
screenshot, template, cv2.TM_CCOEFF_NORMED
)
min_val, max_val, min_loc, max_loc = cv2.minMaxLoc(result)
if max_val >= self.template_threshold:
return max_loc
return None
def wait_until_appear(self, image_path, timeout=30):
"""等待指定图像出现"""
start_time = time.time()
while time.time() - start_time < timeout:
try:
location = pyautogui.locateOnScreen(
image_path,
confidence=0.9
)
if location:
return location
except:
pass
time.sleep(1)
raise TimeoutError(f"等待超时: {image_path}")三、实战案例：自动化报表系统
1. Excel数据处理自动化
import openpyxl
from openpyxl.utils import get_column_letter
class ExcelAutomator:
def __init__(self, file_path):
self.wb = openpyxl.load_workbook(file_path)
self.ws = self.wb.active
def process_monthly_report(self):
"""处理月度报表"""
# 自动计算汇总数据
for row in range(2, self.ws.max_row + 1):
total = sum(
self.ws.cell(row=row, column=col).value or 0
for col in range(2, 6)
self.ws[f'{get_column_letter(7)}{row}'] = total
# 自动生成图表
chart = openpyxl.chart.BarChart()
chart.title = "月度销售数据"
chart.y_axis.title = "金额"
chart.x_axis.title = "产品"
data = openpyxl.chart.Reference(
self.ws,
min_col=2,
max_col=6,
min_row=1,
max_row=self.ws.max_row
)
chart.add_data(data, titles_from_data=True)
self.ws.add_chart(chart, "A10")
def save_and_close(self, new_path):
"""保存并关闭文件"""
self.wb.save(new_path)
self.wb.close()2. 邮件自动发送功能
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email import encoders
class EmailSender:
def __init__(self, smtp_server, port, username, password):
self.smtp_server = smtp_server
self.port = port
self.username = username
self.password = password
def send_report(self, to_email, subject, body, attachment_path):
"""发送带附件的邮件"""
msg = MIMEMultipart()
msg['From'] = self.username
msg['To'] = to_email
msg['Subject'] = subject
msg.attach(MIMEText(body, 'plain'))
with open(attachment_path, 'rb') as attachment:
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header(
'Content-Disposition',
f'attachment; filename= {attachment_path.split("/")[-1]}'
)
msg.attach(part)
with smtplib.SMTP(self.smtp_server, self.port) as server:
server.starttls()
server.login(self.username, self.password)
server.send_message(msg)四、系统集成与调度
1. 主控程序实现
from datetime import datetime
import schedule
import time
class AutomationSystem:
def __init__(self):
self.office = OfficeAutomator()
self.excel = None
self.email = EmailSender(
'smtp.example.com',
587,
'user@example.com',
'password'
)
def daily_task(self):
"""每日自动化任务"""
try:
# 1. 打开Excel处理数据
self.office.open_application('Excel')
self.excel = ExcelAutomator('data/daily_report.xlsx')
self.excel.process_monthly_report()
report_path = 'data/processed_report.xlsx'
self.excel.save_and_close(report_path)
# 2. 发送邮件
today = datetime.now().strftime('%Y-%m-%d')
self.email.send_report(
'manager@example.com',
f'每日报表 - {today}',
'附件为今日自动生成的报表，请查收。',
report_path
)
# 3. 关闭Excel
pyautogui.hotkey('alt', 'f4')
except Exception as e:
self.send_error_notification(str(e))
def run_schedule(self):
"""启动定时任务"""
schedule.every().day.at("17:30").do(self.daily_task)
while True:
schedule.run_pending()
time.sleep(60)五、异常处理与日志
import logging
from logging.handlers import RotatingFileHandler
class ErrorHandler:
def __init__(self):
self.setup_logging()
def setup_logging(self):
"""配置日志系统"""
logging.basicConfig(
level=logging.INFO,
format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
handlers=[
RotatingFileHandler(
'logs/automation.log',
maxBytes=5*1024*1024,
backupCount=3
),
logging.StreamHandler()
]
)
def log_error(self, error_msg):
"""记录错误日志"""
logging.error(error_msg, exc_info=True)
def send_error_notification(self, error_msg):
"""发送错误通知"""
self.log_error(error_msg)
try:
self.email.send_report(
'admin@example.com',
'自动化系统错误通知',
f'系统发生错误:nn{error_msg}',
'logs/automation.log'
)
except Exception as e:
logging.critical(f"无法发送错误通知: {str(e)}")
