/// <summary> /// 將Session的資料上傳到資料庫 /// </summary> public void F_UpLoad() { //有錯誤資料不給匯入 if (!F_CheckDateData()) { F_ErrorShow("已有匯入資料"); DeleteBT.Visible = true; } else if (Session[StrAMZCapacity] != null && Session[StrAMZGuidance] != null) { DataTable dt = (DataTable)Session[StrAMZCapacity]; DataTable dtDataDefine = (DataTable)Session[StrAMZCapacity + "Define"]; DataTable dt1 = (DataTable)Session[StrAMZGuidance]; DataTable dtDataDefine1 = (DataTable)Session[StrAMZGuidance + "Define"]; int iIndex = GetExcelIdex(Str匯入Head); string StrErrorIDLog = ""; if (iIndex > 0) { using (SqlConnection conn1 = new SqlConnection(strConnectString)) { SqlCommand sqlcommand = conn1.CreateCommand(); SqlTransaction transaction1; conn1.Open(); transaction1 = conn1.BeginTransaction("createExcelImport"); sqlcommand.Connection = conn1; sqlcommand.Transaction = transaction1; try { #region 匯入明細 F_UpdateCommand(dt, dtDataDefine, iIndex, ref StrErrorIDLog, sqlcommand, StrAMZCapacity); F_UpdateCommand(dt1, dtDataDefine1, iIndex, ref StrErrorIDLog, sqlcommand, StrAMZGuidance); //上傳成功更新Head狀態 sqlcommand.CommandText = string.Format(@"UPDATE {0} SET IsUpDate = 1 WHERE id = {1} ", Str匯入Head, iIndex); sqlcommand.ExecuteNonQuery(); #endregion transaction1.Commit(); F_ErrorShow("上傳成功"); CapacityErrorGV.DataSource = null; GuidanceErrorGV.DataSource = null; CapacityErrorGV.DataBind(); GuidanceErrorGV.DataBind(); } // 匯入ErrorLog catch (Exception ex1) { try { Log.ErrorLog(ex1, "Import Excel Error :" + Session["FileName"].ToString(), StrProgram); } catch (Exception ex2) { Log.ErrorLog(ex2, "Insert Error Error:" + Session["FileName"].ToString(), StrProgram); } finally { transaction1.Rollback(); F_ErrorShow("Please Contact MIS : Import Error"); //Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('匯入失敗請連絡MIS');</script>"); } } finally { Session.RemoveAll(); //Label1.Text = "資料上傳成功"; } } } else { F_ErrorShow("Create Head Error"); } } else { F_ErrorShow(lang.翻譯("Program", "ImportDataError", "TW")); } }
public void F_CheckDate(string StrExcelSheet, string 副檔名) { string str頁簽名稱 = ""; int ISheetCheck = 0;//確認Sheet 資料是否正確,每份Sheet只會有一個對應 try { DataTable D_table = new DataTable("Excel"); DataTable D_errortable = new DataTable("Error"); DataTable DtColumnDefine = GetDBData(StrExcelSheet); //EnumerableRowCollection<DataRow> query1 = from dt in DtColumnDefine.AsEnumerable() // where dt.Field<string>("匯入資料") == "AMZCapacity" // select dt; //EnumerableRowCollection<DataRow> query2 = from dt in DtColumnDefine.AsEnumerable() // where dt.Field<string>("匯入資料") == "AMZGuidance" // select dt; //DataTable dt1 = new DataTable(), dt2 = new DataTable(); //foreach (DataRow dr in query1) // dt1.ImportRow(dr); //foreach (DataRow dr in query2) // dt2.ImportRow(dr); //if (Session["DataDefine"]!=null) // Session.Remove("DataDeffine"); Session[StrExcelSheet + "Define"] = DtColumnDefine; //指定Import Sheet Name string StrSheetNameCheck = ""; Boolean BCheck = false; int I資料起始欄, I資料起始列; D_errortable.Columns.Add("Error"); foreach (DataRow Dr in DtColumnDefine.Rows) { D_table.Columns.Add(Dr["資料名稱中文"].ToString()); } if (DtColumnDefine.Rows.Count > 0) { StrSheetNameCheck = (string.IsNullOrEmpty(DtColumnDefine.Rows[0]["指定頁籤名稱"].ToString())) ? "" : DtColumnDefine.Rows[0]["指定頁籤名稱"].ToString(); if (!Boolean.TryParse(DtColumnDefine.Rows[0]["是否指定頁籤"].ToString(), out BCheck)) { BCheck = false; } if (!int.TryParse(DtColumnDefine.Rows[0]["資料起始列"].ToString(), out I資料起始列)) { I資料起始列 = 1; } if (!int.TryParse(DtColumnDefine.Rows[0]["資料起始欄"].ToString(), out I資料起始欄)) { I資料起始欄 = 0; } if (副檔名.ToUpper() == ".XLSX") { //XSSFWorkbook workbook = new XSSFWorkbook(upload_file.PostedFile.InputStream); //==只能讀取 System.IO.Stream for (int x = 0; x < XSSworkbook.NumberOfSheets; x++) { //-- 0表示:第一個 worksheet工作表 XSSFSheet u_sheet = (XSSFSheet)XSSworkbook.GetSheetAt(x); str頁簽名稱 = u_sheet.SheetName.ToString(); //檢查是否有要對應資料 if (BCheck && StrSheetNameCheck != str頁簽名稱) { continue; } else { ISheetCheck = +1; } //-- Excel 表頭列 XSSFRow headerRow = (XSSFRow)u_sheet.GetRow(I資料起始列); IRow DateRow = (IRow)u_sheet.GetRow(I資料起始列); for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++) { //--不包含 Excel表頭列的 "其他資料列" IRow row = (IRow)u_sheet.GetRow(i); F_資料格式確認(ref D_table, ref D_errortable, str頁簽名稱, row, DtColumnDefine, i, I資料起始欄); } //-- 釋放 NPOI的資源 u_sheet = null; } //-- 釋放 NPOI的資源 //workbook = null; } else { //HSSFWorkbook workbook = new HSSFWorkbook(upload_file.PostedFile.InputStream); //==只能讀取 System.IO.Stream for (int x = 0; x < HSSworkbook.NumberOfSheets; x++) { HSSFSheet u_sheet = (HSSFSheet)HSSworkbook.GetSheetAt(x); //-- 0表示:第一個 worksheet工作表 HSSFRow headerRow = (HSSFRow)u_sheet.GetRow(I資料起始列); //-- Excel 表頭列 IRow DateRow = (IRow)u_sheet.GetRow(I資料起始列); //-- v.1.2.4版修改 //檢查是否有要對應資料 if (BCheck && StrSheetNameCheck != str頁簽名稱) { continue; } else { ISheetCheck = +1; } str頁簽名稱 = u_sheet.SheetName.ToString(); for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++) //-- 每一列做迴圈 { //--不包含 Excel表頭列的 "其他資料列" IRow row = (IRow)u_sheet.GetRow(i); F_資料格式確認(ref D_table, ref D_errortable, str頁簽名稱, row, DtColumnDefine, i, I資料起始欄); } //-- 釋放 NPOI的資源 u_sheet = null; } //-- 釋放 NPOI的資源 //workbook = null; } //--錯誤資料顯示 DataView D_View3 = new DataView(D_errortable); if (D_errortable.Rows.Count > 0) { if (str頁簽名稱 == "AMZCapacity") { CapacityErrorGV.DataSource = D_View3; CapacityErrorGV.DataBind(); } else { GuidanceErrorGV.DataSource = D_View3; GuidanceErrorGV.DataBind(); } if (ISheetCheck != 1) { F_ErrorShow(str頁簽名稱 + "Sheet 比對失敗"); } } if (D_table.Rows.Count > 0) { if (D_errortable.Rows.Count == 0) { Session[StrExcelSheet] = D_table; } } } else { F_ErrorShow("Please contact Mis : Import format is not defined"); } } catch (Exception ex) { F_ErrorShow($"Error: {ex.Message}"); } }