excel-automation

Excel 自動化技能

概述

本技能使用 xlwings 實現進階 Excel 自動化——這是一個能與即時 Excel 實例互動的函式庫。與 openpyxl（僅限檔案操作）不同，xlwings 可以即時控制 Excel、執行 VBA、更新儀表板，並自動化複雜的工作流程。

使用方式

描述您需要的 Excel 自動化任務
指定您需要即時 Excel 互動還是檔案處理
我將產生 xlwings 程式碼並執行

範例提示：

"用新資料更新這個即時 Excel 儀表板"
"執行這個 VBA 巨集並取得結果"
"建立一個資料驗證的 Excel 增益集"
"自動化產生包含即時圖表的月報"

領域知識

xlwings vs openpyxl

功能	xlwings	openpyxl
需要 Excel	是	否
即時互動	是	否
執行 VBA	是	否
速度（大型檔案）	快	慢
伺服器部署	有限	容易

xlwings 基礎

import xlwings as xw

# 連線到作用中的 Excel 活頁簿
wb = xw.Book.caller()  # 從 Excel 增益集
wb = xw.books.active   # 作用中活頁簿

# 開啟特定檔案
wb = xw.Book('path/to/file.xlsx')

# 建立新活頁簿
wb = xw.Book()

# 取得工作表
sheet = wb.sheets['Sheet1']
sheet = wb.sheets[0]

操作範圍

讀取與寫入

# 單一儲存格
sheet['A1'].value = 'Hello'
value = sheet['A1'].value

# 範圍
sheet['A1:C3'].value = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
data = sheet['A1:C3'].value  # 回傳串列清單

# 命名範圍
sheet['MyRange'].value = 'Named data'

# 展開範圍（偵測資料邊界）
sheet['A1'].expand().value  # 所有相連資料
sheet['A1'].expand('table').value  # 表格格式

動態範圍

# 目前區域（類似 Ctrl+Shift+End）
data = sheet['A1'].current_region.value

# 使用範圍
used = sheet.used_range.value

# 最後一列有資料
last_row = sheet['A1'].end('down').row

# 調整範圍大小
rng = sheet['A1'].resize(10, 5)  # 10 列，5 欄

格式化

# 字型
sheet['A1'].font.bold = True
sheet['A1'].font.size = 14
sheet['A1'].font.color = (255, 0, 0)  # RGB 紅色

# 填滿
sheet['A1'].color = (255, 255, 0)  # 黃色背景

# 數字格式
sheet['B1'].number_format = '$#,##0.00'

# 欄寬
sheet['A:A'].column_width = 20

# 列高
sheet['1:1'].row_height = 30

# 自動調整
sheet['A:D'].autofit()

Excel 功能

圖表

# 新增圖表
chart = sheet.charts.add(left=100, top=100, width=400, height=250)
chart.set_source_data(sheet['A1:B10'])
chart.chart_type = 'column_clustered'
chart.name = 'Sales Chart'

# 修改現有圖表
chart = sheet.charts['Sales Chart']
chart.chart_type = 'line'

表格

# 建立 Excel 表格
rng = sheet['A1'].expand()
table = sheet.tables.add(source=rng, name='SalesTable')

# 重新整理表格
table.refresh()

# 存取表格資料
table_data = table.data_body_range.value

圖片

# 新增圖片
sheet.pictures.add('logo.png', left=10, top=10, width=100, height=50)

# 從 matplotlib 更新圖片
import matplotlib.pyplot as plt
fig, ax = plt.subplots()
ax.plot([1, 2, 3], [1, 4, 9])
sheet.pictures.add(fig, name='MyPlot', update=True)

VBA 整合

# 執行 VBA 巨集
wb.macro('MacroName')()

# 帶引數
wb.macro('MyMacro')('arg1', 'arg2')

# 取得回傳值
result = wb.macro('CalculateTotal')(100, 200)

# 存取 VBA 模組
vb_code = wb.api.VBProject.VBComponents('Module1').CodeModule.Lines(1, 10)

使用者自訂函數 (UDF)

# 定義 UDF（在 Python 檔案中）
import xlwings as xw

@xw.func
def my_sum(x, y):
    """將兩個數字相加"""
    return x + y

@xw.func
@xw.arg('data', ndim=2)
def my_array_func(data):
    """處理陣列資料"""
    import numpy as np
    return np.sum(data)

# 這些會變成 Excel 函數：=my_sum(A1, B1)

應用程式控制

# Excel 應用程式設定
app = xw.apps.active
app.screen_updating = False  # 加速
app.calculation = 'manual'   # 手動計算
app.display_alerts = False   # 隱藏對話框

# 執行操作...

# 還原
app.screen_updating = True
app.calculation = 'automatic'
app.display_alerts = True

最佳實務

停用畫面更新：批次操作時
使用陣列：讀寫整個範圍，而非逐個儲存格
手動計算：載入資料時關閉自動計算
關閉連線：完成後確實關閉活頁簿
錯誤處理：處理 Excel 未安裝的情況

常見模式

效能最佳化

import xlwings as xw

def batch_update(data, workbook_path):
    app = xw.App(visible=False)
    try:
        app.screen_updating = False
        app.calculation = 'manual'
        
        wb = app.books.open(workbook_path)
        sheet = wb.sheets['Data']
        
        # 一次寫入所有資料
        sheet['A1'].value = data
        
        app.calculation = 'automatic'
        wb.save()
    finally:
        wb.close()
        app.quit()

儀表板更新

def update_dashboard(data_dict):
    wb = xw.books.active
    
    # 更新資料工作表
    data_sheet = wb.sheets['Data']
    for name, values in data_dict.items():
        data_sheet[name].value = values
    
    # 重新整理所有圖表
    dashboard = wb.sheets['Dashboard']
    for chart in dashboard.charts:
        chart.refresh()
    
    # 更新時間戳
    from datetime import datetime
    dashboard['A1'].value = f'最後更新：{datetime.now()}'

報表產生器

def generate_monthly_report(month, data):
    template = xw.Book('template.xlsx')
    
    # 填入資料
    sheet = template.sheets['Report']
    sheet['B2'].value = month
    sheet['A5'].value = data
    
    # 執行計算
    template.app.calculate()
    
    # 匯出為 PDF
    sheet.api.ExportAsFixedFormat(0, f'report_{month}.pdf')
    
    template.save(f'report_{month}.xlsx')

範例

範例 1：即時儀表板更新

import xlwings as xw
import pandas as pd
from datetime import datetime

# 連線到執行中的 Excel
wb = xw.books.active
dashboard = wb.sheets['Dashboard']
data_sheet = wb.sheets['Data']

# 擷取新資料（模擬）
new_data = pd.DataFrame({
    'Date': pd.date_range('2024-01-01', periods=30),
    'Sales': [1000 + i*50 for i in range(30)],
    'Costs': [600 + i*30 for i in range(30)]
})

# 更新資料工作表
data_sheet['A1'].value = new_data

# 計算利潤
data_sheet['D1'].value = 'Profit'
data_sheet['D2'].value = '=B2-C2'
data_sheet['D2'].expand('down').value = data_sheet['D2'].formula

# 更新儀表板上的 KPI
dashboard['B2'].value = new_data['Sales'].sum()
dashboard['B3'].value = new_data['Costs'].sum()
dashboard['B4'].value = new_data['Sales'].sum() - new_data['Costs'].sum()
dashboard['A1'].value = f'更新時間：{datetime.now().strftime("%Y-%m-%d %H:%M")}'

# 重新整理圖表
for chart in dashboard.charts:
    chart.api.Refresh()

print("儀表板已更新！")

範例 2：批次處理多個檔案

import xlwings as xw
from pathlib import Path

def process_sales_files(folder_path, output_path):
    """將多個 Excel 檔案合併成一個摘要。"""
    
    app = xw.App(visible=False)
    app.screen_updating = False
    
    try:
        # 建立摘要活頁簿
        summary_wb = xw.Book()
        summary_sheet = summary_wb.sheets[0]
        summary_sheet.name = 'Consolidated'
        
        headers = ['檔案', '總銷售額', '總數量', '平均價格']
        summary_sheet['A1'].value = headers
        
        row = 2
        for file in Path(folder_path).glob('*.xlsx'):
            wb = app.books.open(str(file))
            data_sheet = wb.sheets['Sales']
            
            # 擷取摘要
            total_sales = data_sheet['B:B'].api.SpecialCells(11).Value  # xlCellTypeConstants
            total_units = data_sheet['C:C'].api.SpecialCells(11).Value
            
            # 計算並寫入
            summary_sheet[f'A{row}'].value = file.name
            summary_sheet[f'B{row}'].value = sum(total_sales) if isinstance(total_sales, (list, tuple)) else total_sales
            summary_sheet[f'C{row}'].value = sum(total_units) if isinstance(total_units, (list, tuple)) else total_units
            summary_sheet[f'D{row}'].value = f'=B{row}/C{row}'
            
            wb.close()
            row += 1
        
        # 格式化摘要
        summary_sheet['A1:D1'].font.bold = True
        summary_sheet['B:D'].number_format = '$#,##0.00'
        summary_sheet['A:D'].autofit()
        
        summary_wb.save(output_path)
        
    finally:
        app.quit()
    
    print(f"已合併 {row-2} 個檔案至 {output_path}")

# 使用方式
process_sales_files('/path/to/sales/', 'consolidated_sales.xlsx')

範例 3：含 UDF 的 Excel 增益集

# myudfs.py - 放置於 xlwings 專案中

import xlwings as xw
import numpy as np

@xw.func
@xw.arg('data', pd.DataFrame, index=False, header=False)
@xw.ret(expand='table')
def GROWTH_RATE(data):
    """計算逐期成長率"""
    values = data.iloc[:, 0].values
    growth = np.diff(values) / values[:-1] * 100
    return [['成長率 %']] + [[g] for g in growth]

@xw.func
@xw.arg('range1', np.array, ndim=2)
@xw.arg('range2', np.array, ndim=2)
def CORRELATION(range1, range2):
    """計算兩個範圍的相關係數"""
    return np.corrcoef(range1.flatten(), range2.flatten())[0, 1]

@xw.func
def SENTIMENT(text):
    """基本情緒分析（佔位）"""
    positive = ['good', 'great', 'excellent', 'amazing']
    negative = ['bad', 'poor', 'terrible', 'awful']
    
    text_lower = text.lower()
    pos_count = sum(word in text_lower for word in positive)
    neg_count = sum(word in text_lower for word in negative)
    
    if pos_count > neg_count:
        return 'Positive'
    elif neg_count > pos_count:
        return 'Negative'
    return 'Neutral'

限制

需要安裝 Excel
macOS 上部分功能支援有限
不適合伺服器端處理
VBA 功能需要信任設定
效能因 Excel 版本而異

安裝

pip install xlwings

# 增益集功能
xlwings addin install

資源

您可能也喜歡

meeting-minutes

36Kproductivity

為內部會議產生簡潔、可執行的會議記錄。包含中繼資料、與會者、議程、決策、行動項目（負責人+截止日期）以及後續步驟。

github

獲取

revops

34Kproductivity

當使用者需要營收營運、潛在客戶生命週期管理或行銷轉銷售交接流程的協助時使用。也適用於使用者提及「RevOps」、「營收營運」、「潛在客戶評分」、「潛在客戶路由」、「MQL」、「SQL」、「管道階段」、「交易桌」、「CRM 自動化」、「行銷轉銷售交接」、「資料衛生」、「潛在客戶無法到達銷售端」、「管道管理」、「潛在客戶資格判定」或「何時應將行銷轉交給銷售」等詞彙。用於任何涉及連接行銷與營收的系統與流程。關於陌生開發郵件，請參閱 cold-email。關於電子郵件滴灌式行銷活動，請參閱 emails。關於定價決策，請參閱 pricing。

coreyhaines31

獲取

dbs-good-question

8.1Kproductivity

dontbesilent 好问题生成器。把模糊问题改写成 Agent 可推理、可批评、可验证的问题说明书，并判断它能被自动化解决到什么程度。触发方式：/dbs-good-question、/好问题、/问题说明书、/Agent可解性、「这个问题能不能自动化解决」「帮我把问题说清楚」 Turn fuzzy problems into agent-solvable problem briefs and evaluate automation readiness. Trigger: /dbs-good-question, "clarify this problem", "can an agent solve this"

dontbesilent2025

獲取

繼續探索

瀏覽 productivity 熱門 Skills 最近更新