Skip to content

Instantly share code, notes, and snippets.

@long2know

long2know/IEnumerableExtensions.cs

Created May 6, 2021 18:21
Show Gist options
  • Save long2know/d3a8cf927bd8fbdfc775fb6a8b0a6827 to your computer and use it in GitHub Desktop.
Save long2know/d3a8cf927bd8fbdfc775fb6a8b0a6827 to your computer and use it in GitHub Desktop.
Download ZIP
Excel functions
Raw
IEnumerableExtensions.cs
using System;
using System.Text;
using System.Linq;
using System.Xml.Linq;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Reflection;
using OfficeOpenXml;
using OfficeOpenXml.DataValidation;
using OfficeOpenXml.Table;
namespace Core.Utilities
{
public static class IEnumerableExtensions
{
/// <summary>
/// Allows traversing, recursively through a collection recursively
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="source"></param>
/// <param name="selector"></param>
/// <returns></returns>
public static IEnumerable<T> Traverse<T>(this IEnumerable<T> source,
Func<T, IEnumerable<T>> selector)
{
foreach (T item in source)
{
yield return item;
IEnumerable<T> children = selector(item);
foreach (T child in children.Traverse(selector))
{
yield return child;
}
}
}
/// Traverses an object hierarchy and return a flattened list of elements
/// based on a predicate.
///
/// TSource: The type of object in your collection.</typeparam>
/// source: The collection of your topmost TSource objects.</param>
/// selectorFunction: A predicate for choosing the objects you want.
/// getChildrenFunction: A function that fetches the child collection from an object.
/// returns: A flattened list of objects which meet the criteria in selectorFunction.
/// source: http://stackoverflow.com/questions/141467/recursive-list-flattening
public static IEnumerable<TSource> Flatten<TSource>(
this IEnumerable<TSource> source,
Func<TSource, bool> selectorFunction,
Func<TSource, IEnumerable<TSource>> getChildrenFunction)
{
// Add what we have to the stack
var flattenedList = source.Where(selectorFunction);
// Go through the input enumerable looking for children,
// and add those if we have them
foreach (TSource element in source)
{
flattenedList = flattenedList.Concat(
getChildrenFunction(element).Flatten(selectorFunction,
getChildrenFunction)
);
}
return flattenedList;
}
/// <summary>
/// Return any IEnumerable as a HashSet
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="source"></param>
/// <returns></returns>
public static HashSet<T> ToHashSet<T>(this IEnumerable<T> source)
{
return new HashSet<T>(source);
}
/// <summary>
/// Export an IEnumerable to CSV as a string. If the property on T as a DisplayAttribute, it is used for building the header row. A blank
/// display name will exclude the property. Also, if an order is specified on the DisplayAttribute, it is honored.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="items"></param>
/// <param name="includeHeaderRow">Optionally excluded header row. Default is true.</param>
/// <param name="excludedProps">A list of string property names to exclude from the export</param>
/// <returns></returns>
public static string ToCsv<T>(this IEnumerable<T> items, bool includeHeaderRow = true, List<string> excludedProps = null)
where T : class
{
var csvBuilder = new StringBuilder();
var allProps = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(x => (excludedProps == null || !excludedProps.Contains(x.Name)) && (x.PropertyType == typeof(string) || x.PropertyType == typeof(decimal) ||
(!x.PropertyType.GetInterfaces().Contains(typeof(IEnumerable)) &&
(AuditAndMappingExtensions.IsPrimitiveType(x.PropertyType) || AuditAndMappingExtensions.IsNullablePrimitive(x.PropertyType) || AuditAndMappingExtensions.IsNullableEnum(x.PropertyType)))))
.ToList();
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();
// Create the header row
if (includeHeaderRow)
{
var columns = props.Select(x => x.DisplayName.ToCsvValue()).ToList();
csvBuilder.AppendLine(string.Join(",", columns));
}
foreach (T item in items)
{
string line = string.Join(",", props.Select(p => p.PropertyInfo.GetValue(item, null).ToCsvValue()).ToArray());
csvBuilder.AppendLine(line);
}
return csvBuilder.ToString();
}
private static string ToCsvValue<T>(this T item)
{
if (item == null) return "\"\"";
if (item is string)
{
return string.Format("\"{0}\"", item.ToString().Replace("\"", "\\\""));
}
//double dummy;
//if (double.TryParse(item.ToString(), out dummy))
//{
// return string.Format("{0}", item);
//}
return string.Format("\"{0}\"", item);
}
public static XDocument ToExcelXml<T>(this IEnumerable<T> rows)
{
return rows.ToExcelXml("Sheet1");
}
public static XDocument ToExcelXml<T>(this IEnumerable<T> rows, string sheetName)
{
List<string> excludedProps = null;
sheetName = sheetName.Replace("/", "-");
sheetName = sheetName.Replace("\\", "-");
var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(x => (excludedProps == null || !excludedProps.Contains(x.Name)) && (x.PropertyType == typeof(string) || x.PropertyType == typeof(decimal) ||
(!x.PropertyType.GetInterfaces().Contains(typeof(IEnumerable)) &&
(AuditAndMappingExtensions.IsPrimitiveType(x.PropertyType) || AuditAndMappingExtensions.IsNullablePrimitive(x.PropertyType) || AuditAndMappingExtensions.IsNullableEnum(x.PropertyType)))))
.ToList();
XNamespace mainNamespace = "urn:schemas-microsoft-com:office:spreadsheet";
XNamespace o = "urn:schemas-microsoft-com:office:office";
XNamespace xls = "urn:schemas-microsoft-com:office:excel";
XNamespace ss = "urn:schemas-microsoft-com:office:spreadsheet";
XNamespace html = "http://www.w3.org/TR/REC-html40";
XDocument xdoc = new XDocument(new XDeclaration("1.0", "utf-8", "yes"));
var headerRow = from p in props
select new XElement(mainNamespace + "Cell",
new XElement(mainNamespace + "Data",
new XAttribute(ss + "Type", "String"), p.Name)); //Generate header using reflection
XElement workbook = new XElement(mainNamespace + "Workbook",
new XAttribute(XNamespace.Xmlns + "html", html),
new XAttribute(XName.Get("ss", "http://www.w3.org/2000/xmlns/"), ss),
new XAttribute(XName.Get("o", "http://www.w3.org/2000/xmlns/"), o),
new XAttribute(XName.Get("x", "http://www.w3.org/2000/xmlns/"), xls),
new XAttribute(XName.Get("xmlns", ""), mainNamespace),
new XElement(o + "DocumentProperties",
new XAttribute(XName.Get("xmlns", ""), o),
new XElement(o + "Author", "Smartdesk Systems Ltd"),
new XElement(o + "LastAuthor", "Smartdesk Systems Ltd"),
new XElement(o + "Created", DateTime.Now.ToString())
), //end document properties
new XElement(xls + "ExcelWorkbook",
new XAttribute(XName.Get("xmlns", ""), xls),
new XElement(xls + "WindowHeight", 12750),
new XElement(xls + "WindowWidth", 24855),
new XElement(xls + "WindowTopX", 240),
new XElement(xls + "WindowTopY", 75),
new XElement(xls + "ProtectStructure", "False"),
new XElement(xls + "ProtectWindows", "False")
), //end ExcelWorkbook
new XElement(mainNamespace + "Styles",
new XElement(mainNamespace + "Style",
new XAttribute(ss + "ID", "Default"),
new XAttribute(ss + "Name", "Normal"),
new XElement(mainNamespace + "Alignment",
new XAttribute(ss + "Vertical", "Bottom")
),
new XElement(mainNamespace + "Borders"),
new XElement(mainNamespace + "Font",
new XAttribute(ss + "FontName", "Calibri"),
new XAttribute(xls + "Family", "Swiss"),
new XAttribute(ss + "Size", "11"),
new XAttribute(ss + "Color", "#000000")
),
new XElement(mainNamespace + "Interior"),
new XElement(mainNamespace + "NumberFormat"),
new XElement(mainNamespace + "Protection")
),
new XElement(mainNamespace + "Style",
new XAttribute(ss + "ID", "Header"),
new XElement(mainNamespace + "Font",
new XAttribute(ss + "FontName", "Calibri"),
new XAttribute(xls + "Family", "Swiss"),
new XAttribute(ss + "Size", "11"),
new XAttribute(ss + "Color", "#000000"),
new XAttribute(ss + "Bold", "1")
)
)
), // close styles
new XElement(mainNamespace + "Worksheet",
new XAttribute(ss + "Name", sheetName /* Sheet name */),
new XElement(mainNamespace + "Table",
new XAttribute(ss + "ExpandedColumnCount", headerRow.Count()),
new XAttribute(ss + "ExpandedRowCount", rows.Count() + 1),
new XAttribute(xls + "FullColumns", 1),
new XAttribute(xls + "FullRows", 1),
new XAttribute(ss + "DefaultRowHeight", 15),
new XElement(mainNamespace + "Column",
new XAttribute(ss + "Width", 81)
),
new XElement(mainNamespace + "Row", new XAttribute(ss + "StyleID", "Header"), headerRow),
from contentRow in rows
select new XElement(mainNamespace + "Row",
new XAttribute(ss + "StyleID", "Default"),
from p in contentRow.GetType().GetProperties()
select new XElement(mainNamespace + "Cell",
new XElement(mainNamespace + "Data", new XAttribute(ss + "Type", "String"), p.GetValue(contentRow, null))) /* Build cells using reflection */ )
), //close table
new XElement(xls + "WorksheetOptions",
new XAttribute(XName.Get("xmlns", ""), xls),
new XElement(xls + "PageSetup",
new XElement(xls + "Header",
new XAttribute(xls + "Margin", "0.3")
),
new XElement(xls + "Footer",
new XAttribute(xls + "Margin", "0.3")
),
new XElement(xls + "PageMargins",
new XAttribute(xls + "Bottom", "0.75"),
new XAttribute(xls + "Left", "0.7"),
new XAttribute(xls + "Right", "0.7"),
new XAttribute(xls + "Top", "0.75")
)
),
new XElement(xls + "Print",
new XElement(xls + "ValidPrinterInfo"),
new XElement(xls + "HorizontalResolution", 600),
new XElement(xls + "VerticalResolution", 600)
),
new XElement(xls + "Selected"),
new XElement(xls + "Panes",
new XElement(xls + "Pane",
new XElement(xls + "Number", 3),
new XElement(xls + "ActiveRow", 1),
new XElement(xls + "ActiveCol", 0)
)
),
new XElement(xls + "ProtectObjects", "False"),
new XElement(xls + "ProtectScenarios", "False")
) // close worksheet options
) // close Worksheet
);
xdoc.Add(workbook);
return xdoc;
}
#region EPPlus Xlsx generation
private static string _alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
/// <summary>
/// Add an Excel worksheet to a new or existing ExcelPackage
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="worksheetName"></param>
/// <param name="package"></param>
/// <param name="startRow"></param>
/// <returns></returns>
public static ExcelPackage AddWorksheet<T>(this IEnumerable<T> list, string worksheetName = "Export", ExcelPackage package = null, int startRow = 1, Dictionary<string, ExcelValidation> validation = null, bool applyTimestamp = true)
{
List<string> excludedProps = null;
if (package == null)
{
package = new ExcelPackage();
}
var type = typeof(T);
if (type == typeof(object))
{
type = list.GetType().GetGenericArguments()[0];
}
var worksheet = package.Workbook.Worksheets.Where(x => x.Name == worksheetName).FirstOrDefault() ?? package.Workbook.Worksheets.Add(worksheetName);
var props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(x => (excludedProps == null || !excludedProps.Contains(x.Name)) && (x.PropertyType == typeof(string) || x.PropertyType == typeof(decimal) ||
(!x.PropertyType.GetInterfaces().Contains(typeof(IEnumerable)) &&
(AuditAndMappingExtensions.IsPrimitiveType(x.PropertyType) || AuditAndMappingExtensions.IsNullablePrimitive(x.PropertyType) || AuditAndMappingExtensions.IsNullableEnum(x.PropertyType)))))
.Select(prop =>
{
var displayAttribute = (DisplayAttribute)prop.GetCustomAttributes(typeof(DisplayAttribute), false).FirstOrDefault();
return new
{
Name = prop.Name,
DisplayName = displayAttribute?.Name ?? prop.Name,
Order = displayAttribute == null || !displayAttribute.GetOrder().HasValue ? 999 : displayAttribute.Order,
ShortName = displayAttribute?.ShortName ?? string.Empty,
PropertyInfo = prop,
PropertyType = prop.PropertyType,
HasDisplayName = displayAttribute != null,
NumberFormat = displayAttribute?.GroupName ?? string.Empty
};
})
.OrderBy(prop => prop.Order)
.Where(prop => !string.IsNullOrEmpty(prop.DisplayName))
.ToList();
// Add the headings
var column = 0;
var row = startRow;
var ddlCol = 1;
var columnLetter = string.Empty;
foreach (var prop in props)
{
column++;
columnLetter = ColumnToAlpha(column);
worksheet.Cells[$"{columnLetter}{row}"].Value = prop.DisplayName;
var propTypeCode = Type.GetTypeCode(prop.PropertyType);
// Now handle data type formatting.
if (!string.IsNullOrWhiteSpace(prop.NumberFormat))
{
worksheet.Column(column).Style.Numberformat.Format = prop.NumberFormat;
}
else
{
if (propTypeCode == TypeCode.Object && AuditAndMappingExtensions.IsNullablePrimitive(prop.PropertyType))
{
propTypeCode = Type.GetTypeCode(Nullable.GetUnderlyingType(prop.PropertyType));
}
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;
}
}
}
// 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 = $"Invalid {prop.DisplayName} entered!";
break;
}
case ExcelValidationWarningTypes.Stop:
default:
{
columnValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
columnValidation.ErrorTitle = "Error";
columnValidation.Error = $"Invalid {prop.DisplayName} entered!";
break;
}
}
ExcelWorksheet ddl = package.Workbook.Worksheets["DropDownLists"] ?? package.Workbook.Worksheets.Add("DropDownLists");
ddl.Hidden = OfficeOpenXml.eWorkSheetHidden.Hidden;
var ddlRow = 0;
foreach (var value in columnValidationData.Values)
{
ddlRow++;
ddl.Cells[ddlRow, ddlCol].Value = value;
}
var formula = string.Format("=DropDownLists!${0}${1}:${0}${2}", IEnumerableExtensions.ColumnToAlpha(ddlCol), 1, ddlRow);
columnValidation.Formula.ExcelFormula = formula;
ddlCol++;
}
}
foreach (var item in list)
{
row++;
column = 0;
foreach (var prop in props)
{
column++;
columnLetter = ColumnToAlpha(column);
worksheet.Cells[$"{columnLetter}{row}"].Value = prop.PropertyInfo.GetValue(item, null) ?? string.Empty;
}
}
var rangeStr = $"A{startRow}:{ColumnToAlpha(column)}{list.Count() + startRow}";
using (var range = worksheet.Cells[rangeStr])
{
range.AutoFitColumns();
var tableCount = worksheet.Tables.Count();
var table = worksheet.Tables.Add(range, $"table{tableCount}_{worksheetName}");
table.TableStyle = TableStyles.Medium16;
}
return package;
}
/// <summary>
/// Helper method that will take a dictionary of IEnumerables with each IEnumerable going into a worksheet with the same name as the dictionary key
/// </summary>
/// <param name="dict"></param>
/// <returns></returns>
public static byte[] ToXslx(this Dictionary<string, IEnumerable<object>> dict)
{
ExcelPackage package = null;
foreach (var key in dict.Keys)
{
var list = dict[key];
package = list.AddWorksheet(key, package);
}
return package.GetAsByteArray();
}
/// <summary>
/// Export a list of Lists to a single worksheet
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="lists"></param>
/// <param name="worksheetName"></param>
/// <returns></returns>
public static byte[] ToXslx<T>(this List<List<T>> lists, string worksheetName = "Export")
{
ExcelPackage package = null;
var startRow = 1;
foreach (var list in lists)
{
package = list.AddWorksheet(worksheetName, package, startRow);
var workSheet = package.Workbook.Worksheets.Where(x => x.Name == worksheetName).FirstOrDefault();
startRow = workSheet.Dimension.End.Row + 2;
}
return package.GetAsByteArray();
}
/// <summary>
/// Export an IEnumerable to XLSX as a byte array
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="worksheetName"></param>
/// <returns></returns>
public static byte[] ToXslx<T>(this IEnumerable<T> list, string worksheetName = "Export", Dictionary<string, ExcelValidation> validation = null, bool applyTimestamp = true)
{
var package = list.AddWorksheet(worksheetName, null, 1, validation, applyTimestamp);
return package.GetAsByteArray();
}
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?.Name ?? prop.Name,
Order = displayAttribute == null || !displayAttribute.GetOrder().HasValue ? 999 : displayAttribute.Order,
ShortName = displayAttribute?.ShortName ?? string.Empty,
PropertyInfo = prop,
PropertyType = prop.PropertyType,
HasDisplayName = displayAttribute != null,
NumberFormat = displayAttribute?.GroupName ?? string.Empty
};
})
.OrderBy(prop => prop.Order)
.Where(prop => !string.IsNullOrEmpty(prop.DisplayName))
.ToList();
//// Create our validation
//var category = "ManualLine";
//var lineTypes = _lineTypeRepo.Get(x => x.Category == category && x.IsActive).OrderBy(x => x.Description).ToList();
//var commTypeProp = props.FirstOrDefault(x => x.Name == StaticHelpers.GetPropertyName<ManualItem>(y => y.CommType));
//var rangeStr = ExcelRange.GetAddress(2, commTypeProp.Order, ExcelPackage.MaxRows, commTypeProp.Order);
//var columnValidation = worksheet.DataValidations.AddListValidation(rangeStr);
//columnValidation.ShowErrorMessage = true;
//columnValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
//columnValidation.ErrorTitle = "Error";
//columnValidation.Error = string.Format("Invalid {0} entered!", commTypeProp.DisplayName);
////foreach (var lineType in lineTypes)
////{
//// validation.Formula.Values.Add(lineType.Description);
////}
//// Add worksheet for dropdowns - we cant use formua values because our descriptions contain commas
//ExcelWorksheet ddl = package.Workbook.Worksheets.Add("DropDownLists");
//ddl.Hidden = OfficeOpenXml.eWorkSheetHidden.Hidden;
//var ddlRow = 0;
//foreach (var lineType in lineTypes)
//{
// ddlRow++;
// ddl.Cells[ddlRow, 1].Value = lineType.Description;
//}
//var formula = string.Format("=DropDownLists!${0}${1}:${0}${2}", IEnumerableExtensions.ColumnToAlpha(1), 1, ddlRow);
//columnValidation.Formula.ExcelFormula = formula;
// sheet with a name : DropDownLists
// from DropDownLists sheet, get values from cells: !$A$1:$A$10
//var formula = "=DropDownLists!$A$1:$A$10"validation.Formula.ExcelFormula = formula;
var rangeStr = $"A{1}:{IEnumerableExtensions.ColumnToAlpha(props.Count)}{ExcelPackage.MaxRows}";
var range = worksheet.Cells[rangeStr];
//worksheet.Cells[range].AutoFitColumns();
var tableCount = worksheet.Tables.Count();
var table = worksheet.Tables.Add(range, $"table{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 = ColumnToAlpha(column);
worksheet.Cells[$"{columnLetter}{row}"].Value = prop.DisplayName;
var propTypeCode = Type.GetTypeCode(prop.PropertyType);
// Now handle data type formatting.
if (!string.IsNullOrWhiteSpace(prop.NumberFormat))
{
worksheet.Column(column).Style.Numberformat.Format = prop.NumberFormat;
}
else
{
if (propTypeCode == TypeCode.Object && AuditAndMappingExtensions.IsNullablePrimitive(prop.PropertyType))
{
propTypeCode = Type.GetTypeCode(Nullable.GetUnderlyingType(prop.PropertyType));
}
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["DropDownLists"] ?? package.Workbook.Worksheets.Add("DropDownLists");
ddl.Hidden = OfficeOpenXml.eWorkSheetHidden.Hidden;
var ddlRow = 0;
foreach (var value in columnValidationData.Values)
{
ddlRow++;
ddl.Cells[ddlRow, ddlCol].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;
}
/// <summary>
/// Covert a column number to the expected alpha-character representation
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static string ColumnToAlpha(int column)
{
if (column <= _alphabet.Length)
{
return _alphabet[column - 1].ToString();
}
var number = column;
string letter = string.Empty;
while (number > 0)
{
var remainder = (number - 1) % _alphabet.Length;
letter = string.Format("{0}{1}", _alphabet[remainder], letter);
number = (number - remainder) / _alphabet.Length;
}
return letter;
}
#endregion
}
public enum ExcelValidationTypes
{
Minimum,
Maximum,
DropdownList
}
public enum ExcelValidationWarningTypes
{
Warning,
Stop
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment