public int MTList(UploadFileImportModel uploadFileImportModel)
{
this.uploadFileImportModel = uploadFileImportModel;
this.threadImport = new Thread(new ThreadStart(CallThreadMTList));
this.threadImport.Start();
return(41); // return UploadStatusID
}
public ModelTypeUploadModel Import(UploadFileImportModel uploadFileImportModel)
{
// Todo Logic here
ModelTypeUploadModel model = new ModelTypeUploadModel();
if (IsHeaderValidate(uploadFileImportModel))
{
model = ReadExcel(uploadFileImportModel);
}
return(model);
}
private bool IsHeaderValidate(UploadFileImportModel uploadFileImportModel)
{
bool IsValid = true;
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(uploadFileImportModel.FileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
foreach (Sheet sheet in workbookPart.Workbook.Sheets)
{
WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
foreach (string columnName in cellHeaderValueChecks)
{
if (string.IsNullOrEmpty(GetCellValue(workbookPart, sheet, columnName)))
{
return(IsValid = false);
}
}
}
}
return(IsValid);
}
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 ModelTypeUploadModel KDLogistic(UploadFileImportModel uploadFileImportModel)
{
return(kDLogisticLT.ReadExcelFile(uploadFileImportModel));
}
public ModelTypeUploadModel ReadExcelFile(UploadFileImportModel uploadFileImportModel)
{
// Todo Logic here
return new ModelTypeUploadModel();
}
