使用EPplus对每个单元格进行数据验证，以检查该值是否可用 [英] Data validation for each cell to check whether the value is available or not using EPplus

问题描述

我正在使用Epplus上载文件.我有两张.xlsx格式的表格.我要确保每个单元格都有值.例如在excel中，我有一个下拉框.如果用户在第一张表格中有详细信息第二个工作表是空的.但是这个空工作表只有一个值，由于使用了该下拉菜单而被错误地添加到工作表中.因此，在此阶段，进入此循环时，它将显示对象空引用错误

I am using Epplus for uploading file.I have two sheet in .xlsx format.I want to ensure that each and every cell has value.For example in my excel i have dropdown box.If a user have details in first sheet and second sheet is empty.But this empty sheet have only one value which is added in sheet by mistake by use of this dropdown.So at this stage it will show object null reference error when it goes in to this loop

    for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
     {

         if (int.TryParse(s.Cells[rowIterator, 1].Value.ToString(), out n) && int.TryParse(s.Cells[rowIterator, 2].Value.ToString(), out n))
        {
          Pss.Pbr = Convert.ToInt32(s.Cells[rowIterator, 1].Value);
          Pss.Amount = Convert.ToInt32(s.Cells[rowIterator, 2].Value);
         Ps.Add(Pss);

        }
}

如何验证所有列和行均具有值

how to validate that all columns and row have values

推荐答案

在循环中，检查单元格是否不为null也不为空，如下所示:

In the loop, check if the cell is not null and is not empty, like this:

bool allRangeHasValue=true;
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
{
    for(int col =1;col<=s.Dimension.End.Column;col++)
    {

      if(String.IsNullOrWhiteSpace(s.Cells[rowIterator, col]?.Value?.ToString())
      {
          allRangeHasValue=false;
          break;
      }
    }
    if(!allRangeHasValue)
    {
      break;
    }
}

根据您的评论，您可以执行以下操作: Edit2:由于您使用的是TryParse，因此无需再次解析，TryParse会返回out变量中的值.

Based on your comment, you could do something like: Since you're using TryParse, you don't need to parse again, TryParse returns the value in the out variable.

for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
 {
     int n1;
     int n2;
     if (!string.IsNullOrWhiteSpace(s.Cells[rowIterator, 1]?.Value?.ToString()) &&
         !string.IsNullOrWhiteSpace(s.Cells[rowIterator, 2]?.Value?.ToString()) &&
         int.TryParse(s.Cells[rowIterator, 1].Value.ToString(), out n1) && 
         int.TryParse(s.Cells[rowIterator, 2].Value.ToString(), out n2))
    {
      Pss.Pbr = n1;
      Pss.Amount = n2;
      Ps.Add(Pss);

    }
}

这篇关于使用EPplus对每个单元格进行数据验证，以检查该值是否可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！