输入月份自动生成excel考勤表，周末高亮，内容可以勾选

输入月份自动生成excel考勤表，周末高亮，内容可以勾选

源码下载地址：地址

1. 先上图,可以通过输入年、月自动计算指定月份的工作日
   
   2.Excel操作类引用为

using Excel = Microsoft.Office.Interop.Excel;

3.先定义初始的变量，周一至周日，由于不涉及到数据库，所以参与考勤的人员是做到配置文件里面的

#region 定义初始变量
        public static string[] workDaysArray = { "一", "二", "三", "四", "五", "六", "日" };
        private static readonly string inWorksUsers = ConfigurationManager.AppSettings["inWorksUser"];
#endregion

3.定义方法计算当月第一天是周一

#region 获取当月一号是周几
        /// <summary>
        /// 获取当月一号是周几
        /// </summary>
        /// <param name="years"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public static int GetWeekDayOfMonth(int years, int month)
        {
            string dateString = years.ToString() + '-' + month.ToString() + '-' + "01";
            DateTime converDate = Convert.ToDateTime(dateString);
            int dayOfWeek = (int)DateTime.Parse(dateString).DayOfWeek;
            return dayOfWeek;
        }
 #endregion

4.定义方法计算当月共多少天

 #region 获取当月共多少天
        /// <summary>
        /// 获取当月共多少天
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public static int GetManyDaysOfMonth(int years, int month)
        {
            string dateString = years.ToString() + '-' + month.ToString() + '-' + "01";
            DateTime converDate = Convert.ToDateTime(dateString);
            int days = DateTime.DaysInMonth(converDate.Year, converDate.Month);
            return days;
        }
  #endregion

5. 获得当月所有工作日

#region 生成每月工作日
        /// <summary>
        /// 生成每月工作日
        /// </summary>
        /// <param name="dayOfWeek"></param>
        /// <returns></returns>
        public static List<WorkWeekDays> createMonthWorks(int years, int month)
        {
            List<WorkWeekDays> workDayLists = new List<WorkWeekDays>();
            //当月一号周几
            int dayOfWeek = GetWeekDayOfMonth(years, month);
            if (dayOfWeek == 0)
                dayOfWeek = 7;
            dayOfWeek = dayOfWeek - 1;
            //当月共多少天
            int manyDays = GetManyDaysOfMonth(years, month);
            int daysFlag = dayOfWeek;
            for (int i = 1; i < (manyDays + 1); i++)
            {

                if (daysFlag >= 7)
                    daysFlag = 0;

                string weeks = workDaysArray[daysFlag].ToString();
                int daysOfMonth = GetDaysOfMonth(years.ToString(), month.ToString(), i.ToString());
                var workDay = new WorkWeekDays
                {
                    Days = i.ToString(),
                    weekDays = weeks.ToString(),
                    isWorkDay = daysFlag < 5 ? 0 : 1,
                };
                daysFlag += 1;
                workDayLists.Add(workDay);
            }
            return workDayLists;

        }
 #endregion

6. 生成Excel并调整样式

 #region 创建Excel文件


        public static void CreateExcelFile(string FileName, int years, int months)
        {
            var resultData = createMonthWorks(years, months);
            object Nothing = System.Reflection.Missing.Value;
            var app = new Microsoft.Office.Interop.Excel.Application
            {
                Visible = false
            };
            Workbook workBook = app.Workbooks.Add(Nothing);
            Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[1];
            worksheet.Name = "Work";
           
            worksheet.Cells[3, 1] = "日期";
            worksheet.Cells[4, 1] = "姓名";
           
            var tagList = new List<string>
            {
                "出勤：,√",
                "正休：,●",
                "调休：,〇",
                "请假：,★",
                "迟到：,■"
            };
            int tagFlag = 6;
            var daysCount = resultData.Count;
            foreach (var item in tagList)
            {
                tagFlag--;
                worksheet.Cells[1, 2] = years.ToString();
                ((Range)worksheet.Cells[1, 2]).Interior.ColorIndex = 27;
               
                worksheet.Cells[1, 3] = "年";
                worksheet.Cells[1, 4] = months.ToString();
                ((Range)worksheet.Cells[1, 4]).Interior.ColorIndex = 27;
                worksheet.Cells[1, 5] = "月";
                if (tagFlag > 0)
                {
                    string[] tagListArray = item.Split(',');
                    worksheet.Cells[1, (daysCount - tagFlag)] = tagListArray[0].Replace('：',' ') ;
                    worksheet.Cells[2, (daysCount - tagFlag)] = tagListArray[1];
                }
               
            }
            #region 处理参与考勤的人员
            int usersFlag = 4;
            string[] inWorksUserArray = inWorksUsers.Replace('，', ',').Split(',');
            foreach (var item in inWorksUserArray)
            {
                usersFlag++;
                worksheet.Cells[usersFlag, 1] = item;
                app.ActiveSheet.Rows[usersFlag].RowHeight = 1 / 0.05;
            }
            #endregion

            #region 处理工作日并生成
            int daysFlag = 1;
            var flagList = string.Join(",", tagList.ToArray());
            int inWorksUserArrayLength = inWorksUserArray.Length + 5;
            foreach (var item in resultData)
            {
                daysFlag++;
                worksheet.Cells[3, daysFlag] = item.Days;
                worksheet.Cells[4, daysFlag] = item.weekDays;
                if (item.isWorkDay == 1)
                {
                    for (int i = 3; i < inWorksUserArrayLength; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, daysFlag]).Interior.ColorIndex = 27;
                    }
                }

                for (int userI = 5; userI < inWorksUserArrayLength; userI++)
                {
                    var cell = (Range)worksheet.Cells[userI, daysFlag];
                    cell.Validation.Delete();
                    cell.Validation.Add(
                       XlDVType.xlValidateList,
                       XlDVAlertStyle.xlValidAlertInformation,
                       XlFormatConditionOperator.xlBetween,
                       flagList,
                       Type.Missing);

                    cell.Validation.IgnoreBlank = true;
                    cell.Validation.InCellDropdown = true;
                }
            }
            #endregion

            #region 设置全局样式

            Range r = worksheet.get_Range((Range)worksheet.Cells[1, 1], (Range)worksheet.Cells[inWorksUserArrayLength, (daysCount) + 1]);
            r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            r.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            r.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            r.ColumnWidth = 35;
            r.EntireColumn.AutoFit();
            app.ActiveSheet.Rows[1].RowHeight = 1 / 0.035;
            app.ActiveSheet.Rows[2].RowHeight = 1 / 0.035;
            
            #endregion
            worksheet.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
            workBook.Close(false, Type.Missing, Type.Missing);
            app.Quit();
        }


        #endregion

7. 用到的工具实体为

    #region 工具实体
    /// <summary>
    /// 工作日
    /// </summary>
    public class WorkWeekDays
    {
        public string Days { get; set; }

        public string weekDays { get; set; }

        public int isWorkDay { get; set; }
    }
    #endregion

9.控制台调用即可，默认文件生成地址为：当前执行目录

 static void Main(string[] args)
        {
            try
            {


                string year = "";
                string month = "";
                Console.WriteLine("请输入年份:");
                year = Console.ReadLine();
                Console.WriteLine("请输入月份:");
                month = Console.ReadLine();
                Console.WriteLine("正在确认:" + year + "年" + month + "月");
                Thread.Sleep(200);
                Console.WriteLine("已确认，正在生成。。");
                Thread.Sleep(50);
                Console.WriteLine("已确认，正在生成。。。");
                Thread.Sleep(50);
                Console.WriteLine("已确认，正在生成。。。。");
                string currentPath = Directory.GetCurrentDirectory();
                string fileName = year + "年" + month + "月"+"考勤表,文件标记为："+ Guid.NewGuid().ToString().Substring(0,6);
                string filePath = currentPath + "/" + fileName;
                ClassLibrary.CoreClass.CreateExcelFile(filePath + ".xls", Convert.ToInt32(year), Convert.ToInt32(month));
                Console.WriteLine("文件生成成功！");
                Console.ReadLine();
            }
            catch (Exception ex)
            {
                Console.WriteLine("系统异常！"+ex.Message);
                Console.ReadLine();
            }
        }