public TCExcelDto Read(string path, int pageNumber)
{
// Prepare excel
var excel = new ExcelPackage(new FileInfo(path));
ExcelWorksheet workSheet = excel.Workbook.Worksheets[pageNumber];
// Read data
var result = new TCExcelDto();
for (int row = EXCEL_START_ROW; row <= workSheet.Dimension.End.Row; row++)
{
Dictionary <int, Object> excelDictionary = new Dictionary <int, Object>();
for (int column = 1; column <= workSheet.Dimension.End.Column; column++)
{
Object value = workSheet.Cells[row, column].Value;
excelDictionary.Add(column, value);
}
if (excelDictionary.Any())
{
result.TCList.Add(excelDictionary);
}
}
return(result);
}
public void HandleEmptyAndNullData(TCExcelDto arg, ExcelHelperRule rule)
{
switch (rule)
{
case ExcelHelperRule.CUCUMBER:
HandleByCucumberRule(arg);
break;
default: return;
}
}
public void HandleByCucumberRule(TCExcelDto arg)
{
object previousTestcaseId = null;
object previousCheckinId = null;
object previousPropertyId = null;
object previousRoomId = null;
object previousChannelId = null;
object previousCurrencyId = null;
object previousRatePlanId = null;
foreach (var data in arg.TCList)
{
if (data[TESTCASE_COL] == null)
{
data[TESTCASE_COL] = previousTestcaseId;
}
if (data[CHECKIN_COL] == null)
{
data[CHECKIN_COL] = previousCheckinId;
}
if (data[HOTELID_COL] == null)
{
data[HOTELID_COL] = previousPropertyId;
}
if (data[ROOMID_COL] == null)
{
data[ROOMID_COL] = previousRoomId;
}
if (data[CHANNEL_COL] == null)
{
data[CHANNEL_COL] = previousChannelId;
}
if (data[CURRENCY_COL] == null)
{
data[CURRENCY_COL] = previousCurrencyId;
}
if (data[RATEPLAN_COL] == null)
{
data[RATEPLAN_COL] = previousRatePlanId;
}
previousTestcaseId = data[TESTCASE_COL];
previousCheckinId = data[CHECKIN_COL];
previousPropertyId = data[HOTELID_COL];
previousRoomId = data[ROOMID_COL];
previousChannelId = data[CHANNEL_COL];
previousCurrencyId = data[CURRENCY_COL];
previousRatePlanId = data[RATEPLAN_COL];
}
}
public void MapTestcase(TCExcelDto mapFrom, List <ExcelModel> mapTo)
{
foreach (var excelDto in mapFrom.TCList)
{
var tcModel = new ExcelModel();
tcModel.TestcaseNumber = Convert.ToString(excelDto[2]);
tcModel.HotelID = Convert.ToString(excelDto[3]);
tcModel.CheckIn = Convert.ToString(excelDto[6]);
tcModel.Los = Convert.ToString(excelDto[7]);
tcModel.RatePlan = Convert.ToString(excelDto[8]);
tcModel.Adult = Convert.ToString(excelDto[9]);
tcModel.Children = Convert.ToString(excelDto[10]);
tcModel.ChildAge = Convert.ToString(excelDto[11]);
tcModel.Rooms = Convert.ToString(excelDto[12]);
tcModel.IsAllOcc = Convert.ToString(excelDto[13]);
tcModel.AllowOverideOcc = Convert.ToString(excelDto[14]);
tcModel.HotelID2 = Convert.ToString(excelDto[15]);
tcModel.RoomID = Convert.ToString(excelDto[16]);
tcModel.Channel = Convert.ToString(excelDto[17]);
tcModel.Currency = Convert.ToString(excelDto[18]);
tcModel.RatePlan2 = Convert.ToString(excelDto[19]);
tcModel.Occupancy = Convert.ToString(excelDto[20]);
tcModel.MaxExtraBed = Convert.ToString(excelDto[22]);
tcModel.IsFit = Convert.ToString(excelDto[23]);
tcModel.ExtraBad = Convert.ToString(excelDto[25]);
tcModel.SellEx = Convert.ToString(excelDto[26]);
//breakdown task
tcModel.Date = Convert.ToString(excelDto[27]);
tcModel.Type = Convert.ToString(excelDto[28]);
tcModel.Option = Convert.ToString(excelDto[29]);
tcModel.Quantity = Convert.ToString(excelDto[30]);
tcModel.SellEx2 = Convert.ToString(excelDto[31]);
tcModel.Type2 = Convert.ToString(excelDto[33]);
tcModel.Option2 = Convert.ToString(excelDto[34]);
tcModel.Quantity2 = Convert.ToString(excelDto[35]);
tcModel.SellEx3 = Convert.ToString(excelDto[36]);
mapTo.Add(tcModel);
}
}
