Python中⽤OpenPyXL处理Excel表格-单元格格式设置OpenPyXL库 --单元格样式设置
单元格样式的控制,依赖openpyxl.style包,其中定义有样式需要的对象,引⼊样式相关:
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
Border 边框 Side 边线
PatternFill 填充
Font 字体
Aignment 对齐
以上基本可满⾜需要
基本⽤法是,将单元格对象的设置的属性赋为新的与默认不同的相应对象。
导⼊excel
from openpyxl import load_workbook
from openpyxl.styles import Border,Side
wb = load_workbook("模板.xlsx")#使⽤openpyxl读取xlsx⽂件,创建workbook
ws = wb.active
ws
<Worksheet "sheet1">
1、Border 边框 Side 边线
from openpyxl.styles import Border, Side
border_type=Side(border_style=None, color='FF000000')
border = Border(left=border_type,
right=border_type,
top=border_type,
bottom=border_type,
diagonal=border_type,
diagonal_direction=0,
outline=border_type,
vertical=border_type,
horizontal=border_type
)
border_style的样式有:
‘dashDot’,‘dashDotDot’,‘dashed’,‘dotted’,‘double’,
‘hair’,‘medium’,‘mediumDashDot’,‘mediumDashDotDot’,
‘mediumDashed’,‘slantDashDot’,‘thick’,‘thin’
举例,原excel
# 样式1 - 窄边框,⿊⾊
thin = Side(border_, color="000000")#边框样式,颜⾊border = Border(left=thin, right=thin, top=thin, bottom=thin)#边框的位置ws['A3'].border = border #A3单元格设置边框
for row in ws['A5:D6']:
for cell in row:
cell.border = border#A5:D6区域单元格设置边框
wb.save("test.xlsx")
效果:
# 样式2- 宽边框,蓝⾊
thin = Side(border_, color="0000FF")#边框样式,颜⾊border = Border(left=thin, right=thin, top=thin, bottom=thin)#边框的位置ws['A3'].border = border #A3单元格设置边框
for row in ws['A5:D6']:
for cell in row:
cell.border = border#A5:D6区域单元格设置边框
wb.save("test.xlsx")
效果:
2、字体设置
from openpyxl.styles import Font
font = Font(name='Calibri',
htmlbordersize=11,
color='FF000000',
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False)
字体名称、字体⼤⼩、字体颜⾊、加粗、斜体、纵向对齐⽅式(有三种:baseline,superscript, subscript)、下划线、删除线,字体颜⾊可以⽤RGB 或 aRGB ,
font = Font(size=14, bold=True, name='微软雅⿊',  color="FF0000")#字体⼤⼩,加粗,字体名称,字体名字
ws['A3']="欢迎关注:永恒君的百宝箱"
ws['A3'].font = font
wb.save("test.xlsx")
3、填充
from openpyxl.styles import PatternFill
# fill_type 的样式为 None 或 solid
fill = PatternFill(fill_type =None,start_color='FFFFFF',end_color='000000')
fill_type类型有:‘none’、‘solid’、‘darkDown’、‘darkGray’、‘darkGrid’、‘darkHorizontal’、‘darkTrellis’、
‘darkUp’、‘darkVertical’、‘gray0625’、
‘gray125’、‘lightDown’、‘lightGray’、‘lightGrid’、‘lightHorizontal’、
‘lightTrellis’、‘lightUp’、‘lightVertical’、‘mediumGray’
官⽅⽂档中写明,fill_type若没有特别指定类型,则后续的参数都⽆效
所以上述代码就会出问题,start_color代表前景⾊,end_color是背景⾊,
之所以设置两个参数是为了⽅便样式颜⾊的填充和渐变⾊的显⽰(个⼈认为)
如果想要纯⾊填充的话可以⽤’solid’,然后令前景⾊为你需要的颜⾊即可,即:
fill = PatternFill(fill_type =None,start_color='FF0000')
fill = PatternFill(patternType="solid", start_color="33CCFF")#纯⾊填充
ws['A3']="欢迎关注:永恒君的百宝箱"
ws['A3'].fill = fill
wb.save("test.xlsx")
4、对齐
from openpyxl.styles import Alignment
align = Alignment(horizontal='left',vertical='center',wrap_text=True)
horizontal代表⽔平⽅向,可以左对齐left,还有居中center和右对齐right,分散对齐distributed,跨列居中centerContinuous,两端对齐justify,填充fill,常规general
vertical代表垂直⽅向,可以居中center,还可以靠上top,靠下bottom,两端对齐justify,分散对齐distributed
⾃动换⾏:wrap_text,这是个布尔类型的参数,这个参数还可以写作wrapText
align = Alignment(horizontal='right',vertical='center',wrap_text=True)#纯⾊填充
ws['A3']="永恒君的百宝箱"
ws['A3'].alignment = align
wb.save("test.xlsx")

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。