输入月份自动生成excel考勤表，周末高亮，内容可以勾选
源码下载地址：地址
先上图,可以通过输入年、月自动计算指定月份的工作日
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
获得当月所有工作日
#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
生成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;#endregionworksheet.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
用到的工具实体为
#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();}}
如果觉得《输入月份自动生成excel考勤表 周末高亮 内容可以勾选》对你有帮助，请点赞、收藏，并留下你的观点哦！
