网站备案号:蜀ICP备2026035467号-3 | 服务邮箱:admin@xservx.com | 网址:www.xservx.com

python怎么读写excel文件|从零基础到自动化办公全解析

掌握xlsx/csv读写核心技能|openpyxl、pandas、xlsxwriter三大主流方案对比与实战|含完整代码示例、常见错误、性能优化与真实办公场景应用

立即学习 避坑指南

一、为什么Python成为Excel自动化首选?

在办公自动化领域,python怎么读写excel文件已成为职场人的核心技能。相比VBA依赖Windows环境、宏安全性低,Python具备跨平台、生态丰富、代码可维护性强等优势。尤其在数据清洗、报表生成、批量处理等高频场景中,Python方案更稳定高效。

据2024年Stack Overflow开发者调查,Python连续第9年成为最受欢迎语言,其中数据处理与自动化办公是主要应用场景。国内职场中,金融、电商、制造业对Excel自动化需求激增,掌握python怎么读写excel文件可提升3-5倍处理效率。

本指南将系统讲解:
• 三种主流方案:openpyxl(xlsx)、pandas(CSV/xlsx)、xlsxwriter(xlsx只写)
• 核心操作:读取、写入、样式设置、公式计算、图表生成
• 实战案例:日报自动生成、数据合并、条件格式、定时任务
• 高级技巧:内存优化、大文件处理、多线程并发

二、openpyxl:现代xlsx文件处理首选

openpyxl是处理Excel 2007+格式(.xlsx/.xlsm)的行业标准库,支持读写单元格、样式、公式、图表、数据验证等高级功能。自2010年发布以来,持续更新至v3.1.2,完全兼容Windows/macOS/Linux。

2.1 安装与基础结构

安装命令:pip install openpyxl(推荐使用Python 3.8+)

核心对象层级:
Workbook(工作簿)→ Worksheet(工作表)→ Cell(单元格)

2.2 基础读写示例

读取Excel
写入Excel

读取示例:读取"销售数据.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("✅ 报表生成完成!")

2.3 样式与高级功能

2.4 图表生成实战

生成销售趋势折线图:

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:数据科学级Excel处理

pandas是Python数据科学核心库,其Excel操作基于openpyxl/xlrd/xlsxwriter实现,适合大规模数据清洗、分析与转换。优势在于:支持CSV/xlsx/xls无缝读取、DataFrame结构化操作、缺失值处理、条件筛选等高级功能。

3.1 安装与核心方法

安装命令:pip install pandas openpyxl xlrd xlsxwriter
(注意:xlrd 2.0+仅支持.xls格式,.xlsx需用openpyxl)

3.2 读取Excel

单Sheet读取
多Sheet读取
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())

3.3 写入Excel

支持多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("✅ 报告已生成!")

3.4 处理大文件优化技巧

四、xlrd/xlwt:传统.xls格式兼容方案

xlrd(读取)和xlwt(写入)是处理Excel 97-2003格式(.xls)的经典库,适用于旧系统数据迁移场景。注意:xlrd 2.0+已移除.xlsx支持,处理新格式需用openpyxl。

4.1 安装与基本操作

安装命令:pip install xlrd==1.2.0 xlwt(指定版本以兼容.xls)

4.2 读取.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}")

4.3 写入.xls文件

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文件生成完成!")

4.4 格式转换建议

若需将.xls转为.xlsx,推荐:
1. 用xlrd读取.xls → pandas.DataFrame → openpyxl写入.xlsx
2. 直接用Excel打开.xls另存为.xlsx(推荐小文件)

五、xlsxwriter:高性能写入专用库

xlsxwriter专注xlsx文件的高性能写入,支持复杂格式、图表、条件格式等,但不支持读取。适合报表生成、数据导出等只写场景,速度比openpyxl快30%~50%。

5.1 核心优势

5.2 基础写入示例

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("✅ 高性能报表生成完成!")

5.3 条件格式实战

# 高亮销量>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%的报错集中在以下场景:

6.1 典型错误与解决方案

ValueError: Excel file format
Permission denied
File is corrupted

错误:ValueError: Excel file format cannot be determined

原因:文件扩展名与实际格式不符(如:.xlsx文件实为.csv)
解决:
1. 用Excel打开文件另存为正确格式
2. 用pandas检测实际格式:
import pandas as pd
pd.read_csv("test.xlsx", nrows=1)
(若成功则为CSV)

错误:PermissionError: [Errno 13] Permission denied

原因:Excel文件被占用(未关闭)
解决:
1. 关闭所有Excel进程(Windows:任务管理器结束EXCEL.EXE)
2. 写入时指定新文件名:df.to_excel("new_report.xlsx")
3. 用try-except捕获错误并重试

错误:File is corrupted and cannot be opened

原因:写入时程序异常中断(未调用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: ...

6.2 内存溢出解决方案

处理10万+行数据时,推荐:
1. 用openpyxl的write_only=True模式(仅写入,不保留样式)
2. 用pandas分块处理:
for chunk in pd.read_excel("big.xlsx", chunksize=50000): process(chunk)
3. 直接输出CSV而非Excel(减少50%文件大小)

七、性能优化实战

7.1 不同方案速度对比(10万行数据)

方案 写入时间 内存占用 适用场景
openpyxl 8.2s 420MB 中等文件,需读写+样式
pandas 6.1s 380MB 数据分析+导出
xlsxwriter 3.4s 120MB 纯写入,大文件
csv (pandas) 1.2s 90MB 纯数据,无需格式

7.2 高效编码技巧

7.3 多线程并发处理

场景:同时生成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("所有日报生成完毕!")

八、自动化办公场景应用

8.1 每日销售日报自动生成

流程:
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()

8.2 批量数据清洗

场景:合并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("✅ 数据合并完成!")

九、网友还关心的周边问题

9.1 Excel与CSV格式选择指南

CSV优势
Excel优势
  • 文件体积小(节省50%空间)
  • 读写速度极快(适合大数据)
  • 兼容性极强(所有软件支持)
  • 文本编辑友好(可用记事本修改)

适用场景:数据交换、日志导出、ETL中间文件

  • 支持格式(字体/颜色/边框)
  • 支持公式/函数计算
  • 支持图表/数据透视表
  • 多Sheet组织数据

适用场景:报表展示、用户交互、复杂计算

9.2 跨平台兼容性

Mac/Linux用户注意:
• Excel文件路径用正斜杠:C:/Users/.../data.xlsx
• 文件编码统一UTF-8:df.to_excel(..., encoding="utf-8")
• 避免使用中文路径(部分系统兼容性差)

9.3 云服务集成方案

将Excel数据同步至云端:
1. 用gspread连接Google Sheets
2. 用openpyxl读取本地文件 → requests上传至API
3. 用alibabacloud_dataworks_services对接阿里云DataWorks

十、学习资源推荐

📚 官方文档

openpyxl官方指南

最权威的API参考,更新及时,含完整示例代码

https://openpyxl.readthedocs.io/

📊 视频教程

Python办公自动化实战

系统讲解Excel自动化+邮件发送+GUI界面开发

推荐B站搜索:Python实战办公自动化

💡 社区问答

Stack Overflow

搜索"python-excel",90%问题已有解决方案

关键词:[python] excel automation

十一、常见问题FAQ

  1. Q:如何处理合并单元格?
    → openpyxl需用ws.unmerge_cells("A1:C1")拆分后操作,pandas读取时自动展开
  2. Q:支持宏(.xlsm)吗?
    → openpyxl可读取但不支持写入宏,需用win32com(仅Windows)
  3. Q:如何设置下拉菜单?
    → openpyxl用DataValidation类,xlsxwriter用worksheet.data_validation()