散步在海洋中的碼農

https://www.aspsnippets.com/Articles/Read-and-Import-Excel-data-to-DataTable-using-ClosedXml-in-ASPNet-with-C-and-VBNet.aspx

作者:  Mudassar Ahmed Khan

 

for asp.net 用快速用 datatable匯出

        //呼叫方4...
         public void 匯出EXCEL(){
         
             DataTable dtsample = new DataTable();
//User.Identity.Name  這個來源是用 formauthentication 做的....
           string sFormat = "{0}_{1}_{2}.xlsx";  //檔名定義
            string filename = string.Format(sFormat, DateTime.Now.ToString("yyyyMMddhhmm"), "一些匯出資訊", User.Identity.Name);
            ExportExcelByClosedXML(dtsample , "一些匯出資訊", filename);  //匯出excel
         }
         
       
           
         /// <summary>
        /// 匯出excel 
        /// </summary>
        /// <param name="dtresult">  類別 datatable  </param>
        /// <param name="sheetname"> 工作表明稱 </param>
        /// <param name="filename">  檔案名稱 </param>
        public void ExportExcelByClosedXML(DataTable dtresult ,string sheetname , string filename) {
            using (XLWorkbook wb = new XLWorkbook())
            {
                dtresult.TableName = sheetname;
                wb.Worksheets.Add(dtresult);
                //disable autofilter in closedXml c#?
                var ws = wb.Worksheet(sheetname);

                ws.Tables.FirstOrDefault().SetShowAutoFilter(false);
                IXLRange contents = ws.Range("A1:A65536");
                contents.Style.Alignment.WrapText = true;
                 contents = ws.Range("D1:D65536");
                contents.Style.Alignment.WrapText = true;        

                filename = HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8);
                HttpContext.Current.     Response.Clear();
                HttpContext.Current.Response.Buffer = true;
                HttpContext.Current.Response.Charset = "";
                HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                //Response.AddHeader("content-disposition", "attachment;filename=Export.xls");
                HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + filename);
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    wb.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();
                }
            }
        }

補充系列:

1.在最上頭插入一行，或多行範本…
 https://stackoverflow.com/questions/35314825/closedxml-add-a-new-row-without-overwrite-data-first-line

2.多筆資料 手動建置title，但是明細由資料庫產出，希望可以多次匯入(by Datatabe)

https://stackoverflow.com/questions/22747360/append-to-excel-file-with-closedxml

XLWorkbook wb= new XLWorkbook(@"C:\existing_excel_file.xlsx");
IXLWorksheet ws= wb.Worksheet("Name or index of the sheet to use");


     int NumberOfLastRow = ws.LastRowUsed().RowNumber();  //抓目前最大行號
	      IXLCell CellForNewData = ws.Cell(NumberOfLastRow + 6, 1); //往透推7格
		  CellForNewData => 可呼叫方法isert資料
	 IXLRows rows = ws.Row(NumberOfLastRow).InsertRowsBelow(1); //往往後推一行
     IXLRows rows = ws.Row(NumberOfLastRow).InsertRowsAbove(1); //往往上推一行
	 
	    //設定區域範圍的  儲存格框線
                ws.Ranges("A1:F5").Style.Border.TopBorder = XLBorderStyleValues.Thin;
                ws.Ranges("A1:F5").Style.Border.LeftBorder = XLBorderStyleValues.Thin;
                ws.Ranges("A1:F5").Style.Border.RightBorder = XLBorderStyleValues.Thin;
                ws.Ranges("A1:F5").Style.Border.BottomBorder = XLBorderStyleValues.Thin;
			//設定背景顏色	
            ws.Ranges("A1:A5").Style.Fill.SetBackgroundColor(XLColor.FromHtml("#7AA0CD"));
                ws.Ranges("A1:A5").Style.Font.SetFontColor(XLColor.White);
                ws.Ranges("A1:A5").Style.Font.SetFontColor(XLColor.White);       

 

 

 

 

 

 

以上文章僅用紀錄資料使用.....