private void InputExcel(string pPath) { int NUM = 0; ToolUtility TU = new ToolUtility(); DataTable DT = TU.getExcelSheetData(pPath, false); DataTable dinnertime = DinnerTime.GetAllDinnerTime(); foreach (DataRow DR in DT.Rows) { DinnerTimeInfo dinnerinfo = new DinnerTimeInfo() { Process = DR["Process"].ToString().Trim(), Type = DR["Type"].ToString().Trim(), Class = DR["Class"].ToString().Trim(), PdLine = DR["PdLine"].ToString().Trim(), BeginTime = DR["BeginTime"].ToString().Trim(), EndTime = DR["EndTime"].ToString().Trim(), Remark = DR["Remark"].ToString().Trim(), Editor = Master.userInfo.UserId, Cdt = DateTime.Now, Udt = DateTime.Now }; DinnerLogInfo dinnerLoginfo = new DinnerLogInfo() { Process = DR["Process"].ToString().Trim(), Type = DR["Type"].ToString().Trim(), Class = DR["Class"].ToString().Trim(), PdLine = DR["PdLine"].ToString().Trim(), BeginTime = DR["BeginTime"].ToString().Trim(), EndTime = DR["EndTime"].ToString().Trim(), Remark = DR["Remark"].ToString().Trim(), Editor = Master.userInfo.UserId, Cdt = DateTime.Now }; if (dinnerinfo.Process != "" && dinnerinfo.Type != "" && dinnerinfo.Class != "" && dinnerinfo.PdLine != "" && dinnerinfo.BeginTime != "" && dinnerinfo.EndTime != "") { DinnerTime.AddDinnerLogInfo(dinnerLoginfo); DinnerTime.AddDinnerTimeInfo(dinnerinfo); } else { NUM += 1; continue; } foreach (DataRow allDR in dinnertime.Rows) { if (allDR["Class"].ToString().Trim() == DR["Class"].ToString().Trim() && allDR["PdLine"].ToString().Trim() == DR["PdLine"].ToString().Trim()) { string id = allDR["ID"].ToString().Trim(); DinnerTime.DelDinnerTime(Int32.Parse(id)); id = null; } } } txtremark.Text = ""; txtquery_Click(null, null); }
//private void InputExcel(string pPath) //{ // //FileStream file = null; // //string conn = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + pPath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; // string connstr2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; // string connstr2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pPath + ";Extended Properties=\"Excel 12.0;HDR=YES\""; // OleDbConnection oleCon; // if (System.IO.Path.GetExtension(pPath) == ".xls") // { // oleCon = new OleDbConnection(connstr2003); // } // else // { // oleCon = new OleDbConnection(connstr2007); // } // oleCon.Open(); // //file = new FileStream(pPath, FileMode.Open); // string Sql = "select * from [Sheet1$] where process<>'' and Class<>'' and PdLine<>'' and BeginTime<>'' and EndTime<>'' and Status<>''"; // OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, oleCon); // DataSet ds = new DataSet(); // mycommand.Fill(ds, "[Sheet1$]"); // oleCon.Close(); // //file.Close(); // int count = ds.Tables["[Sheet1$]"].Rows.Count; // AlarmInfo alarminfo = new AlarmInfo(); // AlarmInfoLog alarminfolog = new AlarmInfoLog(); // for (int i = 0; i < count; i++) // { // if (ds.Tables["[Sheet1$]"].Rows[i]["process"].ToString().Trim() != "") // { // alarminfo.process = ds.Tables["[Sheet1$]"].Rows[i]["process"].ToString().ToUpper().Trim(); // alarminfo.Class = ds.Tables["[Sheet1$]"].Rows[i]["Class"].ToString().ToUpper().Trim(); // alarminfo.PdLine = ds.Tables["[Sheet1$]"].Rows[i]["PdLine"].ToString().ToUpper().Trim(); // alarminfo.BeginTime = Convert.ToDateTime(ds.Tables["[Sheet1$]"].Rows[i]["BeginTime"]).ToString("HH:mm").Trim(); // alarminfo.EndTime = Convert.ToDateTime(ds.Tables["[Sheet1$]"].Rows[i]["EndTime"]).ToString("HH:mm").Trim(); // alarminfo.Status = ds.Tables["[Sheet1$]"].Rows[i]["Status"].ToString().Trim(); // alarminfo.Remark = ds.Tables["[Sheet1$]"].Rows[i]["Remark"].ToString().Trim(); // alarminfo.Editor = ((MasterPageMaintain)Master).userInfo.UserName.ToString().Trim(); // alarminfo.Cdt = DateTime.Now; // alarminfo.Udt = DateTime.Now; // IAM.AddAlarmInfo(alarminfo); // //塞入Log // alarminfolog.process = ds.Tables["[Sheet1$]"].Rows[i]["process"].ToString().ToUpper().Trim(); // alarminfolog.Class = ds.Tables["[Sheet1$]"].Rows[i]["Class"].ToString().ToUpper().Trim(); // alarminfolog.PdLine = ds.Tables["[Sheet1$]"].Rows[i]["PdLine"].ToString().ToUpper().Trim(); // alarminfolog.BeginTime = Convert.ToDateTime(ds.Tables["[Sheet1$]"].Rows[i]["BeginTime"]).ToString("HH:mm").Trim(); // alarminfolog.EndTime = Convert.ToDateTime(ds.Tables["[Sheet1$]"].Rows[i]["EndTime"]).ToString("HH:mm").Trim(); // alarminfolog.Status = ds.Tables["[Sheet1$]"].Rows[i]["Status"].ToString().Trim(); // alarminfolog.Remark = "InputExcel! " + ds.Tables["[Sheet1$]"].Rows[i]["Remark"].ToString().Trim(); // alarminfolog.Editor = ((MasterPageMaintain)Master).userInfo.UserName.ToString().Trim(); // alarminfolog.Cdt = DateTime.Now; // IAM.AddAlarmlog(alarminfolog); // } // else // { // break; // } // } // Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "MyScript", "alert('导入成功!')", true); // Button_Query_Click(this, null); //} #endregion private void InputExcel(string pPath) { int NUM = 0; ToolUtility TU = new ToolUtility(); DataTable DT = TU.getExcelSheetData(pPath, false); DataTable allDT = IAM.GetAlarmALL(); foreach (DataRow DR in DT.Rows) { AlarmInfo alarminfo = new AlarmInfo() { process = DR["Process"].ToString().Trim(), Class = DR["Class"].ToString().Trim(), PdLine = DR["PdLine"].ToString().Trim(), BeginTime = DR["BeginTime"].ToString().Trim(), EndTime = DR["EndTime"].ToString().Trim(), Status = DR["Status"].ToString().Trim(), Remark = DR["Remark"].ToString().Trim(), Editor = ((MasterPageMaintain)Master).userInfo.UserId.ToString(), Cdt = DateTime.Now, Udt = DateTime.Now }; AlarmInfoLog alarminfolog = new AlarmInfoLog() { process = DR["Process"].ToString().Trim(), Class = DR["Class"].ToString().Trim(), PdLine = DR["PdLine"].ToString().Trim(), BeginTime = DR["BeginTime"].ToString().Trim(), EndTime = DR["EndTime"].ToString().Trim(), Status = DR["Status"].ToString().Trim(), Remark = "Excel Input! " + DR["Remark"].ToString().Trim(), Editor = ((MasterPageMaintain)Master).userInfo.UserId.ToString(), Cdt = DateTime.Now, }; if (alarminfo.process != "" && alarminfo.Class != "" && alarminfo.PdLine != "" && alarminfo.BeginTime != "" && alarminfo.EndTime != "") { IAM.AddAlarmInfo(alarminfo); IAM.AddAlarmlog(alarminfolog); } else { NUM += 1; continue; } foreach (DataRow allDR in allDT.Rows) { if (allDR["Class"].ToString().Trim() == DR["Class"].ToString().Trim() && allDR["PdLine"].ToString().Trim() == DR["PdLine"].ToString().Trim()) { get_id.Value = allDR["ID"].ToString().Trim(); IAM.DelAlarm(Int32.Parse(get_id.Value)); get_id.Value = null; } } } Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "MyScript", "alert('导入Excel已完成!" + NUM + "行数据不完整,未进行导入!')", true); Button_Query_Click(this, null); }
private void Inutexcel2(Stream ExcelFileStream) { ToolUtility tu = new ToolUtility(); DataTable dt= tu.getExcelSheetData( ExcelFileStream,false); bindbullTable(dt,0); foreach (DataRow dr in dt.Rows) { string process= dr["Process"].ToString().Trim(); if (string.IsNullOrEmpty(process)) { break; } UPHInfo alarminfo = new UPHInfo(); alarminfo.Process = process; alarminfo.Family = dr["Family"].ToString().Trim(); alarminfo.Attend_normal = int.Parse(dr["Attend_normal"].ToString().Trim()); alarminfo.ST = dr["ST"].ToString().Trim(); alarminfo.NormalUPH = int.Parse(dr["NormalUPH"].ToString().Trim()); alarminfo.Cycle = dr["Cycle"].ToString().Trim(); alarminfo.Remark = dr["Remark"].ToString().Trim(); alarminfo.Special = dr["Special"].ToString().Trim(); // alarminfo.Editor = ((MasterPageMaintain)Master).userInfo.UserName.ToString().Trim(); alarminfo.Editor = Master.userInfo.UserId; alarminfo.Cdt = DateTime.Now; alarminfo.Udt = DateTime.Now; uph.AddProductUPHInfo(alarminfo); } }