/// <summary> /// 后台处理 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void readDataToDB_bgWorker_DoWork_Handler(object sender, DoWorkEventArgs e) { this.myExcel = new MyExcel(xlsFilePath); myExcel.open(false); this.percentProgress = 0; //开始读取Excel中的内容。 //1. 产量记录从第三行,第六列开始。 //2. 产量记录到第几列结束了? this._firstSheet = myExcel.getFirstWorkSheetAfterOpen(); this.uEHelper = new Usual_Excel_Helper(_firstSheet); //获取最大列 this.validMaxColIndex = uEHelper.getColIndexOfSpecificContentInSpecificRow(2, 1, uEHelper.getMaxColIndex(), "终止标识") - 1; //获取最大行从第三行起为序号行,当第1列,第一次出现为空的行时,即最大行。 this.validMaxRowIndex = uEHelper.getMaxRowIndexBeforeBlankCell(1); //********定义局部变量****** string pn, report_year_month_str, lineName, reportTeamName; //判断是否已经存在该线体的月报表 string contentOfA1 = uEHelper.getCellContentByRowAndColIndex(1, 1); //1.判断产品名_组_月份格式是否正确。 bool flag = StringHelper.checkPN_Team_Month(contentOfA1, out pn, out report_year_month_str, out lineName, out reportTeamName); if (!flag) { msg.Flag = false; msg.Msg = "报表中A1单元格内容格式应为:产品名_衬衣1组_3月"; this.readDataToDB_bgWorker.ReportProgress(0, msg); myExcel.close(); return; } //2.判断线体,组名是否存在 msg = isValid_TheLineName_Or_TheTeamName(reportTeamName, lineName); if (!msg.Flag) { this.readDataToDB_bgWorker.ReportProgress(0, msg); myExcel.close(); return; } //2.判断该组,在某月,某线体,所作的某产品 在数据库中是否已经有记录? System.Data.DataTable dt = Line_Each_One_Quantities.getAllQuantitiesOfTheLine_team_pn_report(lineName, reportTeamName, pn, report_year_month_str); if (dt.Rows.Count > 0) { msg.Flag = false; msg.Msg = string.Format(@"{0}: 线体(地点):{1},组名:{2},月份: {3} 已经存在!", pn, lineName, reportTeamName, report_year_month_str); this.readDataToDB_bgWorker.ReportProgress(0, msg); myExcel.close(); return; } //3.先检查工序列有没有工序为空的 msg = checkValidityOfAllProcesses(); if (!msg.Flag) { this.readDataToDB_bgWorker.ReportProgress(0, msg); myExcel.close(); return; } //4.检查金额列有无空或者为0.00的数值 msg = checkValidityOfAmountOfMoneyOfAllProcesses(); if (!msg.Flag) { this.readDataToDB_bgWorker.ReportProgress(0, msg); myExcel.close(); return; } List <NameAndRealTeam> nameAndRealTeamList = null; //5. 检查所有的姓名是否符合格式 msg = checkValidityOfAllNameOfLine_Yields_Report(out nameAndRealTeamList); if (!msg.Flag) { this.readDataToDB_bgWorker.ReportProgress(0, msg); myExcel.close(); return; } List <Line_Each_One_Quantities> line_each_one_quantities_list = new List <Line_Each_One_Quantities>(); int maxmium = (validMaxRowIndex - 3 + 1) * (validMaxColIndex - 6 + 1); int count = 0; msg.Flag = true; msg.Msg = "准备读取数据..."; readDataToDB_bgWorker.ReportProgress(0, msg); //自第(3,6)开始 for (int currColIndex = 6; currColIndex <= validMaxColIndex; currColIndex++) { for (int currRowIndex = 3; currRowIndex <= validMaxRowIndex; currRowIndex++) { count++; string contentStr = uEHelper.getCellContentByRowAndColIndex(currRowIndex, currColIndex); //为空,定位到下一个单元格。 if (string.IsNullOrEmpty(contentStr)) { readDataToDB_bgWorker.ReportProgress((count * 100 / maxmium)); continue; } //判断是否为数字 int quantities = 0; flag = int.TryParse(contentStr, out quantities); //若不为数字 if (!flag) { msg.Flag = false; msg.Msg = string.Format(@"第{0}行,第{1}列,应为整数!", currRowIndex, currColIndex); readDataToDB_bgWorker.ReportProgress((count / maxmium) * 100, msg); myExcel.close(); return; } //判断是否为整数 Line_Each_One_Quantities each_One_Quantities = new Line_Each_One_Quantities(); each_One_Quantities.Line_Name = lineName; each_One_Quantities.Report_team_name = reportTeamName; each_One_Quantities.Products_name = pn; each_One_Quantities.Report_year_month_str = report_year_month_str; each_One_Quantities.Quantities = quantities; //第二列为部位 each_One_Quantities.Summary_process = uEHelper.getCellContentByRowAndColIndex(currRowIndex, 2); //第三列为工序 each_One_Quantities.Specific_process = uEHelper.getCellContentByRowAndColIndex(currRowIndex, 3); each_One_Quantities.Man_hour = int.Parse(uEHelper.getCellContentByRowAndColIndex(currRowIndex, 4)); each_One_Quantities.Amount_of_money = decimal.Parse(uEHelper.getCellContentByRowAndColIndex(currRowIndex, 5)); //记录姓名,姓名,自第6列开始。 string realTeamName = nameAndRealTeamList[currColIndex - 6].Real_team_name; if (!string.IsNullOrEmpty(realTeamName)) { each_One_Quantities.Real_team_name = realTeamName; } else { each_One_Quantities.Real_team_name = reportTeamName; } each_One_Quantities.Emp_name = nameAndRealTeamList[currColIndex - 6].Emp_name; line_each_one_quantities_list.Add(each_One_Quantities); readDataToDB_bgWorker.ReportProgress((count * 100 / maxmium)); } } msg.Flag = true; msg.Msg = "提交数据中..."; readDataToDB_bgWorker.ReportProgress(0, msg); count = 0; //开始提交数据. maxmium = line_each_one_quantities_list.Count; for (int i = 0; i < maxmium; i++) { count++; readDataToDB_bgWorker.ReportProgress((count * 100 / maxmium)); line_each_one_quantities_list[i].save(); } msg.Msg = "提交完成。"; readDataToDB_bgWorker.ReportProgress((count * 100 / maxmium), msg); myExcel.close(); }