基本说明
1.设置excel数字格式
2.设置excel条件格式
设置数字格式
from openpyxl import Workbook
import datetime
wb = Workbook()
ws = wb.active
ws['B1'] = 1
ws['B1'].number_format = 'General' # 常规
ws['B2'] = 1
ws['B2'].number_format = '0.00' # 小数点两位，同理0.000表示三位，以此类推
ws['B3'] = 1
ws['B3'].number_format = '0%' # 百分比格式
ws['B4'] = 1
ws['B4'].number_format = '0.00%' # 小数位百分比格式，同理0.000%表示三位，以此类推
ws['B5'] = 1
ws['B5'].number_format = "#,##0" # 货币格式
ws['B6'] = 1000000
ws['B6'].number_format = '0.00E+00' # 科学计数法
ws['B7'] = datetime.datetime(2022, 7, 21, 20, 20, 30, 40)
ws['B8'] = datetime.datetime(2022, 7, 21, 20, 20, 30, 40)
ws['B8'].number_format = "mm-dd-yy" # 短日期格式
ws['B9'] = datetime.datetime(2022, 7, 21, 20, 20, 30, 40)
ws['B9'].number_format = "h:mm:ss" # 时分秒格式
wb.save('test.xlsx')
保存结果如下：
设置条件格式
from openpyxl import Workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill
wb = Workbook()
ws = wb.active
data = [ [14, 15],
[24, 65],
[34, 25],
[40, 40],
[54, 25],
[64, 70],
[74, 99],
]
for r in data:
ws.append(r)
rule1 = CellIsRule(operator='greaterThan', formula=[40], fill=PatternFill(end_color='FFFF1101'))
rule2 = CellIsRule(operator='between', formula=[40, 70], fill=PatternFill(end_color='FFFF1101'))
ws.conditional_formatting.add("A1:A7", rule1) # A列1~7行比40大的填充颜色
ws.conditional_formatting.add("B1:B7", rule2) # B列1~7行40~70的填充颜色，包含40和70
wb.save('test.xlsx')
保存结果如下：
注解
operator表示具体的条件类型是什么，可选参数值以及对应的条件格式类型如下：
