Excel批量导入
#region Excel批量导入 /// <summary> /// Excel批量导入（form表单方式提交数据） /// </summary> /// <returns></returns> [HttpPost] public List<ReturnObject> UploadImportPrize() { List<ReturnObject> d = new List<ReturnObject>(); ReturnObject ret = new ReturnObject(); List<string> errors = new List<string>(); List<string> fails = new List<string>(); int Type = Convert.ToInt32(HttpContext.Current.Request["Type"]); string completePath = ""; HttpFileCollection filelist = HttpContext.Current.Request.Files; if (filelist != null && filelist.Count > 0) { for (int i = 0; i < filelist.Count; i++) { HttpPostedFile file = filelist[i]; String Tpath = "import/"; ; string filename = DateTime.Now.ToString("HHmmss") +"-"+ file.FileName; string FilePath = "C:\\" + Tpath + filename; //这里应该获取当前项目路径地址，再在后面创建文件，如果按上面的注释掉的写法，在服务器上没有找到d盘，则会报错。 //string FilePath = System.Web.Hosting.HostingEnvironment.MapPath(@"~/") + filename; string diPath = Path.GetDirectoryName(FilePath); //获取到当前目录的文件夹，没有就创建 if (!Directory.Exists(diPath)) { Directory.CreateDirectory(diPath); }; try { completePath = FilePath; file.SaveAs(completePath); //生成一个文件目录，把上传的文件写入到目录中去， d = ImportPrize(Type,completePath); //然后获取这个目录的文件，用DataTable进行读取，然后解析excel的每行数据，批量写入到数据库中 } catch (Exception ex) { ret.msg = "上传文件写入失败：" + ex.Message; ret.isOK = false; ret.errorCode = 3; d.Add(ret); return d; } } } else { ret.msg = "上传的文件信息不存在！"; ret.isOK = false; ret.errorCode = 3; d.Add(ret); return d; } return d; } /// <summary> /// /// </summary> /// <param name="path"></param> /// <returns></returns> private DataTable ReadExcelToTable(string path) { DataTable result = new DataTable(); Workbook workbook = new Workbook(); workbook.Open(path); Cells cells = workbook.Worksheets[0].Cells; result = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, false); return result; } /// <summary> /// 写入数据到数据库 /// </summary> /// <param name="Type"></param> /// <param name="completePath"></param> /// <returns></returns> [HttpGet] public List<ReturnObject> ImportPrize(int Type, string completePath) { List<InPrize> list = new List<InPrize>(); List<ReturnObject> retlist = new List<ReturnObject>(); if (Type == 1) { var jinxing = DbHelperSQL.GetDataTable("SELECT Num from ProcessMain where StepState=1"); if (jinxing.Rows.Count > 0) { var bznum = jinxing.Rows[0]["Num"].ToString(); ReturnObject ret = new ReturnObject(); ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！"; ret.errorCode = 500; retlist.Add(ret); return retlist; } else { #region MyRegion var a = ReadExcelToTable(completePath).Rows; var msg = ""; var intStr = ""; var intStr1 = ""; var oil_zuoye = ""; var oil_jieshou = ""; var PhaseName = ""; var StepContent = ""; var str = ""; var RelatedValve = ""; var RelatedYb = ""; var cname = ""; var intStr9 = ""; var Manual = ""; var StepState = ""; for (var i = 2; i < a.Count; i++) { #region 错误信息判断 int intType1; intStr1 = a[i][0].ToString(); if (!int.TryParse(intStr1, out intType1)) { ReturnObject ret = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！"; ret.isOK = false; ret.msg = msg; ret.errorCode = 500; retlist.Add(ret); } int intType; intStr = a[i][1].ToString(); if (!int.TryParse(intStr, out intType)) { ReturnObject ret2 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！"; ret2.isOK = false; ret2.msg = msg; ret2.errorCode = 500; retlist.Add(ret2); } oil_zuoye = a[i][2].ToString(); if (string.IsNullOrWhiteSpace(oil_zuoye)) { ReturnObject ret3 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！"; ret3.isOK = false; ret3.msg = msg; ret3.errorCode = 500; retlist.Add(ret3); } oil_jieshou = a[i][3].ToString(); if (string.IsNullOrWhiteSpace(oil_jieshou)) { ReturnObject ret4 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！"; ret4.isOK = false; ret4.msg = msg; ret4.errorCode = 500; retlist.Add(ret4); } if (oil_zuoye == oil_jieshou) { ReturnObject ret21 = new ReturnObject(); msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！"; ret21.isOK = false; ret21.msg = msg; ret21.errorCode = 500; retlist.Add(ret21); } PhaseName = a[i][4].ToString(); if (string.IsNullOrWhiteSpace(PhaseName)) { ReturnObject ret5 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！"; ret5.isOK = false; ret5.msg = msg; ret5.errorCode = 500; retlist.Add(ret5); } StepContent = a[i][5].ToString(); if (string.IsNullOrWhiteSpace(StepContent)) { ReturnObject ret6 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！"; ret6.isOK = false; ret6.msg = msg; ret6.errorCode = 500; retlist.Add(ret6); } str = ""; var RelatedJobs = a[i][6].ToString(); if (!string.IsNullOrWhiteSpace(RelatedJobs)) { string[] id = RelatedJobs.Split(','); for (int j = 0; j < id.Length; j++) { var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'"); if (tab.Rows.Count > 0) { var jobname = tab.Rows[0]["Job_Id"].ToString(); str += "" + jobname + ","; } else { ReturnObject ret7 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】"; ret7.isOK = false; ret7.msg = msg; ret7.errorCode = 500; retlist.Add(ret7); } } str = str.TrimEnd(','); } RelatedValve = a[i][7].ToString(); RelatedYb = a[i][8].ToString(); cname = ""; var Company = a[i][9].ToString(); if (!string.IsNullOrWhiteSpace(Company)) { var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'"); if (tab.Rows.Count > 0) { cname = tab.Rows[0]["Company_Id"].ToString(); } else { ReturnObject ret8 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！"; ret8.isOK = false; ret8.msg = msg; ret8.errorCode = 500; retlist.Add(ret8); } } int intType9; intStr9 = a[i][10].ToString(); if (!int.TryParse(intStr9, out intType9)) { ReturnObject ret9 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！"; ret9.isOK = false; ret9.msg = msg; ret9.errorCode = 500; retlist.Add(ret9); } int intType13; Manual = a[i][11].ToString(); if (!int.TryParse(Manual, out intType13)) { ReturnObject ret10 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！"; ret10.isOK = false; ret10.msg = msg; ret10.errorCode = 500; retlist.Add(ret10); } int intType14; StepState = a[i][12].ToString(); if (!int.TryParse(StepState, out intType14)) { ReturnObject ret11 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！"; ret11.isOK = false; ret11.msg = msg; ret11.errorCode = 500; retlist.Add(ret11); } //int intType15; Conten = a[i][13].ToString(); //if (!int.TryParse(Conten, out intType15)) //{ // ReturnObject ret12 = new ReturnObject(); // msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！"; // ret12.isOK = false; // ret12.msg = msg; // ret12.errorCode = 500; // retlist.Add(ret12); //} #endregion if (retlist.Count > 0) { return retlist; } else { list.Add(new InPrize() { ID = Guid.NewGuid().ToString(), Type = Convert.ToInt32(intStr1), Num = Convert.ToInt32(intStr), JobOil = oil_zuoye, RecepTionOil = oil_jieshou, PhaseName = PhaseName, StepContent = StepContent, RelatedJobs = str, RelatedValve = RelatedValve, RelatedYb = RelatedYb, Company = cname, Confirmation = Convert.ToInt32(intStr9), Manual = Convert.ToInt32(Manual), StepState = Convert.ToInt32(StepState), }); } } bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1); if (isRepeat == true) { ReturnObject ret22 = new ReturnObject(); ret22.isOK = false; ret22.msg = "步骤序号，有重复项请重新填写！"; ret22.errorCode = 500; retlist.Add(ret22); return retlist; } var CompanyName = a[2][9].ToString(); var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'"); cname = ta.Rows[0]["Company_Id"].ToString(); int tab22 = DbHelperSQL.GetDataTable("SELECT * from ProcessMain where Company='" + cname + "'").Rows.Count; if (tab22 > 0) { var n4 = DbHelperSQL.ExecuteSql("DELETE from ProcessMain"); } foreach (var n1 in list) { var sql = "INSERT INTO ProcessMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")"; var n2 = DbHelperSQL.ExecuteSql(sql); } return retlist; #endregion } } if (Type == 2) { var jinxing = DbHelperSQL.GetDataTable("SELECT Num from HairPortMain where StepState=1"); if (jinxing.Rows.Count > 0) { var bznum = jinxing.Rows[0]["Num"].ToString(); ReturnObject ret = new ReturnObject(); ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！"; ret.errorCode = 500; retlist.Add(ret); return retlist; } else { #region MyRegion var a = ReadExcelToTable(completePath).Rows; var msg = ""; var intStr = ""; var intStr1 = ""; var oil_zuoye = ""; var oil_jieshou = ""; var PhaseName = ""; var StepContent = ""; var str = ""; var RelatedValve = ""; var RelatedYb = ""; var cname = ""; var intStr9 = ""; var Manual = ""; var StepState = ""; for (var i = 2; i < a.Count; i++) { #region 错误信息判断 int intType1; intStr1 = a[i][0].ToString(); if (!int.TryParse(intStr1, out intType1)) { ReturnObject ret = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！"; ret.isOK = false; ret.msg = msg; ret.errorCode = 500; retlist.Add(ret); } int intType; intStr = a[i][1].ToString(); if (!int.TryParse(intStr, out intType)) { ReturnObject ret2 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！"; ret2.isOK = false; ret2.msg = msg; ret2.errorCode = 500; retlist.Add(ret2); } oil_zuoye = a[i][2].ToString(); if (string.IsNullOrWhiteSpace(oil_zuoye)) { ReturnObject ret3 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！"; ret3.isOK = false; ret3.msg = msg; ret3.errorCode = 500; retlist.Add(ret3); } oil_jieshou = a[i][3].ToString(); if (string.IsNullOrWhiteSpace(oil_jieshou)) { ReturnObject ret4 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！"; ret4.isOK = false; ret4.msg = msg; ret4.errorCode = 500; retlist.Add(ret4); } if (oil_zuoye == oil_jieshou) { ReturnObject ret21 = new ReturnObject(); msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！"; ret21.isOK = false; ret21.msg = msg; ret21.errorCode = 500; retlist.Add(ret21); } PhaseName = a[i][4].ToString(); if (string.IsNullOrWhiteSpace(PhaseName)) { ReturnObject ret5 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！"; ret5.isOK = false; ret5.msg = msg; ret5.errorCode = 500; retlist.Add(ret5); } StepContent = a[i][5].ToString(); if (string.IsNullOrWhiteSpace(StepContent)) { ReturnObject ret6 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！"; ret6.isOK = false; ret6.msg = msg; ret6.errorCode = 500; retlist.Add(ret6); } str = ""; var RelatedJobs = a[i][6].ToString(); if (!string.IsNullOrWhiteSpace(RelatedJobs)) { string[] id = RelatedJobs.Split(','); for (int j = 0; j < id.Length; j++) { var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'"); if (tab.Rows.Count > 0) { var jobname = tab.Rows[0]["Job_Id"].ToString(); str += "" + jobname + ","; } else { ReturnObject ret7 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】"; ret7.isOK = false; ret7.msg = msg; ret7.errorCode = 500; retlist.Add(ret7); } } str = str.TrimEnd(','); } RelatedValve = a[i][7].ToString(); RelatedYb = a[i][8].ToString(); cname = ""; var Company = a[i][9].ToString(); if (!string.IsNullOrWhiteSpace(Company)) { var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'"); if (tab.Rows.Count > 0) { cname = tab.Rows[0]["Company_Id"].ToString(); } else { ReturnObject ret8 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！"; ret8.isOK = false; ret8.msg = msg; ret8.errorCode = 500; retlist.Add(ret8); } } int intType9; intStr9 = a[i][10].ToString(); if (!int.TryParse(intStr9, out intType9)) { ReturnObject ret9 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！"; ret9.isOK = false; ret9.msg = msg; ret9.errorCode = 500; retlist.Add(ret9); } int intType13; Manual = a[i][11].ToString(); if (!int.TryParse(Manual, out intType13)) { ReturnObject ret10 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！"; ret10.isOK = false; ret10.msg = msg; ret10.errorCode = 500; retlist.Add(ret10); } int intType14; StepState = a[i][12].ToString(); if (!int.TryParse(StepState, out intType14)) { ReturnObject ret11 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！"; ret11.isOK = false; ret11.msg = msg; ret11.errorCode = 500; retlist.Add(ret11); } //int intType15; Conten = a[i][13].ToString(); //if (!int.TryParse(Conten, out intType15)) //{ // ReturnObject ret12 = new ReturnObject(); // msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！"; // ret12.isOK = false; // ret12.msg = msg; // ret12.errorCode = 500; // retlist.Add(ret12); //} #endregion if (retlist.Count > 0) { return retlist; } else { list.Add(new InPrize() { ID = Guid.NewGuid().ToString(), Type = Convert.ToInt32(intStr1), Num = Convert.ToInt32(intStr), JobOil = oil_zuoye, RecepTionOil = oil_jieshou, PhaseName = PhaseName, StepContent = StepContent, RelatedJobs = str, RelatedValve = RelatedValve, RelatedYb = RelatedYb, Company = cname, Confirmation = Convert.ToInt32(intStr9), Manual = Convert.ToInt32(Manual), StepState = Convert.ToInt32(StepState), }); } } bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1); if (isRepeat == true) { ReturnObject ret22 = new ReturnObject(); ret22.isOK = false; ret22.msg = "步骤序号，有重复项请重新填写！"; ret22.errorCode = 500; retlist.Add(ret22); return retlist; } var CompanyName = a[2][9].ToString(); var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'"); cname = ta.Rows[0]["Company_Id"].ToString(); int tab22 = DbHelperSQL.GetDataTable("SELECT * from HairPortMain where Company='" + cname + "'").Rows.Count; if (tab22 > 0) { var n4 = DbHelperSQL.ExecuteSql("DELETE from HairPortMain"); } foreach (var n1 in list) { var sql = "INSERT INTO HairPortMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")"; var n2 = DbHelperSQL.ExecuteSql(sql); } return retlist; #endregion } } #region 3 if (Type == 3) { var jinxing = DbHelperSQL.GetDataTable("SELECT Num from ReceMain where StepState=1"); if (jinxing.Rows.Count > 0) { var bznum = jinxing.Rows[0]["Num"].ToString(); ReturnObject ret = new ReturnObject(); ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！"; ret.errorCode = 500; retlist.Add(ret); return retlist; } else { #region MyRegion var a = ReadExcelToTable(completePath).Rows; var msg = ""; var intStr = ""; var intStr1 = ""; var oil_zuoye = ""; var oil_jieshou = ""; var PhaseName = ""; var StepContent = ""; var str = ""; var RelatedValve = ""; var RelatedYb = ""; var cname = ""; var intStr9 = ""; var Manual = ""; var StepState = ""; for (var i = 2; i < a.Count; i++) { #region 错误信息判断 int intType1; intStr1 = a[i][0].ToString(); if (!int.TryParse(intStr1, out intType1)) { ReturnObject ret = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！"; ret.isOK = false; ret.msg = msg; ret.errorCode = 500; retlist.Add(ret); } int intType; intStr = a[i][1].ToString(); if (!int.TryParse(intStr, out intType)) { ReturnObject ret2 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！"; ret2.isOK = false; ret2.msg = msg; ret2.errorCode = 500; retlist.Add(ret2); } oil_zuoye = a[i][2].ToString(); if (string.IsNullOrWhiteSpace(oil_zuoye)) { ReturnObject ret3 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！"; ret3.isOK = false; ret3.msg = msg; ret3.errorCode = 500; retlist.Add(ret3); } oil_jieshou = a[i][3].ToString(); if (string.IsNullOrWhiteSpace(oil_jieshou)) { ReturnObject ret4 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！"; ret4.isOK = false; ret4.msg = msg; ret4.errorCode = 500; retlist.Add(ret4); } if (oil_zuoye == oil_jieshou) { ReturnObject ret21 = new ReturnObject(); msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！"; ret21.isOK = false; ret21.msg = msg; ret21.errorCode = 500; retlist.Add(ret21); } PhaseName = a[i][4].ToString(); if (string.IsNullOrWhiteSpace(PhaseName)) { ReturnObject ret5 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！"; ret5.isOK = false; ret5.msg = msg; ret5.errorCode = 500; retlist.Add(ret5); } StepContent = a[i][5].ToString(); if (string.IsNullOrWhiteSpace(StepContent)) { ReturnObject ret6 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！"; ret6.isOK = false; ret6.msg = msg; ret6.errorCode = 500; retlist.Add(ret6); } str = ""; var RelatedJobs = a[i][6].ToString(); if (!string.IsNullOrWhiteSpace(RelatedJobs)) { string[] id = RelatedJobs.Split(','); for (int j = 0; j < id.Length; j++) { var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'"); if (tab.Rows.Count > 0) { var jobname = tab.Rows[0]["Job_Id"].ToString(); str += "" + jobname + ","; } else { ReturnObject ret7 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】"; ret7.isOK = false; ret7.msg = msg; ret7.errorCode = 500; retlist.Add(ret7); } } str = str.TrimEnd(','); } RelatedValve = a[i][7].ToString(); RelatedYb = a[i][8].ToString(); cname = ""; var Company = a[i][9].ToString(); if (!string.IsNullOrWhiteSpace(Company)) { var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'"); if (tab.Rows.Count > 0) { cname = tab.Rows[0]["Company_Id"].ToString(); } else { ReturnObject ret8 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！"; ret8.isOK = false; ret8.msg = msg; ret8.errorCode = 500; retlist.Add(ret8); } } int intType9; intStr9 = a[i][10].ToString(); if (!int.TryParse(intStr9, out intType9)) { ReturnObject ret9 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！"; ret9.isOK = false; ret9.msg = msg; ret9.errorCode = 500; retlist.Add(ret9); } int intType13; Manual = a[i][11].ToString(); if (!int.TryParse(Manual, out intType13)) { ReturnObject ret10 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！"; ret10.isOK = false; ret10.msg = msg; ret10.errorCode = 500; retlist.Add(ret10); } int intType14; StepState = a[i][12].ToString(); if (!int.TryParse(StepState, out intType14)) { ReturnObject ret11 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！"; ret11.isOK = false; ret11.msg = msg; ret11.errorCode = 500; retlist.Add(ret11); } //int intType15; Conten = a[i][13].ToString(); //if (!int.TryParse(Conten, out intType15)) //{ // ReturnObject ret12 = new ReturnObject(); // msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！"; // ret12.isOK = false; // ret12.msg = msg; // ret12.errorCode = 500; // retlist.Add(ret12); //} #endregion if (retlist.Count > 0) { return retlist; } else { list.Add(new InPrize() { ID = Guid.NewGuid().ToString(), Type = Convert.ToInt32(intStr1), Num = Convert.ToInt32(intStr), JobOil = oil_zuoye, RecepTionOil = oil_jieshou, PhaseName = PhaseName, StepContent = StepContent, RelatedJobs = str, RelatedValve = RelatedValve, RelatedYb = RelatedYb, Company = cname, Confirmation = Convert.ToInt32(intStr9), Manual = Convert.ToInt32(Manual), StepState = Convert.ToInt32(StepState), }); } } bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1); if (isRepeat == true) { ReturnObject ret22 = new ReturnObject(); ret22.isOK = false; ret22.msg = "步骤序号，有重复项请重新填写！"; ret22.errorCode = 500; retlist.Add(ret22); return retlist; } var CompanyName = a[2][9].ToString(); var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'"); cname = ta.Rows[0]["Company_Id"].ToString(); int tab22 = DbHelperSQL.GetDataTable("SELECT * from ReceMain where Company='" + cname + "'").Rows.Count; if (tab22 > 0) { var n4 = DbHelperSQL.ExecuteSql("DELETE from ReceMain"); } foreach (var n1 in list) { var sql = "INSERT INTO ReceMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")"; var n2 = DbHelperSQL.ExecuteSql(sql); } return retlist; #endregion } } #endregion #region 4 if (Type == 4) { var jinxing = DbHelperSQL.GetDataTable("SELECT Num from TankMain where StepState=1"); if (jinxing.Rows.Count > 0) { var bznum = jinxing.Rows[0]["Num"].ToString(); ReturnObject ret = new ReturnObject(); ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！"; ret.errorCode = 500; retlist.Add(ret); return retlist; } else { #region MyRegion var a = ReadExcelToTable(completePath).Rows; var msg = ""; var intStr = ""; var intStr1 = ""; var oil_zuoye = ""; var oil_jieshou = ""; var PhaseName = ""; var StepContent = ""; var str = ""; var RelatedValve = ""; var RelatedYb = ""; var cname = ""; var intStr9 = ""; var Manual = ""; var StepState = ""; for (var i = 2; i < a.Count; i++) { #region 错误信息判断 int intType1; intStr1 = a[i][0].ToString(); if (!int.TryParse(intStr1, out intType1)) { ReturnObject ret = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！"; ret.isOK = false; ret.msg = msg; ret.errorCode = 500; retlist.Add(ret); } int intType; intStr = a[i][1].ToString(); if (!int.TryParse(intStr, out intType)) { ReturnObject ret2 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！"; ret2.isOK = false; ret2.msg = msg; ret2.errorCode = 500; retlist.Add(ret2); } oil_zuoye = a[i][2].ToString(); if (string.IsNullOrWhiteSpace(oil_zuoye)) { ReturnObject ret3 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！"; ret3.isOK = false; ret3.msg = msg; ret3.errorCode = 500; retlist.Add(ret3); } oil_jieshou = a[i][3].ToString(); if (string.IsNullOrWhiteSpace(oil_jieshou)) { ReturnObject ret4 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！"; ret4.isOK = false; ret4.msg = msg; ret4.errorCode = 500; retlist.Add(ret4); } if (oil_zuoye == oil_jieshou) { ReturnObject ret21 = new ReturnObject(); msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！"; ret21.isOK = false; ret21.msg = msg; ret21.errorCode = 500; retlist.Add(ret21); } PhaseName = a[i][4].ToString(); if (string.IsNullOrWhiteSpace(PhaseName)) { ReturnObject ret5 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！"; ret5.isOK = false; ret5.msg = msg; ret5.errorCode = 500; retlist.Add(ret5); } StepContent = a[i][5].ToString(); if (string.IsNullOrWhiteSpace(StepContent)) { ReturnObject ret6 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！"; ret6.isOK = false; ret6.msg = msg; ret6.errorCode = 500; retlist.Add(ret6); } str = ""; var RelatedJobs = a[i][6].ToString(); if (!string.IsNullOrWhiteSpace(RelatedJobs)) { string[] id = RelatedJobs.Split(','); for (int j = 0; j < id.Length; j++) { var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'"); if (tab.Rows.Count > 0) { var jobname = tab.Rows[0]["Job_Id"].ToString(); str += "" + jobname + ","; } else { ReturnObject ret7 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】"; ret7.isOK = false; ret7.msg = msg; ret7.errorCode = 500; retlist.Add(ret7); } } str = str.TrimEnd(','); } RelatedValve = a[i][7].ToString(); RelatedYb = a[i][8].ToString(); cname = ""; var Company = a[i][9].ToString(); if (!string.IsNullOrWhiteSpace(Company)) { var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'"); if (tab.Rows.Count > 0) { cname = tab.Rows[0]["Company_Id"].ToString(); } else { ReturnObject ret8 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！"; ret8.isOK = false; ret8.msg = msg; ret8.errorCode = 500; retlist.Add(ret8); } } int intType9; intStr9 = a[i][10].ToString(); if (!int.TryParse(intStr9, out intType9)) { ReturnObject ret9 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！"; ret9.isOK = false; ret9.msg = msg; ret9.errorCode = 500; retlist.Add(ret9); } int intType13; Manual = a[i][11].ToString(); if (!int.TryParse(Manual, out intType13)) { ReturnObject ret10 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！"; ret10.isOK = false; ret10.msg = msg; ret10.errorCode = 500; retlist.Add(ret10); } int intType14; StepState = a[i][12].ToString(); if (!int.TryParse(StepState, out intType14)) { ReturnObject ret11 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！"; ret11.isOK = false; ret11.msg = msg; ret11.errorCode = 500; retlist.Add(ret11); } //int intType15; Conten = a[i][13].ToString(); //if (!int.TryParse(Conten, out intType15)) //{ // ReturnObject ret12 = new ReturnObject(); // msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！"; // ret12.isOK = false; // ret12.msg = msg; // ret12.errorCode = 500; // retlist.Add(ret12); //} #endregion if (retlist.Count > 0) { return retlist; } else { list.Add(new InPrize() { ID = Guid.NewGuid().ToString(), Type = Convert.ToInt32(intStr1), Num = Convert.ToInt32(intStr), JobOil = oil_zuoye, RecepTionOil = oil_jieshou, PhaseName = PhaseName, StepContent = StepContent, RelatedJobs = str, RelatedValve = RelatedValve, RelatedYb = RelatedYb, Company = cname, Confirmation = Convert.ToInt32(intStr9), Manual = Convert.ToInt32(Manual), StepState = Convert.ToInt32(StepState), }); } } bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1); if (isRepeat == true) { ReturnObject ret22 = new ReturnObject(); ret22.isOK = false; ret22.msg = "步骤序号，有重复项请重新填写！"; ret22.errorCode = 500; retlist.Add(ret22); return retlist; } var CompanyName = a[2][9].ToString(); var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'"); cname = ta.Rows[0]["Company_Id"].ToString(); int tab22 = DbHelperSQL.GetDataTable("SELECT * from TankMain where Company='" + cname + "'").Rows.Count; if (tab22 > 0) { var n4 = DbHelperSQL.ExecuteSql("DELETE from TankMain"); } foreach (var n1 in list) { var sql = "INSERT INTO TankMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")"; var n2 = DbHelperSQL.ExecuteSql(sql); } return retlist; #endregion } } #endregion #region 5 if (Type == 5) { var jinxing = DbHelperSQL.GetDataTable("SELECT Num from BrIngMain where StepState=1"); if (jinxing.Rows.Count > 0) { var bznum = jinxing.Rows[0]["Num"].ToString(); ReturnObject ret = new ReturnObject(); ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！"; ret.errorCode = 500; retlist.Add(ret); return retlist; } else { #region MyRegion var a = ReadExcelToTable(completePath).Rows; var msg = ""; var intStr = ""; var intStr1 = ""; var oil_zuoye = ""; var oil_jieshou = ""; var PhaseName = ""; var StepContent = ""; var str = ""; var RelatedValve = ""; var RelatedYb = ""; var cname = ""; var intStr9 = ""; var Manual = ""; var StepState = ""; for (var i = 2; i < a.Count; i++) { #region 错误信息判断 int intType1; intStr1 = a[i][0].ToString(); if (!int.TryParse(intStr1, out intType1)) { ReturnObject ret = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！"; ret.isOK = false; ret.msg = msg; ret.errorCode = 500; retlist.Add(ret); } int intType; intStr = a[i][1].ToString(); if (!int.TryParse(intStr, out intType)) { ReturnObject ret2 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！"; ret2.isOK = false; ret2.msg = msg; ret2.errorCode = 500; retlist.Add(ret2); } oil_zuoye = a[i][2].ToString(); if (string.IsNullOrWhiteSpace(oil_zuoye)) { ReturnObject ret3 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！"; ret3.isOK = false; ret3.msg = msg; ret3.errorCode = 500; retlist.Add(ret3); } oil_jieshou = a[i][3].ToString(); if (string.IsNullOrWhiteSpace(oil_jieshou)) { ReturnObject ret4 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！"; ret4.isOK = false; ret4.msg = msg; ret4.errorCode = 500; retlist.Add(ret4); } if (oil_zuoye == oil_jieshou) { ReturnObject ret21 = new ReturnObject(); msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！"; ret21.isOK = false; ret21.msg = msg; ret21.errorCode = 500; retlist.Add(ret21); } PhaseName = a[i][4].ToString(); if (string.IsNullOrWhiteSpace(PhaseName)) { ReturnObject ret5 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！"; ret5.isOK = false; ret5.msg = msg; ret5.errorCode = 500; retlist.Add(ret5); } StepContent = a[i][5].ToString(); if (string.IsNullOrWhiteSpace(StepContent)) { ReturnObject ret6 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！"; ret6.isOK = false; ret6.msg = msg; ret6.errorCode = 500; retlist.Add(ret6); } str = ""; var RelatedJobs = a[i][6].ToString(); if (!string.IsNullOrWhiteSpace(RelatedJobs)) { string[] id = RelatedJobs.Split(','); for (int j = 0; j < id.Length; j++) { var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'"); if (tab.Rows.Count > 0) { var jobname = tab.Rows[0]["Job_Id"].ToString(); str += "" + jobname + ","; } else { ReturnObject ret7 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】"; ret7.isOK = false; ret7.msg = msg; ret7.errorCode = 500; retlist.Add(ret7); } } str = str.TrimEnd(','); } RelatedValve = a[i][7].ToString(); RelatedYb = a[i][8].ToString(); cname = ""; var Company = a[i][9].ToString(); if (!string.IsNullOrWhiteSpace(Company)) { var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'"); if (tab.Rows.Count > 0) { cname = tab.Rows[0]["Company_Id"].ToString(); } else { ReturnObject ret8 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！"; ret8.isOK = false; ret8.msg = msg; ret8.errorCode = 500; retlist.Add(ret8); } } int intType9; intStr9 = a[i][10].ToString(); if (!int.TryParse(intStr9, out intType9)) { ReturnObject ret9 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！"; ret9.isOK = false; ret9.msg = msg; ret9.errorCode = 500; retlist.Add(ret9); } int intType13; Manual = a[i][11].ToString(); if (!int.TryParse(Manual, out intType13)) { ReturnObject ret10 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！"; ret10.isOK = false; ret10.msg = msg; ret10.errorCode = 500; retlist.Add(ret10); } int intType14; StepState = a[i][12].ToString(); if (!int.TryParse(StepState, out intType14)) { ReturnObject ret11 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！"; ret11.isOK = false; ret11.msg = msg; ret11.errorCode = 500; retlist.Add(ret11); } //int intType15; Conten = a[i][13].ToString(); //if (!int.TryParse(Conten, out intType15)) //{ // ReturnObject ret12 = new ReturnObject(); // msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！"; // ret12.isOK = false; // ret12.msg = msg; // ret12.errorCode = 500; // retlist.Add(ret12); //} #endregion if (retlist.Count > 0) { return retlist; } else { list.Add(new InPrize() { ID = Guid.NewGuid().ToString(), Type = Convert.ToInt32(intStr1), Num = Convert.ToInt32(intStr), JobOil = oil_zuoye, RecepTionOil = oil_jieshou, PhaseName = PhaseName, StepContent = StepContent, RelatedJobs = str, RelatedValve = RelatedValve, RelatedYb = RelatedYb, Company = cname, Confirmation = Convert.ToInt32(intStr9), Manual = Convert.ToInt32(Manual), StepState = Convert.ToInt32(StepState), }); } } bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1); if (isRepeat == true) { ReturnObject ret22 = new ReturnObject(); ret22.isOK = false; ret22.msg = "步骤序号，有重复项请重新填写！"; ret22.errorCode = 500; retlist.Add(ret22); return retlist; } var CompanyName = a[2][9].ToString(); var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'"); cname = ta.Rows[0]["Company_Id"].ToString(); int tab22 = DbHelperSQL.GetDataTable("SELECT * from BrIngMain where Company='" + cname + "'").Rows.Count; if (tab22 > 0) { var n4 = DbHelperSQL.ExecuteSql("DELETE from BrIngMain"); } foreach (var n1 in list) { var sql = "INSERT INTO BrIngMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")"; var n2 = DbHelperSQL.ExecuteSql(sql); } return retlist; #endregion } } #endregion #region 6 if (Type == 6) { var jinxing = DbHelperSQL.GetDataTable("SELECT Num from RecyMain where StepState=1"); if (jinxing.Rows.Count > 0) { var bznum = jinxing.Rows[0]["Num"].ToString(); ReturnObject ret = new ReturnObject(); ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！"; ret.errorCode = 500; retlist.Add(ret); return retlist; } else { #region MyRegion var a = ReadExcelToTable(completePath).Rows; var msg = ""; var intStr = ""; var intStr1 = ""; var oil_zuoye = ""; var oil_jieshou = ""; var PhaseName = ""; var StepContent = ""; var str = ""; var RelatedValve = ""; var RelatedYb = ""; var cname = ""; var intStr9 = ""; var Manual = ""; var StepState = ""; for (var i = 2; i < a.Count; i++) { #region 错误信息判断 int intType1; intStr1 = a[i][0].ToString(); if (!int.TryParse(intStr1, out intType1)) { ReturnObject ret = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！"; ret.isOK = false; ret.msg = msg; ret.errorCode = 500; retlist.Add(ret); } int intType; intStr = a[i][1].ToString(); if (!int.TryParse(intStr, out intType)) { ReturnObject ret2 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！"; ret2.isOK = false; ret2.msg = msg; ret2.errorCode = 500; retlist.Add(ret2); } oil_zuoye = a[i][2].ToString(); if (string.IsNullOrWhiteSpace(oil_zuoye)) { ReturnObject ret3 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！"; ret3.isOK = false; ret3.msg = msg; ret3.errorCode = 500; retlist.Add(ret3); } oil_jieshou = a[i][3].ToString(); if (string.IsNullOrWhiteSpace(oil_jieshou)) { ReturnObject ret4 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！"; ret4.isOK = false; ret4.msg = msg; ret4.errorCode = 500; retlist.Add(ret4); } if (oil_zuoye == oil_jieshou) { ReturnObject ret21 = new ReturnObject(); msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！"; ret21.isOK = false; ret21.msg = msg; ret21.errorCode = 500; retlist.Add(ret21); } PhaseName = a[i][4].ToString(); if (string.IsNullOrWhiteSpace(PhaseName)) { ReturnObject ret5 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！"; ret5.isOK = false; ret5.msg = msg; ret5.errorCode = 500; retlist.Add(ret5); } StepContent = a[i][5].ToString(); if (string.IsNullOrWhiteSpace(StepContent)) { ReturnObject ret6 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！"; ret6.isOK = false; ret6.msg = msg; ret6.errorCode = 500; retlist.Add(ret6); } str = ""; var RelatedJobs = a[i][6].ToString(); if (!string.IsNullOrWhiteSpace(RelatedJobs)) { string[] id = RelatedJobs.Split(','); for (int j = 0; j < id.Length; j++) { var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'"); if (tab.Rows.Count > 0) { var jobname = tab.Rows[0]["Job_Id"].ToString(); str += "" + jobname + ","; } else { ReturnObject ret7 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】"; ret7.isOK = false; ret7.msg = msg; ret7.errorCode = 500; retlist.Add(ret7); } } str = str.TrimEnd(','); } RelatedValve = a[i][7].ToString(); RelatedYb = a[i][8].ToString(); cname = ""; var Company = a[i][9].ToString(); if (!string.IsNullOrWhiteSpace(Company)) { var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'"); if (tab.Rows.Count > 0) { cname = tab.Rows[0]["Company_Id"].ToString(); } else { ReturnObject ret8 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！"; ret8.isOK = false; ret8.msg = msg; ret8.errorCode = 500; retlist.Add(ret8); } } int intType9; intStr9 = a[i][10].ToString(); if (!int.TryParse(intStr9, out intType9)) { ReturnObject ret9 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！"; ret9.isOK = false; ret9.msg = msg; ret9.errorCode = 500; retlist.Add(ret9); } int intType13; Manual = a[i][11].ToString(); if (!int.TryParse(Manual, out intType13)) { ReturnObject ret10 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！"; ret10.isOK = false; ret10.msg = msg; ret10.errorCode = 500; retlist.Add(ret10); } int intType14; StepState = a[i][12].ToString(); if (!int.TryParse(StepState, out intType14)) { ReturnObject ret11 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！"; ret11.isOK = false; ret11.msg = msg; ret11.errorCode = 500; retlist.Add(ret11); } //int intType15; Conten = a[i][13].ToString(); //if (!int.TryParse(Conten, out intType15)) //{ // ReturnObject ret12 = new ReturnObject(); // msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！"; // ret12.isOK = false; // ret12.msg = msg; // ret12.errorCode = 500; // retlist.Add(ret12); //} #endregion if (retlist.Count > 0) { return retlist; } else { list.Add(new InPrize() { ID = Guid.NewGuid().ToString(), Type = Convert.ToInt32(intStr1), Num = Convert.ToInt32(intStr), JobOil = oil_zuoye, RecepTionOil = oil_jieshou, PhaseName = PhaseName, StepContent = StepContent, RelatedJobs = str, RelatedValve = RelatedValve, RelatedYb = RelatedYb, Company = cname, Confirmation = Convert.ToInt32(intStr9), Manual = Convert.ToInt32(Manual), StepState = Convert.ToInt32(StepState), }); } } bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1); if (isRepeat == true) { ReturnObject ret22 = new ReturnObject(); ret22.isOK = false; ret22.msg = "步骤序号，有重复项请重新填写！"; ret22.errorCode = 500; retlist.Add(ret22); return retlist; } var CompanyName = a[2][9].ToString(); var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'"); cname = ta.Rows[0]["Company_Id"].ToString(); int tab22 = DbHelperSQL.GetDataTable("SELECT * from RecyMain where Company='" + cname + "'").Rows.Count; if (tab22 > 0) { var n4 = DbHelperSQL.ExecuteSql("DELETE from RecyMain"); } foreach (var n1 in list) { var sql = "INSERT INTO RecyMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")"; var n2 = DbHelperSQL.ExecuteSql(sql); } return retlist; #endregion } } #endregion else { var jinxing = DbHelperSQL.GetDataTable("SELECT Num from AirPortMain where StepState=1"); if (jinxing.Rows.Count > 0) { var bznum = jinxing.Rows[0]["Num"].ToString(); ReturnObject ret = new ReturnObject(); ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！"; ret.errorCode = 500; retlist.Add(ret); return retlist; } else { #region MyRegion var a = ReadExcelToTable(completePath).Rows; var msg = ""; var intStr = ""; var intStr1 = ""; var oil_zuoye = ""; var oil_jieshou = ""; var PhaseName = ""; var StepContent = ""; var str = ""; var RelatedValve = ""; var RelatedYb = ""; var cname = ""; var intStr9 = ""; var Manual = ""; var StepState = ""; for (var i = 2; i < a.Count; i++) { #region 错误信息判断 int intType1; intStr1 = a[i][0].ToString(); if (!int.TryParse(intStr1, out intType1)) { ReturnObject ret = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！"; ret.isOK = false; ret.msg = msg; ret.errorCode = 500; retlist.Add(ret); } int intType; intStr = a[i][1].ToString(); if (!int.TryParse(intStr, out intType)) { ReturnObject ret2 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！"; ret2.isOK = false; ret2.msg = msg; ret2.errorCode = 500; retlist.Add(ret2); } oil_zuoye = a[i][2].ToString(); if (string.IsNullOrWhiteSpace(oil_zuoye)) { ReturnObject ret3 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！"; ret3.isOK = false; ret3.msg = msg; ret3.errorCode = 500; retlist.Add(ret3); } oil_jieshou = a[i][3].ToString(); if (string.IsNullOrWhiteSpace(oil_jieshou)) { ReturnObject ret4 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！"; ret4.isOK = false; ret4.msg = msg; ret4.errorCode = 500; retlist.Add(ret4); } if (oil_zuoye == oil_jieshou) { ReturnObject ret21 = new ReturnObject(); msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！"; ret21.isOK = false; ret21.msg = msg; ret21.errorCode = 500; retlist.Add(ret21); } PhaseName = a[i][4].ToString(); if (string.IsNullOrWhiteSpace(PhaseName)) { ReturnObject ret5 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！"; ret5.isOK = false; ret5.msg = msg; ret5.errorCode = 500; retlist.Add(ret5); } StepContent = a[i][5].ToString(); if (string.IsNullOrWhiteSpace(StepContent)) { ReturnObject ret6 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！"; ret6.isOK = false; ret6.msg = msg; ret6.errorCode = 500; retlist.Add(ret6); } str = ""; var RelatedJobs = a[i][6].ToString(); if (!string.IsNullOrWhiteSpace(RelatedJobs)) { string[] id = RelatedJobs.Split(','); for (int j = 0; j < id.Length; j++) { var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'"); if (tab.Rows.Count > 0) { var jobname = tab.Rows[0]["Job_Id"].ToString(); str += "" + jobname + ","; } else { ReturnObject ret7 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】"; ret7.isOK = false; ret7.msg = msg; ret7.errorCode = 500; retlist.Add(ret7); } } str = str.TrimEnd(','); } RelatedValve = a[i][7].ToString(); RelatedYb = a[i][8].ToString(); cname = ""; var Company = a[i][9].ToString(); if (!string.IsNullOrWhiteSpace(Company)) { var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'"); if (tab.Rows.Count > 0) { cname = tab.Rows[0]["Company_Id"].ToString(); } else { ReturnObject ret8 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！"; ret8.isOK = false; ret8.msg = msg; ret8.errorCode = 500; retlist.Add(ret8); } } int intType9; intStr9 = a[i][10].ToString(); if (!int.TryParse(intStr9, out intType9)) { ReturnObject ret9 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！"; ret9.isOK = false; ret9.msg = msg; ret9.errorCode = 500; retlist.Add(ret9); } int intType13; Manual = a[i][11].ToString(); if (!int.TryParse(Manual, out intType13)) { ReturnObject ret10 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！"; ret10.isOK = false; ret10.msg = msg; ret10.errorCode = 500; retlist.Add(ret10); } int intType14; StepState = a[i][12].ToString(); if (!int.TryParse(StepState, out intType14)) { ReturnObject ret11 = new ReturnObject(); msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！"; ret11.isOK = false; ret11.msg = msg; ret11.errorCode = 500; retlist.Add(ret11); } //int intType15; Conten = a[i][13].ToString(); //if (!int.TryParse(Conten, out intType15)) //{ // ReturnObject ret12 = new ReturnObject(); // msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！"; // ret12.isOK = false; // ret12.msg = msg; // ret12.errorCode = 500; // retlist.Add(ret12); //} #endregion if (retlist.Count > 0) { return retlist; } else { list.Add(new InPrize() { ID = Guid.NewGuid().ToString(), Type = Convert.ToInt32(intStr1), Num = Convert.ToInt32(intStr), JobOil = oil_zuoye, RecepTionOil = oil_jieshou, PhaseName = PhaseName, StepContent = StepContent, RelatedJobs = str, RelatedValve = RelatedValve, RelatedYb = RelatedYb, Company = cname, Confirmation = Convert.ToInt32(intStr9), Manual = Convert.ToInt32(Manual), StepState = Convert.ToInt32(StepState), }); } } bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1); if (isRepeat == true) { ReturnObject ret22 = new ReturnObject(); ret22.isOK = false; ret22.msg = "步骤序号，有重复项请重新填写！"; ret22.errorCode = 500; retlist.Add(ret22); return retlist; } var CompanyName = a[2][9].ToString(); var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'"); cname = ta.Rows[0]["Company_Id"].ToString(); int tab22 = DbHelperSQL.GetDataTable("SELECT * from AirPortMain where Company='" + cname + "'").Rows.Count; if (tab22 > 0) { var n4 = DbHelperSQL.ExecuteSql("DELETE from AirPortMain"); } foreach (var n1 in list) { var sql = "INSERT INTO AirPortMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")"; var n2 = DbHelperSQL.ExecuteSql(sql); } return retlist; #endregion } } } public class ReturnObject { public bool isOK { get; set; } public int errorCode { get; set; } public string msg { get; set; } } public class InPrize { public string ID { get; set; } public int Type { get; set; } public int Num { get; set; } public string JobOil { get; set; } public string RecepTionOil { get; set; } public string PhaseName { get; set; } public string StepContent { get; set; } public string RelatedJobs { get; set; } public string RelatedValve { get; set; } public string RelatedYb { get; set; } public string Company { get; set; } public int Confirmation { get; set; } public string ConfirPerson { get; set; } public string Twice { get; set; } public string Reviewer { get; set; } public int Manual { get; set; } public int StepState { get; set; } public string Batch { get; set; } public string Images { get; set; } public int Conten { get; set; } } public class HairPortMain { public string ID { get; set; } public int Type { get; set; } public int Num { get; set; } public string JobOil { get; set; } public string RecepTionOil { get; set; } public string PhaseName { get; set; } public string StepContent { get; set; } public string RelatedJobs { get; set; } public string RelatedValve { get; set; } public string RelatedYb { get; set; } public string Company { get; set; } public int Confirmation { get; set; } public string ConfirPerson { get; set; } public string Twice { get; set; } public string Reviewer { get; set; } public int Manual { get; set; } public int StepState { get; set; } public string Batch { get; set; } public string Images { get; set; } public int Conten { get; set; } } #endregion
1.储油罐倒储油罐主表
|
HairPortMain |
|||
|
参数名称 |
参数描述 |
数据类型 |
备注 |
|
ID |
ID |
|
|
|
Type |
类型 |
Int |
1储油罐倒储油罐作业 |
|
Num |
步骤序号 |
int |
|
|
JobOil |
作业油罐 |
Nvarchar(50) |
油罐编号 |
|
RecepTionOil |
接收油罐 |
Nvarchar(50) |
油罐编号 |
|
PhaseName |
阶段名称 |
Nvarchar(50) |
|
|
StepContent |
步骤内容 |
Nvarchar(max) |
|
|
RelatedJobs |
关联岗位 |
Nvarchar(50) |
岗位信息表id |
|
RelatedValve |
关联阀门 |
Nvarchar(100) |
阀门编号 多个就用（,）拼接 |
|
RelatedYb |
关联油泵 |
Nvarchar(100) |
油泵编号 |
|
Company |
所属单位 |
Nvarchar(50) |
|
|
Confirmation |
是否双岗确认 |
int |
0否 1是 |
|
ConfirPerson |
确认人 |
Nvarchar(50) |
员工表id |
|
Twice |
二次确认人 |
Nvarchar(50) |
|
|
Reviewer |
复核人 |
Nvarchar(50) |
|
|
Manual |
手动/自动 |
Int |
0手动 1自动 2没有 |
|
StepState |
步骤状态 |
int |
0未开始 1进行中 2已结束 |
|
Batch |
流程批次 |
Nvarchar(50) |
|
|
JXTime |
流程进行时间 |
datetime |
|
|
Images |
图片 |
nvarchar(max) |
|
|
Conten |
选项内容 跳步 |
int |
1选项一 2选项二 3选项三 |
