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