private void btnRefer_Click(object sender, EventArgs e) { if (tmpSrc != null) { tmpSrc.Clear(); lblRowCshow.Text = "0"; } if (chkPartNum.Checked) { if (txtKeyIn.TextLength < 11) { sysMessage.sysMsg("您輸入的料號碼數不足,請確認!", "錯誤"); } else { tmpSrc = GetSQL.業務_訂單現況查詢(txtKeyIn.Text); tmpSrc.Columns.Remove("partnum"); tmpSrc.Columns.Remove("REVision"); tmpSrc.Columns.Remove("CustomerPartNum"); tmpSrc.Columns.Remove("serialnum"); tmpSrc.Columns.Remove("podate"); tmpSrc.Columns.Remove("expstkdate"); tmpSrc.Columns.Remove("proccode"); tmpSrc.Columns.Remove("LayerId"); tmpSrc.Columns.Remove("QntyXOut"); tmpSrc.Columns.Remove("QntyFGS"); tmpSrc.Columns.Remove("CustomerId"); tmpSrc.Columns.Remove("LotStatus"); tmpSrc.Columns.Remove("PO_Revision"); tmpSrc.Columns.Remove("isPO"); tmpSrc.Columns.Remove("scQuantity"); tmpSrc.Columns.Remove("sL_LL"); tmpSrc.Columns.Remove("LPiece"); tmpSrc.Columns.Remove("LLPiece"); tmpSrc.Columns.Remove("LLLPiece"); tmpSrc.Columns.Remove("rptType"); tmpSrc.Columns.Remove("ReportTitle"); tmpSrc.Columns["sPartNum"].SetOrdinal(0); tmpSrc.Columns["sPO_Revision"].SetOrdinal(1); tmpSrc.Columns["s_podate"].SetOrdinal(4); tmpSrc.Columns["s_expstkdate"].SetOrdinal(5); tmpSrc.Columns["sWIP_Revision"].SetOrdinal(7); dgvDataSrc.DataSource = tmpSrc; dgvDataSrcByPartnum(); int tmp = 0; for (int i = 0; i < dgvDataSrc.Rows.Count - 1; i++) { if (dgvDataSrc.Rows[i].Cells["sPO_Revision"].Value.ToString() != "") { tmp++; } } lblRowCshow.Text = Convert.ToString(tmp); } } else { int POType = 0; if (rdoPOType1.Checked) { POType = 1; } if (chkDateStart.Checked) { tmpSrc = GetSQL.業務_訂單未交貨明細表A(dtpDateS.Value.ToString("yyyy-MM-dd 00:00:00"), dtpDateE.Value.ToString("yyyy-MM-dd 23:59:59"), POType); } else if (chkCustomerID.Checked) { if (txtKeyIn.TextLength != 3) { sysMessage.sysMsg("您輸入的客戶代碼不足3碼或超過3碼,請確認!", "訊息"); return; } tmpSrc = GetSQL.業務_訂單未交貨明細表C(txtKeyIn.Text, POType); } else { tmpSrc = GetSQL.業務_訂單未交貨明細表B(dtpDateE.Value.ToString("yyyy-MM-dd 23:59:59"), POType); } string strNote = ""; if (tmpSrc.Rows.Count > 0) { PGB pgb = new PGB(); pgb.Text = "查詢中"; pgb.label1.Text = "比對現帳中.....請稍候"; pgb.progressBar1.Minimum = 0; pgb.progressBar1.Maximum = tmpSrc.Rows.Count; pgb.progressBar1.Step = 1; pgb.Show(); Application.DoEvents(); tmpSrc.Columns.Add("現帳備註", Type.GetType("System.String")); //依查出來的未交明細料號,下去跑製程現況 for (int i = 0; i < tmpSrc.Rows.Count; i++) { tmpMath = GetSQL.業務_訂單現況查詢(tmpSrc.Rows[i]["料號"].ToString().Trim()); pgb.progressBar1.Maximum += tmpMath.Rows.Count; //依查出來的現況,帶出製程、狀態、數量 for (int x = 0; x < tmpMath.Rows.Count; x++) { if (tmpMath.Rows[x]["proccode"].ToString().Trim() != "") { strNote = tmpMath.Rows[x]["ProcName"].ToString().Trim() + " " + tmpMath.Rows[x]["l0q"].ToString().Trim(); if (tmpSrc.Rows[i]["現帳備註"].ToString() == "") { tmpSrc.Rows[i]["現帳備註"] += strNote; } else { tmpSrc.Rows[i]["現帳備註"] += " / " + strNote; } } pgb.progressBar1.Value++; } pgb.progressBar1.Value++; } pgb.Dispose(); } dgvDataSrc.DataSource = tmpSrc; if (tmpSrc.Rows.Count == 0) { sysMessage.NoData(); return; } dgvDataSrcFormat(); lblRowCshow.Text = Convert.ToString(dgvDataSrc.Rows.Count - 1); } }
/// <summary> /// 將資料表轉成Eecel檔案 /// 若轉出並存檔成功,傳回true /// </summary> /// <param name="Source">要轉出的資料表陣列</param> /// <param name="SheetName">工作表名陣列</param> /// <param name="SavePath">存檔路徑及檔名</param> public static bool DataTableToExcel(DataTable[] Source, string[] SheetName, string SavePath) { bool result = false; IWorkbook workbook; ISheet Sheet; string filenameExtension = ".xls"; if (sysMessage.ExcelSaveRevi() == DialogResult.Yes) { filenameExtension = ".xlsx"; workbook = new XSSFWorkbook(); } else { workbook = new HSSFWorkbook(); } int rowCount = 0; for (int i = 0; i < Source.Length; i++) { for (int j = 0; j < Source[i].Rows.Count; j++) { rowCount++; } } PGB pgb = new PGB(); pgb.Text = "滙出Excel"; pgb.label1.Text = "正在滙出資料中...請稍候....."; pgb.progressBar1.Value = 0; pgb.progressBar1.Maximum = rowCount; pgb.progressBar1.Step = 1; pgb.Show(); Application.DoEvents(); for (int AllSheet = 0; AllSheet < Source.Length; AllSheet++) { if (Source[AllSheet].TableName != string.Empty | Source[AllSheet].TableName != "") { Sheet = workbook.CreateSheet(Source[AllSheet].TableName); } else { Sheet = workbook.CreateSheet(SheetName[AllSheet]); } Sheet.CreateRow(0); for (int i = 0; i < Source[AllSheet].Columns.Count; i++) { Sheet.GetRow(0).CreateCell(i).SetCellValue(Source[AllSheet].Columns[i].ColumnName); } for (int i = 0; i < Source[AllSheet].Rows.Count; i++) { Sheet.CreateRow(i + 1); for (int x = 0; x < Source[AllSheet].Columns.Count; x++) { Sheet.GetRow(i + 1).CreateCell(x).SetCellValue(Source[AllSheet].Rows[i][x].ToString()); } pgb.progressBar1.Value++; } //自動調整欄寬 for (int i = 0; i < Source[AllSheet].Columns.Count; i++) { Sheet.AutoSizeColumn(i); } } FileStream SaveFile = new FileStream(SavePath + filenameExtension, FileMode.Create); try { workbook.Write(SaveFile); result = true; } catch (Exception ex) { sysMessage.SystemEx(ex.Message); } SaveFile.Close(); pgb.Dispose(); return result; }
private void btnChkNAS_Click(object sender, EventArgs e) { if (dgvData.DataSource == null | dgvData.Rows.Count - 1 == 0) { sysMessage.NoData(); } else { DataTable tmpDT = new DataTable(); tmpDT.Columns.Add("料號"); tmpDT.Columns.Add("狀態"); PGB pgb = new PGB(); pgb.Text = "檢查中"; pgb.label1.Text = "檢查料號中,請稍候....."; pgb.progressBar1.Minimum = 0; pgb.progressBar1.Maximum = dgvData.Rows.Count - 1; pgb.progressBar1.Value = 0; pgb.Show(); Application.DoEvents(); string Path = @"\\n5200xxx\LDI_Source\Inn Layer"; string FilePath = ""; if (rdo310.Checked) { Path = @"\\n5200xxx\LDI_Source\Out Layer"; } for (int i = 0; i < dgvData.Rows.Count - 1; i++) { FilePath = Path + @"\" + dgvData.Rows[i].Cells["料號"].Value.ToString().Substring(0, 3); if (!System.IO.Directory.Exists(FilePath)) { DataRow row = tmpDT.NewRow(); row["料號"] = dgvData.Rows[i].Cells["料號"].Value.ToString(); row["狀態"] = "客戶代號資料夾不存在"; tmpDT.Rows.Add(row); } else { int result = 0; string[] Directory = System.IO.Directory.GetDirectories(FilePath); for (int x = 0; x < Directory.Length; x++) { if (Directory[x].Contains(dgvData.Rows[i].Cells["料號"].Value.ToString().Trim())) { result++; } } if (result == 0) { DataRow row = tmpDT.NewRow(); row["料號"] = dgvData.Rows[i].Cells["料號"].Value.ToString(); row["狀態"] = "料號資料夾不存在"; tmpDT.Rows.Add(row); } } pgb.progressBar1.Value++; } pgb.Dispose(); if (tmpDT.Rows.Count == 0) { sysMessage.sysMsg("檢查結果:OK", "訊息"); } else { tmpDT.DefaultView.Sort = "料號"; NewRTB nrtb = new NewRTB(); for (int i = 0; i < tmpDT.Rows.Count; i++) { nrtb.rtbMain.Text += tmpDT.Rows[i]["料號"].ToString().Trim() + " - " + tmpDT.Rows[i]["狀態"].ToString() + "\n"; } nrtb.Show(); } } }
/// <summary> /// 將Excel檔案轉成資料表陣列 /// </summary> /// <param name="strFilePath">檔案路徑</param> /// <returns>DataTable Array</returns> public static DataTable[] ExcelToDataTable(string strFilePath) { DataTable[] result; IWorkbook wb; ISheet hst; IRow HeaderRow; IRow row; using (FileStream fs = new FileStream(strFilePath, FileMode.Open)) { if (strFilePath.Contains(".xlsx")) { wb = new XSSFWorkbook(fs); hst = new XSSFSheet(); } else { wb = new HSSFWorkbook(fs); hst = new HSSFSheet((HSSFWorkbook)wb); } result = new DataTable[wb.NumberOfSheets]; try { PGB pgb = new PGB(); pgb.Text = "滙入資料"; pgb.label1.Text = "滙入資料中,請稍候....."; pgb.Show(); Application.DoEvents(); for (int i = 0; i < wb.NumberOfSheets; i++) { hst = wb.GetSheetAt(i); HeaderRow = hst.GetRow(0); result[i] = new DataTable(); pgb.progressBar1.Minimum = 0; pgb.progressBar1.Value = 0; if (HeaderRow == null & i == wb.NumberOfSheets) { break; } else if (HeaderRow == null & i < wb.NumberOfSheets) { goto Next; } pgb.progressBar1.Maximum = HeaderRow.LastCellNum + hst.LastRowNum; pgb.progressBar1.Step = 1; //寫入欄位標題 for (int h = HeaderRow.FirstCellNum; h < HeaderRow.LastCellNum; h++) { if (HeaderRow.GetCell(h) != null) { result[i].Columns.Add(new DataColumn(HeaderRow.GetCell(h).StringCellValue)); } else { result[i].Columns.Add(new DataColumn("")); } pgb.progressBar1.Value++; } //寫入欄位資料 for (int j = (hst.FirstRowNum + 1); j <= hst.LastRowNum; j++) { row = hst.GetRow(j); if (row == null) continue; DataRow dtRow = result[i].NewRow(); for (int x = row.FirstCellNum; x < row.LastCellNum; x++) { if (row.GetCell(x) != null) { //由NPOI內提供的類別庫判斷公式類型 IFormulaEvaluator iFormula = WorkbookFactory.CreateFormulaEvaluator(wb); var formulaType = iFormula.Evaluate(row.GetCell(x)).CellType; //若判斷結果類型為數值(注意:日期結果會被轉為數值) if (formulaType == CellType.Numeric) { ICell cell = iFormula.EvaluateInCell(row.GetCell(x)); // 判斷結果類型是否為日期 if (DateUtil.IsCellDateFormatted(cell)) { //設定日期格式 //ICellStyle cellStyle = wb.CreateCellStyle(); //IDataFormat format = wb.CreateDataFormat(); //cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); //row.GetCell(x).CellStyle = cellStyle; //dtRow[x] = row.GetCell(x).ToString(); dtRow[x] = row.GetCell(x).DateCellValue.ToString("yyyy-MM-dd"); } else { dtRow[x] = row.GetCell(x).NumericCellValue; } } //判斷結果類型是否為字串 else if (formulaType == CellType.String) { dtRow[x] = row.GetCell(x).StringCellValue; } else { dtRow[x] = row.GetCell(x).ToString(); } } } result[i].Rows.Add(dtRow); pgb.progressBar1.Value++; } Next:; } pgb.Dispose(); } catch (Exception ex) { sysMessage.SystemEx(ex.Message); } return result; } }
private void btnUpload_Click(object sender, EventArgs e) { string strFilePath = ""; OpenFileDialog openFile = new OpenFileDialog(); if (openFile.ShowDialog() == DialogResult.OK) { strFilePath = openFile.FileName; DataTable[] result = ewPour.ExcelToDataTable(strFilePath); dgvSource.DataSource = result[0]; dgvFormat(); DataTable tmpDT = result[0].Copy(); lblSourceNumShow.Text = Convert.ToString(dgvSource.Rows.Count - 1); PGB pgb = new PGB(); pgb.Text = "資料配對"; pgb.label1.Text = "資料配對中,請稍候....."; pgb.progressBar1.Minimum = 0; pgb.progressBar1.Maximum = tmpDT.Rows.Count; pgb.Show(); Application.DoEvents(); double[] tmp = new double[2]; DataTable dtTmp = new DataTable(); dtTmp.Columns.Add("PartNum"); dtTmp.Columns.Add("Revision"); dtTmp.Columns.Add("LotNum"); dtTmp.Columns.Add("ProcCode"); dtTmp.Columns.Add("PP"); dtTmp.Columns.Add("PartNum2"); dtTmp.Columns.Add("Revision2"); dtTmp.Columns.Add("LotNum2"); dtTmp.Columns.Add("ProcCode2"); dtTmp.Columns.Add("PP2"); dtTmp.Columns.Add("Program"); dtTmp.Columns["PartNum"].ColumnName = "料號"; dtTmp.Columns["Revision"].ColumnName = "版本"; dtTmp.Columns["LotNum"].ColumnName = "批號"; dtTmp.Columns["ProcCode"].ColumnName = "途程"; dtTmp.Columns["PP"].ColumnName = "品名"; dtTmp.Columns["PartNum2"].ColumnName = "混壓料號"; dtTmp.Columns["Revision2"].ColumnName = "混壓版本"; dtTmp.Columns["LotNum2"].ColumnName = "混壓批號"; dtTmp.Columns["ProcCode2"].ColumnName = "混壓途程"; dtTmp.Columns["PP2"].ColumnName = "混壓品名"; dtTmp.Columns["Program"].ColumnName = "壓機程式"; //##### DataTable在使用Delete()時,會使Count改變,所以要使用倒序檢查,才不會因Count變動而導致比對不完整 #####// //先拿掉超過8層板的料號 for (int i = tmpDT.Rows.Count - 1; i >= 0; i--) { //若為10層板以上,層別代號非數字,先行直接刪除 if (ewCheck.ChkLetter(tmpDT.Rows[i]["料號"].ToString().Substring(4, 1))) { tmpDT.Rows[i].Delete(); } else { if (Convert.ToInt32(tmpDT.Rows[i]["料號"].ToString().Substring(4, 1)) > 8) { tmpDT.Rows[i].Delete(); } } pgb.progressBar1.Value++; } tmpDT.AcceptChanges(); pgb.progressBar1.Maximum += (tmpDT.Rows.Count * tmpDT.Rows.Count); //進行計算,計算板子的長短邊距是否有小於1.8inch for (int i = 0; i < tmpDT.Rows.Count; i++) { for (int x = 0; x < tmpDT.Rows.Count; x++) { tmp = ewMath.BlendPress(Convert.ToDouble(tmpDT.Rows[i]["L"]), Convert.ToDouble(tmpDT.Rows[x]["L"]), Convert.ToDouble(tmpDT.Rows[i]["W"]), Convert.ToDouble(tmpDT.Rows[x]["W"])); if (tmp[0] < 1.8 & tmp[1] < 1.8) { DataRow row = dtTmp.NewRow(); row["料號"] = tmpDT.Rows[i]["料號"].ToString().Trim(); row["版本"] = tmpDT.Rows[i]["版本"].ToString().Trim(); row["批號"] = tmpDT.Rows[i]["批號"].ToString().Trim(); row["品名"] = tmpDT.Rows[i]["品名"].ToString().Trim(); row["混壓料號"] = tmpDT.Rows[x]["料號"].ToString().Trim(); row["混壓版本"] = tmpDT.Rows[x]["版本"].ToString().Trim(); row["混壓批號"] = tmpDT.Rows[x]["批號"].ToString().Trim(); row["混壓品名"] = tmpDT.Rows[x]["品名"].ToString().Trim(); dtTmp.Rows.Add(row); } pgb.progressBar1.Value++; } } //取得該批號在傑偲的現帳途程 pgb.progressBar1.Maximum += dtTmp.Rows.Count; for (int i = 0; i < dtTmp.Rows.Count; i++) { dtTmp.Rows[i]["途程"] = GetSQL.FMEdProcCode(dtTmp.Rows[i]["批號"].ToString()); dtTmp.Rows[i]["混壓途程"] = GetSQL.FMEdProcCode(dtTmp.Rows[i]["混壓批號"].ToString()); pgb.progressBar1.Value++; } //拿掉二邊重覆的料號 pgb.progressBar1.Maximum += dtTmp.Rows.Count; for (int i = dtTmp.Rows.Count - 1; i >= 0; i--) { if (dtTmp.Rows[i]["料號"].ToString().Trim() == dtTmp.Rows[i]["混壓料號"].ToString().Trim()) { dtTmp.Rows[i].Delete(); } pgb.progressBar1.Value++; } dtTmp.AcceptChanges(); //拿掉4層板配8層板的結果 pgb.progressBar1.Maximum += dtTmp.Rows.Count; for (int i = dtTmp.Rows.Count - 1; i >= 0; i--) { if ((Convert.ToInt32(dtTmp.Rows[i]["料號"].ToString().Trim().Substring(4, 1)) == 4 & Convert.ToInt32(dtTmp.Rows[i]["混壓料號"].ToString().Trim().Substring(4, 1)) == 8) | (Convert.ToInt32(dtTmp.Rows[i]["料號"].ToString().Trim().Substring(4, 1)) == 8 & Convert.ToInt32(dtTmp.Rows[i]["混壓料號"].ToString().Trim().Substring(4, 1)) == 4)) { dtTmp.Rows[i].Delete(); } pgb.progressBar1.Value++; } dtTmp.AcceptChanges(); //拿掉二邊不同PP廠商的料號,NY=南亞、HT=宏泰 /* 2016-08-18 又取消掉不同廠商不可混壓的規則 pgb.progressBar1.Maximum += dtTmp.Rows.Count; for (int i = dtTmp.Rows.Count - 1; i >= 0; i--) { if (dtTmp.Rows[i]["品名"].ToString().Contains("NY") & dtTmp.Rows[i]["混壓品名"].ToString().Contains("HT")) { dtTmp.Rows[i].Delete(); } else if (dtTmp.Rows[i]["品名"].ToString().Contains("HT") & dtTmp.Rows[i]["混壓品名"].ToString().Contains("NY")) { dtTmp.Rows[i].Delete(); } pgb.progressBar1.Value++; } dtTmp.AcceptChanges(); */ //拿掉4層板不可混壓的料號,PP為1080,但TG不是140及155 pgb.progressBar1.Maximum += dtTmp.Rows.Count; for (int i = dtTmp.Rows.Count - 1; i >= 0; i--) { //檢查左、右二邊料號是不是4層板 if (Convert.ToInt32(dtTmp.Rows[i]["料號"].ToString().Substring(4, 1)) == 4 & Convert.ToInt32(dtTmp.Rows[i]["混壓料號"].ToString().Substring(4, 1)) == 4) { //檢查左、右二邊PP是不是1080,若不是1080就都拿掉 if (dtTmp.Rows[i]["品名"].ToString().Contains("1080") & dtTmp.Rows[i]["混壓品名"].ToString().Contains("1080")) { //檢查TG是不是140、155,若不是就拿掉 if (dtTmp.Rows[i]["品名"].ToString().Contains("140") | dtTmp.Rows[i]["品名"].ToString().Contains("155")) { if (!dtTmp.Rows[i]["混壓品名"].ToString().Contains("140") && !dtTmp.Rows[i]["混壓品名"].ToString().Contains("155")) { dtTmp.Rows[i].Delete(); } } } else { dtTmp.Rows[i].Delete(); } } pgb.progressBar1.Value++; } dtTmp.AcceptChanges(); // pgb.progressBar1.Maximum += dtTmp.Rows.Count; for (int i = dtTmp.Rows.Count - 1; i >= 0; i--) { if (Convert.ToInt32(dtTmp.Rows[i]["料號"].ToString().Substring(4, 1)) > 4 & Convert.ToInt32(dtTmp.Rows[i]["混壓料號"].ToString().Substring(4, 1)) == 4) { if (dtTmp.Rows[i]["品名"].ToString().Contains("1080") & dtTmp.Rows[i]["混壓品名"].ToString().Contains("1080")) { dtTmp.Rows[i].Delete(); } } } dtTmp.AcceptChanges(); //DataTable tmpProgram = GetSQL.壓合熱壓程式表(); pgb.progressBar1.Maximum += dtTmp.Rows.Count; for (int i = 0; i < dtTmp.Rows.Count; i++) { if (dtTmp.Rows[i]["品名"].ToString().Contains("1080") & dtTmp.Rows[i]["混壓品名"].ToString().Contains("1080")) { if ((dtTmp.Rows[i]["品名"].ToString().Contains("TG140") & dtTmp.Rows[i]["混壓品名"].ToString().Contains("TG155")) | (dtTmp.Rows[i]["品名"].ToString().Contains("TG155") & dtTmp.Rows[i]["混壓品名"].ToString().Contains("TG140"))) { dtTmp.Rows[i]["壓機程式"] = "1080PP-HT150"; } else { dtTmp.Rows[i]["壓機程式"] = "1080PP-NY140"; } } } pgb.Dispose(); srcResult.DataSource = dtTmp; dgvResult.DataSource = srcResult; dgvResultFormat(); lblResultNumShow.Text = Convert.ToString(dgvResult.Rows.Count - 1); } }