Working solution with validate email,mobile number
public class ExcelProcessing
{
public List<ExcelUserData> ReadExcel()
{
string path = Config.folderPath + @"\MemberUploadFormat.xlsx";
using (var excelPack = new ExcelPackage())
{
//Load excel stream
using (var stream = File.OpenRead(path))
{
excelPack.Load(stream);
}
//Lets Deal with first worksheet.(You may iterate here if dealing with multiple sheets)
var ws = excelPack.Workbook.Worksheets[0];
List<ExcelUserData> userList = new List<ExcelUserData>();
int colCount = ws.Dimension.End.Column; //get Column Count
int rowCount = ws.Dimension.End.Row;
for (int row = 2; row <= rowCount; row++) // start from to 2 omit header
{
bool IsValid = true;
ExcelUserData _user = new ExcelUserData();
for (int col = 1; col <= colCount; col++)
{
if (col == 1)
{
_user.FirstName = ws.Cells[row, col].Value?.ToString().Trim();
if (string.IsNullOrEmpty(_user.FirstName))
{
_user.ErrorMessage += "Enter FirstName <br/>";
IsValid = false;
}
}
else if (col == 2)
{
_user.Email = ws.Cells[row, col].Value?.ToString().Trim();
if (string.IsNullOrEmpty(_user.Email))
{
_user.ErrorMessage += "Enter Email <br/>";
IsValid = false;
}
else if (!IsValidEmail(_user.Email))
{
_user.ErrorMessage += "Invalid Email Address <br/>";
IsValid = false;
}
}
else if (col ==3)
{
_user.MobileNo = ws.Cells[row, col].Value?.ToString().Trim();
if (string.IsNullOrEmpty(_user.MobileNo))
{
_user.ErrorMessage += "Enter Mobile No <br/>";
IsValid = false;
}
else if (_user.MobileNo.Length != 10)
{
_user.ErrorMessage += "Invalid Mobile No <br/>";
IsValid = false;
}
}
else if (col == 4)
{
_user.IsAdmin = ws.Cells[row, col].Value?.ToString().Trim();
if (string.IsNullOrEmpty(_user.IsAdmin))
{
_user.IsAdmin = "0";
}
}
_user.IsValid = IsValid;
}
userList.Add(_user);
}
return userList;
}
}
public static bool IsValidEmail(string email)
{
Regex regex = new Regex(@"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$",
RegexOptions.CultureInvariant | RegexOptions.Singleline);
return regex.IsMatch(email);
}
}