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