public bool ifExistsVacation() { bool result = false; //1.先判断当天是否为公司休息日. result = Have_A_Rest_Helper.ifDayOfRest(this._year_month_day); if (result) { return(false); } // 2. 判断当天是否请假了. //这里要写员工条件 string sqlStr = string.Format(@"SELECT 1 FROM ASK_FOR_LEAVE A_F_L WHERE A_F_L.Job_Number = '{0}' AND TRUNC(A_F_L.Leave_start_time,'DD') = TO_DATE('{1}','yyyy-MM-dd')", this.Job_number, this._year_month_day); result = OracleDaoHelper.getDTBySql(sqlStr).Rows.Count > 0 ? true : false; return(result); }
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; } }