用反射技术实现将泛型集合类中的数据导出成EXCEL

最近在工作中碰到许多地方需要将各种类型的集合对象导出到EXCEL中，之前在网上找了NOPI的EXCEL导出工具类，都是将datatable数据导出成excel。但我们这里的数据都是通过对象返回的。于是对工具类进行了改写，使用反射读取到集合类中的属性和数据，可实现直接从集合类中导出数据到excel。废话不多说，贴代码：

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Reflection;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

public  class ExcelHelper<T>
{
         ///   <summary>
         ///  泛型集合类导出成excel
         ///   </summary>
         ///   <param name="list"> 泛型集合类 </param>
         ///   <param name="fileName"> 生成的excel文件名 </param>
         ///   <param name="propertyName"> excel的字段列表 </param>
         public  static  void ListToExcel(IList<T> list,  string fileName,  params  string[] propertyName)
        {
            HttpContext.Current.Response.ContentType =  " application/vnd.ms-excel;charset=UTF-8 ";
            HttpContext.Current.Response.AddHeader( " Content-Disposition ",  string.Format( " attachment;filename={0} ", fileName));
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.BinaryWrite(ListToExcel<T>(list, propertyName).GetBuffer());
            HttpContext.Current.Response.End();
        }

         public  static MemoryStream ListToExcel<T>(IList<T> list,  params  string[] propertyName)
        {
             // 创建流对象
             using (MemoryStream ms =  new MemoryStream())
            {
                 // 将参数写入到一个临时集合中
                List< string> propertyNameList =  new List< string>();
                 if (propertyName !=  null)
                    propertyNameList.AddRange(propertyName);
                 // 床NOPI的相关对象
                IWorkbook workbook =  new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow( 0);

                 if (list.Count >  0)
                {
                     // 通过反射得到对象的属性集合
                    PropertyInfo[] propertys = list[ 0].GetType().GetProperties();
                     // 遍历属性集合生成excel的表头标题
                     for ( int i= 0;i<propertys.Count();i++)
                    {
                         // 判断此属性是否是用户定义属性
                         if (propertyNameList.Count ==  0)
                        {
                            headerRow.CreateCell(i).SetCellValue(propertys[i].Name);
                        }
                         else
                        {
                             if (propertyNameList.Contains(propertys[i].Name))
                                headerRow.CreateCell(i).SetCellValue(propertys[i].Name);
                        }
                    }


                     int rowIndex =  1;
                     // 遍历集合生成excel的行集数据
                     for ( int i =  0; i < list.Count; i++)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);
                         for ( int j =  0; j < propertys.Count(); j++)
                        {
                             if (propertyNameList.Count ==  0)
                            {
                                 object obj = propertys[j].GetValue(list[i],  null);
                                dataRow.CreateCell(j).SetCellValue(obj.ToString());
                            }
                             else
                            {
                                 if (propertyNameList.Contains(propertys[j].Name))
                                {
                                     object obj = propertys[j].GetValue(list[i],  null);
                                    dataRow.CreateCell(j).SetCellValue(obj.ToString());
                                }
                            }
                        }
                        rowIndex++;
                    }
                }
                workbook.Write(ms);
                ms.Flush();
                ms.Position =  0;
                 return ms;
            }
        }
}

使用时只需调用 ListToExcel方法即可，例如：

string[] propertyName =new string[]{"id","name","content"};
ExcelHelper<dictionaryInfo>.ListToExcel(di, "result", propertyName);

但是有一个小问题，生成的excel中总是从第2列开始，第1列是空的，还没找到原因。请各位大大给看看。

转载于:https://www.cnblogs.com/jinqi79731/archive/2012/11/09/2762969.html