Python自动化办公之从Excel/Word处理到浏览器操控的全面指南

from openpyxl import workbook, load_workbook
from openpyxl.styles import font, color, patternfill

# 1. 创建新 workbook
wb = workbook()
ws = wb.active
ws.title = "sales data"

# 2. 写入数据
ws['a1'] = "product"
ws['b1'] = "price"
ws.append(['laptop', 1200]) # 追加一行
ws.append(['mouse', 25])

# 添加公式
# 3. 设置样式
ws['a1'].font = font(bold=true, color="ffffff")
ws['a1'].fill = patternfill(start_color="4472c4", fill_type="solid")
ws['c1'] = "total"
ws['c2'] = "=a2*b2" # 注意：openpyxl 只写公式字符串，不计算结果

# 4. 读取现有文件
wb_load = load_workbook('report.xlsx')
ws_load = wb_load['sheet1']
value = ws_load['a1'].value
# 读取现有文件
wb = load_workbook("report.xlsx", data_only=true) # data_only=true 读取公式计算后的值
ws = wb["sales data"]
print(ws['a2'].value)
# 5. 保存
wb.save('new_report.xlsx')

from docx import document
from docx.shared import inches, pt
from docx.enum.text import wd_align_paragraph

# 1. 创建文档
doc = document()

# 2. 添加标题
heading = doc.add_heading('monthly report', 0)
heading.alignment = wd_align_paragraph.center

# 3. 添加段落与样式 (run 的概念)
p = doc.add_paragraph('this is an automated report generated by python.')
p.add_run(' bold text').bold = true
p.add_run(' italic text').italic = true

# 4. 添加表格
table = doc.add_table(rows=3, cols=3)
table.style = 'table grid'
cell = table.cell(0, 0)
cell.text = 'header 1'

# 5. 保存
doc.save('report.docx')

from selenium import webdriver
from selenium.webdriver.common.by import by
from selenium.webdriver.common.keys import keys
from selenium.webdriver.chrome.service import service
from selenium.webdriver.chrome.options import options
import time

# 1. 配置 chrome 选项 (无头模式)
options = options()
options.add_argument("--headless=new") # 2026 新版无头模式
options.add_argument("--disable-gpu")

# 初始化驱动 (需确保 chromedriver 版本匹配)
driver = webdriver.chrome(options=options)

try:
    # 2. 打开网页
    driver.get("https://www.example.com")
    
    # 3.查找元素 (推荐 webdriverwait 显式等待，此处简化演示)
    search_box = driver.find_element(by.name, "q")
    # 4. 模拟操作
    search_box.send_keys("python automation")
    search_box.send_keys(keys.return)
    
    time.sleep(2) # 实际生产请用 webdriverwait
    
    # 5. 获取数据
    results = driver.find_elements(by.css_selector, ".result-title")
    print(f"found {len(results)} results")
    
    # 6.截图
    driver.save_screenshot("result.png")
    
finally:
    # 7.关闭浏览器
    driver.quit() # 务必关闭浏览器

import os
import openpyxl
from openpyxl.utils import get_column_letter

def merge_excel_files(folder_path, output_file):
    master_wb = openpyxl.workbook()
    master_ws = master_wb.active
    master_ws.title = "all_data"
    
    headers_written = false
    row_offset = 1
    
    for filename in os.listdir(folder_path):
        if filename.startswith("sales_") and filename.endswith(".xlsx"):
            wb = load_workbook(os.path.join(folder_path, filename))
            ws = wb.active
            
            for i, row in enumerate(ws.iter_rows(values_only=true), start=1):
                if i == 1: # 表头
                    if not headers_written:
                        master_ws.append(row)
                        headers_written = true
                    continue
                
                # 添加来源文件名作为新的一列
                new_row = list(row) + [filename]
                master_ws.append(new_row)
    
    # 添加汇总公式示例
    last_row = master_ws.max_row
    col_b = get_column_letter(2) # 假设第2列是金额
    master_ws[f'b{last_row+1}'] = f"=sum(b2:b{last_row})"
    
    master_wb.save(output_file)
    print(f"merged into {output_file}")

# merge_excel_files('./sales_reports', 'total_sales.xlsx')

import openpyxl
from docx import document

def generate_contracts(excel_path, template_path, output_folder):
    wb = load_workbook(excel_path)
    ws = wb.active
    
    # 加载模板
    template = document(template_path)
    
    for row in ws.iter_rows(min_row=2, values_only=true):
        name, position, salary = row[0], row[1], row[2]
        
        # 复制模板内容 (深拷贝避免污染)
        import copy
        doc = copy.deepcopy(template)
        
        # 替换占位符 (简单文本替换，复杂情况需遍历 paragraphs)
        for paragraph in doc.paragraphs:
            if '{{name}}' in paragraph.text:
                paragraph.text = paragraph.text.replace('{{name}}', name)
            if '{{position}}' in paragraph.text:
                paragraph.text = paragraph.text.replace('{{position}}', position)
            if '{{salary}}' in paragraph.text:
                paragraph.text = paragraph.text.replace('{{salary}}', str(salary))
        
        # 处理表格内的替换 (略，逻辑类似，需遍历 table.rows)
        
        doc.save(f"{output_folder}/contract_{name}.docx")
    print("contracts generated!")

from selenium import webdriver
from selenium.webdriver.common.by import by
from selenium.webdriver.support.ui import webdriverwait
from selenium.webdriver.support import expected_conditions as ec
from selenium.webdriver.chrome.options import options
import os

def auto_download_report(url, username, password, download_dir):
    options = options()
    options.add_argument("--headless=new")
    prefs = {"download.default_directory": download_dir, "download.prompt_for_download": false}
    options.add_experimental_option("prefs", prefs)
    
    driver = webdriver.chrome(options=options)
    wait = webdriverwait(driver, 10)
    
    try:
        driver.get(url)
        
        # 等待并输入账号密码
        user_input = wait.until(ec.presence_of_element_located((by.id, "username")))
        user_input.send_keys(username)
        
        pass_input = driver.find_element(by.id, "password")
        pass_input.send_keys(password)
        
        driver.find_element(by.id, "login-btn").click()
        
        # 等待导航栏出现
        wait.until(ec.presence_of_element_located((by.link_text, "daily reports")))
        driver.find_element(by.link_text, "daily reports").click()
        
        # 点击下载按钮
        download_btn = wait.until(ec.element_to_be_clickable((by.class_name, "download-excel")))
        download_btn.click()
        
        print("download triggered. waiting for completion...")
        # 实际项目中需轮询检查文件是否下载完成
        
    finally:
        driver.quit()

from openpyxl.styles import font, patternfill

# 假设 data 是从 selenium 或 requests 抓取到的列表 [(name, price, change), ...]
data = [("aapl", 150.2, -0.5), ("tsla", 200.5, -6.2), ("nvda", 400.0, 2.1)]

wb = workbook()
ws = wb.active
ws.append(["stock", "price", "change%"])

red_fill = patternfill(start_color="ffc7ce", end_color="ffc7ce", fill_type="solid")
red_font = font(color="9c0006", bold=true)

for item in data:
    row_idx = ws.max_row + 1
    ws.append(item)
    
    # 条件格式化：如果跌幅 > 5% (即 change < -5)
    if item[2] < -5.0:
        for cell in ws[row_idx]:
            cell.fill = red_fill
            cell.font = red_font

wb.save("stock_monitor.xlsx")

import os
from openpyxl import load_workbook
from openpyxl.styles import font, color

def merge_reports(folder_path):
    summary_wb = workbook()
    summary_ws = summary_wb.active
    summary_ws.append(['company', 'item', 'amount', 'status'])

    for filename in os.listdir(folder_path):
        if filename.endswith('.xlsx'):
            wb = load_workbook(os.path.join(folder_path, filename))
            ws = wb.active
            company_name = filename.split('.')[0]
            
            for row in ws.iter_rows(min_row=2, values_only=true):
                item, amount = row[0], row[1]
                status = "normal"
                color = "000000"
                
                # 业务逻辑：金额大于 10000 标红
                if amount > 10000:
                    status = "high risk"
                    color = "ff0000"
                
                summary_ws.append([company_name, item, amount, status])
                
                # 动态样式设置 (需要获取单元格对象)
                cell = summary_ws.cell(row=summary_ws.max_row, column=4)
                cell.font = font(color=color, bold=true)

    summary_wb.save('monthly_summary.xlsx')

from docx import document
import pandas as pd

def generate_contracts():
    # 读取数据源
    df = pd.read_excel('clients.xlsx')
    
    # 加载模板
    template = document('contract_template.docx')
    
    for index, row in df.iterrows():
        # 复制模板内容 (深拷贝避免污染)
        doc = document() 
        # 实际项目中通常直接复制模板文件再打开，此处简化逻辑
        # 更好的做法：shutil.copy('template.docx', f'contract_{row["name"]}.docx')
        # 然后打开该文件进行修改
        
        # 模拟替换逻辑 (实际需遍历 paragraph 和 table 进行文本替换)
        # 这里展示核心思路：查找占位符 {{name}} 并替换
        for para in template.paragraphs:
            if '{{name}}' in para.text:
                para.text = para.text.replace('{{name}}', row['name'])
            if '{{date}}' in para.text:
                para.text = para.text.replace('{{date}}', str(row['date']))
        
        # 保存
        doc.save(f"contracts/contract_{row['name']}.docx")

from selenium import webdriver
from selenium.webdriver.common.by import by
from selenium.webdriver.support.ui import webdriverwait
from selenium.webdriver.support import expected_conditions as ec
import time

def price_monitor_and_entry():
    driver = webdriver.chrome()
    wait = webdriverwait(driver, 15)
    
    # 1. 抓取竞品价格
    driver.get("https://competitor-site.com/product/123")
    price_elem = wait.until(ec.visibility_of_element_located((by.class_name, "price-tag")))
    competitor_price = float(price_elem.text.replace('$', ''))
    
    # 2. 登录内部系统
    driver.get("https://internal-system.com/login")
    driver.find_element(by.id, "user").send_keys("admin")
    driver.find_element(by.id, "pass").send_keys("secret")
    driver.find_element(by.id, "submit").click()
    
    # 3. 填入数据
    wait.until(ec.presence_of_element_located((by.name, "price_input")))
    input_box = driver.find_element(by.name, "price_input")
    input_box.clear()
    input_box.send_keys(str(competitor_price * 0.95)) # 定价为竞品的 95%
    
    driver.find_element(by.id, "save_btn").click()
    
    time.sleep(2)
    driver.quit()

# 读取大文件
wb = load_workbook("huge_file.xlsx", read_only=true)
# 写入大文件
wb = workbook(write_only=true)