写入数据到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         }

 

posted on 2017-03-20 10:17  Thancoo  阅读(405)  评论(0编辑  收藏  举报

导航