# 典型的“面向單元格”編程
ws['A1'] = "姓名"
ws['B1'] = "年齡"
ws['A2'] = user.name
ws['B2'] = user.age
# ... 如果有幾十個字段，代碼將變得冗長且難以維護(hù)

# 偽代碼：理想中的操作方式
row_obj = User(name="Alice", age=25)
sheet.append_row(row_obj) # 自動映射屬性到對應(yīng)列

from openpyxl import Workbook, load_workbook

# 寫入模式（適用于生成新文件）
wb = Workbook()
ws = wb.active
ws.title = "Users"

# 追加模式（適用于大數(shù)據(jù)寫入，內(nèi)存占用低）
# 注意：需要使用 keep_vba=True 或特定參數(shù)，但通常 load_workbook 用于修改
wb = load_workbook('data.xlsx')
ws = wb['Users']

# 讀取表頭
headers = [cell.value for cell in ws[1]]

# 讀取數(shù)據(jù)行（跳過表頭）
for row in ws.iter_rows(min_row=2, values_only=True):
    # row 是一個元組，例如 ('Alice', 25)
    print(row)

from openpyxl.styles import Font, Alignment, PatternFill

# 設(shè)置表頭樣式
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")

for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

from dataclasses import dataclass, fields
from typing import Any

@dataclass
class Employee:
    id: int
    name: str
    department: str
    salary: float
    is_active: bool = True

class ExcelMapper:
    def __init__(self, model_class):
        self.model_class = model_class
        # 獲取字段名列表，作為表頭
        self.headers = [f.name for f in fields(model_class)]
    
    def write_header(self, worksheet):
        """寫入表頭"""
        for col_idx, header in enumerate(self.headers, 1):
            worksheet.cell(row=1, column=col_idx, value=header)
            
    def obj_to_row(self, obj, worksheet, row_idx):
        """將單個對象寫入指定行"""
        for col_idx, f in enumerate(fields(self.model_class), 1):
            value = getattr(obj, f.name)
            worksheet.cell(row=row_idx, column=col_idx, value=value)

    def rows_from_sheet(self, worksheet):
        """從 Excel 讀取并還原為對象列表"""
        objs = []
        for row in worksheet.iter_rows(min_row=2, values_only=True):
            if not any(row): # 跳過空行
                continue
            # 將元組解包為字典，再實例化對象
            data_dict = dict(zip(self.headers, row))
            objs.append(self.model_class(**data_dict))
        return objs

from openpyxl import Workbook
import random

# 1. 準(zhǔn)備數(shù)據(jù)
employees = [
    Employee(
        id=i,
        name=f"User_{i}",
        department=random.choice(["Tech", "HR", "Sales"]),
        salary=random.randint(5000, 20000)
    )
    for i in range(1, 1001)
]

# 2. 初始化 Excel
wb = Workbook()
ws = wb.active
ws.title = "Employee Report"

# 3. 使用映射器
mapper = ExcelMapper(Employee)

# 寫入表頭
mapper.write_header(ws)

# 批量寫入數(shù)據(jù)
print("正在寫入數(shù)據(jù)...")
for idx, emp in enumerate(employees, 2): # 從第2行開始
    mapper.obj_to_row(emp, ws, idx)

# 4. 保存文件
wb.save("employees.xlsx")
print("文件保存成功！")

from openpyxl import Workbook

def data_generator():
    # 模擬海量數(shù)據(jù)流
    for i in range(100000):
        yield (i, f"Name_{i}", "Dept", 5000)

wb = Workbook(write_only=True)
ws = wb.create_sheet()

# 在只寫模式下，不能使用 ws.append，而是直接使用 ws.append
# 但注意：write_only 模式下，無法讀取之前的行，也無法修改樣式（需預(yù)先定義）
# 這里的重點(diǎn)是：數(shù)據(jù)流式寫入，內(nèi)存占用極低
for row in data_generator():
    ws.append(row)

wb.save("big_data.xlsx")

from openpyxl.utils import range_boundaries

# 合并 A1:B1
ws.merge_cells('A1:B1')
ws['A1'] = "User Info" # 只需要在左上角賦值

def get_merged_cell_value(worksheet, cell_coordinate):
    val = worksheet[cell_coordinate].value
    if val is not None:
        return val
    # 如果當(dāng)前單元格為空，檢查它是否在合并區(qū)域內(nèi)
    for merged_range in worksheet.merged_cells.ranges:
        if cell_coordinate in merged_range:
            # 返回合并區(qū)域左上角的值
            return worksheet[merged_range.start_cell.coordinate].value
    return None

# 在 ExcelMapper 的 rows_from_sheet 方法中增強(qiáng)
import datetime

def convert_type(value, target_type):
    if target_type == bool and isinstance(value, str):
        return value.lower() in ('true', '1', 'yes')
    if target_type == int and isinstance(value, float):
        return int(value)
    return value

# ... 在解包時調(diào)用 convert_type ...