# <font face='consolas' size=5 color=#000080><center>**Excel檔案讀寫練習程式**</center></font>
難度：★★進階
台北市文山社大2017暑期Python入門課，老師教了openpyxl這個好用的library，功能是讀寫Excel檔案。限於時間，授課內容點到即止，不過對簡單的操作已夠用。課後我再研究好一陣子，以下是心得和測試成果。
:::success
我的習慣是文章和程式源碼post上後，會不定期檢查修改，務求文字精練準確，內容完備。本文亦不例外，內容將不斷更新。
:::
第一步，欲使用openpyxl，得先安裝這個library。方法和安裝其他Python的libraries一樣，用pip就是：
>pip install openpyxl
此外，在程式中也要import此library的相關模組。
:::info
提示：openpyxl讀寫的Excel檔，是Microsoft Office 2007以後副檔名為xlsx格式的檔案，並非早期的xls。
:::
---
## 讀取檔案內容(含格式)
讀取Excel檔案，社大課程只教讀取檔案的內容(資料)，一般網站的教學也是這樣。但單單讀取資料並不能滿足我的求知欲。特地花些時間研究測試，找出在讀取內容之外，也<font color=red>可以讀取大部分**格式(style)**</font>的方法。
:::info
* 這裡的「格式」是指工作表名稱標籤的顏色以及儲存格的各項格式，如數值、對齊方式、字型、外框、保護等。
* 說「大部分」而非「全部」，是由於部分格式例如原儲存格的長寬等未能讀取。努力中...
:::
以下是欲讀取的Excel檔案擷圖以及<font color=red>**讀取檔案內容/格式**</font>的程式源碼。這個範例Excel檔有四個工作表，為節省篇幅，僅貼第一個工作表的擷圖。程式則讀取所有工作表。
```python=
# -*- coding: utf-8 -*-
"""
Excel(Xlsx)檔案讀取練習。
"""
import sys
from openpyxl import load_workbook
from copy import copy
"""
名 稱：storeXlsxContent()
功 能：將Excel檔案(副檔名xlsx)的資料/格式讀出並儲存於list中。
傳入參數：
str inFile: 欲讀取的檔案檔名(可含路徑)字串。
bool dataOnly: 是否儲存cell的值而非公式。True表存值，False表存公式。預設存公式。
bool isStoreStyle: 是否儲存各工作表及儲存格格式。True表儲存，False不存。預設不存。
傳 回 值：
list wbContent。這個list內容為從檔案讀取到的資料及(如有)其格式，wsContent規格為：
. 每個元素都是tuple，代表一張worksheet「工作表」。
. 每張工作表tuple的元素固定為3個dict's，分別為：
. dict 0: 六個keys。第一為工作表名稱('wsName')，第二個以後分別是'wsSheetFormat',
'wsSheetProperties', 'wsSheetState', 'wsViews'和'wsSortState'，
記錄該工作表整體的屬性、格式、狀態等等。
. dict 1: 每個cell(儲存格)的資料。keys是cell的名稱，如 'A1', 'C3'等，value則是
該cell的資料，即內容。
. dict 2: 每個cell的格式。keys是cell的名稱，如 'A1', 'C3'等，value則是該cell
的格式。格式冗長複雜，我一時間難以消化搞懂。反正還原時copy過去就是。
"""
def storeXlsxContent(inFile, dataOnly=False, isStoreStyle=False):
# . dataOnly是控制「僅儲存各cells的值」或「存其公式」(如有)的flag。
# . True表示cells如有公式，僅存由公式計算出來的值而不存公式本身。False則表示須存公式。
# . openpyxl好像只在load_workbook()函式提供這個選項，所以存值或存公式的是屬於整個
# workbook層次，無法做到不同cells有不同的選擇。如有此需要，請開兩個wb's，一存值
# 一存公式，視需要靈活運用。例如：
# wbValue = load_workbook(inFile, data_only=True)
# wbFomula = load_workbook(inFile, data_only=False)
wb = load_workbook(inFile, data_only=dataOnly)
wbContent = [] # wbContent是將要傳回的list。
# 取得一個list，其內容為工作表名稱字串。
wsNames = wb.get_sheet_names()
for wsName in wsNames: # wsNames是個list。
# 利用工作表名稱(wsName)來載入實際的工作表(ws)。
ws = wb.get_sheet_by_name(wsName)
if isStoreStyle:
sheetFormat = ws.sheet_format
sheetProperties = ws.sheet_properties
sheetState = ws.sheet_state
views = ws.views
sortState = ws.sort_state
else:
sheetFormat = None
sheetProperties = None
sheetState = None
views = None
sortState = None
# . wbContent這個list的規格是：每一個元素都是tuple，代表一張工作表。
# 每張工作表tuple的元素固定為3個dict's。
# . 按照以上規格，在list中先append一個tuple，再在tuple內放3個dict's。目前
# 僅在第一個dict(dict 0)存入資料(工作表的整體資訊)，其餘兩個暫時空置。
wbContent.append(({'wsName':wsName,
'wsSheetFormat':sheetFormat,
'wsSheetProperties':sheetProperties,
'wsSheetState':sheetState,
'wsViews':views,
'wsSortState':sortState}, {}, {}))
wsID = len(wbContent) - 1 # 工作表編號(從0起)。
# 剖析這個工作表。
for row in ws.rows: # 表內逐列處理。
for cell in row: # 列內逐cell處理。
# 檢查cell的內容，有資料(非None)才存入wbContent的dict 1中。
if cell.value != None:
wbContent[wsID][1].update({(cell.column + str(cell.row)):
cell.value})
# isStoreStyle為True(即「要讀取並儲存格式」)時，再檢查該儲存格實際上
# 有無設定「格式」(style)。有設定格式，就存入wbContent的dict 2中。
if isStoreStyle and cell.has_style:
wbContent[wsID][2].update({(cell.column + str(cell.row)):
getStyle(cell)})
return wbContent
"""
名 稱：getStyle()
功 能：取得cell儲存格的格式。
傳入參數：
cell: 儲存格，型態為 <class 'openpyxl.cell.cell.Cell'>。
bool font=True: 是否擷取 font。
bool border=True: 是否擷取 border。
bool fill=True: 是否擷取 fill。
bool number_format=True: 是否擷取 number_format=True。
bool protection=True: 是否擷取 protection。
bool alignment=True 是否擷取 alignment。
傳 回 值：dict style。keys為'font', 'border'...等儲存格格式。
"""
def getStyle(cell, font=True, border=True,
fill=True, number_format=True,
protection=True, alignment=True):
style = {}
if font:
style['font'] = cell.font
if border:
style['border'] = cell.border
if fill:
style['fill'] = cell.fill
if number_format:
style['number_format'] = cell.number_format
if protection:
style['protection'] = cell.protection
if alignment:
style['alignment'] = cell.alignment
return style
# 請先確認在下列路徑有此xlsx檔案，或自行修改路徑和檔名。
content1 = storeXlsxContent('D:\\Python\\粵語長片.xlsx', isStoreStyle=True)
```
---
## 寫入檔案(含格式)
成功讀出內容及格式後，該測試寫入到另一個Excel檔案了。以下是寫入檔案的程式源碼。這個程式完全沒有修改原Excel檔的內容和格式，是「保持原貎」重建。當然這只是練習。如果真要原封不動還原，乾脆用拷貝檔案就好，程式源碼大概只需一行，既快又保證完全一樣。實務上很可能會做些改變，也許更改工作表名稱，或者修改、新增、刪除某些儲存格的內容，又或者只寫入部分內容。修改內容的程式碼我放在後面再介紹。
另外，我建議如果要修改content list，最好改「資料」就好，list中的「格式」很複雜，改錯了怕會當機。
:::info
以下的code snippet實際上是接續上面的程式碼。
:::
```python=+
"""
Excel(Xlsx)檔案寫入練習。
"""
"""
名 稱：WriteContent2Xlsx()
功 能：將之前用storeXlsxContent()函式讀取某xlsx檔而產生，或自行製造的來源list
(內有資料/格式)寫入到指定的xlsx檔。
傳入參數：
str outFile: 欲寫入的檔名(可含路徑)字串。
list wbContent: 存放Excel資料/格式的list，就是寫入的資料來源。
bool isRestoreStyle:
. 存檔時是否還原工作表及各儲存格的格式。True要還原，False不用還原。
. 如果選擇還原，wbContent當初必須有儲存格式。如當初未存格式，當然無法還原，不過程式
不會當機，目前版本也不產生警告訊息。
傳 回 值：無。
"""
def WriteContent2Xlsx(outFile, wbContent, isRestoreStyle=False):
from openpyxl import Workbook
from copy import copy
wb = Workbook()
wb.remove_sheet(wb.active) # 先刪除預設的那個sheet(因為要用自己的)。
# 第一層：restore工作表的整體資訊。
for wsID in range(len(wbContent)):
# 產生工作表。名稱不用預設，而使用在list上記錄的工作表名。
ws = wb.create_sheet(title=wbContent[wsID][0]['wsName'])
if isRestoreStyle:
# list內tuple的dict 0有'wsViews'這個key，而且其value不是None，
# 才要restore回去。實際上用copy()來restore。以下幾個if's同理。
if 'wsViews' in wbContent[wsID][0] \
and wbContent[wsID][0]['wsViews'] != None:
ws.views = copy(wbContent[wsID][0]['wsViews'])
if 'wsSheetProperties' in wbContent[wsID][0] \
and wbContent[wsID][0]['wsSheetProperties'] != None:
ws.sheet_properties = copy(wbContent[wsID][0]['wsSheetProperties'])
if 'wsSheetFormat' in wbContent[wsID][0] \
and wbContent[wsID][0]['wsSheetFormat'] != None:
ws.sheet_format = copy(wbContent[wsID][0]['wsSheetFormat'])
if 'wsSheetState' in wbContent[wsID][0] \
and wbContent[wsID][0]['wsSheetState'] != None:
ws.sheet_state = copy(wbContent[wsID][0]['wsSheetState'])
if 'wsSortState' in wbContent[wsID][0] \
and wbContent[wsID][0]['wsSortState'] != None:
ws.sort_state = copy(wbContent[wsID][0]['wsSortState'])
# 第二層：restore工作表中的cells。
# restore data。先檢查這個工作表tuple是否最少有2個 dict's，確定OK後才
# restore資料。檢查的原因是怕自行製造的wbContent可能沒有dict 1 和 dict 2。
if len(wbContent[wsID]) >= 2:
for key, value in wbContent[wsID][1].items():
ws[key] = value
# restore style。同樣道理，先檢查一下是否有dict 2。
if isRestoreStyle and len(wbContent[wsID]) >= 3:
for key, style in wbContent[wsID][2].items():
restoreCellStyle(ws[key], style)
wb.save(outFile) # 存檔。
"""
名 稱：restoreCellStyle()
功 能：還原儲存格的格式。
傳入參數：
cell: 儲存格，型態為 <class 'openpyxl.cell.cell.Cell'>。
dict style: 儲存格格式。
傳 回 值：無。
"""
def restoreCellStyle(cell, style):
if 'font' in style and style['font'] != None:
cell.font = copy(style['font'])
if 'border' in style and style['border'] != None:
cell.border = copy(style['border'])
if 'fill' in style and style['fill'] != None:
cell.fill = copy(style['fill'])
if 'number_format' in style and style['number_format'] != None:
cell.number_format = copy(style['number_format'])
if 'protection' in style and style['protection'] != None:
cell.protection = copy(style['protection'])
if 'alignment' in style and style['alignment'] != None:
cell.alignment = copy(style['alignment'])
# 這裡的content1就是前面storeXlsxContent()傳回，存放資料/格式的那個list。
WriteContent2Xlsx(r'D:\Python\New粵語長片.xlsx', content1, isRestoreStyle=True)
```
寫入的Excel檔的第一個工作表擷圖如下，內容及格式都和原檔相同：
其實要使用WriteContent2Xlsx()函式寫入檔案，不一定要先從某個Excel檔讀取內容。只要自行製造一個符合規格的wsContent list就行。以下是示範的code snippet。這段code只寫入資料，格式過於複雜，暫不考慮。
```python=
content = [({'wsName':'First Sheet'},{'A1':23.4,'B1':56.9,'C1':'=SUM(A1:B1)'}),
({'wsName':'第二工作表'},{'A1':'台北市','B2':'文山','C3':'社大'}, {})]
WriteContent2Xlsx('D:\\Python\\MyTest.xlsx', content, isRestoreStyle=False)
```
以上code snippet造了一個叫做content的list，放入兩個tuple's，代表兩個工作表。
第一個工作表tuple放兩個dict's，第一個dict定義工作表名稱，第二個記錄欲寫入的儲存格名稱及內容。因不寫入格式，可以不必放置第三個dict：
>({'wsName': 'First Sheet'}, {'A1': 23.4, 'B1': 56.9, 'C1': '=SUM(A1:B1)'})
第二個工作表tuple示範用三個dict's。因不寫入格式，第三個dict其實是家徒二壁({})，空空如也，和第一個工作表省略第三個dict的作法效果相同：
>({'wsName': '第二工作表'}, {'A1': '台北市', 'B2': '文山', 'C3': '社大'}, {})
製造出來的MyTest.xlsx長這樣：
---
## 修改儲存格內容及格式
以上介紹了讀取檔案內容/格式及將之寫入其他檔案。但如果只是想修改一下檔案的部分內容/格式，又當如何處理呢？
openpyxl並沒有提供直接修改檔案的功能。要修改，採用的也是上面的讀檔及寫檔程序，只不過讀和寫同一個檔案吧了。
以下是code snippet：
```python=
# -*- coding: utf-8 -*-
from openpyxl import load_workbook
file = 'd:\\Python\\MyTest.xlsx'
wb = load_workbook(file)
c = wb['第二工作表']['B2']
c.value = '士林'
wb.save(file)
```
修改前的「第二工作表」內容是：
修改後：
以下code snippet修改儲存格的格式。可以修改現有檔案，也可以在寫入資料到新檔時一道設定格式：
```python=
# -*- coding: utf-8 -*-
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.styles import Alignment
file = 'd:\\Python\\MyTest.xlsx'
wb = load_workbook(file)
# 字型、顏色等。
fontCity = Font(name='華康龍門石碑', size=20, bold=True, italic=False,
vertAlign=None, underline='double', strike=False,
color='006400')
# 對齊方式。
alignmentCity = Alignment(horizontal='center', vertical='center')
fontDist = Font(name='文鼎特明', size=16, bold=False, italic=True,
vertAlign='baseline', underline='none', strike=False,
color='8A2BE2')
alignmentDist = Alignment(horizontal='left', vertical='top')
city = wb['第二工作表']['A1']
city.font = fontCity
city.alignment = alignmentCity
dist = wb['第二工作表']['B2']
dist.font = fontDist
dist.alignment = alignmentDist
wb.save(file)
```
改變儲存格字型、顏色、對齊方式之後工作表長相如下：
暫時到此為止。以後有新料再續。
###### tags: `Excel` `style` `格式`
Sign in with Wallet
Connect another wallet