### 目前適用於WebForm，MVC的話在最後Response處需稍做調整

#### 只要傳List&Class或DataTable就能透過NPOI轉成報表

##### 用法
※List或DataTable通常會由Dapper或SQLCommand撈出資料

1. 傳入List包含泛型Class輸出Excel

List<Vw_CarWatch> vw_Cars = new List<Vw_CarWatch>();
NPOIHelper.ExportExcel<Vw_CarWatch>(vw_Cars, "車牌辨識清冊.xlsx");

Class要包含DisplayName Attribute，Excel才會輸出
(標題DisplayName為必要，若欄位沒有要在Excel上輸出則不加)

[DisplayName("車牌辨識清冊")]
public partial class Vw_CarWatch
{
    public long ID { get; set; }

    [DisplayName("進場時間")]
    public DateTime InsTime { get; set; }

    [DisplayName("辨識車號")]
    public string CarNo { get; set; }

    [DisplayName("辨識車尾")]
    public string TailNo { get; set; }

    public string FileName { get; set; }
}

2. 傳入DataTable輸出Excel

DataTable dt = new DataTable();
NPOIHelper.ExportDataTableToExcelNPOI(dt, "進出車輛清單.xlsx");

3. 傳入GridView輸出Excel(WebForm限定)

NPOIHelper.ExportGridViewToExcelNPOI(gv_DataList,"車輛清單");

##### 原始碼

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.UI.WebControls;

/// <summary>
/// NPOIHelper 的摘要描述
/// </summary>
public static class NPOIHelper
{
    public static void ExportExcel<T>(this IEnumerable<T> dataList, string fileName)
    {
        //Create workbook
        var datatype = typeof(T);
        var workbook = new XSSFWorkbook();
        var worksheet = workbook.CreateSheet(string.Format("{0}", datatype.GetDisplayName()));

        //Insert titles
        var row = worksheet.CreateRow(0);
        var titleList = datatype.GetPropertyDisplayNames();
        for (int i = 0; i < titleList.Count; i++)
        {
            row.CreateCell(i).SetCellValue(titleList[i]);
        }

        //Insert data values
        for (int i = 1; i < dataList.Count() + 1; i++)
        {
            var tmpRow = worksheet.CreateRow(i);
            var valueList = dataList.ElementAt(i - 1).GetPropertyValues(titleList);

            for (int j = 0; j < valueList.Count; j++)
            {
                DateTime dt = new DateTime();
                if (DateTime.TryParse(valueList[j], out dt))
                {
                    //Excel日期、時間轉換
                    var _cell = tmpRow.CreateCell(j);
                    XSSFCellStyle styleDate = (XSSFCellStyle)workbook.CreateCellStyle();
                    XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
                    styleDate.DataFormat = valueList[j].Split(':').Count() > 1
                        ? format.GetFormat("HH:mm") //時間
                        : format.GetFormat("yyyy/mm/dd");//日期
                    _cell.CellStyle = styleDate;
                    _cell.SetCellValue(dt);//日期
                }
                else
                    tmpRow.CreateCell(j).SetCellValue(valueList[j]);
            }
        }
        //Set OK AutoSize
        for (int i = 0; i < titleList.Count; i++)
        {
            worksheet.AutoSizeColumn(i);
        }
        using (MemoryStream MS = new MemoryStream())
        {
            workbook.Write(MS);
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
            System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            System.Web.HttpContext.Current.Response.BinaryWrite(MS.ToArray());
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.End();
        }

    }

    private static string GetDisplayName(this MemberInfo memberInfo)
    {
        var titleName = string.Empty;

        //Try get DisplayName
        var attribute = memberInfo.GetCustomAttributes(typeof(DisplayNameAttribute), false).FirstOrDefault();
        if (attribute != null)
        {
            titleName = (attribute as DisplayNameAttribute).DisplayName;
        }
        //If no DisplayName
        else
        {
            //Pass Display
            titleName = "";//memberInfo.Name;
        }

        return titleName;
    }

    private static List<string> GetPropertyDisplayNames(this Type type)
    {
        var titleList = new List<string>();
        var propertyInfos = type.GetProperties();

        foreach (var propertyInfo in propertyInfos)
        {
            var titleName = propertyInfo.GetDisplayName();
            if (!string.IsNullOrWhiteSpace(titleName))
                titleList.Add(titleName);
        }

        return titleList;
    }

    private static List<string> GetPropertyValues<T>(this T data, List<string> titleList)
    {
        var propertyValues = new List<string>();
        var propertyInfos = data.GetType().GetProperties();

        foreach (var propertyInfo in propertyInfos)
        {
            //判斷Class是否有DisplayName
            //與標題交叉比對(LINQ)
            if (propertyInfo.CustomAttributes.Count() > 0
                && titleList.Any(x => x.ToString() == propertyInfo.GetDisplayName()))
            {
                var Value = propertyInfo.GetValue(data, null);
                if (Value == null)
                    Value = "";
                propertyValues.Add(Value.ToString());
            }
        }

        return propertyValues;
    }
    public static void ExportDataTableToExcelNPOI(DataTable pDataTable, string strExportFileName, string SheetName = "")
    {
        int tRowCount = pDataTable.Rows.Count;
        int tColumnCount = pDataTable.Columns.Count;

        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        XSSFWorkbook workbook = new XSSFWorkbook();

        ISheet u_sheet = workbook.CreateSheet(string.IsNullOrWhiteSpace(SheetName) ? "工作表1" : SheetName);

        IRow u_row1 = u_sheet.CreateRow(0);

        XSSFCellStyle styleRow1 = (XSSFCellStyle)workbook.CreateCellStyle();
        for (int i = 1; i <= pDataTable.Columns.Count - 1; i++)
        {
            u_row1.CreateCell(i - 1).SetCellValue(pDataTable.Columns[i].ColumnName);
        }


        for (int j = 0; j < tRowCount; j++)
        {
            int x = 1;
            IRow u_row = u_sheet.CreateRow(j + 1);


            for (int k = 0; k < tColumnCount - 1; k++)
            {
                u_sheet.AutoSizeColumn(j); //自動調整欄寬 

                if (x != 5)
                {
                    u_row.CreateCell(k).SetCellValue(pDataTable.Rows[j][x].ToString().Replace("&nbsp;", ""));

                    x++;
                }
            }
        }

        //== 輸出Excel 2007檔案。==============================
        MemoryStream MS = new MemoryStream();   //==需要 System.IO命名空間
        workbook.Write(MS);

        //== Excel檔名，請寫在最後面 filename的地方
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + strExportFileName);
        HttpContext.Current.Response.BinaryWrite(MS.ToArray());

        //== 釋放資源
        workbook = null;   //== VB為 Nothing
        MS.Close();
        MS.Dispose();

        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.End();
    }

    /// <summary>
    /// GridView下載
    /// </summary>
    /// <param name="gv"></param>
    /// <param name="exportFileName"></param>
    /// <param name="sheetName"></param>
    public static void ExportGridViewToExcelNPOI(GridView gv, string exportFileName, string sheetName = "")
    {
        if (string.IsNullOrWhiteSpace(sheetName))
        {
            sheetName = exportFileName;
        }

        //建立 WorkBook 及試算表
        XSSFWorkbook workbook = new XSSFWorkbook();
        MemoryStream ms = new MemoryStream();
        XSSFSheet mySheet1 = (XSSFSheet)workbook.CreateSheet(sheetName);

        //建立標題列 Header
        XSSFRow rowHeader = (XSSFRow)mySheet1.CreateRow(0);
        for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
        {
            string strValue = gv.HeaderRow.Cells[i].Text;
            XSSFCell cell = (XSSFCell)rowHeader.CreateCell(i);
            cell.SetCellValue(HttpUtility.HtmlDecode(strValue).Trim());

            //建立新的CellStyle
            ICellStyle CellsStyle = workbook.CreateCellStyle();
            //建立字型
            IFont StyleFont = workbook.CreateFont();
            //設定文字字型
            StyleFont.FontName = "微軟正黑體";
            //設定文字大小
            StyleFont.FontHeightInPoints = 12; //設定文字大小為10pt
            CellsStyle.SetFont(StyleFont);
            cell.CellStyle = CellsStyle;
            mySheet1.AutoSizeColumn(i);
        }

        //建立內容列 DataRow
        for (int i = 0; i < gv.Rows.Count; i++)
        {
            XSSFRow rowItem = (XSSFRow)mySheet1.CreateRow(i + 1);

            for (int j = 0; j < gv.HeaderRow.Cells.Count; j++)
            {
                Label lb = null; // 因為GridView中有TemplateField，所以要將Label.Text讀出來
                if (gv.Rows[i].Cells[j].Controls.Count > 1)
                {
                    lb = gv.Rows[i].Cells[j].Controls[1] as Label;
                }

                string value1 = (lb != null)
                    ? HttpUtility.HtmlDecode(lb.Text)
                    : HttpUtility.HtmlDecode(gv.Rows[i].Cells[j].Text).Trim();
                int intry = 0;
                bool isNumeric = !value1.StartsWith("0") && int.TryParse(value1, out intry);

                XSSFCell cell = (XSSFCell)rowItem.CreateCell(j);

                if (string.IsNullOrEmpty(value1.Trim()))
                {
                    //空白
                    cell.SetCellValue(Convert.ToString(""));
                }
                else if (!isNumeric)
                {
                    XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); // 給cell style
                    XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
                    cellStyle.DataFormat = format.GetFormat("@"); // 文字格式

                    //建立字型
                    IFont StyleFont = workbook.CreateFont();
                    //設定文字字型
                    StyleFont.FontName = "微軟正黑體";
                    //設定文字大小
                    StyleFont.FontHeightInPoints = 12; //設定文字大小為12pt
                    cellStyle.SetFont(StyleFont);
                    //cellStyle.WrapText = true; //文字自動換列
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue(value1);
                }
                else
                {
                    //數字格式
                    cell.SetCellValue(intry);
                    ////自訂呈現格式
                    //XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); // 給cell style
                    //XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
                    //cellStyle.DataFormat = format.GetFormat("#,##0;[RED](#,##0)");
                    //cell.CellStyle = cellStyle;
                }
            }
        }

        //匯出
        workbook.Write(ms);

        // 此為匯出副檔名xlsx
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.ContentType =
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        HttpContext.Current.Response.AddHeader("Content-Disposition",
            string.Format("attachment;filename={0}", $"{exportFileName}.xlsx"));
        HttpContext.Current.Response.BinaryWrite(ms.ToArray());
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.End();

        //釋放資源
        workbook = null;
        ms.Close();
        ms.Dispose();

    }
}