protected void Button1_Click(object sender, EventArgs e) { //SqlConnection Conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["SHDBConnectionString"].ConnectionString.ToString()); //Conn.Open(); //string strSQL = "select User_Code,User_Name,user_flag from users where user_acc = '"+ username.Text +"' and user_pw='"+ password.Text +"'"; //SqlCommand cmd = new SqlCommand(strSQL, Conn); //SqlDataReader dr = cmd.ExecuteReader(); //if (dr.HasRows) //{ // dr.Read(); // Session["usercode"] = dr["User_Code"];//dr.GetOrdinal("user_code"); // Session["username"] = dr["User_Name"];//dr.GetOrdinal("user_name"); // Session["userflag"] = dr["User_flag"]; //0:離職 1:普通使用者 3:可以看不可修改 5:最大權限 // Session["login"] = "******"; // Response.Redirect("~/Pages/Home.aspx"); // cmd.Cancel(); // dr.Close(); // Conn.Close(); //} //else //{ // Label1.Text = "您輸入的帳號/密碼有誤!"; // cmd.Cancel(); // dr.Close(); // Conn.Close(); //} //Session["usercode"] = "twshao";//dr.GetOrdinal("user_code"); //Session["username"] = "******";//dr.GetOrdinal("user_name"); //Session["userflag"] = "1"; //0:離職 1:普通使用者 3:可以看不可修改 5:最大權限 //Session["userpassword"] = "******"; clsMySQL db = new clsMySQL(); MySqlDataReader dr; //DataSet ds; //DataRow dr; if (username.Text == "") { lblError.Text = "請輸入工號!"; } else if (password.Text == "") { lblError.Text = "請輸入密碼!"; } else { string strQuerySQL = "Select * from infor_personal where workno ='" + username.Text.Trim() + "' and password = '******'"; //lblError.Text = strQuerySQL +"<br>"; try { //ds = db.QueryDataSet(strQuerySQL); dr = db.dbQueryDR(strQuerySQL); if (dr.HasRows) //if (ds.Tables[0].Rows.Count > 0) { //dr = ds.Tables[0].Rows[0]; dr.Read(); Session["Workno"] = dr["WorkNo"].ToString(); Session["chinesename"] = dr["ChineseName"].ToString(); Session["departname"] = dr["DepartName"].ToString(); Response.Redirect("~/test/Home.aspx"); //lblError.Text = "WorkNo:" + Session["WorkNo"].ToString() +"/ Chinesename :"+ Session["chinesename"].ToString() ; } else { lblError.Text = "您輸入的工號/密碼不正確!"; } db.Close(); } catch (Exception ex) { lblError.Text = "[Login Error Message] : " + ex.ToString(); db.Close(); } //if (username.Text == "twshao" && password.Text == "1234") //{ // Session["username"] = "******"; // Session["usercode"] = "twshao"; // Response.Redirect("~/test/Home.aspx"); //} //else //{ // lblError.Text = "您輸入的帳號/密碼不正確!"; //} } }
public void ImportToDataTable() { string fileName = ""; lblMsg.Text = ""; try { fileName = Path.GetFileName(FileUploadToServer.PostedFile.FileName); string filePath = Server.MapPath("~\\FileUpload\\") + Path.GetFileName(FileUploadToServer.PostedFile.FileName); FileUploadToServer.SaveAs(filePath); DataTable dt = new DataTable(); //---------------取出file type : DIF/D_R/DRC,取檔名的前三字元 if (CheckExcelFile(fileName)) //判斷File是否為xlsx { string strType = fileName.Substring(0, 3); string strSQL = ""; switch (strType) { case "DIF": strSQL = "select Mtype,Mname,Mpos,MRow,MCol from npimap where mtype = 'DIF' order by mid"; break; case "Q_R": strSQL = "select Mtype,Mname,Mpos,MRow,MCol from npimap where mtype = 'Q_R' order by mid"; break; case "DRC": strSQL = "select Mtype,Mname,Mpos,MRow,MCol from npimap where mtype = 'DRC' order by mid"; break; default: lblMsg.ForeColor = System.Drawing.Color.Red; lblMsg.Text = "您選擇的檔案:[" + fileName + "]無法匯入,請重新選擇正確的Excel檔案!!!"; strSQL = ""; break; } //-------------------------------------------------------------------------------- var existingFile = new FileInfo(filePath); using (var package = new ExcelPackage(existingFile)) { ExcelWorkbook workBook = package.Workbook; if (workBook != null && strSQL != "") { if (workBook.Worksheets.Count > 0) { ExcelWorksheet worksheet = workBook.Worksheets.First();//Excel File的第一個Sheet1 DataTable dt_Pos = new DataTable(); clsMySQL db = new clsMySQL(); ArrayList arSQL = new ArrayList(); db.dbConn(); dt_Pos = db.dbQueryDT(strSQL);//db.QueryDataTable(strSQL); db.dbClose(); dt.Columns.Add("檔案類型"); dt.Columns.Add("欄位名稱"); dt.Columns.Add("欄位位置"); //dt.Columns.Add("Rows"); //dt.Columns.Add("Cols"); dt.Columns.Add("欄位內容"); //Get Customer and Device value---------------------------------------------------------------- string tmpCustomer = ""; string tmpDevice = ""; for (int t = 0; t <= 2; t++) { if (dt_Pos.Rows[t][1].ToString() == "Customer") { if (strType == "DRC") { string[] str2 = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[t][3].ToString()), Convert.ToInt32(dt_Pos.Rows[t][4].ToString())].Value.ToString().Split(':'); tmpCustomer = str2[1]; } else { tmpCustomer = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[t][3].ToString()), Convert.ToInt32(dt_Pos.Rows[t][4].ToString())].Value.ToString(); } } else { if (dt_Pos.Rows[t][1].ToString() == "Device") { if (strType == "DRC") { string[] str2 = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[t][3].ToString()), Convert.ToInt32(dt_Pos.Rows[t][4].ToString())].Value.ToString().Split(':'); tmpDevice = str2[1]; } else { tmpDevice = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[t][3].ToString()), Convert.ToInt32(dt_Pos.Rows[t][4].ToString())].Value.ToString(); } } } } //--------------------------------------------------------------------------------------------------------- for (int i = 0; i < dt_Pos.Rows.Count; i++) { DataRow dr = dt.NewRow(); string tmpValue = ""; int x = 0; db.dbConn(); dr[x++] = dt_Pos.Rows[i][0].ToString(); dr[x++] = dt_Pos.Rows[i][1].ToString(); dr[x++] = dt_Pos.Rows[i][2].ToString(); //dr[x++] = dt_Pos.Rows[i][3].ToString(); //dr[x++] = dt_Pos.Rows[i][4].ToString(); if (strType == "DRC" && (dt_Pos.Rows[i][2].ToString() == "B46" || dt_Pos.Rows[i][2].ToString() == "B47" || dt_Pos.Rows[i][2].ToString() == "B48")) { string[] str1 = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[i][3].ToString()), Convert.ToInt32(dt_Pos.Rows[i][4].ToString())].Value.ToString().Split(':'); dr[x++] = str1[1]; tmpValue = str1[1]; } else { dr[x++] = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[i][3].ToString()), Convert.ToInt32(dt_Pos.Rows[i][4].ToString())].Value; if (worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[i][3].ToString()), Convert.ToInt32(dt_Pos.Rows[i][4].ToString())].Value == null) { tmpValue = ""; } else { tmpValue = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[i][3].ToString()), Convert.ToInt32(dt_Pos.Rows[i][4].ToString())].Value.ToString(); } } dt.Rows.Add(dr); gvRecord.DataSource = dt; gvRecord.DataBind(); //-----------------------------------insert MySql Database: npiImportData string strSQL_Query = string.Format("select * from npiImportData where New_Customer = '{0}' and New_Device ='{1}' and Stype='{2}' and Im_Pos='{3}'", tmpCustomer.Trim(), tmpDevice.Trim(), strType.Trim(), dt_Pos.Rows[i][2].ToString().Trim()); string strSQL_Del = string.Format("Delete from npiImportData where New_Customer='{0}' and New_Device='{1}' and Stype='{2}' and Im_Pos='{3}'", tmpCustomer.Trim(), tmpDevice.Trim(), strType.Trim(), dt_Pos.Rows[i][2].ToString().Trim()); arSQL.Add(strSQL_Del); string strSQL_Insert = string.Format("Insert into npiImportData (New_Customer,New_Device,Stype,UpdateTime,npiUser,Im_Name,Im_Pos,Im_Value) values (" + "'{0}','{1}','{2}',NOW(),'','{3}','{4}','{5}')", tmpCustomer.Trim(), tmpDevice.Trim(), strType.Trim(), dt_Pos.Rows[i][1].ToString().Trim(), dt_Pos.Rows[i][2].ToString().Trim(), tmpValue.Trim()); arSQL.Add(strSQL_Insert); lblMsg.ForeColor = System.Drawing.Color.Red; //if (db.QueryDataReader(strSQL_Query).HasRows) if (db.dbQueryDR(strSQL_Query).HasRows) { if (!db.myBatchNonQuery(arSQL)) { lblMsg.Text = "[Import Error Message] Delete/Insert Fail!!<br/>"; } } else { if (!db.QueryExecuteNonQuery(strSQL_Insert)) { lblMsg.Text = "[Import Error Message] Insert Fail!! <br/>"; } } arSQL.Clear(); lblMsg.Text = strSQL_Insert + "<br/>" + lblMsg.Text; //--------------------------------------- db.dbClose(); } lblMsg.ForeColor = System.Drawing.Color.Green; lblMsg.Text = "[" + fileName + "],完成資料匯入!!"; } else { lblMsg.ForeColor = System.Drawing.Color.Red; lblMsg.Text = "您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!"; } } else { gvRecord.DataSource = dt; gvRecord.DataBind(); } } } File.Delete(filePath); //將IIS上的excel file刪除,C:\inetpub\wwwroot\BU3Web\FileUpload } catch (Exception exfile) { lblMsg.ForeColor = System.Drawing.Color.Red; if (fileName == "") { lblMsg.Text = "[Import Error Message] 請選擇要匯入的Excel檔案!!"; } else { lblMsg.Text = "[Import Error Message]您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!"; } } }
public void ImportToDataTable() { string fileName = ""; lblMsg.Text = ""; try { fileName = Path.GetFileName(FileUploadToServer.PostedFile.FileName); string filePath = Server.MapPath("~\\FileUpload\\") + Path.GetFileName(FileUploadToServer.PostedFile.FileName); FileUploadToServer.SaveAs(filePath); DataTable dt = new DataTable(); //---------------取出file type : DIF/D_R/DRC,取檔名的前三字元 if (CheckExcelFile(fileName)) //判斷File是否為xlsx { string strType = fileName.Substring(0, 3); string strSQL = ""; switch (strType) { case "DIF": strSQL = "select Mtype,Mname,Mpos,MRow,MCol from npimap where mtype = 'DIF' order by mid"; break; case "Q_R": strSQL = "select Mtype,Mname,Mpos,MRow,MCol from npimap where mtype = 'Q_R' order by mid"; break; case "DRC": strSQL = "select Mtype,Mname,Mpos,MRow,MCol from npimap where mtype = 'DRC' order by mid"; break; default: lblMsg.ForeColor = System.Drawing.Color.Red; lblMsg.Text = "您選擇的檔案:["+ fileName +"]無法匯入,請重新選擇正確的Excel檔案!!!"; strSQL = ""; break; } //-------------------------------------------------------------------------------- var existingFile = new FileInfo(filePath); using (var package = new ExcelPackage(existingFile)) { ExcelWorkbook workBook = package.Workbook; if (workBook != null && strSQL != "") { if (workBook.Worksheets.Count > 0) { ExcelWorksheet worksheet = workBook.Worksheets.First();//Excel File的第一個Sheet1 DataTable dt_Pos = new DataTable(); clsMySQL db = new clsMySQL(); ArrayList arSQL = new ArrayList(); db.dbConn(); dt_Pos = db.dbQueryDT(strSQL);//db.QueryDataTable(strSQL); db.dbClose(); dt.Columns.Add("檔案類型"); dt.Columns.Add("欄位名稱"); dt.Columns.Add("欄位位置"); //dt.Columns.Add("Rows"); //dt.Columns.Add("Cols"); dt.Columns.Add("欄位內容"); //Get Customer and Device value---------------------------------------------------------------- string tmpCustomer = ""; string tmpDevice = ""; for (int t = 0; t <= 2; t++) { if (dt_Pos.Rows[t][1].ToString() == "Customer") { if (strType == "DRC") { string[] str2 = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[t][3].ToString()), Convert.ToInt32(dt_Pos.Rows[t][4].ToString())].Value.ToString().Split(':'); tmpCustomer = str2[1]; } else { tmpCustomer = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[t][3].ToString()), Convert.ToInt32(dt_Pos.Rows[t][4].ToString())].Value.ToString(); } } else { if (dt_Pos.Rows[t][1].ToString() == "Device") { if (strType == "DRC") { string[] str2 = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[t][3].ToString()), Convert.ToInt32(dt_Pos.Rows[t][4].ToString())].Value.ToString().Split(':'); tmpDevice = str2[1]; } else { tmpDevice = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[t][3].ToString()), Convert.ToInt32(dt_Pos.Rows[t][4].ToString())].Value.ToString(); } } } } //--------------------------------------------------------------------------------------------------------- for (int i = 0; i < dt_Pos.Rows.Count; i++) { DataRow dr = dt.NewRow(); string tmpValue = ""; int x = 0; db.dbConn(); dr[x++] = dt_Pos.Rows[i][0].ToString(); dr[x++] = dt_Pos.Rows[i][1].ToString(); dr[x++] = dt_Pos.Rows[i][2].ToString(); //dr[x++] = dt_Pos.Rows[i][3].ToString(); //dr[x++] = dt_Pos.Rows[i][4].ToString(); if (strType == "DRC" && (dt_Pos.Rows[i][2].ToString() == "B46" || dt_Pos.Rows[i][2].ToString() == "B47" || dt_Pos.Rows[i][2].ToString() == "B48")) { string[] str1 = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[i][3].ToString()), Convert.ToInt32(dt_Pos.Rows[i][4].ToString())].Value.ToString().Split(':'); dr[x++] = str1[1]; tmpValue = str1[1]; } else { dr[x++] = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[i][3].ToString()), Convert.ToInt32(dt_Pos.Rows[i][4].ToString())].Value; if (worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[i][3].ToString()), Convert.ToInt32(dt_Pos.Rows[i][4].ToString())].Value == null) { tmpValue = ""; } else { tmpValue = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[i][3].ToString()), Convert.ToInt32(dt_Pos.Rows[i][4].ToString())].Value.ToString(); } } dt.Rows.Add(dr); gvRecord.DataSource = dt; gvRecord.DataBind(); //-----------------------------------insert MySql Database: npiImportData string strSQL_Query = string.Format("select * from npiImportData where New_Customer = '{0}' and New_Device ='{1}' and Stype='{2}' and Im_Pos='{3}'", tmpCustomer.Trim(), tmpDevice.Trim(), strType.Trim(), dt_Pos.Rows[i][2].ToString().Trim()); string strSQL_Del = string.Format("Delete from npiImportData where New_Customer='{0}' and New_Device='{1}' and Stype='{2}' and Im_Pos='{3}'", tmpCustomer.Trim(), tmpDevice.Trim(), strType.Trim(), dt_Pos.Rows[i][2].ToString().Trim()); arSQL.Add(strSQL_Del); string strSQL_Insert = string.Format("Insert into npiImportData (New_Customer,New_Device,Stype,UpdateTime,npiUser,Im_Name,Im_Pos,Im_Value) values (" + "'{0}','{1}','{2}',NOW(),'','{3}','{4}','{5}')", tmpCustomer.Trim(), tmpDevice.Trim(), strType.Trim(), dt_Pos.Rows[i][1].ToString().Trim(), dt_Pos.Rows[i][2].ToString().Trim(), tmpValue.Trim()); arSQL.Add(strSQL_Insert); lblMsg.ForeColor = System.Drawing.Color.Red; //if (db.QueryDataReader(strSQL_Query).HasRows) if (db.dbQueryDR(strSQL_Query).HasRows) { if (!db.myBatchNonQuery(arSQL)) { lblMsg.Text = "[Import Error Message] Delete/Insert Fail!!<br/>"; } } else { if (!db.QueryExecuteNonQuery(strSQL_Insert)) { lblMsg.Text = "[Import Error Message] Insert Fail!! <br/>"; } } arSQL.Clear(); lblMsg.Text = strSQL_Insert + "<br/>" + lblMsg.Text; //--------------------------------------- db.dbClose(); } lblMsg.ForeColor = System.Drawing.Color.Green; lblMsg.Text = "[" + fileName + "],完成資料匯入!!"; } else { lblMsg.ForeColor = System.Drawing.Color.Red; lblMsg.Text = "您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!"; } } else { gvRecord.DataSource = dt; gvRecord.DataBind(); } } } File.Delete(filePath); //將IIS上的excel file刪除,C:\inetpub\wwwroot\BU3Web\FileUpload } catch (Exception exfile) { lblMsg.ForeColor = System.Drawing.Color.Red; if (fileName == "") { lblMsg.Text = "[Import Error Message] 請選擇要匯入的Excel檔案!!"; } else { lblMsg.Text = "[Import Error Message]您選擇的[" +fileName +"]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!"; } } }