Exemple #1
0
        /// <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();
        }