#region List<T>转EXCEL
/// <summary>
/// 实体列表转EXCEL
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="EntityList">实体列表</param>
/// <param name="Titles">标题</param>
/// <param name="widths">列宽</param>
/// <param name="sheetName">工作簿名</param>
/// <param name="book">EXCEL</param>
/// <returns></returns>
public XSSFWorkbook EntityToExcel<T>(IEnumerable<T> entityIEnumerable, List<string> Titles,int[] widths,string sheetName,XSSFWorkbook book)
{
try
{
List<T> entityList= IEnumerableToList<T>(entityIEnumerable);
ISheet sheet = book.CreateSheet(sheetName);//创建工作簿
//设置样式 //样式
IFont font = book.CreateFont();
font.FontName = "微软雅黑";//设置字体
font.FontHeightInPoints = 10;
ICellStyle style = book.CreateCellStyle();
style.SetFont(font);
ICellStyle styleDouble = book.CreateCellStyle();
styleDouble.SetFont(font);
IDataFormat formatDouble = book.CreateDataFormat();
styleDouble.DataFormat = formatDouble.GetFormat("#,##0.00");
ICellStyle styleInt = book.CreateCellStyle();
styleInt.SetFont(font);
IDataFormat formatInt = book.CreateDataFormat();
styleInt.DataFormat = formatInt.GetFormat("#,##0");
ICellStyle stylePercent = book.CreateCellStyle();
stylePercent.SetFont(font);
IDataFormat formatPercent = book.CreateDataFormat();
stylePercent.DataFormat = formatPercent.GetFormat("0.00%");
IRow titlerow = sheet.CreateRow(0);//标题行
for (int i = 0; i <Titles.Count; i++)
{
sheet.SetColumnWidth(i, widths[i] * 256);//设置列宽
ICell titleCell= titlerow.CreateCell(i);
titleCell.SetCellValue(Titles[i]);
titleCell.CellStyle = style;
}
//反射获取属性信息
PropertyInfo[] PInfos = null;
if (entityList.Count>0)
{
PInfos = entityList[0].GetType().GetProperties();//属性
}
//内容行
for (int i = 0; i < entityList.Count; i++)
{
IRow itemRow = sheet.CreateRow(i + 1);//内容行
T entity = entityList[i];
//细胞
for (int j = 0; j < PInfos.Length; j++)//遍历列信息
{
ICell cellBody = itemRow.CreateCell(j);//创建第i+1行第j列表格
PropertyInfo pi = PInfos[j];//当前属性
try
{
if (string.IsNullOrEmpty(pi.GetValue(entity, null).ToString()))//属性值为空
{
cellBody.SetCellValue(pi.GetValue(entity, null).ToString());
continue;
}
}
catch
{
continue;
}
switch (pi.PropertyType.ToString())//如果不为空或null
{
case "System.Int64":
case "System.Int32":
var vInt = Int64.Parse(pi.GetValue(entity, null).ToString());
cellBody.SetCellValue(vInt);
if (vInt==0)
{
cellBody.CellStyle = style;
}
else
{
cellBody.CellStyle = styleInt;
}
break;
case "System.Double":
var vDouble = double.Parse(pi.GetValue(entity, null).ToString());
cellBody.SetCellValue(vDouble);//添加数值
if (vDouble==0)
{
cellBody.CellStyle = style;
}
else
{
cellBody.CellStyle = stylePercent;
}
break;
case "System.Decimal":
var vDecimal = double.Parse(pi.GetValue(entity, null).ToString());
cellBody.SetCellValue(vDecimal);
if (vDecimal==0)
{
cellBody.CellStyle = style;
}
else
{
cellBody.CellStyle = styleDouble;
}
break;
case "System.DateTime":
var vDate = string.Format("{0:yyyy-MM-dd}", pi.GetValue(entity, null).ToString());
cellBody.SetCellValue(vDate);
cellBody.CellStyle = style;
break;
default:
string str = pi.GetValue(entity, null).ToString().Replace("<br>", "\n");
cellBody.SetCellValue(str);
if (str.Contains("\n"))
{
style.WrapText = true;
itemRow.GetCell(j).CellStyle = style;
}
break;
}
}
}
}
catch
{
return book;
}
return book;//返回 EXCEL表
}
#region 接口转换
public List<T> IEnumerableToList<T>(IEnumerable<T> ts)
{
List<T> tsl = new List<T>();
foreach (T t in ts)
{
tsl.Add(t);
}
return tsl;
}
#endregion
/// <summary>
/// 实体列表转EXCEL
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="EntityList">实体列表</param>
/// <param name="Titles">标题</param>
/// <param name="widths">列宽</param>
/// <param name="sheetName">工作簿名</param>
/// <param name="book">EXCEL</param>
/// <returns></returns>
public XSSFWorkbook EntityToExcel<T>(IEnumerable<T> entityIEnumerable, List<string> Titles,int[] widths,string sheetName,XSSFWorkbook book)
{
try
{
List<T> entityList= IEnumerableToList<T>(entityIEnumerable);
ISheet sheet = book.CreateSheet(sheetName);//创建工作簿
//设置样式 //样式
IFont font = book.CreateFont();
font.FontName = "微软雅黑";//设置字体
font.FontHeightInPoints = 10;
ICellStyle style = book.CreateCellStyle();
style.SetFont(font);
ICellStyle styleDouble = book.CreateCellStyle();
styleDouble.SetFont(font);
IDataFormat formatDouble = book.CreateDataFormat();
styleDouble.DataFormat = formatDouble.GetFormat("#,##0.00");
ICellStyle styleInt = book.CreateCellStyle();
styleInt.SetFont(font);
IDataFormat formatInt = book.CreateDataFormat();
styleInt.DataFormat = formatInt.GetFormat("#,##0");
ICellStyle stylePercent = book.CreateCellStyle();
stylePercent.SetFont(font);
IDataFormat formatPercent = book.CreateDataFormat();
stylePercent.DataFormat = formatPercent.GetFormat("0.00%");
IRow titlerow = sheet.CreateRow(0);//标题行
for (int i = 0; i <Titles.Count; i++)
{
sheet.SetColumnWidth(i, widths[i] * 256);//设置列宽
ICell titleCell= titlerow.CreateCell(i);
titleCell.SetCellValue(Titles[i]);
titleCell.CellStyle = style;
}
//反射获取属性信息
PropertyInfo[] PInfos = null;
if (entityList.Count>0)
{
PInfos = entityList[0].GetType().GetProperties();//属性
}
//内容行
for (int i = 0; i < entityList.Count; i++)
{
IRow itemRow = sheet.CreateRow(i + 1);//内容行
T entity = entityList[i];
//细胞
for (int j = 0; j < PInfos.Length; j++)//遍历列信息
{
ICell cellBody = itemRow.CreateCell(j);//创建第i+1行第j列表格
PropertyInfo pi = PInfos[j];//当前属性
try
{
if (string.IsNullOrEmpty(pi.GetValue(entity, null).ToString()))//属性值为空
{
cellBody.SetCellValue(pi.GetValue(entity, null).ToString());
continue;
}
}
catch
{
continue;
}
switch (pi.PropertyType.ToString())//如果不为空或null
{
case "System.Int64":
case "System.Int32":
var vInt = Int64.Parse(pi.GetValue(entity, null).ToString());
cellBody.SetCellValue(vInt);
if (vInt==0)
{
cellBody.CellStyle = style;
}
else
{
cellBody.CellStyle = styleInt;
}
break;
case "System.Double":
var vDouble = double.Parse(pi.GetValue(entity, null).ToString());
cellBody.SetCellValue(vDouble);//添加数值
if (vDouble==0)
{
cellBody.CellStyle = style;
}
else
{
cellBody.CellStyle = stylePercent;
}
break;
case "System.Decimal":
var vDecimal = double.Parse(pi.GetValue(entity, null).ToString());
cellBody.SetCellValue(vDecimal);
if (vDecimal==0)
{
cellBody.CellStyle = style;
}
else
{
cellBody.CellStyle = styleDouble;
}
break;
case "System.DateTime":
var vDate = string.Format("{0:yyyy-MM-dd}", pi.GetValue(entity, null).ToString());
cellBody.SetCellValue(vDate);
cellBody.CellStyle = style;
break;
default:
string str = pi.GetValue(entity, null).ToString().Replace("<br>", "\n");
cellBody.SetCellValue(str);
if (str.Contains("\n"))
{
style.WrapText = true;
itemRow.GetCell(j).CellStyle = style;
}
break;
}
}
}
}
catch
{
return book;
}
return book;//返回 EXCEL表
}
#region 接口转换
public List<T> IEnumerableToList<T>(IEnumerable<T> ts)
{
List<T> tsl = new List<T>();
foreach (T t in ts)
{
tsl.Add(t);
}
return tsl;
}
#endregion