ExcelPackageExtensions.cs

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;


namespace File.Excel.Extensions
{
    public static class ExcelPackageExtensions
    {
        public static void ToExcel<T>(this IEnumerable<T> source, string filePath) where T : class, new()
        {
            var worksheetAttribute = typeof(T).GetCustomAttribute(typeof(ExcelWorksheetAttribute), true);
            var workSheetName = (worksheetAttribute as ExcelWorksheetAttribute)?.WorksheetName ?? RandomSheetName(10);
            source.ToExcel(filePath, workSheetName);
        }

        public static void ToExcel<T>(this IEnumerable<T> source, string filePath, string sheetName) where T : class, new()
        {
            var file = new FileInfo(filePath);
            if (file.Exists)
            {
                file.Delete();
                file = new FileInfo(filePath);
            }
            using (var package = new ExcelPackage(file))
            {
                package.Workbook.Worksheets.Add(sheetName).FromList<T>(source);
                package.Save();
            }
        }

        public static byte[] ToExcelBytes<T>(this IEnumerable<T> source) where T : class, new()
        {
            var worksheetAttribute = typeof(T).GetCustomAttribute(typeof(ExcelWorksheetAttribute), true);
            var workSheetName = (worksheetAttribute as ExcelWorksheetAttribute)?.WorksheetName ?? RandomSheetName(10);

            return source.ToExcelBytes(workSheetName);
        }

        public static byte[] ToExcelBytes<T>(this IEnumerable<T> source, string sheetName) where T : class, new()
        {
            using (var ms = new MemoryStream())
            {
                var package = new ExcelPackage(ms);

                package.Workbook.Worksheets.Add(sheetName).FromList(source);
                package.Save();
                return ms.ToArray();
            }
        }

        private static void CreateContentRow<T>(this ExcelWorksheet sheet, T item, int index) where T : class, new()
        {
            var type = typeof(T);
            var properties = type.GetProperties();
            List<ExcelColumnAttribute> columns = new();
            foreach (var property in properties)
            {
                var attributes = property.GetCustomAttributes(typeof(ExcelColumnAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[attributes.Length - 1];
                    if (attr is ExcelColumnAttribute)
                    {
                        var col = attr as ExcelColumnAttribute;

                        if (col.Ignore) continue;

                        var value = property.GetValue(item);
                        col.SetValue(value);
                        columns.Add(col);
                    }
                }
            }
            sheet.InsertRow(index, 1);
            var values = columns.OrderBy(p => p.Index).Select(p => p.Value);
            var colIndex = 1;
            foreach (var title in values)
            {
                sheet.SetValue(index, colIndex, title);
                colIndex++;
            }
            sheet.Cells.AutoFitColumns();
        }

        private static void CreateTitleRow<T>(this ExcelWorksheet sheet) where T : class, new()
        {
            var rowIndex = 1;
            var type = typeof(T);
            var properties = type.GetProperties();
            var columns = new List<ExcelColumnAttribute>();
            foreach (var property in properties)
            {
                var attributes = property.GetCustomAttributes(typeof(ExcelColumnAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[attributes.Length - 1];
                    if (attr is ExcelColumnAttribute)
                    {
                        var col = attr as ExcelColumnAttribute;
                        if (col.Ignore)
                        {
                            continue;
                        }
                        columns.Add(col);
                    }
                }
            }
            sheet.InsertRow(rowIndex, 1);
            var titles = columns.OrderBy(p => p.Index).Select(p => p.Title);
            var index = 1;
            foreach (var title in titles)
            {
                sheet.SetValue(rowIndex, index, title);
                index++;
            }
            sheet.Cells.AutoFitColumns();
        }

        private static ExcelWorksheet FromList<T>(this ExcelWorksheet sheet, IEnumerable<T> source) where T : class, new()
        {
            sheet.CreateTitleRow<T>();
            int index = 2;
            foreach (var item in source)
            {
                sheet.CreateContentRow(item, index);
                index++;
            }
            return sheet;
        }

        private static string RandomSheetName(int length)
        {
            const string pool = "abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
            var random = new Random();
            var chars = Enumerable.Range(0, length)
                .Select(_ => pool[random.Next(0, pool.Length)]);

            return new string(chars.ToArray());
        }

        private static void SetValue(this ExcelColumnAttribute column, object value)
        {
            if (value is null or null)
            {
                value = column.DefaultValue;
            }
            if (value is string && string.IsNullOrWhiteSpace(value.ToString()) && column.DefaultValue != null)
            {
                column.Value = column.DefaultValue.ToString();
                return;
            }
            if (value is DateTime)
            {
                column.Value = ((DateTime)value).ToString(column.DateFormat, column.FormatProvider);
                return;
            }
            if (value is DateTime?)
            {
                var v = value as DateTime?;
                if (v.HasValue)
                {
                    column.Value = v.Value.ToString(column.DateFormat, column.FormatProvider);
                    return;
                }

                column.Value = string.Empty;
                return;
            }
            column.Value = value.ToString();
        }
    }
}