作者：小小明

文章目錄

準備資料
Pandas直接保存資料
Pandas的Styler對表格著色輸出
Pandas使用xlsxwriter引擎保存資料
xlsxwriter按照指定樣式寫出Pandas物件的資料
Pandas使用openpyxl引擎保存資料
openpyxl加載資料模板寫出Pandas物件的資料
相關資料
- python讀寫Excel檔案的那些庫
- Excel單元格自定義格式引數含義
總結

準備資料

import pandas as pd
from datetime import datetime, date
df = pd.DataFrame({'Date and time': [datetime(2015, 1, 1, 11, 30, 55),
                                     datetime(2015, 1, 2, 1, 20, 33),
                                     datetime(2015, 1, 3, 11, 10),
                                     datetime(2015, 1, 4, 16, 45, 35),
                                     datetime(2015, 1, 5, 12, 10, 15)],
                   'Dates only': [date(2015, 2, 1),
                                  date(2015, 2, 2),
                                  date(2015, 2, 3),
                                  date(2015, 2, 4),
                                  date(2015, 2, 5)],
                   'Numbers': [1010, 2020, 3030, 2020, 1515],
                   'Percentage': [.1, .2, .33, .25, .5],
                   })
df['final'] = [f"=C{i}*D{i}" for i in range(2, df.shape[0]+2)]
df

結果：

	Date and time	Dates only	Numbers	Percentage	final
0	2015/1/1 11:30	2015/2/1	1010	0.1	=C2*D2
1	2015/1/2 1:20	2015/2/2	2020	0.2	=C3*D3
2	2015/1/3 11:10	2015/2/3	3030	0.33	=C4*D4
3	2015/1/4 16:45	2015/2/4	2020	0.25	=C5*D5
4	2015/1/5 12:10	2015/2/5	1515	0.5	=C6*D6

Pandas直接保存資料

對于這個pandas物件，如果我們需要將其保存為excel，有那些操作方式呢？
首先，最簡單的，直接保存：

df.to_excel("demo1.xlsx", sheet_name='Sheet1', index=False)

效果如下：

但如果我們想要給這個excel在保存時，同時指定一些特殊的自定義格式又該怎么做呢？
這時就可以使用ExcelWriter進行操作，查看API檔案發現兩個重要引數：

date_format : str, default None

Format string for dates written into Excel files (e.g. ‘YYYY-MM-DD’).
datetime_format : str, default None

Format string for datetime objects written into Excel files.
(e.g. ‘YYYY-MM-DD HH:MM:SS’).

這說明對于日期型別資料，都可以通過這兩個引數指定特定的顯示格式，那么我們采用以下方式才創建ExcelWriter，并保存結果：

writer = pd.ExcelWriter("demo1.xlsx",
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy')
df.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()

可以看到excel保存的結果中，格式已經確實的發生了改變：

Pandas的Styler對表格著色輸出

如果我們想對指定的列的資料設定文字顏色或背景色，可以直接pandas.io.formats.style工具，該工具可以直接對指定列用指定的規則著色：

df_style = df.style.applymap(lambda x: 'color:red', subset=["Date and time"]) \
    .applymap(lambda x: 'color:green', subset=["Dates only"]) \
    .applymap(lambda x: 'background-color:#ADD8E6', subset=["Numbers"]) \
    .background_gradient(cmap="PuBu", low=0, high=0.5, subset=["Percentage"])
df_style

顯示效果：

writer = pd.ExcelWriter("demo_style.xlsx",
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy')
df_style.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()

保存效果：

雖然Pandas的Styler樣式還包括設定顯示格式、條形圖等功能，但寫入到excel卻無效，所以我們只能借助Pandas的Styler實作作色的功能，而且只能對資料著色，不能對表頭作色，

Pandas使用xlsxwriter引擎保存資料

進一步的，我們需要將數值等其他型別的資料也修改一下顯示格式，這時就需要從ExcelWriter拿出其中的workbook進行操作：

writer = pd.ExcelWriter("demo1.xlsx")
workbook = writer.book
workbook

結果：

<xlsxwriter.workbook.Workbook at 0x52fde10>

從回傳的結果可以看到這是一個xlsxwriter物件，說明pandas默認的excel寫出引擎是xlsxwriter，即上面的ExcelWriter創建代碼其實等價于：

pd.ExcelWriter("demo1.xlsx", engine='xlsxwriter')

關于xlsxwriter可以參考官方檔案：https://xlsxwriter.readthedocs.org/

下面的代碼即可給數值列設定特定的格式：

writer = pd.ExcelWriter("demo1.xlsx",
                        engine='xlsxwriter',
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

worksheet.set_column('A:A', 19)
worksheet.set_column('B:B', 17)

format1 = workbook.add_format({'num_format': '#,##0.00'})
format2 = workbook.add_format({'num_format': '0%'})
worksheet.set_column('C:C', 8, format1)
worksheet.set_column('D:D', 11, format2)
worksheet.set_column('E:E', 6, format1)

writer.save()

效果：

關于set_column方法：

worksheet.set_column(‘C:C’, 8, format1)
表示將C列的列寬設定為8個字符，并采用format1的樣式，當然
format1 = workbook.add_format({‘num_format’: ‘#,##0.00’})
就是表示生成一個指定的格式物件，

xlsxwriter按照指定樣式寫出Pandas物件的資料

假如，我現在希望能夠定制excel表頭的樣式，并給資料添加邊框，我翻遍了xlsxwriter的API檔案發現，并沒有一個可以修改指定范圍樣式的API，要修改樣式只能通過set_column修改列，或者通過set_row修改行，這種形式的修改都是針對整行和整列，對于顯示格式還能滿足條件，但對于背景色和邊框之類的樣式就不行了，這點上確實不如openpyxl方便，但xlsxwriter還有個優勢，就是寫出資料時可以直接指定樣式，

下面看看如何直接通過xlsxwriter保存指定樣式的資料吧：

import xlsxwriter

workbook = xlsxwriter.Workbook('demo2.xlsx')
worksheet = workbook.add_worksheet('sheet1')
# 創建列名的樣式
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})
# 從A1單元格開始寫出一行資料，指定樣式為header_format
worksheet.write_row(0, 0,  df.columns, header_format)
# 創建一批樣式物件
format1 = workbook.add_format({'border': 1, 'num_format': 'mmm d yyyy hh:mm:ss'})
format2 = workbook.add_format({'border': 1, 'num_format': 'mmmm dd yyyy'})
format3 = workbook.add_format({'border': 1, 'num_format': '#,##0.00'})
format4 = workbook.add_format({'border': 1, 'num_format': '0%'})

# 從第2行（角標從0開始）開始，分別寫出每列的資料，并指定特定的樣式
worksheet.write_column(1, 0, df.iloc[:, 0], format1)
worksheet.write_column(1, 1, df.iloc[:, 1], format2)
worksheet.write_column(1, 2, df.iloc[:, 2], format3)
worksheet.write_column(1, 3, df.iloc[:, 3], format4)
worksheet.write_column(1, 4, df.iloc[:, 4], format3)

# 設定對應列的列寬，單位是字符長度
worksheet.set_column('A:A', 19)
worksheet.set_column('B:B', 17)
worksheet.set_column('C:C', 8)
worksheet.set_column('D:D', 12)
worksheet.set_column('E:E', 6)

workbook.close()

上面的代碼應該都比較好理解，header_format和formatN是創建的樣式物件，write_row用于按行寫出資料，write_column用于按列寫出資料，set_column則是用于設定整列的列寬和樣式（沒傳入的不設定），

運行結果如下：

Pandas使用openpyxl引擎保存資料

pandas的默認寫出引擎是xlsxwriter，那么是不是可以修改為其他引擎呢？答案是可以，下面我們使用 openpyxl 實作同樣的效果，

關于openpyxl可參考：https://openpyxl.readthedocs.org/

也可以直接參考黃同學撰寫的檔案（中文，相對也比較全面）：https://blog.csdn.net/weixin_41261833/article/details/106028038

writer = pd.ExcelWriter("demo3.xlsx",
                        engine='openpyxl',
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']
workbook

結果：

<openpyxl.workbook.workbook.Workbook at 0x16c6bb70>

從上述列印結果可以看到，從engine指定為’openpyxl’后，workbook已經是 openpyxl 物件了，

與 xlsxwriter 不同的是 openpyxl 只能對逐個單元格設定樣式，而xlsxwriter只能指定行或指定列或寫入資料指定樣式，

下面首先修改表頭的樣式：

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

font = Font(name="微軟雅黑", bold=True)
alignment = Alignment(vertical="top", wrap_text=True)
pattern_fill = PatternFill(fill_type="solid", fgColor="D7E4BC")
side = Side(style="thin")
border = Border(left=side, right=side, top=side, bottom=side)

for cell in itertools.chain(*worksheet["A1:E1"]):
    cell.font = font
    cell.alignment = alignment
    cell.fill = pattern_fill
    cell.border = border

上述代碼引入的了itertools.chain方便迭代出每個單元格，而不用寫多重for回圈，

下面再修改數值列的格式：

for cell in itertools.chain(*worksheet["A2:E6"]):
    cell.border = border
for cell in itertools.chain(*worksheet["C2:C6"], *worksheet["E2:E6"]):
    cell.number_format = '#,##0.00'
for cell in itertools.chain(*worksheet["D2:D6"]):
    cell.number_format = '0%'

最后給各列設定一下列寬：

worksheet.column_dimensions["A"].width = 20
worksheet.column_dimensions["B"].width = 17
worksheet.column_dimensions["C"].width = 10
worksheet.column_dimensions["D"].width = 12
worksheet.column_dimensions["E"].width = 8

最后保存即可：

writer.save()

整體完整代碼：

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

writer = pd.ExcelWriter("demo3.xlsx",
                        engine='openpyxl',
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

font = Font(name="微軟雅黑", bold=True)
alignment = Alignment(vertical="top", wrap_text=True)
pattern_fill = PatternFill(fill_type="solid", fgColor="D7E4BC")
side = Side(style="thin")
border = Border(left=side, right=side, top=side, bottom=side)

for cell in itertools.chain(*worksheet["A1:E1"]):
    cell.font = font
    cell.alignment = alignment
    cell.fill = pattern_fill
    cell.border = border

for cell in itertools.chain(*worksheet["A2:E6"]):
    cell.border = border
for cell in itertools.chain(*worksheet["C2:C6"], *worksheet["E2:E6"]):
    cell.number_format = '#,##0.00'
for cell in itertools.chain(*worksheet["D2:D6"]):
    cell.number_format = '0%'

worksheet.column_dimensions["A"].width = 20
worksheet.column_dimensions["B"].width = 17
worksheet.column_dimensions["C"].width = 10
worksheet.column_dimensions["D"].width = 12
worksheet.column_dimensions["E"].width = 8

writer.save()

最終效果：

從上述代碼可以看到，openpyxl 修改樣式時相對xlsxwriter來說麻煩了太多，特別是修改一個表頭就需要創建5個物件，

openpyxl加載資料模板寫出Pandas物件的資料

雖然 openpyxl 直接寫出資料指定樣式相對xlsxwriter麻煩，但 openpyxl 還有個巨大的優勢就是可以讀取已有的excel檔案，在其基礎上修改，

那我們就完全可以先將模板資料寫入到一個excel，然后加載這個模板檔案進行修改，所以上面那個固定不變的表頭，我們就可以事先創建好：

然后加載模板，再寫入：

（經測驗ExcelWriter無法對已經存在的作業表進行操作，會創建新的作業表，所以這里直接使用openpyxl自己的API）

from openpyxl import load_workbook
workbook = load_workbook('template.xlsx')
worksheet = workbook["Sheet1"]
# 添加資料列，i表示當前的行號，用于后續格式設定
for i, row in enumerate(df.values, 2):
    worksheet.append(row.tolist())

# 批量修改給寫入的資料的單元格范圍加邊框
side = Side(style="thin")
border = Border(left=side, right=side, top=side, bottom=side)
for cell in itertools.chain(*worksheet[f"A2:E{i}"]):
    cell.border = border
    
# 批量給各列設定指定的自定義格式
for cell in itertools.chain(*worksheet[f"A2:A{i}"]):
    cell.number_format = 'mmm d yyyy hh:mm:ss'
for cell in itertools.chain(*worksheet[f"B2:B{i}"]):
    cell.number_format = 'mmmm dd yyyy'
for cell in itertools.chain(*worksheet[f"C2:C{i}"], *worksheet[f"E2:E{i}"]):
    cell.number_format = '#,##0.00'
for cell in itertools.chain(*worksheet[f"D2:D{i}"]):
    cell.number_format = '0%'

workbook.save(filename="demo4.xlsx")

最終效果：

可以明顯的看到openpyxl在加載模板后，可以省掉表頭設定和列寬設定的代碼，

相關資料

python讀寫Excel檔案的那些庫

來源：http://www.python-excel.org/

openpyxl

讀取和寫入EXCEL2010檔案的包(即.xlsx)

檔案：https://openpyxl.readthedocs.org/

xlsxwriter

擁有豐富的特性，支持圖片/表格/圖表/篩選/格式/公式等，功能與openpyxl相似，優點是相比 openpyxl 還支持 VBA 檔案匯入，迷你圖等功能，缺點是不能打開/修改已有檔案，意味著使用 xlsxwriter 需要從零開始，支持EXCEL2010檔案(即.xlsx)
檔案：https://xlsxwriter.readthedocs.org/
GitHub：https://github.com/jmcnamara/XlsxWriter

pyxlsb

專門用于讀取 xlsb格式的excel檔案
GitHub：https://github.com/willtrnr/pyxlsb

pylightxl

用于讀取xlsx 和xlsm格式的excel檔案，或寫入xlsx格式的excel檔案
檔案：https://pylightxl.readthedocs.io/en/latest/
GitHub：https://github.com/PydPiper/pylightxl

xlrd

用于讀取xls格式的excel檔案的庫
檔案：http://xlrd.readthedocs.io/en/latest/
GitHub：https://github.com/python-excel/xlrd

xlwt

用于寫入xls格式的excel檔案的庫
檔案：http://xlwt.readthedocs.io/en/latest/
Examples：https://github.com/python-excel/xlwt/tree/master/examples
GitHub：https://github.com/python-excel/xlwt

xlutils

用于配合xlrd和xlwt的工具庫，包括樣式的復制
檔案：http://xlutils.readthedocs.io/en/latest/
GitHub：https://github.com/python-excel/xlutils

必須安裝 Microsoft Excel應用程式后才能使用的庫：

xlwings

xlwings是開源，用Python替代VBA自動化操作Excel，同時支持Windows和MacOS，在Windows平臺下，通過xlwings在Python中撰寫UDF可以實作在Excel中呼叫Python， xlwings PRO是一個具有附加功能的商業插件，

主頁：https://www.xlwings.org/
檔案：https://docs.xlwings.org/en/stable/
GitHub：https://github.com/xlwings/xlwings

Excel單元格自定義格式引數含義

一、代碼結構組

代碼結構組成分為四個部分，中間用";"號分隔，具體如下：
正數格式；負數格式；零格式；文本格式

二、各個引數的含義

1、“G/通用格式”：以常規的數字顯示，相當于"分類"串列中的"常規"選項，

G/通用格式

10顯示為10；10.1顯示為10.1，

2、“0”：數字占位符，如果單元格的內容大于占位符，則顯示實際數字，如果小于點位符的數量，則用0補足，

1234567顯示為1234567；123顯示為00123

00.000

100.14顯示為100.140；1.1顯示為01.100

0000-00-00

20050512顯示為2005-05-12

3、"#"：數字占位符，只顯有意義的零而不顯示無意義的零，小數點后數字如大于"#“的數量，則按”#"的位數四舍五入，

###.##

12.1顯示為12.10；12.1263顯示為12.13

4、"？"：數字占位符，在小數點兩邊為無意義的零添加空格，對齊結果為以小數點對齊，另外還用于對不等到長數字的分數，

??.??

結果自動以小數點對齊：

#??/??

原資料：

展示效果：

5、"."：小數點，如果外加雙引號則為字符，

0.#

11.23顯示為11.2

6、"%"：百分比，

#%

0.1顯示為10%

7、","：千位分隔符，數字使用千位分隔符，如在代碼中","后空，則把原來的數字縮小1000倍，

#,###

“10000"顯示為"10,000”

#,

“10000"顯示為"10”

#,,

“1000000"顯示為"1”

8、"@"：文本占位符，如果只使用單個@，作用是參考原始文本，要在輸入數字資料之前自動添加文本，使用自定義格式為：“文本內容”@；要在輸入數字資料之后自動添加文本，使用自定義格式為：@“文本內容”，@符號的位置決定了Excel輸入的數字資料相對于添加文本的位置，如果使用多個@，則可以重復文本，

"集團"@"部"

財務顯示為：集團財務部

@@@

財務顯示為：財務財務財務

9、*：重復下一次字符，直到充滿列寬，

@*-

“ABC"顯示為"ABC-------------------”(僅在office中生效，wps中無效果)

10、[顏色]：用指定的顏色顯示字符，可有八種顏色可選：紅色、黑色、黃色，綠色、白色、蘭色、青色和洋紅，

[青色];[紅色];[黃色];[蘭色]

顯示結果為正數為青色，負數顯示紅色，零顯示黃色，文本則顯示為蘭色

11、[顏色N]：是呼叫調色板中顏色，N是0~56之間的整數，

[顏色3]

單元格顯示的顏色為調色板上第3種顏色，

12、[條件]：可以單元格內容判斷后再設定格式，條件格式化只限于使用三個條件，其中兩個條件是明確的，另個是"所有的其他"，條件要放到方括號中，必須進行簡單的比較，

[>0]"正數";[=0]"零";"負數"

13、"!"：顯示"""，由于引號是代碼常用的符號，在單元格中是無法用""“來顯示出來”""，要想顯示出來，須在前加入"!"

#!"

“10"顯示"10"”

#!"!"

“10"顯示"10"”"

14、時間和日期代碼

“YYYY"或"YY”：按四位（1900~9999）或兩位（00~99）顯示年
“MM"或"M”：以兩位（01~12）或一位（1~12）表示月，
“DD"或"D”：以兩位（01~31）或一位（1-31）來表示天，
“YYYY-MM-DD”，2005年1月10日顯示為：“2005-01-10”
“YY-M-D”，2005年10月10日顯示為：“05-1-10”
“AAAA”：日期顯示為星期，
“H"或"HH”：以一位（0~23）或兩位（01~23）顯示小時
“M"或"MM”：以一位（0~59）或兩位（01~59）顯示分鐘
“S"或"SS”：以一位（0~59）或兩位（01~59）顯示秒
“HH:MM:SS”，“23:1:15"顯示為"23:01:15”

總結

經過上面的演示，大家應該對openpyxl和xlsxwriter都有了一個比較直觀的認知，這兩個庫大家覺得到底哪個更方便呢？

歡迎在下方留言或討論，

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/241855.html

標籤：python

上一篇：Python 常用基礎知識點（一）—— 基礎知識點

下一篇：第一屆BMZCTF公開賽-MISC-Writeup

Pandas指定樣式保存excel資料的N種姿勢