代码收藏家技术教程 2024-07-24

【Python教程】Python中操作Excel的方法详解

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就足够了。

作者：Ustinian_310