public async Task <IActionResult> DownloadUnreportedEmailAddresses(int vendorCodeTypeId)
{
var unreportedEmailAddresses = await _vendorCodeService
.GetUnreportedEmailAwardCodes(vendorCodeTypeId);
var processed = _dateTimeProvider.Now;
try
{
// this will be disposed by FileStreamResult
var ms = new MemoryStream();
using var workbook = SpreadsheetDocument.Create(ms,
DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook
{
Sheets = new Sheets()
};
var stylesPart = workbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GetStylesheet();
stylesPart.Stylesheet.Save();
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);
var sheet = new Sheet
{
Id = relationshipId,
SheetId = 1,
Name = "Email Award Addresses"
};
sheets.Append(sheet);
var maximumColumnWidth = new Dictionary <int, int>();
var headerColumns = new string[] {
"User Id",
"Name",
"Email Address"
};
var headerRow = new Row();
int columnNumber = 0;
foreach (var dataItem in headerColumns)
{
(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 emailAddress in unreportedEmailAddresses)
{
var row = new Row();
int rowColumnNumber = 0;
var rowValues = new object[]
{
emailAddress.UserId,
emailAddress.Name,
emailAddress.Email
};
foreach (var resultItem in rowValues)
{
(var cell, var length) = CreateCell(resultItem ?? string.Empty);
row.AppendChild(cell);
if (maximumColumnWidth.ContainsKey(rowColumnNumber))
{
maximumColumnWidth[rowColumnNumber]
= Math.Max(maximumColumnWidth[rowColumnNumber], length);
}
else
{
maximumColumnWidth.Add(rowColumnNumber, length);
}
rowColumnNumber++;
}
sheetData.Append(row);
await _vendorCodeService
.UpdateEmailReportedAsync(GetActiveUserId(),
processed,
emailAddress.VendorCodeId);
}
await _vendorCodeService.SaveAsync();
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.Any())
{
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>());
}
}
workbook.Save();
workbook.Close();
ms.Seek(0, SeekOrigin.Begin);
return(new FileStreamResult(ms, ExcelMimeType)
{
FileDownloadName = FileUtility
.EnsureValidFilename($"EmailAwards.{ExcelFileExtension}")
});
}
#pragma warning disable CA1031 // Do not catch general exception types
catch (Exception ex)
{
_logger.LogError(ex,
"Error creating report of unreported email award codes for Vendor Code Id {vendorCodeTypeId}: {Message}",
vendorCodeTypeId,
ex.Message);
ShowAlertDanger("Error creating report of unreported email award codes");
await _vendorCodeService.UpdateEmailNotReportedAsync(GetActiveUserId(),
unreportedEmailAddresses);
return(RedirectToAction(nameof(EmailAward)));
}
#pragma warning restore CA1031 // Do not catch general exception types
}