public bool 匯入格式(string 處理路徑) { string tempFolder = System.IO.Path.GetTempPath(); // Get folder bool bcheck = true; if (!string.IsNullOrEmpty(處理路徑)) { try { DataSet ds = new DataSet(); string strCreateDate = File.GetCreationTime(處理路徑).ToString("MMdd"); //透過DataSet的ReadXml方法來讀取Xmlreader資料 ds.ReadXml(處理路徑); //建立DataTable並將DataSet中的第0個Table資料給DataTable DataTable dt = ds.Tables["ColorSize"]; //轉換數量類型 //dt.Columns.Add("數量", typeof(int), "Convert(Quantity,'System.Int32')"); dt.Columns.Add("數量", typeof(int)); DataTable dt1 = ds.Tables["PrePack"]; DataTable dt2 = ds.Tables["Shipment"]; DataTable dt3 = ds.Tables["Item"]; DataTable dt4 = ds.Tables["ePM_VerContent"]; string str客戶名稱 = dt4.Rows[0]["BuyerName"].ToString(); str客戶名稱 = (str客戶名稱.IndexOf("KO") == 0) ? "KHS" : "BLK"; string str版次 = dt4.Rows[0]["ePMVerNo"].ToString(); if (int.Parse(str版次) > 1) { str客戶名稱 += @"\新版次"; } DataTable dtColor = new DataTable(), dtSize = new DataTable(); dtColor = dt.DefaultView.ToTable(true, new string[] { "Color" }); dtSize = dt.DefaultView.ToTable(true, new string[] { "Size" }); DataTable TempTable = new DataTable(); TempTable.Columns.Add("訂單Style"); TempTable.Columns.Add("客戶PO"); TempTable.Columns.Add("目的地代號"); TempTable.Columns.Add("PO別"); TempTable.Columns.Add("Style"); TempTable.Columns.Add("顏色(英文)"); TempTable.Columns.Add("Size"); TempTable.Columns.Add("數量"); TempTable.Columns.Add("FOB單價"); TempTable.Columns.Add("DC_Date"); TempTable.Columns.Add("客戶交期(起)"); TempTable.Columns.Add("客戶交期(迄)"); TempTable.Columns.Add("客戶交期"); for (int i顏色數量 = 0; i顏色數量 < dtColor.Rows.Count; i顏色數量++) { for (int iSizeCount = 0; iSizeCount < dtSize.Rows.Count; iSizeCount++) { DataRow row; row = TempTable.NewRow(); //----不考慮多單價,所有item都會相同 row["訂單Style"] = dt3.Rows[0]["ItemNo"]; row["FOB單價"] = dt3.Rows[0]["ItemUnitPriceTotal"]; //---- row["客戶PO"] = dt2.Rows[0]["ShipmentBuyerOrderNo"]; row["客戶交期"] = dt2.Rows[0]["ShipmentDeliveryDate"]; row["顏色(英文)"] = dtColor.Rows[i顏色數量][0]; //row["Size"] = (dtSize.Rows[iSizeCount][0].ToString().IndexOf(" (") > 0) ? dtSize.Rows[iSizeCount][0].ToString().Substring(0, dtSize.Rows[iSizeCount][0].ToString().IndexOf(" (")) : dtSize.Rows[iSizeCount][0]; string str尺寸轉換 = 尺寸轉換(str客戶名稱, dtSize, iSizeCount); row["Size"] = str尺寸轉換; object obtest; obtest = dt.Compute("sum(數量)", "Color = '" + dtColor.Rows[i顏色數量][0].ToString() + "' and Size = '" + dtSize.Rows[iSizeCount][0] + "'"); row["數量"] = obtest.ToString().ToUpper().Trim(); if (string.IsNullOrEmpty(obtest.ToString())) { continue; } TempTable.Rows.Add(row); } } DataTableToExcel xx = new DataTableToExcel(); xx.ExcelWithNPOI(TempTable, strSavePath + str客戶名稱 + @"\款號" + dt3.Rows[0]["ItemNo"] + "版次" + str版次 + "_PO_" + dt2.Rows[0]["ShipmentBuyerOrderNo"].ToString() + "_" + strCreateDate + "_匯入格式.xlsx"); } catch (Exception ex) { sl.ErrorLog(ex, "匯入格式"); //ErrorLog(ex, "匯入格式"); bcheck = false; } } return(bcheck); }
public bool ERP格式(string 處理路徑) { //var savePath = Directory.GetFiles(strPath); string tempFolder = System.IO.Path.GetTempPath(); // Get folder bool bcheck = true; if (!string.IsNullOrEmpty(處理路徑)) { try { DataSet ds = new DataSet(); string strCreateDate = File.GetLastWriteTime(處理路徑).ToString("MMdd"); //透過DataSet的ReadXml方法來讀取Xmlreader資料 ds.ReadXml(處理路徑); //建立DataTable並將DataSet中的第0個Table資料給DataTable DataTable dt = ds.Tables["ColorSize"]; //轉換數量類型 //dt.Columns.Add("數量", typeof(int), "Convert(Quantity,'System.Int32')"); dt.Columns.Add("數量", typeof(int)); DataTable dt1 = ds.Tables["PrePack"]; DataTable dt2 = ds.Tables["Shipment"]; DataTable dt3 = ds.Tables["Item"]; DataTable dt4 = ds.Tables["ePM_VerContent"]; string str客戶名稱 = dt4.Rows[0]["12"].ToString(); //str客戶名稱= (str客戶名稱.IndexOf("KO") == 0) ? "KHS" : "BLK"; if (str客戶名稱.IndexOf("KO") == 0) { str客戶名稱 = "KHS"; } if (str客戶名稱.IndexOf("BELK") == 0) { str客戶名稱 = "BLK"; } if (str客戶名稱 != "KHS" && str客戶名稱 != "BLK") { str客戶名稱 = "Other"; } string str版次 = dt4.Rows[0]["ePMVerNo"].ToString(); if (int.Parse(str版次) > 1) { str客戶名稱 += @"\新版次"; } StrStyle = dt3.Rows[0]["ItemNo"].ToString(); DataTable dtColor = new DataTable(), dtSize = new DataTable(); dtColor = dt.DefaultView.ToTable(true, new string[] { "Color" }); dtSize = dt.DefaultView.ToTable(true, new string[] { "Size" }); DataTable TempTable = new DataTable(); TempTable.Columns.Add("訂單Style"); TempTable.Columns.Add("客戶PO"); TempTable.Columns.Add("目的地代號"); TempTable.Columns.Add("PO別"); TempTable.Columns.Add("Style"); TempTable.Columns.Add("FOB單價"); TempTable.Columns.Add("客戶交期"); TempTable.Columns.Add("顏色(英文)"); //if (dtSize.Rows.Count > 0) // for (int iSizeCount = 0; iSizeCount < dtSize.Rows.Count; iSizeCount++) // { // TempTable.Columns.Add(dtSize.Rows[iSizeCount][0].ToString().ToUpper().Trim()); // } if (dtSize.Rows.Count > 0) { for (int iSizeCount = 0; iSizeCount < dtSize.Rows.Count; iSizeCount++) { TempTable.Columns.Add(dtSize.Rows[iSizeCount][0].ToString().ToUpper().Trim()); //string str尺寸轉換 = 尺寸轉換(str客戶名稱, dtSize, iSizeCount); //TempTable.Columns.Add(str尺寸轉換); } } for (int i顏色數量 = 0; i顏色數量 < dtColor.Rows.Count; i顏色數量++) { DataRow row; row = TempTable.NewRow(); //----不考慮多單價,所有item都會相同 row["訂單Style"] = dt3.Rows[0]["ItemNo"]; row["FOB單價"] = dt3.Rows[0]["ItemUnitPriceTotal"]; //---- row["客戶PO"] = dt2.Rows[0]["ShipmentBuyerOrderNo"]; row["客戶交期"] = dt2.Rows[0]["ShipmentDeliveryDate"]; row["顏色(英文)"] = dtColor.Rows[i顏色數量][0]; for (int iSizeCount = 0; iSizeCount < dtSize.Rows.Count; iSizeCount++) { object obtest; obtest = dt.Compute("sum(數量)", "Color = '" + dtColor.Rows[i顏色數量][0].ToString() + "' and Size = '" + dtSize.Rows[iSizeCount][0] + "'"); row[dtSize.Rows[iSizeCount][0].ToString()] = obtest.ToString().ToUpper().Trim(); } TempTable.Rows.Add(row); } if (TempTable.Columns.Count > 0) { for (int i = 0; i < TempTable.Columns.Count; i++) { //if (TempTable.Columns[i].ColumnName.IndexOf(" (") > 0) //{ // //刪除多餘尺寸說明 // TempTable.Columns[i].ColumnName = TempTable.Columns[i].ColumnName.Substring(0, TempTable.Columns[i].ColumnName.IndexOf(" (")); //} //置換size if (i > 7) { string str尺寸轉換 = ""; using (var db = new GGFEntities()) { if (TempTable.Columns[i].ColumnName.ToString().ToUpper().Trim().IndexOf(" (") > 0) { str尺寸轉換 = TempTable.Columns[i].ColumnName.ToString().ToUpper().Trim().Substring(0, TempTable.Columns[i].ColumnName.ToString().ToUpper().Trim().IndexOf(" (")); } else { str尺寸轉換 = TempTable.Columns[i].ColumnName.ToString().ToUpper().Trim(); } var x = db.LFSize.Where(c => c.EDISize.ToUpper() == str尺寸轉換 && c.Cus_id == str客戶名稱).ToList(); foreach (var Size in x) { //有抓到資料覆蓋 str尺寸轉換 = Size.ERPSize; } TempTable.Columns[i].ColumnName = str尺寸轉換; } } } } DataTableToExcel xx = new DataTableToExcel(); xx.ExcelWithNPOI(TempTable, strSavePath + str客戶名稱 + @"\款號" + dt3.Rows[0]["ItemNo"] + "版次" + str版次 + "_PO_" + dt2.Rows[0]["ShipmentBuyerOrderNo"].ToString() + "_" + strCreateDate + "_ERP格式.xlsx"); } catch (Exception ex) { sl.ErrorLog(ex, "ERP格式"); //ErrorLog(ex,"ERP格式"); bcheck = false; } } return(bcheck); }