九、Openpyxl單元格對象的屬性和方法

1. 設(shè)置字體Font

語法

Font(name=None, sz=None, b=None, i=None, charset=None, u=None, strike=None, color=None, scheme=None, family=None, size=None, bold=None, italic=None, strikethrough=None, underline=None, vertAlign=None, outline=None, shadow=None, condense=None, extend=None)

參數(shù)詳解

  • name ：字體名稱
  • size / sz ：字體大小
  • bold / b ：是否加粗（bool值)
  • italic / i ：是否斜體（bool值)
  • underline / u ：下劃線
'single',
'double',
'singleAccounting',
'doubleAccounting'
  • strikethrough / strike ：刪除線（bool值）
  • color ：字體顏色
  • scheme

"major", （標題）
 "minor" （正文）
  • vertAlign

'superscript',  上標
'subscript',  下標
'baseline' 普通
  • 其他省略。

實例



import openpyxl

from openpyxl.styles import Font

wb = openpyxl.Workbook()

ws = wb.active


ws.append(['name','number','unit','price'])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])




# 設(shè)置標題
for c in range(1,5):
    ws.cell(1,c).font = Font(name='宋體',size=14,bold=True)


# 設(shè)置正文內(nèi)容

for r in range(2,7):
    for c in range(1,5):
        ws.cell(r,c).font = Font(name='宋體',size=12)
        



wb.save(r'/Users/junliangchen/Desktop/test.xlsx')

2. 設(shè)置邊框Border

Side語法

Side(style=None, color=None, border_style=None)

參數(shù)詳解

  • style / border_style 邊框樣式
'dashDot','dashDotDot', 'dashed','dotted',
'double','hair', 'medium', 'mediumDashDot',
 'mediumDashDotDot', 'mediumDashed', 'slantDashDot', 'thick', 'thin'
  • color：邊框顏色

Border語法

Border(left=None, right=None, top=None, bottom=None, diagonal=None, diagonal_direction=None, vertical=None, horizontal=None, diagonalUp=False, diagonalDown=False, outline=True, start=None, end=None)

參數(shù)詳解

  • top / bottom / left / right 分別對上下左右設(shè)置邊框
  • diagonalUp ：是否設(shè)置對角線向上(bool值)
  • diagonalDown ：是否設(shè)置對角線向下(bool值）
  • 當設(shè)置了上面選項后，再來設(shè)置 diagonal
  • 其他省略

實例


import openpyxl

from openpyxl.styles import Font, Border, Side

wb = openpyxl.Workbook()

ws = wb.active


ws.append(['name','number','unit','price'])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])




# 設(shè)置標題
for c in range(1,5):
    ws.cell(1,c).font = Font(name='宋體',size=14,bold=True)


# 設(shè)置正文內(nèi)容

for r in range(2,7):
    for c in range(1,5):
        ws.cell(r,c).font = Font(name='宋體',size=12)


# 設(shè)置邊框
for r in range(1,7):
    for c in range(1,5):
        ws.cell(r,c).border = Border(top=Side(style='thin',color='1E1E1E'),
                                    right=Side(style='thin',color='1E1E1E'),
                                    bottom=Side(style='thin',color='1E1E1E'),
                                    left=Side(style='thin',color='1E1E1E'),diagonalUp=True,diagonalDown=True,diagonal=Side(style='thin',color='1E1E1E'))



wb.save(r'/Users/junliangchen/Desktop/test.xlsx')

3. 設(shè)置對齊方式Alignment

語法

Alignment(horizontal=None, vertical=None, textRotation=0, wrapText=None, shrinkToFit=None, indent=0, relativeIndent=0, justifyLastLine=None, readingOrder=0, text_rotation=None, wrap_text=None, shrink_to_fit=None)

參數(shù)詳解

  • horizontal 水平對齊方式
"general", 常規(guī) 
"left", 靠左
"center",居中 
"right",靠右 
"fill", 填充
"justify", 兩端對齊
"centerContinuous",跨列居中
"distributed" 分散對齊
  • vertical 垂直對齊方式
"top", "center", "bottom", 
"justify", 兩端對齊
"distributed",分散對齊
  • wrapText / wrap_text , bool , 是否自動換行
  • shrinkToFit / shrink_to_fit , bool, 是否縮小字體填充
  • justifyLastLine bool 是否合并單元格
  • indent 縮進
  • textRotation / text_rotation 文本旋轉(zhuǎn)角度
  • 其他省略

實例


import openpyxl

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

wb = openpyxl.Workbook()

ws = wb.active


ws.append(['name','number','unit','price'])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])




# 設(shè)置標題
for c in range(1,5):
    ws.cell(1,c).font = Font(name='宋體',size=14,bold=True)


# 設(shè)置正文內(nèi)容

for r in range(2,7):
    for c in range(1,5):
        ws.cell(r,c).font = Font(name='宋體',size=12)


# 設(shè)置邊框
for r in range(1,7):
    for c in range(1,5):
        ws.cell(r,c).border = Border(top=Side(style='thin',color='1E1E1E'),
                                    right=Side(style='thin',color='1E1E1E'),
                                    bottom=Side(style='thin',color='1E1E1E'),
                                    left=Side(style='thin',color='1E1E1E'),diagonalUp=True,diagonalDown=True,diagonal=Side(style='thin',color='1E1E1E'))
# 設(shè)置對齊方式
        ws.cell(r,c).alignment = Alignment(horizontal='center',vertical='center')



wb.save(r'/Users/junliangchen/Desktop/test.xlsx')

4. 設(shè)置填充色PatternFill

語法

PatternFill(patternType=None, fgColor=Color(), bgColor=Color(), fill_type=None, start_color=None, end_color=None)

參數(shù)詳解

  • patternType / fill_type 填充樣式
FILL_NONE = 'none'
FILL_SOLID = 'solid'
FILL_PATTERN_DARKDOWN = 'darkDown'
FILL_PATTERN_DARKGRAY = 'darkGray'
FILL_PATTERN_DARKGRID = 'darkGrid'
FILL_PATTERN_DARKHORIZONTAL = 'darkHorizontal'
FILL_PATTERN_DARKTRELLIS = 'darkTrellis'
FILL_PATTERN_DARKUP = 'darkUp'
FILL_PATTERN_DARKVERTICAL = 'darkVertical'
FILL_PATTERN_GRAY0625 = 'gray0625'
FILL_PATTERN_GRAY125 = 'gray125'
FILL_PATTERN_LIGHTDOWN = 'lightDown'
FILL_PATTERN_LIGHTGRAY = 'lightGray'
FILL_PATTERN_LIGHTGRID = 'lightGrid'
FILL_PATTERN_LIGHTHORIZONTAL = 'lightHorizontal'
FILL_PATTERN_LIGHTTRELLIS = 'lightTrellis'
FILL_PATTERN_LIGHTUP = 'lightUp'
FILL_PATTERN_LIGHTVERTICAL = 'lightVertical'
FILL_PATTERN_MEDIUMGRAY = 'mediumGray'
  • fgColor / start_color
  • bgColor / end_color

實例


import openpyxl

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

wb = openpyxl.Workbook()

ws = wb.active


ws.append(['name','number','unit','price'])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])




# 設(shè)置標題
for c in range(1,5):
    ws.cell(1,c).font = Font(name='宋體',size=14,bold=True)
    # 設(shè)置填充色
    ws.cell(1,c).fill = PatternFill(fill_type='solid',start_color='FB536C') # 紅色


# 設(shè)置正文內(nèi)容

for r in range(2,7):
    for c in range(1,5):
        ws.cell(r,c).font = Font(name='宋體',size=12)


# 設(shè)置邊框
for r in range(1,7):
    for c in range(1,5):
        ws.cell(r,c).border = Border(top=Side(style='thin',color='1E1E1E'),
                                    right=Side(style='thin',color='1E1E1E'),
                                    bottom=Side(style='thin',color='1E1E1E'),
                                    left=Side(style='thin',color='1E1E1E'),diagonalUp=True,diagonalDown=True,diagonal=Side(style='thin',color='1E1E1E'))
        ws.cell(r,c).alignment = Alignment(horizontal='center',vertical='center')



wb.save(r'/Users/junliangchen/Desktop/test.xlsx')

5. 設(shè)置數(shù)字格式

語法

worksheet.number_format = 'General'

可以填的參數(shù)有

0: 'General',
1: '0',
2: '0.00',
3: '#,##0',
4: '#,##0.00',
5: '"$"#,##0_);("$"#,##0)',
6: '"$"#,##0_);[Red]("$"#,##0)',
7: '"$"#,##0.00_);("$"#,##0.00)',
8: '"$"#,##0.00_);[Red]("$"#,##0.00)',
9: '0%',
10: '0.00%',
11: '0.00E+00',
12: '# ?/?',
13: '# ??/??',
14: 'mm-dd-yy',
15: 'd-mmm-yy',
16: 'd-mmm',
17: 'mmm-yy',
18: 'h:mm AM/PM',
19: 'h:mm:ss AM/PM',
20: 'h:mm',
21: 'h:mm:ss',
22: 'm/d/yy h:mm',

37: '#,##0_);(#,##0)',
38: '#,##0_);[Red](#,##0)',
39: '#,##0.00_);(#,##0.00)',
40: '#,##0.00_);[Red](#,##0.00)',

41: r'_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)',
42: r'_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_)',
43: r'_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)',

44: r'_("$"* #,##0.00_)_("$"* \(#,##0.00\)_("$"* "-"??_)_(@_)',
45: 'mm:ss',
46: '[h]:mm:ss',
47: 'mmss.0',
48: '##0.0E+0',
49: '@', }

6. 添加注釋

導(dǎo)入模塊

from openpyxl.comments import Comment

語法

Comment(text, author, height=79, width=144)

參數(shù)詳解

  • text ：注釋內(nèi)容
  • author ：作者

實例



import openpyxl
from openpyxl.styles import Font, Border, Side, Alignment, PatternFill
from openpyxl.comments import Comment


wb = openpyxl.Workbook()

ws = wb.active


ws.append(['name','number','unit','price'])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])




# 設(shè)置標題
for c in range(1,5):
    ws.cell(1,c).font = Font(name='宋體',size=14,bold=True)
    # 設(shè)置填充色
    ws.cell(1,c).fill = PatternFill(fill_type='solid',start_color='FB536C') # 紅色


# 設(shè)置正文內(nèi)容

for r in range(2,7):
    for c in range(1,5):
        ws.cell(r,c).font = Font(name='宋體',size=12)


# 設(shè)置邊框
for r in range(1,7):
    for c in range(1,5):
        ws.cell(r,c).border = Border(top=Side(style='thin',color='1E1E1E'),
                                    right=Side(style='thin',color='1E1E1E'),
                                    bottom=Side(style='thin',color='1E1E1E'),
                                    left=Side(style='thin',color='1E1E1E'),diagonalUp=True,diagonalDown=True,diagonal=Side(style='thin',color='1E1E1E'))
        ws.cell(r,c).alignment = Alignment(horizontal='center',vertical='center')

# 設(shè)置數(shù)字格式
for r in range(2,7):
    ws.cell(r,3).number_format = '#,##0.00'

# 設(shè)置注釋

ws.cell(1,1).comment = Comment(text='增加注釋',author='Jc')

wb.save(r'/Users/junliangchen/Desktop/test.xlsx')

7. 保護

語法

Protection(locked=True, hidden=False)

實例



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


wb = openpyxl.Workbook()

ws = wb.active


ws.append(['name','number','unit','price'])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])




# 設(shè)置標題
for c in range(1,5):
    ws.cell(1,c).font = Font(name='宋體',size=14,bold=True)
    # 設(shè)置填充色
    ws.cell(1,c).fill = PatternFill(fill_type='solid',start_color='FB536C') # 紅色


# 設(shè)置正文內(nèi)容

for r in range(2,7):
    for c in range(1,5):
        ws.cell(r,c).font = Font(name='宋體',size=12)


# 設(shè)置邊框
for r in range(1,7):
    for c in range(1,5):
        ws.cell(r,c).border = Border(top=Side(style='thin',color='1E1E1E'),
                                    right=Side(style='thin',color='1E1E1E'),
                                    bottom=Side(style='thin',color='1E1E1E'),
                                    left=Side(style='thin',color='1E1E1E'),diagonalUp=True,diagonalDown=True,diagonal=Side(style='thin',color='1E1E1E'))
        ws.cell(r,c).alignment = Alignment(horizontal='center',vertical='center')

# 設(shè)置數(shù)字格式
for r in range(2,7):
    ws.cell(r,3).number_format = '#,##0.00'

# 設(shè)置注釋

ws.cell(1,1).comment = Comment(text='增加注釋',author='Jc')

# 保護

ws.cell(1,1).protection = Protection(locked=False,
                      hidden=False)


wb.save(r'/Users/junliangchen/Desktop/test.xlsx')
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容