private void PopulateRecords(FCUImportCLassModel rec, ObservableCollection <FCU> temp)
{
if (fcuCollection.Any(x => x.SerialNo == rec.SerialNo) == false)
{
temp.Add(new FCU
{
ID = Convert.ToInt64(rec.IDSeq),
Project = rec.Project,
UnitTag = rec.UnitTag,
PartNo = rec.PartNo,
Model = rec.Model,
PowerSupply = rec.PowerSupply,
FanMotor1 = rec.FanMotor1,
FanMotor2 = rec.FanMotor2,
Qty = rec.Qty,
Qty2 = rec.Qty,
CoolingCoil = rec.CoolingCoil1 + "R" + "/" + rec.CoolingCoil2 + "H",
SalesOrder = rec.SalesOrder,
Item = rec.Item,
SerialNo = rec.SerialNo,
CreatedOn = currDateTime,
CreatedBy = AuthenticatedUser,
ModifiedOn = currDateTime,
ModifiedBy = AuthenticatedUser,
IsChecked = true,
ShipStatus = false,
ShipStatus2 = false
});
}
}
private void ReadExcelFile()
{
try
{
ISheet sheet = fcuWorkbook.GetSheetAt(1);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
currDateTime = DateTime.Now;
#region Cells Comparison
while (rows.MoveNext())
{
IRow row = (XSSFRow)rows.Current;
if (row.RowNum == 0)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
if (row.Cells.Any(d => d.CellType == CellType.Error))
{
continue;
}
FCUImportCLassModel obj = new FCUImportCLassModel();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
switch (i)
{
case 0:
if (cell == null)
{
continue;
}
else
{
obj.IDSeq = SetCellValue(cell);
}
break;
case 1:
if (cell == null)
{
continue;
}
else
{
obj.SerialNo = SetCellValue(cell);
}
break;
case 2:
if (cell == null)
{
continue;
}
else
{
obj.Project = SetCellValue(cell);
}
break;
case 3:
if (cell == null)
{
continue;
}
else
{
obj.UnitTag = SetCellValue(cell);
}
break;
case 6:
if (cell == null)
{
continue;
}
else
{
obj.SalesOrder = SetCellValue(cell);
}
break;
case 7:
if (cell == null)
{
continue;
}
else
{
obj.Item = (cell.CellType.ToString() == "String" ? Convert.ToDecimal(cell.StringCellValue) : Convert.ToDecimal(cell.NumericCellValue));
};
break;
case 8:
if (cell == null)
{
continue;
}
else
{
obj.PartNo = SetCellValue(cell);
}
break;
case 9:
if (cell == null)
{
continue;
}
else
{
obj.Model = SetCellValue(cell);
}
break;
case 17:
if (cell == null)
{
continue;
}
if (cell.CellType.ToString() == "Formula")
{
if (cell.CachedFormulaResultType.ToString() == "Error")
{
continue;
}
else
{
obj.FanMotor1 = SetFormulaCellValue(cell);
}
}
else
{
obj.FanMotor1 = SetCellValue(cell);
}
break;
case 18:
if (cell == null)
{
continue;
}
if (cell.CellType.ToString() == "Formula")
{
if (cell.CachedFormulaResultType.ToString() == "Error")
{
continue;
}
else
{
obj.FanMotor2 = SetFormulaCellValue(cell);
}
}
else
{
obj.FanMotor2 = SetCellValue(cell);
}
break;
case 19:
if (cell == null)
{
continue;
}
if (cell.CellType.ToString() == "Formula")
{
if (cell.CachedFormulaResultType.ToString() == "Error")
{
continue;
}
else
{
obj.Qty = cell.CachedFormulaResultType.ToString() == "String" ? Convert.ToDecimal(cell.StringCellValue) : Convert.ToDecimal(cell.NumericCellValue);
}
}
else
{
obj.Qty = (cell.CellType.ToString() == "String" ? Convert.ToDecimal(cell.StringCellValue) : Convert.ToDecimal(cell.NumericCellValue));
}
break;
case 23:
if (cell == null)
{
continue;
}
if (cell.CellType.ToString() == "Formula")
{
if (cell.CachedFormulaResultType.ToString() == "Error")
{
continue;
}
else
{
obj.CoolingCoil1 = SetFormulaCellValue(cell);
}
}
else
{
obj.CoolingCoil1 = SetCellValue(cell);
}
break;
case 24:
if (cell == null)
{
continue;
}
if (cell.CellType.ToString() == "Formula")
{
if (cell.CachedFormulaResultType.ToString() == "Error")
{
continue;
}
else
{
obj.CoolingCoil2 = SetFormulaCellValue(cell);
}
}
else
{
obj.CoolingCoil2 = SetCellValue(cell);
}
break;
case 28:
if (cell == null)
{
continue;
}
if (cell.CellType.ToString() == "Formula")
{
if (cell.CachedFormulaResultType.ToString() == "Error")
{
continue;
}
else
{
obj.PowerSupply = SetFormulaCellValue(cell);
}
}
else
{
obj.PowerSupply = SetCellValue(cell);
}
break;
}
}
if (!string.IsNullOrEmpty(obj.SerialNo) && obj.SerialNo != "0")
{
PopulateRecords(obj, tempCollection);
}
}
#endregion Cells Comparison
if (tempCollection.Count() > 0)
{
IsImportBtnEnabled = true;
ImportFGCommand.RaiseCanExecuteChanged();
ExportFGCommand.RaiseCanExecuteChanged();
this.eventAggregator.GetEvent <ObjectEvent>().Publish(IsImportBtnEnabled);
FCU = new ListCollectionView(tempCollection);
FCU.SortDescriptions.Clear();
FCU.SortDescriptions.Add(new SortDescription("IDSeq", ListSortDirection.Ascending));
CollectionViewSource.GetDefaultView(FCU).Filter = Filter;
}
else
{
throw new Exception("There was no matched data detected.");
}
}
catch (Exception ex)
{
MessageBox.Show("Unable to read contents:\n\n" + ex.Message, "Error");
}
}