private static List <ModelTypeTempSheetModel> ReadExcel(UploadFileImportModel model)
{
ModelTypeUploadModel modelTypeUploadModel = new ModelTypeUploadModel();
List <List <string> > rowValues = new List <List <string> >();
string fileName = model.FileName;
List <ModelTypeTempSheetModel> sheetModels = new List <ModelTypeTempSheetModel>();
ModelTypeTempSheetModel sheetModel;
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(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>();
//Find index header
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;
foreach (Cell cell in rows.ElementAt(5).Descendants <Cell>())
{
if (new[] { "A6", "B6", "C6", "E6" }.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 "E6":
sheetModel.Plant = value;
break;
}
}
}
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;
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);
int sequence = 1;
#region Engine
if (new[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" }.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(GetMergeCellEndPosition(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 = sequence
};
sequence++;
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 = sequence
};
typeModels.Add(typeModel);
}
#endregion
#region VIN
if (currentIndex == indexVinStart)
{
modelTypeTempRowModel.VIN = currentCellValue;
}
#endregion
cellValues.Add(currentCellValue);
}
// End Cell
preRow = cellValues;
rowValues.Add(cellValues);
modelTypeTempRowModel.modelTypeTempEngines.Add(engineModel);
modelTypeTempRowModel.modelTypeTempEquipmentModels.AddRange(equipmentModels);
modelTypeTempRowModel.modelTypeTempTypeModels.AddRange(typeModels);
sheetModel.modelTypeTempRowModels.Add(modelTypeTempRowModel);
}
//End Row
sheetModel.SheetNo = ++sheetCount;
sheetModels.Add(sheetModel);
}
}
return(sheetModels);
}