public void SaveReportDefinition(DynamicReportDefinition reportDefinition)
{
using (var validationPortalContext = ValidationPortalDataContextFactory.Create())
{
validationPortalContext.DynamicReportDefinitions.AddOrUpdate(reportDefinition);
validationPortalContext.SaveChanges();
}
}
public ActionResult AddDynamicReportDefinition(int schoolYearId)
{
var schoolYear = _schoolYearService.GetSchoolYearById(schoolYearId);
var dynamicReport = new DynamicReportDefinition
{
Enabled = true,
SchoolYearId = schoolYearId,
SchoolYear = schoolYear,
};
var rulesViews = _dynamicReportingService.GetRulesViews(schoolYearId);
var viewModel = new AdminDynamicReportViewModel
{
DynamicReportDefinition = dynamicReport,
ReportSchoolYearId = schoolYearId,
ValidationRulesViews = rulesViews.ToList()
};
return(PartialView("Partials/DynamicReportAddModal", viewModel));
}
public void UpdateReportDefinition(DynamicReportDefinition newReportDefinition)
{
using (var validationPortalContext = ValidationPortalDataContextFactory.Create())
{
var existingReportDefinition = validationPortalContext.DynamicReportDefinitions
.Include(x => x.Fields)
.Single(x => x.Id == newReportDefinition.Id);
existingReportDefinition.Name = newReportDefinition.Name?.Trim();
existingReportDefinition.Description = newReportDefinition.Description?.Trim();
existingReportDefinition.IsOrgLevelReport = newReportDefinition.IsOrgLevelReport;
foreach (var newField in newReportDefinition.Fields)
{
var existingField = existingReportDefinition.Fields.Single(x => x.Id == newField.Id);
existingField.Enabled = newField.Enabled;
existingField.Description = newField.Description?.Trim();
}
validationPortalContext.SaveChanges();
}
}
public ActionResult UpdateDynamicReportDefinition(DynamicReportDefinition formResponse)
{
_dynamicReportingService.UpdateReportDefinition(formResponse);
return(new HttpStatusCodeResult(HttpStatusCode.OK));
}
public ActionResult SaveDynamicReportDefinition(DynamicReportDefinition reportDefinition)
{
_dynamicReportingService.SaveReportDefinition(reportDefinition);
return(new HttpStatusCodeResult(HttpStatusCode.OK));
}
private byte[] ListToExcel(HttpRequest request, IReadOnlyCollection <T> query, ModelConfiguration config)
{
var modelAccessor = request.HttpContext.RequestServices.GetService <IEdmModelAccessor>();
var entityConfiguration = modelAccessor.EdmModel.ModelConfiguration().ForEntityType <T>();
//var formatterMap = entityConfiguration
// .DisplayTextFormatterMap;
//IEntityDisplayTextFormatter formatter;
//if (formatterMap.Has("Report"))
//{
// formatter = formatterMap.Get("Report");
//}
//else
//{
// formatter = formatterMap.Default;
//}
var report = entityConfiguration.ReportDefinitions.GetDefault();
using (var excelPackage = new ExcelPackage())
{
//Create the worksheet
var ws = excelPackage.Workbook.Worksheets.Add("Result");
//var imageFile = new FileInfo(@"https://i.imgur.com/G9ySTcg.jpg");
//ws.Drawings.AddPicture("test", imageFile);
//ws.CodeModule.
excelPackage.Workbook.CreateVBAProject();
var hyperlinkStyle = excelPackage.Workbook.AddHyperLinkStyle();
//get our column headings
var destinationRange = ws.Cells["A2"];
int columnCount;
if (report == null)
{
report = new DynamicReportDefinition <T>(config);
}
if (report != null)
{
var fields = report.Fields.ToArray();
columnCount = fields.Length;
for (var i = 0; i < fields.Length; i++)
{
var field = fields[i];
var headerCell = ws.Cells[1, i + 1];
if (!string.IsNullOrWhiteSpace(fields[i].Title))
{
headerCell.Value = fields[i].Title;
}
if (field.Kind != ReportFieldKind.PercentageBar)
{
headerCell.AutoFilter = true;
}
}
var cellDictionary = new Dictionary <ExcelRange, bool>();
void SetCellValue(ExcelRange cell, object value, bool append = false)
{
var hasExistingValue = cellDictionary.ContainsKey(cell);
if (!hasExistingValue)
{
cellDictionary.Add(cell, true);
}
if (!append || !hasExistingValue)
{
cell.Value = value;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
else
{
cell.Value = $"{cell.Value}\n{value}";
cell.Style.WrapText = true;
}
}
void ApplyField(IReportField field, ExcelRange cell, ExcelRange columnRange, object item, int column, int row, bool append = false)
{
object noValueValue = null;
if (string.IsNullOrWhiteSpace(cell.Text) && field.NoValueFormatter != null)
{
noValueValue = field.NoValueFormatter(item);
}
if (field.Link != null)
{
var link = field.Link(item);
cell.Hyperlink = new Uri(link);
switch (field.Style)
{
case ReportFieldStyle.Normal:
cell.StyleName = ExcelExtensions.HyperlinkStyle;
break;
}
}
var formatted = field.Formatter(item);
var fieldKind = field.Kind;
if (fieldKind == ReportFieldKind.Auto && formatted != null)
{
var type = formatted.GetType();
var underlyingType = Nullable.GetUnderlyingType(type);
if (underlyingType != null)
{
type = underlyingType;
}
if (type.IsNumericType())
{
fieldKind = ReportFieldKind.Number;
}
else if (type.IsCollection())
{
fieldKind = ReportFieldKind.Collection;
}
else if (type == typeof(DateTime) || type == typeof(DateTimeOffset))
{
fieldKind = ReportFieldKind.Date;
}
else if (type == typeof(string))
{
fieldKind = ReportFieldKind.String;
}
}
switch (fieldKind)
{
case ReportFieldKind.Auto:
break;
case ReportFieldKind.Currency:
var decimalString = formatted + "";
if (decimal.TryParse(decimalString, out var dec))
{
SetCellValue(cell, dec, append);
cell.Style.Numberformat.Format = "£###,###,##0.00";
}
break;
case ReportFieldKind.Number:
SetCellValue(cell, formatted, append);
break;
case ReportFieldKind.String:
SetCellValue(cell, formatted, append);
break;
case ReportFieldKind.Collection:
var collectionField = field as IReportCollectionField;
var collection = collectionField.PropertyAccessor(item);
var nestedField = collectionField.CollectionField;
if (collection != null)
{
foreach (var child in collection)
{
//sb.AppendLine(field)
ApplyField(nestedField, cell, columnRange, child, column, row, true);
}
}
break;
case ReportFieldKind.Date:
var dateString = formatted?.ToString();
if (DateTime.TryParse(dateString, out var date))
{
//cell.Formula = $"=DATEVALUE({dateString})";
if (!append)
{
cell.Formula = $"={date.ToOADate()}";
cell.Style.Numberformat.Format = "ddd, MMMM d, yyyy";
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
else
{
SetCellValue(cell, date.ToString("R"), true);
}
}
break;
case ReportFieldKind.ImageLink:
SetCellValue(cell, "___IMAGE___" + formatted, append);
break;
case ReportFieldKind.EmailAddress:
if (append)
{
SetCellValue(cell, formatted, true);
}
else
{
//cell.Formula = "HYPERLINK(\"mailto:" + field.Formatter(item) + "\",\"" + field.Formatter(item) + "\")";
var uri = formatted.ToStringOrEmpty();
if (!string.IsNullOrWhiteSpace(uri))
{
cell.Hyperlink = new Uri("mailto:" + uri);
}
cell.Value = uri;
cell.StyleName = hyperlinkStyle;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
break;
case ReportFieldKind.Percentage:
if (formatted == null || Equals(formatted, 0))
{
cell.Value = noValueValue;
}
else
{
cell.Style.Numberformat.Format = "#0.00%";
SetCellValue(cell, formatted, append);
}
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
break;
case ReportFieldKind.PercentageBar:
//range.Style.Numberformat.Format = "#0.00%";
columnRange.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
columnRange.IsRichText = true;
if (cell.RichText.Count > 0)
{
cell.RichText.Add("\r\n");
}
var value = formatted;
var hasValue = !Equals(null, value);
if (hasValue)
{
var numericValue = Convert.ToDouble(value);
var c = Color.ForestGreen;
if (numericValue < 0.8)
{
c = Color.YellowGreen;
}
if (numericValue < 0.6)
{
c = Color.DarkOrange;
}
if (numericValue < 0.3)
{
c = Color.Red;
}
const int maxRepetitions = 50;
const float lineSize = 3f;
var repetitions = (int)Math.Round(numericValue * maxRepetitions);
const string fontName = "consolas";
if (repetitions > 0)
{
cell.RichText.Add(new string('█', repetitions), color: c, bold: true, fontName: fontName, size: lineSize, underline: false);
}
var remainder = maxRepetitions - repetitions;
if (remainder > 0)
{
cell.RichText.Add(new string('█', remainder), color: Color.LightGray, fontName: fontName, bold: true, size: lineSize, underline: false);
}
cell.RichText.Add("\u200B", color: Color.LightGray);
if (field.CommentFormatter != null)
{
cell.RichText.Add("\r\n" + field.CommentFormatter(item), fontName: "Calibri", size: 10);
}
}
cell.Style.WrapText = true;
break;
}
}
for (var i = 0; i < fields.Length; i++)
{
var field = fields[i];
var column = i + 1;
var columnRange = ws.Cells[2, column, query.Count + 2, column];
var row = 2;
foreach (var item in query)
{
using (var cell = ws.Cells[row, column])
{
ApplyField(field, cell, columnRange, item, column, row);
}
row++;
}
}
}
else
{
columnCount = typeof(T).GetProperties().Length;
var data = query.ToList();
destinationRange.LoadFromCollection(data, true);
}
//Format the header
using (var headerRange = ws.Cells[1, 1, 1, columnCount])
{
headerRange.AutoFilter = true;
headerRange.Style.Font.Bold = true;
headerRange.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
headerRange.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
headerRange.Style.Font.Color.SetColor(Color.White);
}
using (var allRange = ws.Cells[1, 1, query.Count + 1, columnCount])
{
allRange.AutoFitColumns();
}
void SetColumnWidth(IReportField field, int column)
{
switch (field.Kind)
{
case ReportFieldKind.Collection:
SetColumnWidth((field as IReportCollectionField).CollectionField, column);
break;
case ReportFieldKind.PercentageBar:
ws.Column(column).Width = 30;
break;
case ReportFieldKind.ImageLink:
ws.Column(column).Width = 0;
break;
case ReportFieldKind.Date:
ws.Column(column).Width = 24;
break;
}
}
if (report != null)
{
var fields = report.Fields.ToArray();
for (var i = 0; i < fields.Length; i++)
{
var field = fields[i];
var column = i + 1;
SetColumnWidth(field, column);
}
}
for (var i = 2; i < query.Count + 2; i++)
{
using (var row = ws.Cells[i, 1, i, columnCount])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
if (i % 2 == 0)
{
row.Style.Fill.BackgroundColor.SetColor(Color.AliceBlue);
}
else
{
row.Style.Fill.BackgroundColor.SetColor(Color.White);
}
row.Style.Border.BorderAround(ExcelBorderStyle.None);
}
}
var borderColor = Color.LightSlateGray;
var borderStyle = ExcelBorderStyle.Thin;
using (var leftColumn = ws.Cells[1, 1, query.Count + 1, 1])
{
leftColumn.Style.Border.Left.Style = borderStyle;
leftColumn.Style.Border.Left.Color.SetColor(borderColor);
}
using (var rightColumn = ws.Cells[1, columnCount, query.Count + 1, columnCount])
{
rightColumn.Style.Border.Right.Style = borderStyle;
rightColumn.Style.Border.Right.Color.SetColor(borderColor);
}
using (var bottomRow = ws.Cells[query.Count + 1, 1, query.Count + 1, columnCount])
{
bottomRow.Style.Border.Bottom.Style = borderStyle;
bottomRow.Style.Border.Bottom.Color.SetColor(borderColor);
}
using (var topRow = ws.Cells[1, 1, 1, columnCount])
{
topRow.Style.Border.Top.Style = borderStyle;
topRow.Style.Border.Top.Color.SetColor(borderColor);
}
ws.View.FreezePanes(2, 1);
var vbaCode = new StringBuilder();
vbaCode.Append(@"Private Sub Workbook_Activate()
Dim Result As Excel.Worksheet
Dim ImageUrl As String
Set Result = ThisWorkbook.Sheets(""Result"")
Dim shp As Shape
Dim imagesLoaded As Integer
imagesLoaded = 0
Dim Range As Range
Dim Row As Integer
Dim K As Long, r As Range, v As Variant
K = 1
Result.Activate
For Each r In ActiveSheet.UsedRange
v = r.Value
If InStr(v, ""___IMAGE___"") > 0 Then
K = K + 1
imagesLoaded = imagesLoaded + 1
Img r, 100
End If
Next r
If imagesLoaded > 0 Then
ThisWorkbook.Save
End If
End Sub
Sub Img(cells As Range, size As Integer)
Dim filePath As String
filePath = Environ(""TEMP"") & ""\asfklasfnklfs.jpg""
Dim ImageUrl As String
ImageUrl = cells.Value
ImageUrl = Split(ImageUrl, ""___IMAGE___"")(1)
If Trim(ImageUrl & vbNullString) <> vbNullString Then
'imagesLoaded = imagesLoaded + 1
Download_File ImageUrl, filePath
Set shp = ActiveSheet.Shapes.AddPicture(filePath, True, True, 100, 100, -1, -1)
shp.Top = cells.Top
shp.Left = cells.Left
cells.RowHeight = size * 1.1
cells.ColumnWidth = (size / 5) * 2
shp.Height = size
End If
cells.Value = """"
End Sub
Function FileExists(ByVal FileToTest As String) As Boolean
FileExists = (Dir(FileToTest) <> """")
End Function
Sub DeleteFile(ByVal FileToDelete As String)
If FileExists(FileToDelete) Then 'See above
' First remove readonly attribute, if set
SetAttr FileToDelete, vbNormal
' Then delete the file
Kill FileToDelete
End If
End Sub
Function Download_File(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as MSXML2.XMLHTTP
Set oXMLHTTP = CreateObject(""MSXML2.XMLHTTP"")
oXMLHTTP.Open ""GET"", vWebFile, False 'Open socket to get the website
oXMLHTTP.Send 'send request
'Wait for request to finish
Do While oXMLHTTP.readyState <> 4
DoEvents
Loop
oResp = oXMLHTTP.responseBody 'Returns the results as a byte array
'Create local file and save results to it
vFF = FreeFile
If Dir(vLocalFile) <> """" Then Kill vLocalFile
Open vLocalFile For Binary As #vFF
Put #vFF, , oResp
Close #vFF
'Clear memory
Set oXMLHTTP = Nothing
End Function");
excelPackage.Workbook.CodeModule.Code = vbaCode.ToString();
excelPackage.Save();
//var heights = new List<double>();
//for (var row = 2; row <= query.Count + 1; row++)
//{
// var excelRow = ws.Row(row);
// heights.Add(excelRow.Height);
//}
//var maxHeight = heights.Max();
//for (var row = 2; row <= query.Count + 1; row++)
//{
// ws.Row(row).Height = 30;
//}
//excelPackage.Save();
var bytes = StreamToByteArray(excelPackage.Stream);
return(bytes);
}
}
