private string ValidRow(IRow row, int cellCount, IList <MandatoryColumns> MColumnList, bool splitcol)
{
StringBuilder sb = new StringBuilder();
var flag = false;
var record = new ColumnsToDB();
foreach (var a in MColumnList)
{
if (a.columnName == "email")
{
if (row.GetCell(a.colIndex) != null || row.GetCell(a.colIndex).ToString().Trim() != "")
{
var test = Validator.EmailIsValid(row.GetCell(a.colIndex).ToString().ToLower().Trim());
if (test == false)
{
sb.Append("<tr><td>" + row.RowNum + "</td><td>" + row.GetCell(a.colIndex).ToString() + "</td><td>Not a Valid Email</td></tr>");
flag = true;
}
}
else
{
sb.Append("<tr><td>" + row.RowNum + "</td><td></td><td>Provide an email address</td></tr>");
flag = true;
}
}
else if (a.columnName == "fname")
{
//Remove special characters from name
var fname = (row.GetCell(a.colIndex) == null) ? null : regex.Replace(row.GetCell(a.colIndex).ToString(), String.Empty);
if (fname == null || fname.ToString().Trim() == "")
{
sb.Append("<tr><td>" + row.RowNum + "</td><td></td><td>First Name is missing</td></tr>");
flag = true;
}
}
}
if (flag == true)
{
return(sb.ToString());
}
else
{
return("");
}
}
// GET: Upload/Details/5
public ActionResult Async_Save(IEnumerable <IFormFile> files, short?eid)
{
StringBuilder sb = new StringBuilder();
if (eid != null)
{
IFormFile file = Request.Form.Files[0];
string folderName = "Upload";
string webRootPath = _env.WebRootPath;
string newPath = Path.Combine(webRootPath, folderName);
short eGrpId = GetNextGroupID(eid) == null ? (short)1 : (short)(GetNextGroupID(eid) + 1);
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
if (file.Length > 0)
{
string sFileExtension = Path.GetExtension(file.FileName).ToLower();
ISheet sheet;
string fullPath = Path.Combine(newPath, file.FileName);
bool splitcol = false; //if file has no lname column then trigger the module to split the fname based on space character and create last name column
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
stream.Position = 0;
if (sFileExtension == ".xls")
{
HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
else
{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
IRow headerRow = sheet.GetRow(0); //Get Header Row
int cellCount = headerRow.LastCellNum;
IList <MandatoryColumns> MColumnList = new List <MandatoryColumns>();
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
/******** Used to check whether mandatory column names exist*/
if (cell.ToString().ToLower().Contains("mail"))
{
MColumnList.Add(new MandatoryColumns()
{
colIndex = j, columnName = "email"
});
}
else if (cell.ToString().ToLower().Contains("fname") || cell.ToString().ToLower().Contains("first"))
{
MColumnList.Add(new MandatoryColumns()
{
colIndex = j, columnName = "fname"
});
}
else if (cell.ToString().ToLower().Contains("lname") || cell.ToString().ToLower().Contains("last"))
{
MColumnList.Add(new MandatoryColumns()
{
colIndex = j, columnName = "lname"
});
}
else if (cell.ToString().ToLower().Contains("position"))
{
MColumnList.Add(new MandatoryColumns()
{
colIndex = j, columnName = "position"
});
}
else if (cell.ToString().ToLower().Contains("company") || cell.ToString().ToLower().Contains("agency"))
{
MColumnList.Add(new MandatoryColumns()
{
colIndex = j, columnName = "company"
});
}
else if (cell.ToString().ToLower().Contains("group"))
{
MColumnList.Add(new MandatoryColumns()
{
colIndex = j, columnName = "eventgroupid"
});
}
else if (cell.ToString().ToLower().Contains("deadline"))
{
MColumnList.Add(new MandatoryColumns()
{
colIndex = j, columnName = "deadline"
});
}
/* if header column is blank move to next*/
if (cell == null || string.IsNullOrWhiteSpace(cell.ToString()))
{
continue;
}
// sb.Append("<th>" + cell.ToString() + "</th>");
}
//error message initialisation
ViewData["Import Error"] = "<ul>";
if (!MColumnList.Any(i => i.columnName == "email"))
{
ViewData["Import Error"] = ViewData["Import Error"] + "<li>No Email Address Column</li>";
}
else if (!MColumnList.Any(i => i.columnName == "fname"))
{
ViewData["Import Error"] = ViewData["Import Error"] + "<li>No First Name Column</li>";
}
else if (!MColumnList.Any(i => i.columnName == "lname"))
{
splitcol = true;
// ViewData["Import Error"] = ViewData["Import Error"] + "<li>No Last Name Column</li>";
}
ViewData["Import Error"] = ViewData["Import Error"] + "</ul>";
//if manadatory column missing then return with error message
if ((string)ViewData["Import Error"] != "<ul></ul>")
{
return(View());
}
var errorTD = ""; //this variable will have the html to display error if the row is not valid
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
errorTD = errorTD + ValidRow(row, cellCount, MColumnList, splitcol);
//sb.AppendLine("</tr>");
}
if (errorTD == "") // if no error loop thru xl file and create hashset to write to database
{
// HashSet<ColumnsToDB> recordSet = new HashSet<ColumnsToDB>(new EmailComparer());
HashSet <Ams> recordSet = new HashSet <Ams>(new EmailComparer1());
IList <Ams> recordSetL = new List <Ams>();
StringBuilder tranferTD = new StringBuilder();
var checks = _context.Ams.Where(i => i.EventId == eid).Select(r => r.EmailId).ToArray();
var emailExistInDb = 0;
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
{
IRow row = sheet.GetRow(i);
var r = new ColumnsToDB();
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
r = GetHashSet(row, cellCount, MColumnList, splitcol);
if (!checks.Contains(r.Email))
{
recordSet.Add(new Ams {
Fname = r.Fname, Lname = r.Lname, EmailId = r.Email, Position = r.Position, Company = r.Company, EventId = eid, EventGroupId = eGrpId
});
if (r.Fname.Length <= 2 || r.Fname.Contains(".") || r.Fname.Contains("_") || r.Fname.Contains("-"))
{
tranferTD.Append("<tr><td class='btn-primary'>" + r.Fname + "</td><td class='btn-primary'>" + r.Lname + "</td><td class='btn-primary'>" + r.Email + "</td></tr>");
}
else
{
tranferTD.Append("<tr><td>" + r.Fname + "</td><td>" + r.Lname + "</td><td>" + r.Email + "</td></tr>");
}
}
else
{
emailExistInDb = emailExistInDb + 1;
}
//****
// check recordSet for fname =="Error occured" string and report to user to contact marketing department
//*****
}
//Bulkinsert require a list. to remove duplicates we use hashset. so converted hashset to list
recordSetL = recordSet.ToList();
//follow the below link to bulk insert - to improve insertion time
//https://janaks.com.np/bulk-insert-in-entityframework-core/
//https://github.com/borisdj/EFCore.BulkExtensions
_context.BulkInsert(recordSetL);
sb.Append("<div class='alert alert-success' role='success'> <h4 class='alert-heading'>" + (recordSet.Count == 0 ? "No Records Updated.!": "Successfully Updated.!") + "</h4></div>" +
"<ul><li>(" + recordSet.Count().ToString() + ") Records Inserted </li> <li>(" + (sheet.LastRowNum - recordSet.Count - emailExistInDb).ToString() + ") Duplicates found and eliminated</li><li>(" + (emailExistInDb).ToString() + ") Already Exist in Database</li></ul>");
sb.Append("<table id='resultGrid' class='table table-striped table-bordered'>");
sb.Append("<th>First Name</th><th>Last Name</th><th>Email</th>");
sb.Append((tranferTD.ToString() == "" ? "<tr><td colspan='3'>No Records to update</td></tr>":tranferTD.ToString()) + "</table>");
return(Json(Content(sb.ToString())));
}
else
{
sb.Append("<div class='alert alert-danger' role='alert'> <h4 class='alert-heading'> File not updated.!</h4><p> Please fix the below errors in" + file.FileName + "and try upload again</p></div>");
sb.Append("<table id='resultGrid' class='table table-striped table-bordered'>");
sb.Append("<th>Row#</th><th>First Name/ Email</th><th>Error Message</th>");
sb.Append(errorTD + "</tr> </table>");
//Response.StatusCode = (int)System.Net.HttpStatusCode.InternalServerError;
return(Json(Content(sb.ToString())));
}
}
}
else
{
sb.Append("<div class='alert alert-danger' role='alert'> <h4 class='alert-heading'>No files selected !</h4></div>");
return(Json(Content(sb.ToString())));
}
}
else
{
//Response.StatusCode = (int)System.Net.HttpStatusCode.InternalServerError;
sb.Append("<div class='alert alert-danger' role='alert'> <h4 class='alert-heading'>No Event Selected !</h4><p> Please select an event from the drop down list</p></div>");
return(Json(Content(sb.ToString())));
}
// return this.Content(sb.ToString());
}
public ColumnsToDB GetHashSet(IRow row, int cellCount, IList <MandatoryColumns> MColumnList, bool splitcol)
{
CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
TextInfo textInfo = cultureInfo.TextInfo;
var record = new ColumnsToDB();
try
{
foreach (var a in MColumnList)
{
if (splitcol == true && a.columnName == "fname")
{
var name = textInfo.ToTitleCase(row.GetCell(a.colIndex).ToString().ToLower().Trim()).Split(" ", 2);
//if any change in expression change in validRow module as well
name[0] = regex.Replace(name[0], String.Empty); //remove any special character
record.Fname = name[0];
record.Lname = (name.Length > 0) ? null : textInfo.ToTitleCase(name[1].ToString().ToLower().Trim());
}
else if (splitcol == false && a.columnName == "fname")
{
record.Fname = textInfo.ToTitleCase(row.GetCell(a.colIndex).ToString().ToLower().Trim());
}
if (a.columnName == "lname")
{
record.Lname = row.GetCell(a.colIndex) == null ? null : textInfo.ToTitleCase(row.GetCell(a.colIndex).ToString().ToLower().Trim());
}
else if (a.columnName == "email")
{
record.Email = row.GetCell(a.colIndex).ToString().ToLower().Trim();
}
else if (a.columnName == "position")
{
record.Position = row.GetCell(a.colIndex) == null ? null : textInfo.ToTitleCase(row.GetCell(a.colIndex).ToString().ToLower().Trim());
}
else if (a.columnName == "company")
{
record.Company = row.GetCell(a.colIndex) == null ? null : textInfo.ToTitleCase(row.GetCell(a.colIndex).ToString().ToLower().Trim());
}
else if (a.columnName == "eventgroupid")
{
if (short.TryParse(row.GetCell(a.colIndex).ToString(), out short gid))
{
record.EventGroupId = gid;
}
}
else if (a.columnName == "deadline")
{
if (DateTime.TryParse(row.GetCell(a.colIndex).ToString().Trim(), out DateTime dateValue))
{
record.IndividualDeadline = dateValue;
}
}
}
; //record created
//https://stackoverflow.com/questions/18081595/c-sharp-defining-hashset-with-custom-key
//https://dotnetcodr.com/2016/08/30/using-the-hashset-of-t-object-in-c-net-to-store-unique-elements-2/
//to remove duplicates hashset of objects
return(record);
}
catch (Exception ex)
{
Console.Write(ex.InnerException.Message);
record.Fname = "Error occured";
record.Email = row.RowNum + "@rownumber";
return(record);
throw;
}
}
