Excel Export EP Plus Class C#
public static class ExcelExport
{
public static void EpplusExportToExcel(List<SObject> sobjectList, string fileName, bool isAddDateTimeExt = false)
{
fileName = GetFileName(fileName, isAddDateTimeExt);
List<string[]> arrayList = new List<string[]>();
if (sobjectList != null && sobjectList.Count > 0)
{
//PropertyName leri alınıyor.
var item = sobjectList.FirstOrDefault();
{
IList<SProperty> props = item.Properties;
string[] pName = new string[props.Count];
int index = 0;
foreach (var p in props)
{
pName[index] = p.Name;
index++;
}
arrayList.Add(pName);
}
//Value lar alınıyor.
foreach (SObject obj in sobjectList)
{
IList<SProperty> props = obj.Properties;
string[] array = new string[props.Count];
int index = 0;
foreach (SProperty prop in props)
{
array[index++] = prop.Value;
}
arrayList.Add(array);
}
}
using (var excelFile = new ExcelPackage())
{
ExcelWorksheet sheet = excelFile.Workbook.Worksheets.Add("Sayfa1");
if (sobjectList.Count() > 0)
{
sheet.Cells["A1"].LoadFromArrays(arrayList);
}
Byte[] fileBytes = excelFile.GetAsByteArray();
//Download ediyor.
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.Private);
HttpContext.Current.Response.Expires = -1;
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
HttpContext.Current.Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content - disposition", "attachment; size =" + fileBytes.Length.ToString() + "; filename =\"" + fileName + "\"");
HttpContext.Current.Response.BinaryWrite(fileBytes);
HttpContext.Current.Response.End();
}
}
public static void EpplusExportToExcelFromObjectList(List<object> myList, string fileName, bool isAddDateTimeExt = false)
{
fileName = GetFileName(fileName, isAddDateTimeExt);
List<string[]> arrayList = new List<string[]>();
if (myList != null)
{
//PropertyName leri alınıyor.
for (int i = 0; i < 1; i++)
{
var item = myList.FirstOrDefault();
{
Type itemType = item.GetType();
IList<PropertyInfo> props = itemType.GetProperties();
props = props.Where(m => m.GetGetMethod().IsVirtual == false).ToList();
string[] pName = new string[props.Count];
int index = 0;
foreach (var p in props)
{
var pCustAttribute = p.GetCustomAttributes(typeof(ExportAttribute), false);
if (pCustAttribute != null && pCustAttribute.Length > 0)
{
if (!((ExportAttribute)pCustAttribute[0]).IsVisible)
continue;
pName[index++] = ((ExportAttribute)pCustAttribute[0]).DisplayName;
}
else
pName[index++] = p.Name;
}
arrayList.Add(pName);
}
}
//Value lar alınıyor.
foreach (var item in myList)
{
Type itemType = item.GetType();
IList<PropertyInfo> props = itemType.GetProperties();
props = props.Where(m => m.GetGetMethod().IsVirtual == false).ToList();
string[] array = new string[props.Count];
int index = 0;
foreach (PropertyInfo prop in props)
{
var pCustAttribute = prop.GetCustomAttributes(typeof(ExportAttribute), false);
if (pCustAttribute != null && pCustAttribute.Length > 0)
{
if (!((ExportAttribute)pCustAttribute[0]).IsVisible)
continue;
}
array[index++] = prop.GetValue(item, null) != null ? prop.GetValue(item, null).ToString() : "";
}
arrayList.Add(array);
}
}
using (var excelFile = new ExcelPackage())
{
ExcelWorksheet sheet = excelFile.Workbook.Worksheets.Add("Sayfa1");
if (myList.Count() > 0)
{
sheet.Cells["A1"].LoadFromArrays(arrayList);
}
Byte[] fileBytes = excelFile.GetAsByteArray();
//Download ediyor.
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.Private);
HttpContext.Current.Response.Expires = -1;
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
HttpContext.Current.Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content - disposition", "attachment; size =" + fileBytes.Length.ToString() + "; filename =\"" + fileName + "\"");
HttpContext.Current.Response.BinaryWrite(fileBytes);
HttpContext.Current.Response.End();
}
}
public static void EpplusExportToExcel<T>(List<T> myList, string fileName, bool isAddDateTimeExt = false)
{
fileName = GetFileName(fileName, isAddDateTimeExt);
using (var excelFile = new ExcelPackage())
{
ExcelWorksheet sheet = excelFile.Workbook.Worksheets.Add("Result");
var t = typeof(T);
var Headings = t.GetProperties();
for (int i = 0; i < Headings.Count(); i++)
{
sheet.Cells[1, i + 1].Value = Headings[i].Name;
}
if (myList.Count() > 0)
{
sheet.Cells["A2"].LoadFromCollection(myList.ToArray());
}
Byte[] fileBytes = excelFile.GetAsByteArray();
//Download ediyor.
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.Private);
HttpContext.Current.Response.Expires = -1;
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
HttpContext.Current.Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content - disposition", "attachment; size =" + fileBytes.Length.ToString() + "; filename =\"" + fileName + "\"");
HttpContext.Current.Response.BinaryWrite(fileBytes);
HttpContext.Current.Response.End();
}
}
/// <summary>
/// Grid olarak kaydeder. List<object> de alabilir. Uzantısı xls veya html verilebilir.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="fileName"></param>
/// <param name="isAdddateTimeExt"></param>
public static void ExportToExcel<T>(List<T> list, string fileName, bool isAddDateTimeExt = false)
{
fileName = GetFileName(fileName, isAddDateTimeExt);
var context = HttpContext.Current;
var attachment = "attachment; filename =" + fileName;
context.Response.Clear();
context.Response.AddHeader("content - disposition", attachment);
context.Response.AddHeader("Pragma", "public");
context.Response.AddHeader("Cache-Control", "max-age=0");
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.ContentEncoding = Encoding.GetEncoding("windows-1254");
context.Response.Charset = "windows-1254";
const string headerStart = "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\n" +
"<html xmlns =\"http://www.w3.org/1999/xhtml\">" +
"\n<head>\n" +
"<title></title>\n" +
"<meta http-equiv=\"Content-Type\" content=\"text/html; charset=windows-1254\" />\n" +
"<style>\n</style>\n" +
"</head>\n" +
"<body>\n";
const string headerEnd = "\n</body>\n" +
"</html>";
var gridView = new System.Web.UI.WebControls.GridView
{
AutoGenerateColumns = true,
AllowSorting = false,
AllowPaging = false
};
using (var sw = new StringWriter())
{
using (var htw = new HtmlTextWriter(sw))
{
gridView.DataSource = list;
gridView.DataBind();
gridView.RenderControl(htw);
}
context.Response.Write(headerStart + sw + headerEnd);
context.Response.Flush();
}
}
private static string GetFileName(string fileName, bool isAddDateTimeExt)
{
string[] file = fileName.Split(‘.’);
if (file.Count() == 1)
{
Array.Resize(ref file, 2);
file[1] = "xls";
fileName = file[0] + "." + file[1];
}
if (isAddDateTimeExt)
fileName = file[0] + "_" + DateTime.Now.ToString().Replace(".", "").Replace(" ", "").Replace(":", "") + "." + file[1];
return fileName;
}
}