更多內容，請訪問我的 個人部落格。
前言
用 Python 處理Excel表格的幾個常規庫：
這些庫都不如 OpenPyXL 強大，OpenPyXL 即可以讀也可以寫 Excel 2010+ 的 xlsx xlsm xltx xltm 檔案。不過， OpenPyXL 庫也是比較吃記憶體的，大約是原始檔案的5倍左右。例如，一個50M大小的Excel檔案，需要2.5G大小的記憶體執行。關於以上幾個庫的效能比對，請移步 OpenPyXL效能測試。
安裝
在終端中輸入命令，如下：
pip install openpyxl
複製程式碼openpyxl 在儲存Excel表格時候會丟失原檔案的圖片和圖表。如果要操作圖片，則需要 pillow 庫，安裝如下：
pip install pillow
複製程式碼由於最近軍運會要在天朝舉行，因此必須翻-牆才能安裝。
使用方法
新建Excel表格
新建Excel表格，預設有一個名為 Sheet 的表格，如下：
from openpyxl import Workbook
wb = Workbook() #建立檔案物件
ws = wb.active #獲取預設sheet
wb.save("sample.xlsx")
複製程式碼開啟已有的Excel表格
對已有的Excel表格進行操作，如下：
from openpyxl import Workbook, load_workbook
wb = load_workbook('sample.xlsx')
wb.save("sample.xlsx")
複製程式碼新建/獲取Sheet表格
使用 Workbook.create_sheet() 方法新建Sheet表格。第一個引數是sheet名稱，若不填，則預設以 Sheet1 Sheet2 Sheet3 ...方式命名；第二個引數是插入Sheet表格的位置，以 0 為第一個位置，若不填，則置於最後。如下：
ws1 = wb.create_sheet("Mysheet") #預設在最後插入
ws2 = wb.create_sheet("Mysheet", 0) #在第一個位置插入
複製程式碼也可以後期隨時修改sheet的名字，如下：
ws.title = "New Title"
複製程式碼修改sheet標籤顏色，如下：
ws.sheet_properties.tabColor = "1072BA"
複製程式碼若知道sheet的名字，可以用如下方式獲取sheet ：
ws = wb.get_sheet_by_name("New Title")
ws = wb["New Title"]
複製程式碼也可獲取全部sheet的名字，遍歷sheet名字，如下：
sheets = wb.sheetnames
for sheet in sheets:
print(sheets)
for sheet in wb:
print sheet.title
['Sheet1', 'New Title', 'Sheet2']
複製程式碼複製Sheet表格
僅能複製 單元格的值 樣式 超連結 註釋塊 等，而 圖片 和 表格 等是無法複製的，如下：
source = wb.active
target = wb.copy_worksheet(source)
複製程式碼操作單元格
由 worksheet 獲取單元格，或直接給單元格賦值，如下：
cell = ws['A4'] #獲取第4行第A列的單元格
ws['A4'] = 4 #給第4行第A列的單元格賦值為4
ws.cell(row=4, column=2, value=10) #給第4行第2列的單元格賦值為10
ws.cell(4, 2, 10) #同上
複製程式碼獲取區域內的單元格，如下：
cell_range = ws['A1':'C2'] #獲取A1-C2內的區域
colC = ws['C'] #獲取第C列
col_range = ws['C:D'] #獲取第C-D列
row10 = ws[10] #獲取第10列
row_range = ws[5:10] #獲取第5-10列
複製程式碼一行行的獲取資料，如下：
>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
... for cell in row:
... print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>
複製程式碼一列列的獲取資料，如下：
>>> for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
... for cell in col:
... print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.A2>
<Cell Sheet1.B1>
<Cell Sheet1.B2>
<Cell Sheet1.C1>
<Cell Sheet1.C2>
複製程式碼因為效能的原因， Worksheet.iter_cols() 方法不能在只讀模式下使用。
獲取所有的列或行，如下：
rows = ws.rows
columns = ws.columns
複製程式碼因為效能的原因， Worksheet.columns 方法不能在只讀模式下使用。
如果只想從worksheet中獲取值，可以使用 Worksheet.values 屬性，如下：
for row in ws.values:
for value in row:
print(value)
複製程式碼Worksheet.iter_rows() 和 Worksheet.iter_cols() 方法都可以新增 values_only 引數來達到僅獲取值的目的，如下：
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
print(row)
複製程式碼如果得到單元格，可以給賦值，如下：
cell.value = 'hello, world'
複製程式碼儲存檔案
使用 Workbook.save() 方法儲存workbook，這個方法會不加提示的覆蓋原檔案，如下：
wb = Workbook()
wb.save('balances.xlsx')
複製程式碼獲取單元格型別
from openpyxl import Workbook, load_workbook
import datetime
wb = load_workbook('sample.xlsx')
ws=wb.active
wb.guess_types = True #開啟獲取單元格型別
ws["A1"]=datetime.datetime(2010, 7, 21)
print ws["A1"].number_format
ws["A2"]="12%"
print ws["A2"].number_format
ws["A3"]= 1.1
print ws["A4"].number_format
ws["A4"]= "中國"
print ws["A5"].number_format
wb.save("sample.xlsx")
# 執行結果：
# yyyy-mm-dd h:mm:ss
# 0%
# General
# General
# 如果是常規，顯示general,如果是數字，顯示'0.00_ '，如果是百分數顯示0%
# 數字需要在Excel中設定數字型別，直接寫入的數字是常規型別
複製程式碼使用公式
from openpyxl import Workbook, load_workbook
wb = load_workbook('sample.xlsx')
ws1=wb.active
ws1["A1"]=1
ws1["A2"]=2
ws1["A3"]=3
ws1["A4"] = "=SUM(1, 1)"
ws1["A5"] = "=SUM(A1:A3)"
print ws1["A4"].value #列印的是公式內容，不是公式計算後的值,程式無法取到計算後的值
print ws1["A5"].value #列印的是公式內容，不是公式計算後的值,程式無法取到計算後的值
wb.save("sample.xlsx")
複製程式碼合併單元格
from openpyxl import Workbook, load_workbook
wb = load_workbook('sample.xlsx')
ws1=wb.active
ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2') #合併後的單元格，指令碼單獨執行拆分操作會報錯，需要重新執行合併操作再拆分
# or equivalently
ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
wb.save("sample.xlsx")
複製程式碼插入一個圖片
需要 pillow 庫，安裝如下：
pip install pillow
複製程式碼from openpyxl import load_workbook
from openpyxl.drawing.image import Image
wb = load_workbook('sample.xlsx')
ws1=wb.active
img = Image('1.png')
ws1.add_image(img, 'A1')
wb.save("sample.xlsx")
複製程式碼隱藏單元格
from openpyxl import load_workbook
wb = load_workbook('sample.xlsx')
ws = wb.active
ws.column_dimensions.group('A', 'D', hidden=True) # 隱藏A到D列
ws.row_dimensions.group(1, 10, hidden=True) # 隱藏1到10行
wb.save("sample.xlsx")
複製程式碼優化模式
在處理非常大的 XLSX 檔案時，openpyxl 的常規模式無法處理這種負載。幸運的是，有兩種模式可以在(幾乎)恆定記憶體消耗的情況下讀寫無限量的資料。
只讀模式
from openpyxl import load_workbook
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['big_data']
for row in ws.rows:
for cell in row:
print(cell.value)
複製程式碼只寫模式
from openpyxl import Workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet()
# now we'll fill it with 100 rows x 200 columns
for irow in range(100):
... ws.append(['%d' % i for i in range(200)])
# save the file
wb.save('new_big_file.xlsx') # doctest: +SKIP
複製程式碼- 與普通工作簿不同，新建立的只寫工作簿不包含任何工作表；必須使用
create_sheet()方法專門建立工作表。 - 在只寫的工作簿中，只能使用
append()新增行。使用cell()或iter_rows()在任意位置寫(或讀)單元格是不可能的。 - 它能夠匯出無限數量的資料(甚至比Excel實際能夠處理的更多)，同時將記憶體使用量保持在10Mb以下。
插入/刪除行/列，移動區域單元格
插入行/列
在第7行之上插入一行，如下：
ws.insert_rows(7)
複製程式碼在第7列的左邊插入一列，如下：
ws.insert_cols(7)
複製程式碼刪除行/列
從第6列開始，刪除3列，即刪除6、7、8列，如下：
ws.delete_cols(6, 3)
複製程式碼移動區域單元格
將 D4:F10 區域向上移動一行向右移動2列，如下：
ws.move_range("D4:F10", rows=-1, cols=2)
複製程式碼如果區域內包含 公式 ，則如下方法可以連同公式一起挪動：
ws.move_range("G4:H10", rows=1, cols=1, translate=True)
複製程式碼使用 Pandas 和 NumPy
詳情請移步 Working with Pandas and NumPy
圖表
圖表由至少一個系列的一個或多個單元格區域資料點組成。更多內容請移步 圖表介紹
註釋
openpyxl 可讀/寫註釋，但格式資訊會被丟失。在 只讀模式 下不支援操作註釋。註釋必須包括 內容 和 作者 。
讀註釋，如下：
comment = ws["A1"].comment
comment.text # 註釋內容
comment.author # 註釋作者
複製程式碼寫註釋，如下：
comment = Comment("Text", "Author")
comment.width = 300 # 設定寬度
comment.height = 50 # 設定高度
ws["A1"].comment = comment
ws["B2"].comment = comment
複製程式碼表格樣式
字型樣式
字型名稱、字型大小、字型顏色、加粗、斜體、縱向對齊方式（有三種：baseline，superscript， subscript）、下劃線、刪除線，如下：
from openpyxl.styles import Font
font = Font(name='Calibri',
size=11,
color='FF000000',
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False)
ws['A1'].font = font
複製程式碼字型顏色可以用 RGB 或 aRGB ，如下：
font = Font(color="FFBB00")
font = Font(color="FFFFBB00")
複製程式碼繼承並重寫樣式，如下：
ft1 = Font(name='Arial', size=14)
ft2 = copy(ft1)
ft2.name = "Tahoma"
複製程式碼填充樣式
詳情請移步 填充樣式
from openpyxl.styles import PatternFill
fill = PatternFill(fill_type=None,
start_color='FFFFFFFF',
end_color='FF000000')
複製程式碼邊框樣式
詳情請移步 邊框樣式
from openpyxl.styles import Border, Side
border = Border(left=Side(border_style=None, color='FF000000'),
right=Side(border_style=None, color='FF000000'),
top=Side(border_style=None, color='FF000000'),
bottom=Side(border_style=None, color='FF000000'),
diagonal=Side(border_style=None, color='FF000000'),
diagonal_direction=0,
outline=Side(border_style=None, color='FF000000'),
vertical=Side(border_style=None, color='FF000000'),
horizontal=Side(border_style=None, color='FF000000')
)
複製程式碼對齊樣式
horizontal 的值有：distributed, justify, center, left, fill, centerContinuous, right, general
vertical 的值有：bottom, distributed, justify, center, top
from openpyxl.styles import Alignment
alignment=Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
複製程式碼保護樣式
鎖定、隱藏
from openpyxl.styles import Protection
protection = Protection(locked=True, hidden=False)
複製程式碼整行或整列應用樣式
col = ws.column_dimensions['A']
col.font = Font(bold=True)
row = ws.row_dimensions[1]
row.font = Font(underline="single")
複製程式碼更改合併的單元格樣式
合併的單元格可以想想成為左上角的那個單元格來操作。
篩選和排序
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
data = [
["Fruit", "Quantity"],
["Kiwi", 3],
["Grape", 15],
["Apple", 3],
["Peach", 3],
["Pomegranate", 3],
["Pear", 3],
["Tangerine", 3],
["Blueberry", 3],
["Mango", 3],
["Watermelon", 3],
["Blackberry", 3],
["Orange", 3],
["Raspberry", 3],
["Banana", 3]
]
for r in data:
ws.append(r)
ws.auto_filter.ref = "A1:B15"
ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"])
ws.auto_filter.add_sort_condition("B2:B15")
wb.save("filtered.xlsx")
複製程式碼生成的Excel表格，有篩選排序的操作，但是沒有實際表現出效果，如下圖：
需要手動點選 重寫應用 才能顯示出效果，如下圖：
密碼保護
該功能僅能提供一個很基礎的密碼保護，沒有進行加密處理，網上普通的破解軟體都可以破解密碼。不過，日常使用還是可以的。
該功能僅可用於新建excel表格，不能用於已存在的excel表格。
workbook工作薄保護
防止檢視隱藏sheet，避免增加、移動、刪除、隱藏或重新命名sheet等操作，可以保護workbook的結構，如下：
wb.security.workbookPassword = '...'
wb.security.lockStructure = True
複製程式碼worksheet保護
worksheet保護不需要密碼，如下：
ws = wb.active
wb.protection.sheet = True
複製程式碼openpyxl包檔案
更多詳情請移步 openpyxl package