Python读写Excel文件-1

Excel简介

Excel 是 Microsoft（微软）为使用 Windows 和 macOS 操作系统开发的一款电子表格软件。Excel 凭借其直观的界面、出色的计算功能和图表工具，再加上成功的市场营销，一直以来都是最为流行的个人计算机数据处理软件。当然，Excel 也有很多竞品，例如 Google Sheets、LibreOffice Calc、Numbers 等，这些竞品基本上也能够兼容 Excel，至少能够读写较新版本的 Excel 文件，当然这些不是我们讨论的重点。掌握用 Python 程序操作 Excel 文件，可以让日常办公自动化的工作更加轻松愉快，而且在很多商业项目中，导入导出 Excel 文件都是特别常见的功能。

Python 操作 Excel 文件最常用的三方库是openpyxl，它可以读写xlsx格式的 Excel 文件（Excel 2007 及之后版本使用的格式），当然这个库不仅仅可以操作 Excel，还可以操作其他基于 Office Open XML 的电子表格文件。本章我们先讲解openpyxl的基础用法，下一课再继续讲解它的进阶操作。可以使用下面的命令安装openpyxl。

pip install openpyxl

提示：如果你使用uv管理依赖，等价的命令是uv add openpyxl。

读Excel文件

例如在当前文件夹下有一个名为“阿里巴巴2020年股票数据.xlsx”的 Excel 文件，如果想读取并显示该文件的内容，可以通过如下所示的代码来完成。

import openpyxl

# 使用openpyxl模块的load_workbook函数打开指定的Excel文件并获得Workbook对象（工作簿）
wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')
# 通过Workbook对象的sheetnames属性可以获取所有表单的名称
sheetnames = wb.sheetnames
print(sheetnames)
# 通过表单名称获取Worksheet对象（工作表），也可以用wb.active获取当前活动的工作表
sheet = wb[sheetnames[0]]
# 通过Worksheet对象的max_row和max_column属性获取表单的行数和列数
print(sheet.max_row, sheet.max_column)
# 通过iter_rows方法按行遍历工作表，每次得到一行由Cell对象（单元格）组成的元组
for row in sheet.iter_rows():
    for cell in row:
        # 通过Cell对象的value属性获取单元格中的值
        value = cell.value
        # 对除首行外的其他行进行数据格式化处理
        if cell.row > 1:
            # 第1列是日期（openpyxl会将其处理成datetime类型），格式化为“年月日”的格式
            if cell.column == 1:
                value = f'{value.year}年{value.month:>02d}月{value.day:>02d}日'
            # 其他列的数值处理成小数点后保留两位有效数字的浮点数
            else:
                value = f'{value:.2f}'
        print(value, end='\t')
    print()
# 也可以通过“列标+行号”的坐标直接访问单元格，例如下面的代码获取A1单元格的值
print(sheet['A1'].value)
# 获取最后一个单元格的数据类型
# 's' - 字符串，'n' - 数字，'d' - 日期时间，'b' - 布尔，'f' - 公式，'e' - 错误
last_cell_type = sheet.cell(sheet.max_row, sheet.max_column).data_type
print(last_cell_type)
# 获取第一行的值（元组）
print(tuple(cell.value for cell in sheet[1]))
# 获取指定行指定列范围的数据（通过iter_rows方法的参数来限定行列范围）
for row in sheet.iter_rows(min_row=4, max_row=4, min_col=1, max_col=5):
    print([cell.value for cell in row])

提示：上面代码中使用的Excel文件“阿里巴巴2020年股票数据.xlsx”可以通过后面的百度云盘地址进行获取，原始文件是xls格式，请先用 Excel 或 WPS 将其另存为xlsx格式（openpyxl无法读取旧版xls文件）。链接:https://pan.baidu.com/s/1rQujl5RQn9R7PadB2Z5g_g 提取码:e7b4。

相信通过上面的代码，大家已经了解到了如何读取一个 Excel 文件，如果想知道更多关于openpyxl模块的知识，可以阅读它的官方文档。

写Excel文件

写入 Excel 文件可以先通过openpyxl模块的Workbook类创建工作簿对象，新创建的工作簿默认有一个名为“Sheet”的工作表，可以通过它的title属性重命名，也可以通过工作簿对象的create_sheet方法添加新的工作表；然后通过工作表对象的cell方法（行号、列号都从1开始）或“列标+行号”的坐标方式向指定单元格中写入数据；最后通过工作簿对象的save方法将工作簿保存到指定的文件中。下面的代码实现了将5 个学生 3 门课程的考试成绩写入 Excel 文件的操作。

import random

import openpyxl

student_names = ['关羽', '张飞', '赵云', '马超', '黄忠']
scores = [[random.randrange(50, 101) for _ in range(3)] for _ in range(5)]
# 创建工作簿对象（Workbook）
wb = openpyxl.Workbook()
# 获取默认的工作表并将其重命名为“一年级二班”
# 也可以通过wb.create_sheet('一年级二班')添加新的工作表
sheet = wb.active
sheet.title = '一年级二班'
# 添加表头数据（openpyxl的行号、列号都从1开始）
titles = ('姓名', '语文', '数学', '英语')
for index, title in enumerate(titles):
    sheet.cell(1, index + 1, title)
    # 也可以使用“坐标”方式：sheet.cell(row=1, column=index + 1).value = title
# 将学生姓名和考试成绩写入单元格
for row in range(len(scores)):
    sheet.cell(row + 2, 1, student_names[row])
    for col in range(len(scores[row])):
        sheet.cell(row + 2, col + 2, scores[row][col])
# 保存Excel工作簿
wb.save('考试成绩表.xlsx')

调整单元格样式

在写Excel文件时，我们还可以为单元格设置样式，样式相关的类都来自openpyxl.styles模块，主要包括字体（Font）、填充（PatternFill）、对齐方式（Alignment）和边框（Border）。需要说明的是，openpyxl中单元格的font、fill、alignment、border等样式属性是相互独立的，可以分别进行设置，例如在上面的例子中，如果希望将表头单元格的背景色修改为黄色，可以按照如下的方式进行操作。

from openpyxl.styles import PatternFill

# 黄色实心填充（颜色使用十六进制的RGB值表示）
header_fill = PatternFill(fill_type='solid', start_color='FFFF00', end_color='FFFF00')
for cell in sheet[1]:
    # sheet[1]取到的是工作表的第1行
    cell.fill = header_fill

如果希望为表头设置指定的字体，可以使用Font类并添加如下所示的代码。

from openpyxl.styles import Font

font = Font(
    # 字体名称
    name='华文楷体',
    # 字号（单位是磅）
    size=18,
    # 是否使用粗体
    bold=True,
    # 是否使用斜体
    italic=False,
    # 字体颜色
    color='000000'
)
for cell in sheet[1]:
    cell.font = font

注意：上面代码中指定的字体名（name参数）应当是本地系统有的字体，例如在我的电脑上有名为“华文楷体”的字体。

如果希望表头水平垂直居中对齐，可以使用下面的代码进行设置。

from openpyxl.styles import Alignment

align = Alignment(
    # 水平方向的对齐方式
    horizontal='center',
    # 垂直方向的对齐方式
    vertical='center'
)
for cell in sheet[1]:
    cell.alignment = align

如果希望给表头加上黄色的虚线边框，可以使用下面的代码来设置。

from openpyxl.styles import Border, Side

# 先通过Side类定义边框线（dashed表示虚线），再指定四个方向的边框
side = Side(border_style='dashed', color='FFFF00')
borders = Border(top=side, bottom=side, left=side, right=side)
for cell in sheet[1]:
    cell.border = borders

如果要调整单元格的宽度（列宽）和表头的高度（行高），可以按照下面的代码进行操作，行高的单位是磅（points），列宽的单位大致相当于标准字体下的字符个数。

# 设置第1行的行高为40磅
sheet.row_dimensions[1].height = 40
# 设置A列的列宽为20个字符的宽度
sheet.column_dimensions['A'].width = 20

公式计算

对于前面打开的“阿里巴巴2020年股票数据.xlsx”文件，如果要统计全年收盘价（Close字段）的平均值以及全年交易量（Volume字段）的总和，可以使用Excel的公式计算即可。openpyxl支持向单元格直接写入以等号开头的公式字符串，Excel打开文件时会自动计算公式的值。

实现公式计算的代码如下所示。

import openpyxl

wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')
sheet = wb[wb.sheetnames[0]]
nrows = sheet.max_row
# 向单元格写入公式字符串（以等号开头），其中E列是收盘价（Close），G列是交易量（Volume）
sheet.cell(nrows + 1, 5, f'=AVERAGE(E2:E{nrows})')
sheet.cell(nrows + 1, 7, f'=SUM(G2:G{nrows})')
wb.save('阿里巴巴2020年股票数据汇总.xlsx')

说明：openpyxl只负责将公式写入单元格，公式的计算结果要等到用 Excel 打开文件时才会算出来；如果想读回公式的计算结果，需要在load_workbook函数中传入data_only=True参数，而且前提是该文件已经被 Excel 等软件打开并保存过。另外，上面的代码有一些小瑕疵，有兴趣的读者可以自行探索并思考如何解决（例如为汇总行补上“平均收盘价”“交易量总和”等说明文字）。

总结

掌握了 Python 程序操作 Excel 的方法，可以解决日常办公中很多繁琐的处理 Excel 电子表格工作，最常见就是将多个数据格式相同的 Excel 文件合并到一个文件以及从多个 Excel 文件或表单中提取指定的数据。当然，如果要对表格数据进行处理，使用 Python 数据分析神器之一的 pandas 库可能更为方便。

附注：本章早期版本使用的xlrd、xlwt、xlutils三个库，如今只建议在需要兼容遗留xls格式的场景中使用：xlwt从 2017 年起已停止维护且只能写xls文件，xlrd从 2.0 版本（2020 年 12 月发布）起已不再支持xlsx文件。新项目请一律使用openpyxl，或者使用pandas（其read_excel/to_excel函数底层依赖openpyxl等引擎）来处理 Excel 文件。