openpyxl操作excel的一些方法

一 条件格式

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