public V_W_S get_V_W_S_By_Date_And_Dept() { V_W_S v_W_S = null; string sqlStr = String.Format(@"SELECT DEPT, TO_CHAR(Work_And_Rest_Date,'YYYY-MM-DD') AS Work_And_Rest_Date, CAST(Work_Rate AS VARCHAR2(10)) AS Work_Rate, Work_OR_REST, Day_Of_Week FROM V_W_S WHERE Dept= '{0}' AND Work_And_Rest_Date = TO_DATE('{1}','YYYY-MM-DD')", this.dept, this.work_and_rest_date); v_W_S = ConvertHelper <V_W_S> .ConvertToList(OracleDaoHelper.getDTBySql(sqlStr))[0]; return(v_W_S); }
public Tools.MSG genExcel(out string xlsFilePath) { killHwndOfXls(); MSG msg = new MSG(); string excelName = _YearAndMonthStr + "_工作安排表.xls"; string defaultDir = Environment.CurrentDirectory + string.Format(@"\工作安排表", excelName); string xlsFileFullPath = FileNameDialog.getSaveFileNameWithDefaultDir("请选择考勤记录:", "*.xls|*.xls", defaultDir, excelName); if (!xlsFileFullPath.Contains(@"\")) { msg.Flag = false; msg.Msg = "取消了选择!"; xlsFilePath = xlsFileFullPath; return(msg); } ApplicationClass app = new ApplicationClass(); hwndOfXls_Queue.Enqueue(app.Hwnd); app.Visible = false; Workbook wBook = null; int rowsMaxCount = 0; int colsMaxCount = 0; try { wBook = app.Workbooks.Add(true); Worksheet wS = wBook.Worksheets.Item[1] as Worksheet; //每行格式设置,注意标题占一行。 rowsMaxCount = this.getDeptNum() + 4; Queue <int> daysQueue = this.getAllDaysOfThisMonth(); colsMaxCount = daysQueue.Count; //每行格式设置,注意标题占一行。 Range range = wS.get_Range(wS.Cells[1, 1], wS.Cells[rowsMaxCount + 1, colsMaxCount + 1]); //设置单元格为文本。 range.NumberFormatLocal = "@"; //水平对齐方式 range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //第一行写考勤分析结果。 wS.Cells[1, 1] = _YearAndMonthStr + " 工作安排分析结果"; wS.Cells[1, 3] = "休假"; ((Range)(wS.Cells[1, 4])).Interior.Color = System.Drawing.Color.Green.ToArgb(); //获取该日期详细的考勤记录。 wS.Cells[1, 5] = "周日"; ((Range)wS.Cells[1, 6]).Interior.Color = System.Drawing.Color.Yellow.ToArgb(); V_AR_DETAIL v_AR_Detail = V_AR_DETAIL.get_V_AR_Detail(_YearAndMonthStr); //第三行:考勤时间 wS.Cells[3, 1] = "考勤时间"; wS.Cells[3, 3] = String.Format(@"{0} ~ {1}", v_AR_Detail.Start_date, v_AR_Detail.End_date); wS.Cells[3, 10] = "制表时间"; wS.Cells[3, 12] = v_AR_Detail.Tabulation_time; //书写部门 List <string> deptList = this.getDeptList(); for (int i = 0; i <= deptList.Count - 1; i++) { wS.Cells[5 + i, 1] = deptList[i].ToString(); } string v_w_s_Day = string.Empty; //开始书写此月的日子 int colIndex = 1; while (daysQueue.Count > 0) { colIndex++; int day = daysQueue.Dequeue(); wS.Cells[4, colIndex] = day; v_w_s_Day = _YearAndMonthStr + "-" + day.ToString().PadLeft(2, '0'); for (int i = 0; i <= deptList.Count - 1; i++) { //填写该日内所有部门的休假情况. V_W_S v_W_S = new V_W_S(); v_W_S.Dept = deptList[i].ToString(); v_W_S.Work_and_rest_date = v_w_s_Day; v_W_S = v_W_S.get_V_W_S_By_Date_And_Dept(); wS.Cells[5 + i, colIndex] = v_W_S.Work_rate; if ("休息".Equals(v_W_S.Work_or_rest, StringComparison.OrdinalIgnoreCase)) { ((Range)wS.Cells[5 + i, colIndex]).Interior.Color = System.Drawing.Color.Green.ToArgb(); continue; } //注意周日为每周的第一天。 if ("1" == v_W_S.Day_of_week) { //周日用暗灰色 ((Range)wS.Cells[5 + i, colIndex]).Interior.Color = System.Drawing.Color.Yellow.ToArgb(); } } } //自动调整列宽 //range.EntireColumn.AutoFit(); //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存excel文档并关闭 wBook.SaveAs(xlsFileFullPath, 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, xlsFileFullPath, Type.Missing); //退出Excel程序 app.Quit(); //释放资源 System.Runtime.InteropServices.Marshal.ReleaseComObject(range); System.Runtime.InteropServices.Marshal.ReleaseComObject(wS); System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //调用GC的垃圾收集方法 GC.Collect(); GC.WaitForPendingFinalizers(); msg.Msg = "工作安排: " + xlsFileFullPath; msg.Flag = true; xlsFilePath = xlsFileFullPath; return(msg); } catch (Exception ex) { MessageBox.Show(ex.Message, "提示消息:", MessageBoxButtons.OK, MessageBoxIcon.Information); msg.Msg = ex.ToString(); msg.Flag = false; xlsFilePath = xlsFileFullPath; return(msg); } }