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

Python 操作 Excel 文件

1. 常用的几种库

1. xlrd和xlwt库
2. xlrd：用于读取Excel文件。
3. xlwt：用于写入Excel文件。
4. 这两个库通常一起使用，xlrd用于读取，xlwt用于写入，但它们不支持Excel 2007及以后的.xlsx格式。
5. openpyxl库
6. 适用于.xlsx格式的Excel文件读写。
7. 支持读写Excel公式，样式等高级特性。
8. xlsxwriter库
9. 主要用于写入.xlsx格式的Excel文件。
10. 提供丰富的特性，如写入图片、图表等。
11. pandas库
12. pandas是一个强大的数据分析库，它提供了非常便捷的数据读取和写入功能，支持多种格式，包括Excel。
13. 使用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库的额外功能：

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的高级功能：

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的数据分析功能：

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)

在选择库时，应考虑以下因素：

每种库都有其独特的优势和适用场景，根据实际需求灵活选择。如果你需要进行复杂的数据分析，pandas可能是最佳选择。如果你需要创建格式化程度较高的Excel报告，xlsxwriter可能是更好的选择。如果你只需要简单的读写操作，openpyxl就足够了。

作者：Ustinian_310