asp.net 利用NPOI匯出Excel通用類

解決中文檔名儲存Excel亂碼問題，主要是判斷火狐或者IE瀏覽器，然後做對應的判斷處理，核心程式碼如下：

 System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
 //設定下載的Excel檔名\
 if (System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1)
 {
        //火狐瀏覽器      
        System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "=?UTF-8?B?" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(fileName)) + "?="));
}
else
{
       //IE等瀏覽器
       System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)));
}

廢話不多說，直接上類庫程式碼，ATNPOIHelper.cs：

using System;
using System.Linq;
using System.Web;
using System.IO;
using NPOI;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using System.Collections.Generic;
using System.Text;

namespace AT.Utility.DotNetFile
{
    /*
    匯出Excel包含的功能： 
    1.多表頭匯出最多支援到三行，表頭格式說明 
    相鄰父列頭之間用’#’分隔,父列頭與子列頭用空格(’ ‘)分隔,相鄰子列頭用逗號分隔(‘,’) 
    兩行：序號#分公司#組別#本日成功簽約單數 預警,續約,流失,合計#累計成功簽約單數 預警,續約,流失,合計#任務數#完成比例#排名 
    三行：等級#級別#上期結存 件數,重量,比例#本期調入 收購調入 件數,重量,比例#本期發出 車間投料 件數,重量,比例#本期發出 產品外銷百分比 件數,重量,比例#平均值 
    三行時請注意：列頭要重複 
    2.新增表頭標題功能 
    3.新增序號功能 
    4.根據資料設定列寬

    缺陷： 
    資料內容不能合併列合併行

    改進思路： 
    新增一屬性：設定要合併的列，為了實現多列合併可以這樣設定{“列1,列2”,”列4”}
        */
    /// <summary>
    /// 利用NPOI實現匯出Excel
    /// </summary>
    public class ATNPOIHelper
    {

        #region 初始化

        /// <summary>
        /// 宣告 HSSFWorkbook 物件
        /// </summary>
        private static HSSFWorkbook _workbook;

        /// <summary>
        /// 宣告 HSSFSheet 物件
        /// </summary>
        private static HSSFSheet _sheet;

        #endregion

        #region Excel匯出

        /// <summary>
        /// Excel匯出
        /// </summary>
        /// <param name="fileName">檔名稱 如果為空或NULL，則預設“新建Excel.xls”</param>
        /// <param name="list"></param>
        /// <param name="ColMergeNum">合計：末行合計時，合併的列數</param>
        /// <param name="method">匯出方式 1：WEB匯出（預設）2：按檔案路徑匯出</param>
        /// <param name="filePath">檔案路徑 如果WEB匯出，則可以為空；如果按檔案路徑匯出，則預設桌面路徑</param>
        public static void Export(string fileName, IList<NPOIModel> list, int ColMergeNum, int method = 1, string filePath = null)
        {
            // 檔名稱
            if (!string.IsNullOrEmpty(fileName))
            {
                if (fileName.IndexOf('.') == -1)
                {
                    fileName += ".xls";
                }
                else
                {
                    fileName = fileName.Substring(1, fileName.IndexOf('.')) + ".xls";
                }
            }
            else
            {
                fileName = "新建Excel.xls";
            }
            // 檔案路徑
            if (2 == method && string.IsNullOrEmpty(filePath))
            {
                filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            }
            // 呼叫匯出處理程式
            Export(list, ColMergeNum);
            // WEB匯出
            if (1 == method)
            {
                System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                //設定下載的Excel檔名\
                if (System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1)
                {
                    //火狐瀏覽器      
                    System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "=?UTF-8?B?" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(fileName)) + "?="));
                }
                else
                {
                    //IE等瀏覽器
                    System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)));
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    //將工作簿的內容放到記憶體流中
                    _workbook.Write(ms);
                    //將記憶體流轉換成位元組陣列傳送到客戶端
                    System.Web.HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
                    System.Web.HttpContext.Current.Response.End();
                    _sheet = null;
                    _workbook = null;
                }
            }
            else if (2 == method)
            {
                using (FileStream fs = File.Open(filePath, FileMode.Append))
                {
                    _workbook.Write(fs);
                    _sheet = null;
                    _workbook = null;
                }
            }
        }

        /// <summary>
        /// 匯出方法實現
        /// </summary>
        /// <param name="list"></param>
        private static void Export(IList<NPOIModel> list, int ColMergeNum)
        {

            #region 變數宣告

            // 初始化
            _workbook = new HSSFWorkbook();
            // 宣告 Row 物件
            IRow _row;
            // 宣告 Cell 物件
            ICell _cell;
            // 總列數
            int cols = 0;
            // 總行數
            int rows = 0;
            // 行數計數器
            int rowIndex = 0;
            // 單元格值
            string drValue = null;

            #endregion

            foreach (NPOIModel model in list)
            {
                // 工作薄命名
                if (model.sheetName != null)
                    _sheet = (HSSFSheet)_workbook.CreateSheet(model.sheetName);
                else
                    _sheet = (HSSFSheet)_workbook.CreateSheet();

                // 獲取資料來源
                DataTable dt = model.dataSource;
                // 初始化
                rowIndex = 0;
                // 獲取總行數
                rows = GetRowCount(model.headerName);
                // 獲取總列數
                cols = GetColCount(model.headerName);

                //合計：合併表格末行N列,rows為表頭行數，dt.Rows.Count為資料行數
                if (ColMergeNum > 1)
                {
                    CellRangeAddress region_Merge = new CellRangeAddress(rows + dt.Rows.Count, rows + dt.Rows.Count, 0, ColMergeNum - 1);
                    _sheet.AddMergedRegion(region_Merge);
                }

                ICellStyle myBodyStyle = bodyStyle;
                ICellStyle myTitleStyle = titleStyle;
                ICellStyle myDateStyle = dateStyle;
                ICellStyle myBodyRightStyle = bodyRightStyle;
                // 迴圈行數
                foreach (DataRow row in dt.Rows)
                {

                    #region 新建表，填充表頭，填充列頭，樣式

                    if (rowIndex == 65535 || rowIndex == 0)
                    {
                        if (rowIndex != 0)
                            _sheet = (HSSFSheet)_workbook.CreateSheet();

                        // 構建行
                        for (int i = 0; i < rows + model.isTitle; i++)
                        {
                            _row = _sheet.GetRow(i);
                            // 建立行
                            if (_row == null)
                                _row = _sheet.CreateRow(i);

                            for (int j = 0; j < cols; j++)
                                _row.CreateCell(j).CellStyle = myBodyStyle;
                        }

                        // 如果存在表標題
                        if (model.isTitle > 0)
                        {
                            // 獲取行
                            _row = _sheet.GetRow(0);
                            // 合併單元格
                            CellRangeAddress region = new CellRangeAddress(0, 0, 0, (cols - 1));
                            _sheet.AddMergedRegion(region);
                            // 填充值
                            _row.CreateCell(0).SetCellValue(model.tableTitle);
                            // 設定樣式
                            _row.GetCell(0).CellStyle = myTitleStyle;
                            // 設定行高
                            _row.HeightInPoints = 20;
                        }

                        // 取得上一個實體
                        NPOIHeader lastRow = null;
                        IList<NPOIHeader> hList = GetHeaders(model.headerName, rows, model.isTitle);
                        // 建立表頭
                        foreach (NPOIHeader m in hList)
                        {
                            var data = hList.Where(c => c.firstRow == m.firstRow && c.lastCol == m.firstCol - 1);
                            if (data.Count() > 0)
                            {
                                lastRow = data.First();
                                if (m.headerName == lastRow.headerName)
                                    m.firstCol = lastRow.firstCol;
                            }

                            // 獲取行
                            _row = _sheet.GetRow(m.firstRow);
                            // 合併單元格
                            CellRangeAddress region = new CellRangeAddress(m.firstRow, m.lastRow, m.firstCol, m.lastCol);

                            _sheet.AddMergedRegion(region);
                            // 填充值
                            _row.CreateCell(m.firstCol).SetCellValue(m.headerName);
                        }
                        // 填充表頭樣式
                        for (int i = 0; i < rows + model.isTitle; i++)
                        {
                            _row = _sheet.GetRow(i);
                            for (int j = 0; j < cols; j++)
                            {
                                _row.GetCell(j).CellStyle = myBodyStyle;
                                //設定列寬
                                _sheet.SetColumnWidth(j, (model.colWidths[j] + 1) * 450);
                            }
                        }

                        rowIndex = (rows + model.isTitle);
                    }

                    #endregion

                    #region 填充內容

                    // 構建列
                    _row = _sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in dt.Columns)
                    {
                        // 新增序號列
                        if (1 == model.isOrderby && column.Ordinal == 0)
                        {
                            _cell = _row.CreateCell(0);
                            _cell.SetCellValue(rowIndex - rows);
                            _cell.CellStyle = myBodyStyle;
                        }

                        // 建立列
                        _cell = _row.CreateCell(column.Ordinal + model.isOrderby);

                        // 獲取值
                        drValue = row[column].ToString();

                        switch (column.DataType.ToString())
                        {
                            case "System.String"://字串型別
                                _cell.SetCellValue(drValue);
                                _cell.CellStyle = myBodyStyle;
                                break;
                            case "System.DateTime"://日期型別
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                _cell.SetCellValue(dateV);

                                _cell.CellStyle = myDateStyle;//格式化顯示
                                break;
                            case "System.Boolean"://布林型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                _cell.SetCellValue(boolV);
                                _cell.CellStyle = myBodyStyle;
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                _cell.SetCellValue(intV);
                                _cell.CellStyle = myBodyRightStyle;
                                break;
                            case "System.Decimal"://浮點型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                _cell.SetCellValue(doubV.ToString("f2"));
                                _cell.CellStyle = myBodyRightStyle;
                                break;
                            case "System.DBNull"://空值處理
                                _cell.SetCellValue("");
                                break;
                            default:
                                _cell.SetCellValue("");
                                break;
                        }

                    }

                    #endregion

                    rowIndex++;
                }
            }
        }

        #region 輔助方法

        /// <summary>
        /// 表頭解析
        /// </summary>
        /// <remarks>
        /// </remarks>
        /// <param name="header">表頭</param>
        /// <param name="rows">總行數</param>
        /// <param name="addRows">外加行</param>
        /// <param name="addCols">外加列</param>
        /// <returns></returns>
        private static IList<NPOIHeader> GetHeaders(string header, int rows, int addRows)
        {
            // 臨時表頭陣列
            string[] tempHeader;
            string[] tempHeader2;
            // 所跨列數
            int colSpan = 0;
            // 所跨行數
            int rowSpan = 0;
            // 單元格物件
            NPOIHeader model = null;
            // 行數計數器
            int rowIndex = 0;
            // 列數計數器
            int colIndex = 0;
            // 
            IList<NPOIHeader> list = new List<NPOIHeader>();
            // 初步解析
            string[] headers = header.Split(new string[] { "#" }, StringSplitOptions.RemoveEmptyEntries);
            // 表頭遍歷
            for (int i = 0; i < headers.Length; i++)
            {
                // 行數計數器清零
                rowIndex = 0;
                // 列數計數器清零
                colIndex = 0;
                // 獲取所跨行數
                rowSpan = GetRowSpan(headers[i], rows);
                // 獲取所跨列數
                colSpan = GetColSpan(headers[i]);

                // 如果所跨行數與總行數相等，則不考慮是否合併單元格問題
                if (rows == rowSpan)
                {
                    colIndex = GetMaxCol(list);
                    model = new NPOIHeader(headers[i],
                        addRows,
                        (rowSpan - 1 + addRows),
                        colIndex,
                        (colSpan - 1 + colIndex),
                        addRows);
                    list.Add(model);
                    rowIndex += (rowSpan - 1) + addRows;
                }
                else
                {
                    // 列索引
                    colIndex = GetMaxCol(list);
                    // 如果所跨行數不相等，則考慮是否包含多行
                    tempHeader = headers[i].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
                    for (int j = 0; j < tempHeader.Length; j++)
                    {

                        // 如果總行數=陣列長度
                        if (1 == GetColSpan(tempHeader[j]))
                        {
                            if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
                            {
                                model = new NPOIHeader(tempHeader[j],
                                    (j + addRows),
                                    (j + addRows) + (rows - tempHeader.Length),
                                    colIndex,
                                    (colIndex + colSpan - 1),
                                    addRows);
                                list.Add(model);
                            }
                            else
                            {
                                model = new NPOIHeader(tempHeader[j],
                                        (j + addRows),
                                        (j + addRows),
                                        colIndex,
                                        (colIndex + colSpan - 1),
                                        addRows);
                                list.Add(model);
                            }
                        }
                        else
                        {
                            // 如果所跨列數不相等，則考慮是否包含多列
                            tempHeader2 = tempHeader[j].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
                            for (int m = 0; m < tempHeader2.Length; m++)
                            {
                                // 列索引
                                colIndex = GetMaxCol(list) - colSpan + m;
                                if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
                                {
                                    model = new NPOIHeader(tempHeader2[m],
                                        (j + addRows),
                                        (j + addRows) + (rows - tempHeader.Length),
                                        colIndex,
                                        colIndex,
                                        addRows);
                                    list.Add(model);
                                }
                                else
                                {
                                    model = new NPOIHeader(tempHeader2[m],
                                            (j + addRows),
                                            (j + addRows),
                                            colIndex,
                                            colIndex,
                                            addRows);
                                    list.Add(model);
                                }
                            }
                        }
                        rowIndex += j + addRows;
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 獲取最大列
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        private static int GetMaxCol(IList<NPOIHeader> list)
        {
            int maxCol = 0;
            if (list.Count > 0)
            {
                foreach (NPOIHeader model in list)
                {
                    if (maxCol < model.lastCol)
                        maxCol = model.lastCol;
                }
                maxCol += 1;
            }

            return maxCol;
        }

        /// <summary>
        /// 獲取表頭行數
        /// </summary>
        /// <param name="newHeaders">表頭文字</param>
        /// <returns></returns>
        private static int GetRowCount(string newHeaders)
        {
            string[] ColumnNames = newHeaders.Split(new char[] { '@' });
            int Count = 0;
            if (ColumnNames.Length <= 1)
                ColumnNames = newHeaders.Split(new char[] { '#' });
            foreach (string name in ColumnNames)
            {
                int TempCount = name.Split(new char[] { ' ' }).Length;
                if (TempCount > Count)
                    Count = TempCount;
            }
            return Count;
        }

        /// <summary>
        /// 獲取表頭列數
        /// </summary>
        /// <param name="newHeaders">表頭文字</param>
        /// <returns></returns>
        private static int GetColCount(string newHeaders)
        {
            string[] ColumnNames = newHeaders.Split(new char[] { '@' });
            int Count = 0;
            if (ColumnNames.Length <= 1)
                ColumnNames = newHeaders.Split(new char[] { '#' });
            Count = ColumnNames.Length;
            foreach (string name in ColumnNames)
            {
                int TempCount = name.Split(new char[] { ',' }).Length;
                if (TempCount > 1)
                    Count += TempCount - 1;
            }
            return Count;
        }

        /// <summary>
        /// 列頭跨列數
        /// </summary>
        /// <remarks>
        /// </remarks>
        /// <param name="newHeaders">表頭文字</param>
        /// <returns></returns>
        private static int GetColSpan(string newHeaders)
        {
            return newHeaders.Split(',').Count();
        }

        /// <summary>
        /// 列頭跨行數
        /// </summary> 
        /// <remarks>
        /// </remarks>
        /// <param name="newHeaders">列頭文字</param>
        /// <param name="rows">表頭總行數</param>
        /// <returns></returns>
        private static int GetRowSpan(string newHeaders, int rows)
        {
            int Count = newHeaders.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).Length;
            // 如果總行數與當前表頭所擁有行數相等
            if (rows == Count)
                Count = 1;
            else if (Count < rows)
                Count = 1 + (rows - Count);
            else
                throw new Exception("表頭格式不正確！");
            return Count;
        }

        #endregion

        #region 單元格樣式

        /// <summary>
        /// 資料單元格樣式
        /// </summary>
        private static ICellStyle bodyStyle
        {
            get
            {
                ICellStyle style = _workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.CENTER; //居中
                style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 
                style.WrapText = true;//自動換行
                // 邊框
                style.BorderBottom = BorderStyle.THIN;
                style.BorderLeft = BorderStyle.THIN;
                style.BorderRight = BorderStyle.THIN;
                style.BorderTop = BorderStyle.THIN;
                // 字型
                //IFont font = _workbook.CreateFont();
                //font.FontHeightInPoints = 10;
                //font.FontName = "宋體";
                //style.SetFont(font);

                return style;
            }
        }

        /// <summary>
        /// 資料單元格樣式
        /// </summary>
        private static ICellStyle bodyRightStyle
        {
            get
            {
                ICellStyle style = _workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.RIGHT; //居中
                style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 
                style.WrapText = true;//自動換行
                // 邊框
                style.BorderBottom = BorderStyle.THIN;
                style.BorderLeft = BorderStyle.THIN;
                style.BorderRight = BorderStyle.THIN;
                style.BorderTop = BorderStyle.THIN;
                // 字型
                //IFont font = _workbook.CreateFont();
                //font.FontHeightInPoints = 10;
                //font.FontName = "宋體";
                //style.SetFont(font);

                return style;
            }
        }

        /// <summary>
        /// 標題單元格樣式
        /// </summary>
        private static ICellStyle titleStyle
        {
            get
            {
                ICellStyle style = _workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.CENTER; //居中
                style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 
                style.WrapText = true;//自動換行 

                //IFont font = _workbook.CreateFont();
                //font.FontHeightInPoints = 14;
                //font.FontName = "宋體";
                //font.Boldweight = (short)FontBoldWeight.BOLD;
                //style.SetFont(font);

                return style;
            }
        }

        /// <summary>
        /// 日期單元格樣式
        /// </summary>
        private static ICellStyle dateStyle
        {
            get
            {
                ICellStyle style = _workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.CENTER; //居中
                style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 
                style.WrapText = true;//自動換行
                // 邊框
                style.BorderBottom = BorderStyle.THIN;
                style.BorderLeft = BorderStyle.THIN;
                style.BorderRight = BorderStyle.THIN;
                style.BorderTop = BorderStyle.THIN;
                // 字型
                //IFont font = _workbook.CreateFont();
                //font.FontHeightInPoints = 10;
                //font.FontName = "宋體";
                //style.SetFont(font);

                IDataFormat format = _workbook.CreateDataFormat();
                style.DataFormat = format.GetFormat("yyyy-MM-dd");
                return style;
            }
        }

        #endregion

        #endregion
    }

    /// <summary>
    /// 實體類
    /// </summary>
    public class NPOIModel
    {
        /// <summary>
        /// 資料來源
        /// </summary>
        public DataTable dataSource { get; private set; }
        /// <summary>
        /// 要匯出的資料列陣列
        /// </summary>
        public string[] fileds { get; private set; }
        /// <summary>
        /// 工作薄名稱陣列
        /// </summary>
        public string sheetName { get; private set; }
        /// <summary>
        /// 表標題
        /// </summary>
        public string tableTitle { get; private set; }
        /// <summary>
        /// 表標題是否存在 1：存在 0：不存在
        /// </summary>
        public int isTitle { get; private set; }
        /// <summary>
        /// 是否新增序號
        /// </summary>
        public int isOrderby { get; private set; }
        /// <summary>
        /// 表頭
        /// </summary>
        public string headerName { get; private set; }
        /// <summary>
        /// 取得列寬
        /// </summary>
        public int[] colWidths { get; private set; }
        /// <summary>
        /// 建構函式
        /// </summary>
        /// <remarks>
        /// </remarks>
        /// <param name="dataSource">資料來源 DataTable</param>
        /// <param name="filed">要匯出的欄位，如果為空或NULL，則預設全部</param> 
        /// <param name="sheetName">工作薄名稱</param>
        /// <param name="headerName">表頭名稱 如果為空或NULL，則預設資料列欄位
        /// 相鄰父列頭之間用'#'分隔,父列頭與子列頭用空格(' ')分隔,相鄰子列頭用逗號分隔(',')
        /// 兩行：序號#分公司#組別#本日成功簽約單數 預警,續約,流失,合計#累計成功簽約單數 預警,續約,流失,合計#任務數#完成比例#排名 
        /// 三行：等級#級別#上期結存 件數,重量,比例#本期調入 收購調入 件數,重量,比例#本期發出 車間投料 件數,重量,比例#本期發出 產品外銷百分比 件數,重量,比例#平均值 
        /// 三行時請注意：列頭要重複
        /// </param>
        /// <param name="tableTitle">表標題</param> 
        /// <param name="isOrderby">是否新增序號 0：不新增 1：新增</param>
        public NPOIModel(DataTable dataSource, string filed, string sheetName, string headerName, string tableTitle = null, int isOrderby = 0)
        {
            if (!string.IsNullOrEmpty(filed))
            {
                this.fileds = filed.ToUpper().Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);

                // 移除多餘資料列
                for (int i = dataSource.Columns.Count - 1; i >= 0; i--)
                {
                    DataColumn dc = dataSource.Columns[i];
                    if (!this.fileds.Contains(dataSource.Columns[i].Caption.ToUpper()))
                    {
                        dataSource.Columns.Remove(dataSource.Columns[i]);
                    }
                }

                // 列索引
                int colIndex = 0;
                // 迴圈排序
                for (int i = 0; i < dataSource.Columns.Count; i++)
                {
                    // 獲取索引
                    colIndex = GetColIndex(dataSource.Columns[i].Caption.ToUpper());
                    // 設定下標
                    dataSource.Columns[i].SetOrdinal(colIndex);
                }
            }
            else
            {
                this.fileds = new string[dataSource.Columns.Count];
                for (int i = 0; i < dataSource.Columns.Count; i++)
                {
                    this.fileds[i] = dataSource.Columns[i].ColumnName;
                }
            }
            this.dataSource = dataSource;

            if (!string.IsNullOrEmpty(sheetName))
            {
                this.sheetName = sheetName;
            }
            if (!string.IsNullOrEmpty(headerName))
            {
                this.headerName = headerName;
            }
            else
            {
                this.headerName = string.Join("#", this.fileds);
            }
            if (!string.IsNullOrEmpty(tableTitle))
            {
                this.tableTitle = tableTitle;
                this.isTitle = 1;
            }
            // 取得資料列寬 資料列寬可以和表頭列寬比較，採取最長寬度  
            colWidths = new int[this.dataSource.Columns.Count];
            foreach (DataColumn item in this.dataSource.Columns)
            {
                colWidths[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            // 迴圈比較最大寬度
            for (int i = 0; i < this.dataSource.Rows.Count; i++)
            {
                for (int j = 0; j < this.dataSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(this.dataSource.Rows[i][j].ToString()).Length;
                    if (intTemp > colWidths[j])
                    {
                        colWidths[j] = intTemp;
                    }
                }
            }
            if (isOrderby > 0)
            {
                this.isOrderby = isOrderby;
                this.headerName = "序號#" + this.headerName;
            }
        }

        /// <summary>
        /// 獲取列名下標
        /// </summary>
        /// <param name="colName">列名稱</param>
        /// <returns></returns>
        private int GetColIndex(string colName)
        {
            for (int i = 0; i < this.fileds.Length; i++)
            {
                if (colName == this.fileds[i])
                    return i;
            }
            return 0;
        }
    }

    /// <summary>
    /// 表頭構建類
    /// </summary>
    public class NPOIHeader
    {
        /// <summary>
        /// 表頭
        /// </summary>
        public string headerName { get; set; }
        /// <summary>
        /// 起始行
        /// </summary>
        public int firstRow { get; set; }
        /// <summary>
        /// 結束行
        /// </summary>
        public int lastRow { get; set; }
        /// <summary>
        /// 起始列
        /// </summary>
        public int firstCol { get; set; }
        /// <summary>
        /// 結束列
        /// </summary>
        public int lastCol { get; set; }
        /// <summary>
        /// 是否跨行
        /// </summary>
        public int isRowSpan { get; private set; }
        /// <summary>
        /// 是否跨列
        /// </summary>
        public int isColSpan { get; private set; }
        /// <summary>
        /// 外加行
        /// </summary>
        public int rows { get; set; }

        public NPOIHeader() { }
        /// <summary>
        /// 建構函式
        /// </summary>
        /// <param name="headerName">表頭</param>
        /// <param name="firstRow">起始行</param>
        /// <param name="lastRow">結束行</param>
        /// <param name="firstCol">起始列</param>
        /// <param name="lastCol">結束列</param>
        /// <param name="rows">外加行</param>
        /// <param name="cols">外加列</param>
        public NPOIHeader(string headerName, int firstRow, int lastRow, int firstCol, int lastCol, int rows = 0)
        {
            this.headerName = headerName;
            this.firstRow = firstRow;
            this.lastRow = lastRow;
            this.firstCol = firstCol;
            this.lastCol = lastCol;
            // 是否跨行判斷
            if (firstRow != lastRow)
                isRowSpan = 1;
            if (firstCol != lastCol)
                isColSpan = 1;

            this.rows = rows;
        }
    }
}

3、匯出程式碼示例如下：

        /// <summary>
        /// 匯出測點列表表格
        /// </summary>
        [HttpGet]
        [AllowAnonymous]
        public void ExportMeasurePointData(string TreeID, string TreeType)
        {
            DataTable dtResult = new DataTable();
            DataTable dtExcel = new DataTable();
            try
            {
                string sql = string.Format("EXEC P_GET_ZXJG_TagList '{0}','{1}'", TreeID, TreeType);
                dtResult = QuerySQL.GetDataTable(sql);
                dtExcel = dtResult.Copy();
                dtExcel.Columns.Add("xuhao", typeof(string));
                dtExcel.Columns.Add("StrValueTime", typeof(string));
                dtExcel.Columns["xuhao"].SetOrdinal(0);
                dtExcel.Columns["StrValueTime"].SetOrdinal(2);
                for (int i = 0; i < dtResult.Rows.Count; i++)
                {
                    dtExcel.Rows[i]["xuhao"] = (i + 1).ToString();
                    dtExcel.Rows[i]["StrValueTime"] = Convert.ToDateTime(dtResult.Rows[i]["F_ValueTime"]).ToString("yyyy-MM-dd HH:mm:ss");
                }
                List<NPOIModel> list = new List<NPOIModel>();
                list.Add(new NPOIModel(dtExcel, "xuhao;F_Description;StrValueTime;F_Value;F_Unit;F_AlmLow;F_AlmUp", "sheet", "序號#監測點#採集時間#當前數值#工程單位#報警下限#報警上限"));
                ATNPOIHelper.Export("測點列表", list, 0);
            }
            catch (Exception ex)
            {

            }
        }