public async Task <IActionResult> View(int id)
{
try
{
var storedReport = await _reportService.GetReportResultsAsync(id);
PageTitle = storedReport.request.Name ?? "Report Results";
var viewModel = new ReportResultsViewModel
{
Title = PageTitle,
ReportResultId = id
};
if (storedReport.criterion.StartDate.HasValue)
{
viewModel.StartDate = storedReport.criterion.StartDate;
}
if (storedReport.criterion.EndDate.HasValue)
{
viewModel.EndDate = storedReport.criterion.EndDate;
}
if (storedReport.criterion.SystemId.HasValue)
{
viewModel.SystemName = (await _siteService
.GetSystemByIdAsync(storedReport.criterion.SystemId.Value)).Name;
}
if (storedReport.criterion.BranchId.HasValue)
{
viewModel.BranchName = await _siteService
.GetBranchName(storedReport.criterion.BranchId.Value);
}
if (storedReport.criterion.ProgramId.HasValue)
{
viewModel.ProgramName = (await _siteService
.GetProgramByIdAsync(storedReport.criterion.ProgramId.Value)).Name;
}
if (storedReport.criterion.GroupInfoId.HasValue)
{
viewModel.GroupName = (await _userService
.GetGroupInfoByIdAsync(storedReport.criterion.GroupInfoId.Value)).Name;
}
if (storedReport.criterion.SchoolDistrictId.HasValue)
{
viewModel.SchoolDistrictName = (await _schoolService
.GetDistrictByIdAsync(storedReport.criterion.SchoolDistrictId.Value)).Name;
}
if (storedReport.criterion.SchoolId.HasValue)
{
viewModel.SchoolName = (await _schoolService
.GetByIdAsync(storedReport.criterion.SchoolId.Value)).Name;
}
if (storedReport.criterion.VendorCodeTypeId.HasValue)
{
viewModel.VendorCodeName = (await _vendorCodeService
.GetTypeById(storedReport.criterion.VendorCodeTypeId.Value)).Description;
}
viewModel.ReportSet = JsonConvert
.DeserializeObject <StoredReportSet>(storedReport.request.ResultJson);
foreach (var report in viewModel.ReportSet.Reports)
{
int count = 0;
int totalRows = report.Data.Count();
var displayRows = new List <List <string> >();
if (report.HeaderRow != null)
{
var display = new List <string>();
foreach (var dataItem in report.HeaderRow)
{
display.Add(FormatDataItem(dataItem));
}
report.HeaderRow = display;
}
foreach (var resultRow in report.Data)
{
var displayRow = new List <string>();
foreach (var resultItem in resultRow)
{
displayRow.Add(FormatDataItem(resultItem));
}
displayRows.Add(displayRow);
count++;
}
report.Data = displayRows;
if (report.FooterRow != null)
{
var display = new List <string>();
foreach (var dataItem in report.FooterRow)
{
display.Add(FormatDataItem(dataItem));
}
report.FooterRow = display;
}
}
return(View(viewModel));
}
catch (GraException gex)
{
AlertDanger = gex.Message;
return(RedirectToAction("Index"));
}
}
public async Task <FileStreamResult> Download(int id)
{
var storedReport = await _reportService.GetReportResultsAsync(id);
PageTitle = storedReport.request.Name ?? "Report Results";
var viewModel = new ReportResultsViewModel
{
Title = PageTitle
};
var criteriaDictionnary = new Dictionary <string, object>();
if (storedReport.criterion.StartDate.HasValue)
{
criteriaDictionnary.Add("Start Date", storedReport.criterion.StartDate.Value.ToString());
}
if (storedReport.criterion.EndDate.HasValue)
{
criteriaDictionnary.Add("End Date", storedReport.criterion.EndDate.Value.ToString());
}
if (storedReport.criterion.SystemId.HasValue)
{
criteriaDictionnary.Add("System", (await _siteService
.GetSystemByIdAsync(storedReport.criterion.SystemId.Value)).Name);
}
if (storedReport.criterion.BranchId.HasValue)
{
criteriaDictionnary.Add("Branch", await _siteService
.GetBranchName(storedReport.criterion.BranchId.Value));
}
if (storedReport.criterion.ProgramId.HasValue)
{
criteriaDictionnary.Add("Program", (await _siteService
.GetProgramByIdAsync(storedReport.criterion.ProgramId.Value)).Name);
}
if (storedReport.criterion.GroupInfoId.HasValue)
{
criteriaDictionnary.Add("Group", (await _userService
.GetGroupInfoByIdAsync(storedReport.criterion.GroupInfoId.Value)).Name);
}
if (storedReport.criterion.SchoolDistrictId.HasValue)
{
criteriaDictionnary.Add("School District", (await _schoolService
.GetDistrictByIdAsync(storedReport.criterion.SchoolDistrictId.Value)).Name);
}
if (storedReport.criterion.SchoolId.HasValue)
{
criteriaDictionnary.Add("Program", (await _schoolService
.GetByIdAsync(storedReport.criterion.SchoolId.Value)).Name);
}
if (storedReport.criterion.VendorCodeTypeId.HasValue)
{
criteriaDictionnary.Add("Program", (await _vendorCodeService
.GetTypeById(storedReport.criterion.VendorCodeTypeId.Value)).Description);
}
viewModel.ReportSet = JsonConvert
.DeserializeObject <StoredReportSet>(storedReport.request.ResultJson);
var ms = new System.IO.MemoryStream();
using (var workbook = SpreadsheetDocument.Create(ms,
DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook
{
Sheets = new Sheets()
};
var stylesPart = workbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GetStylesheet();
stylesPart.Stylesheet.Save();
foreach (var report in viewModel.ReportSet.Reports)
{
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet(sheetData);
var sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>();
var relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>()
.Select(_ => _.SheetId.Value).Max() + 1;
}
string sheetName = report.Title ?? PageTitle ?? "Report Results";
if (sheetName.Length > 31)
{
sheetName = sheetName.Substring(0, 31);
}
var sheet = new Sheet
{
Id = relationshipId,
SheetId = sheetId,
Name = sheetName
};
sheets.Append(sheet);
var maximumColumnWidth = new Dictionary <int, int>();
if (report.HeaderRow != null)
{
var headerRow = new Row();
int columnNumber = 0;
foreach (var dataItem in report.HeaderRow)
{
(var cell, var length) = CreateCell(dataItem);
cell.StyleIndex = ExcelStyleIndexBold;
headerRow.AppendChild(cell);
if (maximumColumnWidth.ContainsKey(columnNumber))
{
maximumColumnWidth[columnNumber]
= Math.Max(maximumColumnWidth[columnNumber], length);
}
else
{
maximumColumnWidth.Add(columnNumber, length);
}
columnNumber++;
}
sheetData.Append(headerRow);
}
foreach (var resultRow in report.Data)
{
var row = new Row();
int columnNumber = 0;
foreach (var resultItem in resultRow)
{
(var cell, var length) = CreateCell(resultItem);
row.AppendChild(cell);
if (maximumColumnWidth.ContainsKey(columnNumber))
{
maximumColumnWidth[columnNumber]
= Math.Max(maximumColumnWidth[columnNumber], length);
}
else
{
maximumColumnWidth.Add(columnNumber, length);
}
columnNumber++;
}
sheetData.Append(row);
}
if (report.FooterRow != null)
{
var footerRow = new Row();
int columnNumber = 0;
foreach (var dataItem in report.FooterRow)
{
(var cell, var length) = CreateCell(dataItem);
cell.StyleIndex = ExcelStyleIndexBold;
footerRow.AppendChild(cell);
if (maximumColumnWidth.ContainsKey(columnNumber))
{
maximumColumnWidth[columnNumber]
= Math.Max(maximumColumnWidth[columnNumber], length);
}
else
{
maximumColumnWidth.Add(columnNumber, length);
}
columnNumber++;
}
sheetData.Append(footerRow);
}
if (report.FooterText != null)
{
foreach (var dataItem in report.FooterText)
{
var footerTextRow = new Row();
(var cell, var length) = CreateCell(dataItem);
footerTextRow.AppendChild(cell);
sheetData.Append(footerTextRow);
}
}
foreach (var value in maximumColumnWidth.Keys.OrderByDescending(_ => _))
{
var columnId = value + 1;
var width = maximumColumnWidth[value] + ExcelPaddingCharacters;
Columns cs = sheet.GetFirstChild <Columns>();
if (cs != null)
{
var columnElements = cs.Elements <Column>()
.Where(_ => _.Min == columnId && _.Max == columnId);
if (columnElements.Count() > 0)
{
var column = columnElements.First();
column.Width = width;
column.CustomWidth = true;
}
else
{
var column = new Column
{
Min = (uint)columnId,
Max = (uint)columnId,
Width = width,
CustomWidth = true
};
cs.Append(column);
}
}
else
{
cs = new Columns();
cs.Append(new Column
{
Min = (uint)columnId,
Max = (uint)columnId,
Width = width,
CustomWidth = true
});
sheetPart.Worksheet.InsertAfter(cs,
sheetPart.Worksheet.GetFirstChild <SheetFormatProperties>());
}
}
}
var criteriaSheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var criteriaSheetData = new SheetData();
criteriaSheetPart.Worksheet = new Worksheet(criteriaSheetData);
var criteriaSheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>();
var criteriaRelationshipId = workbook.WorkbookPart.GetIdOfPart(criteriaSheetPart);
uint criteriaSheetId = 1;
if (criteriaSheets.Elements <Sheet>().Count() > 0)
{
criteriaSheetId = criteriaSheets.Elements <Sheet>()
.Select(_ => _.SheetId.Value).Max() + 1;
}
string criteriaSheetName = "Report Criteria";
var criteriaSheet = new Sheet
{
Id = criteriaRelationshipId,
SheetId = criteriaSheetId,
Name = criteriaSheetName
};
criteriaSheets.Append(criteriaSheet);
var criteriaMaximumColumnWidth = new Dictionary <int, int>();
foreach (var criterion in criteriaDictionnary)
{
var row = new Row();
(var nameCell, var nameLength) = CreateCell(criterion.Key);
row.AppendChild(nameCell);
if (criteriaMaximumColumnWidth.ContainsKey(0))
{
criteriaMaximumColumnWidth[0]
= Math.Max(criteriaMaximumColumnWidth[0], nameLength);
}
else
{
criteriaMaximumColumnWidth.Add(0, nameLength);
}
(var dataCell, var dataLength) = CreateCell(criterion.Value);
row.AppendChild(dataCell);
if (criteriaMaximumColumnWidth.ContainsKey(1))
{
criteriaMaximumColumnWidth[1]
= Math.Max(criteriaMaximumColumnWidth[1], dataLength);
}
else
{
criteriaMaximumColumnWidth.Add(1, dataLength);
}
criteriaSheetData.Append(row);
}
foreach (var value in criteriaMaximumColumnWidth.Keys.OrderByDescending(_ => _))
{
var columnId = value + 1;
var width = criteriaMaximumColumnWidth[value] + ExcelPaddingCharacters;
Columns cs = criteriaSheet.GetFirstChild <Columns>();
if (cs != null)
{
var columnElements = cs.Elements <Column>()
.Where(_ => _.Min == columnId && _.Max == columnId);
if (columnElements.Count() > 0)
{
var column = columnElements.First();
column.Width = width;
column.CustomWidth = true;
}
else
{
var column = new Column
{
Min = (uint)columnId,
Max = (uint)columnId,
Width = width,
CustomWidth = true
};
cs.Append(column);
}
}
else
{
cs = new Columns();
cs.Append(new Column
{
Min = (uint)columnId,
Max = (uint)columnId,
Width = width,
CustomWidth = true
});
criteriaSheetPart.Worksheet.InsertAfter(cs,
criteriaSheetPart.Worksheet.GetFirstChild <SheetFormatProperties>());
}
}
workbook.Save();
workbook.Close();
ms.Seek(0, System.IO.SeekOrigin.Begin);
var fileOutput = new FileStreamResult(ms, ExcelMimeType)
{
FileDownloadName = $"{PageTitle}.{ExcelFileExtension}"
};
return(fileOutput);
}
}
public async Task <IActionResult> View(int id)
{
try
{
var storedReport = await _reportService.GetReportResultsAsync(id);
PageTitle = storedReport.request.Name ?? "Report Results";
var viewModel = new ReportResultsViewModel
{
Title = PageTitle,
ReportResultId = id
};
viewModel.ReportSet = JsonConvert
.DeserializeObject <StoredReportSet>(storedReport.request.ResultJson);
foreach (var report in viewModel.ReportSet.Reports)
{
int count = 0;
int totalRows = report.Data.Count();
var displayRows = new List <List <string> >();
if (report.HeaderRow != null)
{
var display = new List <string>();
foreach (var dataItem in report.HeaderRow)
{
display.Add(FormatDataItem(dataItem));
}
report.HeaderRow = display;
}
foreach (var resultRow in report.Data)
{
var displayRow = new List <string>();
foreach (var resultItem in resultRow)
{
displayRow.Add(FormatDataItem(resultItem));
}
displayRows.Add(displayRow);
count++;
}
report.Data = displayRows;
if (report.FooterRow != null)
{
var display = new List <string>();
foreach (var dataItem in report.FooterRow)
{
display.Add(FormatDataItem(dataItem));
}
report.FooterRow = display;
}
}
return(View(viewModel));
}
catch (GraException gex)
{
AlertDanger = gex.Message;
return(RedirectToAction("Index"));
}
}
