GridView匯出Excel(NPOI篇)[1]

using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Reflection;
using System.Text;
using EBIZ.Common.UI;
using System.Text.RegularExpressions;

namespace EBIZ.Common.Utility
{
    /// <summary>
    /// 輸出資料
    /// </summary>
    public class ExportData
    {
        private bool _enableShowHiddenField = false;

        private HSSFWorkbook _workBook;

        private GridView _gvData;

        private Dictionary<Color, IFont> _fontColors;

        private Dictionary<string, ICellStyle> _cellStyles;

        private Dictionary<int, short> _cellDataFormat;

        private Dictionary<string, short> _dataFormat;

        /// <summary>
        /// 是否充許顯示隱藏欄位
        /// </summary>
        public bool EnableShowHiddenField
        {
            set { _enableShowHiddenField = value; }
            get { return _enableShowHiddenField; }
        }

        /// <summary>
        /// 匯出資料建構式
        /// </summary>
        public ExportData()
            : this(false)
        {
        }

        /// <summary>
        /// 匯出資料建構式
        /// </summary>
        /// <param name="showHiddenField">是否顯示隱藏欄位</param>
        public ExportData(bool showHiddenField)
        {
            EnableShowHiddenField = showHiddenField;

            _workBook = new HSSFWorkbook();

            _fontColors = new Dictionary<Color, IFont>();

            _cellStyles = new Dictionary<string, ICellStyle>();

            _cellDataFormat = new Dictionary<int, short>();

            _dataFormat = new Dictionary<string, short>();
        }

/// <summary>
        /// 將GridView匯出為MemoryStream
        /// </summary>
        /// <param name="gvData">GridView object</param>
        /// <returns>MemoryStream object</returns>
        public MemoryStream ExportGridViewToMemoryStream(GridView gvData)
        {
            return ExportGridViewToMemoryStream(gvData, "Sheet1", null);
        }

        /// <summary>
        /// 將GridView匯出為MemoryStream
        /// </summary>
        /// <param name="gvData">GridView object</param>
        /// <param name="sheetName">Excel Sheet Name</param>
        /// <param name="picBytes">圖片二進制數據bytes</param>
        /// <returns>MemoryStream object</returns>
        public MemoryStream ExportGridViewToMemoryStream(GridView gvData, string sheetName, object picBytes)
        {
            MemoryStream stream = null;

            try
            {
                if (gvData != null)
                {
                    _gvData = gvData;

                    RemovePager(_gvData);

                    RenderDataToText();

                    stream = new MemoryStream();

                    ISheet sheet = _workBook.CreateSheet(sheetName);

                    // 轉出Header
                    RenderHeaderRow(sheet);

                    // 轉出Data
                    RenderDataRow(sheet);

                    // 轉出Footer
                    RenderFooterRow(sheet);

                    // 設定欄位寬度
                    SetAutoSizeColumn(sheet, gvData);

                    if (picBytes != null)
                    {
                        int pictureIdx = _workBook.AddPicture((byte[])picBytes, PictureType.JPEG);

                        // Create the drawing patriarch.  This is the top level container for all shapes.
                        IDrawing patriarch = sheet.CreateDrawingPatriarch();

                        //add a picture
                        IClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, _gvData.Rows.Count + 2, 1, 3);
                        IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);

                        pict.Resize();
                    }

                    _workBook.Write(stream);

                    stream.Flush();

                    stream.Position = 0;
                }
            }
            catch (Exception ex)
            {
                Log.Log.Write(ex);

                throw;
            }

            return stream;
        }