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

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

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

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

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

安裝 openpyxl

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

pip install openpyxl

或使用uv：

uv add openpyxl

Excel 單元格中設置公式

與我們?nèi)粘Ｊ褂肊xcel相同，所有的單元格公式都需要以“=”號開頭。

代碼示例

下面是一個示例代碼，展示如何創(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 的結(jié)果并保留兩位小數(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 需要打開文件后才能計算并顯示公式的結(jié)果。

公式中包含別的工作簿中的內(nèi)容

有時候，我們需要引用別的工作簿中的單元格內(nèi)容作為公式的一部分?？梢允褂靡韵抡Z法：

='[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 單元格內(nèi)容的公式。請注意，Excel 需要打開這個文件本身以及被引用的工作簿文件，才能正確計算和顯示公式的結(jié)果。

使用openpyxl 合并多個工作簿

從上面的示例可見，不打開被引用的工作簿，是無法正確得到所需要的公式的，那么，把需要引用的工作簿的對應工作表，整合到一個工作簿當中，那么單元格引用的問題，就變成了本工作簿的“內(nèi)部問題”了。

代碼示例

而在處理多個工作簿時，openpyxl 提供了方便的方法來合并它們的內(nèi)容。以下是一個示例代碼，展示如何將多個工作簿的內(nèi)容合并到一個新的工作簿中：

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)