openpyxl单元格公式批注字体对齐边框填充

添加公式

• 单元格 = 公式，例如.ws1[“B8”]= “=sum(B1:B4)”
• 当需要读取值而不是公式是，在打开的时候需要用data_only=True参数

from openpyxl import *
file = r"D:\xlsx\lianxi_5.xlsx"
wb1 = load_workbook(file)
ws1 = wb1["score"]
ws1["B8"] = "=sum(B1:B4)"
print(ws1["B8"].value)
wb1.save(file)
1
2
3
4
5
6
7

=sum(B1:B4)
1

wb2 = load_workbook(file, data_only=True)
ws2 = wb2["score"]
print(ws2["B8"].value)
wb2.save(file)
1
2
3
4

265
1

---

增加批注

• 首先创建批注对象
• 单元格.comment = 批注对象

import openpyxl as xl
fileimport openpyxl as xl
file = r"D:\xlsx\lianxi_5.xlsx"
wb1 = xl.load_workbook(file)
ws1 = wb1["score"]
cm1 = xl.comments.Comment("这里要加批注", "liujun")
cm1.width = 100
cm1.height = 200
ws1["B8"].comment = cm1
wb1.save(file)
1
2
3
4
5
6
7
8
9
10

---

字体

• 首先创建字体对象
• 单元格.字体 = 字体对象
  Font(
  name=None, # 字体名，可以用字体名字的字符串
  strike=None, # 删除线，True/False
  color=None, # 文字颜色
  size=None, # 字号
  bold=None, # 加粗, True/False
  italic=None, # 倾斜，Tue/False
  underline=None # 下划线, ‘singleAccounting’, ‘double’, ‘single’, ‘doubleAccounting’
  )

import openpyxl as xl
file = r"D:\xlsx\lianxi_5.xlsx"
wb1 = xl.load_workbook(file)
ws1 = wb1["score"]
font1 = xl.styles.Font(name="微软雅黑",
                       size=14,
                        italic=True,
                        color="ff0000",
                        bold=False,
                        strike=None)
ws1["A1"].font = font1
wb1.save(file)
1
2
3
4
5
6
7
8
9
10
11
12

---

对齐方式

• 首先创建对齐对象
• 单元格.对齐 = 对齐对象
  xl.styles.Alignment(
  horizontal=‘center’, # 水平对齐，可选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 # 缩进值
  )

import openpyxl as xl
file = r"D:\xlsx\lianxi_5.xlsx"
wb1 = xl.load_workbook(file)
ws1 = wb1["score"]
ws1["B1"].alignment = xl.styles.Alignment(
    horizontal='center',    # 水平对齐，可选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                # 缩进值
)
wb1.save(file)
1
2
3
4
5
6
7
8
9
10
11
12
13

---

边框样式

• 首先创建side对象，boarder对象
• 单元格.boarder = boarder对象
  xl.styles.Alignment(
  horizontal=‘center’, # 水平对齐，可选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 # 缩进值
  )

import openpyxl as xl
from openpyxl.styles import Border, Side

file = r"D:\xlsx\lianxi_5.xlsx"
wb1 = xl.load_workbook(file)
ws1 = wb1.active
ws1["B2"] = "B2"

side = Side(
    style="medium",  # 边框样式，可选dashDot、dashDotDot、dashed、dotted、double、hair、medium、mediumDashDot、mediumDashDotDot、mediumDashed、slantDashDot、thick、thin
    color="ff66dd"   # 边框颜色，16进制rgb表示
)
ws1["B2"].border = Border(
    top=side,  
    bottom=side,
    left=side, 
    right=side,
    diagonal=side
)
wb1.save(file)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

---

单元格填充

• 首先创建fill对象
• 单元格.fill = fill对象

fill1 = PatternFill(
    patternType="darkGray",  # 填充类型，可选none、solid、darkGray、mediumGray、lightGray、lightDown、lightGray、lightGrid
    fgColor="F562a4",   # 前景色，16进制rgb   =start_color
    bgColor="0000ff",   # 背景色，16进制rgb   =end_color
    # fill_type=None,     # 填充类型
    # start_color=None,   # 前景色，16进制rgb
    # end_color=None      # 背景色，16进制rgb
)
ws1["C3"].fill = fill1
wb1.save(file)
1
2
3
4
5
6
7
8
9
10