将数据导出到Excel，供用户下载是经常要使用到的一个功能，我这里进行了一些封装，目前已满足项目中导出的需求，使用DataReader导出Excel，支持自定义多表头，使用委托处理字段格式化及字段值的计算；

导出执行的代码如下：

/// <summary>
        /// 将DataReader数据写入到 excel book 中
        /// 一般数据量小的导出用这个就可以啦
        /// </summary>
        /// <param name="dataReader"></param>
        /// <param name="book">Excel Book</param>
        /// <param name="excelName">excelName文件名字</param>
        /// <param name="sheetSize">每个Sheet放多少条记录</param>
        /// <param name="excelHeaderModels">Excel表头实体，Select字段可以从这里获取</param>
        /// <param name="func">字段格式化委托函数</param>
        /// <param name="funcCalc">字段的值计算委托函数</param>
        /// <returns></returns>
        public Hashtable CommonDataWrite2Sheet(IDataReader dataReader, IWorkbook book, string excelName, int sheetSize, List<ExcelHeaderModel> excelHeaderModels = null, Func<string, object, string> func = null, Func<string, object, IDataReader, object> funcCalc = null)
        {
            string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, BusinessSystemInfo.ExportDir, DateTime.Now.ToString(BaseSystemInfo.DateFormat));
            string fileName = Path.Combine(filePath, excelName);
            //创建表头
            int totalCount = 1;
            //表头的要去掉 否则默认的是65535条记录 一个表头 所以从1开始
            if (excelHeaderModels != null)
            {
                totalCount = excelHeaderModels.Max(c => c.RowStart) + 1;
            }
            Hashtable ht = new Hashtable();
            //给book 创建Sheet
            int sheetIndex = 0;
            string sheetName = "sheet";
            ISheet sheet = book.CreateSheet(sheetName + sheetIndex);
            //在创建的sheet里面创建表头
            CreateHeader(dataReader, book, excelHeaderModels, sheet);
            //写入到book中 逐行
            while (dataReader.Read())
            {
                // 输出的总数量大于每页数量（如65535），换一页写入 2003版本的才会要求限制每sheet放65535条记录
                if (book.GetType() == typeof(HSSFWorkbook))
                {
                    if (totalCount > sheetSize)
                    {
                        //换一页写
                        sheetIndex++;
                        totalCount = 0;
                        sheet = book.CreateSheet(sheetName + sheetIndex);
                        CreateHeader(dataReader, book, excelHeaderModels, sheet);
                    }
                }
                //创建Excel的行这一行是sheet创建的第rowIndex++行
                IRow excelRow = sheet.CreateRow(totalCount);
                for (int i = 0; i < dataReader.FieldCount; i++)
                {
                    //在rowIndex++行的给第j列 写入 dt的第i行第j列的值
                    ICell cell = excelRow.CreateCell(i);
                    string colName = dataReader.GetName(i);
                    object value = dataReader.GetValue(i);
                    if (funcCalc != null)
                    {
                        value = funcCalc(colName, value, dataReader);
                    }
                    SetCellValue(func, cell, value, colName);
                }
                //自动调整单元格大小 有些耗时
                //for (int i = 0; i < dataReader.FieldCount; i++)
                //{
                //    sheet.AutoSizeColumn(i,true);
                //}
                totalCount++;
            }

            // book写入到文件中
            WriteToFile(book, fileName);

            //获取扩展名
            //string fileExt = Path.GetExtension(fileName);
            //using (ZipFile zip = ZipFile.Create(fileName.Replace(fileExt, ".zip")))
            //{
            //    zip.BeginUpdate();
            //    zip.SetComment("这是我的压缩包");
            //    zip.Add(fileName);       //添加一个文件
            //    zip.CommitUpdate();
            //}

            fileName = fileName.Replace(AppDomain.CurrentDomain.BaseDirectory + "", "");
            ht.Add("Code", "1");
            ht.Add("Detail", "数据导出完毕，请下载。");
            ht.Add("fileName", fileName);
            ht.Add("Count", totalCount);
            return ht;
        }

WriteToFile 将book写入到文件的方法,注意，这里使用了 ms.ToArray()。

/// <summary>
        /// Excel book写入到Excel文件
        /// </summary>
        /// <param name="book"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        private bool WriteToFile(IWorkbook book, string fileName)
        {
            bool result = true;
            try
            {
                using (MemoryStream ms = new MemoryStream())
                {
                    string filePath = Path.GetDirectoryName(fileName);
                    if (filePath != null && !Directory.Exists(filePath))
                    {
                        Directory.CreateDirectory(filePath);
                    }
                    book.Write(ms);
                    var buf = ms.ToArray();
                    using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
                    {
                        fs.Write(buf, 0, buf.Length);
                        fs.Flush();
                    }
                }
            }
            catch (Exception e)
            {
                LogHelper.WriteErrorLog(e.Message, e);
                result = false;
            }
            finally
            {
                GC.Collect();
            }
            return result;
        }

ExcelHeaderModel 是定义的表头实体，支持自定义多表头，如下：

/// <summary>
    /// 导出配置
    ///
    /// 修改纪录
    ///
    /// 2015-12-19版本：1.0 SongBiao 创建文件。    
    ///
    /// <author>
    ///     <name>SongBiao</name>
    ///     <date>2015-12-19</date>
    /// </author>
    /// </summary>
    [Serializable]
    public class ExcelHeaderModel
    {
        /// <summary>
        /// 数据库里面的字段名称
        /// </summary>
        public string SrcName { get; set; }

        /// <summary>
        /// 名称
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 行开始
        /// </summary>
        public int RowStart { get; set; }

        /// <summary>
        /// 行结束
        /// </summary>
        public int RowEnd { get; set; }

        /// <summary>
        /// 列开始
        /// </summary>
        public int ColStart { get; set; }

        /// <summary>
        /// 列结束
        /// </summary>
        public int ColEnd { get; set; }

        /// <summary>
        /// 是否显示在表头 有些字段不需要显示 但是需要用来做计算
        /// </summary>
        [DefaultValue(false)]
        public bool Hidden { get; set; }
    }

Controller中的业务调用

/// <summary>
        /// 导出操作,支持导出当前页,导出全部,导出文件格式excel2003,excel2007及以上
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pager"></param>
        /// <param name="sort"></param>
        /// <param name="direction"></param>
        /// <param name="isPage">true:导出当前页,false:导出全部</param>
        /// <returns></returns>
        [CustomerResource("ExportData")]
        public ActionResult ExportData(PriceSearchEntity searchModel, QuiGridPager pager, string sort, string direction,bool isPage)
        {
            PriceManager priceManager = new PriceManager(BusinessDbHelper, UserInfo);
            // 使用DataReader导出
            IDataReader dr ;
            //要导出的字段
            string selectField = GetExportField();
            string tableName = PriceEntity.TableName + " A";
            // 导出当前页
            if (isPage)
            {
                dr = priceManager.ExecuteReaderByPage(searchModel,tableName, pager, sort, direction, selectField);
            }
            else
            {
                dr = priceManager.ExecuteReader(searchModel, tableName, sort, direction, selectField);
            }
            using (dr)
            {
                IWorkbook book = new HSSFWorkbook();
                // 2007的导出
                // IWorkbook book = new XSSFWorkbook();//.xlsx

                string fileName = UserInfo.NickName+"_数据导出-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";

                // GetSelectField()
                // 导出的文件自动压缩
                // var hashTable = new ExportHelper().DataWrite2Excel(dr, book, fileName, 5, 65530, null, null, null);

                //一般用这个
                var hashTable = new ExportHelper().CommonDataWrite2Sheet(dr, book, fileName, 65530);
                hashTable.Add("TotalTime", CalcTimeSpan(StartTime));
                return Json(hashTable, JsonRequestBehavior.AllowGet);
            }
        }

这里使用了通用权限系统的权限控制功能及底层的数据访问功能，解决导出权限控制的问题。

代码里可根据需要控制输出的文件使用Excel2003还是Excel2007的文件格式。

前端导出完成展示的效果：