將數(shù)據(jù)導(dǎo)出到Excel，供用戶下載是經(jīng)常要使用到的一個(gè)功能，我這里進(jìn)行了一些封裝，目前已滿足項(xiàng)目中導(dǎo)出的需求，使用DataReader導(dǎo)出Excel，支持自定義多表頭，使用委托處理字段格式化及字段值的計(jì)算；

導(dǎo)出執(zhí)行的代碼如下：

/// <summary>
        /// 將DataReader數(shù)據(jù)寫入到 excel book 中
        /// 一般數(shù)據(jù)量小的導(dǎo)出用這個(gè)就可以啦
        /// </summary>
        /// <param name="dataReader"></param>
        /// <param name="book">Excel Book</param>
        /// <param name="excelName">excelName文件名字</param>
        /// <param name="sheetSize">每個(gè)Sheet放多少條記錄</param>
        /// <param name="excelHeaderModels">Excel表頭實(shí)體，Select字段可以從這里獲取</param>
        /// <param name="func">字段格式化委托函數(shù)</param>
        /// <param name="funcCalc">字段的值計(jì)算委托函數(shù)</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);
            //創(chuàng)建表頭
            int totalCount = 1;
            //表頭的要去掉 否則默認(rèn)的是65535條記錄 一個(gè)表頭 所以從1開始
            if (excelHeaderModels != null)
            {
                totalCount = excelHeaderModels.Max(c => c.RowStart) + 1;
            }
            Hashtable ht = new Hashtable();
            //給book 創(chuàng)建Sheet
            int sheetIndex = 0;
            string sheetName = "sheet";
            ISheet sheet = book.CreateSheet(sheetName + sheetIndex);
            //在創(chuàng)建的sheet里面創(chuàng)建表頭
            CreateHeader(dataReader, book, excelHeaderModels, sheet);
            //寫入到book中 逐行
            while (dataReader.Read())
            {
                // 輸出的總數(shù)量大于每頁數(shù)量（如65535），換一頁寫入 2003版本的才會(huì)要求限制每sheet放65535條記錄
                if (book.GetType() == typeof(HSSFWorkbook))
                {
                    if (totalCount > sheetSize)
                    {
                        //換一頁寫
                        sheetIndex++;
                        totalCount = 0;
                        sheet = book.CreateSheet(sheetName + sheetIndex);
                        CreateHeader(dataReader, book, excelHeaderModels, sheet);
                    }
                }
                //創(chuàng)建Excel的行這一行是sheet創(chuàng)建的第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);
                }
                //自動(dòng)調(diào)整單元格大小 有些耗時(shí)
                //for (int i = 0; i < dataReader.FieldCount; i++)
                //{
                //    sheet.AutoSizeColumn(i,true);
                //}
                totalCount++;
            }

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

            //獲取擴(kuò)展名
            //string fileExt = Path.GetExtension(fileName);
            //using (ZipFile zip = ZipFile.Create(fileName.Replace(fileExt, ".zip")))
            //{
            //    zip.BeginUpdate();
            //    zip.SetComment("這是我的壓縮包");
            //    zip.Add(fileName);       //添加一個(gè)文件
            //    zip.CommitUpdate();
            //}

            fileName = fileName.Replace(AppDomain.CurrentDomain.BaseDirectory + "", "");
            ht.Add("Code", "1");
            ht.Add("Detail", "數(shù)據(jù)導(dǎo)出完畢，請(qǐng)下載。");
            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 是定義的表頭實(shí)體，支持自定義多表頭，如下：

/// <summary>
    /// 導(dǎo)出配置
    ///
    /// 修改紀(jì)錄
    ///
    /// 2015-12-19版本：1.0 SongBiao 創(chuàng)建文件。    
    ///
    /// <author>
    ///     <name>SongBiao</name>
    ///     <date>2015-12-19</date>
    /// </author>
    /// </summary>
    [Serializable]
    public class ExcelHeaderModel
    {
        /// <summary>
        /// 數(shù)據(jù)庫里面的字段名稱
        /// </summary>
        public string SrcName { get; set; }

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

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

        /// <summary>
        /// 行結(jié)束
        /// </summary>
        public int RowEnd { get; set; }

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

        /// <summary>
        /// 列結(jié)束
        /// </summary>
        public int ColEnd { get; set; }

        /// <summary>
        /// 是否顯示在表頭 有些字段不需要顯示 但是需要用來做計(jì)算
        /// </summary>
        [DefaultValue(false)]
        public bool Hidden { get; set; }
    }

Controller中的業(yè)務(wù)調(diào)用

/// <summary>
        /// 導(dǎo)出操作,支持導(dǎo)出當(dāng)前頁,導(dǎo)出全部,導(dǎo)出文件格式excel2003,excel2007及以上
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pager"></param>
        /// <param name="sort"></param>
        /// <param name="direction"></param>
        /// <param name="isPage">true:導(dǎo)出當(dāng)前頁,false:導(dǎo)出全部</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導(dǎo)出
            IDataReader dr ;
            //要導(dǎo)出的字段
            string selectField = GetExportField();
            string tableName = PriceEntity.TableName + " A";
            // 導(dǎo)出當(dāng)前頁
            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的導(dǎo)出
                // IWorkbook book = new XSSFWorkbook();//.xlsx

                string fileName = UserInfo.NickName+"_數(shù)據(jù)導(dǎo)出-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";

                // GetSelectField()
                // 導(dǎo)出的文件自動(dòng)壓縮
                // var hashTable = new ExportHelper().DataWrite2Excel(dr, book, fileName, 5, 65530, null, null, null);

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

這里使用了通用權(quán)限系統(tǒng)的權(quán)限控制功能及底層的數(shù)據(jù)訪問功能，解決導(dǎo)出權(quán)限控制的問題。

代碼里可根據(jù)需要控制輸出的文件使用Excel2003還是Excel2007的文件格式。

前端導(dǎo)出完成展示的效果：