在办公自动化领域,python怎么读写excel文件已成为职场人的核心技能。相比VBA依赖Windows环境、宏安全性低,Python具备跨平台、生态丰富、代码可维护性强等优势。尤其在数据清洗、报表生成、批量处理等高频场景中,Python方案更稳定高效。
据2024年Stack Overflow开发者调查,Python连续第9年成为最受欢迎语言,其中数据处理与自动化办公是主要应用场景。国内职场中,金融、电商、制造业对Excel自动化需求激增,掌握python怎么读写excel文件可提升3-5倍处理效率。
本指南将系统讲解:
• 三种主流方案:openpyxl(xlsx)、pandas(CSV/xlsx)、xlsxwriter(xlsx只写)
• 核心操作:读取、写入、样式设置、公式计算、图表生成
• 实战案例:日报自动生成、数据合并、条件格式、定时任务
• 高级技巧:内存优化、大文件处理、多线程并发
openpyxl是处理Excel 2007+格式(.xlsx/.xlsm)的行业标准库,支持读写单元格、样式、公式、图表、数据验证等高级功能。自2010年发布以来,持续更新至v3.1.2,完全兼容Windows/macOS/Linux。
安装命令:pip install openpyxl(推荐使用Python 3.8+)
核心对象层级:
Workbook(工作簿)→ Worksheet(工作表)→ Cell(单元格)
读取示例:读取"销售数据.xlsx"中Sheet1的A1:C10区域
from openpyxl import load_workbook
# 加载工作簿
wb = load_workbook("销售数据.xlsx", data_only=True) # data_only=True读取公式结果
ws = wb["Sheet1"] # 或 wb.active
# 读取单个单元格
cell_value = ws["A2"].value # 返回实际值(非对象)
print(f"A2单元格值:{cell_value}")
# 读取区域(二维元组)
data_range = ws["A2:C5"]
for row in data_range:
for cell in row:
print(f"({cell.row},{cell.column}) = {cell.value}")
# 按行/列遍历
for row in ws.iter_rows(min_row=2, max_row=10, max_col=3, values_only=True):
print(row) # values_only=True直接返回值
wb.close() # 养成关闭习惯
写入示例:生成带标题的销售报表
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
# 创建新工作簿
wb = Workbook()
ws = wb.active
ws.title = "销售汇总"
# 写入标题行
headers = ["日期", "产品", "销量", "单价", "金额"]
for col, header in enumerate(headers, start=1):
cell = ws.cell(row=1, column=col, value=header)
cell.font = Font(bold=True, color="FFFFFF") # 白色粗体
cell.fill = PatternFill(start_color="037EF3", fill_type="solid") # 主色调背景
cell.alignment = Alignment(horizontal="center")
# 写入数据
data = [
["2024-06-01", "A产品", 120, 25.5, "=C2*D2"],
["2024-06-02", "B产品", 85, 18.0, "=C3*D3"],
["2024-06-03", "C产品", 200, 30.0, "=C4*D4"]
]
for row_idx, row_data in enumerate(data, start=2):
for col_idx, value in enumerate(row_data, start=1):
ws.cell(row=row_idx, column=col_idx, value=value)
# 自动调整列宽
for col in ws.columns:
max_length = 0
column = col[0].column_letter
for cell in col:
if cell.value:
max_length = max(max_length, len(str(cell.value)))
ws.column_dimensions[column].width = min(max_length + 2, 30)
# 保存文件
wb.save("销售汇总_202406.xlsx")
print("✅ 报表生成完成!")
生成销售趋势折线图:
from openpyxl.chart import LineChart, Reference
# 数据范围
data = Reference(ws, min_col=3, min_row=1, max_row=4) # 销量列
categories = Reference(ws, min_col=1, min_row=2, max_row=4) # 日期列
# 创建图表
chart = LineChart()
chart.title = "近3日销量趋势"
chart.x_axis.title = "日期"
chart.y_axis.title = "销量"
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
# 插入图表
ws.add_chart(chart, "F2")
wb.save("图表示例.xlsx")
pandas是Python数据科学核心库,其Excel操作基于openpyxl/xlrd/xlsxwriter实现,适合大规模数据清洗、分析与转换。优势在于:支持CSV/xlsx/xls无缝读取、DataFrame结构化操作、缺失值处理、条件筛选等高级功能。
安装命令:pip install pandas openpyxl xlrd xlsxwriter
(注意:xlrd 2.0+仅支持.xls格式,.xlsx需用openpyxl)
import pandas as pd
# 读取指定Sheet
df = pd.read_excel("销售数据.xlsx", sheet_name="Sheet1")
# 读取多Sheet(返回字典)
dfs = pd.read_excel("销售数据.xlsx", sheet_name=["1月", "2月", "3月"])
# 读取时指定列类型与缺失值处理
df = pd.read_excel(
"销售数据.xlsx",
usecols=["日期", "产品", "销量"], # 只读指定列
dtype={"销量": int, "单价": float}, # 强制类型
na_values=["", "N/A"], # 识别缺失值
parse_dates=["日期"] # 解析日期
)
print(df.head()) # 显示前5行
# 读取所有Sheet并合并
all_sheets = pd.read_excel("销售数据.xlsx", sheet_name=None)
combined_df = pd.concat(all_sheets.values(), ignore_index=True)
# 按Sheet名筛选
jan_df = pd.read_excel("销售数据.xlsx", sheet_name="1月")
feb_df = pd.read_excel("销售数据.xlsx", sheet_name="2月")
# 按条件筛选(pandas核心优势)
filtered = combined_df[(combined_df["销量"] > 100) & (combined_df["产品"].isin(["A产品", "B产品"]))]
print(filtered.groupby("产品")["销量"].sum())
支持多Sheet写入与样式控制:
import pandas as pd
# 创建DataFrame
data = {
"日期": ["2024-06-01", "2024-06-02", "2024-06-03"],
"产品": ["A产品", "B产品", "C产品"],
"销量": [120, 85, 200],
"金额": [3060.0, 1530.0, 6000.0]
}
df = pd.DataFrame(data)
# 写入Excel
with pd.ExcelWriter("汇总报告.xlsx", engine="openpyxl") as writer:
# 写入主数据
df.to_excel(writer, sheet_name="日报", index=False)
# 写入统计表
summary = df.groupby("产品")["销量"].sum().reset_index()
summary.to_excel(writer, sheet_name="统计", index=False)
# 获取workbook对象设置样式
wb = writer.book
ws = writer.sheets["日报"]
# 设置列宽(通过openpyxl)
ws.column_dimensions["A"].width = 12
ws.column_dimensions["B"].width = 10
print("✅ 报告已生成!")
pd.read_excel(..., chunksize=10000)usecols=["A", "C"]dtype={"销量": "int16"}(节省50%内存)openpyxl的write_only模式xlrd(读取)和xlwt(写入)是处理Excel 97-2003格式(.xls)的经典库,适用于旧系统数据迁移场景。注意:xlrd 2.0+已移除.xlsx支持,处理新格式需用openpyxl。
安装命令:pip install xlrd==1.2.0 xlwt(指定版本以兼容.xls)
import xlrd
# 打开工作簿
wb = xlrd.open_workbook("旧数据.xls")
ws = wb.sheet_by_name("Sheet1")
# 读取单元格
value = ws.cell_value(1, 0) # 行索引, 列索引(从0开始)
print(f"A2值:{value}")
# 读取整行/整列
row_values = ws.row_values(1) # 第2行
col_values = ws.col_values(0) # 第1列
# 获取行数/列数
print(f"总行数:{ws.nrows}, 总列数:{ws.ncols}")
import xlwt
# 创建工作簿
wb = xlwt.Workbook()
ws = wb.add_sheet("汇总")
# 写入标题(带样式)
style = xlwt.easyxf("font: bold on; pattern: pattern solid, fore_colour gray25;")
ws.write(0, 0, "日期", style)
ws.write(0, 1, "销量", style)
# 写入数据
data = [
["2024-06-01", 120],
["2024-06-02", 85],
["2024-06-03", 200]
]
for row_idx, (date, sales) in enumerate(data, start=1):
ws.write(row_idx, 0, date)
ws.write(row_idx, 1, sales)
# 保存
wb.save("旧格式汇总.xls")
print("✅ .xls文件生成完成!")
若需将.xls转为.xlsx,推荐:
1. 用xlrd读取.xls → pandas.DataFrame → openpyxl写入.xlsx
2. 直接用Excel打开.xls另存为.xlsx(推荐小文件)
xlsxwriter专注xlsx文件的高性能写入,支持复杂格式、图表、条件格式等,但不支持读取。适合报表生成、数据导出等只写场景,速度比openpyxl快30%~50%。
import xlsxwriter
# 创建工作簿
workbook = xlsxwriter.Workbook("报表.xlsx")
worksheet = workbook.add_worksheet("汇总")
# 定义格式
title_format = workbook.add_format({
"bold": True,
"font_color": "white",
"bg_color": "#037EF3",
"align": "center",
"valign": "vcenter"
})
# 写入标题
headers = ["日期", "产品", "销量", "单价", "金额"]
for col, header in enumerate(headers):
worksheet.write(0, col, header, title_format)
# 写入数据
data = [
["2024-06-01", "A产品", 120, 25.5],
["2024-06-02", "B产品", 85, 18.0],
["2024-06-03", "C产品", 200, 30.0]
]
for row_idx, (date, product, sales, price) in enumerate(data, start=1):
worksheet.write(row_idx, 0, date)
worksheet.write(row_idx, 1, product)
worksheet.write(row_idx, 2, sales)
worksheet.write(row_idx, 3, price)
worksheet.write_formula(row_idx, 4, f"=C{row_idx+1}*D{row_idx+1}")
# 设置列宽
worksheet.set_column("A:A", 12)
worksheet.set_column("B:D", 10)
# 添加图表
chart = workbook.add_chart({"type": "column"})
chart.add_series({
"categories": "=汇总!$A$2:$A$4",
"values": "=汇总!$C$2:$C$4",
"name": "销量"
})
worksheet.insert_chart("F2", chart)
workbook.close()
print("✅ 高性能报表生成完成!")
# 高亮销量>150的单元格
worksheet.conditional_format("C2:C100", {
"type": "cell",
"criteria": ">",
"value": 150,
"format": workbook.add_format({"bg_color": "#C6EFCE", "font_color": "#006100"})
})
# 红黄绿三色刻度
worksheet.conditional_format("E2:E100", {
"type": "3_color_scale",
"min_color": "#FF0000",
"mid_color": "#FFFF00",
"max_color": "#00FF00"
})
根据GitHub issues统计,90%的报错集中在以下场景:
原因:文件扩展名与实际格式不符(如:.xlsx文件实为.csv)
解决:
1. 用Excel打开文件另存为正确格式
2. 用pandas检测实际格式:
import pandas as pd(若成功则为CSV)
pd.read_csv("test.xlsx", nrows=1)
原因:Excel文件被占用(未关闭)
解决:
1. 关闭所有Excel进程(Windows:任务管理器结束EXCEL.EXE)
2. 写入时指定新文件名:df.to_excel("new_report.xlsx")
3. 用try-except捕获错误并重试
原因:写入时程序异常中断(未调用close())
解决:
1. 用openpyxl修复:
from openpyxl import load_workbook
wb = load_workbook("file.xlsx", keep_vba=True)
2. 改用with上下文管理器自动关闭:
with pd.ExcelWriter("file.xlsx") as writer: ...
处理10万+行数据时,推荐:
1. 用openpyxl的write_only=True模式(仅写入,不保留样式)
2. 用pandas分块处理:
for chunk in pd.read_excel("big.xlsx", chunksize=50000): process(chunk)
3. 直接输出CSV而非Excel(减少50%文件大小)
| 方案 | 写入时间 | 内存占用 | 适用场景 |
|---|---|---|---|
| openpyxl | 8.2s | 420MB | 中等文件,需读写+样式 |
| pandas | 6.1s | 380MB | 数据分析+导出 |
| xlsxwriter | 3.4s | 120MB | 纯写入,大文件 |
| csv (pandas) | 1.2s | 90MB | 纯数据,无需格式 |
ws.append([row1, row2])或df.to_excel(...)ws.parent.calculate_cell = Falseopenpyxl的write_only模式场景:同时生成5份日报(互不依赖)
import threading
import pandas as pd
from pathlib import Path
def generate_report(day):
# 独立生成逻辑
df = pd.DataFrame(...)
df.to_excel(f"日报_{day}.xlsx", index=False)
print(f"✅ 日报_{day}.xlsx 生成完成")
# 多线程启动
threads = []
for day in range(1, 6):
t = threading.Thread(target=generate_report, args=(f"2024060{day}",))
threads.append(t)
t.start()
for t in threads:
t.join()
print("所有日报生成完毕!")
流程:
1. 每日9:00自动读取前一天销售数据
2. 生成汇总报表(含图表)
3. 邮件发送至管理层邮箱
import pandas as pd
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from datetime import datetime, timedelta
def auto_report():
# 读取数据
yesterday = (datetime.now() - timedelta(1)).strftime("%Y%m%d")
df = pd.read_excel(f"sales_{yesterday}.xlsx")
# 生成报表
with pd.ExcelWriter(f"日报_{yesterday}.xlsx") as writer:
df.to_excel(writer, sheet_name="明细", index=False)
# 汇总表
summary = df.groupby("产品")["销量"].sum().reset_index()
summary.to_excel(writer, sheet_name="汇总", index=False)
# 邮件发送
msg = MIMEMultipart()
msg["Subject"] = f"销售日报_{yesterday}"
msg.attach(MIMEText("详见附件", "plain"))
with open(f"日报_{yesterday}.xlsx", "rb") as f:
part = MIMEApplication(f.read(), _subtype="xlsx")
part.add_header("Content-Disposition", "attachment", filename=f"日报_{yesterday}.xlsx")
msg.attach(part)
# 发送逻辑(需配置SMTP)
# server = smtplib.SMTP("smtp.example.com", 587)
# server.login("user", "pass")
# server.sendmail("from", "to", msg.as_string())
print(f"✅ 自动化日报已生成并待发送!")
# 定时任务(用cron或Windows任务计划)
auto_report()
场景:合并100个销售报表(同结构)
import pandas as pd
from pathlib import Path
# 读取所有文件
files = Path("sales_reports").glob("*.xlsx")
dfs = [pd.read_excel(f) for f in files]
# 合并并清洗
combined = pd.concat(dfs, ignore_index=True)
combined.drop_duplicates(inplace=True) # 去重
combined["销量"].fillna(0, inplace=True) # 填充缺失值
# 保存
combined.to_excel("全年销售汇总.xlsx", index=False)
print("✅ 数据合并完成!")
适用场景:数据交换、日志导出、ETL中间文件
适用场景:报表展示、用户交互、复杂计算
Mac/Linux用户注意:
• Excel文件路径用正斜杠:C:/Users/.../data.xlsx
• 文件编码统一UTF-8:df.to_excel(..., encoding="utf-8")
• 避免使用中文路径(部分系统兼容性差)
将Excel数据同步至云端:
1. 用gspread连接Google Sheets
2. 用openpyxl读取本地文件 → requests上传至API
3. 用alibabacloud_dataworks_services对接阿里云DataWorks
系统讲解Excel自动化+邮件发送+GUI界面开发
推荐B站搜索:Python实战办公自动化
搜索"python-excel",90%问题已有解决方案
关键词:[python] excel automation
ws.unmerge_cells("A1:C1")拆分后操作,pandas读取时自动展开