Python 用openpyxl设置Excel单元格样式（字体样式、行列宽高、对齐方式、边框、填充和渐变）

1.设置单元格的字体样式

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active

# 默认字体样式
ws["A1"] = "A1"

# 自定义字体样式
ws["B2"] = "B2"
font = Font(
    name="微软雅黑",  # 字体
    size=15,         # 字体大小
    color="0000FF",  # 字体颜色，用16进制rgb表示
    bold=True,       # 是否加粗，True/False
    italic=True,     # 是否斜体，True/False
    strike=None,     # 是否使用删除线，True/False
    underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
)
ws["B2"].font = font

wb.save("./test.xlsx")

2.设置sheet的行宽列高

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.row_dimensions[2].height = 30  # 设置第2行高度为30
ws.column_dimensions["B"].width = 30  # 设置B列宽度为30

wb.save("./test.xlsx")

3.设置单元格字体对齐方式

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

ws.row_dimensions[2].height = 30      # 设置第2行高度为30
ws.column_dimensions["B"].width = 30  # 设置B列宽度为30

# 默认字体样式
ws["A1"] = "A1"

ws["B2"] = "B1"
ws['B2'].alignment = Alignment(
    horizontal='left',     # 水平对齐，可选general、left、center、right、fill、justify、centerContinuous、distributed
    vertical='top',        # 垂直对齐， 可选top、center、bottom、justify、distributed
    text_rotation=0,       # 字体旋转，0~180整数
    wrap_text=False,       # 是否自动换行
    shrink_to_fit=False,   # 是否缩小字体填充
    indent=0,              # 缩进值
)

wb.save("./test.xlsx")

4.设置单元格边框

from openpyxl import Workbook
from openpyxl.styles import Border, Side

wb = Workbook()
ws = wb.active

ws["B2"] = "B2"

side = Side(
    style="medium",  # 边框样式，可选dashDot、dashDotDot、dashed、dotted、double、hair、medium、mediumDashDot、mediumDashDotDot、mediumDashed、slantDashDot、thick、thin
    color="ff66dd",  # 边框颜色，16进制rgb表示
)

ws["B2"].border = Border(
    top=side,     # 上
    bottom=side,  # 下
    left=side,    # 左
    right=side,   # 右
    diagonal=side # 对角线
)

wb.save("./test.xlsx")

5.设置单元格的填充和渐变

from openpyxl import Workbook
from openpyxl.styles import PatternFill, GradientFill

wb = Workbook()
ws = wb.active

ws["B2"] = "B2"

fill = PatternFill(
    patternType="solid",  # 填充类型，可选none、solid、darkGray、mediumGray、lightGray、lightDown、lightGray、lightGrid
    fgColor="F562a4",     # 前景色，16进制rgb
    bgColor="0000ff",     # 背景色，16进制rgb
    # fill_type=None,     # 填充类型
    # start_color=None,   # 前景色，16进制rgb
    # end_color=None      # 背景色，16进制rgb
)
ws["B2"].fill = fill
ws["B3"].fill = GradientFill(
    degree=60,  # 角度
    stop=("000000", "FFFFFF")  # 渐变颜色，16进制rgb
)

wb.save("./test.xlsx")