代码收藏家 【史上最全】11种Python 操作 Excel 文件的详尽指南
Python 提供了多种库和方法来操作 Excel 文件，每种方法都有其独特的优势和适用场景。本文将详细介绍这 11 种方法，包括它们的优缺点、适用场景以及详细的使用方式和代码示例，帮助读者全面掌握这些工具。本文不仅涵盖了基本的读写操作，还将深入探讨高级功能和最佳实践。
1. 使用 pandas 库
优点：
.xls 和 .xlsx。缺点：
适用场景：
详细使用方式：
-
安装
pandas：pip install pandas -
读取 Excel 文件：
import pandas as pd # 读取 Excel 文件 df = pd.read_excel('example.xlsx') print(df.head()) # 显示前五行数据 # 读取特定工作表 df = pd.read_excel('example.xlsx', sheet_name='Sheet1') print(df.head()) -
写入 Excel 文件：
import pandas as pd # 创建数据 data = { 'Name': ['Tom', 'Jerry'], 'Age': [20, 21] } df = pd.DataFrame(data) # 写入 Excel 文件 df.to_excel('output.xlsx', index=False) -
数据操作：
-
选择特定列：
df = pd.read_excel('example.xlsx', usecols=['Name', 'Age']) print(df.head()) -
过滤数据：
df = pd.read_excel('example.xlsx') filtered_df = df[df['Age'] > 20] print(filtered_df) -
数据排序：
df = pd.read_excel('example.xlsx') sorted_df = df.sort_values(by='Age', ascending=False) print(sorted_df) -
数据分组：
df = pd.read_excel('example.xlsx') grouped_df = df.groupby('Age').mean() print(grouped_df) -
添加新列：
df = pd.read_excel('example.xlsx') df['NewColumn'] = df['Age'] * 2 print(df) -
更新单元格：
df = pd.read_excel('example.xlsx') df.at[0, 'Age'] = 25 print(df) -
删除列：
df = pd.read_excel('example.xlsx') del df['Age'] print(df) -
合并多个 Excel 文件：
df1 = pd.read_excel('file1.xlsx') df2 = pd.read_excel('file2.xlsx') merged_df = pd.concat([df1, df2], ignore_index=True) print(merged_df) -
数据透视表：
df = pd.read_excel('example.xlsx') pivot_table = pd.pivot_table(df, values='Age', index=['Name'], aggfunc='sum') print(pivot_table)
2. 使用 openpyxl 库
优点：
.xlsx 文件格式。缺点：
.xls 文件格式。适用场景：
.xlsx 文件格式的任务。详细使用方式：
-
安装
openpyxl：pip install openpyxl -
读取 Excel 文件：
from openpyxl import load_workbook # 加载 Excel 文件 wb = load_workbook('example.xlsx') sheet = wb.active # 读取数据 for row in sheet.iter_rows(values_only=True): print(row) -
写入 Excel 文件：
from openpyxl import Workbook # 创建新的工作簿 wb = Workbook() sheet = wb.active # 写入数据 sheet.append(['Name', 'Age']) sheet.append(['Tom', 20]) sheet.append(['Jerry', 21]) # 保存文件 wb.save('output.xlsx') -
修改 Excel 文件：
from openpyxl import load_workbook # 加载 Excel 文件 wb = load_workbook('example.xlsx') sheet = wb.active # 修改单元格数据 sheet['A1'] = 'New Name' sheet['B1'] = 25 # 保存修改后的 Excel 文件 wb.save('modified_example.xlsx') -
设置单元格格式：
from openpyxl import Workbook from openpyxl.styles import Font, Alignment # 创建新的工作簿 wb = Workbook() sheet = wb.active # 设置单元格格式 cell = sheet['A1'] cell.value = 'Hello, World!' cell.font = Font(bold=True, color='FF0000') cell.alignment = Alignment(horizontal='center', vertical='center') # 保存文件 wb.save('formatted_output.xlsx')
3. 使用 xlrd 和 xlwt 库
优点：
.xls 文件格式。xlrd 用于读取 Excel 文件，xlwt 用于写入 Excel 文件。缺点：
.xlsx 文件格式。适用场景：
.xls 文件格式的任务。详细使用方式：
-
安装
xlrd和xlwt：pip install xlrd xlwt -
读取 Excel 文件：
import xlrd # 打开 Excel 文件 workbook = xlrd.open_workbook('example.xls') sheet = workbook.sheet_by_index(0) # 读取数据 for row_idx in range(sheet.nrows): row = sheet.row_values(row_idx) print(row) -
写入 Excel 文件：
import xlwt # 创建新的工作簿 workbook = xlwt.Workbook() sheet = workbook.add_sheet('Sheet1') # 写入数据 sheet.write(0, 0, 'Name') sheet.write(0, 1, 'Age') sheet.write(1, 0, 'Tom') sheet.write(1, 1, 20) sheet.write(2, 0, 'Jerry') sheet.write(2, 1, 21) # 保存文件 workbook.save('output.xls')
4. 使用 xlwings 库
优点：
.xls 和 .xlsx 文件格式。缺点：
适用场景：
详细使用方式：
-
安装
xlwings：pip install xlwings -
读取 Excel 文件：
import xlwings as xw # 创建 Excel 应用程序对象 app = xw.App(visible=True, add_book=False) # 打开工作簿 wb = app.books.open('example.xlsx') sheet = wb.sheets[0] # 读取数据 data = sheet.range('A1:B7').value print(data) # 关闭工作簿和应用程序 wb.close() app.quit() -
写入 Excel 文件：
import xlwings as xw # 创建 Excel 应用程序对象 app = xw.App(visible=True, add_book=False) # 创建新的工作簿 wb = app.books.add() sheet = wb.sheets[0] # 写入数据 sheet.range('A1').value = [['Name', 'Age'], ['Tom', 20], ['Jerry', 21]] # 保存文件 wb.save('output.xlsx') # 关闭工作簿和应用程序 wb.close() app.quit() -
设置单元格格式：
import xlwings as xw # 创建 Excel 应用程序对象 app = xw.App(visible=True, add_book=False) # 创建新的工作簿 wb = app.books.add() sheet = wb.sheets[0] # 写入数据 sheet.range('A1').value = [['Name', 'Age'], ['Tom', 20], ['Jerry', 21]] # 设置单元格格式 cell = sheet.range('A1') cell.api.Font.Bold = True cell.api.HorizontalAlignment = -4108 # 水平居中 cell.api.VerticalAlignment = -4108 # 垂直居中 # 保存文件 wb.save('formatted_output.xlsx') # 关闭工作簿和应用程序 wb.close() app.quit()
5. 使用 XlsxWriter 库
优点：
.xlsx 文件。缺点：
适用场景：
.xlsx 文件的任务。详细使用方式：
-
安装
XlsxWriter：pip install XlsxWriter -
写入 Excel 文件：
import xlsxwriter # 创建新的 Excel 文件 workbook = xlsxwriter.Workbook('output.xlsx') worksheet = workbook.add_worksheet() # 写入数据 data = [['Name', 'Age'], ['Tom', 20], ['Jerry', 21]] for row_num, row_data in enumerate(data): worksheet.write_row(row_num, 0, row_data) # 设置单元格格式 bold = workbook.add_format({'bold': True}) worksheet.write('A1', 'Name', bold) worksheet.write('B1', 'Age', bold) # 插入图表 chart = workbook.add_chart({'type': 'column'}) chart.add_series({ 'categories': '=Sheet1!$A$2:$A$3', 'values': '=Sheet1!$B$2:$B$3', }) worksheet.insert_chart('D2', chart) # 保存文件 workbook.close()
6. 使用 pyexcel 库
优点：
.xls 和 .xlsx。缺点：
pandas 和 openpyxl 功能丰富。适用场景：
详细使用方式：
-
安装
pyexcel：pip install pyexcel pyexcel-xls pyexcel-xlsx -
读取 Excel 文件：
import pyexcel # 读取 Excel 文件 sheet = pyexcel.get_sheet(file_name="example.xlsx") print(sheet) # 读取特定工作表 sheet = pyexcel.get_sheet(file_name="example.xlsx", sheet_name="Sheet1") print(sheet) -
写入 Excel 文件：
import pyexcel # 创建数据 data = [['Name', 'Age'], ['Tom', 20], ['Jerry', 21]] # 写入 Excel 文件 sheet = pyexcel.Sheet(data) sheet.save_as("output.xlsx")
7. 使用 et_xmlfile 库
优点：
缺点：
适用场景：
详细使用方式：
-
安装
et_xmlfile：pip install et_xmlfile -
读取 Excel 文件的 XML 内容：
from et_xmlfile import xmlfile # 读取 Excel 文件的 XML 内容 with xmlfile.XmlFile('example.xlsx') as xf: for event, elem in xf.iterparse(): print(event, elem.tag)
8. 使用 win32com.client 库
优点：
缺点：
适用场景：
详细使用方式：
-
安装
pywin32：pip install pywin32 -
读取 Excel 文件：
import win32com.client # 创建 Excel 应用程序对象 excel = win32com.client.Dispatch("Excel.Application") excel.Visible = True # 打开工作簿 workbook = excel.Workbooks.Open(r'C:\path\to\example.xlsx') sheet = workbook.Sheets(1) # 读取数据 cell_value = sheet.Cells(1, 1).Value print(cell_value) # 关闭工作簿和应用程序 workbook.Close() excel.Quit() -
写入 Excel 文件：
import win32com.client # 创建 Excel 应用程序对象 excel = win32com.client.Dispatch("Excel.Application") excel.Visible = True # 创建新的工作簿 workbook = excel.Workbooks.Add() sheet = workbook.Sheets(1) # 写入数据 sheet.Cells(1, 1).Value = 'Name' sheet.Cells(1, 2).Value = 'Age' sheet.Cells(2, 1).Value = 'Tom' sheet.Cells(2, 2).Value = 20 sheet.Cells(3, 1).Value = 'Jerry' sheet.Cells(3, 2).Value = 21 # 保存文件 workbook.SaveAs(r'C:\path\to\output.xlsx') # 关闭工作簿和应用程序 workbook.Close() excel.Quit()
9. 使用 tablib 库
优点：
缺点：
pandas 和 openpyxl 功能丰富。适用场景：
详细使用方式：
-
安装
tablib：pip install tablib -
读取 Excel 文件：
import tablib # 导入 Excel 文件 with open('example.xlsx', 'rb') as f: data = tablib.Dataset().load(f.read(), format='xlsx') print(data) -
写入 Excel 文件：
import tablib # 创建数据集 data = tablib.Dataset() data.headers = ['Name', 'Age'] data.append(['Tom', 20]) data.append(['Jerry', 21]) # 导出为 Excel 文件 with open('output.xlsx', 'wb') as f: f.write(data.export('xlsx'))
10. 使用 odfpy 库
优点：
.ods 文件。.ods 文件的功能。缺点：
.xls 和 .xlsx 文件格式。适用场景：
.ods 文件格式的任务。详细使用方式：
-
安装
odfpy：pip install odfpy -
读取 ODS 文件：
from odf.opendocument import load from odf.table import TableRow, TableCell from odf.text import P # 读取 ODS 文件 doc = load('example.ods') table = doc.spreadsheet.getElementsByType(Table)[0] # 遍历表格中的数据 for row in table.getElementsByType(TableRow): cells = row.getElementsByType(TableCell) row_data = [cell.getElementsByType(P)[0].text for cell in cells] print(row_data) -
写入 ODS 文件：
from odf.opendocument import OpenDocumentSpreadsheet from odf.table import Table, TableRow, TableCell from odf.text import P # 创建新的 ODS 文件 doc = OpenDocumentSpreadsheet() table = Table(name="Sheet1") doc.spreadsheet.addElement(table) # 添加新行 new_row = TableRow() new_row.addElement(TableCell(text=P(text='Name'))) new_row.addElement(TableCell(text=P(text='Age'))) table.addElement(new_row) # 添加更多行 new_row = TableRow() new_row.addElement(TableCell(text=P(text='Tom'))) new_row.addElement(TableCell(text=P(text='20'))) table.addElement(new_row) new_row = TableRow() new_row.addElement(TableCell(text=P(text='Jerry'))) new_row.addElement(TableCell(text=P(text='21'))) table.addElement(new_row) # 保存文件 doc.save('output.ods')
11. 使用 pyexcel-ods3 库
优点：
.ods 文件格式。.ods 文件。缺点：
.xls 和 .xlsx 文件格式。适用场景：
.ods 文件格式的任务。详细使用方式：
-
安装
pyexcel-ods3：pip install pyexcel-ods3 -
读取 ODS 文件：
import pyexcel_ods3 # 读取 ODS 文件 data = pyexcel_ods3.get_data('example.ods') print(data) -
写入 ODS 文件：
import pyexcel_ods3 # 创建数据 data = { 'Sheet1': [ ['Name', 'Age'], ['Tom', 20], ['Jerry', 21] ] } # 写入 ODS 文件 pyexcel_ods3.save_data('output.ods', data)
总结
Python 提供了多种库和方法来操作 Excel 文件，每种方法都有其独特的优缺点和适用场景。选择合适的库可以提高开发效率和代码质量。以下是每种方法的简要总结：
pandas：功能强大，支持数据清洗、转换和分析，适用于数据分析和处理任务。openpyxl：专注于.xlsx文件格式，提供细粒度的操作，适用于需要对 Excel 文件进行细粒度操作的场景。xlrd和xlwt：支持.xls文件格式，轻量级，适用于处理.xls文件格式的任务。xlwings：支持.xls和.xlsx文件格式，提供与 Excel 应用程序交互的功能，适用于需要与 Excel 应用程序交互的场景。XlsxWriter：专注于写入.xlsx文件，提供丰富的单元格格式设置功能，适用于需要写入.xlsx文件的任务。pyexcel：支持多种 Excel 文件格式，提供一致的接口，适用于处理多种 Excel 文件格式的任务。et_xmlfile：用于处理 Excel 文件的 XML 内容，适用于需要直接操作 Excel 文件内部结构的场景。win32com.client：通过 COM 接口操作 Excel 文件，适用于需要与 Excel 应用程序交互的场景。tablib：支持多种数据格式，提供一致的接口，适用于处理多种数据格式的任务。odfpy：用于处理 OpenDocument 格式文件，包括.ods文件，适用于处理.ods文件格式的任务。pyexcel-ods3：支持.ods文件格式，提供一致的接口，适用于处理.ods文件格式的任务。
希望本文能帮助你全面掌握 Python 操作 Excel 文件的各种方法。
作者：极客代码
