/// <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();
}
