public void SaveToExcelX(string beginTime, string endTime, string department, string selTeam, string buildDepart)
{
if (beginTime == "" || endTime == "")
{
this.ShowNotification(NotificationType.Error, "请填写日期！");
return;
}
if (department == "")
{
this.ShowNotification(NotificationType.Error, "请填写部门！");
return;
}
try
{
var begin = DateTime.Parse(beginTime);
var end = DateTime.Parse(endTime);
}
catch (Exception e)
{
this.ShowNotification(NotificationType.Error, "请填写正确日期！");
return;
}
var teamIDList = workPlan.getTeamID(GetCureentID());
IDictionary <string, object> Dic = new Dictionary <string, object>();
Dictionary <int, string> columns = new Dictionary <int, string>();
if (string.IsNullOrEmpty(selTeam))
{
columns = _iTResourceTeamService.GetSubTeamresource(GetCureentID());
}
else
{
columns = _iTResourceTeamService.GetUserInts(Convert.ToInt32(selTeam));
}
List <string> dates = workPlan.GetDates(beginTime, endTime);
SpRPTParameter parameter = new SpRPTParameter()
{
FromDate = beginTime, ToDate = endTime, UserDeptId = Convert.ToInt32(department), SubTeamId = (selTeam == "" ? null : selTeam), ProjectRequestorDeptId = (buildDepart == "" ? null : buildDepart)
};
var resRPT = _spRptTeamTimeSheetServer.GetRPPTList(parameter).ToList();
ViewBag.pros = resRPT;
if (beginTime == "")
{
beginTime = new DateTime(1900, 1, 1).ToString();
}
if (endTime == "")
{
endTime = new DateTime(2900, 1, 1).ToString();
}
dates = dates.Where(ct => DateTime.Parse(beginTime) <= DateTime.Parse(ct) & SqlMethods.DateDiffMinute(DateTime.Parse(endTime), DateTime.Parse(ct)) <= 0).ToList();
Dic.Add("d2", dates);
Dic.Add("d3", resRPT.Distinct());
BindViewBagUser();
ExcelPackage excel = new ExcelPackage();
ExcelWorksheet sheet = excel.Workbook.Worksheets.Add("sheet1");
//sheet.Cells.Style.ShrinkToFit = true;
sheet.Cells[1, 1].Value = "日期";
for (int x = 2; x <= columns.Count + 1; x++)
{
sheet.Cells[1, x].Value = columns.ElementAtOrDefault(x - 2).Value;
}
for (int i = 2; i <= dates.Count + 1; i++)
{
sheet.Cells[i, 1].Value = Convert.ToDateTime(dates[i - 2]).ToString("yyyy-MM-dd");
for (int x = 2; x <= columns.Keys.Count + 1; x++)
{
var tempData = resRPT.Where(d => d.uid == columns.ElementAtOrDefault(x - 2).Key&& d.dates.ToString("yyyy-MM-dd") == Convert.ToDateTime(dates[i - 2]).ToString("yyyy-MM-dd"));
if (tempData.Any() == false)
{
sheet.Cells[i, x].Value = "";
}
else
{
var res = "";
foreach (var h in tempData)
{
res = res + h.content + " ";
}
sheet.Cells[i, x].Value = res;
}
}
}
MemoryStream ms = new MemoryStream();
excel.SaveAs(ms);
//asp.net输出的Excel文件名
//如果文件名是中文的话，需要进行编码转换，否则浏览器看到的下载文件是乱码。
string fileName = HttpUtility.UrlEncode("ExcelX.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 <SpRPTTeamTimeSheet> GetRPPTList(SpRPTParameter parameter)
{
return(_repository.ExecuteStoredProcedure(SpRPTTeamTimeSheet.NAME,
SpRPTTeamTimeSheet.Parameters(parameter)).ToList());
}
public ActionResult getHumanResource(string beginTime, string endTime, string department, string selTeam, string buildDepart)
{
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);
}
int userids = -1;
var ints = CurrentUserDepartmentTrafficeLeaderId();
if (ints == -1)
{
userids = User.UserId;
}
else
{
userids = ints;
}
var teamIDList = workPlan.getTeamID(userids);
IDictionary <string, object> Dic = new Dictionary <string, object>();
Dictionary <int, string> columns = new Dictionary <int, string>();
if (string.IsNullOrEmpty(selTeam))
{
columns = _iTResourceTeamService.GetSubTeamresource(userids);
}
else
{
columns = _iTResourceTeamService.GetUserInts(Convert.ToInt32(selTeam));
}
List <string> dates = workPlan.GetDates(beginTime, endTime);
Dic.Add("d1", columns);
SpRPTParameter parameter = new SpRPTParameter()
{
FromDate = beginTime, ToDate = endTime, UserDeptId = Convert.ToInt32(department), SubTeamId = (selTeam == "" ? null : selTeam), ProjectRequestorDeptId = (buildDepart == "" ? null : buildDepart)
};
var resRPT = _spRptTeamTimeSheetServer.GetRPPTList(parameter).ToList();
ViewBag.pros = resRPT;
if (beginTime == "")
{
beginTime = new DateTime(1900, 1, 1).ToString();
}
if (endTime == "")
{
endTime = new DateTime(2900, 1, 1).ToString();
}
dates = dates.Where(ct => DateTime.Parse(beginTime) <= DateTime.Parse(ct) & SqlMethods.DateDiffMinute(DateTime.Parse(endTime), DateTime.Parse(ct)) <= 0).ToList();
Dic.Add("d2", dates);
Dic.Add("d3", resRPT.Distinct());
BindViewBagUser();
return(PartialView("hrTable", Dic));
}
