读取Excel时for循环末尾抛出空引用异常问题求助

程序可正常读取Excel行数据，但读取完所有行后抛出空引用异常，异常触发在for循环执行到末尾时。

控制器代码 #

[HttpPost]
public IActionResult MostrarDados([FromForm] IFormFile ArquivoExcel)
{
    Stream stream = ArquivoExcel.OpenReadStream();

    IWorkbook MeuExcel = null;

    if (Path.GetExtension(ArquivoExcel.FileName) == ".xlsx")
    {
        MeuExcel = new XSSFWorkbook(stream);
    }
    else
    {
        MeuExcel = new HSSFWorkbook(stream);
    }

    ISheet FolhaExcel = MeuExcel.GetSheetAt(0);

    int QtdFilas = FolhaExcel.LastRowNum;

    List<VMProduct> list = new List<VMProduct>();

    for (int i = 1; i <= QtdFilas; i++)
    {
        IRow fila = FolhaExcel.GetRow(i);

        list.Add(new VMProduct
        {
            Id_Item = fila.GetCell(0).ToString(),
            Nome_Item = fila.GetCell(1).ToString(),
            Qtd_Estoque = fila.GetCell(2).ToString(),
            Preco_por = fila.GetCell(3).ToString(),
        });
    }

    return StatusCode(StatusCodes.Status200OK, list);
}

异常信息 #

异常类型：NullReferenceException（未将对象引用设置到对象的实例）
触发位置：for循环中调用fila.GetCell(x).ToString()的代码段

问题原因 #

1. Excel存在空白行：FolhaExcel.GetRow(i)读取空白行时会返回null，后续调用fila.GetCell()直接触发空引用异常。
2. 行内有空单元格：即使行本身不为null，未赋值的单元格调用GetCell()会返回null，直接调用ToString()也会抛出异常。

解决方案 #

1. 跳过空行 #

获取行对象后先判断是否为null，跳过空行避免后续报错：

IRow fila = FolhaExcel.GetRow(i);
if (fila == null) continue;

2. 安全处理空单元格 #

对每个单元格做null判断，用空字符串替代null值：

Id_Item = fila.GetCell(0)?.ToString() ?? string.Empty,
Nome_Item = fila.GetCell(1)?.ToString() ?? string.Empty,
Qtd_Estoque = fila.GetCell(2)?.ToString() ?? string.Empty,
Preco_por = fila.GetCell(3)?.ToString() ?? string.Empty,

修改后的完整代码 #

[HttpPost]
public IActionResult MostrarDados([FromForm] IFormFile ArquivoExcel)
{
    Stream stream = ArquivoExcel.OpenReadStream();

    IWorkbook MeuExcel = null;

    if (Path.GetExtension(ArquivoExcel.FileName) == ".xlsx")
    {
        MeuExcel = new XSSFWorkbook(stream);
    }
    else
    {
        MeuExcel = new HSSFWorkbook(stream);
    }

    ISheet FolhaExcel = MeuExcel.GetSheetAt(0);

    int QtdFilas = FolhaExcel.LastRowNum;

    List<VMProduct> list = new List<VMProduct>();

    for (int i = 1; i <= QtdFilas; i++)
    {
        IRow fila = FolhaExcel.GetRow(i);
        if (fila == null) continue;

        list.Add(new VMProduct
        {
            Id_Item = fila.GetCell(0)?.ToString() ?? string.Empty,
            Nome_Item = fila.GetCell(1)?.ToString() ?? string.Empty,
            Qtd_Estoque = fila.GetCell(2)?.ToString() ?? string.Empty,
            Preco_por = fila.GetCell(3)?.ToString() ?? string.Empty,
        });
    }

    return StatusCode(StatusCodes.Status200OK, list);
}

内容的提问来源于stack exchange，提问作者VICTOR MARCONI SANTOS DE OLIVE