public ActionResult getTeam(string beginTime, string endTime, string department)
{
if (beginTime == "" || endTime == "")
{
this.ShowNotification(NotificationType.Error, "请填写日期！");
return(null);
}
if (department == "")
{
this.ShowNotification(NotificationType.Error, "请填写部门！");
return(null);
}
try
{
var begin = DateTime.Parse(beginTime);
var end = DateTime.Parse(endTime);
}
catch (Exception e)
{
this.ShowNotification(NotificationType.Error, "请填写正确日期！");
return(null);
}
var teamIDList = workPlan.getTeamID(GetCureentID());
IDictionary <string, object> Dic = new Dictionary <string, object>();
SpRPTByTeamParameter sq = new SpRPTByTeamParameter()
{
FromDate = beginTime, ToDate = endTime, UserDeptId = Convert.ToInt32(department)
};
var zx = _rptByTeam.GetList(sq);
//= workPlan.GetTeams().Where(u => teamIDList.Contains(u.teamID)).Where(u => u.status == DataStatus.ProjectState.pendingByTeamLeaderEnd || u.status == DataStatus.ProjectState.closed).OrderBy(u=>u.leadID).ToList();
var group1 = (from u in zx
orderby u.lead
group u by new { u.lead }
into g
select new { c = g.Key, d = g.Count() })
.ToList();
var group2 = (from u in zx
orderby u.lead
group u by new { u.lead, u.name }
into g
select new { d = g.Count() })
.ToList();
Dic.Add("d1", group1);
Dic.Add("d2", group2);
// if (beginTime == "") { beginTime = new DateTime(1900, 1, 1).ToString(); }
//if (endTime == "") { endTime = new DateTime(2900, 1, 1).ToString(); }
var list = zx.Distinct().OrderBy(ct => ct.lead).ToList();//endTime大于ct.EndTime(页面大于数据库)
Dic.Add("d3", list);
if (list.Count == 0)
{
this.ShowNotification(NotificationType.Information, "没有信息！");
return(null);
}
return(Json(Dic, JsonRequestBehavior.AllowGet));
return(View(Dic));
//return PartialView("tmTable", Dic);
}
public void SaveToExcel(string beginTime, string endTime, string department)
{
//引入EPPlus的命名空间
//using OfficeOpenXml;
notification(beginTime, endTime, department);
ExcelPackage excel = new ExcelPackage();
ExcelWorksheet sheet = excel.Workbook.Worksheets.Add("sheet1");
var teamIDList = workPlan.getTeamID(GetCureentID());
SpRPTByTeamParameter sq = new SpRPTByTeamParameter()
{
FromDate = beginTime, ToDate = endTime, UserDeptId = Convert.ToInt32(department)
};
var zx = _rptByTeam.GetList(sq);
//var zx = workPlan.GetTeams().Where(u => teamIDList.Contains(u.teamID)).Where(u => u.status == DataStatus.ProjectState.pendingByTeamLeaderEnd || u.status == DataStatus.ProjectState.closed).OrderBy(u=>u.leadID).ToList();
var list = new List <team>();
sheet.Cells.Style.ShrinkToFit = true;
sheet.Cells[1, 1].Value = "领导";
sheet.Cells[1, 2].Value = "设计师";
sheet.Cells[1, 3].Value = "项目";
for (int i = 2; i <= list.Count + 1; i++)
{
sheet.Cells[i, 1].Value = list.ElementAtOrDefault(i - 2).lead;
sheet.Cells[i, 2].Value = list.ElementAtOrDefault(i - 2).name;
sheet.Cells[i, 3].Value = list.ElementAtOrDefault(i - 2).proName;
}
var group1 = list.GroupBy(u => u.leadID).Select(u => u.Count()).ToList();
var index1 = 2;
foreach (var team in group1)
{
sheet.Cells[index1, 1, index1 + team - 1, 1].Merge = true;
sheet.Cells[index1, 1, index1 + team - 1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
sheet.Cells[index1, 1, index1 + team - 1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
sheet.Cells[index1, 1, index1 + team - 1, 1].Value = sheet.Cells[index1, 1].Value;
index1 += team;
}
var group2 = list.GroupBy(u => u.uid).Select(u => u.Count()).ToList();
var index2 = 2;
foreach (var team in group2)
{
sheet.Cells[index2, 2, index2 + team - 1, 2].Merge = true;
sheet.Cells[index2, 2, index2 + team - 1, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
sheet.Cells[index2, 2, index2 + team - 1, 2].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
sheet.Cells[index2, 2, index2 + team - 1, 2].Value = sheet.Cells[index2, 2].Value;
index2 += team;
}
MemoryStream ms = new MemoryStream();
excel.SaveAs(ms);
//asp.net输出的Excel文件名
//如果文件名是中文的话，需要进行编码转换，否则浏览器看到的下载文件是乱码。
string fileName = HttpUtility.UrlEncode("Excel.xlsx");
Response.ContentType = "application/vnd.ms-excel";
//Response.ContentType = "application/download"; //也可以设置成download
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
Response.Buffer = true;
Response.Clear();
Response.BinaryWrite(ms.GetBuffer());
Response.End();
}
public List <SpRPTByTeamTimeSheet> GetList(SpRPTByTeamParameter parameter)
{
return
(_repository.ExecuteStoredProcedure(SpRPTByTeamTimeSheet.NAME, SpRPTByTeamTimeSheet.Parameters(parameter))
.ToList());
}
