Python 自動化辦公室:操作Excel 的七個範例

2024.05.29

在日常工作中,Excel作為數據分析和報告製作的利器,幾乎無人不知無人不曉。然而,面對大量重複的資料輸入、整理、分析任務時,手動操作不僅耗時費力,也容易出錯。此時,Python與它的強大庫openpyxl便閃亮登場,助你輕鬆實現Excel自動化辦公室。

Python以其簡潔易學的語法和豐富的資料處理函式庫,能有效率地處理複雜的資料任務,而openpyxl則是專門用來操作Excel檔案的函式庫,讓你不需要開啟Excel,就能完成建立、讀取、編輯等操作。

openpyxl庫簡介與安裝

openpyxl是一個開源的Python函式庫,專注於讀寫Microsoft Excel 2010 xlsx/xlsm/xltx/xltm檔。它支援進階功能如資料驗證、圖表、超連結、註解等,且效能優秀,對大型檔案處理游刃有餘。安裝只需在命令列中輸入以下命令:

pip install openpyxl
  • 1.

範例一:建立新Excel檔案並寫入數據

想在Python裡「無中生有」一個Excel檔? openpyxl幫你實作!下面的程式碼將建立一個名為「my_data.xlsx」的文件,並在其中填入一些資料:

from openpyxl import Workbook

# 创建Workbook对象,相当于一个新的Excel文件
wb = Workbook()

# 获取默认的活跃工作表(Sheet)
ws = wb.active

# 在A1到C3单元格写入数据
for row in range(1, 4):
    for col in 'ABC':
        ws[col+str(row)] = f"Data {row} {col}"

# 保存到硬盘
wb.save("my_data.xlsx")
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.

範例二:讀取現有Excel檔案數據

有了數據,自然要能讀取。這段程式碼將開啟“my_data.xlsx”,讀取其內容並列印出來:

from openpyxl import load_workbook

# 加载已有的Excel文件
wb = load_workbook("my_data.xlsx")

# 获取第一个工作表(索引为0)
ws = wb.worksheets[0]

# 遍历所有单元格并打印内容
for row in ws.iter_rows():
    for cell in row:
        print(cell.value)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.

範例三:修改儲存格內容及樣式

數據錯了?顏色不滿意? openpyxl讓你輕鬆調整:

from openpyxl.styles import Font, Color

# 加载已有的Excel文件
wb = load_workbook("my_data.xlsx")
ws = wb.active

# 修改A1单元格内容为"Updated Data"
ws["A1"] = "Updated Data"

# 设置A1单元格字体为红色,加粗
font = Font(color=Color(rgb='FF0000'), bold=True)
ws["A1"].font = font

# 保存更改
wb.save("my_data.xlsx")
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.

範例四:新增/刪除行與列

資料結構需要調整? openpyxl讓你增刪自如:

# 加载已有的Excel文件
wb = load_workbook("my_data.xlsx")
ws = wb.active

# 在第二行前插入一行
ws.insert_rows(2)

# 删除第三列(C列)
ws.delete_cols(3)

# 保存更改
wb.save("my_data.xlsx")
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.

範例五:合併儲存格與設定條件格式

表格需要更美觀?合併儲存格與條件格式來幫忙:

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

# 加载已有的Excel文件
wb = load_workbook("my_data.xlsx")
ws = wb.active

# 合并A1到C1单元格
ws.merge_cells('A1:C1')

# 创建一个条件格式规则:当单元格值大于10时填充绿色
green_fill = PatternFill(start_color="00FF00", end_color="00FF00", fill_type="solid")
rule = CellIsRule(operator='>', formula=['10'], stopIfTrue=False, fill=green_fill)

# 应用规则到整个工作表
ws.conditional_formatting.add('A1:C5', rule)

# 保存更改
wb.save("my_data.xlsx")
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.

範例六:利用公式計算與資料統計

數據需要計算或統計? openpyxl支援直接寫入公式:

# 加载已有的Excel文件
wb = load_workbook("my_data.xlsx")
ws = wb.active

# 在D1单元格写入求和公式
ws["D1"] = "=SUM(A1:C1)"

# 在D2单元格写入平均值公式
ws["D2"] = "=AVERAGE(A2:C2)"

# 保存更改并计算公式结果
wb.save("my_data.xlsx")
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.

範例七:批次處理多個工作表

面對多工作表的大型文件,openpyxl也能輕鬆應付:

# 加载已有的Excel文件
wb = load_workbook("my_data.xlsx")

# 遍历所有工作表
for sheet in wb.worksheets:
    print(f"Processing sheet: {sheet.title}")

    # 对每个工作表执行相同的操作,如数据清洗、格式化等

# 保存更改
wb.save("my_data.xlsx")
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.

結語:進階學習與應用場景推薦

恭喜你,透過以上7個範例,你已經掌握了openpyxl的基本操作。接下來,你可以嘗試結合pandas函式庫進行更複雜的資料分析,或是利用定時任務來實現自動化報表產生。無論你是處理銷售數據、財務報表,或是科學研究數據,openpyxl都能成為你提升工作效率的得力助手。