Ensuring specific columns in an ExcelWorksheet format as ShortDate

Question

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:

1. In a Controller ActionResult, create a new instance of a custom class that accepts the IEnumerable to be exported along with other string parameters.

   var exportIenumerable = new EpplusExcelPackage<ModelToExport>(data.AsEnumerable(), 
    /* string params for Excel heading */);
   byte[] export = exportIenumerable.ExportToExcel();
   

2. Use the EpplusExcelPackage custom class to -- along with creating the new Excel document, identify any DateTime fields 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;
 }
 }
}

Answer 1

Readability

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

Because you ommited the {} for each if it super hard to understand your code not mentioning is very error prone.

You should always use {}.

This for example is terrible:

private int GetRowCount()
{
 var count = 0;
 IEnumerator<T> enumerator;
 if (_dataToExport == null) return count;
 using (enumerator = _dataToExport.GetEnumerator())
 while (enumerator.MoveNext())
 count++;
 return count;
}

---

for variable

We usually start with the i. When I see a j I immediately think it's the second nested loop just to discover it's the only loop. You can also use more meaningful names like row, x or column but the default first index is normally an i.

---

DRY

Don't Repeat Yourself.

In the above method you do the same thing twice. Try to merge the conditions and give them a meaningful name rather then putting a long expression into an if:

private void FormatDateTimeAsDate(ExcelWorksheet worksheet)
{
 for (var i = 0; i < _modelProperties.Length; i++)
 {
 var canSetFormat = 
 DateTimeFormatInfo.CurrentInfo != null &&
 (_modelProperties[i].PropertyType == _typeNullDatetime || _modelProperties[i].PropertyType == _typeDateTime);
 if (canSetFormat) 
 {
 worksheet.Column(i + ColumnStart).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
 } 
 }
}

(I hope I got the condition right)

By the way, I doubt this DateTimeFormatInfo.CurrentInfo is ever null so no need to check this.

---

ctor

public EpplusExcelPackage(IEnumerable<T> dataToExport, string reportName, string userName)
{
 _dataToExport = dataToExport;
 _reportName = reportName;
 _userName = userName;
 _rowCount = GetRowCount();
}

If you checked the parameters here and prevented nulls, you'd save a lot of work later where check them for nulls anyway.

---

IEnumerable

private int GetRowCount()
{
 var count = 0;
 IEnumerator<T> enumerator;
 if (_dataToExport == null) return count;
 using (enumerator = _dataToExport.GetEnumerator())
 while (enumerator.MoveNext())
 count++;
 return count;
}

You shouldn't check the _dataToExport for null. You should prevent it to be a null at all and check the parameter passed to the constructor.

If you however still want to do it then consider this:

private int GetRowCount()
{
 return _dataToExport == null ? -1 : _dataToExport.Count(); 
}

There is convention that collections/ienumerables shouldn't be null but rather emtpy.

-1 usually indicates there is something not right like when the collection is null in this case. saying the number of items is 0 let me thing the collection exists. Compare to String.IndexOf.

• user86489: Good comments. Some of my code was because ReSharper recommended it (like removing some of the {} blocks).
• t3chb0t: @Rubix_Revenge this is strange. I use ReSharper too but it never suggested me to remove the {} :-| but at least there is an explanations for this ;-)
• user86489: On another note, the reason I have the GetRowCount() block is because the _dataToExport IEnumerable has no available Count method. I tried that first.
• t3chb0t: @Rubix_Revenge this is true, it doesn't have one - that's why I used the linq's Count() extension there.
• user86489: The suggestions helped, but one underlying question I still have is whether there is a better approach than the one used by FormatDateTimeAsDate(), in the sense of having to loop through the Property Types to identify datetimes and set the ExcelWorksheet column format accordingly..