Python办公自动化：使用openpyxl对单元格操作(二)

3 处理公式

Excel 公式是非常强大的工具，可以通过 openpyxl 来读取和写入单元格中的公式。

写入公式

可以直接将公式字符串赋值给单元格的 value 属性。

import openpyxl

# 创建一个新的工作簿
workbook = openpyxl.Workbook()
sheet = workbook.active

# 在A1和B1单元格写入数据
sheet['A1'] = 10
sheet['B1'] = 20

# 在C1单元格写入一个求和公式
sheet['C1'] = '=A1+B1'

# 保存工作簿
workbook.save('cell_formula.xlsx')

代码解释

• sheet['C1'] = '=A1+B1'：将求和公式写入 C1 单元格。

读取公式

可以读取包含公式的单元格内容，但默认读取到的是公式本身，而不是计算结果。

import openpyxl

# 打开现有工作簿
workbook = openpyxl.load_workbook('cell_formula.xlsx')

# 获取当前活动工作表
sheet = workbook.active

# 读取C1单元格的公式
c1_formula = sheet['C1'].value

print(f"C1单元格中的公式是: {c1_formula}")

代码解释

• sheet['C1'].value：读取 C1 单元格的公式。

4 数据验证

数据验证用于限制用户在单元格中输入的数据类型和范围。

设置数据验证

可以使用 DataValidation 类来创建数据验证规则。

import openpyxl
from openpyxl.worksheet.datavalidation import DataValidation

# 创建一个新的工作簿
workbook = openpyxl.Workbook()
sheet = workbook.active

# 创建一个整数类型的数据验证，限定输入值在1到10之间
dv = DataValidation(type="whole", operator="between", formula1=1, formula2=10, showErrorMessage=True)
dv.error = "请输入1到10之间的整数！"
dv.prompt = "请输入数字"

# 将数据验证应用于A1到A10单元格
sheet.add_data_validation(dv)
dv.add('A1:A10')

# 保存工作簿
workbook.save('data_validation_example.xlsx')

代码解释

• `Data

Validation(type=“whole”, operator=“between”, formula1=1, formula2=10)`：创建一个数据验证对象，类型为整数，范围在1到10之间。

• sheet.add_data_validation(dv)：将数据验证规则添加到工作表中。
• dv.add('A1:A10')：将数据验证应用于指定的单元格区域。

5 条件格式

条件格式用于根据单元格的内容自动更改其格式。

应用条件格式

使用 ConditionalFormattingRule 类可以创建条件格式规则，并使用 add_conditional_formatting() 方法将其应用到单元格范围。

import openpyxl
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

# 创建一个新的工作簿
workbook = openpyxl.Workbook()
sheet = workbook.active

# 在A1到A10单元格写入数据
for i in range(1, 11):
    sheet[f'A{i}'] = i

# 创建一个条件格式规则：如果单元格值大于5，则填充绿色背景
green_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
rule = CellIsRule(operator='greaterThan', formula=['5'], fill=green_fill)

# 将条件格式应用于A1到A10单元格
sheet.conditional_formatting.add('A1:A10', rule)

# 保存工作簿
workbook.save('conditional_formatting_example.xlsx')

代码解释

• CellIsRule(operator='greaterThan', formula=['5'], fill=green_fill)：创建一个条件格式规则，当单元格值大于5时，应用绿色填充。
• sheet.conditional_formatting.add('A1:A10', rule)：将条件格式应用于指定的单元格范围。