Python 玩轉 Excel

在前面抓取高考分數線的文章中，我們用到了 openpyxl 模塊來存儲數據到 Excel，今天帶大家學習一下該模塊的詳細使用。

根據官方文檔，openpyxl 是一個用來處理 xlsx/xlsm/xltx/xltm 格式 Excel 文件的 Python 代碼庫，同時支持 Pandas 和 NumPy 等包，能夠繪制圖表，并且同樣支持格式控制等，詳細文檔可以參考： https://openpyxl.readthedocs.io/。

openpyxl 用起來非常簡單，對照文檔就可以解決一些基本需求，比如常見的都寫操作。

現在還有很多人在用 Excel 2003 版本，即 xls 格式，那么 xls 和 xlsx 有什么區別呢？

xls 是一個特有的二進制格式，其核心結構是復合文檔類型的結構，而 xlsx 的核心結構是 XML 類型的結構，采用的是基于 XML 的壓縮方式，使其占用的空間更小。xlsx 中最后一個 x 的意義就在于此。

1 基本概念

在 openpyxl 中，主要用到三個概念：Workbook，Sheet，Cell：

• Workbook：就是一個 excel 工作簿，其中包含多個 sheet；
• Sheet：工作簿中的一張表頁；
• Cell：就是簡單的一個單元格，用來存儲數據對象；

openpyxl 的主要操作就是圍繞著這三個概念進行的，無怪乎：打開 Workbook，定位 Sheet，操作 Cell。下面就分別介紹 openpyxl 幾個常見的方法。

2 安裝

openpyxl 的安裝很簡單，使用 pip 直接安裝即可。

pip install openpyxl

3 基本操作

提前新建一個測試 Excel：

• 導入模塊

>>> import openpyxl

3.1 Workbook 相關

• 讀取已存在的 xlsx

>>> wb = openpyxl.load_workbook("test.xlsx")

openpyxl.load_workbook() 函數接受文件名，返回一個 Workbook 數據類型的值。這個 Workbook 對象代表這個 Excel 文件，有點類似 File 對象代表一個打開的文本文件。

• 以只讀模式讀取

>>> wb = openpyxl.load_workbook("test.xlsx", read_only=True)

• 保存 Workbook

在對 Workbook 進行了相關操作后，可以調用 save(filename) 方法進行保存。

另外，在只讀模式下保存時，會報 Workbook is read-only 異常。

>>> wb.save('test.xlsx')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.5/dist-packages/openpyxl/workbook/workbook.py", line 363, in save
    raise TypeError("""Workbook is read-only""")
TypeError: Workbook is read-only

3.2 Sheet 相關

• 獲取 Workbook 中的 sheet 列表

返回一個 sheet 的 list。

>>> sheet = wb.worksheets

• 獲取 sheet 頁的名稱列表

>>> wb.sheetnames
['各專業歷年錄取分數線', '測試頁']

• 讀取 sheet 頁

# 根據名稱讀取
>>> sheet = wb['測試頁']

# 通過索引 index 讀取
>>> sheet = wb.worksheets[1]

• 獲取當前正在使用的 sheet 頁

>>> sheet =  wb.active

• sheet 頁屬性

>>> sheet.title
'測試頁'

# 最大列數
>>> sheet.max_column
4

# 最大行數
>>> sheet.max_row
13

• 新建 sheet 頁

>>> wb.create_sheet('test2')
<Worksheet "test2">
>>> wb.sheetnames
['各專業歷年錄取分數線', '測試頁', 'test2']

# 在指定索引處新建
>>> sheet = wb.create_sheet('test2',1)
>>> wb.sheetnames
['各專業歷年錄取分數線', 'test21', 'test2', '測試頁']

若 sheet 頁重名，會自動進行重命名。

• 修改 sheet 頁名稱

>>> sheet = wb['test2']
>>> sheet.title = 'test3'
>>> wb.sheetnames
['各專業歷年錄取分數線', '測試頁', 'test3']

• 刪除 sheet 頁

要先獲取到 sheet 頁才能刪除，不能直接用 sheet 頁的名稱刪除

>>> sheet = wb['test3']
>>> wb.remove(sheet)
>>> wb.sheetnames
['各專業歷年錄取分數線', '測試頁']

# 也可以使用 del 進行刪除
>>> del wb['test2']

3.3 行和列

• 獲取指定行/列

# 獲取第 1 行
>>> sheet[1]
(<Cell '測試頁'.A1>, <Cell '測試頁'.B1>, <Cell '測試頁'.C1>, <Cell '測試頁'.D1>)

# 獲取第 1 列
>>> sheet['A']
(<Cell '測試頁'.A1>, <Cell '測試頁'.A2>, <Cell '測試頁'.A3>, <Cell '測試頁'.A4>, <Cel
l '測試頁'.A5>, <Cell '測試頁'.A6>, <Cell '測試頁'.A7>, <Cell '測試頁'.A8>, <Cell '測
試頁'.A9>, <Cell '測試頁'.A10>, <Cell '測試頁'.A11>, <Cell '測試頁'.A12>, <Cell '測試
頁'.A13>)

• 對行/列切片獲取

>>> sheet[2:3]
((<Cell '測試頁'.A2>, <Cell '測試頁'.B2>, <Cell '測試頁'.C2>, <Cell '測試頁'.D2>), (<
Cell '測試頁'.A3>, <Cell '測試頁'.B3>, <Cell '測試頁'.C3>, <Cell '測試頁'.D3>))
>>> sheet['A:B']
((<Cell '測試頁'.A1>, <Cell '測試頁'.A2>, <Cell '測試頁'.A3>, <Cell '測試頁'.A4>, <Ce
ll '測試頁'.A5>, <Cell '測試頁'.A6>, <Cell '測試頁'.A7>, <Cell '測試頁'.A8>, <Cell '
測試頁'.A9>, <Cell '測試頁'.A10>, <Cell '測試頁'.A11>, <Cell '測試頁'.A12>, <Cell '測
試頁'.A13>), (<Cell '測試頁'.B1>, <Cell '測試頁'.B2>, <Cell '測試頁'.B3>, <Cell '測試
頁'.B4>, <Cell '測試頁'.B5>, <Cell '測試頁'.B6>, <Cell '測試頁'.B7>, <Cell '測試頁'.B
8>, <Cell '測試頁'.B9>, <Cell '測試頁'.B10>, <Cell '測試頁'.B11>, <Cell '測試頁'.B12>
, <Cell '測試頁'.B13>))

• 獲取所有行/列

返回的是一個 Generator 對象，它包含該區域中的 Cell 對象。里面是每一行（列）的數據，每一行（列）又由一個 tuple 包裹。

>>> rows = sheet.rows
>>> rows
<generator object Worksheet._cells_by_row at 0x7f778a7978e0>

>>> columns = sheet.columns

>>> for row in sheet.rows:
...     for cell in row:
...         print(cell.value)

因為 sheet.rows 是生成器類型，不能直接使用索引，需要先轉換成 list 之后才行，如 list(sheet.rows)2 這樣就獲取到第三行的 tuple 對象。

• 添加一行值

>>> sheet.append(row)
>>> row = [1,2,3,4,5,6]

3.4 Cell 相關

• 讀取 Cell

>>> cell = sheet['B2']
>>> cell = sheet.cell(2,1)

需要注意的是：openpyxl 中 row 和 column 為了和 Excel 中的表達方式一致，并不和編程語言的習慣以 0 表示第一個值，而是 1 開始。

• Cell 屬性

# 所在列
>>> cell.column
'A'

# 所在行
>>> cell.row
2

# 所屬坐標
>>> cell.coordinate
'A2'

# 對應的值
>>> cell.value
'A2'

• 寫入 Cell

# 直接給單元格賦值
>>> cell.value = 'test'
# 這里可以不寫 value？
>>> sheet['A1'] = 'kk'
>>> sheet.cell(1,1).value = 'ff'

• 寫入公式

# 寫入和值
>>> sheet['A14'] = "=SUM(B14:D14)"
>>> sheet['A14'].value
'=SUM(B14:D14)'

# 寫入平均值
>>> sheet['A14'] = "=AVERAGE(B14:D14)"
>>> sheet['A14'].value
'=AVERAGE(B14:D14)'

這里可發現，在讀取的時候，返回的是公式本身 '=AVERAGE(B14:D14)'，而不是計算結果。若要返回計算結果，只有手動打開 test.xlsx 文件，然后點擊保存更改。

• 單元格合并與拆分

>>> sheet.merge_cells('A1:A3')
>>> sheet.merge_cells('B1:D2')

如果這些要合并的單元格都有數據，只會保留左上角的數據，其他則丟棄。

分解類似：

>>> sheet.unmerge_cells('A1:A3')
>>> sheet.unmerge_cells('B1:D2')

• 單元格樣式

from openpyxl.styles import Font, colors, Alignment

# 設置字體: 等線 24 號加粗斜體，字體顏色紅色
bold_itatic_24_font = Font(name="等線", size=24, italic=True, color=colors.RED, bold=True)
sheet["B1"].font = bold_itatic_24_font

# 對齊方式: B1 中的數據垂直居中和水平居中
sheet["C1"].alignment = Alignment(horizontal="center", vertical="center")

# 設置行高和列寬
sheet.row_dimensions[2].height = 40
sheet.column_dimensions["C"].width = 30

設置后的效果：

openpyxl 模塊的使用就到這里，完整使用示例可以參考我的上篇：Python 助你填寫高考志愿。

其實還有很多高級用法，但個人覺得用的較少，有興趣的可以參考官網：https://openpyxl.readthedocs.io/en/stable/