/// <summary> /// 生成考勤数据 /// </summary> /// <param name="zd">站点</param> /// <param name="time1">起始日期</param> /// <param name="time2">结束日期</param> /// <param name="FileID">文件ID</param> private void KQ_Export(int zd, string names, DateTime time1, DateTime time2, string FileID) { string SQL = @"SELECT [日期],[姓名] ,isnull(convert(varchar(20),[上班签到],120),'X') as [上班签到] ,isnull(convert(varchar(20),[下班签退],120),'X') as [下班签退] ,datediff(d,'@起始时间@',日期) as 日 ,[出勤情况] FROM [CNGTZ].[dbo].[TZ_人员_考勤表] where 姓名 ='@姓名@' AND 日期 BETWEEN '@起始时间@' AND '@结束时间@' order by 日期"; SQL = SQL.Replace("@起始时间@", time1.ToShortDateString()); SQL = SQL.Replace("@结束时间@", time2.ToShortDateString()); TimeSpan sp = time2 - time1;//相隔天数 int days = sp.Days + 1; string nowFile = HttpContext.Current.Server.MapPath("~/Public/ExcelTemplate/DownTemp/替换文本.xls"); nowFile = nowFile.Replace("替换文本", FileID); MyExcel ME = new MyExcel(); ME.Open(nowFile);//打开副本文件 for (int i = 0; i < 31; i++) {//插入 抬头 日期行数据 if (i >= days) { ME.HideCol(3 + i, true); continue; } DateTime o = time1.AddDays(i); ME.WriteStr(o.ToString("dd"), 3, 3 + i); } ME.WriteStr("起始时间:" + time1.ToShortDateString() + " 至 " + time2.ToShortDateString() + " ", 2, 1); DAL dal = new DAL(); string[] name = names.Split(',');//生成姓名数组 for (int i = 0; i < name.Length - 1; i++) {//插入姓名行数 ME.InsertRow(6); ME.InsertRow(6); ME.CellsCopy("A4:AG5", "A6"); } for (int i = 0; i < name.Length; i++) {//生成每人考勤数据 ME.WriteStr(name[i], 4 + i * 2, 1); string zxSQL = SQL.Replace("@姓名@", name[i]); Sys.DebugMes(zxSQL); DataTable dt = dal.RunSqlDataTable(zxSQL); if (dt == null) { continue; } foreach (DataRow row in dt.Rows) { int n = int.Parse(row["日"].ToString()); string x1 = row["上班签到"].ToString(); string x2 = row["下班签退"].ToString(); string cq = row["出勤情况"].ToString(); if (zd == 2 || zd == 3) {//西路 和 南高 签到对应上班, 签退对应下班 if (cq != "异常" && cq != "正常") { System.Drawing.Color mycolor = System.Drawing.Color.Red; switch (cq) { case "事假": case "病假": case "其他": case "工伤": mycolor = System.Drawing.Color.Red; break; case "婚假": case "年休": mycolor =Color.Green; break; } ME.WriteStr(cq.Substring(0, 1), 4 + i * 2, 3 + n); ME.WriteStr(cq.Substring(0, 1), 5 + i * 2, 3 + n); ME.SetFont(mycolor, 4 + i * 2, 3 + n); ME.SetFont(mycolor, 5 + i * 2, 3 + n); } if (x1 != "X") { ME.WriteStr("○", 4 + i * 2, 3 + n); ME.SetFont(System.Drawing.Color.Black, 4 + i * 2, 3 + n); } if (x2 != "X") { ME.WriteStr("○", 5 + i * 2, 3 + n); ME.SetFont(System.Drawing.Color.Black, 5 + i * 2, 3 + n); } } if (zd == 4) {//麻柳沱 签到对应第一天,签退对应第二天 if (x1 != "X") { ME.WriteStr("○", 4 + i * 2, 3 + n); ME.WriteStr("○", 5 + i * 2, 3 + n); } if (x2 != "X") { ME.WriteStr("○", 4 + i * 2, 4 + n); ME.WriteStr("○", 5 + i * 2, 4 + n); } } } } ME.Save(); ME.Close(); }