private void GenerateSheet(Excel.Worksheet sheet, string sheetName)
{
sheet.Name = sheetName;
sheet.Cells.Locked = false;
var columns = definition.Worksheets[EnumUtils.EnumValue <ConfigurationFile.WorkSheets>(sheetName)].Columns;
foreach (var column in columns)
{
if (EnumUtils.IsOptional(column.TargetField))
{
sheet.Cells[1, column.Position] = "(Optional)";
sheet.Columns[column.Position].Style = "Optional";
}
else
{
sheet.Cells[1, column.Position] = "(Required)";
}
sheet.Cells[1, column.Position].Style = "HeadingDetail";
sheet.Cells[2, column.Position] = column.Heading;
sheet.Cells[2, column.Position].Style = "HeadingTitle";
var defaultValue = EnumUtils.GetDefault(column.TargetField);
if (defaultValue != null)
{
sheet.Cells[3, column.Position] = $"(Default: {defaultValue.ToString()})";
}
sheet.Cells[3, column.Position].Style = "HeadingDetail";
var options = EnumUtils.GetOptions(column.TargetField);
if (options.Count() > 0)
{
var cell = (Excel.Range)sheet.Columns[column.Position];
cell.Validation.Add(
Excel.XlDVType.xlValidateList,
Excel.XlDVAlertStyle.xlValidAlertStop,
Excel.XlFormatConditionOperator.xlBetween,
string.Join(", ", options));
}
}
sheet.Columns.AutoFit();
sheet.Protect(Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, true,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value);
// Freeze the header rows.
sheet.Activate();
sheet.Application.ActiveWindow.SplitRow = 3;
sheet.Application.ActiveWindow.FreezePanes = true;
}
