For a while, I have worked with the Nuget Epplus package to export MVC models to fresh Excel documents. While the code I show below works correctly, I have a feeling that either I can make this code better or perhaps that there is a coding pattern of which I am unaware that could simplify what I am trying to do.
The main issues are that Epplus does not:
- read MVC model annotations (e.g.,
[DisplayFormat(DataFormatString="{0:d}")]) - format date or time values as dates or times, instead formatting them as integers
To address these issues, I created a class EpplusExcelPackage which has evolved as I have learned more about C#. A recent purchase of ReSharper has helped me to further improve the code.
In order to have the correct output, as you can see from the code shown below, I have to use reflection and several different looping methods to get the model size and properties and format accordingly.
What I'm really hoping is that someone sees something obvious, such as a coding approach or a design pattern that would simplify the process. Any ideas?
Steps:
In a Controller
ActionResult, create a new instance of a custom class that accepts theIEnumerableto be exported along with other string parameters.var exportIenumerable = new EpplusExcelPackage<ModelToExport>(data.AsEnumerable(), /* string params for Excel heading */); byte[] export = exportIenumerable.ExportToExcel();Use the
EpplusExcelPackagecustom class to -- along with creating the new Excel document, identify anyDateTimefields and format them accordingly. Otherwise, those fields would export as integers. Here is the full code, as there will inevitably be questions about the code if I were to show only the highlights.
That being said, I believe the important area is this method. I have to use reflection to loop through the IEnumerable in order to identify the DateTime columns and format appropriately.
// Without this, DateTimes are formatted as integers
private void FormatDateTimeAsDate(ExcelWorksheet worksheet)
{
for (var j = 0; j < _modelProperties.Length; j++)
{
if (_modelProperties[j].PropertyType == _typeNullDatetime
&& DateTimeFormatInfo.CurrentInfo != null)
worksheet.Column(j + ColumnStart).Style.Numberformat.Format =
DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
if (_modelProperties[j].PropertyType != _typeDateTime) continue;
if (DateTimeFormatInfo.CurrentInfo != null)
worksheet.Column(j + ColumnStart).Style.Numberformat.Format =
DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
}
}
Here is the full class:
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Globalization;
using System.Reflection;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
namespace ExporterModule
{
public class EpplusExcelPackage<T> : IEpplusExcelPackage where T : class
{
private readonly IEnumerable<T> _dataToExport;
private readonly string _reportName;
private readonly string _userName;
// Used to format data range on worksheet
private string _modelRange;
private const int ColumnStart = 5;
private readonly int _rowCount;
// Reflection used to identify DateTime fields for formatting
private readonly Type _typeNullDatetime = typeof(DateTime?);
private readonly Type _typeDateTime = typeof(DateTime);
private readonly PropertyInfo[] _modelProperties = typeof(T).GetProperties();
public EpplusExcelPackage(IEnumerable<T> dataToExport, string reportName, string userName)
{
_dataToExport = dataToExport;
_reportName = reportName;
_userName = userName;
_rowCount = GetRowCount();
}
private int GetRowCount()
{
var count = 0;
IEnumerator<T> enumerator;
if (_dataToExport == null) return count;
using (enumerator = _dataToExport.GetEnumerator())
while (enumerator.MoveNext())
count++;
return count;
}
// Without this, DateTimes are formatted as integers
private void FormatDateTimeAsDate(ExcelWorksheet worksheet)
{
for (var j = 0; j < _modelProperties.Length; j++)
{
if (_modelProperties[j].PropertyType == _typeNullDatetime
&& DateTimeFormatInfo.CurrentInfo != null)
worksheet.Column(j + ColumnStart).Style.Numberformat.Format =
DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
if (_modelProperties[j].PropertyType != _typeDateTime) continue;
if (DateTimeFormatInfo.CurrentInfo != null)
worksheet.Column(j + ColumnStart).Style.Numberformat.Format =
DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
}
}
public byte[] ExportToExcel()
{
using (var excelPackage = new ExcelPackage())
{
// create worksheet
excelPackage.Workbook.Worksheets.Add("Sheet1");
var worksheet = excelPackage.Workbook.Worksheets[1];
worksheet.Name = "Sheet1";
excelPackage.Workbook.Properties.Author = _userName;
AddHeaderInfo(worksheet);
if (_rowCount == 0)
worksheet.Cells[1, ColumnStart].Value = "No records exist.";
else
worksheet.Cells[1, ColumnStart].LoadFromCollection(_dataToExport, true);
FormatDateTimeAsDate(worksheet);
AutoFitColumns(worksheet);
FormatDataTable(worksheet);
return excelPackage.GetAsByteArray();
}
}
private void FormatDataTable(ExcelWorksheet worksheet)
{
if (_rowCount == 0)
{
var range = worksheet.Cells["E1"];
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#ffc7ce"));
range.Style.Font.Color.SetColor(ColorTranslator.FromHtml("#be0006"));
}
else
{
var dataRange = string.Concat("E1:", GetRightmostColumnLetter(), (_rowCount + 1).ToString());
var range = worksheet.Cells[dataRange];
var table = worksheet.Tables.Add(range, "DataExport");
table.TableStyle = TableStyles.Medium15;
}
}
private static string GetExcelColumnName(int columnNumber)
{
var dividend = columnNumber;
var columnName = string.Empty;
while (dividend > 0)
{
var modulo = (dividend - 1) % 26;
columnName = Convert.ToChar(65 + modulo) + columnName;
dividend = (dividend - modulo) / 26;
}
return columnName;
}
private void AutoFitColumns(ExcelWorksheet worksheet)
{
var rightCol = GetRightmostColumnLetter();
var fullRows = (_rowCount <= 3 ? 3 : _rowCount).ToString();
_modelRange = string.Concat("A1:", rightCol, fullRows);
var modelTable = worksheet.Cells[_modelRange];
modelTable.AutoFitColumns();
}
private string GetRightmostColumnLetter()
{
var dataPropertiesInfos = typeof(T).GetProperties();
var dataCols = dataPropertiesInfos.Length - 1;
var fullColumns = dataCols + ColumnStart;
var rightCol = GetExcelColumnName(fullColumns);
return rightCol;
}
private void AddHeaderInfo(ExcelWorksheet worksheet)
{
worksheet.Cells[1, 1].Value = "Report Name";
worksheet.Cells[1, 2].Value = _reportName;
worksheet.Cells[2, 1].Value = "Timestamp";
worksheet.Cells[2, 2].Value =
DateTime.Now.ToString(CultureInfo.InvariantCulture);
worksheet.Cells[3, 1].Value = "Report Requester";
worksheet.Cells[3, 2].Value = _userName;
worksheet.Cells["A1:B3"].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells["A1:B3"].Style.Fill.BackgroundColor.SetColor(Color.LightYellow);
worksheet.Cells["A1:A3"].Style.Font.Bold = true;
worksheet.Cells["B1:B3"].Style.Font.Italic = true;
}
}
}
