使用 Python 操作 Excel 資料表格，可有效實現自動化處理。openpyxl 幾乎涵蓋 Excel 九成以上的操作功能， 且無需安裝 Microsoft Office 即可使用。若再搭配 Pandas，可大幅提升資料處理、分析與批次報表產出的效能。

安裝套件

依如下指令安裝套件。

pip install openpyxl pandas

建立工作簿、工作表

Workbook() 可產生一個新的工作簿 (.xlsx 檔)，且預設會有一個工作表，工作表預設名稱為 “Sheet”。wb.create_sheet() 可產生新的工作表。最後 wb.save() 儲存成一個 .xlsx 檔。

from openpyxl import Workbook

wb = Workbook()

# 預設會有一個工作表
ws1 = wb.active
ws1.title = "首頁"

# 新增工作表
ws2 = wb.create_sheet(title="工作表2")

wb.save("test.xlsx")

開啟工作簿

若工作簿已存在，可以使用 load_workbook 開啟工作簿。

wb = load_workbook(
    "test.xlsx",
    read_only=False,
    data_only=False
)
print(wb.sheetnames)

結果 : 
['首頁', '工作表2']

read_only 為 True 時，只可讀取，不可更改，可加速讀取效能。
data_only 為 True 時，只讀取最後計算的結果。如果為 False，則讀取公式。
wb.sheetnames 則列出所有工作表的名稱。

刪除工作表

可以使用 del 或 wb.remove() 刪除，刪除前請先判斷一下工作表是否存在

from openpyxl.reader.excel import load_workbook

wb=load_workbook("test.xlsx")
if "2016" in wb.sheetnames:
    del wb["2016"]
if "2017" in wb.sheetnames:
    wb.remove(wb["2017"])
wb.save("test.xlsx")

指定儲存格

使用 ws[“儲存格”] 可修改儲存格資料

wb = load_workbook(
    "test.xlsx",
    read_only=False,
    data_only=False
)
ws=wb["首頁"]
ws["A1"]="姓名"
ws["B1"]="帳號"
ws["C1"]="密碼"
ws["D1"]="電話"
ws["e1"]="地址"
wb.save("test.xlsx")

插入欄

使用 ws.insert_cols 插入欄位，第一個參數是要插入的欄位索引，索引由 1 開始，第二個參數是要插入的欄數。

ws=wb["首頁"]
ws.insert_cols(2,3)
ws["B1"]="test"
wb.save("test.xlsx")

插入列

ws.insert_rows() 插入列，第一參數為插入的列索引，索引由 1 開始，第二個參數為列數。

from openpyxl.reader.excel import load_workbook

wb = load_workbook("test.xlsx")
ws=wb["2018"]
for i in range(1, 10):
    ws[f"A{i}"]=i
ws.insert_rows(2,5)
wb.save("test.xlsx")

應用

相關檔案請由 樓層登記表.xlsx 下載，並將 .xlsx 儲存在專案之下

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Border, Side

def noBlod(sheet):
    for row in sheet.iter_rows():
        for cell in row:
            #if cell.font.bold:  # 如果原本是粗體
            cell.font = Font(bold=False)  # 取消粗體
            cell.border = thin_border

thin_border = Border(
    left=Side(style='thin', color='000000'),
    right=Side(style='thin', color='000000'),
    top=Side(style='thin', color='000000'),
    bottom=Side(style='thin', color='000000')
)
file_path = '樓層登記表.xlsx'
sheets = ["搬入", "搬離", "未搬出"]
df = pd.read_excel(file_path, sheet_name="工作表1")
df['搬入'] = df['搬入'].dt.strftime('%Y-%m-%d')
df['搬離'] = df['搬離'].dt.strftime('%Y-%m-%d')
try:
    wb = load_workbook(file_path)
    for sheet in sheets:
        if sheet in wb:
            wb.remove(wb[sheet])
    wb.save(file_path)

    print("===============搬入排序===============")
    dfx=df
    dfx.sort_values(by="搬入")
    with pd.ExcelWriter(file_path, mode='a', engine='openpyxl') as writer:
        dfx.to_excel(writer, sheet_name='搬入', index=False)
    wb=load_workbook(file_path)
    ws=wb["搬入"]
    ws.column_dimensions['A'].width = 12
    ws.column_dimensions['B'].width = 12
    noBlod(ws)
    wb.save(file_path)
    print(dfx)

    print("\n===============搬出排序===============")
    df.sort_values(by="搬離")
    dfx=df.dropna()
    with pd.ExcelWriter('樓層登記表.xlsx', mode='a', engine='openpyxl') as writer:
        dfx.to_excel(writer, sheet_name='搬離', index=False)
    wb=load_workbook(file_path)
    ws=wb["搬離"]
    ws.column_dimensions['A'].width = 12
    ws.column_dimensions['B'].width = 12
    noBlod(ws)
    wb.save(file_path)
    print(dfx)

    print("\n===============未搬出排序===============")
    dfx=df[df["搬離"].isna()]
    dfx.sort_values(by="搬入")
    dfx=dfx.drop(columns=df.columns[0])
    with pd.ExcelWriter('樓層登記表.xlsx', mode='a', engine='openpyxl') as writer:
        dfx.to_excel(writer, sheet_name='未搬出', index=False)
    wb=load_workbook(file_path)
    ws = wb['未搬出']
    ws.column_dimensions['A'].width = 12
    noBlod(ws)
    wb.save(file_path)
    print(dfx)

    input("\n\n已完成，請按任何鍵結束....")
except Exception as e:
    print(e)
    input(f"\n\nerror : {e}，請按任何鍵結束....")

todo