public ModelTypeUploadModel ReadExcel(UploadFileImportModel model)
{
ModelTypeUploadModel modelTypeUpload = new ModelTypeUploadModel
{
UpdatedBy = model.UploadBy,
UpdatedDate = model.UploadDate,
CreatedBy = model.CreatedBy,
CreatedDate = model.CreatedDate
};
List <ModelTypeTempSheetModel> sheetModels = new List <ModelTypeTempSheetModel>();
ModelTypeTempSheetModel sheetModel;
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(model.FileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
int sheetCount = 0;
foreach (Sheet sheet in workbookPart.Workbook.Sheets)
{
sheetModel = new ModelTypeTempSheetModel();
WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
Worksheet worksheet = workbookPart.WorksheetParts.First().Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
List <Row> rows = sheetData.Descendants <Row>().ToList();
List <string> preRow = new List <string>();
List <string> cellValues = new List <string>();
int indexMainEquipStart = 0;
int indexPNoStart = 0;
int indexTypeStart = 0;
int indexVinStart = 0;
int indexEngineSerialNoStart = 0;
int indexErrorDescriptionStart = 0;
int inedexRowOfHeader_Start = 6;
int indexRowOfHeader_End = 8;
// Assign header value
foreach (Cell cell in rows.ElementAt(5).Descendants <Cell>())
{
if (cellHeaderValueChecks.Contains(cell.CellReference.Value))
{
string value = GetCellValue(workbookPart, sheet, cell.CellReference);
switch (cell.CellReference.Value)
{
case "A6":
sheetModel.YM = value;
break;
case "B6":
sheetModel.Model = value;
break;
case "C6":
sheetModel.Door = value;
break;
case "D6":
sheetModel.Engine = value;
break;
case "E6":
sheetModel.Plant = value;
break;
case "F6":
sheetModel.Status = value;
break;
}
}
}
// Find column header index
for (var i = inedexRowOfHeader_Start; i < indexRowOfHeader_End; i++)
{
foreach (Cell cell in rows.ElementAt(i).Descendants <Cell>())
{
if (GetCellValue(workbookPart, sheet, cell.CellReference) == "MAIN EQUIPMENT")
{
indexMainEquipStart = GetColumnIndex(cell.CellReference);
}
if (GetCellValue(workbookPart, sheet, cell.CellReference) == "P.No.")
{
indexPNoStart = GetColumnIndex(cell.CellReference);
}
if (GetCellValue(workbookPart, sheet, cell.CellReference) == "TYPE")
{
indexTypeStart = GetColumnIndex(cell.CellReference);
}
if (GetCellValue(workbookPart, sheet, cell.CellReference) == "VIN")
{
indexVinStart = GetColumnIndex(cell.CellReference);
}
if (GetCellValue(workbookPart, sheet, cell.CellReference) == "ENGINE SERIAL No.")
{
indexEngineSerialNoStart = GetColumnIndex(cell.CellReference);
}
if (GetCellValue(workbookPart, sheet, cell.CellReference) == "Error Description")
{
indexErrorDescriptionStart = GetColumnIndex(cell.CellReference);
}
}
}
ModelTypeTempRowModel modelTypeTempRowModel;
ModelTypeTempEngineModel engineModel;
List <ModelTypeTempEquipmentModel> equipmentModels;
ModelTypeTempEquipmentModel equipmentModel;
List <ModelTypeTempTypeModel> typeModels;
ModelTypeTempTypeModel typeModel;
for (var i = 9; i < rows.Count; i++)
{
modelTypeTempRowModel = new ModelTypeTempRowModel();
equipmentModels = new List <ModelTypeTempEquipmentModel>();
typeModel = new ModelTypeTempTypeModel();
typeModels = new List <ModelTypeTempTypeModel>();
cellValues = new List <string>();
engineModel = new ModelTypeTempEngineModel();
modelTypeTempRowModel.RowNo = i + 1;
int seqEquipment = 1;
int seqType = 1;
foreach (Cell cell in rows.ElementAt(i).Cast <Cell>())
{
string currentColumn = GetColumnName(cell.CellReference);
int currentIndex = GetColumnIndex(cell.CellReference);
string currentCellValue = GetCellValue(workbookPart, sheet, cell.CellReference);
#region Engine
if (engineColumn.Contains(GetColumnName(cell.CellReference)))
{
#region Replace Value
if (cell.CellReference == "A" + (i + 1))
{
if (string.IsNullOrEmpty(currentCellValue))
{
currentCellValue = preRow[0];
}
}
if (cell.CellReference == "B" + (i + 1))
{
if (string.IsNullOrEmpty(currentCellValue))
{
currentCellValue = preRow[1];
}
}
if (cell.CellReference == "C" + (i + 1))
{
if (string.IsNullOrEmpty(currentCellValue))
{
currentCellValue = preRow[2];
}
}
if (cell.CellReference == "D" + (i + 1))
{
if (string.IsNullOrEmpty(currentCellValue))
{
currentCellValue = preRow[3];
}
}
if (cell.CellReference == "E" + (i + 1))
{
if (string.IsNullOrEmpty(currentCellValue))
{
currentCellValue = preRow[4];
}
}
#endregion
switch (GetColumnName(cell.CellReference))
{
case "A":
engineModel.SS = currentCellValue;
break;
case "B":
engineModel.DISP = currentCellValue;
break;
case "C":
engineModel.COMCARB = currentCellValue;
break;
case "D":
engineModel.Grade = currentCellValue;
break;
case "E":
engineModel.Mis = currentCellValue;
break;
case "F":
engineModel.ModelCode01 = currentCellValue;
break;
case "G":
engineModel.ModelCode02 = currentCellValue;
break;
case "H":
engineModel.ModelCode03 = currentCellValue;
break;
case "I":
engineModel.ModelCode04 = currentCellValue;
break;
case "J":
engineModel.ModelCode05 = currentCellValue;
break;
}
}
#endregion
#region MAIN EQUIPMENT
string columnEndGetEquipment = GetColumnName(GetEndColumnMergeCell(workbookPart, sheet, "K7"));
int indexMainEquipEnd = GetColumnIndex(columnEndGetEquipment);
if (currentIndex >= indexMainEquipStart && currentIndex <= indexMainEquipEnd) // Start K Column
{
equipmentModel = new ModelTypeTempEquipmentModel
{
EquipmentName = GetCellValue(workbookPart, sheet, currentColumn + 9),
EquipmentValue = currentCellValue,
Sequence = seqEquipment
};
seqEquipment++;
equipmentModels.Add(equipmentModel);
}
#endregion
#region PNo
if (currentIndex == indexPNoStart)
{
modelTypeTempRowModel.PNo = currentCellValue;
}
#endregion
#region TYPE
if (currentIndex >= indexTypeStart && currentIndex <= indexVinStart - 1)
{
typeModel = new ModelTypeTempTypeModel
{
ModelType = GetCellValue(workbookPart, sheet, currentColumn + 9),
ModelCode = currentCellValue,
Sequence = seqType
};
seqType++;
typeModels.Add(typeModel);
}
#endregion
#region VIN
if (currentIndex == indexVinStart)
{
modelTypeTempRowModel.VIN = currentCellValue;
}
#endregion
cellValues.Add(currentCellValue);
}
// End Cell
preRow = cellValues;
modelTypeTempRowModel.ModelTypeTempEngines.Add(engineModel);
modelTypeTempRowModel.ModelTypeTempEquipmentModels.AddRange(equipmentModels);
modelTypeTempRowModel.ModelTypeTempTypeModels.AddRange(typeModels);
sheetModel.ModelTypeTempRowModels.Add(modelTypeTempRowModel);
}
//End Row
sheetModel.SheetNo = ++sheetCount;
modelTypeUpload.ModelTypeTempSheetModels.Add(sheetModel);
}
}
modelTypeUpload.UploadStatusID = 44; // wait edit
//StagingTest(modelTypeUpload);
return(modelTypeUpload);
}
public bool AddModelTypeUpload(ModelTypeUploadModel model)
{
try
{
bool IsSucceed = false;
ASHAOP_DEVEntities entities = new ASHAOP_DEVEntities();
// Add ModelTypeUpload
entities.M_ModelTypeUpload.Add(new M_ModelTypeUpload
{
CreatedBy = model.CreatedBy,
CreatedDate = DateTime.Now,
UpdatedBy = model.UpdatedBy,
UpdatedDate = DateTime.Now,
M_ModelTypeTempSheet = model.ModelTypeTempSheetModels.Select(sheet => new M_ModelTypeTempSheet
{
SheetNo = sheet.SheetNo,
YM = sheet.YM,
Model = sheet.Model,
Door = sheet.Door,
Engine = sheet.Engine,
Plant = sheet.Plant,
Status = sheet.Status,
// Add M_ModelTypeTempRow
M_ModelTypeTempRow = sheet.ModelTypeTempRowModels.Select(row => new M_ModelTypeTempRow
{
RowNo = row.RowNo,
PNo = row.PNo,
VIN = row.VIN,
ErrorMessage = row.ErrorMessage,
// Add M_ModelTypeTempEngine
M_ModelTypeTempEngine = row.ModelTypeTempEngines.Select(engine => new M_ModelTypeTempEngine
{
SS = engine.SS,
DISP = engine.DISP,
COMCARB = engine.COMCARB,
Grade = engine.Grade,
Mis = engine.Mis,
ModelCode01 = engine.ModelCode01,
ModelCode02 = engine.ModelCode02,
ModelCode03 = engine.ModelCode03,
ModelCode04 = engine.ModelCode04,
ModelCode05 = engine.ModelCode05
}).ToList(),
// Add M_ModelTypeTempEquipment
M_ModelTypeTempEquipment = row.ModelTypeTempEquipmentModels.Select(equip => new M_ModelTypeTempEquipment
{
EquipmentName = equip.EquipmentName,
EquipmentValue = equip.EquipmentValue,
Sequence = equip.Sequence
}).ToList(),
// Add M_ModelTypeTempType
M_ModelTypeTempType = row.ModelTypeTempTypeModels.Select(type => new M_ModelTypeTempType
{
ModelType = type.ModelType,
ModelCode = type.ModelCode,
Sequence = type.Sequence
}).ToList()
}).ToList(),
}).ToList()
});
entities.SaveChanges();
IsSucceed = true;
return(IsSucceed);
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
}
catch (Exception e)
{
throw e;
}
}
