closed xml 轉 datatable
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);
以上文章僅用紀錄資料使用.....