public void ReadExcelData(XLWorkbook workBook) { if (workBook.Worksheets.Count > 0) { int sheetNo = 1; foreach (IXLWorksheet sh in workBook.Worksheets) { var dataRange = sh.RangeUsed(); int skiprows = 1; if (dataRange != null) { foreach (var row in dataRange.Rows()) { if (skiprows >= 2 && sheetNo == 1) { if (row.Cell(1).GetString().Trim() != "" && ValidateRow(row)) { RSVP rsvp = RSVP.NewRSVP(); rsvp.Type = RemoveControlChars(row.Cell(1).GetString()); //rsvp.Salutation = RemoveControlChars(row.Cell(3).GetString()); rsvp.FirstName = RemoveControlChars(row.Cell(2).GetString()); rsvp.LastName = RemoveControlChars(row.Cell(3).GetString()); rsvp.UserData1 = RemoveControlChars(row.Cell(4).GetString()); rsvp.Company = RemoveControlChars(row.Cell(5).GetString()); //rsvp.UserData3 = RemoveControlChars(row.Cell(6).GetString()); rsvp.Salutation = RemoveControlChars(row.Cell(6).GetString()); rsvp.Email = RemoveControlChars(row.Cell(7).GetString()); //rsvp.Round1PanelID = RemoveControlChars(row.Cell(14).GetString()); //rsvp.Round2PanelID = RemoveControlChars(row.Cell(15).GetString()); rsvp.IsInvitingGalaDinner = true; rsvp.WorkflowStatus = "01"; rsvp.DateCreatedString = DateTime.Now.ToString(); if (rsvp.IsValid) { rsvp = rsvp.Save(); } } //else // return; } skiprows++; } } sheetNo++; } } else { lbError.Text += "The Sheet has invalid/not enough data.<br/>Please check your file"; } }
public string ValidateExcelData(XLWorkbook workBook) { string error = string.Empty; bool isErrorOccured = false; if (workBook.Worksheets.Count > 0) { int sheetNo = 1; foreach (IXLWorksheet sh in workBook.Worksheets) { var dataRange = sh.RangeUsed(); int skiprows = 1; if (dataRange != null) { foreach (var row in dataRange.Rows()) { if (skiprows >= 2 && sheetNo == 1) { if (row.Cell(1).GetString().Trim() != "" && ValidateRow(row)) { RSVP rsvp = RSVP.NewRSVP(); /*rsvp.Salutation = RemoveControlChars(row.Cell(3).GetString()); * rsvp.FirstName = RemoveControlChars(row.Cell(4).GetString()); * rsvp.LastName = RemoveControlChars(row.Cell(5).GetString()); * rsvp.Email = RemoveControlChars(row.Cell(2).GetString()); * rsvp.Company = RemoveControlChars(row.Cell(9).GetString()); * rsvp.Location = (RemoveControlChars(row.Cell(8).GetString()).Equals("Singapore") ? "Local" : "Overseas"); * rsvp.UserData1 = RemoveControlChars(row.Cell(6).GetString()); * rsvp.UserData2 = RemoveControlChars(row.Cell(1).GetString()); * rsvp.UserData3 = RemoveControlChars(row.Cell(8).GetString());*/ rsvp.Type = RemoveControlChars(row.Cell(1).GetString()); //rsvp.Salutation = RemoveControlChars(row.Cell(3).GetString()); rsvp.FirstName = RemoveControlChars(row.Cell(2).GetString()); rsvp.LastName = RemoveControlChars(row.Cell(3).GetString()); rsvp.UserData1 = RemoveControlChars(row.Cell(4).GetString()); rsvp.Company = RemoveControlChars(row.Cell(5).GetString()); //rsvp.UserData3 = RemoveControlChars(row.Cell(6).GetString()); rsvp.Salutation = RemoveControlChars(row.Cell(6).GetString()); rsvp.Email = RemoveControlChars(row.Cell(7).GetString()); //rsvp.Round1PanelID = RemoveControlChars(row.Cell(14).GetString()); //rsvp.Round2PanelID = RemoveControlChars(row.Cell(15).GetString()); if (!rsvp.IsValid) { //error += "Data is not valid in row: " + (row.RowNumber() - 1).ToString() + "[" + rsvp.BrokenRulesCollection.ToString() + "]"; //isErrorOccured = true; break; } } else { //error += "Data is not valid in row: " + (row.RowNumber() - 1).ToString(); //isErrorOccured = true; break; } } skiprows++; } } sheetNo++; if (isErrorOccured) { break; } } } else { error += "Sheet contains no data.<br/>"; } return(error); }