public ActionResult DownloadExcel(string data) { distribution_copy.Models.ExpandWI.RootObject urlResponse = new distribution_copy.Models.ExpandWI.RootObject(); Download model = new Download(); model = JsonConvert.DeserializeObject <Download>(data); TraceInputModel input = new TraceInputModel(); input.OrgName = model.AccountName; input.ProjectName = model.ProjectName; input.WIType = "Epic"; AccountService accountService = new AccountService(); TraceController trace = new TraceController(); ExcelPackage excel = trace.TraceExport(input, false); var added = trace.added; urlResponse.value = ((distribution_copy.Models.ExpandWI.RootObject)System.Web.HttpContext.Current.Session["EWorkItems"]).value.Where(x => (!added.Contains(x)) && x.fields.TeamProject == input.ProjectName).ToList(); var workSheet = excel.Workbook.Worksheets[0]; var colcount = workSheet.Dimension.End.Column; var rowCount = workSheet.Dimension.End.Row + 1; List <string> colNames = new List <string>(); for (int i = 1; i <= colcount; i++) { colNames.Add(workSheet.Cells[1, i].Value.ToString()); } int titlecount = colNames.Where(x => x.ToLower().StartsWith("title")).Count(); foreach (var WI in urlResponse.value) { int columnNo = 0; workSheet.Cells[rowCount, ++columnNo].Value = WI.id; workSheet.Cells[rowCount, ++columnNo].Value = WI.fields.WorkItemType; workSheet.Cells[rowCount, ++columnNo].Value = WI.fields.Title; columnNo += titlecount; workSheet.Cells[rowCount, columnNo++].Value = WI.fields.TeamProject; workSheet.Cells[rowCount, columnNo++].Value = WI.fields.State; workSheet.Cells[rowCount, columnNo++].Value = WI.fields.AreaPath; workSheet.Cells[rowCount, columnNo++].Value = WI.fields.IterationPath; rowCount++; } string excelName = input.OrgName + "-" + input.ProjectName + DateTime.Now.ToString(); using (var memoryStream = new MemoryStream()) { Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=" + excelName + ".xlsx"); excel.SaveAs(memoryStream); memoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); } return(null); }
public ExcelPackage TraceExport(TraceInputModel inp, bool flush = true) { InputModel inputModel = new InputModel { OrganizationName = inp.OrgName, ProjectName = inp.ProjectName, WorkItemType = inp.WIType }; RootObject List; try { List = (RootObject)Filter(inputModel, 1); } catch { WITypes(inputModel); List = (RootObject)Filter(inputModel, 1); } workSheet = excel.Workbook.Worksheets.Add("WorkItems"); workSheet.TabColor = System.Drawing.Color.White; workSheet.DefaultRowHeight = 12; workSheet.Row(1).Height = 20; workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; workSheet.Row(1).Style.Font.Bold = true; workSheet.Cells[1, 1].Value = "ID"; workSheet.Cells[1, 2].Value = "Work Item Type"; int j = AddColumns(List); workSheet.Cells[1, j++].Value = "Team Project"; workSheet.Cells[1, j++].Value = "State"; workSheet.Cells[1, j++].Value = "Area Path"; workSheet.Cells[1, j++].Value = "Iteration Path"; //workSheet.Cells[1,++j].Value = "Url"; //workSheet.Cells[1,++j].Value = "PlannedHours"; //workSheet.Cells[1,++j].Value = "ActualHours"; //workSheet.Cells[1,++j].Value = "Sprint"; //workSheet.Cells[1,++j].Value = "OriginalEstimate"; //workSheet.Cells[1,++j].Value = "CompletedWork"; //workSheet.Cells[1,++j].Value = "RemainingWork"; //workSheet.Cells[1,++j].Value = "CreatedDate"; //workSheet.Cells[1,++j].Value = "Description"; //workSheet.Cells[1,++j].Value = "CreatedBy"; //workSheet.Cells[1,++j].Value = "AssignedTo"; //workSheet.Cells[1,++j].Value = "ChangedBy"; recordIndex = 2; foreach (var i in List.value) { FindRelations(i, 3); } for (var i = 1; i <= columnNo; i++) { workSheet.Column(i).AutoFit(); } string excelName = inp.OrgName + "-" + (inp.ProjectName ?? "") + "-" + (inp.WIType ?? "") + DateTime.Now.ToString(); if (flush) { using (var memoryStream = new MemoryStream()) { Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=" + excelName + ".xlsx"); excel.SaveAs(memoryStream); memoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); return(excel); } } else { return(excel); } }