Validate empty fields in XLSX file - (EPPlus) ASP.NET

Question

I am doing the import of an XLSX file in C #, using ExcelPackage. I make some validations in the Ex import: Numeric, text and if the field is empty.

Note: If all the columns in my file are filled the import is done successfully.

One of the application's needs is to validate if there are empty fields in the worksheet.

Example: In a range of {A2: C2}.

Where A2 is of type int, B2 is string and C2 is float

1 |  A2  |   B2   |  C2  |
2 |      | house1 | 50,8 |
3 |  50  | house2 | 50,0 |

I need to throw an Exception stating that the file contains empty fields.

The problem is that when reading the field A2 is ignored, and field B2 "Assumes" the place of A2, ie, field A2 is the first cell so it must be read, even if it is empty, and in that if you throw the Exception stating that it contains empty fields.

So my question is: I need to "force" the fields even though they are empty, and throw Exception, if they are. How do I do that?

My code is this:

public static List<ModularCostCapacitor> ReadFlieAndBuildModularCostsCapacitors(HttpPostedFile upLoad) 
 {
    var fileOriginalName = upLoad.FileName;
    var fileOriginalExtension = Path.GetExtension(fileOriginalName);

    int rowNum = 0;

    if (fileOriginalExtension.ToUpper().Equals("XLS") || fileOriginalExtension.ToUpper().Equals("XSLX"))
    {
      try
      {
         using (var excel = new ExcelPackage(upLoad.InputStream))
         {
           var ws = excel.Workbook.Worksheets.First();
           var hasHeader = true;

           int startRow = hasHeader ? 2 : 1;

           for (rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
           {
             var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];

            int i = 0;
            foreach (var cell in wsRow)
            {
                switch (i)
                {
                    case 0:
                        if (ValidateCellsXLSX(cell.Text))
                        {
                            // ...
                        }
                        break;
                    case 1:
                        if (!String.IsNullOrEmpty(cell.Text))
                        {
                            // ...
                        }
                        break;
                    case 2:
                        if (ValidateCellsXLSX(cell.Text))
                        {
                            // ...
                        }
                        break;
                    case 3:
                        if (ValidateCellsXLSX(cell.Text))
                        {
                            // ...
                        }
                        break;
                    case 4:
                        if (ValidateCellsXLSX(cell.Text))
                        {
                            // ...
                        }
                        break;
                    case 5:
                        if (ValidateCellsXLSX(cell.Text))
                        {
                            // ...
                        }
                        break;
                    case 6:
                        if (ValidateCellsXLSX(cell.Text))
                        {
                            // ...
                        }
                        break;
                    default:
                        break;
                }
                i++;
            }
        }
    }
}
catch (Exception ex)
{
    throw ex;
}

}

Validation Code:

public static bool ValidateCellsXLSX(string cell)
{
  Regex regex = new Regex(@"[;!*#&@?()'$~^<>ºª%\{}A-Za-z]");

  string validString = cell.Replace("%", "").Replace("R$", "").Replace("-", "");

  Match match = regex.Match(validString);

   if (String.IsNullOrEmpty(cell))
   {
      throw new Exception("File contains empty fields";);
   }
   else if (match.Success)
   {
      throw new Exception("File contains invalid characters");
   }

   return true;
}

Answer 1

Can you check this? it seems these guys have an answer. their approach is diffent but you might be able to get an idea https://www.codeproject.com/Questions/561817/DetectingplusEmpypluscellplusinplusExcelplusRange