Last active
March 10, 2021 09:53
-
-
Save NDiiong/e8f4c54128525ecb0e92f2a33f148e04 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment