/// <summary>
/// 创建Excel
/// </summary>
/// <typeparam name="T">泛型实体类</typeparam>
/// <param name="data">导出的数据</param>
/// <param name="excelParameters">通过反射得到的列头对象</param>
/// <param name="FileName">文件存放路径</param>
/// <param name="dataIndex">预留参数,暂未用到</param>
/// <param name="excelVersion">导出的格式后缀</param>
public static void SaveExcel(IList <T> data, IList <ExcelParameterVo> excelParameters, string FileName, int dataIndex = 1, ExcelVersion excelVersion = ExcelVersion.xlsx)
{
FileInfo fileInfo = new FileInfo(FileName);
if (fileInfo.Exists)
{
//删除原有文件
fileInfo.Delete();
fileInfo = new FileInfo(FileName);//创建新文件
}
try
{
using (ExcelPackage package = new ExcelPackage(fileInfo))
{
if (data != null && data.Count > 0)
{
excelParameters = (from s in excelParameters orderby s.Sort select s).ToList();
object obj = null;
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("shett1");
//传入的列名 col用于得到list的下标，i用于写入Excel的某一列
for (int col = 0, i = 1; col < ((ICollection <ExcelParameterVo>)excelParameters).Count; col++, i++)
{
ExcelParameterVo excelParameterVo = excelParameters[col]; //得到列名对象
worksheet.Cells[1, i].Value = excelParameterVo.ColumnName; //设置列名
worksheet.Column(i).Width = excelParameterVo.ColumnWidth; //设置列宽
}
//传入的列名 row用于得到data的下标，j用于写入Excel的某一行
for (int row = 0, j = 2; row < data.Count; row++, j++)
{
//传入的列名 col用于得到excelParameters的下标，i用于写入Excel的某一列
for (int col = 0, i = 1; col < ((ICollection <ExcelParameterVo>)excelParameters).Count; col++, i++)
{
ExcelParameterVo excelParameterVo = excelParameters[col]; //得到列名对象
var item = data[row];
obj = excelParameterVo.Property.GetValue(item); //通过反射获取item
if (obj == null) //如果obj=null的话该列直接写空
{
worksheet.Cells[j, i].Value = "";
}
else
{
worksheet.Cells[j, i].Value = obj.ToString();
}
}
}
}
package.Save();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 创建Excel;并返回文件流
/// </summary>
/// <typeparam name="T">泛型实体类</typeparam>
/// <param name="data">导出的数据</param>
/// <param name="excelParameters">通过反射得到的列头对象</param>
/// <param name="FileName">文件存放路径</param>
/// <param name="dataIndex">预留参数,暂未用到</param>
/// <param name="excelVersion">导出的格式后缀</param>
public static byte[] ToExcelbyByte(IList <T> data, IList <ExcelParameterVo> excelParameters, int dataIndex = 1, ExcelVersion excelVersion = ExcelVersion.xlsx)
{
using (ExcelPackage package = new ExcelPackage())
{
try
{
if (data != null && data.Count > 0)
{
excelParameters = (from s in excelParameters orderby s.Sort select s).ToList();
object obj = null;
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");
//传入的列名 col用于得到list的下标，i用于写入Excel的某一列
for (int col = 0, i = 1; col < ((ICollection <ExcelParameterVo>)excelParameters).Count; col++, i++)
{
ExcelParameterVo excelParameterVo = excelParameters[col]; //得到列名对象
worksheet.Cells[1, i].Value = excelParameterVo.ColumnName; //设置列名
worksheet.Column(i).Width = excelParameterVo.ColumnWidth; //设置列宽
}
//传入的列名 row用于得到data的下标，j用于写入Excel的某一行
for (int row = 0, j = 2; row < data.Count; row++, j++)
{
//传入的列名 col用于得到excelParameters的下标，i用于写入Excel的某一列
for (int col = 0, i = 1; col < ((ICollection <ExcelParameterVo>)excelParameters).Count; col++, i++)
{
ExcelParameterVo excelParameterVo = excelParameters[col]; //得到列名对象
var item = data[row];
obj = excelParameterVo.Property.GetValue(item); //通过反射获取item
if (obj == null) //如果obj=null的话该列直接写空
{
worksheet.Cells[j, i].Value = "";
}
else
{
worksheet.Cells[j, i].Value = obj.ToString();
}
}
}
return(package.GetAsByteArray());
}
return(package.GetAsByteArray());
}
catch (Exception ex)
{
throw ex;
}
}
}
