Python 表格数据处理：pandas与openpyxl

概览与选用建议

• pandas：面向 "数据" —— 读取（CSV/Excel/SQL/JSON）、清洗（缺失/类型/字符串）、聚合（groupby/agg）、重塑（pivot/melt）、时间序列（resample/shift）和快速 I/O（read_/to_）。它输出数据结构（DataFrame）给后续展示或持久化。
• openpyxl：专注对 Excel (.xlsx) 文件的低层级操作：逐单元格读写、样式、合并单元格、图表、数据验证、公式、表格（Table）等。适合需要生成或修改 Excel 报表格式（样式/图表/公式）的场景。

常用组合模式：用 pandas.read_excel / read_csv 快速读写表格数据（性能好、API 简洁）；用 openpyxl 在 pandas 输出的文件上进一步“美化”（格式、合并单元格、图表、条件格式等）。

---

环境与前置

pip install pandas openpyxl

在 pandas.to_excel() / read_excel() 时，指定 engine='openpyxl' 会使用 openpyxl 读写 .xlsx。

---

一、pandas 模块

1.1 核心概念

• Series：带索引的一维数据。类似于数组 + 索引。常用属性 .values, .index, .dtype, .name。

• DataFrame：二维表格，行索引 + 列索引。核心方法如 .head(), .info(), .describe()。

• Index：索引对象，可做集合运算与对齐。

• dtype：类型系统，包括 int64, float64, object, category, datetime64[ns] 以及 pandas 扩展类型 Int64, string, boolean（支持缺失值）。

1.2 常用函数与方法表

创建数据表

函数	关键参数（含义）	简要示例
pd.Series(data, index)	创建 Series	pd.Series([1,2,3], index=['a','b','c'])
pd.DataFrame(data, columns)	创建 DataFrame data, 原始数据 ;columns=None 指定列名（可选）	pd.DataFrame({'A':[1,2],'B':[3,4]}) pd.DataFrame(restart_times, columns=["Restart Time"])

输入输出

方法	说明	示例
pd.read_csv(filepath_or_buffer, sep=',', header='infer', names=None, index_col=None, usecols=None, dtype=None, parse_dates=None, chunksize=None, encoding=None)	filepath_or_buffer：文件或类文件对象；sep：分隔符；header：标题行位置或 None；names：自定义列名；index_col：索引列；usecols：读取的列；dtype：强制 dtype；parse_dates：需解析为 datetime 的列；chunksize：分块读取	df = pd.read_csv('data.csv', parse_dates=['date'], dtype={'id':str}, usecols=['id','date','sales'])
pd.read_excel(io, sheet_name=0, header=0, usecols=None, dtype=None, parse_dates=None, engine=None)	io：文件路径或文件对象；sheet_name：sheet 名称/索引或 None 或 list；usecols：列选择；engine：如 openpyxl	xls = pd.read_excel('workbook.xlsx', sheet_name='Sheet1', parse_dates=['日期'])
pd.ExcelWriter(path, engine='openpyxl')	多表写入或更灵活写入	with pd.ExcelWriter('out.xlsx', engine='openpyxl') as w: df.to_excel(w, 'Sheet1')
DataFrame.to_csv(path, index=False)	写 CSV	df.to_csv('out.csv', index=False)
DataFrame.to_excel(path, sheet_name='Sheet1', engine=None)	写 Excel（可指定 engine）	df.to_excel('out.xlsx', engine='openpyxl', index=False)
.to_sql()	写数据库	-

import pandas as pd

df = pd.read_excel("sales.xlsx", sheet_name="2025", engine="openpyxl")  # 读 xlsx
df = pd.read_csv("sales.csv", dtype={"hostname": "string"}, parse_dates=["date"])

# 写出（xlsx，后续用 openpyxl 再“美化”）
df.to_excel("out.xlsx", index=False, engine="openpyxl")

# 写入 .xlsx
with pd.ExcelWriter('out.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, index=False)

提示：.xls 老格式不由 openpyxl 支持；xlsx/xlsm 用 engine="openpyxl"。

xlsx转化为csv

# xlsx转化为csv

import pandas as pd


def convert_xlsx_to_csv(input_file, output_file):
    # 读取 Excel 文件
    df = pd.read_excel(input_file, engine='openpyxl')  # 使用 openpyxl 处理 .xlsx 文件
    # 保存为 CSV，保持原始数据格式（不丢失数值类型）
    df.to_csv(output_file, index=False, encoding='utf-8-sig')
    print(f"转换完成，已保存到: {output_file}")

# 示例用法
if __name__ == "__main__":
    input_xlsx = './Windows-Restarted.xlsx'  # 输入的 Excel 文件路径
    output_csv = 'a.csv'  # 输出的 CSV 文件路径

    convert_xlsx_to_csv(input_xlsx, output_csv)

查看/检查数据

方法	关键参数	示例
df.head(n=5) / df.tail(n=5)	n：行数	df.head(3)
df.info(verbose=None, memory_usage=None)	verbose 控制是否完全列出	df.info()
df.describe(include='all')	include：统计对象类型	df.describe()
df.shape / df.dtypes	属性	rows, cols = df.shape

# 列选择
df[["region", "amount"]]

# 行筛选（布尔条件）
df.loc[(df["region"] == "APAC") & (df["amount"] > 1000), ["sku", "amount"]]

# 位置索引
df.iloc[0:10, 1:3]

索引与选取

方法/属性	关键参数	示例
df.loc[row_labels, col_labels]	标签定位；支持布尔掩码、切片	df.loc[0,'A']
df.iloc[row_positions, col_positions]	位置定位（整数）	df.iloc[0:5, 0:3]
df.at[row_label, col_label] / df.iat[r, c]	快速标量访问（单元素）	df.at[10, 'sales'] = 999
df.set_index(keys, inplace=False, drop=True)	设置索引	df = df.set_index('date')
df.reset_index(drop=False)	还原索引	df.reset_index(inplace=True)

清洗与类型转换

方法	关键参数	示例
df.isnull() / df.notnull()	无	df[df['sales'].isnull()]
df.dropna(axis=0, how='any', subset=None, inplace=False)	axis：行/列；how：any/all；subset：检查特定列	df.dropna(subset=['sales'], inplace=True)
df.fillna(value=None, method=None, inplace=False)	value：替换值；method：ffill/bfill	df['sales'].fillna(0, inplace=True)
df.astype(dtype)	类型转换，可传 dict	df['id'] = df['id'].astype(str)
df.rename(columns={'old':'new'}, inplace=False)	重命名列	df.rename(columns={'amt':'amount'})
df.drop(columns=['c1','c2'], inplace=False)	删除列	df.drop(columns=['tmp'])
df.replace(to_replace, value)	替换值/映射	df['status'].replace({'Y':'Yes','N':'No'})
pd.to_datetime(arg, format=None, errors='raise')	解析时间	df['date'] = pd.to_datetime(df['date'], errors='coerce')

排序/去重/查询

方法	关键参数	示例
df.sort_values(by, ascending=True)	by：列或列列表	df.sort_values(['date','region'])
df.drop_duplicates(subset=None, keep='first')	subset：依据列	df.drop_duplicates(subset=['id'], keep='last')
df.query(expr)	字符串表达式过滤	df.query('sales>1000 and region=="East"')
df.eval(expr)	在 dataframe 内部高效计算表达式	df.eval("amount = price * qty", inplace=True)

数据统计与计算

方法	说明	示例
.sum() / .mean() / .median()	聚合运算	df['A'].mean()
.value_counts()	频次统计	df['A'].value_counts()
.groupby(col)	分组聚合	df.groupby('dept')['salary'].mean()
.pivot_table()	生成透视表	-
.corr()	相关系数矩阵	df.corr()

二、openpyxl 模块

2.1 核心对象与工作模式

• Workbook：工作簿对象（openpyxl.Workbook() 或 load_workbook()）。

• Worksheet：工作表对象，通过 wb.active 或 wb[sheetname] 获取。

• Cell：单元格对象，支持 .value, .number_format, .font 等属性。

工作模式：

• 默认模式（读写随机访问）适合小/中等文件，支持样式/图表。

• read_only=True：流式低内存读取，大文件用。读取得到的是只读单元格，不能修改。

• write_only=True：流式写入，效率高但不能随机访问已有单元格（只 append 写）。

2.2 常用函数与方法表

类/方法	说明	示例
Workbook()	创建新工作簿	wb = Workbook()
load_workbook(filename)	打开已有 Excel 文件	wb = load_workbook('demo.xlsx')
wb.active	获取当前活动工作表	ws = wb.active
wb[sheetname]	获取指定工作表	ws = wb['Sheet1']
wb.create_sheet(title)	新建工作表	wb.create_sheet('Report')
ws.iter_rows	按行便利单元格	ws.iter_rows(min_row=2, values_only=True)
ws.cell(row, col, value)	读写单元格	ws.cell(1,1,'Hello')
ws['A1']	按坐标读写单元格	ws['A1'].value
ws.append(list)	追加一行数据	ws.append([1,2,3])
ws.insert_rows(idx)	插入行	ws.insert_rows(2)
ws.merge_cells('A1:D1')	合并单元格	-
Font/Border/PatternFill	设置字体/边框/背景	-
ws.add_chart(chart, pos)	插入图表	-
ws.add_image(img, pos)	插入图片	-
wb.save(filename)	保存工作簿	wb.save('new.xlsx')

2.3 用 openpyxl 创建一个格式化的报表并保存

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Summary"

# 写标题行
headers = ["Region", "Sales", "Orders"]
ws.append(headers)

# 写数据
data = [
    ['North', 12000, 34],
    ['South', 15000, 42],
    ['East', 9000, 20],
]
for row in data:
    ws.append(row)

# 合并单元格示例
ws.merge_cells('E1:G1')
ws['E1'] = "Report generated by openpyxl"

wb.save("report.xlsx")

2.4 遍历单元格

iter_rows 是 openpyxl.worksheet.worksheet.Worksheet 的一个方法，用来按行返回单元格对象（或值）。
原型：

iter_rows(
    min_row=None,
    max_row=None,
    min_col=None,
    max_col=None,
    values_only=False,
    *,
    range_string=None
)

参数一览（全部可选）

参数名	类型	默认值	含义
min_row	int	None	起始行号（含）。省略时从工作表第一行有数据的地方开始。
max_row	int	None	结束行号（含）。省略时到工作表最后一行有数据的地方。
min_col	int	None	起始列号（1-based）。省略时从第一列有数据的地方开始。
max_col	int	None	结束列号（1-based）。省略时到最后一列有数据的地方。
values_only	bool	False	False → 返回 Cell 对象；True → 直接返回单元格的值。
range_string	str	None	Excel 风格的区域字符串，如 "A1:C3"。如果给出，前 4 个行列参数会被忽略。

示例

# 遍历 A1:C3 区域，只要值
for row in ws.iter_rows(min_row=1, max_row=3,
                        min_col=1, max_col=3,
                        values_only=True):
    print(row)          # row 是 tuple，支持切片操作
row[1] 就是 当前行第 2 列 的值（Python 索引从 0 开始）
# 输出：
# (A1_value, B1_value, C1_value)
# (A2_value, B2_value, C2_value)
# (A3_value, B3_value, C3_value)

用 range_string 写法

for row in ws.iter_rows(range_string="A1:C3", values_only=True):
    print(row)          # 效果同上

边界规则小结

• 四个边界参数都省略 → 遍历整个 used range（有数据的矩形区域）。
• 只给 min_row → 从该行到 used range 末尾。
• 只给 max_row → 从第一行到该行。
• 列同理。

与 iter_cols 的区别

• iter_rows 按行优先返回；

• iter_cols 按列优先返回，其余参数完全一致。

三、openpyxl 与 pandas 的结合场景

1. pandas 用于数据分析和生成结果表格。

2. openpyxl 用于美化表格（设置字体、颜色、合并单元格、插入图表）。

3. 典型应用：报表自动化 → 数据清洗 (pandas) → 输出 Excel (pandas) → 样式修饰 (openpyxl)。

示例：

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font

# pandas 生成 Excel
df = pd.DataFrame({'Name':['Tom','Jerry'], 'Score':[90,95]})
df.to_excel('report.xlsx', index=False)

# openpyxl 美化 Excel
wb = load_workbook('report.xlsx')
ws = wb.active
ws['A1'].font = Font(bold=True, color='FF0000')
wb.save('report.xlsx')

---