Python 如何操作 Excel 文件（openpyxl, xlrd）

Python是处理Excel文件的一种非常强大且灵活的工具，尤其是通过使用openpyxl和xlrd等库。openpyxl专注于Excel 2007及更高版本的.xlsx文件的创建、读取、修改和写入，而xlrd则主要用于读取早期版本的Excel文件（.xls），但自从2020年xlrd库不再支持.xlsx文件，因此它的主要用途现在局限于读取.xls文件。

一、安装库

在使用这些库之前，我们需要先安装它们。可以通过以下命令安装：

pip install openpyxl
pip install xlrd

openpyxl 是一个更现代的库，主要用于处理.xlsx格式的文件，而xlrd 在新版更新后仅支持.xls文件的读取操作。

二、openpyxl库的使用

1. 创建Excel文件

首先，我们可以通过openpyxl创建一个新的Excel文件，并向其中写入数据：

from openpyxl import Workbook# 创建一个新的工作簿
wb = Workbook()# 激活默认的工作表
ws = wb.active# 写入数据到工作表
ws['A1'] = 'Hello'
ws['B1'] = 'World'# 保存工作簿
wb.save("example.xlsx")

在上面的代码中，我们首先创建了一个工作簿，然后激活了默认的工作表，并向A1和B1单元格中写入了数据，最后将工作簿保存为example.xlsx。

2. 读取Excel文件

openpyxl也可以用于读取现有的Excel文件：

from openpyxl import load_workbook# 加载已有的工作簿
wb = load_workbook("example.xlsx")# 选择活动工作表
ws = wb.active# 读取单元格数据
print(ws['A1'].value)  # 输出：Hello
print(ws['B1'].value)  # 输出：World

load_workbook()函数用于加载现有的工作簿，并返回一个Workbook对象，然后可以通过.active属性访问默认的工作表。

3. 操作工作表

在openpyxl中，我们可以轻松地操作工作表，例如创建新的工作表、修改工作表名称、删除工作表等：

# 创建新工作表
ws1 = wb.create_sheet("NewSheet")# 修改工作表名称
ws1.title = "MySheet"# 删除工作表
wb.remove(ws1)# 或者使用 del 关键字
del wb["MySheet"]

4. 写入和修改数据

openpyxl允许我们使用不同的方式写入数据，除了通过单元格坐标，还可以通过遍历行和列的方式批量操作数据：

# 批量写入数据
for row in range(1, 10):for col in range(1, 5):ws.cell(row=row, column=col, value=f"Row {row}, Col {col}")# 修改单元格的值
ws['A1'] = 'Updated Value'

在上面的代码中，我们通过ws.cell()方法可以指定行和列，使用嵌套循环快速填充数据。

5. 单元格样式

除了读写数据外，openpyxl还支持修改单元格的样式，例如字体、边框、颜色等：

from openpyxl.styles import Font, Color, Alignment# 设置单元格字体
ws['A1'].font = Font(size=14, bold=True)# 设置单元格对齐方式
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')# 设置单元格填充颜色
from openpyxl.styles import PatternFill
ws['A1'].fill = PatternFill("solid", fgColor="00FF00")

openpyxl.styles模块提供了丰富的样式选项，允许我们根据需求定制Excel文件的外观。

6. 处理公式

openpyxl还支持Excel中的公式。例如：

# 添加一个简单的加法公式
ws['C1'] = '=A1+B1'# 手动计算公式（注意：需要 Excel 应用程序打开此文件后才能真正计算）
wb.save("formula_example.xlsx")

虽然openpyxl可以写入和读取公式，但实际的计算需要Excel应用程序本身来执行。

三、xlrd库的使用

xlrd专注于读取Excel文件，特别是xls格式。虽然它不再支持读取xlsx文件，但对于处理旧版的Excel文件依然很有用。

1. 读取Excel文件

使用xlrd读取Excel文件非常简单：

import xlrd# 打开一个Excel文件
book = xlrd.open_workbook('example.xls')# 获取第一个工作表
sheet = book.sheet_by_index(0)# 读取单元格数据
print(sheet.cell_value(0, 0))  # 输出第1行第1列的数据

这里我们通过xlrd.open_workbook()函数打开了一个.xls文件，然后通过sheet_by_index()方法获取工作表，并通过sheet.cell_value()读取单元格的内容。

2. 获取工作表信息

xlrd可以帮助我们获取更多关于工作表的信息，例如行数、列数等：

# 获取工作表名称
print(sheet.name)# 获取工作表的行数和列数
print(sheet.nrows)
print(sheet.ncols)

这对于遍历整个表格的内容是非常有用的。

3. 遍历单元格数据

xlrd允许我们轻松遍历整个工作表中的数据：

# 遍历行数据
for row_idx in range(sheet.nrows):row = sheet.row(row_idx)for col_idx, cell in enumerate(row):print(f"({row_idx}, {col_idx}) = {cell.value}")

上面的代码示例展示了如何遍历工作表中的所有数据，逐行输出每个单元格的内容。

四、openpyxl与xlrd的配合使用

在某些场景下，我们可能需要处理.xls和.xlsx两种文件格式。这时，可以结合使用openpyxl和xlrd。例如，我们可以使用xlrd读取.xls文件，然后使用openpyxl将其转换为.xlsx格式：

import xlrd
from openpyxl import Workbook# 使用xlrd打开xls文件
xls_book = xlrd.open_workbook('example.xls')
xls_sheet = xls_book.sheet_by_index(0)# 创建一个新的xlsx文件
wb = Workbook()
ws = wb.active# 将xls数据复制到xlsx
for row_idx in range(xls_sheet.nrows):for col_idx in range(xls_sheet.ncols):ws.cell(row=row_idx+1, column=col_idx+1, value=xls_sheet.cell_value(row_idx, col_idx))# 保存新的xlsx文件
wb.save('converted_example.xlsx')

这样，通过结合xlrd和openpyxl，我们可以轻松实现不同Excel格式之间的转换和处理。

openpyxl主要用于处理现代的.xlsx文件，而xlrd则用于读取老旧的.xls文件格式。通过结合这两个库，我们可以灵活地处理各种Excel文件格式，并根据需要进行数据转换和格式定制。

在实际应用中，根据文件格式的不同选择合适的库进行操作，可以提高代码的效率和兼容性。掌握这两个库的使用，将为数据处理和分析领域提供极大的便利。