I have a working ExcelPackage function in an MVC 5 application where I can successfully output a strongly-typed model to a new Excel file.
I have a case where a specific column is nullable DateTime with four possible values:
- null
- date only
- time only
- date and time
I have extracted a function that does the job of identifying any DateTime values in the Worksheet. Although the DateTime values are consistently identified and formatted correctly, I can't help but think there might be a better way to achieve the same outcome than a nested loop.
Because this function (and the containing class) are designed to accept any IEnumerable, I cannot predict which column -- or even if -- the IEnumerable will contain DateTime values. For that reason, I cannot hard code for a known range of cells.
To provide better context, the class constructor is pasted here, followed by the function that I am trying to make better. Even if you take away the comments, it's still a pretty ugly set of nested code.
In summary, my question is this: am I missing a more elegant or simpler coding approach in terms of the C# language or the Nuget Epplus package?
public class EpplusExcelPackage<T>
{
private IEnumerable<T> _data;
private string _reportName;
public EpplusExcelPackage(IEnumerable<T> Data, string ReportName)
{
this._data = Data;
this._reportName = ReportName;
}
// much more code...
This is the method I hope to make more efficient:
private static void FormatDateTimeValuesInWorksheet(ExcelWorksheet worksheet)
{
/* correctly format datetime values as:
* if date only, format as shortdate
* if time only, format as am/pm time
* if date & time present, format as default datetime */
// the worksheet is data is a strongly-typed model, populated in the model constructor
System.DateTime dateValue; // used as the out variable of DateTime.TryParse()
// nested for-loop to find datetime values in worksheet
for (int i = worksheet.Dimension.Start.Column; i < worksheet.Dimension.End.Column; i++)
{
for (int j = worksheet.Dimension.Start.Row; j < worksheet.Dimension.End.Row; j++)
{
// ignore null cell values to prevent null exception error
if (worksheet.Cells[i, j].Value != null)
{
// convert the cell value to string: required by TryParse()
string cellValue = worksheet.Cells[i, j].Value.ToString();
// identify type of datetime and format accordingly
if (DateTime.TryParse(cellValue, out dateValue))
{
if (dateValue.Date == Convert.ToDateTime("12/30/1899"))
{
worksheet.Cells[i, j].Value = dateValue.ToShortTimeString();
}
else if (dateValue.TimeOfDay.TotalSeconds == 0)
{
worksheet.Cells[i, j].Value = dateValue.ToShortDateString();
}
else // do not change
{
worksheet.Cells[i, j].Value = worksheet.Cells[i, j].Value;
}
}
}
}
}
}
