使用反射导出对象列表到Excel,并且支持统计

最近项目中需要导出Excel报表,由于用到的是Linq,输出数据一般都是List<object>。例如:

           var sum = from l in downloadlist
join u
in uselist on l.ID equals u.ID into jUL
from u in jUL.DefaultIfEmpty()
orderby l.Count descending
select
new
DownReportInfo
{
Name
= l.Name,
UseCount
= u == null ? 0 : u.Count,
DownCount
= l.Count,
Per
= u == null ? 0 : (l.Count != 0 ? (int)((double)u.Count / (double)l.Count * 100) : 0),
ContentID
= l.ID
};

几乎每个前端能看到的表格都需要这样导出到Excel中,如果全部依靠手工使用NPOI的话,工作量将会非常大,而且是很枯燥的。那么如何利用自定义属性来生成Excel呢?下面一步步来实现:
首先,我们的Linq返回不能是匿名类,如上面的代码,sum.ToList()后返回List<DownReportInfo>这样的泛型列表。其中DownReportInfo这个类定义如下:

    public class DownReportInfo
{
[DisplayName(
"名称")]
[Formula(
"总计")]
public string Name { get; set; }
[DisplayName(
"下载数量")]
[Formula(FormulaType.SUM)]
public int DownCount { get; set; }

[DisplayName(
"使用数量")]
[Formula(FormulaType.SUM)]
public int UseCount { get; set; }

[DisplayName(
"使用率")]
[Formula(FormulaType.AVG)]
public int Per { get; set; }

public int AppID { get; set; }
public int ContentID { get; set; }
public int FolderID { get; set; }
}

两个属性Display与Formula其中Formula是自定义属性(CustomAttribute),继承自Attribute,当实体字段没有这两个属性时,Excel中将不包含此字段

public class FormulaAttribute:Attribute
{
public FormulaAttribute(FormulaType type)
{
FormulaType
= type;
}
public FormulaAttribute(string val)
{
FormulaType
=FormulaType.STRING;
DisplayString
= val;
}

public string DisplayString { get; set; }
public FormulaType FormulaType { get; set; }
}
public enum FormulaType
{
SUM,
COUNT,
AVG,
STRING
}

其中的枚举FormulaType值,顾名思义分别是总计,计数,平均,最后一个STRING是照样输出。看完全文你可以自由扩展统计类型.

DisplayString是当FormulaType为String时要照原样输出的字符串。

生成Excel的工具类如下:

public class ExcelReportHelper
{
/// <summary>
/// 将列表导出到Excel中
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="listData"></param>
/// <param name="sheetName"></param>
/// <param name="title"></param>
/// <param name="description"></param>
/// <param name="saveTo"></param>
/// <returns></returns>
public string ExportToExcel<T>(List<T> listData, string sheetName, string title, string description,string saveTo)
{
try
{
int propertyCount = getPropertyCount(typeof(T));

var hssfWorkbook = new HSSFWorkbook();
var sheet1 = hssfWorkbook.CreateSheet(sheetName);
var row0 = (HSSFRow)sheet1.CreateRow(0);
row0.CreateCell(
0, CellType.STRING);
var row = sheet1.CreateRow(0);
var cell = row.CreateCell(0);
cell.SetCellValue(title);

//标题样式
var styleTitle = hssfWorkbook.CreateCellStyle();
styleTitle.Alignment
= HorizontalAlignment.CENTER;
var font = hssfWorkbook.CreateFont();
font.FontHeight
= 20 * 20;
styleTitle.SetFont(font);
cell.CellStyle
= styleTitle;

sheet1.AddMergedRegion(
new CellRangeAddress(0, 0, 0, propertyCount-1));
row
= sheet1.CreateRow(1);
cell
= row.CreateCell(0);
sheet1.AddMergedRegion(
new CellRangeAddress(1, 1, 0, propertyCount-1));
cell.SetCellValue(description);

//描述样式
var styleDesc = hssfWorkbook.CreateCellStyle();
styleDesc.Alignment
= HorizontalAlignment.CENTER;
var fontDesc = hssfWorkbook.CreateFont();
fontDesc.Color
= HSSFColor.GREY_50_PERCENT.index;
styleDesc.SetFont(fontDesc);
cell.CellStyle
= styleDesc;

var row1 = (HSSFRow)sheet1.CreateRow(2);
//表头样式
var rowstyle = hssfWorkbook.CreateCellStyle();
rowstyle.Alignment
= HorizontalAlignment.CENTER;
rowstyle.FillBackgroundColor
= HSSFColor.GREY_25_PERCENT.index;
var font1 = hssfWorkbook.CreateFont();

font1.FontHeight
= 20 * 12;
font1.Boldweight
= 600;
rowstyle.SetFont(font1);

WriteHeader(
typeof(T), row1,rowstyle);

int i = 0;
foreach (var item in listData)
{
int rowIndex = i;
var rowData = (HSSFRow)sheet1.CreateRow(rowIndex + 3);
WriteData(item,
typeof(T),rowData);
i
++;
}
setAutoColumn(sheet1,i);
var rowfooter = (HSSFRow)sheet1.CreateRow(i+3);
WriteFooter(
typeof(T), rowfooter,sheet1);
var p=Path.GetDirectoryName(saveTo);

if (!Directory.Exists(p))
{
Directory.CreateDirectory(p);
}
var xlsname = sheetName+ ".xls";
string save = p + "/" + xlsname;

var file = new FileStream(save, FileMode.Create);
hssfWorkbook.Write(file);
file.Close();

return xlsname;
}
catch (Exception)
{
return "";
}
}
private void setAutoColumn(ISheet sheet,int maxColumn)
{
//列宽自适应,只对英文和数字有效
for (int i = 0; i <= maxColumn; i++)
{
sheet.AutoSizeColumn(i);
}
//获取当前列的宽度,然后对比本列的长度,取最大值
for (int columnNum = 0; columnNum <= maxColumn; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 3; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow
= sheet.CreateRow(rowNum);
}
else
{
currentRow
= sheet.GetRow(rowNum);
}

if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell
= currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth
= length;
}
}
}
sheet.SetColumnWidth(columnNum, columnWidth
* 256);
}
}
private int getPropertyCount(Type type)
{
if (type != null)
{
Type t
= type;
PropertyInfo[] propertyInfo
= t.GetProperties();

int i = 0;
foreach (PropertyInfo propInfo in propertyInfo)
{
object[] objAttrs = propInfo.GetCustomAttributes(typeof(DisplayNameAttribute), true);

if (objAttrs.Length > 0)
{
i
++;
}

}
return i;
}
return 0;
}

/// <summary>
/// 写表头
/// </summary>
/// <param name="type"></param>
/// <param name="row"> </param>
/// <param name="style"> </param>
public void WriteHeader(Type type,HSSFRow row,ICellStyle style)
{
if (type != null)
{
Type t
= type;
PropertyInfo[] propertyInfo
= t.GetProperties();
int i = 0;
foreach (PropertyInfo propInfo in propertyInfo)
{
var cell = row.CreateCell(i);
object[] objAttrs = propInfo.GetCustomAttributes(typeof(DisplayNameAttribute), true);

if (objAttrs.Length > 0)
{
var attr = objAttrs[0] as DisplayNameAttribute;
cell.SetCellValue(attr
!= null ? attr.DisplayName : "");
i
++;
}
cell.CellStyle
= style;

}

}
}

public void WriteData<T>(T obj,Type type,HSSFRow row)
{
if (obj != null)
{
Type t
= type;
PropertyInfo[] propertyInfo
= t.GetProperties();
int i = 0;
foreach (PropertyInfo propInfo in propertyInfo)
{
object[] objAttrs = propInfo.GetCustomAttributes(typeof (DisplayNameAttribute), true);

if (objAttrs.Length > 0)
{
var cell = row.CreateCell(i);
object value = propInfo.GetValue(obj, null);
if(propInfo.PropertyType==typeof(int))
{

cell.SetCellValue((
int)value);
}
else
{
cell.SetCellValue(value.ToString());
}


i
++;
}
}

}
}

private void WriteFooter(Type type,HSSFRow row,ISheet sheet)
{

Type t
= type;
PropertyInfo[] propertyInfo
= t.GetProperties();
int col = 0;
int rownum=row.RowNum;
foreach (PropertyInfo propInfo in propertyInfo)
{
object[] objAttrs = propInfo.GetCustomAttributes(typeof(DisplayNameAttribute), true);

if (objAttrs.Length > 0)
{

object[] formulaAttrs = propInfo.GetCustomAttributes(typeof (FormulaAttribute), true);
if(formulaAttrs.Length>0)
{
string formulaRange = "({0}{1}:{2}{3})";
var colName = ConvertColumnIndexToColumnName(col);
formulaRange
= string.Format(formulaRange, colName, 4, colName, rownum);

var cell = row.CreateCell(col);
var value = formulaAttrs[0] as FormulaAttribute;
if (value != null)
{
switch (value.FormulaType)
{
case FormulaType.STRING:
cell.SetCellValue(value.DisplayString);
break;
case FormulaType.COUNT:
cell.SetCellFormula(
"COUNT"+formulaRange);
break;
case FormulaType.SUM:
cell.SetCellFormula(
"SUM"+formulaRange);
break;
case FormulaType.AVG:
cell.SetCellFormula(
"AVERAGE" + formulaRange);
break;
default:
cell.SetCellValue(
"");
break;
}
}
}
col
++;
}
}

sheet.ForceFormulaRecalculation
= true;


}

// <summary>
/// 将Excel的列索引转换为列名,列索引从0开始,列名从A开始。如第0列为A,第1列为B...
/// </summary>
/// <param name="index">列索引</param>
/// <returns>列名,如第0列为A,第1列为B...</returns>
public static string ConvertColumnIndexToColumnName(int index)
{
index
= index + 1;
int system = 26;
char[] digArray = new char[100];
int i = 0;
while (index > 0)
{
int mod = index % system;
if (mod == 0) mod = system;
digArray[i
++] = (char)(mod - 1 + 'A');
index
= (index - 1) / 26;
}
StringBuilder sb
= new StringBuilder(i);
for (int j = i - 1; j >= 0; j--)
{
sb.Append(digArray[j]);
}
return sb.ToString();
}
}

调用非常方便:

  var filename = erh.ExportToExcel(json.ToList(), "按内容统计", curdep.Name + " - 下载统计", "" + datebegin + "" + dateend + "按内容统计", saveTo);

第一个参数为泛型列表,第二个为Excel的表单名称,第三个为标题,第四个为描述,第五个为Excel保存路径。最终生成的Excel形式如下:

 

本文链接



You must enable javascript to see captcha here!

Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress

无觅相关文章插件,快速提升流量