private int FillWorkloadData(List <WorkloadEntity> workloads, int startrow, bool isTester) { int firstrow = startrow; int standardWorkingDays = TFS.Utility.GetStandardWorkingDays(this.project.Name, TFS.Utility.GetBestIteration(this.project.Name)); var orderedLoads = GetOrderedWorkloads(workloads); var allbugs = Bug.GetAddedBugsByIteration(this.project.Name, API.TFS.Utility.GetBestIteration(this.project.Name)); object[,] arr = new object[orderedLoads.Count(), 35]; int line = 0; var capacities = TFS.Agile.Capacity.GetIterationCapacitiesForTeamMember(this.project.Name, TFS.Utility.GetBestIteration(this.project.Name).Id); foreach (var load in orderedLoads) { string person = Utility.GetPersonName(load.Key); int bugCount = 0; if (isTester) { bugCount = allbugs.Where(bug => bug.DiscoveryUser == load.Key).Count(); } else { bugCount = allbugs.Where(bug => bug.AssignedTo == load.Key).Count(); } double leavetime = load.Where(wl => wl.Type == "请假").Sum(wl => wl.SumHours); var dev = load.Where(wl => wl.SupperType == "研发"); double devtime = dev.Sum(wl => wl.SumHours); double devtime1 = dev.Where(wl => wl.Type == "开发").Sum(wl => wl.SumHours); double devtime2 = dev.Where(wl => wl.Type == "需求").Sum(wl => wl.SumHours); double devtime3 = dev.Where(wl => wl.Type == "设计").Sum(wl => wl.SumHours); double devtime4 = dev.Where(wl => wl.Type == "测试设计").Sum(wl => wl.SumHours); double devtime5 = dev.Where(wl => wl.Type == "测试执行").Sum(wl => wl.SumHours); double devtime6 = dev.Where(wl => wl.Type == "技术支持").Sum(wl => wl.SumHours); double devtime7 = devtime - (devtime1 + devtime2 + devtime3 + devtime4 + devtime5 + devtime6); double mgrtime = load.Where(wl => wl.SupperType == "管理").Sum(wl => wl.SumHours); double oprtime = load.Where(wl => wl.SupperType == "运维").Sum(wl => wl.SumHours); double doctime = load.Where(wl => wl.SupperType == "文档").Sum(wl => wl.SumHours); double studytime = load.Where(wl => wl.SupperType == "学习交流").Sum(wl => wl.SumHours); double presalestime = load.Where(wl => wl.SupperType == "售前/推广").Sum(wl => wl.SumHours); double othertime = load.Where(wl => wl.SupperType == "其他").Sum(wl => wl.SumHours) - leavetime; arr[line, 0] = person; //if (capacities.ContainsKey(load.Key)) //{ // arr[line, 1] = capacities[load.Key] * standardWorkingDays; //} //else //{ // arr[line, 1] = 9999;// "未在迭代settings中定义【"+Utility.GetPersonName(load.Key)+"】的容量"; //} arr[line, 1] = standardWorkingDays * 8;//capacities[load.Key] * standardWorkingDays;// standardWorkingDays * 8; arr[line, 2] = load.Where(wl => wl.Type != "请假").Sum(wl => wl.SumHours); arr[line, 3] = leavetime; arr[line, 4] = String.Format("=D{0}/C{0}", startrow); arr[line, 5] = bugCount; arr[line, 6] = String.Format("=G{0}/(D{0}/8)", startrow); arr[line, 7] = devtime / (load.Sum(wl => wl.SumHours) - leavetime); arr[line, 8] = devtime1; arr[line, 9] = String.Format("=IF(J{0}<>0,J{0}/D{0}, \"\"", startrow); arr[line, 10] = devtime2; arr[line, 11] = String.Format("=IF(L{0}<>0,L{0}/D{0}, \"\"", startrow); arr[line, 12] = devtime3; arr[line, 13] = String.Format("=IF(N{0}<>0,N{0}/D{0}, \"\"", startrow); arr[line, 14] = devtime4; arr[line, 15] = String.Format("=IF(P{0}<>0,P{0}/D{0}, \"\"", startrow); arr[line, 16] = devtime5; arr[line, 17] = String.Format("=IF(R{0}<>0,R{0}/D{0}, \"\"", startrow); arr[line, 18] = devtime6; arr[line, 19] = String.Format("=IF(T{0}<>0,T{0}/D{0}, \"\"", startrow); arr[line, 20] = devtime7; arr[line, 21] = String.Format("=IF(V{0}<>0,V{0}/D{0}, \"\"", startrow); arr[line, 22] = mgrtime; arr[line, 23] = String.Format("=IF(X{0}<>0,X{0}/D{0}, \"\"", startrow); arr[line, 24] = oprtime; arr[line, 25] = String.Format("=IF(Z{0}<>0,Z{0}/D{0}, \"\"", startrow); arr[line, 26] = doctime; arr[line, 27] = String.Format("=IF(AB{0}<>0,AB{0}/D{0}, \"\"", startrow); arr[line, 28] = studytime; arr[line, 29] = String.Format("=IF(AD{0}<>0,AD{0}/D{0}, \"\"", startrow); arr[line, 30] = presalestime; arr[line, 31] = String.Format("=IF(AF{0}<>0,AF{0}/D{0}, \"\"", startrow); arr[line, 32] = othertime; arr[line, 33] = String.Format("=IF(AH{0}<>0,AH{0}/D{0}, \"\"", startrow); startrow++; line++; } ExcelInterop.Range devRange = sheet.Range[sheet.Cells[firstrow, "B"], sheet.Cells[startrow - 1, "AI"]]; Utility.AddNativieResource(devRange); devRange.Value2 = arr; devRange.HorizontalAlignment = ExcelInterop.XlHAlign.xlHAlignRight; devRange.WrapText = true; devRange.RowHeight = 20; devRange.ColumnWidth = 7; Utility.SetCellBorder(devRange); Utility.SetCellAlignAndWrap(sheet.Range[sheet.Cells[firstrow, "B"], sheet.Cells[firstrow + orderedLoads.Count() - 1, "B"]]); Utility.SetCellPercentFormat(sheet.Range[sheet.Cells[firstrow, "F"], sheet.Cells[startrow - 1, "F"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "D"], sheet.Cells[startrow - 1, "D"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "F"], sheet.Cells[startrow - 1, "F"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "H"], sheet.Cells[startrow - 1, "H"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "I"], sheet.Cells[startrow - 1, "I"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "K"], sheet.Cells[startrow - 1, "K"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "M"], sheet.Cells[startrow - 1, "M"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "O"], sheet.Cells[startrow - 1, "O"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "Q"], sheet.Cells[startrow - 1, "Q"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "S"], sheet.Cells[startrow - 1, "S"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "U"], sheet.Cells[startrow - 1, "U"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "W"], sheet.Cells[startrow - 1, "W"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "Y"], sheet.Cells[startrow - 1, "Y"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "AA"], sheet.Cells[startrow - 1, "AA"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "AC"], sheet.Cells[startrow - 1, "AC"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "AE"], sheet.Cells[startrow - 1, "AE"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "AG"], sheet.Cells[startrow - 1, "AG"]]); Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "AI"], sheet.Cells[startrow - 1, "AI"]]); ExcelInterop.Range bugrange = sheet.Range[sheet.Cells[firstrow, "H"], sheet.Cells[startrow - 1, "H"]]; Utility.AddNativieResource(bugrange); bugrange.NumberFormat = "#0.00"; for (int col = 9; col <= 35; col += 2) { Utility.SetCellPercentFormat(sheet, firstrow, col, startrow - 1, col); } Utility.SetFormatBigger(sheet.Range[sheet.Cells[firstrow, "F"], sheet.Cells[startrow - 1, "F"]], 1.20d); Utility.SetFormatSmaller(sheet.Range[sheet.Cells[firstrow, "F"], sheet.Cells[startrow - 1, "F"]], 1.00d); Utility.SetFormatSmaller(sheet.Range[sheet.Cells[firstrow, "I"], sheet.Cells[startrow - 1, "I"]], 0.60d); return(startrow); }