Skip to content

Instantly share code, notes, and snippets.

@NDiiong
Last active March 10, 2021 09:53
Show Gist options
  • Save NDiiong/e8f4c54128525ecb0e92f2a33f148e04 to your computer and use it in GitHub Desktop.
Save NDiiong/e8f4c54128525ecb0e92f2a33f148e04 to your computer and use it in GitHub Desktop.
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