如何使用 Python 和 Openpyxl 自动化电子表格

电子表格一直是管理个人和组织信息的重要工具。但是，手动电子表格任务可能非常耗时且容易出错。本指南将向您展示 Python 和 Openpyxl（一个用于处理 Excel 文件的强大 Python 库）如何彻底改变您管理电子表格的方式。我们还将探讨使用 Python 执行电子表格任务的好处，以及 Openpyxl 如何帮助您自动执行许多繁琐且容易出错的电子表格任务。

在本文中，我们将学习如何执行简单的数据自动化操作，例如访问单元格值、条件格式以及使用公式和计算。您将能够使用电子表格简化您的日常任务。

开始

让我们从设置环境开始。

安装 Python

您可以从 Python 官方网站获取操作系统最新版本的 Python。之后，只需按照安装说明完成该过程即可。

安装 Openpyxl

之后，将 Openpyxl 添加到您的工具箱中。打开终端或命令提示符，然后使用以下命令使用 Python 的包管理器 pip 安装 Openpyxl。

pip 安装 openpyxl

如果安装成功，您将看到以下输出。

现在，您可以使用 Openpyxl 来自动化您的电子表格操作。

电子表格基本操作

想象一下，您有一个 Excel 工作表，里面装满了有价值的数据，例如员工信息。在本教程中，我们将使用 The Spreadsheet Guru 提供的 Excel 工作表。您可以从给定的链接下载它，它将包含以下数据集。我们将使用此 Excel 工作表来展示如何使用 Openpyxl 进行各种电子表格操作。

加载和访问数据

让我们从访问我们之前下载的 Excel 工作表开始，以展示如何在本地计算机上加载 Excel 工作表。这是我项目的文件夹结构。employee_data 文件和 working.py 文件位于同一目录中。

要使用 Openpyxl 加载 Excel 文件，请执行以下 Python 代码。

import openpyxl

wb = openpyxl.load_workbook('employee_data.xlsx')

它不会提供任何输出。但是代码将执行而不会出现任何错误。

访问单元格值

证明上述代码打开文件的最简单方法之一是访问和显示单元格值。让我们在单元格 B2 中打印员工的全名。要访问某个单元格值，必须首先访问它所属的工作表。在本例中，工作表的名称为 Data。

import openpyxl

#Load the Excel file
wb = openpyxl.load_workbook('employee_data.xlsx')

#Access the sheet
sheet = wb['Data']

#Access the cell value
first_name = sheet['B2'].value

#Print the cell value
print(f"The first name is: {first_name}")

您将获得以下输出。

这是访问 Excel 工作表中的单元格值的简单示例。现在，让我们做一些更复杂的事情。

假设您要分别显示在 B 列和 J 列中存储的所有员工的全名和年薪。Python 可以通过以下代码帮助您完成此任务：

import openpyxl

#Load the Excel file
wb = openpyxl.load_workbook('employee_data.xlsx')

#Access the sheet
sheet = wb['Data']

for row in sheet.iter_rows(min_row=2, values_only=True):
    full_name = f"{row[1]}"
    annual_salary = row[9]
    print(f"Employee: {full_name}, Annual Salary: {annual_salary}")

执行此代码后，将获得以下输出。

让我们再举一个例子。筛选是处理 Excel 数据的日常操作之一。让我们过滤所有姓名以“E”开头的员工。

import openpyxl

wb = openpyxl.load_workbook('employee_data.xlsx')

sheet = wb['Data']

for row in sheet.iter_rows(min_row=2, values_only=True):
    full_name = f"{row[1]}"
    annual_salary = row[9]
   
    # Filter employees whose name starts with 'E'
    if full_name.startswith("E"):
        print(f"Employee: {full_name}")

This code will give the following output.

Modifying Cell Values

修改单元格值是您将经常在 Excel 中执行的一项基本操作。下面是一个示例来说明这一点。您必须使用程序更改员工的电话号码或地址。假设以前在北京的每个人都搬到了迈阿密，并且您希望相应地修改数据并将其保存到新的 Excel 文件中。如何一次性为多名员工进行此更改？

import openpyxl

wb = openpyxl.load_workbook('employee_data.xlsx')

sheet = wb['Data']

# Change cell values from Beijing to Miami
for row in sheet.iter_rows(min_row=2, min_col=13, max_col=13):
    for cell in row:
        if cell.value == 'Beijing':
            cell.value = 'Miami'

 # Save the modified workbook
wb.save('employee_data_updated.xlsx')  

在此代码片段中，我们创建了一个名为 employee_data_updated 的新 Excel 文件，其中所有居住在北京的员工都搬到了迈阿密。

高级操作

在本节中，我们将研究使用 Openpyxl 进行更具挑战性的操作。

条件格式

Excel 的条件格式设置功能非常有效。假设您想以绿色突出显示年收入超过 50,000 美元的所有员工。按照以下步骤设置条件格式规则：

import openpyxl
from openpyxl.styles import PatternFill

wb = openpyxl.load_workbook('employee_data.xlsx')

sheet = wb['Data']

# Define the green fill style
green_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')

# Iterate through rows starting from the second row (assuming the headers are in the first row)
for row_number, row in enumerate(sheet.iter_rows(min_row=2, values_only=True), start=2):
    annual_salary = row[9]  
   
    # Check if the Annual Salary is greater than $50,000
    if annual_salary > 50000:
        # If the condition is met, apply a green fill to the Annual Salary cell
        cell = sheet.cell(row=row_number, column=10)  
        cell.fill = green_fill

wb.save('employee_data_updated.xlsx')

您可以通过阅读注释轻松理解代码。运行上述代码后，您将在新的 Excel 工作表文件中看到以下更改。

但是，如果您想使薪水超过 50,000 美元加粗和斜体，您可以使用以下代码：

import openpyxl
from openpyxl.styles import Font

wb = openpyxl.load_workbook('employee_data.xlsx')

sheet = wb['Data']

# Define the bold and italic font styles combined
bold_italic_font = Font(bold=True, italic=True)

for row_number, row in enumerate(sheet.iter_rows(min_row=2, values_only=True), start=2):
    annual_salary = row[9]

    if annual_salary > 50000:
        # If the condition is met, apply the bold and italic font style to the Annual Salary cell
        cell = sheet.cell(row=row_number, column=10)
        cell.font = bold_italic_font

wb.save('employee_data_updated.xlsx')

这是它的输出。

公式和计算

Excel 以其公式功能而闻名。使用 Openpyxl，您可以自动应用这些公式。让我们找到所有工资的总和、平均和中位数。您可以找到 J2 单元和 J1001 单元之间的所有工资。虽然我们可以使用 Python 计算这些值，但我们将使用 Excel 公式“=SUM（J2：J1001）”向您展示如何自动化 Excel 公式。

import openpyxl

wb = openpyxl.load_workbook('employee_data.xlsx')

sheet = wb['Data']

# Insert labels and formulas to find the sum, average, and median
sheet['O2'].value = "Sum"
sheet['P2'].value = "=SUM(J2:J1001)"

sheet['O3'].value = "Average"
sheet['P3'].value = "=AVERAGE(J2:J1001)"

sheet['O4'].value = "Median"
sheet['P4'].value = "=MEDIAN(J2:J1001)"

wb.save('employee_data_updated.xlsx')

更新后的 Excel 文件在 P2、P3 和 P4 单元格上显示方程式。

批量处理多个文件

假设一个文件夹包含多个 Excel 文件，每个文件都需要相同的数据处理步骤。您可以使用 Python 自动执行此批处理。

import os
import openpyxl

# Define the directory containing Excel files
directory = 'excel_files/'

# Iterate through files in the directory
for filename in os.listdir(directory):
    if filename.endswith('.xlsx'):
        file_path = os.path.join(directory, filename)
       
        # Load and process each file
        wb = openpyxl.load_workbook(file_path)
        # Perform operations…
       
        wb.save(file_path)

此脚本可自动处理大量 Excel 文件，从而帮助您节省时间和精力。您需要根据自己的需求更新目录路径，并编写需要执行的操作。

合并来自多个工作表的数据

您将遇到的另一个例行 Excel 任务是合并来自各种工作表或工作簿的数据。Python 可以使这个过程更容易。例如，如果您在单独的工作簿中有多个季度的销售数据，则可以使用 Python 将它们组合在一起。

import openpyxl

# Load the target workbook where data will be consolidated
target_wb = openpyxl.load_workbook('combined_sales.xlsx')
target_sheet = target_wb['CombinedData']

# List of source workbooks
source_files = ['sales_data_q1.xlsx', 'sales_data_q2.xlsx']

for source_file in source_files:
    source_wb = openpyxl.load_workbook(source_file)
    source_sheet = source_wb.active  # Assuming data is in the first sheet

    # Append rows from source to target, skipping the header
    for row in source_sheet.iter_rows(min_row=2, values_only=True):
        target_sheet.append(row)

# Save the combined data
target_wb.save('combined_sales.xlsx')

借助此代码，您可以将多个工作表中的数据合并为一个工作表。您需要根据需要更新文件名。

最佳实践

让我们看看在使用 Python 和 Openpyxl 自动执行电子表格操作时可以遵循的一些最佳实践。

优化代码效率

效率至关重要，尤其是在处理大量数据集时。在优化代码时，使用有效的方法，消除不必要的操作，并考虑实现多线程或多处理以进行并行处理。

版本控制和协作

使用 Git 等版本控制工具，您可以跟踪更改、有效协作并保留自动化脚本的修订历史记录。

文档和注释

使用注释和文档字符串，彻底记录您的代码。它将帮助其他人快速理解您的自动化脚本，并帮助您在文档变大时维护文档。

测试和验证

在生产环境中部署自动化脚本之前，必须在受控环境中对其进行全面测试。确保它们按预期执行，并能够处理各种情况。

您可以在本文中了解用于使用 Python 处理电子表格的其他第三方包。

结论

使用 Python 和 openpyxl 自动化电子表格有很多好处。Python 和 Openpyxl 提供了所需的工具和灵活性，无论是创建报告、进行数据分析还是加速数据验证。

通过成为电子表格自动化专家，您可以获得对数据管理的新见解，并节省时间、金钱和精力。我们希望您学习如何使用 openpyxl 和 Python 创建创新的电子表格，并将自动化作为日常工作的基本技能。