Python結合Excel能消除內置公式限制，實現高級分析、自動化、數據可視化等。可通過xlwings、PyXLL等插件啟用Python功能，或使用Microsoft 365預覽版。可內聯運行代碼、自定義函數或VBA互操作。數據分析工作流程包括用pandas導入數據、Matplotlib可視化、機器學習預測等。需注意內存管理、文件安全等問題。

譯自：Why Combine Python and Excel?[1]

作者：Jessica Wachtel

為什么要結合 Python[2] 和 Excel[3]？ 簡而言之，Python 消除了 Excel 內置公式的限制。Python 是應用程序開發人員最流行的語言之一，其原因同樣適用于在 Excel 中使用 Python 的人。在 Excel 中使用 Python 將 Python 的高級分析庫（pandas、NumPy）、自動化功能、數據可視化工具（Matplotlib）和可擴展性引入到 Excel 中。

前提條件和設置

安裝 Python 和 pip

1. 從 python.org[4] 下載最新版本的 Python。

2. 在安裝過程中，選中“將 Python 添加到 PATH”的框。

3. 安裝或升級 pip（Python 的包管理器）：

python -m ensurepip --upgrade

測試安裝：

python --version
pip --version

輸出：Python 和 pip 的最新版本

在您的機器上安裝了最新版本的 Python 和 pip 之后，我們可以開始設置 Excel。

啟用 Excel-Python 插件 (xlwings, PyXll)

xlwings

xlwings 是一個免費的開源 Excel-Python 橋梁，允許您從 Excel 調用 Python 腳本。它非常適合自動化報告、清理數據和構建自定義函數，而無需 Visual Basic for Applications (VBA)[5])。

首先，使用終端在您的機器上安裝：

pip install xlwings

然后從 Excel 啟用它：

1. 打開 Excel，然后打開“開發工具”選項卡，然后打開“Excel 插件”，并選中“xlwings”。

2. 使用 RunPython 宏從單元格執行 Python 代碼。

PyXLL

PyXLL 是一種專業級（付費）Excel-Python 集成工具，專為生產環境而設計。它比 xlwings 更高級。它允許您在 Python 中創建自定義 Excel 函數，在工作簿打開時運行 Python 代碼[6]，并與高級數據科學庫集成。

1. 從 pyxll.com[7] 下載。

2. 安裝并激活您的許可證。

3. 配置您的 pyxll.cfg（或 .ini）文件以設置 Python 路徑和選項。

4. 重新啟動 Excel 并直接從單元格開始使用 Python 函數。

使用 xlwings 進行免費、開源的 Excel 自動化和快速 Python 集成（例如，從 CSV 中提取數據，使用 pandas 清理數據并將其發送回 Excel）。選擇 PyXLL 用于需要高性能、自定義函數和專業支持的高級生產級解決方案（例如，在 Excel 中創建具有實時計算的實時市場數據饋送）。

本教程將使用 xlwings 而不是 PyXLL，因為 xlwings 是開源選項。

嘗試 Microsoft 365 “Excel 中的 Python”預覽版

如果您需要立即訪問 Excel 中的 Python 而無需任何安裝，Microsoft 365 的“Excel 中的 Python”（測試版）非常有用。它不能真正替代 xlwings 或 PyXLL，因為這些工具具有更強大的功能。Microsoft 365 “Excel 中的 Python”的用例是：

? 任務簡單或探索性。

? 在 Excel 中試驗或學習 Python。

Microsoft 365 “Excel 中的 Python” 使您可以使用 ＝PY() 函數立即訪問 Python 庫，如 pandas 和 Matplotlib。

如果您是 Microsoft 365 預覽體驗成員 Beta 頻道的一部分，則可以試用該測試版。打開 Excel 并輸入如下公式：

=PY("print('Hello from Python!')")

此函數將在單元格內運行。

從 Excel 運行 Python 代碼

根據您的需求和偏好，有幾種方法可以做到這一點。

內聯 Python 單元格

使用 ＝PY() 函數直接在 Excel 單元格中運行小型 Python 腳本?？紤]輕量級計算和簡單的數據轉換。

=PY("sum([1, 2, 3, 4])")

用戶自定義函數 (UDF)

UDF[8] 是將 Python 邏輯集成到電子表格中的自定義函數。使用 xlwings 和 PyXLL 時，可以創建 UDF。UDF 非常適合實時數據集成、自定義財務計算以及數據驗證和清理。

如何創建 UDF：

? 構建 Python 代碼文件：

打開文本編輯器或 IDE（例如，VS Code，[9] PyCharm[10]，Sublime 等）。

保存一個新文件，例如 py。

編寫代碼（例如，基本加法代碼）。

import xlwings as xw


@xw.func
def add_numbers(a, b):
    return a + b

? 連接到 Excel（必須安裝 xlwings 并在 Excel 中啟用 xlwings）：

在 Excel 的 xlwings 設置中，將插件指向您的 udfs.py 文件。

? 從 Excel 調用它：

          在任何單元格中，鍵入在 py 中定義的函數名稱。

=add_numbers(5, 10)

Excel 將運行 Python 代碼并返回 15。

VBA 到 Python 的互操作性

如果您已經在使用 VBA 宏，則可以使用它們通過 xlwings 或 PyXLL 觸發 Python 腳本。這允許您將 Python 引入 VBA 工作流程，而無需從頭開始重建所有內容。

與上面的 UDF 示例類似，您需要安裝 xlwings（或 PyXLL，具體取決于您使用的工具）并在 Excel 中啟用 xlwings。

創建 Python 代碼文件

該文件需要包含一個 main() 函數來匹配 VBA 調用。讓我們將文件命名為 vba_file.py。

def main():
    print("Yay Python code")

在 xlwings 設置（或 VBA 代碼中）配置路徑，以確保 Python 知道腳本的位置。

在 Excel 中運行 VBA 宏時，它將使用 xlwings（或 PyXLL）。Python 執行代碼后，可以將結果發送回 Excel 或用于更新工作簿。

數據分析工作流程

這些工作流程不會使用內聯代碼。它們將與上面的 UDF 示例密切相關（但并非所有這些函數都是 UDF）。這意味著您必須在 Excel 中將 xlwings 指向 Python 腳本文件的名稱，或使用 VBA RunPython 調用正確配置它。

使用 pandas 導入數據

使用 pandas 庫導入和分析 Excel 文件中的數據。下面的示例從 Excel 文件中讀取數據，執行基本分析并將結果寫回工作簿。

import pandas as pd
import xlwings as xw


def analyze_and_write():
    # Connect to the calling Excel workbook
    wb = xw.Book.caller()       
    sheet = wb.sheets[0]


    # Step 1: Read data from a local Excel file 
    df = pd.read_excel("sales.xlsx")


    # Step 2: Analyze data
    summary = df.describe()


    # Step 3: Write the summary statistics back into Excel, starting at cell A10
    sheet.range('A10').options(index=True).value = summary

使用 Matplotlib 可視化

Matplotlib 是一種數據可視化工具。下面的示例從 Excel 文件中讀取數據，生成條形圖，將其保存為圖像，并將圖表插入到工作簿中。

import pandas as pd
import matplotlib.pyplot as plt
import xlwings as xw


def create_and_insert_chart():
    wb = xw.Book.caller()
    sheet = wb.sheets[0]


    # read data 
    df = pd.read_excel("sales.xlsx")


    # create  bar chart for 'Revenue' column
    df['Revenue'].plot(kind='bar')
    plt.title('Monthly Revenue')
    plt.tight_layout()
    plt.savefig('revenue_chart.png')  # Save chart as image file
    plt.close()


    # insert chart  into Excel sheet at cell E2
    sheet.pictures.add('revenue_chart.png', left=sheet.range('E2').left, top=sheet.range('E2').top)

下面的機器學習 (ML) 示例使用 pandas 來處理 Excel 數據，并使用 joblib 來加載預先訓練的模型以進行預測。這些工具協同工作以從 Excel 文件加載數據。然后，它應用預先訓練的模型來預測結果并將結果寫回工作簿。

import pandas as pd
import xlwings as xw
from joblib import load


def predict_and_write():
    wb = xw.Book.caller()
    sheet = wb.sheets[0]


    # load data for prediction
    df = pd.read_excel("sales.xlsx")


    # load pre-trained ML model
    model = load('model.joblib')


    # predict using selected features
    predictions = model.predict(df[['feature1', 'feature2']])


    # write predictions back to Excel starting at cell D2
    sheet.range('D2').options(index=False, header=False).value = predictions

從 Python 讀取和寫入 Excel 文件

openpyxl 和 xlsxwriter 是用于處理 Excel 文件的流行 Python 庫。

openpyxl 基礎

當您需要讀取或修改現有 Excel 文件時，openpyxl 是一個很棒的工具?？紤]編輯電子表格、使用公式、讀取數據。

openxyl 可以處理磁盤上的 Excel 文件，這意味著它可以從計算機上保存的 .xlsx 文件讀取或寫入數據。您不需要 xlwings 就可以做到這一點。

下面的示例打開一個文件，更新它并保存更改。

from openpyxl import load_workbook


# Load an existing workbook
wb = load_workbook("existing_file.xlsx")


# Select the active worksheet (or specify by name: wb['Sheet1'])
ws = wb.active


# Modify a cell value
ws['A1'] = 'Updated Hello Excel'


# Save changes back to the file (can overwrite or save as new file)
wb.save("existing_file.xlsx")

xlswriter 基礎

xlswriter 創建具有高級格式和圖表的新 Excel 文件。它不能修改現有文件?？紤]從頭開始生成報告或文件。

xlswriter 是一個獨立的 Python 庫。它將文件直接寫入磁盤，但不與打開的 Excel 應用程序交互。

下面的示例創建一個格式化的 Excel 文件。

import xlsxwriter


workbook = xlsxwriter.Workbook('output_xlsxwriter.xlsx')
worksheet = workbook.add_worksheet()


worksheet.write('A1', 'Hello Excel with XlsxWriter')


workbook.close()

樣式和條件格式

樣式使報告在視覺上更具吸引力且更易于閱讀。

您可以使用 openxl 自定義 Excel 文件的外觀。您可以設置顏色、字體和邊框等內容。

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side


wb = Workbook()
ws = wb.active


# write some data
ws['A1'] = "Sales"
ws['A2'] = 100
ws['A3'] = 250


# wet font color and bold
ws['A1'].font = Font(color="FF0000", bold=True)


# set cell fill color (yellow)
ws['A2'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")


# set thin border around cell A3
thin_border = Border(left=Side(style='thin'),
                     right=Side(style='thin'),
                     top=Side(style='thin'),
                     bottom=Side(style='thin'))
ws['A3'].border = thin_border


wb.save("styled.xlsx")

條件格式根據單元格值自動突出顯示趨勢或重要數據。使用條件格式而不是樣式來突出顯示異常值、數據閾值或使分析更直觀。

下面的條件格式突出顯示所有大于 150 的單元格。

from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill


red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')


ws.conditional_formatting.add('A2:A10',
                              CellIsRule(operator='greaterThan', formula=['150'], fill=red_fill))


wb.save("conditional_formatting.xlsx")

自動化報告和儀表板

自動化是開發人員喜歡 Python 的一個重要原因，它可以很好地轉化為 Excel 用戶。

調度腳本

對于以前調度過腳本的任何人來說，這是一個熟悉的工作流程。您可以簡單地使用 Mac/Linux 的 cron 或 Windows 上的 Windows 任務計劃程序。這將按設定的時間調度 Python 腳本。

通過電子郵件發送生成的工作簿

SMTP[11] 和 Python 的內置 smtplib 庫協同工作，以將您的工作簿作為電子郵件發送。它們允許您的 Python 腳本連接到電子郵件服務器并發送帶有附件的電子郵件。此集成使您的 Excel 工作流程實現端到端自動化。

下面的示例將 Excel 文件作為電子郵件發送：

import smtplib
from email.message import EmailMessage


msg = EmailMessage()
msg['Subject'] = 'Automated Report'
msg['From'] = 'you@example.com'
msg['To'] = 'team@example.com'
msg.set_content('Please see attached report.')


with open('report.xlsx', 'rb') as f:
    msg.add_attachment(f.read(),
                       maintype='application',
                       subtype='vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                       filename='report.xlsx')


with smtplib.SMTP('smtp.example.com') as s:
    s.login('user', 'password')
    s.send_message(msg)

性能和安全注意事項

管理內存和大型數據集

與任何其他大型文件類似，當處理大型 Excel 文件時，如果一次將所有內容加載到內存中，性能可能會迅速下降。由于這些 Excel 文件通常非常大，因此以下是一些提示：

? 分塊讀取文件： pandas 有很好的工具可以做到這一點，例如 pandas.read_csv(..., chunksize=50000) 和 pandas.read_excel()。這將有助于以較小的部分處理數據，減少內存并防止崩潰。

? 利用數據庫： 您可以將繁重的數據處理或聚合卸載到數據庫（SQL、Postgres），而不是使用 Python。然后，您可以將匯總或篩選的數據提取到 Python 中以進行分析或報告。

? 避免不必要的副本： 除非絕對必要，否則在處理 DataFrames 時，優先使用就地操作而不是深度復制。

沙盒和宏安全性

在 Excel 中運行 Python 腳本和宏會帶來安全風險，尤其是在文件來自未知或不受信任的來源時。以下是一些提示：

? 驗證和掃描文件： 在運行 Excel 文件之前，始終掃描 Excel 文件中是否存在惡意宏或嵌入式腳本。

? 使用虛擬環境： 在公司或共享環境中，在隔離的虛擬環境（venv 或 conda）中運行 Python 代碼，以包含依賴項并降低系統范圍影響的風險。

? 限制宏設置： 配置 Excel 的宏安全設置，以禁用或提示在運行來自不受信任來源的宏之前。

結論

閱讀本教程后，您就可以很好地開始在 Excel 中使用 Python。雖然我們只了解了 Python 和 Excel 可以一起完成的工作的表面，但這肯定是一個開始。現在，您可以更智能地工作、處理更大的數據并創建比以往任何時候都更具洞察力的電子表格。

引用鏈接

[1] Why Combine Python and Excel?:https://thenewstack.io/why-combine-python-and-excel/

[2]Python:https://thenewstack.io/what-is-python/

[3]Excel:https://thenewstack.io/microsoft-puts-python-in-excel/

[4]python.org:https://python.org

[5]Visual Basic for Applications (VBA):https://learn.microsoft.com/en-us/office/vba/library-reference/concepts/getting-started-with-vba-in-office

[6]Python 代碼:https://thenewstack.io/how-to-use-json-in-your-python-code/

[7]pyxll.com:http://pyxll.com

[8]UDF:https://thenewstack.io/scylladbs-take-on-webassembly-for-user-defined-functions/

[9]VS Code，:https://thenewstack.io/how-to-use-vs-code-for-python-and-why-you-should/

[10]PyCharm:https://thenewstack.io/7-must-have-python-tools-for-ml-devs-and-data-scientists/

[11]SMTP:https://thenewstack.io/how-to-write-your-own-email-server-in-rust/