〇、前情提要

學習小甲魚的使用Python讀寫Excel文件。這是下篇。
參考：

1. 【辦公篇】《極客Python之效率革命》（小甲魚）https://www.bilibili.com/video/av23697305/?p=1

------------第一課------------

2. [辦公] 使用Python讀寫Excel文件（1）
   https://fishc.com.cn/thread-101887-1-1.html
3. [模塊檔案] openpyxl 模塊中文文檔（一個讀寫 EXCEL 文件的模塊）https://blog.csdn.net/weixin_43210113/article/details/107505405
4. 爬取豆瓣 TOP250 電影排行榜
   https://fishc.com.cn/thread-94979-1-1.html
5. 豆瓣電影 Top 250
   https://movie.douban.com/top250
6. 我的筆記-【爬蟲篇】《極客Python之效率革命》（小甲魚） b站筆記
   https://blog.csdn.net/weixin_43210113/article/details/107505405

------------第二課------------

7. [辦公] 使用Python讀寫Excel文件（2）
   https://fishc.com.cn/thread-102046-1-1.html

------------第三課------------

8. [辦公] 使用Python讀寫Excel文件（3）
   https://fishc.com.cn/thread-102708-1-1.html

------------第四課------------

9. [辦公] 使用Python讀寫Excel文件（4）
   https://fishc.com.cn/thread-103472-1-1.html

------------第五課------------

10. [辦公] 使用Python讀寫Excel文件（5）
    https://fishc.com.cn/thread-103980-1-1.html

------------第六課------------

11. [辦公] 使用Python讀寫Excel文件（6）
    https://fishc.com.cn/forum.php?mod=viewthread&tid=141918&extra=page%3D1%26filter%3Dtypeid%26typeid%3D722

我的筆記：

1. 【Python】辦公篇-《極客Python之效率革命》（小甲魚） 筆記一
   https://blog.csdn.net/weixin_43210113/article/details/107548337
2. 【Python】辦公篇-《極客Python之效率革命》（小甲魚） 筆記二
   https://blog.csdn.net/weixin_43210113/article/details/107564712

提示：

• wb為workbook，wb["sheetname"]表示選中一張worksheet
• ws為worksheet，ws['A1']表示選中一個單元格
• 由于視頻錄制年份較早，現在的語句都會有DeprecationWarning: Call to deprecated function 過時的語句 (Use 推薦的語句).提示，可根據自身需求來使用
• 由于權限問題，請關閉excel表后再save
• 有些內容不如在excel表中直接修改，但量大時使用python會很有效

四、P4 使用Python讀寫Excel文件（4）

設置單元格字體

openpyxl.styles

• name：字體名稱
• size：字體尺寸
• bold：True（加粗）/ False（不加粗）
• italic：True（傾斜）/ False（不傾斜）
• vertAlign：‘None’（默認）/ ‘superscript’（上標）/ ‘subscript’（下標）
• underline：‘None’（默認）/ ‘single’（單下劃線）/ ‘double’（雙下劃線）/‘singleAccounting’（會計用單下劃線）/‘doubleAccounting’（會計用雙下劃線）
• strike：‘True’（顯示刪除線）/ ‘False’（不顯示刪除線）
• List item

color：字體的顏色

>>> from openpyxl import Workbook
>>> from openpyxl.styles import Font
>>> 
>>> wb = Workbook()
>>> ws = wb.active
>>> 
>>> b2 = ws['B2']
>>> b2.value = "FishC"
>>> bold_red_font = Font(bold=True, color="FF0000")
>>> b2.font = bold_red_font
>>> 
>>> b3 = ws['B3']
>>> b3.value = "FishC"
>>> italic_strike_blue_16_font = Font(italic=True, strike=True, color="0000FF", size=16)
>>> b3.font = italic_strike_blue_16_font
>>> 
>>> wb.save(r"路徑")

填充單元格

PatternFill

• fill_type：填充類型
• start_color / fgColor：背景顏色
• end_color / bgColor：圖案顏色

純色填充

>>> from openpyxl.styles import PatternFill
>>> 
>>> yellow_fill = PatternFill(fill_type="solid", fgColor="FFFF00")
>>> b2.fill = yellow_fill
>>> 
>>> wb.save(r"路徑")

漸變色填充
GradientFill 類

• fill_type：‘linear’（線性漸變）/ ‘path’（中心擴散）
• degree：旋轉角度
• stop：一個元組 (OO, XX)，OO 為起始顏色，XX 為結束顏色

>>> from openpyxl.styles import GradientFill
>>> 
>>> red2green_fill = GradientFill(fill_type="linear", stop=("FF0000", "00FF00"))
>>> b3.fill = red2green_fill
>>> 
>>> wb.save(r"路徑")

設置邊框

Border

• left：指定左側邊框線類型和顏色
• right：指定右側邊框線類型和顏色
• top：指定頂端邊框線類型和顏色
• bottom：指定低端邊框線類型和顏色
• diagonal：指定對角線類型和顏色
• diagonalUp：是否繪制左下角到右上角的對角線
• diagonalDown：是否繪制左上角到右下角的對角線
• diagonal_direction：指定對角線方向
• outline：指定輪廓線類型和顏色
• vertical：指定垂直線類型和顏色
• horizontal：指定水平線類型和顏色

>>> from openpyxl.styles import Border, Side
>>> 
>>> thin_side = Side(border_style="thin", color="000000")
>>> double_side = Side(border_style="double", color="FF0000")
>>> 
>>> b2.border = Border(diagonal=thin_side, diagonalUp=True, diagonalDown=True)
>>> b3.border = Border(left=double_side, top=double_side, right=double_side, bottom=double_side)
>>> 
>>> wb.save(r"路徑")

文本對齊

Alignment 類

• horizontal：‘general’（常規）/ ‘justify’（兩端對齊）/ ‘right’（靠右）/ ‘centerContinuous’（跨列居中）/ ‘distributed’（分散對齊）/ ‘fill’（填充）/ ‘center’（居中）/ ‘left’（靠左）
• vertical：‘center’（垂直居中）/ ‘top’（靠上）/ ‘bottom’（靠下）/ ‘justify’（兩端對齊）/ ‘distributed’（分散對齊）
• text_rotation：指定文本旋轉角度
• wrap_text：是否自動換行
• shrink_to_fit：是否縮小字體填充
• indent：指定縮進

>>> from openpyxl.styles import Alignment
>>> 
>>> ws.merge_cells('A1:C2')
>>> ws['A1'].value = "I love FishC.com"
>>> 
>>> center_alignment = Alignment(horizontal='center', vertical='center')
>>> ws['A1'].alignment = center_alignment
>>> 
>>> wb.save(r"路徑")

雖然 ‘B2’ 中存在數據和樣式，但在合并后均會被 ‘A1’ 單元格所覆蓋

命名樣式

>>> from openpyxl.styles import NamedStyle
>>> 
>>> highlight = NamedStyle(name="highlight") #實例化一個 NamedStyle 類
>>> highlight.font = Font(bold=True, size=20)#初始化命名樣式
>>> highlight.alignment = Alignment(horizontal='center', vertical='center')
>>> 
>>> wb.add_named_style(highlight)#注冊命名樣式到工作簿中
>>> 
>>> ws['A1'].style = highlight #將單元格的 style 屬性賦值為命名樣式
>>> 
>>> ws['B5'].value = "LOVE"
>>> ws['B5'].style = highlight
>>> wb.save(r"路徑")

---

五、P5 使用Python讀寫Excel文件（5）

數字格式

Excel 只有 2 種數據類型：一種是文本，另一種是數字

>>> import openpyxl
>>> 
>>> wb = openpyxl.Workbook()
>>> ws = wb.active
>>> 
>>> ws.append(['文本', '數字'])
>>> ws['A2'] = '520'
>>> ws['B2'] = 520
>>> 
>>> wb.save(r"C:\Users\goodb\Desktop\test.xlsx")

文本左對齊，數字右對齊。

自定義格式

import openpyxl
import datetime

wb = openpyxl.Workbook()
ws = wb.active

ws['A1'].number_format = "#,###.00元"
ws['A1'] = 88.8

ws['A2'] = datetime.datetime.today()
ws['A2'].number_format = "yyyy-mm-dd"

wb.save(r"路徑")

彩色自定義

正值;負值;零值;文本

import openpyxl
from openpyxl.styles.colors import RED, GREEN, BLUE, YELLOW

wb = openpyxl.Workbook()
ws = wb.active

ws['A1'].number_format = "[RED]+#,###.00;[GREEN]-#,###.00"
ws['A1'] = 99

ws['A2'].number_format = "[RED]+#,###.00;[GREEN]-#,###.00"
ws['A2'] = -99

ws['A3'].number_format = "[RED];[GREEN];[BLUE];[YELLOW]"
ws['A3'] = 0

ws['A4'].number_format = "[RED];[GREEN];[BLUE];[YELLOW]"
ws['A4'] = "FishC"

wb.save(r"路徑")

自動替換

ws['A5'].number_format = "[=1]男;[=0]女"
ws['A5'] = 0

ws['A6'].number_format = "[=1]男;[=0]女"
ws['A6'] = 1

ws['A7'].number_format = "[=1]男;[=0]女"
ws['A7'] = 2

ws['A8'].number_format = "[<60][RED]不及格;[>=60][GREEN]及格"
ws['A8'] = 58

ws['A9'].number_format = "[<60][RED]不及格;[>=60][GREEN]及格"
ws['A9'] = 68

---

六、P6 使用Python讀寫Excel文件（6）

檢查函數公式

>>> from openpyxl.utils import FORMULAE
>>> "SUM" in FORMULAE
True
>>> "SAM" in FORMULAE
False

SUM測試

=SUM(B2:D2)

每一個row為一個元組

>>> from openpyxl import load_workbook
>>> wb = load_workbook(filename = r"路徑")
>>> ws = wb["Sheet"]
>>> for row in ws.iter_rows(min_col=2, min_row=2, max_col=5, max_row=5):
        ws[row[3].coordinate] = "=SUM(%s:%s)" % (row[0].coordinate, row[2].coordinate)
>>> wb.save(r"路徑")

if函數

'=IF(%s>250,"A","B")'一定要外’'內""

>>> from openpyxl.styles import Alignment
>>> center_alignment = Alignment(horizontal='center')
>>> for row in ws.iter_rows(min_col=2, min_row=2, max_col=6, max_row=5):
        ws[row[4].coordinate] = '=IF(%s>250,"A","B")' % (row[3].coordinate)
        ws[row[4].coordinate].alignment = center_alignment

>>> wb.save(r"路徑")

LOOKUP

語法：=LOOKUP(lookup_value, lookup_vector, [result_vector])

>>> ws['I2'] = "=LOOKUP(H2, D2:D5, A2:A5)"

VLOOKUP

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value：指定待查找的值，也稱為查閱值。
• table_array：指定待查找的值所在的區域。注意，查閱值應該始終位于所在區域的第一列，這樣 VLOOKUP 才能正常工作（例如，如果查閱值位于單元格 C2 內，那么您的區域應該以 C 開頭）。
• col_index_num：指定區域中包含返回值的列號（例如，如果指定 B2:D11 作為區域，那么應該將 B 算作第一列，C 作為第二列，以此類推）。
• [range_lookup]（可選）如果需要返回值的近似匹配，可以指定 TRUE；如果需要返回值的精確匹配，則指定 FALSE。如果沒有指定任何內容，默認值將始終為 TRUE。

ws['I2'] = "=VLOOKUP(H2, A:F, 5, FALSE)"

---