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

更新時間：2025年11月03日 09:33:00 作者：Humbunklung

這篇文章主要介紹了如何使用Python的openpyxl庫在Excel單元格中設置公式,并展示了如何引用其他工作簿中的單元格內容以及如何合并多個工作簿,需要的朋友可以參考下

Python openpyxl 設置Excel單元格公式和工作簿合并

在 Python 的 openpyxl 庫中，為 Excel 單元格設置公式的方法是直接將公式字符串賦值給單元格的 .value 屬性。公式必須以等號 (=) 開頭。

安裝 openpyxl

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

pip install openpyxl

或使用uv：

uv add openpyxl

Excel 單元格中設置公式

與我們日常使用Excel相同，所有的單元格公式都需要以“=”號開頭。

代碼示例

下面是一個示例代碼，展示如何創(chuàng)建一個新的工作簿，在單元格中設置公式，并保存工作簿：

from openpyxl import Workbook
# 創(chuàng)建一個新的工作簿和活動工作表
wb = Workbook()
ws = wb.active

# 為 A1 和 B1 單元格設置數(shù)值
ws['A1'] = 10
ws['B1'] = 5

# 為 C1 單元格設置公式，計算 A1 和 B1 的和
ws['C1'] = '=SUM(A1:B1)'

# 為 D1 單元格設置另一個公式，計算 A1 除以 B1 的結果并保留兩位小數(shù)
ws['D1'] = '=ROUND(A1/B1, 2)'

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

print("已成功為單元格設置公式并保存到 formula_example.xlsx")

步驟詳解

導入庫并打開工作簿：首先，導入 openpyxl 庫，然后創(chuàng)建一個新的工作簿或打開一個現(xiàn)有的工作簿。
選擇單元格：使用工作表對象（例如 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
# 創(chuàng)建一個新的工作簿和活動工作表
wb = Workbook()
ws = wb.active
# 為 A1 和 B1 單元格設置數(shù)值
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文件中。
    參數(shù):
        source_files: 包含源Excel文件路徑的列表。
        target_file: 目標Excel文件的路徑。
        sheet_index: 要合并的工作表的索引，默認為0（第一個工作表）。
    '''
    # 創(chuàng)建一個新的工作簿作為目標文件
    target_wb = Workbook()
    # 刪除默認創(chuàng)建的第一個工作表，因為我們稍后會添加自己的
    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
        # 目標工作簿中創(chuàng)建對應的表
        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]

        # 復制源工作表的數(shù)據(jù)到目標工作表
        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)