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

Question

I am importing an XLSX file into C#, using Excelpackage. I do some validations on import Ex: Numeric, text and if field is empty.

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

One of the needs of the application is to validate whether there are empty fields in the spreadsheet.

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

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

1 |  A2  |  B2   |  C2   |
2 |      | casa1 | 50,8  |
3 |  50  | casa2 | 50,0  |

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

The problem is that when reading the A2 field is ignored, and the B2 field "Takes" the place of A2, i.e, the A2 field is the first cell so it must be read, even if it is empty, and in this case launch Exception stating that it contains empty fields.

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

My code is this:

public static List<ModularCostCapacitor> ReadFlieAndBuildModularCostsCapacitors(HttpPostedFile upLoad) 
{
// Validando extensão, cabeçalho e colunas
var fileOriginalName = upLoad.FileName;
var fileOriginalExtension = Path.GetExtension(fileOriginalName);

int rowNum = 0;

if (fileOriginalExtension.ToUpper().Equals(FileExtensionXLS) || fileOriginalExtension.ToUpper().Equals(FileExtensionXLSX))
{
    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;
    }
}
else
{
    throw new Exception("Invalid File");
}

}

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("Arquivo contém campos vazios";);
   }
   else if (match.Success)
   {
      throw new Exception("Arquivo contém caracteres inválidos");
    }

   return true;
}

Answer 1

If you still need the resolution. I will reduce your method like this.

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

    if (fileOriginalExtension.ToUpper().Equals(FileExtensionXLSX) || fileOriginalExtension.ToUpper().Equals(FileExtensionXLSX))
    {
        try
        {
            var package = new ExcelPackage(upLoad.InputStream);

            var workSheet = package.Workbook.Worksheets[1];

            for (int i = workSheet.Dimension.Start.Row; i <= workSheet.Dimension.End.Row; i++)
            {
                for (int j = workSheet.Dimension.Start.Column; j <= workSheet.Dimension.End.Column; j++)
                {

                    var cellValue = workSheet.Cells[i, j].Text;
                    ValidateCellsXLSX(cellValue);
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    else
    {
        throw new Exception("Invalid File");
    }
}

Only one thing I didn’t understand, what this method does with this type of return, since you only do validation in the file. Any questions just call for chat. Att.