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;

        }