Python系列：利用 xlsxwriter 模組輕鬆製作excel

Background photo by Rubaitul Azad on Unsplash

excel 是製作工作報表的好幫手，比起手動新增資料，若能用 python 自動生成 excel 檔並填入內容，可節省時間增加辦公效率。今天將介紹一款可客製化生成 excel 的 python 套件 xlsxwriter，包含常用的函數功能，並以實例呈現參數的使用結果。

下載與引入套件

若在 Anaconda 環境下使用python或jupytor notebook， xlsxwriter 屬於內建套件，可直截引入；若在 miniconda 等其他環境，需要 conda / pip install 下載後引入

1

import xlsxwriter

生成 excel 文件

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

開啟新.xlsx檔並注意最後要關閉工作表

1
2


workbook = xlsxwriter.Workbook('example.xlsx')
workbook.close()

注意:如果使用 with 方式開啟可以不用加上close()來關閉工作表

1
2


with xlsxwriter.Workbook('example.xlsx') as workbook:
    ...

worksheet 建立工作表

透過 workbook.add_worksheet() 實體化工作表，工作表名稱可自行更改

1
2


worksheet = workbook.add_worksheet() # default命名為sheet1 或 工作表1
worksheet = workbook.add_worksheet('Data') # 開啟名為Data的工作表

注意: xlsxWriter 支援處理的工作表邊界值為 1,048,576 rows x 16,384 columns

輸入內容

透過 worksheet.wtire() 在指定位置寫入內容 excel 的格線就跟棋盤一樣，可以用列/欄編號，來指定棋子放置的地方，如在A1填上 ‘apple’ 文字

xlsxwriter 接受以下兩種指示方式:

使用索引 .write(row_index, col_index, args) :注意工作表跟 python list 一樣使用 0-index，因此第一列/欄的 index = 0
使用標記 .write('row-col-notation', args) : 例如第一列第一欄等價於 A1，第二列第三欄等價於 C2

實例: 當我想將資料填入第一列(row 1)的第一欄(col 1)，以下兩種寫法都會在相同位置填入字串:

1
2


worksheet.write(0, 0, 'hello')
worksheet.write('A1', 'hello')

指定輸入類型

為防止輸入內容被自動轉換型別，如 2/29 的字串被 excel 當成日期轉成 2月29日，可以用.write_${type}()函數指定寫入類型，常用的有:

1
2
3
4
5
6
7
8


worksheet.write_string()
worksheet.write_number()
worksheet.write_formula('A1', '=SUM(B1:B4)')
worksheet.write_boolean(0, 0, False)
worksheet.write_blank(0, 0, None)

worksheet.write_datetime('B2', date_time)
date_time = datetime.datetime.strptime('2013-01-23', '%Y-%m-%d')

整列/整欄輸入 (搭配迴圈)

當你想將list, dict 內的資料寫入同一列/欄內，可以用 .wirte_row() 和 .write_column() 兩函數，以下舉個例子:

1
2
3
4


# 如果想將 python list 寫入 excel A 欄位
input_list = ['a', 'b', 'c', 'd']
worksheet.write_column('A1', input_list)
worksheet.write_column(0, 0, input_list)

結果輸出如下:

worksheet 樣式設定

整列/整欄設定高度/寬度

透過 .set_row() 和 .set_column() 設定格子大小，常會搭配樣式設定 注意.set_row() 以設定一列的高度為限，而.set_column() 要指定範圍，如 ‘B:D’ 或 1, 3 等

1
2


worksheet.set_row(1, 20)
worksheet.set_column('D:D', 20)

隨內容最適化格子大小

使用worksheet.autofit()，讓最長的內容寬度當作欄位寬度，減少文字遮蓋

自動增加篩選

透過 worksheet.autofilter(first_row, first_col, last_row, last_col) 添加篩選符號，須設定作用範圍

1
2
3
4
5


worksheet.write(0, 0, 'hello')

## 以下兩者等價
worksheet.autofilter(0,0,1,1)
worksheet.autofilter('A1:B2')

結果如下圖所示，可以看見篩選符號(三角形)自動出現在工作表上

固定某列/欄

為了增加比對方便性，透過.freeze_panes() 在滑動工作表時，讓指定欄位固定不消失

1

worksheet.freeze_panes(1,1) ## 將第一列/第一欄固定不動

縮放比例

worksheet.set_zoom() 設定工作表開啟時，初始的縮放大小(default 100%)

1

worksheet.set_zoom(150) ## 開啟大小放大為150%

format 客製化設定

透過 workbook.add_format() 增添客製化，樣式需用中括號 {} 包起並以 key: value 形式撰寫:

1

a_format = workbook.add_format({'bold': True, 'font_color': 'red'})

定義客製化樣式的方式有兩種:

變數和樣式一起定義cell_format = workbook.add_format({'font_color': 'red'})
先定義變數再賦予樣式

1
2
3


cell_format = workbook.add_format()
cell_format.set_font_color('red')
cell_format.set_bold(True)

樣式種類可參考官方網站樣式有分六大類Font, Number, Protection, Alignment, Pattern, and Border

傳入工作表

傳入工作表的方式有以下兩種

在 worksheet.write() 寫入同時加上樣式
用 worksheet.set_row() 和 worksheet.set_column() 為整列/欄加上樣式

實際例子:

1
2
3
4
5
6


## 在 A1 寫入 'hello' 並設定文字顏色為紅色
a1_format = workbook.add_format({'font_color': 'red'})
worksheet.write(0, 0, 'hello', a1_format)
## 將 B1 整行的背景顏色設為黃色，寬度調整為11
bcol_format = workbook.add_format({'bg_color': 'yellow'})
worksheet.set_column('B:B', 11, bcol_format)

生成結果入下圖所示: 可以看到注意B欄的寬度調整為 11，和 default 8.43 相比(A, C, D欄) 變得較寬

chart 生成圖表

透過 workbook.add_chart() 生成圖表後，使用 worksheet.insert_chart() 插入

個人喜歡將資料整理好並使用R ggplot 做視覺化，所以省略 xlsxwriter 生成圖表的函數介紹

參考資料

https://xlsxwriter.readthedocs.io/worksheet.html

https://xlsxwriter.readthedocs.io/format.html