美文网首页
九、Openpyxl单元格对象的属性和方法

九、Openpyxl单元格对象的属性和方法

作者: JunChen | 来源:发表于2022-08-16 10:01 被阅读0次

    1. 设置字体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)

    参数详解

    • 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])




# 设置标题
for c in range(1,5):
    ws.cell(1,c).font = Font(name='宋体',size=14,bold=True)


# 设置正文内容

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. 设置边框Border

    Side语法

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

    参数详解

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

    参数详解

    • top / bottom / left / right 分别对上下左右设置边框
    • diagonalUp ：是否设置对角线向上(bool值)
    • diagonalDown ：是否设置对角线向下(bool值）
    • 当设置了上面选项后，再来设置 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])




# 设置标题
for c in range(1,5):
    ws.cell(1,c).font = Font(name='宋体',size=14,bold=True)


# 设置正文内容

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


# 设置边框
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. 设置对齐方式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)

    参数详解

    • horizontal 水平对齐方式
    "general", 常规 
"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 文本旋转角度
    • 其他省略

    实例

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




# 设置标题
for c in range(1,5):
    ws.cell(1,c).font = Font(name='宋体',size=14,bold=True)


# 设置正文内容

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


# 设置边框
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')

    4. 设置填充色PatternFill

    语法

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

    参数详解

    • 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])




# 设置标题
for c in range(1,5):
    ws.cell(1,c).font = Font(name='宋体',size=14,bold=True)
    # 设置填充色
    ws.cell(1,c).fill = PatternFill(fill_type='solid',start_color='FB536C') # 红色


# 设置正文内容

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


# 设置边框
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. 设置数字格式

    语法

    worksheet.number_format = 'General'

    可以填的参数有

    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. 添加注释

    导入模块

    from openpyxl.comments import Comment

    语法

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

    参数详解

    • text ：注释内容
    • 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])




# 设置标题
for c in range(1,5):
    ws.cell(1,c).font = Font(name='宋体',size=14,bold=True)
    # 设置填充色
    ws.cell(1,c).fill = PatternFill(fill_type='solid',start_color='FB536C') # 红色


# 设置正文内容

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


# 设置边框
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')

# 设置数字格式
for r in range(2,7):
    ws.cell(r,3).number_format = '#,##0.00'

# 设置注释

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




# 设置标题
for c in range(1,5):
    ws.cell(1,c).font = Font(name='宋体',size=14,bold=True)
    # 设置填充色
    ws.cell(1,c).fill = PatternFill(fill_type='solid',start_color='FB536C') # 红色


# 设置正文内容

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


# 设置边框
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')

# 设置数字格式
for r in range(2,7):
    ws.cell(r,3).number_format = '#,##0.00'

# 设置注释

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

    相关文章

      网友评论

          本文标题：九、Openpyxl单元格对象的属性和方法

          本文链接：https://www.haomeiwen.com/subject/dpcmwrtx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|九、Openpyxl单元格对象的属性和方法|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！