建立Datatable及匯出Excel

        #region 建立Datatable
        //建立Datatable
        DataTable dt = new DataTable();

        //宣告欄位名稱
        dt.Columns.Add(new DataColumn("會計科目", typeof(string)));
        dt.Columns.Add(new DataColumn("科目代碼", typeof(string)));
        dt.Columns.Add(new DataColumn("分公司小計", typeof(Int32)));
        dt.Columns.Add(new DataColumn("合計", typeof(Int32)));

        //新增一筆資料
        DataRow dr = dt.NewRow();
        dr["會計科目"] = "小計：";
        dr["科目代碼"] = "1001";
        dr["分公司小計"] = 12345;
        dr["合計"] = 12345;
        dt.Rows.Add(dr);

        //排序
        dt.DefaultView.Sort = "科目代碼,會計科目";

        //過濾出分公司小計有不為零的才呈現該筆資料
        dt.DefaultView.RowFilter = "ISNULL(分公司小計, 0) <> 0";

        //刪除多餘欄位
        dt.Columns.Remove("完整科目代碼");
        dt.Columns.Remove("科目代碼前四碼");
        dt.Columns.Remove("分公司代碼");

        //匯出EXCEL
        GridView gv = new GridView();
        gv.DataSource = dt;
        gv.DataBind();
        ExportToFile(gv, "表單名稱");
        #endregion

        #region 匯出Excel ExportToFile
        ///
        /// 匯出Excel ExportToFile
        ///
        public static void ExportToFile(GridView gv, string ExcelName)
        {
            try
            {
                //建立 WorkBook 及試算表
                HSSFWorkbook workbook = new HSSFWorkbook();
                MemoryStream ms = new MemoryStream();
                HSSFSheet mySheet1 = (HSSFSheet)workbook.CreateSheet(ExcelName);
                mySheet1.DefaultColumnWidth = 15;         //預設的字元寬度

                //建立標題列 Header
                HSSFRow rowHeader = (HSSFRow)mySheet1.CreateRow(0);
                for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
                {
                    //若有啟用排序，Header會變成 LinkButton
                    LinkButton lb = null;
                    if (gv.HeaderRow.Cells[i].Controls.Count > 0)
                    {
                        lb = gv.HeaderRow.Cells[i].Controls[0] as LinkButton;
                    }
                    string strValue = (lb != null) ? lb.Text : gv.HeaderRow.Cells[i].Text;

                    HSSFCell cell = (HSSFCell)rowHeader.CreateCell(i);
                    cell.SetCellValue(HttpUtility.HtmlDecode(strValue).Trim());
                    NPOI.SS.UserModel.CellStyle cellStyle = workbook.CreateCellStyle();
                    cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;    //設為居中

                    cell.CellStyle = cellStyle;
                }

                //建立內容列 DataRow
                for (int i = 0; i < gv.Rows.Count; i++)
                {
                    HSSFRow rowItem = (HSSFRow)mySheet1.CreateRow(i + 1);
                    for (int j = 0; j < gv.HeaderRow.Cells.Count; j++)
                    {
                        string value1 = HttpUtility.HtmlDecode(gv.Rows[i].Cells[j].Text).Trim();
                        long number1 = 0;
                        bool isNumeric = long.TryParse(value1, out number1);

                        if (string.IsNullOrEmpty(value1))
                        {
                            //空白
                            rowItem.CreateCell(j).SetCellValue("");
                        }
                        else if (!isNumeric)
                        {
                            //文字格式
                            rowItem.CreateCell(j).SetCellValue(value1);
                            mySheet1.SetColumnWidth(j, 30 * 256);     //欄位寬度設為30
                        }
                        else
                        {
                            //數字格式
                            HSSFCell cell = (HSSFCell)rowItem.CreateCell(j);
                            cell.SetCellValue(number1);
                            //NPOI.SS.UserModel.CellStyle cellStyle = workbook.CreateCellStyle();
                            //cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0");
                            //cell.CellStyle = cellStyle;

                            HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                            HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
                            cellStyle.DataFormat = format.GetFormat("#,##0;[RED](#,##0)");
                            cell.CellStyle = cellStyle;
                        }
                    }
                }

                //匯出
                workbook.Write(ms);
                HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("Attachment; Filename=" + HttpUtility.UrlEncode(ExcelName) + ".xls"));
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());

                //釋放資源
                workbook = null;
                ms.Close();
                ms.Dispose();
            }
            catch (Exception)
            { }
        }
        #endregion