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); }