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

python openpyxl 设置excel单元格公式和工作簿合并

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

安装 openpyxl

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

pip install openpyxl

或使用uv：

uv add openpyxl

excel 单元格中设置公式

与我们日常使用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 合并多个工作簿

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

代码示例

而在处理多个工作簿时，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)