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 = "您輸入的帳號/密碼不正確!";
//}
}
}
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檔案內容!!";
}
}
}