Openpyxl与Pytest：正确检测Excel单元格的空值状态

# 假设 cell_values["marks"] 实际值为 '' (空字符串)
# row_number 为当前行号
assert cell_values["marks"] is None, (f"The value in 'marks' is not None for row {row_number}. "
                                      f"Actual: {cell_values['marks']}")

import openpyxl
import pytest

def get_cell_value_from_excel(filepath, sheet_name, row, col):
    """模拟从Excel文件获取单元格值"""
    # 实际应用中，这里会使用 openpyxl 加载工作簿并读取单元格
    # 为演示目的，我们直接返回模拟值
    if row == 2 and col == 'marks':
        return None  # 模拟真正空的单元格
    elif row == 3 and col == 'marks':
        return ''    # 模拟包含空字符串的单元格
    elif row == 4 and col == 'marks':
        return 5     # 模拟包含数字的单元格
    else:
        return "some_value"

# 假设 cell_values 是一个字典，包含从Excel读取的单元格值
# 实际应用中，cell_values 会通过循环读取多行数据获得

def test_marks_column_emptiness():
    """测试 'marks' 列的空值情况"""

    # 模拟第一行数据，'marks' 列为 None
    row_number_1 = 2
    cell_values_1 = {"marks": get_cell_value_from_excel("dummy.xlsx", "Sheet1", row_number_1, 'marks')}

    # 模拟第二行数据，'marks' 列为空字符串
    row_number_2 = 3
    cell_values_2 = {"marks": get_cell_value_from_excel("dummy.xlsx", "Sheet1", row_number_2, 'marks')}

    # 模拟第三行数据，'marks' 列为数字
    row_number_3 = 4
    cell_values_3 = {"marks": get_cell_value_from_excel("dummy.xlsx", "Sheet1", row_number_3, 'marks')}

    # 对第一行进行断言
    cell_value_1 = cell_values_1["marks"]
    assert cell_value_1 is None or cell_value_1 == "", \
        f"The value in 'marks' is not None or empty for row {row_number_1}. Actual: '{cell_value_1}'"
    print(f"Row {row_number_1}: 'marks' is correctly identified as empty/None.")

    # 对第二行进行断言
    cell_value_2 = cell_values_2["marks"]
    assert cell_value_2 is None or cell_value_2 == "", \
        f"The value in 'marks' is not None or empty for row {row_number_2}. Actual: '{cell_value_2}'"
    print(f"Row {row_number_2}: 'marks' is correctly identified as empty/None.")

    # 对第三行进行断言 (期望它不为空)
    cell_value_3 = cell_values_3["marks"]
    assert not (cell_value_3 is None or cell_value_3 == ""), \
        f"The value in 'marks' for row {row_number_3} is unexpectedly empty/None. Actual: '{cell_value_3}'"
    print(f"Row {row_number_3}: 'marks' is correctly identified as not empty/None.")