Created
February 11, 2018 00:09
-
-
Save oguzhankircali/3a192d6b5b1cdd265f658cadf7f734af to your computer and use it in GitHub Desktop.
Excel Export EP Plus Class C#
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
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; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment