private MSG checkValidityOfAllNameOfLine_Yields_Report(out List <NameAndRealTeam> nameAndTeamList)
{
MSG msg = new MSG();
List <NameAndRealTeam> theNameAndTeamList = new List <NameAndRealTeam>();
//姓名从(2,6)开始
for (int currColIndex = 6; currColIndex <= validMaxColIndex; currColIndex++)
{
string nameStr = uEHelper.getCellContentByRowAndColIndex(2, currColIndex);
string realTeamName, empName;
bool flag = StringHelper.checkNameOfLine_Yields_Report(nameStr, out realTeamName, out empName);
if (!flag)
{
msg.Msg = string.Format(@"第2行，{0}列，姓名格式不一致!", currColIndex);
nameAndTeamList = null;
return(msg);
}
NameAndRealTeam nameAndOtherTeam = new NameAndRealTeam();
nameAndOtherTeam.Real_team_name = realTeamName;
nameAndOtherTeam.Emp_name = empName;
theNameAndTeamList.Add(nameAndOtherTeam);
}
nameAndTeamList = theNameAndTeamList;
msg.Flag = true;
return(msg);
}
private void saveCriticalARInfo(List <string> xlsFilePathList)
{
//先清除所有记录。
AR_Temp.deleteTheARTemp();
for (int i = 0; i <= xlsFilePathList.Count - 1; i++)
{
string excelPath = xlsFilePathList[i];
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.openWithoutAlerts();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
//删除 时间后立即为空的行。
AttendanceRHelper.clearSheet(firstWS);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
string excelName = Usual_Excel_Helper.getExcelName(excelPath);
//先获取第4行的最大行列数目。
int rowMaxIndex = firstWS.UsedRange.Rows.Count;
int pbMaximum = rowMaxIndex - 4;
int pbValue = 0;
//0: 表示 lblResult.Visible
checkNameBGWorker.ReportProgress(0, "lblResult.Visible");
checkNameBGWorker.ReportProgress(pbMaximum, "pb.Maximum");
checkNameBGWorker.ReportProgress(pbValue, "pb.Value");
//lblPrompt.Text = excelName + ": 基本信息采集中...";
checkNameBGWorker.ReportProgress(0, string.Format(@"lblPrompt.Text={0}: 姓名采集中...", excelName));
for (int rowIndex = 5; rowIndex <= rowMaxIndex; rowIndex++)
{
//偶数行为 时间。
if (0 == rowIndex % 2)
{
checkNameBGWorker.ReportProgress(pbValue++, "pb.Value");
continue;
}
//姓名 存于第11列。
string name = uEHelper.getCellContentByRowAndColIndex(rowIndex, 11);
AR_Temp ar_Temp = new AR_Temp();
ar_Temp.Attendance_machine_flag = int.Parse(excelName.Substring(excelName.Length - 1, 1));
ar_Temp.Row_Index = rowIndex;
ar_Temp.Job_number = uEHelper.getCellContentByRowAndColIndex(rowIndex, 3);
ar_Temp.Name = name;
/// <summary>
/// 保存 ATTENDANCE_MACHINE_FLAG,
/// row_index,
/// job_number,
/// name
/// 至AR_Temp中
ar_Temp.saveRecord();
checkNameBGWorker.ReportProgress(pbValue++, "pb.Value");
}
myExcel.close();
}
}
/// <summary>
/// 获取第四行中，为天数的最大列索引
/// </summary>
/// <param name="wS"></param>
/// <returns></returns>
public static int getMaxColIndexOfThe4thRowOfAR(Worksheet wS)
{
Stack <int> sDate = new Stack <int>();
sDate.Push(0);
int aDate = 0;
int maxColIndex = wS.UsedRange.Columns.Count;
for (int colIndex = 1; colIndex <= maxColIndex; colIndex++)
{
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
string tempStr = uEHelper.getCellContentByRowAndColIndex(4, colIndex);
if (string.IsNullOrEmpty(tempStr))
{
return(colIndex - 1);
}
aDate = int.Parse(tempStr);
//判断新增的日期是否大于上一个.
if (aDate <= sDate.Peek())
{
return(colIndex - 1);
}
sDate.Push(aDate);
//取其中的最小值。
}
return(maxColIndex);
}
private void saveCriticalARInfo(List <String> excelPathList)
{
//先清除所有记录。
AR_Temp.deleteTheARTemp();
foreach (string excelPath in excelPathList)
{
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.open();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
//删除 时间后立即为空的行。
AttendanceRHelper.clearSheet(firstWS);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
string fileNameWithoutSuffix = DirectoryHelper.getFileNameWithoutSuffix(excelPath);
//先获取第4行的最大行列数目。
int rowMaxIndex = firstWS.UsedRange.Rows.Count;
pb.Value = 0;
pb.Maximum = rowMaxIndex - 4;
pb.Visible = true;
lblPrompt.Visible = true;
lblResult.Visible = false;
lblPrompt.Text = fileNameWithoutSuffix + ": 基本信息采集中...";
for (int rowIndex = 5; rowIndex <= rowMaxIndex; rowIndex++)
{
if (0 == rowIndex % 2)
{
pb.Value++;
continue;
}
//姓名 存于第11列。
string name = uEHelper.getCellContentByRowAndColIndex(rowIndex, 11);
AR_Temp ar_Temp = new AR_Temp();
ar_Temp.Attendance_machine_flag = int.Parse(fileNameWithoutSuffix.Substring(fileNameWithoutSuffix.Length - 1, 1));
ar_Temp.Row_Index = rowIndex;
ar_Temp.Job_number = uEHelper.getCellContentByRowAndColIndex(rowIndex, 3);
ar_Temp.Name = name;
ar_Temp.saveRecord();
pb.Value++;
}
lblPrompt.Visible = false;
pb.Visible = false;
myExcel.save();
myExcel.close();
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
private MSG checkExistSameStaffsButDifferentAttendanceMachine(List <String> excelPathList)
{
MSG msg = new MSG();
List <SimpleARInfo> simpleARInfoList = new List <SimpleARInfo>();
foreach (string excelPath in excelPathList)
{
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.open();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
//删除 时间为空 的行。
AttendanceRHelper.clearSheet(firstWS);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
SimpleARInfo simpleARInfo = null;
string fileNameWithout = DirectoryHelper.getFileNameWithoutSuffix(excelPath);
int rowMaxIndex = firstWS.UsedRange.Rows.Count;
for (int rowIndex = 5; rowIndex <= rowMaxIndex; rowIndex++)
{
if (0 == rowIndex % 2)
{
continue;
}
//姓名 存于第11列。
string name = uEHelper.getCellContentByRowAndColIndex(rowIndex, 11);
simpleARInfo = new SimpleARInfo();
simpleARInfo.AttendanceMachineFlag = int.Parse(fileNameWithout.Substring(fileNameWithout.Length - 1, 1));
simpleARInfo.Name = name;
simpleARInfo.RowIndex = rowIndex;
SimpleARInfo sARInfo = simpleARInfoList.Find(x => x.Name == simpleARInfo.Name);
if (sARInfo != null)
{
//存在重复的员工.
msg.Msg = string.Format(@"{0} 与 {1} 同名;同一人请汇总为一行,不同人,请姓名相异。", sARInfo.toString(), simpleARInfo.toString());
myExcel.close();
return(msg);
}
simpleARInfoList.Add(simpleARInfo);
}
myExcel.save();
myExcel.close();
}
msg.Flag = true;
msg.Msg = "未发现同名用户";
return(msg);
}
/// <summary>
/// 保存一份文件到Prepared中。
/// </summary>
/// <param name="xlsFilePath"></param>
private void saveTheExcel(string xlsFilePath)
{
//获取该记录表所对应的月份。
MyExcel srcExceFile = new MyExcel(xlsFilePath);
srcExceFile.openWithoutAlerts();
Worksheet firstSheet_Src = srcExceFile.getFirstWorkSheetAfterOpen();
string year_and_month_src_str = new Usual_Excel_Helper(firstSheet_Src).getCellContentByRowAndColIndex(3, 3).Substring(0, 7);
string fileNameWithoutSuffix = DirectoryHelper.getFileNameWithoutSuffix(xlsFilePath);
string attendanceMachineFlag = fileNameWithoutSuffix.Substring(fileNameWithoutSuffix.Length - 1);
year_and_month_src_str += string.Format(@"_{0}", attendanceMachineFlag);
srcExceFile.close();
//year_and_month_src_str 即为文件名
//string fileName = DirectoryHelper.getFileName(xlsFilePath);
//1.复制该excel 到 Prepared中。
Tools.CmdHelper.copyFileToDestDir(xlsFilePath, System.Windows.Forms.Application.StartupPath + "\\prepared\\" + year_and_month_src_str + ".xls");
string destFilePath = System.Windows.Forms.Application.StartupPath + "\\prepared\\" + year_and_month_src_str + ".xls";
//打开该文件
MyExcel myExcel = new MyExcel(destFilePath);
myExcel.openWithoutAlerts();
//新增一个sheet.
Worksheet firstSheet = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uHelper = new Usual_Excel_Helper(firstSheet);
//获取月份.
string C3ContentStr = uHelper.getCellContentByRowAndColIndex(3, 3);
string year_and_month_str = C3ContentStr.Substring(0, 7);
Worksheet theLastestExcel = myExcel.AddSheetToLastIndex(year_and_month_str);
myExcel.copyRangeFromOneToAnotherSheet(firstSheet, theLastestExcel);
int sheetsCount = myExcel.getCountsOfAllSheet();
//删掉之前的表格,保留最后一个。
for (int i = 1; i <= sheetsCount - 1; i++)
{
myExcel.delTheSheet(1);
}
//一定要保存否则，无效。
myExcel.saveAndColumnsAutoFit();
//关闭该文件
myExcel.close();
}
/// <summary>
/// 判断某行是否都为数字。
/// </summary>
/// <param name="wS"></param>
/// <param name="rowIndex"></param>
/// <returns></returns>
public static bool isAllDigit(Worksheet wS, int rowIndex, out int checkedColIndex)
{
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
int maxColIndex = wS.UsedRange.Columns.Count;
bool flag = false;
int num = 0;
for (int colIndex = 1; colIndex <= maxColIndex; colIndex++)
{
string tempStr = uEHelper.getCellContentByRowAndColIndex(4, colIndex);
flag = int.TryParse(tempStr, out num);
if (!flag)
{
checkedColIndex = colIndex;
return(false);
}
}
checkedColIndex = maxColIndex;
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImportEmpsARInfo_Click(object sender, EventArgs e)
{
//判断是否存在Excel进程.
if (CmdHelper.ifExistsTheProcessByName("EXCEL.EXE"))
{
ShowResult.show(lblResult, "存在未关闭的Office Excel进程,请先关闭!", false);
return;
}
string _uncertainWSPath = _defaultDir + "\\uncertainRecord_" + TimeHelper.getCurrentTimeStr() + ".xls";
this.dgv.Visible = false;
this.dgv_same_pinyin_of_name.Visible = true;
this.dgv_same_name.Visible = false;
dgv_same_name.DataSource = null;
dgv_same_pinyin_of_name.DataSource = null;
dgv.DataSource = null;
lblResult.Text = "";
lblResult.BackColor = this.BackColor;
tb.Clear();
randomStr = TimeHelper.getCurrentTimeStr();
xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择考勤记录：", "*.xls|*.xls", defaultDir);
string dir = DirectoryHelper.getDirOfFile(xlsFilePath);
if (string.IsNullOrEmpty(dir))
{
return;
}
List <string> xlsFileList = DirectoryHelper.getXlsFileUnderThePrescribedDir(dir);
List <string> resultList = new List <string>();
foreach (string xlsFile in xlsFileList)
{
string fileName = DirectoryHelper.getFileNameWithoutSuffix(xlsFile);
if (!CheckString.CheckARName(fileName))
{
continue;
}
//格式符合: 3月考勤记录1。
resultList.Add(xlsFile);
}
#region 先判断第四行，是否全为数字。
int maxColIndex = 0;
if (!check4thRow(resultList, out maxColIndex))
{
return;
}
#endregion
if (cbCheckSameNamesButDifferentMachineNo.Checked)
{
#region 保存关键信息到后台.
saveCriticalARInfo(resultList);
#endregion
#region 打开4个考勤文件
for (int i = 1; i <= resultList.Count; i++)
{
switch (i)
{
case 1:
_1th_my_excel = new MyExcel(resultList[0]);
_1th_my_excel.open();
_1th_Sheet = _1th_my_excel.getFirstWorkSheetAfterOpen();
break;
case 2:
_2nd_my_excel = new MyExcel(resultList[1]);
_2nd_my_excel.open();
_2nd_Sheet = _2nd_my_excel.getFirstWorkSheetAfterOpen();
break;
case 3:
_3rd_my_excel = new MyExcel(resultList[2]);
_3rd_my_excel.open();
_3rd_Sheet = _3rd_my_excel.getFirstWorkSheetAfterOpen();
break;
case 4:
_4th_my_excel = new MyExcel(resultList[3]);
_4th_my_excel.open();
_4th_Sheet = _4th_my_excel.getFirstWorkSheetAfterOpen();
break;
}
}
#endregion
#region 创建 _uncertain_myExcel;
MyExcel uncertainRecordExcel = null;
uncertainRecordExcel = new MyExcel(_uncertainWSPath);
uncertainRecordExcel.create();
uncertainRecordExcel.open();
uncertainWS = uncertainRecordExcel.getFirstWorkSheetAfterOpen();
//先写，日期行。
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(uncertainWS);
uEHelper.writeToSpecificRow(1, 1, maxColIndex);
#endregion
System.Data.DataTable dt = getSamePinYinButName();
bool have_same_pinyin_flag = false;
if (dt != null && dt.Rows.Count > 0)
{
have_same_pinyin_flag = true;
}
//*************判断是否拼音相同 开始********************8
if (have_same_pinyin_flag)
{
this.dgv_same_pinyin_of_name.Visible = true;
this.dgv_same_pinyin_of_name.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv_same_pinyin_of_name);
ShowResult.show(lblResult, "存在姓名拼音相同的记录!", false);
this.lblPrompt.Visible = false;
timerRestoreTheLblResult.Enabled = true;
btnSwitch.Text = "查看拼音相同的记录";
btnSwitch_Click(sender, e);
#region 写记录到不确定文档中.
int theRowIndex = 0;
int Attendance_Machine_No = 0;
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString());
Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString());
switch (Attendance_Machine_No)
{
case 1:
//获取源区域
srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
//保存一下。
break;
case 2:
srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 3:
srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 4:
srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
}
}
//显示该文档
CmdHelper.runCmd(_uncertainWSPath);
#endregion
string promptStr = string.Format(@"存在姓名拼音相同的记录。{0}
继续(OK), 取消导入(Cancel)。", "\r\n");
if (DialogResult.Cancel.Equals(MessageBox.Show(promptStr, "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
uncertainWS.UsedRange.ColumnWidth = 3.75M;
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
return;
}
}
//closeThe4ARExcels();
//*************判断是否拼音相同 结束*****************88
//1.h
dt = getSameNameInfo();
string prompt = string.Empty;
if (dt.Rows.Count != 0)
{
btnSwitch.Text = "查看同名记录";
btnSwitch_Click(sender, e);
int theRowIndex = 0;
int Attendance_Machine_No = 0;
#region 记录书写结束.
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString());
Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString());
switch (Attendance_Machine_No)
{
case 1:
//获取源区域
srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
//保存一下。
break;
case 2:
srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 3:
srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 4:
srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
}
}
#endregion
prompt = string.Format(@"存在同名记录，将合并;{0}
继续(OK), 取消导入(Cancel)。", "\r\n");
if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
uEHelper.setAllColumnsWidth(3.75M);
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
return;
}
}
//关闭不确定文档。
uEHelper.setAllColumnsWidth(3.75M);
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
}
resultList.Sort();
//判断该考勤机中是否已经存在，某月的记录
foreach (string xlsFilePath in resultList)
{
string fileName = DirectoryHelper.getFileNameWithoutSuffix(xlsFilePath);
int attendanceMachineFlag = int.Parse(fileName.Substring(fileName.Length - 1, 1));
//打开文档获取考勤机，所记录的日期。
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.open();
Worksheet firstSheet = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstSheet);
string year_and_month_str = uEHelper.getCellContentByRowAndColIndex(3, 3);
year_and_month_str = year_and_month_str.Substring(0, 7);
myExcel.close();
System.Data.DataTable dtSubmitInfo = getSubmitInfoOfTheSpecificeMachineAndYearAndMonth(attendanceMachineFlag, year_and_month_str);
if (!"0".Equals(dtSubmitInfo.Rows[0]["nums_of_staffs"].ToString()))
{
string prompt = string.Format(@"您已经于: {1},{0}提交了{2} 考勤机{3},{0}共计{4}个用户的纪录{0} 覆盖(OK),退出(Cancel)?", "\r\n", dtSubmitInfo.Rows[0]["latest_record_time"].ToString(), year_and_month_str, attendanceMachineFlag, dtSubmitInfo.Rows[0]["nums_of_staffs"].ToString());
if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question)))
{
return;
}
delTheInfoOfTheSpecificeMachineAndYearAndMonth(attendanceMachineFlag, year_and_month_str);
}
}
this.dgv.DataSource = null;
//this.dgv.Columns.Clear();
lblPrompt.Visible = false;
lblPrompt.Text = "";
pb.Value = 0;
pb.Maximum = 0;
pb.Visible = false;
foreach (string xlsFilePath in resultList)
{
tb.Text = xlsFilePath;
lblResult.Visible = false;
MSG msg = AttendanceRHelper.ImportAttendanceRecordToDB(xlsFilePath, randomStr, lblPrompt, pb, lblResult);
//导入完成后进行保存，保存该文件至prepared目录中
pb.Visible = false;
lblPrompt.Visible = false;
ShowResult.show(lblResult, msg.Msg, msg.Flag);
timerRestoreTheLblResult.Enabled = true;
if (!msg.Flag)
{
return;
}
//saveTheExcel(xlsFilePath);
}
dgv_same_name.Visible = false;
dgv_same_pinyin_of_name.Visible = false;
//加载导入的数据。
this.dgv.DataSource = null;
this.dgv.DataSource = aR.getAR(randomStr);
this.dgv.Visible = true;
DGVHelper.AutoSizeForDGV(dgv);
tb.Clear();
}
/// <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();
}
private void doNextAfterCheckName()
{
#region 打开4个考勤文件
for (int i = 1; i <= xlsFilePathList.Count; i++)
{
switch (i)
{
case 1:
_1th_my_excel = new MyExcel(xlsFilePathList[0]);
_1th_my_excel.openWithoutAlerts();
_1th_Sheet = _1th_my_excel.getFirstWorkSheetAfterOpen();
break;
case 2:
_2nd_my_excel = new MyExcel(xlsFilePathList[1]);
_2nd_my_excel.openWithoutAlerts();
_2nd_Sheet = _2nd_my_excel.getFirstWorkSheetAfterOpen();
break;
case 3:
_3rd_my_excel = new MyExcel(xlsFilePathList[2]);
_3rd_my_excel.openWithoutAlerts();
_3rd_Sheet = _3rd_my_excel.getFirstWorkSheetAfterOpen();
break;
case 4:
_4th_my_excel = new MyExcel(xlsFilePathList[3]);
_4th_my_excel.openWithoutAlerts();
_4th_Sheet = _4th_my_excel.getFirstWorkSheetAfterOpen();
break;
}
}
#endregion
#region 创建 _uncertain_myExcel;
MyExcel uncertainRecordExcel = null;
uncertainRecordExcel = new MyExcel(_uncertainWSPath);
uncertainRecordExcel.create();
uncertainRecordExcel.openWithoutAlerts();
uncertainWS = uncertainRecordExcel.getFirstWorkSheetAfterOpen();
//先写，日期行。
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(uncertainWS);
uEHelper.writeToSpecificRow(1, 1, maxColIndexOfCheckedNameOfExcel);
#endregion
System.Data.DataTable dt = getSamePinYinButName();
int amountOfGroupOfSamePinYinButName = getAmountOfGroupOfSamePinYinButName();
bool have_same_pinyin_flag = false;
if (dt != null && dt.Rows.Count > 0)
{
have_same_pinyin_flag = true;
}
//*************判断是否拼音相同 开始********************8
if (have_same_pinyin_flag)
{
ShowResult.show(lblResult, "存在姓名拼音相同的记录!", false);
this.lblPrompt.Visible = false;
timerRestoreTheLblResult.Enabled = true;
#region 写记录到不确定文档中.
int theRowIndex = 0;
int Attendance_Machine_No = 0;
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString());
Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString());
switch (Attendance_Machine_No)
{
case 1:
//获取源区域
//替换源文件的工号为 工号位于第三列
_1th_Sheet.Cells[theRowIndex, 3] = "'111111111" + ((Range)(_1th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
//保存一下。
break;
case 2:
_2nd_Sheet.Cells[theRowIndex, 3] = "'222222222" + ((Range)(_2nd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 3:
_3rd_Sheet.Cells[theRowIndex, 3] = "'333333333" + ((Range)(_3rd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 4:
_4th_Sheet.Cells[theRowIndex, 3] = "'444444444" + ((Range)(_4th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
}
}
//设置列宽
uncertainWS.UsedRange.ColumnWidth = 3.75;
//显示该文档对应的图片
#endregion
string promptStr = string.Format(@" 存在姓名拼音相同但书写不同的记录：{1}组;{0}
确定: 将视为不同员工; 取消: 取消本次导入;", "\r\n", amountOfGroupOfSamePinYinButName);
if (DialogResult.Cancel.Equals(MessageBox.Show(promptStr, "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
closeThe4ARExcels();
uncertainWS.UsedRange.ColumnWidth = 3.75M;
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
//显示该文档。
uncertainRecordExcel = new MyExcel(_uncertainWSPath);
uncertainRecordExcel.open(true);
btnViewTheUncertaiRecordInExcel.Enabled = true;
return;
}
if (!btnViewTheUncertaiRecordInExcel.Enabled)
{
btnViewTheUncertaiRecordInExcel.Enabled = true;
}
}
//*************判断是否拼音相同 结束*****************88
//1.h
dt = getSameNameInfo();
//获取汉字相同的组的数目。
int amountOfGroupOfSameName = getAmountOfGroupOfSameName();
string prompt = string.Empty;
if (dt.Rows.Count != 0)
{
int theRowIndex = 0;
int Attendance_Machine_No = 0;
#region 记录书写结束.
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString());
Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString());
switch (Attendance_Machine_No)
{
case 1:
_1th_Sheet.Cells[theRowIndex, 3] = "'111111111" + ((Range)(_1th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
//获取源区域
srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
//保存一下。
break;
case 2:
_2nd_Sheet.Cells[theRowIndex, 3] = "'222222222" + ((Range)(_2nd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 3:
_3rd_Sheet.Cells[theRowIndex, 3] = "'333333333" + ((Range)(_3rd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 4:
_4th_Sheet.Cells[theRowIndex, 3] = "'444444444" + ((Range)(_4th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
}
}
#endregion
prompt = string.Format(@" 存在同名的记录：{1}组;{0}
确定: 将视为同一员工; 取消: 取消本次导入;", "\r\n", amountOfGroupOfSameName);
if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
uEHelper.setAllColumnsWidth(3.75M);
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
//显示该文档。
uncertainRecordExcel = new MyExcel(_uncertainWSPath);
uncertainRecordExcel.open(true);
closeThe4ARExcels();
if (!btnViewTheUncertaiRecordInExcel.Enabled)
{
btnViewTheUncertaiRecordInExcel.Enabled = true;
}
return;
}
if (!btnViewTheUncertaiRecordInExcel.Enabled)
{
btnViewTheUncertaiRecordInExcel.Enabled = true;
}
}
//关闭不确定文档。
uEHelper.setAllColumnsWidth(3.75M);
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
xlsFilePathList.Sort();
//直接删除
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.openWithoutAlerts();
Worksheet firstSheet = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelperTemp = new Usual_Excel_Helper(firstSheet);
string year_and_month_str = uEHelperTemp.getCellContentByRowAndColIndex(3, 3);
year_and_month_str = year_and_month_str.Substring(0, 7);
myExcel.close();
delARDetailInfoByYearAndMonth(year_and_month_str);
//删除完毕。
this.dgv.DataSource = null;
//this.dgv.Columns.Clear();
lblPrompt.Visible = false;
lblPrompt.Text = "";
pb.Value = 0;
pb.Maximum = 0;
pb.Visible = false;
this.btnImportEmpsInfo.Enabled = false;
readDataFromExcelBGWorker.RunWorkerAsync();
}
