一 条件格式
import openpyxl
from openpyxl import styles
from openpyxl.formatting.rule import CellIsRule
otl_file_path = r'test.xlsx'
wb = openpyxl.load_workbook(otl_file_path)
red_color = 'ffc7ce'
red_color_font = 'F0E68C'
# 设置格式样式
red_fill = styles.PatternFill(start_color=red_color, end_color=red_color, fill_type='solid')
yel_fill = styles.PatternFill(start_color=red_color_font, end_color=red_color_font, fill_type='solid')
# 设置条件的 符号和英文对照关系
{">": "greaterThan",
">=": "greaterThanOrEqual",
"<": "lessThan",
"<=": "lessThanOrEqual",
"=": "equal",
"==": "equal",
"!=": "notEqual"}
# 设置条件 从K3:M3 大于0的单元格设置成红色背景
ws_otl.conditional_formatting.add(f'K3:M3',
CellIsRule(operator='>', formula=['0'],fill=red_fill,))
wb.save(otl_file_path)
二 设置批注
import openpyxl
from openpyxl.comments import Comment
otl_file_path = r'test.xlsx'
wb = openpyxl.load_workbook(otl_file_path)
# 设置批注
comment = Comment('部门规范性名称', 'author')
comment.width = 200
comment.height = 40
wb.cell('A1', 'B1').comment = comment
wb.save(otl_file_path)
三 冻结窗口, 保护工作簿, 隐藏sheet页
import openpyxl
otl_file_path = r'test.xlsx'
wb = openpyxl.load_workbook(otl_file_path)
# 冻结窗口
wb.freeze_panes = 'P1'
# 保护工作簿(设置密码为: 123)
wb.security = WorkbookProtection(workbookPassword='123', lockStructure=True)
# 隐藏sheet页
wb.sheet_state = 'hidden'
wb.save(otl_file_path)
四 复制sheet页, 删除sheet页,创建sheet页
import openpyxl
otl_file_path = r'test.xlsx'
wb = openpyxl.load_workbook(otl_file_path)
# 创建sheet页
wb_create = wb.create_sheet('合并表')
# 复制sheet页
wb_copy = wb.copy_worksheet(ws_all)
# 删除sheet页
wb.remove(wb.worksheets[0])
wb.save(otl_file_path)
https://www.cnblogs.com/pinpin/p/10471732.html