# 安装
pip install openpyxl

# 为什么用 openpyxl 而不是手动操作 Excel？
# 手动操作：打开 → 复制 → 粘贴 → 保存 → 重复 100 次...
# openpyxl：写一个循环，100 个文件 10 秒搞定

# 常见场景：
# ✅ 批量生成报表（如月度销售报告）
# ✅ 批量生成工资条（每人一张表）
# ✅ 合并多个 Excel 文件
# ✅ 数据清洗和转换
# ✅ 自动生成图表

from openpyxl import load_workbook

# === 打开工作簿 ===
wb = load_workbook('sales_data.xlsx')

# 查看所有工作表名
print(wb.sheetnames)  # ['Sheet1', 'Sheet2', '汇总']

# 获取工作表
ws = wb.active                    # 当前活动工作表
ws = wb['Sheet1']                 # 按名称获取
ws = wb.worksheets[0]             # 按索引获取

# === 读取单元格 ===
# 方式1：坐标字符串
value = ws['A1'].value             # 读取 A1 单元格的值
value = ws['B3'].value

# 方式2：行号列号（从1开始！）
value = ws.cell(row=1, column=1).value   # 等同于 A1
value = ws.cell(row=3, column=2).value   # 等同于 B3

# === 读取范围 ===
# 读取一列
for row in ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=1):
    for cell in row:
        print(cell.value)

# 读取多列
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=5):
    name = row[0].value     # A 列
    price = row[1].value    # B 列
    qty = row[2].value      # C 列
    total = row[3].value    # D 列
    print(f'{name}: {price} × {qty} = {total}')

# === 读取所有数据（推荐方式）===
def read_excel_data(filepath):
    """读取 Excel 所有数据为列表"""
    wb = load_workbook(filepath)
    ws = wb.active

    headers = []
    data = []

    for i, row in enumerate(ws.iter_rows(values_only=True)):
        if i == 0:
            headers = list(row)      # 第一行是表头
        else:
            data.append(dict(zip(headers, row)))

    wb.close()
    return headers, data

headers, data = read_excel_data('sales_data.xlsx')
print(f'表头: {headers}')
print(f'数据行数: {len(data)}')
print(f'第一行: {data[0]}')

from openpyxl import Workbook

# === 创建新工作簿 ===
wb = Workbook()
ws = wb.active
ws.title = '销售数据'  # 重命名工作表

# === 写入数据 ===
# 方式1：直接指定单元格
ws['A1'] = '姓名'
ws['B1'] = '销售额'
ws['C1'] = '日期'

ws['A2'] = '小明'
ws['B2'] = 15000
ws['C2'] = '2026-01-15'

# 方式2：通过行号列号
ws.cell(row=3, column=1, value='小红')
ws.cell(row=3, column=2, value=18000)
ws.cell(row=3, column=3, value='2026-01-16')

# === 批量写入（推荐）===
data = [
    ['姓名', '销售额', '日期'],
    ['小明', 15000, '2026-01-15'],
    ['小红', 18000, '2026-01-16'],
    ['小刚', 12000, '2026-01-17'],
    ['小美', 22000, '2026-01-18'],
    ['小强', 16500, '2026-01-19'],
]

for row in data:
    ws.append(row)  # 逐行追加

# === 保存 ===
wb.save('output.xlsx')
wb.close()
print('✅ 文件已保存: output.xlsx')

# === 从 Pandas DataFrame 写入 ===
import pandas as pd

df = pd.DataFrame({
    '姓名': ['小明', '小红', '小刚'],
    '销售额': [15000, 18000, 12000],
})

# 方式1：pandas 直接写（简单）
df.to_excel('pandas_output.xlsx', index=False)

# 方式2：openpyxl 写入已有工作簿（灵活）
wb = Workbook()
ws = wb.active
ws.title = '销售数据'

# 写入表头
for col, header in enumerate(df.columns, 1):
    ws.cell(row=1, column=col, value=header)

# 写入数据
for row_idx, row_data in enumerate(df.values, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

wb.save('formatted_output.xlsx')
wb.close()

from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active

# === 列宽设置 ===
ws.column_dimensions['A'].width = 20       # A 列宽 20
ws.column_dimensions['B'].width = 15       # B 列宽 15
ws.column_dimensions['C'].width = 30       # C 列宽 30

# 批量设置列宽
for col in range(1, 11):
    letter = get_column_letter(col)
    ws.column_dimensions[letter].width = 15

# === 行高设置 ===
ws.row_dimensions[1].height = 30           # 第1行高 30

# 批量设置行高
for row in range(1, 20):
    ws.row_dimensions[row].height = 25

# === 合并单元格 ===
ws.merge_cells('A1:D1')                    # 合并 A1 到 D1
ws['A1'] = '2026年销售报表'
ws['A1'].font = Font(size=16, bold=True)

# 取消合并
# ws.unmerge_cells('A1:D1')

# === 插入行/列 ===
ws.insert_rows(2)          # 在第2行前插入一行
ws.insert_cols(3)          # 在第3列前插入一列

# 删除行/列
ws.delete_rows(2)          # 删除第2行
ws.delete_cols(3)          # 删除第3列

# === 复制工作表 ===
ws_copy = wb.copy_worksheet(ws)
ws_copy.title = '备份'

# === 删除工作表 ===
# del wb['Sheet2']
# wb.remove(ws_copy)

wb.save('cell_ops.xlsx')
wb.close()

from openpyxl import Workbook
from openpyxl.styles import (
    Font, PatternFill, Alignment, Border, Side, numbers
)

wb = Workbook()
ws = wb.active

# === 字体样式 ===
ws['A1'] = '标题'
ws['A1'].font = Font(
    name='微软雅黑',       # 字体
    size=16,               # 字号
    bold=True,             # 加粗
    italic=False,          # 斜体
    color='FFFFFF',        # 字体颜色（白色）
)

# === 背景填充 ===
ws['A1'].fill = PatternFill(
    start_color='07C160',  # 背景色（微信绿）
    end_color='07C160',
    fill_type='solid'      # 纯色填充
)

# === 对齐方式 ===
ws['A1'].alignment = Alignment(
    horizontal='center',   # 水平居中
    vertical='center',     # 垂直居中
    wrap_text=True          # 自动换行
)

# === 边框 ===
thin_border = Border(
    left=Side(style='thin', color='000000'),
    right=Side(style='thin', color='000000'),
    top=Side(style='thin', color='000000'),
    bottom=Side(style='thin', color='000000'),
)
ws['A1'].border = thin_border

# === 数字格式 ===
ws['B2'] = 15000
ws['B2'].number_format = '#,##0.00'        # 千分位 + 2位小数
ws['B2'].number_format = '¥#,##0.00'       # 人民币格式
ws['B2'].number_format = '0.00%'           # 百分比格式
ws['B2'].number_format = 'YYYY-MM-DD'      # 日期格式

# === 批量设置样式（推荐写法）===
def set_header_style(cell):
    """设置表头样式"""
    cell.font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')
    cell.fill = PatternFill(start_color='07C160', fill_type='solid')
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.border = thin_border

def set_data_style(cell):
    """设置数据单元格样式"""
    cell.font = Font(name='微软雅黑', size=11)
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.border = thin_border

# 写入表头并设置样式
headers = ['姓名', '销售额', '日期', '状态']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    set_header_style(cell)

# 写入数据并设置样式
data = [
    ['小明', 15000, '2026-01-15', '已完成'],
    ['小红', 18000, '2026-01-16', '进行中'],
    ['小刚', 12000, '2026-01-17', '已完成'],
]
for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        cell = ws.cell(row=row_idx, column=col_idx, value=value)
        set_data_style(cell)
        # 销售列特殊格式
        if col_idx == 2:
            cell.number_format = '¥#,##0.00'

# === 条件样式（数据条/色阶/图标集）===
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule

# 色阶（最小值红色，中间黄色，最大值绿色）
ws.conditional_formatting.add(
    'B2:B100',
    ColorScaleRule(
        start_type='min', start_color='FF6B6B',
        mid_type='percentile', mid_value=50, mid_color='FFD93D',
        end_type='max', end_color='07C160',
    )
)

# 数据条
ws.conditional_formatting.add(
    'B2:B100',
    DataBarRule(start_type='min', end_type='max', color='07C160')
)

wb.save('styled.xlsx')
wb.close()

💡 样式速查表：

• 字体：Font(name, size, bold, color)
• 背景：PatternFill(start_color, fill_type='solid')
• 对齐：Alignment(horizontal, vertical, wrap_text)
• 边框：Border(left, right, top, bottom)
• 数字：number_format = '¥#,##0.00'
• 条件：conditional_formatting.add(range, rule)

6. 公式计算

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 写入数据
ws['A1'] = '商品'
ws['B1'] = '单价'
ws['C1'] = '数量'
ws['D1'] = '小计'

data = [
    ['苹果', 5.5, 10],
    ['香蕉', 3.8, 20],
    ['橘子', 4.2, 15],
]
for i, row in enumerate(data, 2):
    for j, val in enumerate(row, 1):
        ws.cell(row=i, column=j, value=val)

# 写入公式（用 = 开头）
ws['D2'] = '=B2*C2'           # 苹果小计
ws['D3'] = '=B3*C3'           # 香蕉小计
ws['D4'] = '=B4*C4'           # 橘子小计
ws['D5'] = '=SUM(D2:D4)'     # 合计
ws['D6'] = '=AVERAGE(D2:D4)' # 平均值
ws['D7'] = '=MAX(D2:D4)'     # 最大值
ws['D8'] = '=MIN(D2:D4)'     # 最小值
ws['D9'] = '=COUNT(D2:D4)'   # 计数

# 带条件的公式
ws['E2'] = '=IF(D2>50,"大单","小单")'

# VLOOKUP 公式
ws['F2'] = '=VLOOKUP(A2,$A$2:$D$4,4,FALSE)'

# 跨工作表引用
# ws['G2'] = "=Sheet2!A1"

# 注意：openpyxl 写入的公式需要在 Excel 中打开才能计算
# openpyxl 本身不会计算公式结果，读取时也只能拿到公式字符串
# 如果需要在 Python 中得到计算结果，有两种方式：
# 1. 用 Python 先算好，再把数值写入单元格
# 2. 使用第三方库 formulas 或 pylightxl 进行公式求值
# 例如：先把 (B2*C2) 的结果算出来，再 ws['D2'] = 5.5 * 10

wb.save('formula.xlsx')
wb.close()

7. 图表生成

from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, PieChart, Reference

wb = Workbook()
ws = wb.active

# 准备数据
ws['A1'] = '月份'
ws['B1'] = '销售额'
ws['C1'] = '利润'

months = ['1月', '2月', '3月', '4月', '5月', '6月']
sales = [12000, 15000, 13500, 18000, 16500, 22000]
profit = [3000, 4500, 3200, 5500, 4800, 7000]

for i, (month, s, p) in enumerate(zip(months, sales, profit), 2):
    ws.cell(row=i, column=1, value=month)
    ws.cell(row=i, column=2, value=s)
    ws.cell(row=i, column=3, value=p)

# === 柱状图 ===
chart = BarChart()
chart.type = 'col'                # 'col'=垂直柱状图, 'bar'=水平
chart.title = '月度销售额'
chart.x_axis.title = '月份'
chart.y_axis.title = '金额'
chart.style = 10                  # 图表样式

# 数据引用
data_ref = Reference(ws, min_col=2, min_row=1, max_row=7)
cats_ref = Reference(ws, min_col=1, min_row=2, max_row=7)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats_ref)
chart.shape = 4                   # 图表元素形状样式（4=圆角矩形）
chart.width = 20                  # 图表宽度（单位：厘米）
chart.height = 12                 # 图表高度（单位：厘米）

ws.add_chart(chart, 'A10')

# === 折线图 ===
line_chart = LineChart()
line_chart.title = '月度趋势'
line_chart.x_axis.title = '月份'
line_chart.y_axis.title = '金额'

data_ref = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=7)
cats_ref = Reference(ws, min_col=1, min_row=2, max_row=7)

line_chart.add_data(data_ref, titles_from_data=True)
line_chart.set_categories(cats_ref)
line_chart.width = 20
line_chart.height = 12

ws.add_chart(line_chart, 'A28')

# === 饼图 ===
pie_chart = PieChart()
pie_chart.title = '销售占比'

# 饼图只需要一列数据
pie_data = Reference(ws, min_col=2, min_row=1, max_row=7)
pie_cats = Reference(ws, min_col=1, min_row=2, max_row=7)

pie_chart.add_data(pie_data, titles_from_data=True)
pie_chart.set_categories(pie_cats)
pie_chart.width = 15
pie_chart.height = 10

ws.add_chart(pie_chart, 'A46')

wb.save('charts.xlsx')
wb.close()

8. 数据验证与保护

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.worksheet.properties import PageSetupProperties

wb = Workbook()
ws = wb.active

# === 数据验证（下拉列表）===
# 在 A 列创建下拉列表
dv = DataValidation(
    type='list',
    formula1='"已完成,进行中,未开始"',
    allow_blank=True,
)
dv.error = '请从下拉列表中选择'
dv.errorTitle = '输入错误'
ws.add_data_validation(dv)
dv.add('A2:A100')

# 数字范围验证
dv_num = DataValidation(
    type='whole',
    operator='between',
    formula1='0',
    formula2='100',
)
dv_num.error = '请输入 0-100 的整数'
ws.add_data_validation(dv_num)
dv_num.add('C2:C100')

# === 工作表保护 ===
ws.protection.sheet = True
ws.protection.password = 'mypassword'

# 提示：要让某些单元格可编辑，需要先解锁它们
# from openpyxl.styles import Protection
# ws['A1'].protection = Protection(locked=False)  # 解锁 A1
# 然后再开启工作表保护，被解锁的单元格仍可编辑

# === 冻结窗格 ===
ws.freeze_panes = 'A2'      # 冻结第一行（滚动时表头始终可见）

# === 打印设置 ===
ws.print_title_rows = '1:1'  # 打印时每页都重复第一行
# 注意：pageSetUpPr 默认为 None，必须先实例化才能设置属性
ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0
ws.page_setup.orientation = 'landscape'  # 横向打印

wb.save('validated.xlsx')
wb.close()

9. 实战：批量生成工资条

"""
实战场景：HR 需要给每个员工发一张独立的工资条 Excel
输入：一个包含所有员工工资数据的大 Excel
输出：每人一个独立的工资条文件
"""
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
import os

def generate_payslips(source_file, output_dir='payslips'):
    """批量生成工资条"""

    os.makedirs(output_dir, exist_ok=True)

    # 读取源数据
    wb_src = load_workbook(source_file)
    ws_src = wb_src.active

    # 获取表头
    headers = [cell.value for cell in ws_src[1]]

    # 样式定义
    header_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')
    header_fill = PatternFill(start_color='07C160', fill_type='solid')
    data_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'),
    )

    # 逐行生成工资条
    count = 0
    for row_idx in range(2, ws_src.max_row + 1):
        row_data = [ws_src.cell(row=row_idx, column=c).value for c in range(1, len(headers) + 1)]

        # 跳过空行
        if all(v is None for v in row_data):
            continue

        # 创建新的工作簿
        wb_pay = Workbook()
        ws_pay = wb_pay.active
        ws_pay.title = '工资条'

        # 设置列宽
        for col in range(1, len(headers) + 1):
            ws_pay.column_dimensions[get_column_letter(col)].width = 18

        # 写入标题
        ws_pay.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(headers))
        title_cell = ws_pay.cell(row=1, column=1, value='工资条')
        title_cell.font = Font(name='微软雅黑', size=16, bold=True)
        title_cell.alignment = Alignment(horizontal='center', vertical='center')
        ws_pay.row_dimensions[1].height = 40

        # 写入表头
        for col, header in enumerate(headers, 1):
            cell = ws_pay.cell(row=2, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = center_align
            cell.border = thin_border

        # 写入数据
        for col, value in enumerate(row_data, 1):
            cell = ws_pay.cell(row=3, column=col, value=value)
            cell.font = data_font
            cell.alignment = center_align
            cell.border = thin_border
            # 金额格式
            if isinstance(value, (int, float)) and '薪' in str(headers[col-1]):
                cell.number_format = '¥#,##0.00'

        # 保存
        name = str(row_data[0]).replace('/', '_')  # 文件名安全处理
        filename = f'{output_dir}/工资条_{name}.xlsx'
        wb_pay.save(filename)
        wb_pay.close()
        count += 1

    wb_src.close()
    print(f'✅ 生成完成！共 {count} 个工资条，保存在 {output_dir}/')

# 使用
# generate_payslips('工资表.xlsx', '工资条输出')

10. 今日练习

🏋️ 练习 1：创建学生成绩表

# 用 openpyxl 创建一个学生成绩表：
# - 设置表头样式（绿色背景、白色字体、居中）
# - 10 个学生 × 3 门课的成绩
# - 用公式计算每人总分和平均分
# - 用条件格式高亮低于 60 分的成绩（红色）

🏋️ 练习 2：合并多个 Excel

# 创建 3 个 Excel 文件（模拟 3 个月的销售数据）
# 写一个函数，把它们合并为一个 Excel：
# - 每个月一个工作表
# - 添加一个"汇总"工作表，计算总计

🏋️ 练习 3：自动生成报表

# 从一个销售数据 Excel 中：
# 1. 按品类分组统计
# 2. 生成带图表的 Excel 报告
# 3. 设置打印格式（横向、每页重复表头）

11. 今日小结

🚀 明日预告：Day 82 — 邮件自动化

用 Python 自动发邮件！smtplib + email 库，支持纯文本、HTML、附件、批量发送。配合 openpyxl，自动生成报表并发送给老板！