from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Color, PatternFill

# 1. 創(chuàng)建新 workbook
wb = Workbook()
ws = wb.active
ws.title = "Sales Data"

# 2. 寫(xiě)入數(shù)據(jù)
ws['A1'] = "Product"
ws['B1'] = "Price"
ws.append(['Laptop', 1200]) # 追加一行
ws.append(['Mouse', 25])

# 添加公式
# 3. 設(shè)置樣式
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 只寫(xiě)公式字符串，不計(jì)算結(jié)果

# 4. 讀取現(xiàn)有文件
wb_load = load_workbook('report.xlsx')
ws_load = wb_load['Sheet1']
value = ws_load['A1'].value
# 讀取現(xiàn)有文件
wb = load_workbook("report.xlsx", data_only=True) # data_only=True 讀取公式計(jì)算后的值
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. 創(chuàng)建文檔
doc = Document()

# 2. 添加標(biāo)題
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 選項(xiàng) (無(wú)頭模式)
options = Options()
options.add_argument("--headless=new") # 2026 新版無(wú)頭模式
options.add_argument("--disable-gpu")

# 初始化驅(qū)動(dòng) (需確保 chromedriver 版本匹配)
driver = webdriver.Chrome(options=options)

try:
    # 2. 打開(kāi)網(wǎng)頁(yè)
    driver.get("https://www.example.com")
    
    # 3.查找元素 (推薦 WebDriverWait 顯式等待，此處簡(jiǎn)化演示)
    search_box = driver.find_element(By.NAME, "q")
    # 4. 模擬操作
    search_box.send_keys("Python Automation")
    search_box.send_keys(Keys.RETURN)
    
    time.sleep(2) # 實(shí)際生產(chǎn)請(qǐng)用 WebDriverWait
    
    # 5. 獲取數(shù)據(jù)
    results = driver.find_elements(By.CSS_SELECTOR, ".result-title")
    print(f"Found {len(results)} results")
    
    # 6.截圖
    driver.save_screenshot("result.png")
    
finally:
    # 7.關(guān)閉瀏覽器
    driver.quit() # 務(wù)必關(guān)閉瀏覽器

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
                
                # 添加來(lái)源文件名作為新的一列
                new_row = list(row) + [filename]
                master_ws.append(new_row)
    
    # 添加匯總公式示例
    last_row = master_ws.max_row
    col_b = get_column_letter(2) # 假設(shè)第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]
        
        # 復(fù)制模板內(nèi)容 (深拷貝避免污染)
        import copy
        doc = copy.deepcopy(template)
        
        # 替換占位符 (簡(jiǎn)單文本替換，復(fù)雜情況需遍歷 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))
        
        # 處理表格內(nèi)的替換 (略，邏輯類(lèi)似，需遍歷 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)
        
        # 等待并輸入賬號(hào)密碼
        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()
        
        # 等待導(dǎo)航欄出現(xiàn)
        wait.until(EC.presence_of_element_located((By.LINK_TEXT, "Daily Reports")))
        driver.find_element(By.LINK_TEXT, "Daily Reports").click()
        
        # 點(diǎn)擊下載按鈕
        download_btn = wait.until(EC.element_to_be_clickable((By.CLASS_NAME, "download-excel")))
        download_btn.click()
        
        print("Download triggered. Waiting for completion...")
        # 實(shí)際項(xiàng)目中需輪詢(xún)檢查文件是否下載完成
        
    finally:
        driver.quit()

from openpyxl.styles import Font, PatternFill

# 假設(shè) 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"
                
                # 業(yè)務(wù)邏輯：金額大于 10000 標(biāo)紅
                if amount > 10000:
                    status = "High Risk"
                    color = "FF0000"
                
                summary_ws.append([company_name, item, amount, status])
                
                # 動(dòng)態(tài)樣式設(shè)置 (需要獲取單元格對(duì)象)
                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():
    # 讀取數(shù)據(jù)源
    df = pd.read_excel('clients.xlsx')
    
    # 加載模板
    template = Document('contract_template.docx')
    
    for index, row in df.iterrows():
        # 復(fù)制模板內(nèi)容 (深拷貝避免污染)
        doc = Document() 
        # 實(shí)際項(xiàng)目中通常直接復(fù)制模板文件再打開(kāi)，此處簡(jiǎn)化邏輯
        # 更好的做法：shutil.copy('template.docx', f'contract_{row["name"]}.docx')
        # 然后打開(kāi)該文件進(jìn)行修改
        
        # 模擬替換邏輯 (實(shí)際需遍歷 paragraph 和 table 進(jìn)行文本替換)
        # 這里展示核心思路：查找占位符 {{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. 抓取競(jìng)品價(jià)格
    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. 登錄內(nèi)部系統(tǒng)
    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. 填入數(shù)據(jù)
    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)) # 定價(jià)為競(jìng)品的 95%
    
    driver.find_element(By.ID, "save_btn").click()
    
    time.sleep(2)
    driver.quit()

# 讀取大文件
wb = load_workbook("huge_file.xlsx", read_only=True)
# 寫(xiě)入大文件
wb = Workbook(write_only=True)