/// <summary>
/// 返回Model 数据集,Excel形式的二进制数据
/// </summary>
/// <param name="ListEmployee"></param>
/// <returns></returns>
public static byte[] OutPutExcelByListModel<T>(List<T> ObjT)
{
//创建Graphics 测量 文字宽度
System.Drawing.Bitmap bitmap = new System.Drawing.Bitmap(1, 1);
System.Drawing.Graphics g = System.Drawing.Graphics.FromImage(bitmap);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = (Aspose.Cells.Worksheet)workbook.Worksheets[0];
int RowIndex = 0;
int CellsIndex = 0;
var _obj = ObjT as System.Collections.IList;
Type _ty = null;
if (_obj.Count > 0)
{
_ty = _obj[0].GetType();
}
Type t = _ty;
System.Reflection.PropertyInfo[] PropertyInfos = t.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic);
//string ss = Common.GetDisplayName(_Employee.GetType(), "Id");
foreach (var item in _obj)
{
CellsIndex = 0;
if (RowIndex == 0)
{
foreach (System.Reflection.PropertyInfo fi in PropertyInfos)
{
DisplayAttribute disAttr = (DisplayAttribute)fi.GetCustomAttributes(typeof(DisplayAttribute), true).SingleOrDefault();
string StrTxt = fi.Name + "(" + fi.PropertyType.Name + ")";
if (disAttr != null)
StrTxt = disAttr.Name + "(" + fi.PropertyType.Name + ")";
sheet.Cells[RowIndex, CellsIndex].Value = StrTxt;
Aspose.Cells.Style style = sheet.Cells[RowIndex, CellsIndex].GetStyle();
System.Drawing.Font font = new System.Drawing.Font(style.Font.Name, style.Font.Size);
//12cm为12*96/2.54=454像素
sheet.Cells.Columns[CellsIndex].Width = g.MeasureString(StrTxt, font).Width / 7.384;
CellsIndex++;
}
CellsIndex = 0;
RowIndex++;
}
foreach (System.Reflection.PropertyInfo fi in PropertyInfos)
{
DisplayAttribute disAttr = (DisplayAttribute)fi.GetCustomAttributes(typeof(DisplayAttribute), true).SingleOrDefault();
object obj = fi.GetValue((object)item, null);
string str = "";
Type _type = null;
if (obj != null)
{
str = obj.ToString();
_type = obj.GetType();
}
if (str.IndexOf("KSWECDS.") >= 0 || str.IndexOf("System.") >= 0)
{
try
{
if (_type != null)
{
if (str.IndexOf("KSWECDS.") >= 0 && _type.Name.IndexOf("HashSet") < 0)
{
string FieldName = "Name";
System.Reflection.PropertyInfo[] _PropertyInfos = _type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic);
var WhereProperty = _PropertyInfos.Where(x => x.Name == FieldName);
if (_PropertyInfos.Any())
{
obj = WhereProperty.FirstOrDefault().GetValue((object)obj, null);
}
}
if (str.IndexOf("System.Collections.") >= 0)
{
string FieldName = "Count";
System.Reflection.PropertyInfo[] _PropertyInfos = _type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic);
var WhereProperty = _PropertyInfos.Where(x => x.Name == FieldName);
if (_PropertyInfos.Any())
{
string ModelName = obj.ToString();
try
{
if (disAttr != null)
ModelName = disAttr.Name;
else
{
string Split = "KSWECDS.Web.Models.";
int Sindex = ModelName.IndexOf(Split);
if (Sindex > 0)
{
ModelName = ModelName.Substring(Sindex + Split.Length);
if (ModelName.IndexOf(']') >= 0)
{
ModelName = ModelName.Substring(0, ModelName.IndexOf(']'));
}
}
}
}
catch
{
}
obj = WhereProperty.FirstOrDefault().GetValue((object)obj, null).ToString() + "-" + ModelName;
}
}
}
}
catch
{
}
}
var style = sheet.Cells[RowIndex, CellsIndex].GetStyle();
var retVal = obj;
string DataType = fi.PropertyType.Name;
if (fi.PropertyType.IsGenericType && fi.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
var Arguments = fi.PropertyType.GetGenericArguments();
DataType = Arguments[0].Name;
}
if (retVal != null)
{
switch (DataType.ToLower())
{
case "int":
int Dftint = 0;
if (int.TryParse(retVal.ToString(), out Dftint))
{
style.Number = 1;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
break;
case "int32":
int Dftint32 = 0;
if (int.TryParse(retVal.ToString(), out Dftint32))
{
style.Number = 1;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
break;
case "int64":
int Dftint64 = 0;
if (int.TryParse(retVal.ToString(), out Dftint64))
{
style.Number = 1;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
break;
case "string":
style.Number = 9;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
break;
case "datetime":
int TDatetime = 0;
if (int.TryParse(retVal.ToString(), out TDatetime))
{
style.Custom = "yyyy-MM-dd HH:mm:ss";
sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
else
{
DateTime DftDateTime = new DateTime();
if (DateTime.TryParse(retVal.ToString(), out DftDateTime))
{
style.Custom = "yyyy-MM-dd HH:mm:ss";
sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
}
break;
case "bool":
bool Dftbool = false;
if (bool.TryParse(retVal.ToString(), out Dftbool))
{
style.Number = 9;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
if (Dftbool)
{
style.Font.Color = System.Drawing.Color.Green;
sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
obj = "是";
}
else
{
style.Font.Color = System.Drawing.Color.Red;
sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
obj = "否";
}
}
break;
case "boolean":
bool Dftboolean = false;
if (bool.TryParse(retVal.ToString(), out Dftboolean))
{
style.Number = 9;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
if (Dftboolean)
{
style.Font.Color = System.Drawing.Color.Green;
sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
obj = "是";
}
else
{
style.Font.Color = System.Drawing.Color.Red;
sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
obj = "否";
}
}
break;
case "decimal":
decimal Dftdecimal = 0;
if (decimal.TryParse(retVal.ToString(), out Dftdecimal))
{
style.Number = 1;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
break;
case "double":
double Dftdouble = 0;
if (double.TryParse(retVal.ToString(), out Dftdouble))
{
style.Number = 1;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
break;
case "float":
float Dftfloat = 0;
if (float.TryParse(retVal.ToString(), out Dftfloat))
{
style.Number = 1;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
break;
default:
style.Number = 9;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
break;
}
}
if (obj != null)
{
Aspose.Cells.Style _style = sheet.Cells.Columns[CellsIndex].Style;
System.Drawing.Font font = new System.Drawing.Font(style.Font.Name, style.Font.Size);
double StrWidth = 0;
StrWidth = g.MeasureString(obj.ToString(), font).Width / 7.384;
if (sheet.Cells.Columns[CellsIndex].Width < StrWidth)
sheet.Cells.Columns[CellsIndex].Width = StrWidth;
}
#region 设置背景色
//else
//{
// var _style = sheet.Cells[RowIndex, CellsIndex].GetStyle();
// //_style.BackgroundColor = System.Drawing.Color.LightSkyBlue;
// _style.ForegroundColor = System.Drawing.Color.FromArgb(230, 211, 211, 211);
// _style.Pattern = Aspose.Cells.BackgroundType.Solid;
// sheet.Cells[RowIndex, CellsIndex].SetStyle(_style);
//}
#endregion
sheet.Cells[RowIndex, CellsIndex].Value = obj;
CellsIndex++;
}
RowIndex++;
}
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(HttpContext.Current.Server.MapPath("/DownLoad/"));
if (!dir.Exists)
dir.Create();
workbook.Save((dir.FullName + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"), Aspose.Cells.SaveFormat.Excel97To2003);
return workbook.SaveToStream().ToArray();
}
/// 返回Model 数据集,Excel形式的二进制数据
/// </summary>
/// <param name="ListEmployee"></param>
/// <returns></returns>
public static byte[] OutPutExcelByListModel<T>(List<T> ObjT)
{
//创建Graphics 测量 文字宽度
System.Drawing.Bitmap bitmap = new System.Drawing.Bitmap(1, 1);
System.Drawing.Graphics g = System.Drawing.Graphics.FromImage(bitmap);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = (Aspose.Cells.Worksheet)workbook.Worksheets[0];
int RowIndex = 0;
int CellsIndex = 0;
var _obj = ObjT as System.Collections.IList;
Type _ty = null;
if (_obj.Count > 0)
{
_ty = _obj[0].GetType();
}
Type t = _ty;
System.Reflection.PropertyInfo[] PropertyInfos = t.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic);
//string ss = Common.GetDisplayName(_Employee.GetType(), "Id");
foreach (var item in _obj)
{
CellsIndex = 0;
if (RowIndex == 0)
{
foreach (System.Reflection.PropertyInfo fi in PropertyInfos)
{
DisplayAttribute disAttr = (DisplayAttribute)fi.GetCustomAttributes(typeof(DisplayAttribute), true).SingleOrDefault();
string StrTxt = fi.Name + "(" + fi.PropertyType.Name + ")";
if (disAttr != null)
StrTxt = disAttr.Name + "(" + fi.PropertyType.Name + ")";
sheet.Cells[RowIndex, CellsIndex].Value = StrTxt;
Aspose.Cells.Style style = sheet.Cells[RowIndex, CellsIndex].GetStyle();
System.Drawing.Font font = new System.Drawing.Font(style.Font.Name, style.Font.Size);
//12cm为12*96/2.54=454像素
sheet.Cells.Columns[CellsIndex].Width = g.MeasureString(StrTxt, font).Width / 7.384;
CellsIndex++;
}
CellsIndex = 0;
RowIndex++;
}
foreach (System.Reflection.PropertyInfo fi in PropertyInfos)
{
DisplayAttribute disAttr = (DisplayAttribute)fi.GetCustomAttributes(typeof(DisplayAttribute), true).SingleOrDefault();
object obj = fi.GetValue((object)item, null);
string str = "";
Type _type = null;
if (obj != null)
{
str = obj.ToString();
_type = obj.GetType();
}
if (str.IndexOf("KSWECDS.") >= 0 || str.IndexOf("System.") >= 0)
{
try
{
if (_type != null)
{
if (str.IndexOf("KSWECDS.") >= 0 && _type.Name.IndexOf("HashSet") < 0)
{
string FieldName = "Name";
System.Reflection.PropertyInfo[] _PropertyInfos = _type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic);
var WhereProperty = _PropertyInfos.Where(x => x.Name == FieldName);
if (_PropertyInfos.Any())
{
obj = WhereProperty.FirstOrDefault().GetValue((object)obj, null);
}
}
if (str.IndexOf("System.Collections.") >= 0)
{
string FieldName = "Count";
System.Reflection.PropertyInfo[] _PropertyInfos = _type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic);
var WhereProperty = _PropertyInfos.Where(x => x.Name == FieldName);
if (_PropertyInfos.Any())
{
string ModelName = obj.ToString();
try
{
if (disAttr != null)
ModelName = disAttr.Name;
else
{
string Split = "KSWECDS.Web.Models.";
int Sindex = ModelName.IndexOf(Split);
if (Sindex > 0)
{
ModelName = ModelName.Substring(Sindex + Split.Length);
if (ModelName.IndexOf(']') >= 0)
{
ModelName = ModelName.Substring(0, ModelName.IndexOf(']'));
}
}
}
}
catch
{
}
obj = WhereProperty.FirstOrDefault().GetValue((object)obj, null).ToString() + "-" + ModelName;
}
}
}
}
catch
{
}
}
var style = sheet.Cells[RowIndex, CellsIndex].GetStyle();
var retVal = obj;
string DataType = fi.PropertyType.Name;
if (fi.PropertyType.IsGenericType && fi.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
var Arguments = fi.PropertyType.GetGenericArguments();
DataType = Arguments[0].Name;
}
if (retVal != null)
{
switch (DataType.ToLower())
{
case "int":
int Dftint = 0;
if (int.TryParse(retVal.ToString(), out Dftint))
{
style.Number = 1;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
break;
case "int32":
int Dftint32 = 0;
if (int.TryParse(retVal.ToString(), out Dftint32))
{
style.Number = 1;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
break;
case "int64":
int Dftint64 = 0;
if (int.TryParse(retVal.ToString(), out Dftint64))
{
style.Number = 1;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
break;
case "string":
style.Number = 9;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
break;
case "datetime":
int TDatetime = 0;
if (int.TryParse(retVal.ToString(), out TDatetime))
{
style.Custom = "yyyy-MM-dd HH:mm:ss";
sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
else
{
DateTime DftDateTime = new DateTime();
if (DateTime.TryParse(retVal.ToString(), out DftDateTime))
{
style.Custom = "yyyy-MM-dd HH:mm:ss";
sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
}
break;
case "bool":
bool Dftbool = false;
if (bool.TryParse(retVal.ToString(), out Dftbool))
{
style.Number = 9;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
if (Dftbool)
{
style.Font.Color = System.Drawing.Color.Green;
sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
obj = "是";
}
else
{
style.Font.Color = System.Drawing.Color.Red;
sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
obj = "否";
}
}
break;
case "boolean":
bool Dftboolean = false;
if (bool.TryParse(retVal.ToString(), out Dftboolean))
{
style.Number = 9;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
if (Dftboolean)
{
style.Font.Color = System.Drawing.Color.Green;
sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
obj = "是";
}
else
{
style.Font.Color = System.Drawing.Color.Red;
sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
obj = "否";
}
}
break;
case "decimal":
decimal Dftdecimal = 0;
if (decimal.TryParse(retVal.ToString(), out Dftdecimal))
{
style.Number = 1;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
break;
case "double":
double Dftdouble = 0;
if (double.TryParse(retVal.ToString(), out Dftdouble))
{
style.Number = 1;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
break;
case "float":
float Dftfloat = 0;
if (float.TryParse(retVal.ToString(), out Dftfloat))
{
style.Number = 1;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
}
break;
default:
style.Number = 9;
//sheet.Cells[RowIndex, CellsIndex].SetStyle(style);
break;
}
}
if (obj != null)
{
Aspose.Cells.Style _style = sheet.Cells.Columns[CellsIndex].Style;
System.Drawing.Font font = new System.Drawing.Font(style.Font.Name, style.Font.Size);
double StrWidth = 0;
StrWidth = g.MeasureString(obj.ToString(), font).Width / 7.384;
if (sheet.Cells.Columns[CellsIndex].Width < StrWidth)
sheet.Cells.Columns[CellsIndex].Width = StrWidth;
}
#region 设置背景色
//else
//{
// var _style = sheet.Cells[RowIndex, CellsIndex].GetStyle();
// //_style.BackgroundColor = System.Drawing.Color.LightSkyBlue;
// _style.ForegroundColor = System.Drawing.Color.FromArgb(230, 211, 211, 211);
// _style.Pattern = Aspose.Cells.BackgroundType.Solid;
// sheet.Cells[RowIndex, CellsIndex].SetStyle(_style);
//}
#endregion
sheet.Cells[RowIndex, CellsIndex].Value = obj;
CellsIndex++;
}
RowIndex++;
}
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(HttpContext.Current.Server.MapPath("/DownLoad/"));
if (!dir.Exists)
dir.Create();
workbook.Save((dir.FullName + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"), Aspose.Cells.SaveFormat.Excel97To2003);
return workbook.SaveToStream().ToArray();
}