用反射技術(shù)ListToExcel方法將泛型集合類中的數(shù)據(jù)導(dǎo)出成EXCEL

最近在工作中碰到許多地方需要將各種類型的集合對(duì)象導(dǎo)出到EXCEL中，之前在網(wǎng)上找了NOPI的EXCEL導(dǎo)出工具類，都是將datatable數(shù)據(jù)導(dǎo)出成excel。但我們這里的數(shù)據(jù)都是通過對(duì)象返回的。于是對(duì)工具類進(jìn)行了改寫，使用反射讀取到集合類中的屬性和數(shù)據(jù)，可實(shí)現(xiàn)直接從集合類中導(dǎo)出數(shù)據(jù)到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>
        /// 泛型集合類導(dǎo)出成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)
        {
            //創(chuàng)建流對(duì)象
            using (MemoryStream ms = new MemoryStream())
            {
                //將參數(shù)寫入到一個(gè)臨時(shí)集合中
                List<string> propertyNameList = new List<string>();
                if (propertyName != null)
                    propertyNameList.AddRange(propertyName);
                //床NOPI的相關(guān)對(duì)象
                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow(0);

                if (list.Count > 0)
                {
                    //通過反射得到對(duì)象的屬性集合
                    PropertyInfo[] propertys = list[0].GetType().GetProperties();
                    //遍歷屬性集合生成excel的表頭標(biāo)題
                    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的行集數(shù)據(jù)
                    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;
            }
        }
}

使用時(shí)只需調(diào)用 ListToExcel方法即可，例如：

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

但是有一個(gè)小問題，生成的excel中總是從第2列開始，第1列是空的，還沒找到原因。請(qǐng)各位大大給看看。