Featured image of post Python系列： 利用 openpxyl 操作 excel
Python

Python系列： 利用 openpxyl 操作 excel

Background photo by Rubaitul Azad on Unsplash

過去曾介紹 xlsxwriter 套件將資料寫入輸出成 .xlsx 檔，若想讀取 .xlsx 內容、或想編輯已存在的檔案，推薦另一套件 openpyxl 套件。本文將介紹 python openpyxl 套件的常用函數、基本用法，並帶入範例說明。

下載與引入套件

可選擇用 pip 或 conda install 安裝套件 注意：推薦用 pip 安裝，使用 conda 安裝容易出現 partially initialized module的抱錯

1
2
3
4
5

pip install openpyxl
conda install conda-forge::openpyxl

import openpyxl
from openpyxl import Workbook

開啟 excel 檔案

和 xlsxwriter 一樣， openpyxl 將整份 excel 檔案稱為 Workbook，內部的工作表稱為 worksheet，而每個格子稱為 cell。

openpyxl 分成創建和讀取兩種模式

創建 / 寫入excel

以 Workbook() 創立表格文件，並透過 active 讀取第一份工作表 (active default:0)

若想添加工作表，可用.create_sheet("sheet_name",position)來命名+決定放置順序

可透過 .sheetnames 檢視當前所有工作表名稱，輸出為list

完成操作後以.save('output.xlsx') 把 workbook 輸出成檔案，注意若系統有相同檔名的文件，openpyxl 會直接覆寫掉，此外若檔名結尾非.xlsx等有效格式，可能會造成檔案輸出後無法正常開啟的狀況

實際例子

開啟新workbook以及名為 test 的工作表，並指定在test內的A1寫入’Hi’字串輸出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

import openpyxl
from openpyxl import Workbook

## create new workbook
wb = Workbook()

## create new worksheet other than default(name: Sheet)
wb.create_sheet('test')

## list current worksheet names
print(wb.sheetnames)
## ['Sheet', 'test']

## write string into cell
sheet = wb['test']
sheet['A1'] = 'Hi'

## save(overwrite) as xlsx file
wb.save('test.xlsx')

結果如下圖：

讀取 excel

以 openpyxl.load_workbook() 來讀取特定檔案，若檔案內有多個工作表，可用工作表名稱指定(default為操作第一份工作表)

1
2
3
4
5

wb = openpyxl.load_workbook('my_file.xlsx')
sheet = wb['specific_sheetname']

## 若想直接用第一份 worksheet
sheet = wb.active

load_workbook() 有許多參數:

  • data_only 遇到公式時要印出公式本身(default)還是計算的結果
  • keep_vba 是否保留設定的儲存格事件(Visual Basic elements) 預設為不保留，若選擇保留，也不能透過 openpyxl 編輯事件
  • read-only 以讀取模式開啟，會減少記憶體需求，且不支援讀取某些類型(表格、圖片)
  • rich_text 不否保留預設的樣式設定(default)
  • keep_links 保留超連結設定

操作 cell 內容

指定cell的方式也是以 row + column index (start from 1)或 cell index (如:A1)等方式，

1
2
3

strings = sheet['A1'] 
## the same as
strings = sheet.cell(row=1, column=1)

若想看cell內容要加 .value 來將object轉換成內文print(strings.value)

1
2
3
4
5
6
7

strings = sheet['A1']

print(strings)
## <Cell 'test'.A1>

print(strings.value)
## Amy

當想看特定範圍的格子，可用 iter_rows() 或是 [index1:index2] 來操作，並搭配迴圈觸及到每個cell內容

1
2
3
4
5

for row in worksheet.iter_rows(min_row=1, max_col=3, max_row=2):
   for cell in row:
       print(cell)
## print (1,1), (1,2), (1,3), (2,1), (2,2), and (2,3)
## same as A1, B1, C1, A2, B2, C2

1
2
3
4
5

multi_cell = worksheet['B10':'C11']
#print(multi_cell)
for row in multi_cell:
    print(row[0].value, row[1].value) 
## print B10, C10 and then B11, C11

實際例子

有份 test.xlsx 紀錄營隊的資訊，今天想要讀取excel並將隊員資訊印出

test.xlsx 的內容

1
2
3
4
5
6
7
8

wb = openpyxl.load_workbook('test.xlsx')

sheet = wb.active

for row in sheet['A1:C13']:  
## == sheet.iter_rows(min_col=1, min_row=1):
   for cell in row:
      print(cell.value)

添加樣式

引入相關函數

1

from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font

Font 調整文字樣式

1
2
3
4
5
6
7
8

font = Font(name='Calibri', ## 字體名稱
                size=11,    ## 大小
                bold=False, ## 粗體
                italic=False, ## 斜體
                vertAlign=None, ## 垂直置中
                underline='none', ## 底線
                strike=False,      
                color='FF000000')  ## 顏色

PatternFill 調整cell格子樣式

1
2
3

fill = PatternFill(fill_type=None,
                start_color='FFFFFFFF',
                end_color='FF000000')

Border 調整邊框邊界樣式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

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')
               )

Alignment 調整格子內文對齊方式

1
2
3
4
5
6

alignment=Alignment(horizontal='general',
                    vertical='bottom',
                    text_rotation=0,
                    wrap_text=False,
                    shrink_to_fit=False,
                    indent=0)

Protection 容許編輯或保護模式

1
2

protection = Protection(locked=True,
                        hidden=False)

設定好樣式後，使用 .font 套用至cell上

1
2
3
4
5

ft = Font(size = 15, bold = True)
sheet['A1'].font = ft

## 也可以透過 .font 直接調整樣式
sheet['A1'].font.italic = True

若想將樣式套用在整行或整欄，可先用column_dimensions['A','B','C'...] 和 row_dimensions[1,2,3...] 指定索引，接著以 .font 套用樣式

1
2
3
4

ft = Font(size = 15, bold = True)
col = wb.row_dimensions[2]

col.font = ft

References

https://openpyxl.readthedocs.io/en/stable/tutorial.html#playing-with-data https://stringfestanalytics.com/how-to-understand-the-difference-between-the-openpyxl-and-xlsxwriter-python-packages-for-excel/

Python
Licensed under CC BY-NC-SA 4.0
comments powered by Disqus