public static bool HeaderValidation(UploadFileImportModel model)
{
bool IsSucceed = true;
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(model.FileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
List <string> cellHeaderValueChecks = new List <string>()
{
"A6", "B6", "C6", "D6", "E6", "F6"
};
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(IsSucceed = false);
}
}
}
}
return(IsSucceed);
}
static void Main(string[] args)
{
//IExcelService excelService = new ModelTypeList();
//BaseExcel excel = new BaseExcel(excelService);
//string filePath = string.Empty;
//excel.ReadFile(filePath);
//ModelTypeImportExcel modelTypeImportExcel = new ModelTypeImportExcel();
//IModelTypeImportExcel ImodelTypeImportExcelService = modelTypeImportExcel;
//ReadExcel(new UploadFileImportModel {
// SavePathSuccess = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Import\\Test.xlsx"),
//});
UploadFileImportModel model = new UploadFileImportModel
{
FileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Import\\Test.xlsx")
};
int?value = ReadExcelOnThread(model);
//if (IsPreverifyExcel(model))
//{
// //Create Header
// // Service M_ModelTypeUpload Add
// UpdateModelTypeUploadHeader(model, new List<string>() { "A6", "B6", "C6", "D6", "E6" });
//}
Console.Read();
}
private static int ReadExcelOnThread(UploadFileImportModel model)
{
ModelTypeUploadModel value = new ModelTypeUploadModel();
Thread thReadExcel = new Thread(() => { value = ReadExcel(model); });
thReadExcel.Start();
return(value.UploadStatusID ?? 44);
}
public static int ImportExcel(UploadFileImportModel model)
{
if (!HeaderValidation(model))
{
return(7);
}
return(ReadExcelOnThread(model));
}
private static int?ReadExcelOnThread(UploadFileImportModel model)
{
List <ModelTypeTempSheetModel> value = new List <ModelTypeTempSheetModel>();
Thread thReadExcel = new Thread(() => { value = ReadExcel(model); });
thReadExcel.Start();
return(44);
}
private static void UpdateModelTypeUploadHeader(UploadFileImportModel model, List <string> headerAddress)
{
ModelTypeUploadModel updateModel = new ModelTypeUploadModel();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(model.FileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
foreach (Sheet sheet in workbookPart.Workbook.Sheets)
{
}
}
}
static void Main(string[] args)
{
var uploadModel = new UploadFileImportModel
{
FileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Import\\Test.xlsx"),
UploadBy = "SYSTEM",
UploadDate = DateTime.Now,
CreatedBy = "SYSTEM",
CreatedDate = DateTime.Now
};
IImportExcelFactory factory = new ImportExcelFactory();
ImportExcel import = new ImportExcel(factory);
import.MTList(uploadModel);
Console.Read();
}
private static void StagingTest(UploadFileImportModel uploadModel)
{
//int status = import.MTList(uploadModel);
var modelTypeUpload = ReadExcel(uploadModel);
ASHAOP_DEVEntities entities = new ASHAOP_DEVEntities();
// Add ModelTypeUpload
entities.M_ModelTypeUpload.Add(new M_ModelTypeUpload
{
CreatedBy = "SYSTEM",
CreatedDate = DateTime.Now,
UpdatedBy = "SYSTEM",
UpdatedDate = DateTime.Now,
M_ModelTypeTempSheet = modelTypeUpload.ModelTypeTempSheetModels.Select(sheet => new M_ModelTypeTempSheet
{
SheetNo = sheet.SheetNo,
YM = sheet.YM,
Model = sheet.Model,
Door = sheet.Door,
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();
}
public static ModelTypeUploadModel ReadExcel(UploadFileImportModel model)
{
ModelTypeUploadModel modelTypeUpload = new ModelTypeUploadModel();
List <List <string> > rowValues = new List <List <string> >();
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 (new[] { "A6", "B6", "C6", "E6", "F6" }.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;
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;
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(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 = 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;
modelTypeUpload.ModelTypeTempSheetModels.Add(sheetModel);
}
}
modelTypeUpload.UploadStatusID = 44; // wait edit
return(modelTypeUpload);
}
