Previously, I wrote about parsing an Excel spreadsheet to a list of objects. This is a pretty useful technique, but what do you do if you want to provide the user with an Excel template to get started? Being able to provide them with an initial template based on your object model is a good starting point.
The real goal here for me was to allow the user to be able to download a dynamically generated template and then have them be able to upload that template. The template would be parsed, as per my other post, validated, and then the data would be inserted into a database.
For the first step, though, how do we get an Excel spreadsheet that is aware of our type(T)? This is is actually pretty straight-forward using reflection. The basic flow is as follows:
- Get all properties for the type(T)
- Iterate over all properties created a header row with Property Names
- For any columns that we want constrained, add Excel validation elements
That’s really it. I know, that sounds simple, but the implementation of these three steps is where the action is. To faciliate the creation of our header rows, in terms of which properties are included, how they are named, etc, we can use custom attributes. I chose to, again, take advantage of the standard DisplayAttribute. I utilize its Order value for ordering columns, and if Name is blank, the property is excluded from Excel.
You can see in the code-snippet below that I set the Excel column format based on the .NET type. The interesting part for me is taking advantage of Excel validation. You’ll notice in the CreateXlsxTemplate signature, there is a dictionary that is passed in. Basically, this dictionary is intended to provide drop-down list values. For any property that has a defined drop-down list, then it is constrained to the values that are in the list. If the user tries to enter a value not in the list, they get an exception message from Excel. It’s kind of neat that Excel does provide a drop-down gadget and such. Implementation wise, you’ll notice that the drop-down values are put into a hidden worksheet and the column’s DataValidation is set to utilize the range that is set in the hidden worksheet.
If the user mistypes, they get a message like this in Excel:
That’s really all there is to see besides the code-snippet below. As you can imagine, the template would be parsed by the previous code I provided that parse from Excel to List<T>. I’m currently working an upload demo which will include both pieces of code and show all of this in action along with a nice Anuglar-based upload mechanism.
public static GeneratedFile CreateXlsxTemplate<T>(string templateName = "Template", string filename = "", Dictionary<string, ExcelValidation> validation = null, bool applyTimestamp = true)
{
var package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add(templateName);
var props = typeof(T).GetProperties()
.Select(prop =>
{
var displayAttribute = (DisplayAttribute)prop.GetCustomAttributes(typeof(DisplayAttribute), false).FirstOrDefault();
return new
{
Name = prop.Name,
DisplayName = displayAttribute == null ? prop.Name : displayAttribute.Name,
Order = displayAttribute == null || !displayAttribute.GetOrder().HasValue ? 999 : displayAttribute.Order,
ShortName = displayAttribute == null ? string.Empty : displayAttribute.ShortName,
PropertyInfo = prop,
PropertyType = prop.PropertyType,
HasDisplayName = displayAttribute != null
};
})
.OrderBy(prop => prop.Order)
.Where(prop => !string.IsNullOrEmpty(prop.DisplayName))
.ToList();
var rangeStr = string.Format("A{0}:{1}{2}", 1, IEnumerableExtensions.ColumnToAlpha(props.Count), ExcelPackage.MaxRows);
var range = worksheet.Cells[rangeStr];
var tableCount = worksheet.Tables.Count();
var table = worksheet.Tables.Add(range, string.Format("table{0}_{1}", tableCount, tableCount));
table.TableStyle = TableStyles.Medium16;
// Add the header
var column = 0;
var row = 1;
var ddlCol = 1;
var columnLetter = string.Empty;
foreach (var prop in props)
{
column++;
columnLetter = IEnumerableExtensions.ColumnToAlpha(column);
worksheet.Cells[string.Format("{0}{1}", columnLetter, row)].Value = prop.DisplayName;
var propTypeCode = Type.GetTypeCode(prop.PropertyType);
// Now handle data type formatting.
switch (propTypeCode)
{
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.Byte:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
{
worksheet.Column(column).Style.Numberformat.Format = "#";
break;
}
case TypeCode.Decimal:
{
worksheet.Column(column).Style.Numberformat.Format = "\"$\"#,##0.00;[Red]\"$\"#,##0.00";
break;
}
case TypeCode.DateTime:
{
worksheet.Column(column).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
break;
}
default:
{
break;
}
}
var width = 0;
int.TryParse(prop.ShortName, out width);
worksheet.Column(column).Width = width == 0 ? 15 : width;
// Create our validation - only supporting dropdown lists at the moment
if (validation != null && validation.ContainsKey(prop.Name) && validation[prop.Name].ValidationType == ExcelValidationTypes.DropdownList)
{
var columnValidationData = validation[prop.Name];
var columnRangeStr = ExcelRange.GetAddress(2, column, ExcelPackage.MaxRows, column);
var columnValidation = worksheet.DataValidations.AddListValidation(columnRangeStr);
columnValidation.ShowErrorMessage = true;
switch (columnValidationData.WarningType)
{
case ExcelValidationWarningTypes.Warning:
{
columnValidation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
columnValidation.ErrorTitle = "Warning";
columnValidation.Error = string.Format("Invalid {0} entered!", prop.DisplayName);
break;
}
case ExcelValidationWarningTypes.Stop:
default:
{
columnValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
columnValidation.ErrorTitle = "Error";
columnValidation.Error = string.Format("Invalid {0} entered!", prop.DisplayName);
break;
}
}
ExcelWorksheet ddl = package.Workbook.Worksheets["DropDownList"] ?? package.Workbook.Worksheets.Add("DropDownLists");
ddl.Hidden = OfficeOpenXml.eWorkSheetHidden.Hidden;
var ddlRow = 0;
foreach (var value in columnValidationData.Values)
{
ddlRow++;
ddl.Cells[ddlRow, 1].Value = value;
}
var formula = string.Format("=DropDownLists!${0}${1}:${0}${2}", IEnumerableExtensions.ColumnToAlpha(ddlCol), 1, ddlRow);
columnValidation.Formula.ExcelFormula = formula;
ddlCol++;
}
}
var file = new GeneratedFile()
{
FileName = applyTimestamp ?
string.Format("{0}_{1}", filename, DateTime.Now.ToString("MMddyyyyHHmmssffff")) :
string.Format("{0}", filename),
FileExtension = "xlsx",
FileMimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
FileArray = package.GetAsByteArray()
};
return file;
}
public class GeneratedFile
{
public int Id { get; set; }
public string FileName { get; set; }
public string FileExtension { get; set; }
public string FileMimeType { get; set; }
public byte[] FileArray { get; set; }
}
public class ExcelValidation
{
public string PropertyName { get; set; }
public ExcelValidationTypes ValidationType { get; set; }
public ExcelValidationWarningTypes WarningType { get; set; }
public List<string> Values { get; set; }
}