using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ClosedXML.Excel;
using System.IO;
using System.Data;
using System.Web;
using System.Reflection;
using System.ComponentModel;
namespace VML.Blacklist.Web.Common.Utils
{
public class ExcelHelper
{
public ExcelHelper()
{ }
private DataTable dataTable = new DataTable();
private StringBuilder builder = new StringBuilder();
public PropertyInfo[] GetPropertyInfoArray(Type type)
{
PropertyInfo[] props = null;
try
{
object obj = Activator.CreateInstance(type);
//props = (from r in type.GetProperties(BindingFlags.Public | BindingFlags.Instance)
// where r.GetCustomAttribute(typeof(DisplayNameAttribute)) != null
// select r).ToArray();
props = type.GetProperties(BindingFlags.Instance | BindingFlags.Public)
.Select(x => new
{
Property = x,
Attribute = (ExportAttribute)Attribute.GetCustomAttribute(x, typeof(ExportAttribute), true)
})
.Where(x => x.Property.GetCustomAttribute(typeof(DisplayNameAttribute)) != null )
.OrderBy(x => x.Attribute != null ? x.Attribute.FieldOrder : -1)
.Select(x => x.Property )
.ToArray();
}
catch (Exception ex)
{
AppLogger.LogErrorOnly(ex);
}
return props;
}
public void AppendRow(PropertyInfo[] props)
{
if (props != null && props.Length > 0)
{
foreach (PropertyInfo prop in props)
{
dataTable.Columns.Add(new DataColumn(prop.Name));
}
}
}
public void ExportDataToExcel(Type type, Object[] objectList, string fileName)
{
PropertyInfo[] props = GetPropertyInfoArray(type);
AppendRow(props);
foreach (object obj in objectList)
{
DataRow dr = dataTable.NewRow();
for (int i = 0; i < props.Length; i++)
{
dr[props[i].Name] = props[i].GetValue(obj, null) != null ? props[i].GetValue(obj, null) : "";
}
this.dataTable.Rows.Add(dr);
}
ExportDataToExcel(this.dataTable, fileName);
}
public void ExportDataToExcel(DataTable dt, string fileName)
{
using (XLWorkbook wb = new XLWorkbook())
{
var ws = wb.Worksheets.Add(dt, "ws");
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
//set header style
ws.Rows(1, 1).Style.Fill.BackgroundColor = XLColor.White;
ws.Rows(1, 1).Style.Font.Bold = true;
ws.Rows(1, 1).Style.Font.FontColor = XLColor.Onyx;
ws.Columns().Width = 30;
//remove AutoFilter
ws.Tables.FirstOrDefault().ShowAutoFilter = false;
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
}
}
}
}