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