public IActionResult UploadFile(IFormFile file) { m_ExcelPartial model = new m_ExcelPartial(); ExcelHepler Helper = new ExcelHepler(_hostingEnvironment); if (file == null) { ISheet sheet = Helper.GetSheetSample(); //資料轉為Datatable DataTable sheetDt = Helper.GetDataTableFromExcel(sheet, true); //驗證資料 Helper.ExcelStackupCheck(sheetDt, model); return(PartialView("m_ExcelPartial", model)); } //存檔並返回檔案路徑 //string FilePath = Helper.SaveAndGetExcelPath(file); Stream stream = file.OpenReadStream(); //轉NPOI類型 XSSFWorkbook ExcelFile = new XSSFWorkbook(stream); ISheet xSSFSheet = ExcelFile.GetSheet("Stackup"); //資料轉為Datatable DataTable ExcelDt = Helper.GetDataTableFromExcel(xSSFSheet, true); //驗證資料 Helper.ExcelStackupCheck(ExcelDt, model); return(PartialView("m_ExcelPartial", model)); }
public IActionResult SaveCheckFile([FromBody] object model) { ExcelHepler Helper = new ExcelHepler(_hostingEnvironment); var jsonString = JsonConvert.SerializeObject(model); m_ExcelPartial ViewModel = JsonConvert.DeserializeObject <m_ExcelPartial>(jsonString); //存檔並返回檔案路徑 DataTable StackupDetalDt = Helper.GetDataTableFromStackupDetail(ViewModel.StackupDetalList); ////驗證資料 Helper.ExcelStackupCheck(StackupDetalDt, ViewModel); return(Json(ViewModel.Errmsg)); }
public IActionResult DownloadCheckFile([FromBody] object model) { ExcelHepler Helper = new ExcelHepler(_hostingEnvironment); FileService FileService = new FileService(_hostingEnvironment, _context); var jsonString = JsonConvert.SerializeObject(model); m_ExcelPartial ViewModel = JsonConvert.DeserializeObject <m_ExcelPartial>(jsonString); //取得範例 MemoryStream stream = Helper.ExportExcelSample(ViewModel.StackupDetalList); string FilePath = FileService.SaveAndGetExcelPath(stream); string FileName = Path.GetFileName(FilePath); return(Json(FileName)); }
//consume custom security service to get all roles public IActionResult Index() { FileService FileService = new FileService(_hostingEnvironment, _context); ParameterService ParameterService = new ParameterService(_context); m_ExcelPartial model = new m_ExcelPartial(); //取得要同步驗證的資料 model.FastLinkList = ParameterService.GetParameterList("Configuration_HomeLink").Where(x => x.IsSync == true).OrderBy(x => x.OrderNo).ToList(); //取得要同步驗證的連結 model.FastLinkFileList = FileService.GetFileList("Configuration_HomeLink").Where(x => model.FastLinkList.Select(pa => pa.ParameterID).Contains(x.SourceID)).ToList(); //取得Excel範本 PDC_File sample = FileService.GetFileList("ConfigurationSample").FirstOrDefault(); //加入Excel範本 if (sample != null) { model.FastLinkFileList.Add(sample); } return(View(model)); }
/// <summary> 驗證Stackup資料 /// /// </summary> /// <param name="ExcelDt">Stackup資料</param> /// <param name="model">返回ViewModel</param> public void ExcelStackupCheck(DataTable ExcelDt, m_ExcelPartial model) { model.Errmsg = string.Empty; List <string> NameList = new List <string>(); List <string> GroupNameList = new List <string>(); //Layer數字檢查結果 bool LayerNumCheck = false; //StackupType檢查結果 bool StackupTypeCheck = false; //Top檢查結果 bool TopCheck = false; //Bot檢查結果 bool BotCheck = false; //VCC檢查結果 bool VCCCheck = false; //GND檢查結果 bool GNDCheck = false; //SVCC檢查結果 bool SVCCCheck = false; //SGND檢查結果 bool SGNDCheck = false; //INx檢查結果 bool INxCheck = false; //INxNum檢查結果 bool INxNumCheck = false; //End檢查結果 bool EndCheck = false; decimal ThicknessTotal = 0; int ColIndex = 0; //第二行開始才是資料 for (int i = 1; i <= ExcelDt.Rows.Count - 1; i += 2) { //當前筆數 ColIndex += 1; //每筆有兩列 DataRow ColFirst = ExcelDt.Rows[i]; decimal Thickness1 = 0; if (Decimal.TryParse(ColFirst[7].ToString(), out Thickness1)) { ThicknessTotal += Thickness1; } //最後一筆疊構(Stack up)結尾是Solder Mask代表結束 if (ColFirst[6].ToString() == "Solder Mask") { if (i == (ExcelDt.Rows.Count - 1)) { break; } else { EndCheck = true; break; } } DataRow ColSecond = ExcelDt.Rows[i + 1]; decimal Thickness2 = 0; if (Decimal.TryParse(ColSecond[7].ToString(), out Thickness2)) { ThicknessTotal += Thickness2; } if (!string.IsNullOrWhiteSpace(ColFirst[2].ToString())) { NameList.Add(ColFirst[2].ToString()); } if (!string.IsNullOrWhiteSpace(ColFirst[3].ToString())) { GroupNameList.Add(ColFirst[3].ToString()); } int LayerIndex = 0; if (Int32.TryParse(System.Text.RegularExpressions.Regex.Replace(ColFirst[0].ToString(), @"[^0-9]+", ""), out LayerIndex)) { //判斷Layer欄位數字是否 if (LayerIndex != ColIndex) { LayerNumCheck = true; } } //判斷疊構類別只能有三種選項 if (!StackupType.Where(x => x.Contains(ColFirst[1].ToString())).Any() || (!StackupType.Where(x => x.Contains(ColSecond[1].ToString())).Any() && ColFirst[2].ToString() != "BOT")) { StackupTypeCheck = true; } //每個『Conductoe』or『Plane』的下一列必須固定帶上『Dielecatric』。 if (ColFirst[2].ToString() != "BOT" && (ColFirst[1].ToString() == "Conductor" || ColFirst[1].ToString() == "Plane")) { ColSecond[1] = "Dielectric"; } //固定值『TOP』B欄對應『Conductor』且欄位不可編輯,D欄對應『T』且欄位不可編輯,E欄 & F欄必須有值。 if (ColFirst[2].ToString() == "TOP") { ColFirst[1] = "Conductor"; ColSecond[1] = "Dielectric"; ColFirst[3] = "T"; if (string.IsNullOrWhiteSpace(ColFirst[4].ToString()) || string.IsNullOrWhiteSpace(ColFirst[5].ToString())) { TopCheck = true; } } //固定值『BOT』B欄對應『Conductor』且欄位不可編輯,D欄對應『B』且欄位不可編輯,E欄 & F欄必須有值。 if (ColFirst[2].ToString() == "BOT") { ColFirst[1] = "Conductor"; ColFirst[3] = "B"; if (string.IsNullOrWhiteSpace(ColFirst[4].ToString()) || string.IsNullOrWhiteSpace(ColFirst[5].ToString())) { BotCheck = true; } } //C欄=『INx』B欄對應『Conductor』且欄位不可編輯,D欄對應『數字』且欄位不可編輯, E欄 & F欄必須有值。(IN1=1, IN2=2, …最多到 9),不得重複。 if (ColFirst[2].ToString().StartsWith("IN")) { //判斷INx數字跟D欄『數字』是否對應 if (System.Text.RegularExpressions.Regex.Replace(ColFirst[2].ToString(), @"[^0-9]+", "").ToString() != ColFirst[3].ToString()) { INxNumCheck = true; } if (ColSecond[1].ToString() != "Conductor" || !string.IsNullOrWhiteSpace(ColFirst[3].ToString()) || string.IsNullOrWhiteSpace(ColFirst[4].ToString()) || string.IsNullOrWhiteSpace(ColFirst[5].ToString())) { INxCheck = true; } } //C欄=『VCCx』B欄對應『Conductor』且欄位不可編輯,D欄對應『不填值』且欄位不可編輯, E欄 & F欄必須有值。(VCC, VCC1 …最多到 9),不得重複。 if (ColFirst[2].ToString().StartsWith("VCC")) { if (ColSecond[1].ToString() != "Conductor" || !string.IsNullOrWhiteSpace(ColFirst[3].ToString()) || string.IsNullOrWhiteSpace(ColFirst[4].ToString()) || string.IsNullOrWhiteSpace(ColFirst[5].ToString())) { VCCCheck = true; } } //C欄=『SVCCx』B欄對應『Plane』且欄位不可編輯,D欄對應『不填值』且欄位不可編輯, E欄 & F欄『不填值』且欄位不可編輯。(SVCC, SVCC1 …最多到 9),不得重複。 if (ColFirst[2].ToString().StartsWith("SVCC")) { if (ColSecond[1].ToString() != "Plane" || !string.IsNullOrWhiteSpace(ColFirst[3].ToString()) || !string.IsNullOrWhiteSpace(ColFirst[4].ToString()) || !string.IsNullOrWhiteSpace(ColFirst[5].ToString())) { SVCCCheck = true; } } //C欄=『GNDx』B欄對應『Conductor』且欄位不可編輯,D欄對應『不填值』且欄位不可編輯, E欄 & F欄必須有值。(GND, GND1 …最多到 9),不得重複。 if (ColFirst[2].ToString().StartsWith("GND")) { if (ColSecond[1].ToString() != "Conductor" || !string.IsNullOrWhiteSpace(ColFirst[3].ToString()) || string.IsNullOrWhiteSpace(ColFirst[4].ToString()) || string.IsNullOrWhiteSpace(ColFirst[5].ToString())) { GNDCheck = true; } } //C欄=『SGNDx』B欄對應『Plane』且欄位不可編輯,D欄對應『不填值』且欄位不可編輯, E欄 & F欄『不填值』且欄位不可編輯。(SGND, SGND1, …最多到 9),不得重複。 if (ColFirst[2].ToString().StartsWith("SGND")) { if (ColSecond[1].ToString() != "Plane" || !string.IsNullOrWhiteSpace(ColFirst[3].ToString()) || !string.IsNullOrWhiteSpace(ColFirst[4].ToString()) || !string.IsNullOrWhiteSpace(ColFirst[5].ToString())) { SGNDCheck = true; } } } //第一筆資料是起始 if (ExcelDt.Rows[0][6].ToString() != "Solder Mask") { model.Errmsg += "第一筆起始疊構(Stack up)必需為Solder Mask\n"; } if (LayerNumCheck) { model.Errmsg += "Layer數字不可跳號\n"; } if (StackupTypeCheck) { model.Errmsg += "B欄:只有 Conductor、Dielectric、Plane三種項目。\n"; } if (TopCheck) { model.Errmsg += "固定值『TOP』,F欄 & G欄必須有值。\n"; } if (BotCheck) { model.Errmsg += "固定值『BOT』,F欄 & G欄必須有值。\n"; } if (VCCCheck) { model.Errmsg += "C欄=『VCCx』B欄對應『Conductor』且欄位不可編輯,D欄對應『不填值』且欄位不可編輯, E欄 & F欄必須有值。\n"; } if (NameList.Where(x => x.StartsWith("VCC")).Select(x => System.Text.RegularExpressions.Regex.Replace(x, @"[^0-9]+", "").ToString()).Where(x => x.Length > 1).Any()) { model.Errmsg += "VCC, VCC1 …最多到 9。\n"; } if (GNDCheck) { model.Errmsg += "C欄=『GNDx』B欄對應『Conductor』且欄位不可編輯,D欄對應『不填值』且欄位不可編輯, E欄 & F欄必須有值。\n"; } if (NameList.Where(x => x.StartsWith("GND")).Select(x => System.Text.RegularExpressions.Regex.Replace(x, @"[^0-9]+", "").ToString()).Where(x => x.Length > 1).Any()) { model.Errmsg += "GND, GND1 …最多到 9。\n"; } if (SVCCCheck) { model.Errmsg += "C欄=『SVCCx』B欄對應『Plane』且欄位不可編輯,D欄對應『不填值』且欄位不可編輯, E欄 & F欄『不填值』且欄位不可編輯。\n"; } if (NameList.Where(x => x.StartsWith("SVCC")).Select(x => System.Text.RegularExpressions.Regex.Replace(x, @"[^0-9]+", "").ToString()).Where(x => x.Length > 1).Any()) { model.Errmsg += "SVCC, SVCC1 …最多到 9。\n"; } if (SGNDCheck) { model.Errmsg += "C欄=『SGNDx』B欄對應『Plane』且欄位不可編輯,D欄對應『不填值』且欄位不可編輯, E欄 & F欄『不填值』且欄位不可編輯。\n"; } if (NameList.Where(x => x.StartsWith("SGND")).Select(x => System.Text.RegularExpressions.Regex.Replace(x, @"[^0-9]+", "").ToString()).Where(x => x.Length > 1).Any()) { model.Errmsg += "SGND, SGND1, …最多到 9。\n"; } if (INxCheck) { model.Errmsg += "C欄=『INx』B欄對應『Conductor』且欄位不可編輯, E欄 & F欄必須有值。\n"; } if (INxNumCheck) { model.Errmsg += "C欄=『INx』,D欄對應『數字』且欄位不可編輯。\n"; } if (NameList.Where(x => x.StartsWith("IN")).Select(x => System.Text.RegularExpressions.Regex.Replace(x, @"[^0-9]+", "").ToString()).Where(x => x.Length > 1).Any()) { model.Errmsg += "IN1=1, IN2=2, …最多到 9。\n"; } for (int i = 0; i <= GroupNameList.Count - 1; i++) { if (i == 0 && GroupNameList[i] != "T") { model.Errmsg += "D欄規則 = T + 數字(小至大自動排序)+ B。\n"; break; } if (i == (GroupNameList.Count - 1) && GroupNameList[i] != "B") { model.Errmsg += "D欄規則 = T + 數字(小至大自動排序)+ B。\n"; break; } int GroupNameNum1 = 0; int GroupNameNum2 = 0; if (i >= 2 && Int32.TryParse(GroupNameList[i - 1], out GroupNameNum1) && Int32.TryParse(GroupNameList[i], out GroupNameNum2)) { if ((GroupNameNum1 + 1) != GroupNameNum2) { model.Errmsg += "D欄規則 = T + 數字(小至大自動排序)+ B。\n"; break; } } else { model.Errmsg += "D欄規則 = T + 數字(小至大自動排序)+ B。\n"; break; } } if (EndCheck) { model.Errmsg += "最後一筆起始疊構(Stack up)必需為Solder Mask。\n"; } model.ExcelSheetDts.Add(ExcelDt); model.ThicknessTotal = decimal.Round(ThicknessTotal, 2); }