/// <summary> /// 获取考勤记录。 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnGetARResult_Click(object sender, EventArgs e) { V_Work_Schedule v_W_S = new V_Work_Schedule(); v_W_S.WORK_AND_REST_DATE = YearAndMonthStr; /* * if (!v_W_S.ifExistsWS()) { * return; * } */ ARResult aRResult = new ARResult(YearAndMonthStr); string randomStr = RandomStr.GetRandomString(40, true, true, true, false, ""); if (aRResult.updateARResult(randomStr) == 0) { this.dgv.DataSource = MESSAGES.getMSG(randomStr); DGVHelper.AutoSizeForDGV(dgv); ShowResult.show(lblResult, "异常!", false); timerRestoreTheLblResult.Enabled = true; return; } ShowResult.show(lblResult, "完成!", true); //显示结果 AttendanceR aR = new AttendanceR(); this.dgv.DataSource = aR.getARByYearAndMonth(aRResult.Year_And_Month_str); DGVHelper.AutoSizeForDGV(dgv); }
/// <summary> /// 生成工作安排表。 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void mCalendar_DateChanged(object sender, DateRangeEventArgs e) { startDateStr = e.Start.ToString("yyyy-MM-dd"); endDateStr = e.End.ToString("yyyy-MM-dd"); AttendanceR aR = new AttendanceR(); //获取相应的工作安排记录 System.Data.DataTable dt = aR.getARByRange(startDateStr, endDateStr); this.dgv.DataSource = dt; DGVHelper.AutoSizeForDGV(dgv); }
private void toAWholePiece() { killHwndOfXls(); Queue <int> prefix_Of_Staffs_Queue = V_AR_RESULT.get_Prefix_Staffs(YearAndMonthStr); if (prefix_Of_Staffs_Queue.Count == 0) { ShowResult.show(lblResult, "尚未导入本月的考勤记录!", false); timerRestoreTheLblResult.Enabled = true; return; } MSG msg = new MSG(); ApplicationClass app = new ApplicationClass(); //追加Hwnd到队列中. int appHwnd = app.Hwnd; app.Visible = false; Workbook wBook = app.Workbooks.Add(true); Worksheet wSheet = (Worksheet)wBook.Worksheets[1]; string _defaultDir = System.Windows.Forms.Application.StartupPath + "\\考勤汇总"; int seq = prefix_Of_Staffs_Queue.Dequeue(); int lastSeq = 0; while (prefix_Of_Staffs_Queue.Count >= 1) { lastSeq = prefix_Of_Staffs_Queue.Dequeue(); } string _fileName = YearAndMonthStr + "_考勤汇总" + seq.ToString() + "-" + lastSeq.ToString() + ".xls"; if (!xlsFilePath.Contains(":")) { //导出到Excel中 xlsFilePath = FileNameDialog.getSaveFileNameWithDefaultDir("考勤汇总:", "*.xls|*.xls", _defaultDir, _fileName); if (!xlsFilePath.Contains(@"\")) { return; } } //依据前缀和月份获取列表。 //获取该月该考勤机的出勤人数。 int AR_Num = V_AttendanceRecord.getARNumByYearAndMonth(YearAndMonthStr); if (AR_Num == 0) { MessageBox.Show("数据源为空,无法导出。", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } int rowMaxCount = AR_Num * 2 + 6; int colMaxCount = AttendanceR.get_AR_Days_Num(YearAndMonthStr); //写标题 try { //每行格式设置,注意标题占一行。 Range range = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[rowMaxCount + 1, colMaxCount + 1]); //设置单元格为文本。 range.NumberFormatLocal = "@"; //水平对齐方式 range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //第一行写考勤分析结果。 wSheet.Cells[1, 1] = YearAndMonthStr + " 考勤分析结果" + seq.ToString() + "-" + lastSeq.ToString(); //获取该日期详细的考勤记录。 #region V_AttendanceRecord.AR_Properties aR_Properties = V_AttendanceRecord.getARProperties(YearAndMonthStr); //第三行:考勤时间 wSheet.Cells[3, 1] = "考勤时间"; wSheet.Cells[3, 3] = String.Format(@"{0} ~ {1}", aR_Properties.Start_Date, aR_Properties.End_Date); wSheet.Cells[3, 10] = "制表时间"; wSheet.Cells[3, 12] = aR_Properties.Tabulation_date; #endregion List <int> dayList = V_AR_DETAIL.getDayListOfTheSpecificMonth(YearAndMonthStr); //需要统计有多少人。 int total_num_of_AttendanceR = AttendanceR.get_Total_Num_Of_Staffs_By_YAndM(YearAndMonthStr); pb.Value = 0; pb.Maximum = dayList.Count + dayList.Count * total_num_of_AttendanceR; pb.Visible = true; lblPrompt.Text = _fileName + ":"; lblPrompt.Visible = true; //写 此月与考勤相关的日。 for (int i = 0; i <= dayList.Count - 1; i++) { //写该月的具体有哪些日:1,2,3.与考勤相关。 wSheet.Cells[4, i + 1] = dayList[i].ToString(); pb.Value++; } //实际出勤天数. wSheet.Cells[4, dayList.Count + 1] = "实际出勤天数"; //事假 wSheet.Cells[4, dayList.Count + 2] = "事假"; //未打卡 wSheet.Cells[4, dayList.Count + 3] = "未打卡"; //延点 wSheet.Cells[4, dayList.Count + 4] = "延点(小时)"; //迟到 wSheet.Cells[4, dayList.Count + 5] = "迟到"; //早退 wSheet.Cells[4, dayList.Count + 6] = "早退"; //餐补 wSheet.Cells[4, dayList.Count + 7] = "餐补"; string AR_YEAR_AND_Month_Str = String.Empty; string AR_Day = string.Empty; List <V_AR_DETAIL> v_AR_Detail_Specific_Day_List = null; for (int j = 1; j <= dayList.Count; j++) { AR_YEAR_AND_Month_Str = aR_Properties.Start_Date.Substring(0, 8); AR_Day = AR_YEAR_AND_Month_Str + dayList[j - 1].ToString().PadLeft(2, '0'); v_AR_Detail_Specific_Day_List = V_AR_DETAIL.get_V_AR_Detail_By_Specific_Day(AR_Day); //按日取。 for (int i = 0; i <= v_AR_Detail_Specific_Day_List.Count - 1; i++) { V_AR_DETAIL v_AR_Detail = v_AR_Detail_Specific_Day_List[i]; if (j == 1) { //第五行写工号。 wSheet.Cells[5 + i * 2, 1] = "工号"; //获取原始的工号,没有前缀。 wSheet.Cells[5 + i * 2, 3] = "'" + v_AR_Detail.Job_number; //9 wSheet.Cells[5 + i * 2, 9] = "姓名"; //11 wSheet.Cells[5 + i * 2, 11] = v_AR_Detail.Name; //19 wSheet.Cells[5 + i * 2, 19] = "部门"; //21 wSheet.Cells[5 + i * 2, 21] = v_AR_Detail.Dept; V_Summary_OF_AR v_summary_of_ar = new V_Summary_OF_AR(v_AR_Detail.Job_number, YearAndMonthStr); System.Data.DataTable dtARSummary = v_summary_of_ar.getSummaryOFAR(); //实际出勤天数. wSheet.Cells[6 + i * 2, dayList.Count + 1] = dtARSummary.Rows[0]["AR_DAYS"].ToString(); //事假 string vacatioin_total_time = dtARSummary.Rows[0]["VACATION_TOTAL_TIME"].ToString(); wSheet.Cells[6 + i * 2, dayList.Count + 2] = "0".Equals(vacatioin_total_time) ? "" : vacatioin_total_time; string not_Finger_Print_num = dtARSummary.Rows[0]["NOT_FINGERPRINT_TIMES"].ToString(); //未打卡 wSheet.Cells[6 + i * 2, dayList.Count + 3] = "0".Equals(not_Finger_Print_num) ? "" : not_Finger_Print_num; string delayTime = dtARSummary.Rows[0]["DELAY_TOTAL_TIME"].ToString(); //延点 wSheet.Cells[6 + i * 2, dayList.Count + 4] = "0.0".Equals(delayTime) ? "" : delayTime; string come_late_Num = dtARSummary.Rows[0]["COME_LATE_NUM"].ToString(); //迟到 wSheet.Cells[6 + i * 2, dayList.Count + 5] = "0".Equals(come_late_Num) ? "" : come_late_Num; string leave_early_num = dtARSummary.Rows[0]["LEAVE_EARLY_NUM"].ToString(); //早退 wSheet.Cells[6 + i * 2, dayList.Count + 6] = "0".Equals(leave_early_num) ? "" : leave_early_num; //餐补 wSheet.Cells[6 + i * 2, dayList.Count + 7] = dtARSummary.Rows[0]["DINNER_SUBSIDY_NUM"].ToString(); } System.Data.DataTable dt = V_AR_Time_Helper.getARTime(v_AR_Detail.Job_number, AR_Day); string tempStr = String.Empty; int length = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1.ToString().Length; for (int k = 0; k <= dt.Rows.Count - 1; k++) { //先设置颜色. if ("0".Equals(dt.Rows[k]["FLAG"].ToString())) { if ("1".Equals(dt.Rows[k]["COME_LATE_NUM"].ToString())) //迟到 { //先计算单元格已有字符长度。 length = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1.ToString().Length; //迟到 tempStr = dt.Rows[k]["TIME"].ToString() + (k < dt.Rows.Count - 1 ? "\r\n" : ""); ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 + tempStr; ((Range)wSheet.Cells[6 + i * 2, j]).Characters[length + 1, 5].Font.Color = -16776961; continue; } if ("1".Equals(dt.Rows[k]["LEAVE_EARLY_NUM"].ToString())) { //先计算单元格已有字符长度。 length = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1.ToString().Length; //早退 tempStr = dt.Rows[k]["TIME"].ToString() + (k < dt.Rows.Count - 1 ? "\r\n" : ""); ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 + tempStr; //写完即改变前景色。 ((Range)wSheet.Cells[6 + i * 2, j]).Characters[length + 1, 5].Font.Color = -16776961; continue; } //先计算单元格已有字符长度。 length = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1.ToString().Length; //正常 //正常上班点. tempStr = dt.Rows[k]["TIME"].ToString() + (k < dt.Rows.Count - 1 ? "\r\n" : ""); ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 + tempStr; ((Range)wSheet.Cells[6 + i * 2, j]).Characters[length + 1, 5].Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); continue; } //先计算单元格已有字符长度。 length = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1.ToString().Length; //请假点。 tempStr = "<" + dt.Rows[k]["TIME"].ToString() + ">" + (k < (dt.Rows.Count - 1) ? "\r\n" : ""); ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 + tempStr; //((Range)wSheet.Cells[6 + i * 2, j]).Characters[length + 1, 5].Font.Bold = true; //((Range)wSheet.Cells[6 + i * 2, j]).Characters[length + 1, 5].Font.ThemeColor = XlThemeColor.xlThemeColorDark1; //((Range)wSheet.Cells[6 + i * 2, j]).Characters[length + 1, 5].Font.Color = -16776961; } pb.Value++; } } rowMaxCount = wSheet.UsedRange.Rows.Count; //休息日,背景色变为浅绿色。 for (int j = 1; j <= dayList.Count; j++) { bool ifRestDay = false; AR_YEAR_AND_Month_Str = aR_Properties.Start_Date.Substring(0, 8); AR_Day = AR_YEAR_AND_Month_Str + dayList[j - 1].ToString().PadLeft(2, '0'); ifRestDay = Have_A_Rest_Helper.ifDayOfRest(AR_Day); if (ifRestDay) { //此列背景色改为: /* * ange("AF102").Select * With Selection.Interior * .Pattern = xlSolid * .PatternColorIndex = xlAutomatic * .ThemeColor = xlThemeColorAccent3 * .TintAndShade = 0.599993896298105 * .PatternTintAndShade = 0 * End With * End Sub */ Range rangeRestDay = wSheet.get_Range(wSheet.Cells[4, j], wSheet.Cells[rowMaxCount, j]); rangeRestDay.Interior.Pattern = XlPattern.xlPatternSolid; rangeRestDay.Interior.PatternColorIndex = XlPattern.xlPatternAutomatic; rangeRestDay.Interior.ThemeColor = XlThemeColor.xlThemeColorAccent3; rangeRestDay.Interior.TintAndShade = 0.599993896298105; rangeRestDay.Interior.PatternTintAndShade = 0; } } //合并第一行 Range rangeTitle = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[1, dayList.Count + 7]); rangeTitle.Merge(); rangeTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter; rangeTitle.VerticalAlignment = XlVAlign.xlVAlignCenter; pb.Visible = false; lblPrompt.Visible = false; //自动调整列宽 //range.EntireColumn.AutoFit(); //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存excel文档并关闭 wBook.SaveAs(xlsFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wBook.Close(true, xlsFilePath, Type.Missing); //退出Excel程序 app.Quit(); //释放资源 System.Runtime.InteropServices.Marshal.ReleaseComObject(range); System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //调用GC的垃圾收集方法 GC.Collect(); GC.WaitForPendingFinalizers(); ShowResult.show(lblResult, "存于: " + xlsFilePath, true); timerRestoreTheLblResult.Enabled = true; //生成工作安排表。 } catch (Exception ex) { MessageBox.Show(ex.Message, "提示消息:", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } }
/// <summary> /// 将考勤记录导入数据库. /// </summary> /// <param name="xlsFilePath"></param> /// <param name="randomStr"></param> /// <param name="pb"></param> /// <returns></returns> public static MSG ImportARToDB(string xlsFilePath) { int pbLength = 0; MSG msg = new MSG(); ProgressInfo pI = new ProgressInfo("ConsoleCollectAR"); if (pI.ifExists()) { pI.delete(); } pI.add(); //用于确定本月最后一天. Stack <int> sDate = new Stack <int>(); Queue <AttendanceR> qAttendanceR = new Queue <AttendanceR>(); //按指纹日期 string fingerPrintDate = String.Empty; //导入数据的行数. int affectedCount = 0; //行最大值. int rowsMaxCount = 0; int colsMaxCount = 0; MyExcel myExcel = new MyExcel(xlsFilePath); //打开该文档。 myExcel.open(); #region 获取该工作簿中可视的工作表列表 List <Worksheet> listVisualWS = myExcel.getVisualWS(); List <AR_Sheet_Info> list_AR_Sheet_Info = new List <AR_Sheet_Info>(); Usual_Excel_Helper uEHelper = null; foreach (Worksheet wS in listVisualWS) { int sheet_index = wS.Index; int max_rows = wS.UsedRange.Rows.Count; uEHelper = new Usual_Excel_Helper(wS); //获取A1内容 string contentOfA3 = uEHelper.getSpecificCellValue("A3"); //考勤时间为 空,忽略 if (!"考勤时间".Equals(contentOfA3)) { continue; } int maxColumns = wS.UsedRange.Columns.Count; if (maxColumns <= 27 || maxColumns >= 32) { continue; } //列的宽度28-31. AR_Sheet_Info ar_sheet_info = new AR_Sheet_Info(sheet_index, max_rows); list_AR_Sheet_Info.Add(ar_sheet_info); } #endregion //获取list_AR_Sheet_Info 中 rows 最少的那个Sheet //先对list_AR_Sheet_Info进行排序。 list_AR_Sheet_Info.Sort((a, b) => a.Max_rows.CompareTo(b.Max_rows)); int sheetIndex = list_AR_Sheet_Info[0].SheetIndex; Worksheet ws = listVisualWS[sheetIndex - 1]; //行;列最大值 赋值. rowsMaxCount = ws.UsedRange.Rows.Count; colsMaxCount = ws.UsedRange.Columns.Count; AttendanceR.Sheet_name = ws.Name; //判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表. string A1Str = ((Range)ws.Cells[1, 1]).Text.ToString().Trim().Replace("\n", "").Replace("\r", "").Replace(" ", ""); if (String.IsNullOrEmpty(A1Str)) { msg.Msg = "工作表的A1单元格不能为空!"; msg.Flag = false; myExcel.close(); return(msg); } //如果A1Str的内容不包含"考勤记录表"5个字。 if (!A1Str.Contains("考勤记录表")) { msg.Msg = "A1内容未包含'考勤记录表'"; msg.Flag = false; myExcel.close(); return(msg); } #region 判断名称中是否区分了考勤记录。 string Seq_Attendance_Record = string.Empty; int indexOfFullStop = xlsFilePath.LastIndexOf("."); Seq_Attendance_Record = xlsFilePath.Substring(indexOfFullStop - 1, 1); if (!CheckPattern.CheckNumber(Seq_Attendance_Record)) { msg.Msg = "考勤记录表名称请以数字结尾!"; msg.Flag = false; myExcel.close(); return(msg); } #endregion string excelName = Usual_Excel_Helper.getExcelName(xlsFilePath); AttendanceR.Prefix_Job_Number = excelName.Substring(excelName.Length - 1, 1).ToCharArray()[0]; string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim(); // \0: 表空字符. if (String.IsNullOrEmpty(C3Str)) { msg.Msg = "异常: 考勤时间为空!"; msg.Flag = false; myExcel.close(); return(msg); } // string[] ArrayC3 = C3Str.Split('~'); if (ArrayC3.Length == 0) { msg.Msg = "异常: 考勤时间格式变更!"; msg.Flag = false; myExcel.close(); return(msg); } AttendanceR.Start_date = ArrayC3[0].ToString().Trim(); AttendanceR.End_date = ArrayC3[1].ToString().Trim(); //制表时间: L3 3行12列. string L3Str = ((Range)ws.Cells[3, 12]).Text.ToString().Trim(); if (String.IsNullOrEmpty(L3Str)) { msg.Msg = "异常: 制表时间为空!"; msg.Flag = false; myExcel.close(); return(msg); } //制表时间. AttendanceR.Tabulation_time = L3Str; //检查第4行是否为;考勤时间: string A4Str = ((Range)ws.Cells[4, 1]).Text.ToString().Trim(); if (!"1".Equals(A4Str, StringComparison.CurrentCultureIgnoreCase)) { msg.Msg = "异常: 第四行已变更!"; msg.Flag = false; myExcel.close(); return(msg); } uEHelper = new Usual_Excel_Helper(ws); //此刻不能删除,只是获取行号。 Queue <Range> rangeToDelQueue = new Queue <Range>(); //判断是否有空行。 for (int i = 5; i <= rowsMaxCount; i++) { if (uEHelper.isBlankRow(i)) { //只要上一列不是 //删除掉此行。 //判断上一行中的A列是否为工号。 string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString()); if ("工号:".Equals(temp)) { continue; } //获取该行。 Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing]; //不为工号 rangeToDelQueue.Enqueue(rangeToDel); } ; } Range rangeToDelete; //开始删除空行。 while (rangeToDelQueue.Count > 0) { rangeToDelete = rangeToDelQueue.Dequeue(); rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp); } ; rowsMaxCount = ws.UsedRange.Rows.Count; //进度条长度增加。 pbLength += colsMaxCount; pbLength += (colsMaxCount * (rowsMaxCount - 5 + 1)); pI.Max_value = pbLength; pI.Current_value = 0; pI.Msg = excelName + ",正在读取:"; pI.update(); Console.WriteLine(excelName + ",正在读取:"); //入队列值0 sDate.Push(0); int actualMaxDay = 0; //开始循环 for (int i = 1; i <= colsMaxCount; i++) { A4Str = ((Range)ws.Cells[4, i]).Text.ToString(); //碰到第4行某列为空,退出循环。 if (String.IsNullOrEmpty(A4Str)) { break; } int aDate = 0; //对A4Str进行分析. if (!Int32.TryParse(A4Str, out aDate)) { msg.Msg = String.Format(@"异常: 考勤日期行第{0}列出现非数字内容!", aDate); msg.Flag = false; myExcel.close(); return(msg); } pI.Current_value++; pI.update(); //判断新增的日期是否大于上一个. if (aDate <= sDate.Peek()) { //跳出循环. break; } actualMaxDay++; sDate.Push(aDate); } //取其中的最小值。 colsMaxCount = Math.Min(sDate.Count - 1, actualMaxDay); //考勤日期 fingerPrintDate = AttendanceR.Start_date.Substring(0, 7).Replace('/', '-'); string tempStr = string.Empty; //开始循环 for (int colIndex = 1; colIndex <= colsMaxCount; colIndex++) { //从第5行开始. //奇数;偶数行共用一个对象. AttendanceR AR = null; for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++) { //如果行数为奇数则为工号行. if (rowIndex % 2 == 1) { //工号行. //取工号 AR = new AttendanceR(); AR.Job_number = ((Range)ws.Cells[rowIndex, 3]).Text.ToString().Trim(); //自行拼凑AR. AR.combine_Job_Number(); //取姓名: K5 AR.Name = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("K")]).Text.ToString().Trim(); //取部门: U5 AR.Dept = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("U")]).Text.ToString().Trim(); //部门为空,则填充为NULL; AR.Dept = !String.IsNullOrEmpty(AR.Dept) ? AR.Dept : "NULL"; //取日期.填充0; AR.Fingerprint_Date = fingerPrintDate + "-" + colIndex.ToString().PadLeft(2, '0'); } else { //偶数行取考勤结果. //上班时间. 如B10; tempStr = ((Range)ws.Cells[rowIndex, colIndex]).Text.ToString().Trim(); string tempFirstTime = String.Empty; string tempLastTime = String.Empty; if (!getFPTime(tempStr, out tempFirstTime, out tempLastTime)) { msg.Msg = string.Format(@"导入失败:表中第{0}行{1}列的按指纹时间格式不对!", rowIndex, colIndex); msg.Flag = false; return(msg); } ; AR.FPT_Fisrt_Time = String.IsNullOrEmpty(tempFirstTime) ? String.Empty : AR.Fingerprint_Date + " " + tempFirstTime; AR.FPT_Last_Time = String.IsNullOrEmpty(tempLastTime) ? String.Empty : AR.Fingerprint_Date + " " + tempLastTime; qAttendanceR.Enqueue(AR); } pI.Current_value++; pI.update(); } } Console.WriteLine("提交数据: "); pI.Max_value = qAttendanceR.Count; pI.Current_value = 0; pI.Msg = "提交数据: "; pI.update(); System.Threading.Thread.Sleep(2000); #region OracleDaoHelper.noLogging("Attendance_Record"); //保存对象 while (qAttendanceR.Count > 0) { try { affectedCount += qAttendanceR.Dequeue().saveDataToDB(); pI.Current_value++; pI.update(); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Error); msg.Msg = ex.ToString(); msg.Flag = false; OracleConnHelper.closeConn(); return(msg); } } #endregion OracleDaoHelper.logging("Attendance_Record"); //释放对象 myExcel.close(); Console.WriteLine(String.Format(@"导入完成;计{0}条.", affectedCount.ToString())); //隐藏进度条。 pI.Msg = String.Format(@"导入完成;计{0}条.", affectedCount.ToString()); pI.Finish_flag = true; pI.update(); return(msg); }
/// <summary> /// 将考勤记录导入预备表中. /// </summary> /// <param name="xlsFilePath"></param> /// <param name="randomStr"></param> /// <param name="pb"></param> /// <returns></returns> public static MSG Import_Record_To_Preparative_Table(string xlsFilePath, string randomStr, System.Windows.Forms.Label lblPrompt, ProgressBar pb, System.Windows.Forms.Label lblResult) { int pbLength = 0; MSG msg = new MSG(); //用于确定本月最后一天. Stack <int> sDate = new Stack <int>(); Queue <AttendanceR> qAttendanceR = new Queue <AttendanceR>(); AttendanceR.Random_Str = randomStr; //按指纹日期 string fingerPrintDate = String.Empty; //导入数据的行数. int affectedCount = 0; //行最大值. int rowsMaxCount = 0; int colsMaxCount = 0; Usual_Excel_Helper uEHelper = null; MyExcel myExcel = new MyExcel(xlsFilePath); //打开该文档。 myExcel.open(); //只获取第一个表格。 Worksheet ws = myExcel.getFirstWorkSheetAfterOpen(); AttendanceR.File_path = xlsFilePath; //行;列最大值 赋值. rowsMaxCount = ws.UsedRange.Rows.Count; colsMaxCount = ws.UsedRange.Columns.Count; pb.Value = 0; pb.Visible = true; lblPrompt.Visible = true; AttendanceR.Sheet_name = ws.Name; //判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表. string A1Str = ((Range)ws.Cells[1, 1]).Text.ToString().Trim().Replace("\n", "").Replace("\r", "").Replace(" ", ""); if (String.IsNullOrEmpty(A1Str)) { msg.Msg = "工作表的A1单元格不能为空!"; msg.Flag = false; myExcel.close(); return(msg); } //如果A1Str的内容不包含"考勤记录表"5个字。 if (!A1Str.Contains("考勤记录表")) { msg.Msg = "A1内容未包含'考勤记录表'"; msg.Flag = false; myExcel.close(); return(msg); } #region 判断名称中是否区分了考勤记录。 string Seq_Attendance_Record = string.Empty; int indexOfFullStop = xlsFilePath.LastIndexOf("."); Seq_Attendance_Record = xlsFilePath.Substring(indexOfFullStop - 1, 1); if (!CheckPattern.CheckNumber(Seq_Attendance_Record)) { msg.Msg = "考勤记录表名称请以数字结尾!"; msg.Flag = false; myExcel.close(); return(msg); } #endregion string excelName = Usual_Excel_Helper.getExcelName(xlsFilePath); AttendanceR.Prefix_Job_Number = excelName.Substring(excelName.Length - 1, 1).ToCharArray()[0]; string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim(); // \0: 表空字符. if (String.IsNullOrEmpty(C3Str)) { msg.Msg = "异常: 考勤时间为空!"; msg.Flag = false; myExcel.close(); return(msg); } // string[] ArrayC3 = C3Str.Split('~'); if (ArrayC3.Length == 0) { msg.Msg = "异常: 考勤时间格式变更!"; msg.Flag = false; myExcel.close(); return(msg); } AttendanceR.Start_date = ArrayC3[0].ToString().Trim(); AttendanceR.End_date = ArrayC3[1].ToString().Trim(); //制表时间: L3 3行12列. string L3Str = ((Range)ws.Cells[3, 12]).Text.ToString().Trim(); if (String.IsNullOrEmpty(L3Str)) { msg.Msg = "异常: 制表时间为空!"; msg.Flag = false; myExcel.close(); return(msg); } //制表时间. AttendanceR.Tabulation_time = L3Str; //检查第4行是否为;考勤时间: string A4Str = ((Range)ws.Cells[4, 1]).Text.ToString().Trim(); if (!"1".Equals(A4Str, StringComparison.CurrentCultureIgnoreCase)) { msg.Msg = "异常: 第四行已变更!"; msg.Flag = false; myExcel.close(); return(msg); } uEHelper = new Usual_Excel_Helper(ws); //此刻不能删除,只是获取行号。 Queue <Range> rangeToDelQueue = new Queue <Range>(); //判断是否有空行。 for (int i = 5; i <= rowsMaxCount; i++) { if (uEHelper.isBlankRow(i)) { //只要上一列不是 //删除掉此行。 //判断上一行中的A列是否为工号。 string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString()); if ("工号:".Equals(temp)) { //本行为空,上一行为工号行,则也统计。 continue; } //本行,为空,上一行非工号行。则删除本行。 Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing]; //不为工号 rangeToDelQueue.Enqueue(rangeToDel); } ; } Range rangeToDelete; //开始删除空行。 while (rangeToDelQueue.Count > 0) { rangeToDelete = rangeToDelQueue.Dequeue(); rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp); } ; rowsMaxCount = ws.UsedRange.Rows.Count; //进度条长度增加。 pbLength += colsMaxCount; pbLength += (colsMaxCount * (rowsMaxCount - 5 + 1)); pb.Maximum = pbLength; //入队列值0 sDate.Push(0); //显示进度条。 //考勤表中第4行,某月的最大考勤天数。 lblPrompt.Text = excelName + ",正在读取:"; int actualMaxDay = 0; //开始循环 for (int i = 1; i <= colsMaxCount; i++) { A4Str = ((Range)ws.Cells[4, i]).Text.ToString(); //碰到第4行某列为空,退出循环。 if (String.IsNullOrEmpty(A4Str)) { break; } int aDate = 0; //对A4Str进行分析. if (!Int32.TryParse(A4Str, out aDate)) { msg.Msg = String.Format(@"异常: 考勤日期行第{0}列出现非数字内容!", aDate); msg.Flag = false; myExcel.close(); return(msg); } pb.Value++; //判断新增的日期是否大于上一个. if (aDate <= sDate.Peek()) { //跳出循环. break; } actualMaxDay++; sDate.Push(aDate); } //取其中的最小值。 colsMaxCount = Math.Min(sDate.Count - 1, actualMaxDay); //考勤日期 fingerPrintDate = AttendanceR.Start_date.Substring(0, 7).Replace('/', '-'); string tempStr = string.Empty; //开始循环 for (int colIndex = 1; colIndex <= colsMaxCount; colIndex++) { //从第5行开始. //奇数;偶数行共用一个对象. AttendanceR AR = null; for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++) { //如果行数为奇数则为工号行. if (rowIndex % 2 == 1) { //工号行. //取工号 AR = new AttendanceR(); AR.Job_number = ((Range)ws.Cells[rowIndex, 3]).Text.ToString().Trim(); //自行拼凑AR. AR.combine_Job_Number(); //取姓名: K5 AR.Name = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("K")]).Text.ToString().Trim(); //取部门: U5 AR.Dept = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("U")]).Text.ToString().Trim(); //部门为空,则填充为NULL; AR.Dept = !String.IsNullOrEmpty(AR.Dept) ? AR.Dept : "NULL"; //取日期.填充0; AR.Fingerprint_Date = fingerPrintDate + "-" + colIndex.ToString().PadLeft(2, '0'); } else { //偶数行取考勤结果. //上班时间. 如B10; tempStr = ((Range)ws.Cells[rowIndex, colIndex]).Text.ToString().Trim(); string tempFirstTime = String.Empty; string tempLastTime = String.Empty; if (!getFPTime(tempStr, out tempFirstTime, out tempLastTime)) { msg.Msg = string.Format(@"导入失败:表中第{0}行{1}列的按指纹时间格式不对!", rowIndex, colIndex); msg.Flag = false; myExcel.close(); return(msg); } ; AR.FPT_Fisrt_Time = String.IsNullOrEmpty(tempFirstTime) ? String.Empty : AR.Fingerprint_Date + " " + tempFirstTime; AR.FPT_Last_Time = String.IsNullOrEmpty(tempLastTime) ? String.Empty : AR.Fingerprint_Date + " " + tempLastTime; qAttendanceR.Enqueue(AR); } pb.Value++; } } //释放对象 myExcel.close(); System.Threading.Thread.Sleep(2000); GC.Collect(); GC.WaitForPendingFinalizers(); lblResult.Text = ""; lblResult.Visible = false; lblPrompt.Visible = false; lblPrompt.Text = ""; lblPrompt.Text = "提交数据: "; lblPrompt.Focus(); lblPrompt.Visible = true; pb.Maximum = qAttendanceR.Count; pb.Value = 0; #region //保存对象 while (qAttendanceR.Count > 0) { try { AttendanceR aR = qAttendanceR.Dequeue(); affectedCount += aR.import_AR_To_Preparative_Table(); pb.Value++; } catch (Exception ex) { MessageBox.Show(ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Error); msg.Msg = DirectoryHelper.getFileName(xlsFilePath) + ":导入失败; " + ex.ToString(); msg.Flag = false; return(msg); } } #endregion lblPrompt.Visible = false; //隐藏进度条。 pb.Visible = false; msg.Flag = true; msg.Msg = String.Format(@"导入完成;计{0}条.", affectedCount.ToString()); return(msg); }