Python 操作 Excel 的常用方法

import pandas as pd


# 读取整个工作表
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")


# 读取指定列范围（A到C列）
df = pd.read_excel("data.xlsx", usecols="A:C")


# 写入数据（保留原有格式）
with pd.ExcelWriter("output.xlsx", engine="openpyxl", mode="a") as writer:
    df.to_excel(writer, sheet_name="NewSheet", index=False)

from openpyxl import load_workbook


# 打开现有文件
wb = load_workbook("template.xlsx")
ws = wb.active


# 按单元格读写
ws["B2"] = "动态数据"
print(ws["A1"].value)


# 保存修改（会覆盖原文件）
wb.save("modified_template.xlsx")

# 删除空值行
df_clean = df.dropna(subset=["关键列"])


# 替换异常值
df["价格"] = df["价格"].replace(999, pd.NA)


# 类型转换
df["日期"] = pd.to_datetime(df["日期列"], format="%Y/%m/%d")

# 添加公式列（使用 openpyxl）
ws["D2"] = "=B2*C2"
ws["D2"].number_format = "0.00"  # 设置数字格式


# 使用 pandas 计算
df["总价"] = df["单价"] * df["数量"]

# 多条件筛选（pandas）
filtered = df[(df["部门"] == "销售") & (df["金额"] > 10000)]


# 按颜色筛选（openpyxl）
from openpyxl.styles import PatternFill


yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
for row in ws.iter_rows(min_row=2):
    if row[3].fill == yellow_fill:
        print("标记行:", row[0].value)

from openpyxl.styles import Font, Alignment


# 设置字体和居中对齐
bold_font = Font(bold=True, color="FF0000")
center_alignment = Alignment(horizontal="center")
ws["A1"].font = bold_font
ws["A1"].alignment = center_alignment


# 设置列宽/行高
ws.column_dimensions["B"].width = 20
ws.row_dimensions[1].height = 30

from openpyxl.formatting.rule import FormulaRule


# 高亮大于10000的值
red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE")
rule = FormulaRule(formula=["$C2>10000"], stopIfTrue=True, fill=red_fill)
ws.conditional_formatting.add("C2:C100", rule)

ws.freeze_panes = "B2"  # 冻结首行和首列

# 合并多个Excel文件
all_files = glob.glob("sales_data/*.xlsx")
combined_df = pd.concat([pd.read_excel(f) for f in all_files], ignore_index=True)

pivot = pd.pivot_table(df, 
                      values="销售额", 
                      index="地区", 
                      columns="季度",
                      aggfunc="sum")
pivot.to_excel("pivot_table.xlsx")

from openpyxl.chart import BarChart, Reference


# 创建柱状图
chart = BarChart()
data = Reference(ws, min_row=2, max_row=5, min_col=2, max_col=3)
chart.add_data(data, titles_from_data=True)


# 设置图表位置
ws.add_chart(chart, "E2")

# 分块读取（pandas）
chunk_size = 10000
for chunk in pd.read_excel("large_file.xlsx", chunksize=chunk_size):
    process(chunk)


# 只读模式（openpyxl）
wb = load_workbook(filename="huge_file.xlsx", read_only=True)

# 指定列数据类型
dtype_dict = {"ID": str, "Amount": float}
df = pd.read_excel("data.xlsx", dtype=dtype_dict)

# 强制指定日期格式
df = pd.read_excel("data.xlsx", parse_dates=["日期列"], date_parser=lambda x: pd.to_datetime(x, format="%d-%m-%Y"))

# 保存时指定编码
df.to_excel("output.xlsx", encoding="utf-8-sig") 

# 获取合并单元格范围
merged_ranges = ws.merged_cells.ranges
for merged_range in merged_ranges:
    print(f"合并区域: {merged_range.coord}")

with pd.ExcelFile("data.xlsx") as excel:
    for sheet_name in excel.sheet_names:
        df = pd.read_excel(excel, sheet_name=sheet_name)
        print(f"处理工作表: {sheet_name}")

df.rename(columns={
    "OldName1": "新名称1",
    "OldName2": "新名称2"
}, inplace=True)

with pd.ExcelWriter("formatted.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, sheet_name="Sheet1")


    workbook = writer.book
    worksheet = writer.sheets["Sheet1"]


    # 添加自定义格式
    money_format = workbook.add_format({"num_format": "$#,##0"})
    worksheet.set_column("C:C", 15, money_format)