Python使用openpyxl设置Excel单元格公式和工作簿合并

目录

• Python openpyxl 设置Excel单元格公式和工作簿合并
• 安装 openpyxl
• Excel 单元格中设置公式
• 代码示例
• 步骤详解
• 公式中包含别的工作簿中的内容
• 代码示例
• 步骤详解
• 使用openpyxl 合并多个工作簿
• 代码示例
• 步骤详解
• 运行效果
• 总结

在 Python 的 openpyxl 库中，为 Excel 单元格设置公式的方法是直接将公式字符串赋值给单元格的 .value 属性。公式必须以等号 (=) 开头。

如果尚未安装 openpyxl，可以使用以下命令进行安装：

pip install openpyxl

或使用uv：

uv add openpyxl

与我们日常使用Excel相同，所有的单元格公式都需要以“=”号开头。

下面是一个示例代码，展示如何创建一个新的工作簿，在单元格中设置公式，并保存工作簿：

from openpyxl import Workbook
# 创建一个新的工作簿和活动工作表
wb = Workbook()
ws = wb.active

# 为 A1 和 B1 单元格设置数值
ws['A1'] = 10
ws['B1'] = 5

# 为 C1 单元格设置公式，计算 A1 和 B1 的和
ws['C1'] = '=SUM(A1:B1)'

# 为 D1 单元格设置另一个公式，计算 A1 除以 B1 的结果并保留两位小数
ws['D1'] = '=ROUND(A1/B1, 2)'

# 保存工作簿
wb.save('formula_example.xlsx')

print("已成功为单元格设置公式并保存到 formula_example.xlsx")

• 导入库并打开工作簿：首先，导入 openpyxl 库，然后创建一个新的工作簿或打开一个现有的工作簿。
• 选择单元格：使用工作表对象（例如 ws）的方括号表示法，并传入单元格的地址（如 ‘A1’）来选择目标单元格。
• 设置公式字符串：将以等号开头的 Excel 公式作为字符串赋值给选定单元格的 .value 属性。例如，ws[‘A1’] = ‘=SUM(B1:C1)’。
• 保存文件：使用 wb.save() 方法将修改后的工作簿保存为 .xlsx 文件。

运行上述代码后，我们将得到一个名为 formula_example.xlsx 的 Excel 文件，其中 C1 单元格包含计算 A1 和 B1 之和的公式。请注意，Excel 需要打开文件后才能计算并显示公式的结果。

有时候，我们需要引用别的工作簿中的单元格内容作为公式的一部分。可以使用以下语法：

='[workbook_name.xlsx]sheet_name'!cell_reference

from openpyxl import Workbook
# 创建一个新的工作簿和活动工作表
wb = Workbook()
ws = wb.active
# 为 A1 和 B1 单元格设置数值
ws['A1'] = 10
ws['B1'] = 5
# 为 C1 单元格设置公式，引用另一个工作簿中的单元格
ws['C1'] = "='[other_workbook.xlsx]Sheet'!A1 + '[other_workbook.xlsx]Sheet'!B1"
# 保存工作簿
wb.save('formula_with_external_reference.xlsx')
print("已成功为单元格设置引用外部工作簿的公式并保存到 formula_with_external_reference.xlsx")

• 引用外部工作簿：在公式字符串中使用上述语法，确保工作簿名称、工作表名称和单元格引用正确无误。
• 保存文件：同样使用 wb.save() 方法将工作簿保存为 .xlsx 文件。

运行上述代码后，我们将得到一个名为 formula_with_external_reference.xlsx 的 Excel 文件，其中 C1 单元格包含引用另一个工作簿中 A1 和 B1 单元格内容的公式。请注意，Excel 需要打开这个文件本身以及被引用的工作簿文件，才能正确计算和显示公式的结果。

从上面的示例可见，不打开被引用的工作簿，是无法正确得到所需要的公式的，那么，把需要引用的工作簿的对应工作表，整合到一个工作簿当中，那么单元格引用的问题，就变成了本工作簿的“内部问题”了。

而在处理多个工作簿时，openpyxl 提供了方便的方法来合并它们的内容。以下是一个示例代码，展示如何将多个工作簿的内容合并到一个新的工作簿中：

from openpyxl import load_workbook, Workbook
import os

def merge_sheets(source_files, target_file, sheet_index=0):
    '''
    合并多个Excel文件中的指定工作表到一个新的Excel文件中。
    参数:
        source_files: 包含源Excel文件路径的列表。
        target_file: 目标Excel文件的路径。
        sheet_index: 要合并的工作表的索引，默认为0（第一个工作表）。
    '''
    # 创建一个新的工作簿作为目标文件
    target_wb = Workbook()
    # 删除默认创建的第一个工作表，因为我们稍后会添加自己的
    if 'Sheet' in target_wb.sheetnames:
        del target_wb['Sheet']

    for file in source_files:
        if not os.path.exists(file):
            print(f"文件 {file} 不存在，跳过。")
            continue
        # 目标工作簿中创建对应的表
        target_ws = target_wb.create_sheet(title=os.path.splitext(os.path.basename(file))[0])
        # 加载源工作簿
        source_wb = load_workbook(filename=file)
        source_ws = source_wb.worksheets[sheet_index]

        # 复制源工作表的数据到目标工作表
        current_row = 1
        for row in source_ws.iter_rows(values_only=True):
            for col_index, value in enumerate(row, start=1):
                target_ws.cell(row=current_row, column=col_index, value=value)
            current_row += 1

    # 保存合并后的工作簿
    target_wb.save(target_file)
    print(f"已成功合并工作表并保存到 {target_file}")

# 示例用法
if __name__ == "__main__":
    source_files = []
    for i in range(1, 4):
        source_files.append(f'./data/快递价格{i}.xlsx')
    target_file = 'merged_sheets.xlsx'
    merge_sheets(source_files, target_file)

• 加载工作簿：使用 load_workbook() 函数加载每个源工作簿。
• 创建目标工作簿：使用 Workbook() 创建一个新的工作簿作为目标文件。
• 复制数据：遍历源工作表的行和列，将数据复制到目标工作表中。
• 保存文件：使用 target_wb.save() 方法将合并后的工作簿保存为 .xlsx 文件。

运行上述代码后，我们将得到一个名为 merged_sheets.xlsx 的 Excel 文件：

通过上述方法，我们可以轻松地在 Excel 单元格中设置公式，并且可以引用其他工作簿中的单元格内容。使用 openpyxl 库，我们还可以将多个工作簿的内容合并到一个新的工作簿中，方便进行数据处理和分析。

以上就是Python使用openpyxl设置Excel单元格公式和工作簿合并的详细内容，更多关于Python openpyxl设置Excel单元格公式和合并的资料请关注风君子博客其它相关文章！

您可能感兴趣的文章:

• Python自动化操作Excel的多种方式(Pandas+openpyxl+xlrd)
• Python使用openpyxl读取Excel的操作详解
• Python中处理Excel数据的方法对比(pandas和openpyxl)
• python使用Openpyxl操作Excel文件的实现
• Python利用xlwt/openpyxl/xlutils实现写入Excel数据