封装了一个NPOI操作类 WriteToDownLoad方法对DataTable和List写入Excel

  1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using System.Text;
  5using System.Web;
  6using System.IO;
  7using NPOI.HSSF.UserModel;
  8using NPOI.HPSF;
  9using NPOI.POIFS.FileSystem;
 10using System.Data;
 11using System.Reflection;
 12
 13    public class ExcelOperate
 14    {
 15        public ExcelOperate()
 16        {
 17
 18        }
 19
 20        static HSSFWorkbook hssfworkbook;
 21
 22        /// <summary>
 23        /// 初始化
 24        /// </summary>
 25       static  void InitializeWorkbook()
 26        {
 27            hssfworkbook = new HSSFWorkbook();
 28
 29            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
 30            dsi.Company = "";
 31            hssfworkbook.DocumentSummaryInformation = dsi;
 32
 33            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
 34            si.Subject = "";
 35            hssfworkbook.SummaryInformation = si;
 36        }
 37
 38        /// <summary>
 39        /// DataTable写入Excel
 40        /// </summary>
 41       /// <param name="FileName">要保存的文件名称 eg:test.xls</param>
 42       /// <param name="SheetName">工作薄名称</param>
 43       /// <param name="dt">要写入的DataTable </param>
 44        public static  void WriteToDownLoad(string FileName, string SheetName, DataTable dt)
 45        {
 46            string filename = FileName;
 47            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
 48            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
 49            HttpContext.Current.Response.Clear();
 50
 51            //初始化Excel信息
 52            InitializeWorkbook();
 53
 54            //填充数据
 55            DTExcel(SheetName, dt, null);
 56
 57            HttpContext.Current.Response.BinaryWrite(WriteToStream().GetBuffer());
 58            HttpContext.Current.Response.End();
 59        }
 60
 61
 62        /// <summary>
 63        /// List写入Excel
 64        /// </summary>
 65        /// <typeparam name="T">实体</typeparam>
 66        /// <param name="FileName">要保存的文件名称 eg:test.xls</param>
 67        /// <param name="SheetName">工作薄名称</param>
 68        /// <param name="lst">要写入的List</param>
 69        public static void WriteToDownLoad<T>(string FileName, string SheetName, List<T> lst)
 70        {
 71            string filename = FileName;
 72            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
 73            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
 74            HttpContext.Current.Response.Clear();
 75
 76            //初始化Excel信息
 77            InitializeWorkbook();
 78
 79            //填充数据
 80            ListExcel<T>(SheetName, lst, null);
 81
 82            HttpContext.Current.Response.BinaryWrite(WriteToStream().GetBuffer());
 83            HttpContext.Current.Response.End();
 84        }
 85
 86
 87        static MemoryStream WriteToStream()
 88        {
 89            MemoryStream file = new MemoryStream();
 90            hssfworkbook.Write(file);
 91            return file;
 92        }
 93
 94      
 95
 96        #region 数据填充部分
 97
 98        /// <summary>
 99        /// 将DataTable数据写入到Excel
100        /// </summary>
101        /// <param name="SheetName"></param>
102        /// <param name="dt"></param>
103        /// <param name="lstTitle"></param>
104        static void DTExcel(string SheetName, DataTable dt, List<string> lstTitle)
105        {
106            HSSFSheet sheet1 = hssfworkbook.CreateSheet(SheetName);
107            int y = dt.Columns.Count;
108            int x = dt.Rows.Count;
109
110            //给定的标题为空,赋值datatable默认的列名
111            if (lstTitle == null)
112            {
113                lstTitle = new List<string>();
114                for (int ycount = 0; ycount < y; ycount++)
115                {
116                    lstTitle.Add(dt.Columns[ycount].ColumnName);
117                }
118            }
119
120            HSSFRow hsTitleRow = sheet1.CreateRow(0);
121            //标题赋值
122            for (int yt = 0; yt <lstTitle.Count; yt++)
123            {
124                hsTitleRow.CreateCell(yt).SetCellValue(lstTitle[yt]);
125            }
126
127            //填充数据项
128            for (int xcount = 1; xcount < x; xcount++)
129            {
130                HSSFRow hsBodyRow = sheet1.CreateRow(xcount);
131
132                for (int ycBody = 0; ycBody < y; ycBody++)
133                {
134                    hsBodyRow.CreateCell(ycBody).SetCellValue(dt.DefaultView[xcount - 1][ycBody].ToString());
135                }
136            }
137
138        }
139
140       static void ListExcel<T>(string SheetName, List<T> lst, List<string> lstTitle)
141        {
142            HSSFSheet sheet1 = hssfworkbook.CreateSheet(SheetName);
143
144            T _t = (T)Activator.CreateInstance(typeof(T));
145            PropertyInfo[] propertys = _t.GetType().GetProperties();
146
147            //给定的标题为空,赋值T默认的列名
148            if (lstTitle == null)
149            {
150                lstTitle = new List<string>();
151                for (int ycount = 0; ycount < propertys.Length; ycount++)
152                {
153                    lstTitle.Add(((System.Reflection.MemberInfo)(propertys[ycount])).Name);//获取实体中列名称,去掉列类型
154                }
155            }
156
157            HSSFRow hsTitleRow = sheet1.CreateRow(0);
158            //标题赋值
159            for (int yt = 0; yt < lstTitle.Count; yt++)
160            {
161                hsTitleRow.CreateCell(yt).SetCellValue(lstTitle[yt]);
162            }
163
164            //填充数据项
165            for (int xcount = 1; xcount < lst.Count; xcount++)
166            {
167                HSSFRow hsBodyRow = sheet1.CreateRow(xcount);
168
169                for (int ycBody = 0; ycBody < propertys.Length; ycBody++)
170                {
171                    PropertyInfo pi = propertys[ycBody];
172                    object obj = pi.GetValue(lst[xcount], null);
173                    hsBodyRow.CreateCell(ycBody).SetCellValue(obj.ToString());
174                }
175            }
176
177        }
178        #endregion
179    }
180
181