Python 操作 Excel 文件
1. 常用的几种库
- xlrd和xlwt库
xlrd
:用于读取Excel文件。xlwt
:用于写入Excel文件。- 这两个库通常一起使用,
xlrd
用于读取,xlwt
用于写入,但它们不支持Excel 2007及以后的.xlsx
格式。
- openpyxl库
- 适用于
.xlsx
格式的Excel文件读写。 - 支持读写Excel公式,样式等高级特性。
- 适用于
- xlsxwriter库
- 主要用于写入
.xlsx
格式的Excel文件。 - 提供丰富的特性,如写入图片、图表等。
- 主要用于写入
- pandas库
pandas
是一个强大的数据分析库,它提供了非常便捷的数据读取和写入功能,支持多种格式,包括Excel。- 使用
pandas
的read_excel
和to_excel
函数可以方便地对Excel文件进行操作。
2. 使用xlrd
和xlwt
读取和写入Excel
2.1 基础应用示例
import xlrd
from xlwt import Workbook
# 读取Excel
rb = xlrd.open_workbook('example.xls')
sheet = rb.sheet_by_index(0)
for row_index in range(sheet.nrows):
print(sheet.row_values(row_index))
# 写入Excel
wb = Workbook()
ws = wb.add_sheet('Sheet1')
ws.write(0, 0, 'Hello')
wb.save('example.xls')
2.2 xlrd库的额外功能:
- 可以读取单元格的数据类型,如日期、数字、字符串等。
- 支持读取单元格的格式信息,如字体、颜色、边框等。
- 可以读取Excel文件的批注。
- 允许设置单元格的字体、边框、背景色等样式。
- 支持在不同的单元格中写入不同的数据类型。
- 可以冻结窗格,设置列宽和行高。
2.2.1 示例: 设置单元格样式
from xlwt import XFStyle, Pattern
# 创建样式
style = XFStyle()
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 5 # 设置背景色
style.pattern = pattern
# 应用样式
ws.write(0, 0, 'Styled Cell', style)
3. 使用openpyxl
读取和写入Excel
3.1 基础应用示例
from openpyxl import Workbook, load_workbook
# 写入Excel
wb = Workbook()
ws = wb.active
ws['A1'] = 'Hello'
wb.save('example.xlsx')
# 读取Excel
wb = load_workbook('example.xlsx')
ws = wb.active
for row in ws.iter_rows(values_only=True):
print(row)
3.2 openpyxl的高级功能:
- 支持大型Excel文件的读取和写入。
- 可以使用数据验证功能,限制用户输入的值。
- 支持条件格式化,可以根据单元格的值自动改变其样式。
3.2.1 示例: 添加数据验证
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
# 创建工作簿和工作表
wb = Workbook()
ws = wb.active
# 添加数据验证
dv = DataValidation(type="list", formula1='"Dog,Cat,Bird"', allow_blank=True)
ws.add_data_validation(dv)
dv.add(ws['A1'])
# 写入数据
ws['A1'] = 'Cat'
wb.save('example.xlsx')
4. 使用xlsxwriter
写入Excel
4.1 基础应用示例
import xlsxwriter
# 创建一个Excel文件并添加一个工作表
workbook = xlsxwriter.Workbook('example.xlsx')
worksheet = workbook.add_worksheet()
# 写入数据
worksheet.write('A1', 'Hello')
# 关闭文件
workbook.close()
4.2 xlsxwriter库的图表和图片功能
4.2.1 xlsxwriter的图表功能:
- 可以创建多种类型的图表,如柱状图、折线图、饼图等。
- 支持图表的自定义,如设置标题、轴标签、图例等。
4.2.2 示例: 创建柱状图
import xlsxwriter
# 创建工作簿和工作表
workbook = xlsxwriter.Workbook('example.xlsx')
worksheet = workbook.add_worksheet()
# 添加数据
data = [
[1, 2, 3, 4, 5],
[2, 4, 6, 8, 10],
[3, 6, 9, 12, 15]
]
# 创建柱状图
chart = workbook.add_chart({'type': 'column'})
# 添加数据系列
chart.add_series({
'values': '=Sheet1!$A$1:$A$5',
'name': 'First Series',
})
chart.add_series({
'values': '=Sheet1!$B$1:$B$5',
'name': 'Second Series',
})
# 将图表插入到工作表中
worksheet.insert_chart('E2', chart)
# 写入数据
for row_num, row_data in enumerate(data):
worksheet.write_row(row_num, 0, row_data)
workbook.close()
5. 使用pandas
读取和写入Excel
5.1 基础应用示例
import pandas as pd
# 读取Excel
df = pd.read_excel('example.xlsx')
print(df)
# 写入Excel
df.to_excel('example.xlsx', index=False)
在使用这些库之前,需要通过pip安装它们:
pip install xlrd xlwt openpyxl xlsxwriter pandas
5.2 pandas库的数据分析能力
5.2.1 pandas的数据分析功能:
- 提供了丰富的数据处理方法,如合并、筛选、分组、聚合等。
- 可以轻松处理时间序列数据。
- 支持多种文件格式的读写,包括CSV、Excel、SQL数据库等。
5.2.2 示例: 使用pandas进行数据筛选和聚合
import pandas as pd
# 读取Excel文件
df = pd.read_excel('example.xlsx')
# 筛选数据
filtered_df = df[df['Column1'] > 10]
# 聚合数据
aggregated_df = df.groupby('Column2').sum()
# 输出结果
print(filtered_df)
print(aggregated_df)
6. 综合示例
以下是一个示例 python 程序,先检查是否存在 Example.xlsx 文件,若不存在则创建并命名为 Example.xlsx,然后读取该文件,若文件为空则在该文件第一行第一列第二列单元格中分别写入Hello、world;若不为空,则在原有文件内容下一行追加,设置行高为24,单元格颜色为浅绿色,字体为Arial。
import os
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill
# 文件名
file_name = "Example.xlsx"
# 检查文件是否存在
if not os.path.exists(file_name):
# 创建一个新的Excel文件
wb = Workbook()
ws = wb.active
# 写入"Hello"和"world"
ws.append(["Hello", "world"])
# 设置行高
ws.row_dimensions[1].height = 24
# 设置单元格颜色和字体
for cell in ws[1]:
cell.fill = PatternFill(start_color='90EE90', end_color='90EE90', fill_type='solid')
cell.font = Font(name='Arial', size=12)
else:
# 读取现有的Excel文件
wb = load_workbook(file_name)
ws = wb.active
# 在下一行追加"Hello"和"world"
ws.append(["Hello", "world"])
# 设置行高
ws.row_dimensions[ws.max_row].height = 24
# 设置单元格颜色和字体
for cell in ws[ws.max_row]:
cell.fill = PatternFill(start_color='90EE90', end_color='90EE90', fill_type='solid')
cell.font = Font(name='Arial', size=12)
# 保存文件
wb.save(file_name)
在选择库时,应考虑以下因素:
- 文件格式:
.xls
还是.xlsx
。 - 需求复杂度:简单的读写还是需要数据分析、格式化等高级功能。
- 性能要求:处理大型文件时,库的性能也是一个重要的考虑因素。
每种库都有其独特的优势和适用场景,根据实际需求灵活选择。如果你需要进行复杂的数据分析,pandas
可能是最佳选择。如果你需要创建格式化程度较高的Excel报告,xlsxwriter
可能是更好的选择。如果你只需要简单的读写操作,openpyxl
就足够了。