NPOI导出Excel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
#region NPOI
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System.Web;
using System.IO;
using System.Data;
using System.Collections;
using System.Web.UI.WebControls;
#endregion
/*========================================================================================
 *
 *
 * 说  明：导入、导出Excel
 * 作  者：李朝强
 * 日  期：2015/03/28
 *
 *
 * =====================================================================================*/
namespace TiKu.Common.Office
{
    public class Excel
    {

        #region ===============================================导出==================================
        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="context">请求上下文</param>
        /// <param name="fileName">导出文件的名</param>
        /// <param name="stream">数据流</param>
        public void Export(HttpContext context,
                           string fileName,
                           MemoryStream stream)
        {
            if (context.Request.Browser.Browser == "IE")
                fileName = HttpUtility.UrlEncode(fileName);
            context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
            context.Response.BinaryWrite(stream.ToArray());
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="context">上下文</param>
        /// <param name="filename">导出的文件名</param>
        /// <param name="data">数据</param>
        public void Export(HttpContext context,
                           string filename,
                           string sheetName,
                           Hashtable hashColumnInfos,
                           DataTable data)
        {
            MemoryStream ms = null;
            try
            {
                ms = ReadToExcel(data,
                                 sheetName,
                                 hashColumnInfos);
                //执行导出
                Export(context, filename, ms);
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                if (null != ms)
                {
                    ms.Close();
                    ms.Dispose();
                }
            }
        }

        /// <summary>
        /// 从Repeat控件中，导出Excel
        /// </summary>
        /// <param name="RepList">控件</param>
        /// <param name="filename">文件名</param>
        /// <param name="sheetName">工作表</param>
        public void Export(Repeater RepList,
                           string filename,
                           string sheetName,
                           HttpContext context)
        {
            object data = RepList.DataSource;
            if (data is DataTable)
            {
                Export(context,
                       filename,
                       sheetName,
                       null,
                       (DataTable)data);
                return;
            }
            if (data is DataView)
            {
                return;
            }
            if (data is IList)
            {
                return;
            }
        }
        #endregion

        #region ================================================数据流========================================
        /// <summary>
        /// 把DataTable对象，读取到内存流中
        /// </summary>
        /// <param name="data">DataTable对象</param>
        /// <param name="hashColumnInfos">（可选）列</param>
        /// <returns></returns>
        public MemoryStream ReadToExcel(DataTable data,
                                        string sheetName,
                                        Hashtable hashColumnInfos)
        {
            sheetName = string.IsNullOrEmpty(sheetName) ? string.Format("Sheet-{0:yyy/MM/dd}", DateTime.Now) : sheetName;
            //0>内存流
            MemoryStream ms = new MemoryStream();
            //1>创建工作薄
            IWorkbook __workbook = new HSSFWorkbook();
            //2>创建工作表
            ISheet __sheet = __workbook.CreateSheet(sheetName);


            #region 单元格样式
            //单元格样式
            ICellStyle cellStyle = __workbook.CreateCellStyle();
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;
            //设置字体
            IFont font = __workbook.CreateFont();
            font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
            cellStyle.SetFont(font);

            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;
            #endregion

            #region 3>创建表头
            //创建一行
            IRow __header = __sheet.CreateRow(0);
            if (null == hashColumnInfos)
            {
                for (int i = 0; i < data.Columns.Count; i++)
                {
                    //创建单元格
                    ICell __cell = __header.CreateCell(i);
                    __cell.SetCellValue(data.Columns[i].ColumnName);
                    __sheet.SetColumnWidth(i, 20 * 256);
                    __cell.CellStyle = cellStyle;
                }
            }
            else
            {
                int j = 0;
                foreach (DictionaryEntry item in hashColumnInfos)
                {
                    //创建单元格
                    ICell __cell = __header.CreateCell(j);
                    __cell.SetCellValue(item.Value.ToString());
                    __sheet.SetColumnWidth(j, 20 * 256);
                    __cell.CellStyle = cellStyle;
                    ++j;
                }
            }
            #endregion

            #region 4>创建数据行
            int c = 0;
            for (int k = 0; k < data.Rows.Count; k++)
            {
                IRow __dataRow = __sheet.CreateRow(k + 1);
                for (c = 0; c < data.Columns.Count; c++)
                {
                    ICell iCell = __dataRow.CreateCell(c);
                    iCell.SetCellValue(data.Rows[k][data.Columns[c].ColumnName].ToString());
                    c++;
                }
                c = 0;
            }
            #endregion

            //5>最后，写入内存流
            __workbook.Write(ms);
            return ms;

        }

        /// <summary>
        /// 把IDataReader对象，读取到内存流中
        /// </summary>
        /// <param name="dataReader">IDataReader对象</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="hashColumnInfos">列</param>
        /// <returns></returns>
        public MemoryStream ReadToExcel(IDataReader dataReader,
                                        string sheetName,
                                        Hashtable hashColumnInfos)
        {
            //0>内存流
            MemoryStream ms = new MemoryStream();
            //1>创建工作薄
            IWorkbook __workbook = new HSSFWorkbook();

            //2>创建工作表
            ISheet __sheet = __workbook.CreateSheet(sheetName);

            #region 3>表头
            IRow __header = __sheet.CreateRow(0);
            if (null == hashColumnInfos)
            {
                for (int i = 0; i < dataReader.FieldCount; i++)
                {
                    ICell iHeaderCell = __header.CreateCell(i);
                    iHeaderCell.SetCellValue(dataReader.GetName(i));
                }
            }
            else
            {
                int j = 0;
                foreach (DictionaryEntry item in hashColumnInfos)
                {
                    //创建单元格
                    ICell __cell = __header.CreateCell(j);
                    __cell.SetCellValue(item.Value.ToString());
                    ++j;
                }
            }
            #endregion

            #region 4>数据行
            int n = 1;
            while (dataReader.Read())
            {
                IRow __dataRow = __sheet.CreateRow(n);
                for (int c = 0; c < dataReader.FieldCount; c++)
                {
                    ICell iCell = __dataRow.CreateCell(c);
                    iCell.SetCellValue((dataReader.GetValue(c) ?? "").ToString());
                }
            }
            #endregion

            //5>写入内存流
            __workbook.Write(ms);

            return ms;
        }
        #endregion


    }
}