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

2

I'm 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 successful.

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 |      | casa1 | 50,8  |
3 |  50  | casa2 | 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 case 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 this?

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;
}
    
asked by anonymous 04.06.2018 / 16:16

1 answer

0

If you still need resolution. I'd cut down on 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 did not understand, what this method does with this kind of feedback, since you only do validation in the file. Any questions just call for chat. Att.

    
15.06.2018 / 21:11