Python实现Excel自动化

个人网站

文章首发于公众号：

Excel是办公自动化的关键工具之一，用于数据存储、处理和分析。

Python通过 openpyxl 库，提供了强大的Excel操作能力，让我们可以读取、写入、修改和创建复杂的Excel文件。

安装 openpyxl

在开始之前，确保你已经安装了 openpyxl 库。如果还没有安装，可以使用以下命令：

pip install openpyxl

读取Excel文件

假设我们有一个名为 example.xlsx 的Excel文件，我们可以使用openpyxl来读取它。

from openpyxl import load_workbook

# 加载工作簿
workbook = load_workbook('example.xlsx')

# 选择活动的工作表
sheet = workbook.active

# 读取特定单元格的数据
cell_value = sheet['A1'].value
print(f'The value in A1 is: {cell_value}')

写入Excel文件

如果想要修改Excel文件，你可以更改单元格的值并保存工作簿。

# 修改单元格内容
sheet['A1'] = 'New Value'

# 添加一行数据
sheet.append([1, 'data', 3.14])

# 保存工作簿
workbook.save('example_modified.xlsx')

创建图表

openpyxl 也允许你在Excel中创建图表来可视化数据。

from openpyxl.chart import BarChart, Reference

# 选择图表的数据范围
chart_data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=sheet.max_row)

# 创建条形图
chart = BarChart()

# 添加数据和类别（例如，日期或名称）
chart.add_data(chart_data, titles_from_data=True)

# 将图表放入工作表中的特定位置
sheet.add_chart(chart, "E2")

# 保存工作簿
workbook.save('example_chart.xlsx')

数据校验

openpyxl 支持数据校验，让用户在输入数据时进行限制。

from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl import load_workbook
# 加载工作簿
workbook = load_workbook('example.xlsx')

# 选择活动的工作表
sheet = workbook.active
# 创建一个数据验证对象
dv = DataValidation(type="list", formula1='"Item1,Item2,Item3"', showDropDown=True)

# 添加数据验证到单元格
sheet.add_data_validation(dv)
dv.add(sheet["A2"])

# 保存工作簿
workbook.save('example_validation.xlsx')

为了处理更复杂的Excel自动化任务，如合并多个sheet页，合并多个文件，以及拆分sheet页，你可以使用 openpyxl 进行编程。以下是每个任务对应的代码示例。

合并同一Excel文件中的多个sheet页

# 导入openpyxl库中的load_workbook模块，用于加载已存在的Excel文件
from openpyxl import load_workbook
#os模块获取文件的路径、文件名等相关操作
import os

# 加载含有多个Sheet页的Excel文件
workbook = load_workbook('需合并的文件.xlsx')

# 创建一个新的Sheet，用于存放合并后的数据
merged_sheet_title = "合并后"
workbook.create_sheet(merged_sheet_title)

# 获取新创建的Sheet对象
merged_sheet = workbook[merged_sheet_title]

# 初始化行计数器
row_counter = 1

# 获取当前工作目录
current_dir = os.getcwd()

# 遍历所有的Sheet
for sheet in workbook.sheetnames:
    # 跳过我们新建的合并Sheet
    if sheet == merged_sheet_title:
        continue

    # 获取当前Sheet对象
    current_sheet = workbook[sheet]

    # 如果是第一个Sheet，包括标题行，否则跳过标题行
    if row_counter == 1:
        for row in current_sheet.iter_rows(values_only=True):
            merged_sheet.append(row)
            row_counter += 1
    else:
        for row in current_sheet.iter_rows(min_row=2, values_only=True):
            merged_sheet.append(row)


# 检查文件是否存在
file_path = '合并后的文件.xlsx'
abs_file_path = os.path.abspath(file_path)
if os.path.exists(file_path):
    response = input(f"{file_path} 已存在。是否覆盖？(y/n): ")
    if response.lower() != 'y':
        print("操作已取消。")
    else:
        # 保存工作簿，如果文件已存在则会被覆盖
        workbook.save(file_path)
        full_path = os.path.abspath(file_path)
        print(f"文件已覆盖到 {abs_file_path}")
else:
    # 文件不存在，直接保存
    workbook.save(file_path)
    full_path = os.path.abspath(file_path)
    print(f"文件已保存到 {abs_file_path}")

合并多个Excel文件中的对应sheet

假设你有多个Excel文件，每个文件都有一个名为Data的sheet，以下是如何合并它们的代码示例。

from openpyxl import load_workbook, Workbook

# 创建一个新的Workbook对象来存放合并后的数据
merged_workbook = Workbook()
merged_sheet = merged_workbook.active
merged_sheet.title = 'Merged Data'

# 假设您有一系列文件名，这些Excel文件都包含一个名为'Data'的sheet
file_names = ['file1.xlsx', 'file2.xlsx', 'file3.xlsx']

# 遍历所有文件
for file_index, file_name in enumerate(file_names):
    workbook = load_workbook(file_name)
    sheet = workbook['Data']
    
    # 如果是第一个文件，保留标题行
    if file_index == 0:
        for row in sheet.iter_rows(values_only=True):
            merged_sheet.append(row)
    else:
        # 如果不是第一个文件，跳过标题行
        for row in sheet.iter_rows(min_row=2, values_only=True):
            merged_sheet.append(row)

# 保存新的合并后的工作簿
merged_workbook.save('merged_files.xlsx')

拆分一个sheet页到多个sheet页

假设你有一个名为Data的sheet，你希望根据某个条件（例如，每10行数据创建一个新的sheet）进行拆分。

from openpyxl import load_workbook

# 载入原始的工作簿
workbook = load_workbook('example.xlsx')
# 获取需要拆分的工作表
original_sheet = workbook['Data']

# 设置每个新工作表的最大行数
rows_per_sheet = 10

# 提取标题行数据
titles = [cell.value for cell in original_sheet[1]]

# 初始化新工作表变量
new_sheet = None

# 从第二行开始迭代原始工作表的数据行，以跳过标题行
for idx, row in enumerate(original_sheet.iter_rows(min_row=2, values_only=True), start=1):
    # 每rows_per_sheet行数据创建一个新工作表，并添加标题行
    if idx % rows_per_sheet == 1:
        sheet_title = f'Sheet_{(idx - 1) // rows_per_sheet + 1}'
        new_sheet = workbook.create_sheet(title=sheet_title)
        new_sheet.append(titles)
    
    # 往新工作表添加当前行数据
    new_sheet.append(row)

# 保存工作簿
workbook.save('example_splitted.xlsx')

请注意，这些示例中的代码需要适当修改以适应你的特定需求，如文件名、sheet名、拆分条件等。同时，确保在运行这些脚本之前，备份所有原始文件，以免不小心覆盖或丢失数据。