输入月份自动生成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;
#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
- 用到的工具实体为
#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();
}
}