public ContentResult Export(int year, int month)
{
var date = new DateTime(year, month, 1);
var book = new Workbook();
var sheet = book.Worksheets[0];
sheet.Name = "考勤统计";
sheet.Cells[0, 0].PutValue("序号");
sheet.Cells.SetColumnWidthPixel(0, 40);
var style = sheet.Cells[0, 0].GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
sheet.Cells[0, 0].SetStyle(style);
sheet.Cells.Merge(0, 0, 2, 1);
sheet.Cells[0, 1].PutValue("姓名");
sheet.Cells.SetColumnWidthPixel(1, 60);
style = sheet.Cells[0, 1].GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
sheet.Cells[0, 1].SetStyle(style);
sheet.Cells.Merge(0, 1, 2, 1);
sheet.Cells[0, 2].PutValue("日期及出缺勤情况");
style = sheet.Cells[0, 2].GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
sheet.Cells[0, 2].SetStyle(style);
var i = 0;
while (date.AddDays(i).Month == date.Month)
{
i++;
sheet.Cells[1, 1 + i].PutValue(i);
sheet.Cells.SetColumnWidthPixel(1 + i, 30);
}
sheet.Cells.Merge(0, 2, 1, i);
sheet.Cells[0, 1 + i + 1].PutValue("缺勤");
sheet.Cells.SetColumnWidthPixel(1 + i + 1, 60);
style = sheet.Cells[0, 1 + i + 1].GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
sheet.Cells[0, 1 + i + 1].SetStyle(style);
sheet.Cells.Merge(0, 1 + i + 1, 1, 6);
sheet.Cells[1, 1 + i + 1].PutValue("病假");
style = sheet.Cells[1, 1 + i + 1].GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
sheet.Cells[1, 1 + i + 1].SetStyle(style);
sheet.Cells[1, 1 + i + 2].PutValue("事假");
style = sheet.Cells[1, 1 + i + 2].GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
sheet.Cells[1, 1 + i + 2].SetStyle(style);
sheet.Cells[1, 1 + i + 3].PutValue("调休");
style = sheet.Cells[1, 1 + i + 3].GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
sheet.Cells[1, 1 + i + 3].SetStyle(style);
sheet.Cells[1, 1 + i + 4].PutValue("公休");
style = sheet.Cells[1, 1 + i + 4].GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
sheet.Cells[1, 1 + i + 4].SetStyle(style);
sheet.Cells[1, 1 + i + 5].PutValue("出差");
style = sheet.Cells[1, 1 + i + 5].GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
sheet.Cells[1, 1 + i + 5].SetStyle(style);
sheet.Cells[1, 1 + i + 6].PutValue("其他");
style = sheet.Cells[1, 1 + i + 6].GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
sheet.Cells[1, 1 + i + 6].SetStyle(style);
var user = OperatorProvider.Provider.Current();
var bll = new WorkmeetingBLL();
var data = bll.GetSigninData(user.DeptId, year, month);
for (int j = 0; j < data.Count; j++)
{
sheet.Cells[j + 2, 0].PutValue(j + 1);
sheet.Cells[j + 2, 1].PutValue(data[j].UserName);
var cnt1 = 0d;
var cnt2 = 0d;
var cnt3 = 0d;
var cnt4 = 0d;
var cnt5 = 0d;
var cnt6 = 0d;
var m = 0;
while (date.AddDays(m).Month == date.Month)
{
var signins = data[j].Signins.Where(x => x.MeetingStartTime > date.AddDays(m) && x.MeetingStartTime < date.AddDays(m + 1)).ToList();
if (signins.Count > 0)
{
var g = signins.GroupBy(x => new { x.MeetingId, x.MeetingStartTime }).OrderByDescending(x => x.Key.MeetingStartTime).First();
switch (g.Count())
{
case 1:
if (g.First().IsSignin)
{
sheet.Cells[j + 2, m + 2].PutValue("/");
}
else
{
sheet.Cells[j + 2, m + 2].PutValue(g.First().Reason);
switch (g.First().Reason)
{
case "病假":
cnt1 = cnt1 + 0.5;
break;
case "事假":
cnt2 = cnt2 + 0.5;
break;
case "调休":
cnt3 = cnt3 + 0.5;
break;
case "公休":
cnt4 = cnt4 + 0.5;
break;
case "出差":
cnt5 = cnt5 + 0.5;
break;
case "其他":
cnt6 = cnt6 + 0.5;
break;
default:
break;
}
}
break;
case 2:
var start = g.OrderBy(x => x.CreateDate).First();
var end = g.OrderBy(x => x.CreateDate).Last();
switch (g.Count(x => x.IsSignin))
{
case 0:
sheet.Cells[j + 2, m + 2].PutValue(start.Reason);
switch (start.Reason)
{
case "病假":
cnt1 = cnt1 + 0.5;
break;
case "事假":
cnt2 = cnt2 + 0.5;
break;
case "调休":
cnt3 = cnt3 + 0.5;
break;
case "公休":
cnt4 = cnt4 + 0.5;
break;
case "出差":
cnt5 = cnt5 + 0.5;
break;
case "其他":
cnt6 = cnt6 + 0.5;
break;
default:
break;
}
switch (end.Reason)
{
case "病假":
cnt1 = cnt1 + 0.5;
break;
case "事假":
cnt2 = cnt2 + 0.5;
break;
case "调休":
cnt3 = cnt3 + 0.5;
break;
case "公休":
cnt4 = cnt4 + 0.5;
break;
case "出差":
cnt5 = cnt5 + 0.5;
break;
case "其他":
cnt6 = cnt6 + 0.5;
break;
default:
break;
}
break;
case 1:
var reason = start.IsSignin ? end.Reason : start.Reason;
sheet.Cells[j + 2, m + 2].PutValue(reason);
switch (reason)
{
case "病假":
cnt1 = cnt1 + 0.5;
break;
case "事假":
cnt2 = cnt2 + 0.5;
break;
case "调休":
cnt3 = cnt3 + 0.5;
break;
case "公休":
cnt4 = cnt4 + 0.5;
break;
case "出差":
cnt5 = cnt5 + 0.5;
break;
case "其他":
cnt6 = cnt6 + 0.5;
break;
default:
break;
}
break;
default:
sheet.Cells[j + 2, m + 2].PutValue("/");
break;
}
break;
default:
break;
}
}
m++;
}
if (cnt1 > 0)
{
sheet.Cells[j + 2, m + 2].PutValue(cnt1);
}
if (cnt2 > 0)
{
sheet.Cells[j + 2, m + 2 + 1].PutValue(cnt2);
}
if (cnt3 > 0)
{
sheet.Cells[j + 2, m + 2 + 2].PutValue(cnt3);
}
if (cnt4 > 0)
{
sheet.Cells[j + 2, m + 2 + 3].PutValue(cnt4);
}
if (cnt5 > 0)
{
sheet.Cells[j + 2, m + 2 + 4].PutValue(cnt5);
}
if (cnt6 > 0)
{
sheet.Cells[j + 2, m + 2 + 5].PutValue(cnt6);
}
}
style = sheet.Cells[0, 0].GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
style.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
var range = sheet.Cells.CreateRange(0, 0, data.Count + 2, i + 8);
range.ApplyStyle(style, new StyleFlag()
{
All = true
});
book.Save(System.Web.HttpContext.Current.Response, "考勤统计表.xlsx", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Xlsx));
return(Content(null));
}
