共享一个MVC4通过NPOI导出excel的通用方法

  1 public static System.IO.MemoryStream ExportExcel<T>(string title, List<T> objList, params string[] excelPropertyNames)
  2 {
  3     NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
  4     NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Sheet1");
  5     NPOI.SS.UserModel.IRow row;
  6     NPOI.SS.UserModel.ICell cell;
  7     NPOI.SS.UserModel.ICellStyle cellStyle;
  8 
  9     int rowNum = 0;
 10     if (!string.IsNullOrEmpty(title))
 11     {
 12         #region 标题
 13         #region 标题样式
 14         cellStyle = workbook.CreateCellStyle();
 15         cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
 16         cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中有问题
 17         NPOI.SS.UserModel.IFont font = workbook.CreateFont();
 18         font.FontHeightInPoints = 15;
 19         cellStyle.SetFont(font);
 20         #endregion
 21         row = sheet.CreateRow(rowNum);
 22         cell = row.CreateCell(0, NPOI.SS.UserModel.CellType.String);
 23         cell.SetCellValue(title);
 24         cell.CellStyle = cellStyle;
 25         sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelPropertyNames.Length > 0 ? excelPropertyNames.Length - 1 : 0));
 26         rowNum++;
 27         #endregion
 28     }
 29 
 30     if (objList.Count > 0)
 31     {
 32         Type type = objList[0].GetType();
 33         if (type != null)
 34         {
 35             System.Reflection.PropertyInfo[] properties = type.GetProperties();
 36             if (properties.Length > 0)
 37             {
 38                 #region 表头
 39                 #region 表头样式
 40                 cellStyle = workbook.CreateCellStyle();
 41                 cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
 42                 #endregion
 43                 if (excelPropertyNames.Length > 0)
 44                 {
 45                     row = sheet.CreateRow(rowNum);
 46                     int count = 0;
 47                     for (int m = 0; m < properties.Length; m++)
 48                     {
 49                         if (excelPropertyNames.Contains(properties[m].Name))
 50                         {
 51                             cell = row.CreateCell(count, NPOI.SS.UserModel.CellType.String);
 52                             string displayName = GetDisplayNameByPropertyName(properties[m].Name);
 53                             cell.SetCellValue(displayName == null ? "" : displayName);
 54                             cell.CellStyle = cellStyle;
 55                             count++;
 56                         }
 57                     }
 58                     rowNum++;
 59                 }
 60                 #endregion
 61 
 62                 #region 表体
 63                 if (excelPropertyNames.Length > 0)
 64                 {
 65                     for (int i = 0; i < objList.Count; i++)
 66                     {
 67                         row = sheet.CreateRow(i + rowNum);
 68                         int count = 0;
 69                         for (int j = 0; j < properties.Length; j++)
 70                         {
 71                             if (excelPropertyNames.Contains(properties[j].Name))
 72                             {
 73                                 cell = row.CreateCell(count);
 74                                 object obj = properties[j].GetValue(objList[i]);
 75                                 cell.SetCellValue(obj == null ? "" : obj.ToString());
 76                                 cell.CellStyle = cellStyle;
 77                                 count++;
 78                             }
 79                         }
 80                     }
 81                 }
 82                 #endregion
 83             }
 84         }
 85     }
 86     System.IO.MemoryStream ms = new System.IO.MemoryStream();
 87     workbook.Write(ms);
 88     return ms;
 89 }
 90 
 91 public static string GetDisplayNameByPropertyName(string propertyName)
 92 {
 93     string result = null;
 94     foreach (KeyValuePair<string,string> dic in NameDictionary())
 95     {
 96         if (dic.Key == propertyName)
 97         {
 98             result = dic.Value;
 99         }
100         continue;
101     }
102     return result;
103 }
104 
105 public static Dictionary<string, string> NameDictionary()
106 {
107     Dictionary<string, string> dic = new Dictionary<string, string>();
108     dic.Add("AdminID", "编号");
109 
110     dic.Add("AdminName", "用户名");
111 
112     dic.Add("AdminMobile", "手机号");
113 
114     dic.Add("RealName", "真实姓名");
115 
116     return dic;
117 }

 1 public ActionResult Test()
 2 {
 3     int totalCount;
 4     List<AdminModel> adminModelList = adminBLL.GetPageList(1, 10, out totalCount);
 5     if (adminModelList == null)
 6     {
 7         adminModelList = new List<AdminModel>();
 8     }
 9     return File(ExcelHelper.ExportExcel<AdminModel>("表头", adminModelList, "AdminID", "AdminName", "AdminMobile", "RealName").ToArray(), "application/vnd.ms-excel", "工作簿.xls");
10 }