用反射隔热涂料|用反射技术ListToExcel方法将泛型集合类中的数据导出成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列是空的，还没找到原因。请各位大大给看看。 

用反射隔热涂料|用反射技术ListToExcel方法将泛型集合类中的数据导出成EXCEL

http://m.quanqiunao.cn/yingjianxiangguan/13289/