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);
}
