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