写入数据到Excel
---------------------DownloadExcelAsStreame<T>(IList<T> param, string sheetname=null) where T : new()------------------
依赖：
|-NPOI
|-Newtonsoft
备注：
这里为了方便，么有自己定义Attribute，用Josn的Attribute来给类型带入参数。
注意：
这个版本的NPOI写入Excel，单个文件最多只能写入65536行，如果不满足需要可以转写入CVS（CVS不限制大小）
--------------------------------------------------------------------------------------------------------------------------------
1 public static MemoryStream DownloadExcelAsStreame<T>(IList<T> param, string sheetname=null) where T : new() 2 { 3 if (param == null || param.Count < 1) 4 return null; 5 int rowsCount = param.Count(),columnCount = param.FirstOrDefault().GetType().GetProperties().Count(p => p.PropertyType.IsPublic); 6 var tp= typeof (T); 7 var tpattr = tp.GetCustomAttribute<JsonObjectAttribute>(); 8 if (tpattr == null) 9 return null; 10 HSSFWorkbook workbook = new HSSFWorkbook(); 11 HSSFSheet sheet = workbook.CreateSheet(sheetname?? tpattr.Id) as HSSFSheet; 12 HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; 13 List<PropertyInfo> pts = tp.GetProperties().Where(p => p.PropertyType.IsPublic).Select(o => o).ToList(); 14 string[] atv = pts.ConvertAll(ii => 15 { 16 var jsonPropertyAttribute = ii.GetCustomAttribute<JsonPropertyAttribute>(); 17 if (jsonPropertyAttribute != null) 18 return jsonPropertyAttribute.PropertyName; 19 return string.Empty; 20 }).ToArray(); 21 var me = new MemoryStream(); 22 int paddingrow = 0; 23 int rowindex = 0; 24 for (int nn = 0; nn < columnCount; nn++) 25 { 26 if (!string.IsNullOrEmpty(atv[nn])) 27 { 28 headerRow.CreateCell(rowindex, NPOI.SS.UserModel.CellType.String).SetCellValue(atv[nn] ?? pts[nn].Name); 29 rowindex++; 30 } 31 } 32 paddingrow += 1; 33 rowindex = 0; 34 for (int r = 0; r < rowsCount; r++) 35 { 36 HSSFRow cntRow = sheet.CreateRow(r + paddingrow) as HSSFRow; 37 for (int pj = 0; pj < columnCount; pj++) 38 { 39 if (!string.IsNullOrEmpty(atv[pj])) 40 { 41 cntRow.CreateCell(rowindex, NPOI.SS.UserModel.CellType.String) 42 .SetCellValue(pts[pj].GetValue(param[r]) != null 43 ? pts[pj].GetValue(param[r]).ToString() 44 : ""); 45 rowindex++; 46 } 47 } 48 rowindex =0; 49 } 50 workbook.Write(me); 51 workbook.Close(); 52 return me; 53 }
