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);
}
}
