#region 获取Excel数据 /// <summary> /// 获取Excel数据 /// </summary> /// <param name="fs"></param> /// <param name="fileName"></param> /// <param name="sheetName"></param> /// <param name="isFirstRowColumn"></param> /// <returns></returns> public DataTable ExcelToDataTable(Stream fs, string fileName, string sheetName, bool isFirstRowColumn) { IWorkbook workbook = null; ISheet sheet = null; DataTable data = new DataTable(); int startRow = 0; try { if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(fs); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(fs); if (sheetName != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } } return data; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } } #endregion #region 验证Excel数据 /// <summary> /// 验证Excel数据 /// </summary> /// <returns></returns> private DataTable xsldata() { if (fuload.FileName == "") { return null; } string fileExtenSion; fileExtenSion = Path.GetExtension(fuload.FileName); if (fileExtenSion.ToLower() != ".xls" && fileExtenSion.ToLower() != ".xlsx") { PubFunc.outPutJavaScript("alert('上传格式错误!')"); return null; } try { var s = Request.Files[0]; var dt = ExcelToDataTable(s.InputStream, s.FileName, "sheet1", true); //创建表 DataTable tblDatas = new DataTable("Datas"); tblDatas.Columns.Add("车牌号", Type.GetType("System.String")); tblDatas.Columns.Add("错误行数", Type.GetType("System.Int32")); tblDatas.Columns.Add("错误原因", Type.GetType("System.String")); for (int i = 0; i < dt.Rows.Count; i++) { int state = 0; //dt.Rows[i]["Name"].ToString(); "Name"即为Excel中Name列的表头 string Vcl_No = dt.Rows[i]["车牌号"].ToString(); string Vcl_Organ_Dealer = dt.Rows[i]["公司名称"].ToString(); string Vcl_Organ_OEM = dt.Rows[i]["所属OEM"].ToString(); string Vcl_UnderpanCode = dt.Rows[i]["VIN码"].ToString(); string Vcl_Manufacturer = dt.Rows[i]["生产厂家"].ToString(); string Vcl_Type = dt.Rows[i]["车型号"].ToString(); string Vcl_TotalWeight = dt.Rows[i]["总质量"].ToString(); string Vcl_TotalPrepareWeight = dt.Rows[i]["整备质量"].ToString(); string Vcl_MaxTakeoffWeight = dt.Rows[i]["最大设计牵引质量"].ToString(); string Vcl_EngineType = dt.Rows[i]["发动机型号"].ToString(); string Vcl_EngineNo = dt.Rows[i]["发动机编号"].ToString(); string Vcl_LinkManFirst = dt.Rows[i]["第一联系人"].ToString(); string Vcl_LinkManFirstMobile = dt.Rows[i]["第一联系人手机"].ToString(); string Vcl_LinkManSecond = dt.Rows[i]["第二联系人"].ToString(); string Vcl_LinkManSecondMobile = dt.Rows[i]["第二联系人手机"].ToString(); string strerror = ""; #region 判断不为空的 if (Vcl_EngineType.Trim() == "") { strerror += "发动机型号:出现空字段;"; state++; } //if (Vcl_MaxTakeoffWeight.Trim() == "") //{ // strerror+="最大设计牵引质量:出现空字段;"; // state++; //} //if (Vcl_TotalPrepareWeight.Trim() == "") //{ // strerror+="整备质量:出现空字段;"; // state++; //} if (Vcl_Organ_Dealer.Trim() == "") { strerror+="公司名称:出现空字段;"; state++; } if (Vcl_Organ_OEM.Trim() == "") { strerror+="所属OEM:出现空字段;"; state++; } if (Vcl_No.Trim() == "") { strerror+="车牌号:出现空字段;"; state++; } if (Vcl_UnderpanCode.Trim() == "") { strerror+="VIN码:出现空字段;"; state++; } if (Vcl_Manufacturer.Trim() == "") { strerror+="生产厂家:出现空字段;"; state++; } if (Vcl_Type.Trim() == "") { strerror+="车型号:出现空字段;"; state++; } //if (Vcl_TotalWeight.Trim() == "") //{ // strerror+="总质量:出现空字段;"; // state++; //} if (Vcl_EngineNo.Trim() == "") { strerror+="发动机编号:出现空字段;"; state++; } if (Vcl_LinkManFirst.Trim() == "") { strerror+="第一联系人:出现空字段;"; state++; } if (Vcl_LinkManFirstMobile.Trim() == "") { strerror+="第一联系人手机:出现空字段;"; state++; } if (Vcl_LinkManFirstMobile.Trim() == Vcl_LinkManSecondMobile.Trim()) { strerror+="第二联系人手机:两联系人手机号相同;"; state++; } #endregion #region 规则验证 //if (txtNo.Text.Trim() != "") //{ // //if (PubFunc.IsCHIandEng(txtNo.Text.Trim())) // //{ // // PubFunc.outPutJavaScript("alert('车牌号格式为一个汉字加6个字符,请重新输入。')"); // // txtNo.Text = ""; // // return; // //} // if (!PubFunc.IsCHIandEng(txtNo.Text.Trim())) // { // PubFunc.outPutJavaScript("alert('车牌号格式为一个汉字加6个字符,请重新输入。')"); // txtNo.Text = ""; // return; // } //} //if (txtVINCode.Text.Trim() != "") //{ // if (!PubFunc.Is17(txtVINCode.Text.Trim())) // { // PubFunc.outPutJavaScript("alert('VIN码为17个字符,请重新输入。')"); // txtVINCode.Text = ""; // return; // } //} if (ht[Vcl_Organ_Dealer] == null) { strerror += "公司名称:公司名称不存在;"; state++; } if (ht[Vcl_Organ_OEM]==null) { strerror+="所属OEM:所属OEM不正确;"; state++; } if (!PubFunc.Iscount(Vcl_LinkManFirstMobile.Trim(), 11)) { strerror+="第一联系人手机:手机号必须为11位数字;"; state++; } if (Vcl_LinkManSecondMobile.Trim() != "" && Vcl_LinkManSecondMobile.Trim() != null) { if (!PubFunc.Iscount(Vcl_LinkManSecondMobile.Trim(), 11)) { strerror+="第二联系人手机:手机号必须为11位数字;"; state++; } } //if (PubFunc.IsDecimal(txtTotalWeight.Text.Trim())) //{ // PubFunc.outPutJavaScript("alert('只能输入数字,请重新输入。')"); // txtTotalWeight.Text = ""; // return; //} #endregion #region 检查是否重复; //string sqlIsIDrepeat = "select count(Vcl_ID) from Vcl_Info where Vcl_ID='" + Convert.ToDecimal(txtVclID.Text.Trim()) + "'"; //int count = Convert.ToInt16(PubFunc.ExecuteScalar(sqlIsIDrepeat)); //if (count > 0) //{ // PubFunc.outPutJavaScript("alert('系统中已存在此车ID号,请重新输入。')"); // txtVclID.Text = ""; // return; //} string sqlIsCarNoRpt = "select count(Vcl_No) from " + ReturnConnText_VCL_database + ".dbo.Vcl_Info where Vcl_Exp>'" + DateTime.Now.ToString() + "' and Vcl_No='" + Vcl_No.Trim() + "'"; int countCarNo = Convert.ToInt16(PubFunc.ExecuteScalar(sqlIsCarNoRpt)); if (countCarNo == 0) { if (Vcl_UnderpanCode.Trim() != "") { string sqlIsVINRpt = "select count(Vcl_VinCode) from " + ReturnConnText_VCL_database + ".dbo.Vcl_Info where Vcl_Exp>'" + DateTime.Now.ToString() + "' and Vcl_VinCode='" + Vcl_UnderpanCode.Trim() + "'"; int countVIN = Convert.ToInt16(PubFunc.ExecuteScalar(sqlIsVINRpt)); if (countVIN > 0) { strerror += "VIN码:系统中已存在此VIN码;"; state++; } } } else { if (Vcl_UnderpanCode.Trim() != "") { string sqlIsVINRpt = "select count(Vcl_VinCode) from " + ReturnConnText_VCL_database + ".dbo.Vcl_Info where Vcl_Exp>'" + DateTime.Now.ToString() + "' and Vcl_VinCode='" + Vcl_UnderpanCode.Trim() + "'"; int countVIN = Convert.ToInt16(PubFunc.ExecuteScalar(sqlIsVINRpt)); if (countVIN > 1) { strerror += "VIN码:系统中已存在此VIN码;"; state++; } } } if (Vcl_UnderpanCode.Trim().Length>20) { strerror += "VIN码:VIN码长度应小于20;"; state++; } if (Vcl_No.Trim().Length > 20) { strerror += "VIN码:车牌号长度应小于20;"; state++; } if (state > 0) { tblDatas.Rows.Add(new object[] { Vcl_No, (i + 1), strerror }); continue; } #endregion } if (tblDatas.Rows.Count >0) { for (int j = 0; j < tblDatas.Rows.Count; j++) { dgList.DataSource = tblDatas; dgList.DataBind(); } PubFunc.outPutJavaScript("alert('信息错误,请修改后重新上传。')"); return null; } //删除服务器里上传的文件 return dt; } catch (Exception e) { PubFunc.outPutJavaScript("alert('模板格式被破坏,请重新下载模板。')"); return null; } } #endregion #region 保存Excel数据 protected void Button2_Click(object sender, EventArgs e) { try { dgList.DataSource = null; dgList.DataBind(); DataTable dt = xsldata(); //dataGridView2.DataSource = ds.Tables[0]; int errorcount = 0;//记录错误信息条数 int insertcount = 0;//记录插入成功条数 int states = 0; int updatecount = 0;//记录更新信息条数 for (int i = 0; i < dt.Rows.Count; i++) { //dt.Rows[i]["Name"].ToString(); "Name"即为Excel中Name列的表头 string Vcl_No = dt.Rows[i][0].ToString(); string Vcl_Organ_Dealer = ht[dt.Rows[i][1].ToString()].ToString(); string Vcl_Organ_OEM = ht[dt.Rows[i][2].ToString()].ToString(); string Vcl_UnderpanCode = dt.Rows[i][3].ToString(); string Vcl_Manufacturer = dt.Rows[i][4].ToString(); string Vcl_Type = dt.Rows[i][5].ToString(); string Vcl_TotalWeight = dt.Rows[i][6].ToString(); string Vcl_TotalPrepareWeight = dt.Rows[i][7].ToString(); string Vcl_MaxTakeoffWeight = dt.Rows[i][8].ToString(); string Vcl_EngineType = dt.Rows[i][9].ToString(); string Vcl_EngineNo = dt.Rows[i][10].ToString(); string Vcl_LinkManFirst = dt.Rows[i][11].ToString(); string Vcl_LinkManFirstMobile = dt.Rows[i][12].ToString(); string Vcl_LinkManSecond = dt.Rows[i][13].ToString(); string Vcl_LinkManSecondMobile = dt.Rows[i][14].ToString(); int state = 0; string sqlVcl = ""; #region 2017年9月30日12:09:59 //string sqlCount = "select * from Vcl_Info where Vcl_No='" + Vcl_No + "'"; //DataTable dtone=PubFunc.ReturnDataTable(sqlCount,"Table"); //int count =dtone.Rows.Count; //if (count > 0) //{ // decimal VclID = (decimal)dtone.Rows[0]["Vcl_ID"]; // state++; // string sqlCounts = "select * from Vcl_Info where Vcl_No='" + Vcl_No + "' and Vcl_Exp<'"+ DateTime.Now.ToString()+"' "; // DataTable dtones=PubFunc.ReturnDataTable(sqlCount,"Table"); // int counts =dtones.Rows.Count; // sqlVcl = "UPDATE Vcl_Info" // + " SET "; // if (Vcl_Organ_Dealer.Trim() != "") // { // sqlVcl += "Vcl_Organ_Dealer = '" + Vcl_Organ_Dealer.Trim() + "' "; // } // if (Vcl_Organ_OEM.Trim() != "") // { // sqlVcl += ",Vcl_Organ_OEM = '" + Vcl_Organ_OEM.Trim() + "' "; // } // sqlVcl += ",Vcl_UnderpanCode = '" + Vcl_UnderpanCode.Trim() + "' " // + ",Vcl_Manufacturer = '" + Vcl_Manufacturer.Trim() + "'" // + ",Vcl_Type = '" + Vcl_Type.Trim() + "'" // + ",Vcl_TotalWeight = '" + Vcl_TotalWeight.Trim() + "'" // + ",Vcl_TotalPrepareWeight = '" + Vcl_TotalPrepareWeight.Trim() + "'" // + ",Vcl_MaxTakeoffWeight = '" + Vcl_MaxTakeoffWeight.Trim() + "'" // + ",Vcl_EngineType = '" + Vcl_EngineType.Trim() + "'" // + ",Vcl_EngineNo = '" + Vcl_EngineNo.Trim() + "'" // + ",Vcl_LinkManFirst = '" + Vcl_LinkManFirst.Trim() + "'" // + ",Vcl_LinkManFirstMobile = '" + Vcl_LinkManFirstMobile.Trim() + "'" // + ",Vcl_LinkManSecond = '" + Vcl_LinkManSecond.Trim() + "'" // + ",Vcl_LinkManSecondMobile = '" + Vcl_LinkManSecondMobile.Trim() + "'"; // if (counts > 0) // { // sqlVcl += ",Vcl_Exp = '2099-12-31 00:00:00.000' "; // Czy userone = Czy.GetCzyInfo(); // decimal OrganID = Convert.ToDecimal(userone.Organ_ID); // ArrayList arrSystemID = new ArrayList(); // arrSystemID.Add("4"); // string ip = Page.Request.UserHostAddress; // //UnifiedPlatformInterface IVclAdd = new UnifiedPlatformInterface(); // //IVclAdd.Add_Vcl_Info(Vcl_No.Trim(), Vcl_UnderpanCode.Trim(), "4", "", arrSystemID, userone.Acnt_ID, System.DateTime.Now.ToString(), ip, "4"); // UnifiedPlatformInterface IVclUpdates = new UnifiedPlatformInterface(); // IVclUpdates.Update_Vcl_Info(VclID.ToString(), Vcl_No.Trim(), Vcl_UnderpanCode.Trim(), "4", "", userone.Acnt_ID, System.DateTime.Now.ToString(), ip, "4"); // states++; // } // sqlVcl += " where Vcl_No='" +Vcl_No.Trim() + "'"; // Czy user = Czy.GetCzyInfo(); // decimal s = Convert.ToDecimal(user.Organ_ID); // ArrayList arrSystemIDs = new ArrayList(); // arrSystemIDs.Add("4"); // string ips = Page.Request.UserHostAddress; // UnifiedPlatformInterface IVclUpdate = new UnifiedPlatformInterface(); // IVclUpdate.Update_Vcl_Info(VclID.ToString(), Vcl_No.Trim(), Vcl_UnderpanCode.Trim(), "4", "", user.Acnt_ID, System.DateTime.Now.ToString(), ips, "4"); // try // { // int rst = PubFunc.ExecuteNonQuery(sqlVcl); // if (rst == -9999) // { // PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); // } // else // { // // 添加操作日志 // // LogDB.LogDBOperationIns(sqlVcl, "", Convert.ToInt32(dtID.Rows[0]["Vcl_ID"].ToString()), "Vcl_Info_OprtLog", "VclOL_", false, 0, false, user); // if (states > 0) // { // insertcount += states; // } // else // { // updatecount++; // } // } // } // catch // { // PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); // } // finally { } //} //else //{ // #region 确定Vcl_ID // string Vcl_ID; // string ResultFlag; // string Reason = ""; // Czy user = Czy.GetCzyInfo(); // string sqlIsCarNoRpt = "select Vcl_No,Vcl_ID from " + ReturnConnText_VCL_database + ".dbo.Vcl_Info where Vcl_Exp>'" + DateTime.Now.ToString() + "' and Vcl_No='" + Vcl_No + "'"; // DataTable dtid= PubFunc.ReturnDataTable(sqlIsCarNoRpt, "Vcl_NoTab"); // if (dtid.Rows.Count == 0) // { // decimal OrganID = Convert.ToDecimal(user.Organ_ID); // ArrayList arrSystemID = new ArrayList(); // arrSystemID.Add("4"); // string ip = Page.Request.UserHostAddress; // UnifiedPlatformInterface IVclAdd = new UnifiedPlatformInterface(); // IVclAdd.Add_Vcl_Info(Vcl_No.Trim(), Vcl_UnderpanCode.Trim(), "4", "", arrSystemID, user.Acnt_ID, System.DateTime.Now.ToString(), ip, "4"); // ResultFlag=IVclAdd.ResultFlag; // Vcl_ID= IVclAdd.Vcl_ID; // Reason=IVclAdd.Reason; // } // else // { // Vcl_ID= dtid.Rows[0]["Vcl_ID"].ToString(); // ResultFlag = "1"; // } // #endregion // if (ResultFlag == "1") // { // sqlVcl = "INSERT INTO Vcl_Info" // + "(Vcl_ID " // + ",Vcl_No " // + ",Vcl_Organ_Dealer " // + ",Vcl_Organ_OEM " // + ",Vcl_UnderpanCode " // + ",Vcl_Manufacturer " // + ",Vcl_Type " // + ",Vcl_TotalWeight " // + ",Vcl_TotalPrepareWeight " // + ",Vcl_MaxTakeoffWeight " // + ",Vcl_EngineType " // + ",Vcl_EngineNo " // + ",Vcl_LinkManFirst " // + ",Vcl_LinkManFirstMobile " // + ",Vcl_LinkManSecond " // + ",Vcl_LinkManSecondMobile " // //+ ",Vcl_NoLoad" // //+ ",Vcl_Load" // //+ ",Vcl_FullLoad" // //+ ",Vcl_LoadSetDate // + " ) " // + "VALUES " // + "('" + Vcl_ID + "' " // + ",'" + Vcl_No.Trim() + "' "; // if (Vcl_Organ_Dealer.Trim() != "") // { // sqlVcl += ",'" + Vcl_Organ_Dealer.Trim() + "' "; // } // else // { // sqlVcl += ",null"; // } // if (Vcl_Organ_OEM.Trim() != "") // { // sqlVcl += ",'" + Vcl_Organ_OEM.Trim() + "' "; // } // else // { // sqlVcl += ",null"; // } // sqlVcl += ",'" + Vcl_UnderpanCode.Trim() + "' " // + ",'" + Vcl_Manufacturer.Trim() + "' " // + ",'" + Vcl_Type.Trim() + "' " // + ",'" + Vcl_TotalWeight.Trim() + "' " // + ",'" + Vcl_TotalPrepareWeight.Trim() + "' " // + ",'" + Vcl_MaxTakeoffWeight.Trim() + "' " // + ",'" + Vcl_EngineType.Trim() + "' " // + ",'" + Vcl_EngineNo.Trim() + "' " // + ",'" + Vcl_LinkManFirst.Trim() + "' " // + ",'" + Vcl_LinkManFirstMobile.Trim() + "' " // + ",'" + Vcl_LinkManSecond.Trim() + "' " // + ",'" + Vcl_LinkManSecondMobile.Trim() + "' "; // int load = 0; // //if (txtNoLoadMax.Text.Trim() != "") // //{ // // sqlVcl += ",'" + txtNoLoadMax.Text.Trim() + "' "; // // load++; // //} // //else // //{ // // sqlVcl += ",null"; // //} // //if (txtLoadMax.Text.Trim() != "") // //{ // // sqlVcl += ",'" + txtLoadMax.Text.Trim() + "'"; // // load++; // //} // //else // //{ // // sqlVcl += ",null"; // //} // //if (txtFullLoadMax.Text.Trim() != "") // //{ // // sqlVcl += ",'" + txtFullLoadMax.Text.Trim() + "'"; // // load++; // //} // //else // //{ // // sqlVcl += ",null"; // //} // //if (load > 0) // //{ // // sqlVcl += ",'" + DateTime.Now.ToString() + "') "; // //} // //else // //{ // // sqlVcl += ",null) "; // //} // sqlVcl += ")"; // try // { // int rst = PubFunc.ExecuteNonQuery(sqlVcl); // if (rst == -9999) // { // PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); // } // else // { // // 添加操作日志 // LogDB.LogDBOperationIns(sqlVcl, "", Convert.ToInt32(Vcl_ID), "Vcl_Info_OprtLog", "VclOL_", false, 0, false, user); // insertcount++; // } // } // catch // { // PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); // } // finally { } // } // else // { // if (Reason == "") // { // PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); // } // else // { // PubFunc.outPutJavaScript("alert('第" + (i + 1) + "行" + Reason + "')"); // } // } //} #endregion string sqlCounts = "select * from Vcl_Info where Vcl_No='" + Vcl_No + "' and Vcl_Exp<'" + DateTime.Now.ToString() + "' "; DataTable dtones = PubFunc.ReturnDataTable(sqlCounts, "Table"); int counts = dtones.Rows.Count; string sqlCount = "delete from Vcl_Info where Vcl_No='" + Vcl_No + "'"; int count=PubFunc.ExecuteNonQuery(sqlCount); string sqlIsCarNoRpt = "select Vcl_No,Vcl_ID from " + ReturnConnText_VCL_database + ".dbo.Vcl_Info where Vcl_Exp>'" + DateTime.Now.ToString() + "' and Vcl_No='" + Vcl_No + "'"; DataTable dtid = PubFunc.ReturnDataTable(sqlIsCarNoRpt, "Vcl_NoTab"); Czy user = Czy.GetCzyInfo(); #region 确定Vcl_ID string Vcl_ID; string ResultFlag; string Reason = ""; if (dtid.Rows.Count == 0) { decimal OrganID = Convert.ToDecimal(user.Organ_ID); ArrayList arrSystemID = new ArrayList(); arrSystemID.Add("4"); string ip = Page.Request.UserHostAddress; UnifiedPlatformInterface IVclAdd = new UnifiedPlatformInterface(); IVclAdd.Add_Vcl_Info(Vcl_No.Trim(), Vcl_UnderpanCode.Trim(), "4", "", arrSystemID, user.Acnt_ID, System.DateTime.Now.ToString(), ip, "4"); ResultFlag = IVclAdd.ResultFlag; Vcl_ID = IVclAdd.Vcl_ID; Reason = IVclAdd.Reason; } else { Vcl_ID = dtid.Rows[0]["Vcl_ID"].ToString(); decimal OrganID = Convert.ToDecimal(user.Organ_ID); ArrayList arrSystemID = new ArrayList(); arrSystemID.Add("4"); string ip = Page.Request.UserHostAddress; UnifiedPlatformInterface IVclUpdate = new UnifiedPlatformInterface(); IVclUpdate.Update_Vcl_Info(Vcl_ID, Vcl_No.Trim(), Vcl_UnderpanCode.Trim(), "4", "", user.Acnt_ID, System.DateTime.Now.ToString(), ip, "4"); ResultFlag = "1"; } #endregion if (ResultFlag == "1") { sqlVcl = "INSERT INTO Vcl_Info" + "(Vcl_ID " + ",Vcl_No " + ",Vcl_Organ_Dealer " + ",Vcl_Organ_OEM " + ",Vcl_UnderpanCode " + ",Vcl_Manufacturer " + ",Vcl_Type " + ",Vcl_TotalWeight " + ",Vcl_TotalPrepareWeight " + ",Vcl_MaxTakeoffWeight " + ",Vcl_EngineType " + ",Vcl_EngineNo " + ",Vcl_LinkManFirst " + ",Vcl_LinkManFirstMobile " + ",Vcl_LinkManSecond " + ",Vcl_LinkManSecondMobile " //+ ",Vcl_NoLoad" //+ ",Vcl_Load" //+ ",Vcl_FullLoad" //+ ",Vcl_LoadSetDate + " ) " + "VALUES " + "('" + Vcl_ID + "' " + ",'" + Vcl_No.Trim() + "' "; if (Vcl_Organ_Dealer.Trim() != "") { sqlVcl += ",'" + Vcl_Organ_Dealer.Trim() + "' "; } else { sqlVcl += ",null"; } if (Vcl_Organ_OEM.Trim() != "") { sqlVcl += ",'" + Vcl_Organ_OEM.Trim() + "' "; } else { sqlVcl += ",null"; } sqlVcl += ",'" + Vcl_UnderpanCode.Trim() + "' " + ",'" + Vcl_Manufacturer.Trim() + "' " + ",'" + Vcl_Type.Trim() + "' " + ",'" + Vcl_TotalWeight.Trim() + "' " + ",'" + Vcl_TotalPrepareWeight.Trim() + "' " + ",'" + Vcl_MaxTakeoffWeight.Trim() + "' " + ",'" + Vcl_EngineType.Trim() + "' " + ",'" + Vcl_EngineNo.Trim() + "' " + ",'" + Vcl_LinkManFirst.Trim() + "' " + ",'" + Vcl_LinkManFirstMobile.Trim() + "' " + ",'" + Vcl_LinkManSecond.Trim() + "' " + ",'" + Vcl_LinkManSecondMobile.Trim() + "' "; int load = 0; //if (txtNoLoadMax.Text.Trim() != "") //{ // sqlVcl += ",'" + txtNoLoadMax.Text.Trim() + "' "; // load++; //} //else //{ // sqlVcl += ",null"; //} //if (txtLoadMax.Text.Trim() != "") //{ // sqlVcl += ",'" + txtLoadMax.Text.Trim() + "'"; // load++; //} //else //{ // sqlVcl += ",null"; //} //if (txtFullLoadMax.Text.Trim() != "") //{ // sqlVcl += ",'" + txtFullLoadMax.Text.Trim() + "'"; // load++; //} //else //{ // sqlVcl += ",null"; //} //if (load > 0) //{ // sqlVcl += ",'" + DateTime.Now.ToString() + "') "; //} //else //{ // sqlVcl += ",null) "; //} sqlVcl += ")"; try { int rst = PubFunc.ExecuteNonQuery(sqlVcl); if (rst == -9999) { PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); } else { // 添加操作日志 LogDB.LogDBOperationIns(sqlVcl, "", Convert.ToInt32(Vcl_ID), "Vcl_Info_OprtLog", "VclOL_", false, 0, false, user); if (counts > 0) { insertcount++; } else if (count > 0) { updatecount++; } else { insertcount++; } } } catch { PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); } finally { } } else { if (Reason == "") { PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); } else { PubFunc.outPutJavaScript("alert('第" + (i + 1) + "行" + Reason + "')"); } } } PubFunc.outPutJavaScript("alert('" + insertcount + "条数据导入成功!" + updatecount + "条数据更新!')"); } catch (Exception ex) { } } #endregion
#region 获取Excel数据 /// <summary> /// 获取Excel数据 /// </summary> /// <param name="fs"></param> /// <param name="fileName"></param> /// <param name="sheetName"></param> /// <param name="isFirstRowColumn"></param> /// <returns></returns> public DataTable ExcelToDataTable(Stream fs, string fileName, string sheetName, bool isFirstRowColumn) { IWorkbook workbook = null; ISheet sheet = null; DataTable data = new DataTable(); int startRow = 0; try { if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(fs); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(fs);
if (sheetName != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; }
//最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } }
return data; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } } #endregion
#region 验证Excel数据 /// <summary> /// 验证Excel数据 /// </summary> /// <returns></returns> private DataTable xsldata() { if (fuload.FileName == "") { return null; } string fileExtenSion; fileExtenSion = Path.GetExtension(fuload.FileName); if (fileExtenSion.ToLower() != ".xls" && fileExtenSion.ToLower() != ".xlsx") { PubFunc.outPutJavaScript("alert('上传格式错误!')"); return null; } try { var s = Request.Files[0]; var dt = ExcelToDataTable(s.InputStream, s.FileName, "sheet1", true); //创建表 DataTable tblDatas = new DataTable("Datas"); tblDatas.Columns.Add("车牌号", Type.GetType("System.String")); tblDatas.Columns.Add("错误行数", Type.GetType("System.Int32")); tblDatas.Columns.Add("错误原因", Type.GetType("System.String"));
for (int i = 0; i < dt.Rows.Count; i++) { int state = 0; //dt.Rows[i]["Name"].ToString(); "Name"即为Excel中Name列的表头 string Vcl_No = dt.Rows[i]["车牌号"].ToString(); string Vcl_Organ_Dealer = dt.Rows[i]["公司名称"].ToString(); string Vcl_Organ_OEM = dt.Rows[i]["所属OEM"].ToString(); string Vcl_UnderpanCode = dt.Rows[i]["VIN码"].ToString(); string Vcl_Manufacturer = dt.Rows[i]["生产厂家"].ToString(); string Vcl_Type = dt.Rows[i]["车型号"].ToString(); string Vcl_TotalWeight = dt.Rows[i]["总质量"].ToString(); string Vcl_TotalPrepareWeight = dt.Rows[i]["整备质量"].ToString(); string Vcl_MaxTakeoffWeight = dt.Rows[i]["最大设计牵引质量"].ToString(); string Vcl_EngineType = dt.Rows[i]["发动机型号"].ToString(); string Vcl_EngineNo = dt.Rows[i]["发动机编号"].ToString(); string Vcl_LinkManFirst = dt.Rows[i]["第一联系人"].ToString(); string Vcl_LinkManFirstMobile = dt.Rows[i]["第一联系人手机"].ToString(); string Vcl_LinkManSecond = dt.Rows[i]["第二联系人"].ToString(); string Vcl_LinkManSecondMobile = dt.Rows[i]["第二联系人手机"].ToString(); string strerror = ""; #region 判断不为空的 if (Vcl_EngineType.Trim() == "") { strerror += "发动机型号:出现空字段;"; state++; } //if (Vcl_MaxTakeoffWeight.Trim() == "") //{ // strerror+="最大设计牵引质量:出现空字段;"; // state++; //} //if (Vcl_TotalPrepareWeight.Trim() == "") //{ // strerror+="整备质量:出现空字段;"; // state++; //} if (Vcl_Organ_Dealer.Trim() == "") { strerror+="公司名称:出现空字段;"; state++; } if (Vcl_Organ_OEM.Trim() == "") { strerror+="所属OEM:出现空字段;"; state++; } if (Vcl_No.Trim() == "") { strerror+="车牌号:出现空字段;"; state++; } if (Vcl_UnderpanCode.Trim() == "") { strerror+="VIN码:出现空字段;"; state++; } if (Vcl_Manufacturer.Trim() == "") { strerror+="生产厂家:出现空字段;"; state++; } if (Vcl_Type.Trim() == "") { strerror+="车型号:出现空字段;"; state++; } //if (Vcl_TotalWeight.Trim() == "") //{ // strerror+="总质量:出现空字段;"; // state++; //}
if (Vcl_EngineNo.Trim() == "") { strerror+="发动机编号:出现空字段;"; state++; } if (Vcl_LinkManFirst.Trim() == "") { strerror+="第一联系人:出现空字段;"; state++; } if (Vcl_LinkManFirstMobile.Trim() == "") { strerror+="第一联系人手机:出现空字段;"; state++; } if (Vcl_LinkManFirstMobile.Trim() == Vcl_LinkManSecondMobile.Trim()) { strerror+="第二联系人手机:两联系人手机号相同;"; state++; } #endregion
#region 规则验证 //if (txtNo.Text.Trim() != "") //{ // //if (PubFunc.IsCHIandEng(txtNo.Text.Trim())) // //{ // // PubFunc.outPutJavaScript("alert('车牌号格式为一个汉字加6个字符,请重新输入。')"); // // txtNo.Text = ""; // // return; // //} // if (!PubFunc.IsCHIandEng(txtNo.Text.Trim())) // { // PubFunc.outPutJavaScript("alert('车牌号格式为一个汉字加6个字符,请重新输入。')"); // txtNo.Text = ""; // return; // } //}
//if (txtVINCode.Text.Trim() != "") //{ // if (!PubFunc.Is17(txtVINCode.Text.Trim())) // { // PubFunc.outPutJavaScript("alert('VIN码为17个字符,请重新输入。')"); // txtVINCode.Text = ""; // return; // } //} if (ht[Vcl_Organ_Dealer] == null) { strerror += "公司名称:公司名称不存在;"; state++; } if (ht[Vcl_Organ_OEM]==null) { strerror+="所属OEM:所属OEM不正确;"; state++; } if (!PubFunc.Iscount(Vcl_LinkManFirstMobile.Trim(), 11)) { strerror+="第一联系人手机:手机号必须为11位数字;"; state++; } if (Vcl_LinkManSecondMobile.Trim() != "" && Vcl_LinkManSecondMobile.Trim() != null) { if (!PubFunc.Iscount(Vcl_LinkManSecondMobile.Trim(), 11)) { strerror+="第二联系人手机:手机号必须为11位数字;"; state++; } } //if (PubFunc.IsDecimal(txtTotalWeight.Text.Trim())) //{ // PubFunc.outPutJavaScript("alert('只能输入数字,请重新输入。')"); // txtTotalWeight.Text = ""; // return; //}
#endregion
#region 检查是否重复; //string sqlIsIDrepeat = "select count(Vcl_ID) from Vcl_Info where Vcl_ID='" + Convert.ToDecimal(txtVclID.Text.Trim()) + "'"; //int count = Convert.ToInt16(PubFunc.ExecuteScalar(sqlIsIDrepeat)); //if (count > 0) //{ // PubFunc.outPutJavaScript("alert('系统中已存在此车ID号,请重新输入。')"); // txtVclID.Text = ""; // return; //}
string sqlIsCarNoRpt = "select count(Vcl_No) from " + ReturnConnText_VCL_database + ".dbo.Vcl_Info where Vcl_Exp>'" + DateTime.Now.ToString() + "' and Vcl_No='" + Vcl_No.Trim() + "'"; int countCarNo = Convert.ToInt16(PubFunc.ExecuteScalar(sqlIsCarNoRpt)); if (countCarNo == 0) { if (Vcl_UnderpanCode.Trim() != "") { string sqlIsVINRpt = "select count(Vcl_VinCode) from " + ReturnConnText_VCL_database + ".dbo.Vcl_Info where Vcl_Exp>'" + DateTime.Now.ToString() + "' and Vcl_VinCode='" + Vcl_UnderpanCode.Trim() + "'"; int countVIN = Convert.ToInt16(PubFunc.ExecuteScalar(sqlIsVINRpt)); if (countVIN > 0) { strerror += "VIN码:系统中已存在此VIN码;"; state++; } } } else { if (Vcl_UnderpanCode.Trim() != "") { string sqlIsVINRpt = "select count(Vcl_VinCode) from " + ReturnConnText_VCL_database + ".dbo.Vcl_Info where Vcl_Exp>'" + DateTime.Now.ToString() + "' and Vcl_VinCode='" + Vcl_UnderpanCode.Trim() + "'"; int countVIN = Convert.ToInt16(PubFunc.ExecuteScalar(sqlIsVINRpt)); if (countVIN > 1) { strerror += "VIN码:系统中已存在此VIN码;"; state++; } } }
if (Vcl_UnderpanCode.Trim().Length>20) { strerror += "VIN码:VIN码长度应小于20;"; state++; } if (Vcl_No.Trim().Length > 20) { strerror += "VIN码:车牌号长度应小于20;"; state++; } if (state > 0) { tblDatas.Rows.Add(new object[] { Vcl_No, (i + 1), strerror }); continue; } #endregion } if (tblDatas.Rows.Count >0) { for (int j = 0; j < tblDatas.Rows.Count; j++) { dgList.DataSource = tblDatas; dgList.DataBind(); } PubFunc.outPutJavaScript("alert('信息错误,请修改后重新上传。')"); return null; }
//删除服务器里上传的文件 return dt; } catch (Exception e) { PubFunc.outPutJavaScript("alert('模板格式被破坏,请重新下载模板。')"); return null; } } #endregion
#region 保存Excel数据 protected void Button2_Click(object sender, EventArgs e) { try { dgList.DataSource = null; dgList.DataBind(); DataTable dt = xsldata();
//dataGridView2.DataSource = ds.Tables[0]; int errorcount = 0;//记录错误信息条数 int insertcount = 0;//记录插入成功条数 int states = 0;
int updatecount = 0;//记录更新信息条数 for (int i = 0; i < dt.Rows.Count; i++) { //dt.Rows[i]["Name"].ToString(); "Name"即为Excel中Name列的表头 string Vcl_No = dt.Rows[i][0].ToString(); string Vcl_Organ_Dealer = ht[dt.Rows[i][1].ToString()].ToString(); string Vcl_Organ_OEM = ht[dt.Rows[i][2].ToString()].ToString(); string Vcl_UnderpanCode = dt.Rows[i][3].ToString(); string Vcl_Manufacturer = dt.Rows[i][4].ToString(); string Vcl_Type = dt.Rows[i][5].ToString(); string Vcl_TotalWeight = dt.Rows[i][6].ToString(); string Vcl_TotalPrepareWeight = dt.Rows[i][7].ToString(); string Vcl_MaxTakeoffWeight = dt.Rows[i][8].ToString(); string Vcl_EngineType = dt.Rows[i][9].ToString(); string Vcl_EngineNo = dt.Rows[i][10].ToString(); string Vcl_LinkManFirst = dt.Rows[i][11].ToString(); string Vcl_LinkManFirstMobile = dt.Rows[i][12].ToString(); string Vcl_LinkManSecond = dt.Rows[i][13].ToString(); string Vcl_LinkManSecondMobile = dt.Rows[i][14].ToString();
int state = 0; string sqlVcl = "";
#region 2017年9月30日12:09:59 //string sqlCount = "select * from Vcl_Info where Vcl_No='" + Vcl_No + "'"; //DataTable dtone=PubFunc.ReturnDataTable(sqlCount,"Table"); //int count =dtone.Rows.Count; //if (count > 0) //{ // decimal VclID = (decimal)dtone.Rows[0]["Vcl_ID"]; // state++; // string sqlCounts = "select * from Vcl_Info where Vcl_No='" + Vcl_No + "' and Vcl_Exp<'"+ DateTime.Now.ToString()+"' "; // DataTable dtones=PubFunc.ReturnDataTable(sqlCount,"Table"); // int counts =dtones.Rows.Count; // sqlVcl = "UPDATE Vcl_Info" // + " SET "; // if (Vcl_Organ_Dealer.Trim() != "") // { // sqlVcl += "Vcl_Organ_Dealer = '" + Vcl_Organ_Dealer.Trim() + "' "; // } // if (Vcl_Organ_OEM.Trim() != "") // { // sqlVcl += ",Vcl_Organ_OEM = '" + Vcl_Organ_OEM.Trim() + "' "; // } // sqlVcl += ",Vcl_UnderpanCode = '" + Vcl_UnderpanCode.Trim() + "' " // + ",Vcl_Manufacturer = '" + Vcl_Manufacturer.Trim() + "'" // + ",Vcl_Type = '" + Vcl_Type.Trim() + "'" // + ",Vcl_TotalWeight = '" + Vcl_TotalWeight.Trim() + "'" // + ",Vcl_TotalPrepareWeight = '" + Vcl_TotalPrepareWeight.Trim() + "'" // + ",Vcl_MaxTakeoffWeight = '" + Vcl_MaxTakeoffWeight.Trim() + "'" // + ",Vcl_EngineType = '" + Vcl_EngineType.Trim() + "'" // + ",Vcl_EngineNo = '" + Vcl_EngineNo.Trim() + "'" // + ",Vcl_LinkManFirst = '" + Vcl_LinkManFirst.Trim() + "'" // + ",Vcl_LinkManFirstMobile = '" + Vcl_LinkManFirstMobile.Trim() + "'" // + ",Vcl_LinkManSecond = '" + Vcl_LinkManSecond.Trim() + "'" // + ",Vcl_LinkManSecondMobile = '" + Vcl_LinkManSecondMobile.Trim() + "'"; // if (counts > 0) // { // sqlVcl += ",Vcl_Exp = '2099-12-31 00:00:00.000' "; // Czy userone = Czy.GetCzyInfo(); // decimal OrganID = Convert.ToDecimal(userone.Organ_ID); // ArrayList arrSystemID = new ArrayList(); // arrSystemID.Add("4"); // string ip = Page.Request.UserHostAddress; // //UnifiedPlatformInterface IVclAdd = new UnifiedPlatformInterface(); // //IVclAdd.Add_Vcl_Info(Vcl_No.Trim(), Vcl_UnderpanCode.Trim(), "4", "", arrSystemID, userone.Acnt_ID, System.DateTime.Now.ToString(), ip, "4");
// UnifiedPlatformInterface IVclUpdates = new UnifiedPlatformInterface(); // IVclUpdates.Update_Vcl_Info(VclID.ToString(), Vcl_No.Trim(), Vcl_UnderpanCode.Trim(), "4", "", userone.Acnt_ID, System.DateTime.Now.ToString(), ip, "4");
// states++; // } // sqlVcl += " where Vcl_No='" +Vcl_No.Trim() + "'"; // Czy user = Czy.GetCzyInfo(); // decimal s = Convert.ToDecimal(user.Organ_ID); // ArrayList arrSystemIDs = new ArrayList(); // arrSystemIDs.Add("4"); // string ips = Page.Request.UserHostAddress; // UnifiedPlatformInterface IVclUpdate = new UnifiedPlatformInterface(); // IVclUpdate.Update_Vcl_Info(VclID.ToString(), Vcl_No.Trim(), Vcl_UnderpanCode.Trim(), "4", "", user.Acnt_ID, System.DateTime.Now.ToString(), ips, "4");
// try // { // int rst = PubFunc.ExecuteNonQuery(sqlVcl); // if (rst == -9999) // { // PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); // } // else // { // // 添加操作日志 // // LogDB.LogDBOperationIns(sqlVcl, "", Convert.ToInt32(dtID.Rows[0]["Vcl_ID"].ToString()), "Vcl_Info_OprtLog", "VclOL_", false, 0, false, user); // if (states > 0) // { // insertcount += states; // } // else // { // updatecount++; // } // } // } // catch // { // PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); // } // finally { } //} //else //{ // #region 确定Vcl_ID // string Vcl_ID; // string ResultFlag; // string Reason = ""; // Czy user = Czy.GetCzyInfo(); // string sqlIsCarNoRpt = "select Vcl_No,Vcl_ID from " + ReturnConnText_VCL_database + ".dbo.Vcl_Info where Vcl_Exp>'" + DateTime.Now.ToString() + "' and Vcl_No='" + Vcl_No + "'"; // DataTable dtid= PubFunc.ReturnDataTable(sqlIsCarNoRpt, "Vcl_NoTab"); // if (dtid.Rows.Count == 0) // { // decimal OrganID = Convert.ToDecimal(user.Organ_ID); // ArrayList arrSystemID = new ArrayList(); // arrSystemID.Add("4"); // string ip = Page.Request.UserHostAddress; // UnifiedPlatformInterface IVclAdd = new UnifiedPlatformInterface(); // IVclAdd.Add_Vcl_Info(Vcl_No.Trim(), Vcl_UnderpanCode.Trim(), "4", "", arrSystemID, user.Acnt_ID, System.DateTime.Now.ToString(), ip, "4"); // ResultFlag=IVclAdd.ResultFlag; // Vcl_ID= IVclAdd.Vcl_ID; // Reason=IVclAdd.Reason; // } // else // { // Vcl_ID= dtid.Rows[0]["Vcl_ID"].ToString(); // ResultFlag = "1"; // } // #endregion
// if (ResultFlag == "1") // { // sqlVcl = "INSERT INTO Vcl_Info" // + "(Vcl_ID " // + ",Vcl_No " // + ",Vcl_Organ_Dealer " // + ",Vcl_Organ_OEM " // + ",Vcl_UnderpanCode " // + ",Vcl_Manufacturer " // + ",Vcl_Type " // + ",Vcl_TotalWeight " // + ",Vcl_TotalPrepareWeight " // + ",Vcl_MaxTakeoffWeight " // + ",Vcl_EngineType " // + ",Vcl_EngineNo " // + ",Vcl_LinkManFirst " // + ",Vcl_LinkManFirstMobile " // + ",Vcl_LinkManSecond " // + ",Vcl_LinkManSecondMobile " // //+ ",Vcl_NoLoad" // //+ ",Vcl_Load" // //+ ",Vcl_FullLoad" // //+ ",Vcl_LoadSetDate // + " ) " // + "VALUES " // + "('" + Vcl_ID + "' " // + ",'" + Vcl_No.Trim() + "' "; // if (Vcl_Organ_Dealer.Trim() != "") // { // sqlVcl += ",'" + Vcl_Organ_Dealer.Trim() + "' "; // } // else // { // sqlVcl += ",null"; // } // if (Vcl_Organ_OEM.Trim() != "") // { // sqlVcl += ",'" + Vcl_Organ_OEM.Trim() + "' "; // } // else // { // sqlVcl += ",null"; // } // sqlVcl += ",'" + Vcl_UnderpanCode.Trim() + "' " // + ",'" + Vcl_Manufacturer.Trim() + "' " // + ",'" + Vcl_Type.Trim() + "' " // + ",'" + Vcl_TotalWeight.Trim() + "' " // + ",'" + Vcl_TotalPrepareWeight.Trim() + "' " // + ",'" + Vcl_MaxTakeoffWeight.Trim() + "' " // + ",'" + Vcl_EngineType.Trim() + "' " // + ",'" + Vcl_EngineNo.Trim() + "' " // + ",'" + Vcl_LinkManFirst.Trim() + "' " // + ",'" + Vcl_LinkManFirstMobile.Trim() + "' " // + ",'" + Vcl_LinkManSecond.Trim() + "' " // + ",'" + Vcl_LinkManSecondMobile.Trim() + "' "; // int load = 0; // //if (txtNoLoadMax.Text.Trim() != "") // //{ // // sqlVcl += ",'" + txtNoLoadMax.Text.Trim() + "' "; // // load++; // //} // //else // //{ // // sqlVcl += ",null"; // //} // //if (txtLoadMax.Text.Trim() != "") // //{ // // sqlVcl += ",'" + txtLoadMax.Text.Trim() + "'"; // // load++; // //} // //else // //{ // // sqlVcl += ",null"; // //} // //if (txtFullLoadMax.Text.Trim() != "") // //{ // // sqlVcl += ",'" + txtFullLoadMax.Text.Trim() + "'"; // // load++; // //} // //else // //{ // // sqlVcl += ",null"; // //} // //if (load > 0) // //{ // // sqlVcl += ",'" + DateTime.Now.ToString() + "') "; // //} // //else // //{ // // sqlVcl += ",null) "; // //} // sqlVcl += ")"; // try // { // int rst = PubFunc.ExecuteNonQuery(sqlVcl); // if (rst == -9999) // { // PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); // } // else // { // // 添加操作日志 // LogDB.LogDBOperationIns(sqlVcl, "", Convert.ToInt32(Vcl_ID), "Vcl_Info_OprtLog", "VclOL_", false, 0, false, user); // insertcount++; // } // } // catch // { // PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); // } // finally { } // } // else // { // if (Reason == "") // { // PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); // } // else // { // PubFunc.outPutJavaScript("alert('第" + (i + 1) + "行" + Reason + "')"); // } // } //}
#endregion
string sqlCounts = "select * from Vcl_Info where Vcl_No='" + Vcl_No + "' and Vcl_Exp<'" + DateTime.Now.ToString() + "' "; DataTable dtones = PubFunc.ReturnDataTable(sqlCounts, "Table"); int counts = dtones.Rows.Count;
string sqlCount = "delete from Vcl_Info where Vcl_No='" + Vcl_No + "'"; int count=PubFunc.ExecuteNonQuery(sqlCount);
string sqlIsCarNoRpt = "select Vcl_No,Vcl_ID from " + ReturnConnText_VCL_database + ".dbo.Vcl_Info where Vcl_Exp>'" + DateTime.Now.ToString() + "' and Vcl_No='" + Vcl_No + "'"; DataTable dtid = PubFunc.ReturnDataTable(sqlIsCarNoRpt, "Vcl_NoTab");
Czy user = Czy.GetCzyInfo();
#region 确定Vcl_ID string Vcl_ID; string ResultFlag; string Reason = ""; if (dtid.Rows.Count == 0) { decimal OrganID = Convert.ToDecimal(user.Organ_ID); ArrayList arrSystemID = new ArrayList(); arrSystemID.Add("4"); string ip = Page.Request.UserHostAddress; UnifiedPlatformInterface IVclAdd = new UnifiedPlatformInterface(); IVclAdd.Add_Vcl_Info(Vcl_No.Trim(), Vcl_UnderpanCode.Trim(), "4", "", arrSystemID, user.Acnt_ID, System.DateTime.Now.ToString(), ip, "4"); ResultFlag = IVclAdd.ResultFlag; Vcl_ID = IVclAdd.Vcl_ID; Reason = IVclAdd.Reason; } else { Vcl_ID = dtid.Rows[0]["Vcl_ID"].ToString();
decimal OrganID = Convert.ToDecimal(user.Organ_ID); ArrayList arrSystemID = new ArrayList(); arrSystemID.Add("4"); string ip = Page.Request.UserHostAddress; UnifiedPlatformInterface IVclUpdate = new UnifiedPlatformInterface(); IVclUpdate.Update_Vcl_Info(Vcl_ID, Vcl_No.Trim(), Vcl_UnderpanCode.Trim(), "4", "", user.Acnt_ID, System.DateTime.Now.ToString(), ip, "4");
ResultFlag = "1"; } #endregion
if (ResultFlag == "1") { sqlVcl = "INSERT INTO Vcl_Info" + "(Vcl_ID " + ",Vcl_No " + ",Vcl_Organ_Dealer " + ",Vcl_Organ_OEM " + ",Vcl_UnderpanCode " + ",Vcl_Manufacturer " + ",Vcl_Type " + ",Vcl_TotalWeight " + ",Vcl_TotalPrepareWeight " + ",Vcl_MaxTakeoffWeight " + ",Vcl_EngineType " + ",Vcl_EngineNo " + ",Vcl_LinkManFirst " + ",Vcl_LinkManFirstMobile " + ",Vcl_LinkManSecond " + ",Vcl_LinkManSecondMobile " //+ ",Vcl_NoLoad" //+ ",Vcl_Load" //+ ",Vcl_FullLoad" //+ ",Vcl_LoadSetDate + " ) " + "VALUES " + "('" + Vcl_ID + "' " + ",'" + Vcl_No.Trim() + "' "; if (Vcl_Organ_Dealer.Trim() != "") { sqlVcl += ",'" + Vcl_Organ_Dealer.Trim() + "' "; } else { sqlVcl += ",null"; } if (Vcl_Organ_OEM.Trim() != "") { sqlVcl += ",'" + Vcl_Organ_OEM.Trim() + "' "; } else { sqlVcl += ",null"; } sqlVcl += ",'" + Vcl_UnderpanCode.Trim() + "' " + ",'" + Vcl_Manufacturer.Trim() + "' " + ",'" + Vcl_Type.Trim() + "' " + ",'" + Vcl_TotalWeight.Trim() + "' " + ",'" + Vcl_TotalPrepareWeight.Trim() + "' " + ",'" + Vcl_MaxTakeoffWeight.Trim() + "' " + ",'" + Vcl_EngineType.Trim() + "' " + ",'" + Vcl_EngineNo.Trim() + "' " + ",'" + Vcl_LinkManFirst.Trim() + "' " + ",'" + Vcl_LinkManFirstMobile.Trim() + "' " + ",'" + Vcl_LinkManSecond.Trim() + "' " + ",'" + Vcl_LinkManSecondMobile.Trim() + "' "; int load = 0; //if (txtNoLoadMax.Text.Trim() != "") //{ // sqlVcl += ",'" + txtNoLoadMax.Text.Trim() + "' "; // load++; //} //else //{ // sqlVcl += ",null"; //} //if (txtLoadMax.Text.Trim() != "") //{ // sqlVcl += ",'" + txtLoadMax.Text.Trim() + "'"; // load++; //} //else //{ // sqlVcl += ",null"; //} //if (txtFullLoadMax.Text.Trim() != "") //{ // sqlVcl += ",'" + txtFullLoadMax.Text.Trim() + "'"; // load++; //} //else //{ // sqlVcl += ",null"; //} //if (load > 0) //{ // sqlVcl += ",'" + DateTime.Now.ToString() + "') "; //} //else //{ // sqlVcl += ",null) "; //} sqlVcl += ")";
try { int rst = PubFunc.ExecuteNonQuery(sqlVcl); if (rst == -9999) { PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); } else { // 添加操作日志 LogDB.LogDBOperationIns(sqlVcl, "", Convert.ToInt32(Vcl_ID), "Vcl_Info_OprtLog", "VclOL_", false, 0, false, user); if (counts > 0) { insertcount++; } else if (count > 0) { updatecount++; } else { insertcount++; } } } catch { PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); } finally { }
} else { if (Reason == "") { PubFunc.outPutJavaScript("alert('提交时异常,请重试!')"); } else { PubFunc.outPutJavaScript("alert('第" + (i + 1) + "行" + Reason + "')"); } } }
PubFunc.outPutJavaScript("alert('" + insertcount + "条数据导入成功!" + updatecount + "条数据更新!')"); } catch (Exception ex) {
} } #endregion