2018-12-05
2020-04-17 更新
問題解決,詳見這篇
[研究][C#][ASP.NET][WebForm] 用 ClosedXML 0.95.0 把 GridView1 匯出成 Excel (.xlsx)
https://shaurong.blogspot.com/2020/04/caspnetwebform-closedxml-0950-gridview1.html
********************************************************************************
工具:Visual Studio 2017 v15.9.3
WebForm + C# + ASP.NET 4.7 + ClosedXML 0.93.1
ClosedXML 雖然不是微軟做的,但是底層用的 OpenXML 是 Microsoft 開發的,輸出格式的副檔名請用 .xlsx,如果用 .xls,開啟會有錯誤,表示產生的格式不是真的 .xls 格式。
protected void Button_Download_Xlsx_Click(object sender, EventArgs e) { // https://www.codeproject.com/Questions/1027572/Gridview-Export-To-Excel-xlsx-with-TemplateField using (XLWorkbook wb = new XLWorkbook()) { DataTable dt = this.Cache["ABCSelectionOfAuditedInstitution"] as DataTable; ; // 要給名稱,否則 Worksheet names cannot be empty dt.TableName = "工作表1"; //dt.Rows[1][1] = "測試"; // B3 // 注意 GridView 和 DataTable 欄位索引未必相同 dt.Columns[0].ColumnName = "欄位1名稱"; //GridView1.HeaderRow.Cells[1] dt.Columns[1].ColumnName = "欄位2名稱"; dt.Columns[2].ColumnName = "欄位3名稱"; wb.Worksheets.Add(dt); //disable autofilter in closedXml c#? var ws = wb.Worksheet("工作表1"); ws.Tables.FirstOrDefault().SetShowAutoFilter(false); string fileName= "Export" + DateTime.Now.ToString("-yyyy-MM-dd-HH-mm-ss") + ".xlsx"; Response.Clear(); Response.Buffer = true; Response.Charset = ""; Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //Response.AddHeader("content-disposition", "attachment;filename=Export.xls"); Response.AddHeader("content-disposition", "attachment;filename=" +fileName); using (MemoryStream MyMemoryStream = new MemoryStream()) { wb.SaveAs(MyMemoryStream); MyMemoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); } } } |
2020-03-20
用 ClosedXML 0.94.2 匯出,用 Excel 2016 開啟,OK。
用 ClosedXML 0.94.2 匯入 ClosedXML 0.94.2 匯出的檔案,會失敗。
*****
用 OpenXML 2.10.1 匯出,用 Excel 2016 開啟,有問題。如下圖。
用 ClosedXML 0.94.2 匯入前,必須另存新檔案 (不管有無修改),才能成功匯入。
*****
(完)
相關
相關文章
[研究] .ods、.csv 轉 .xlsx 檔案
https://shaurong.blogspot.com/2020/04/odscsv-xlsx.html
[研究][C#][ASP.NET] ClosedXML 0.94.2 不支援 .csv
https://shaurong.blogspot.com/2020/04/closedxml-0942-csv.html
[研究][C#][ASP.NET][WebForm] 用 ClosedXML 把 GridView1 匯出成 Excel (.xlsx)
http://shaurong.blogspot.com/2020/04/caspnetwebform-closedxml-gridview1.html
[研究][C#][ASP.NET][WebForm] 用 ClosedXML 把 DataTable 匯出成 Excel (.xlsx)ClosedXML
https://shaurong.blogspot.com/2018/12/cwebform-closedxml-datatable-excel-xlsx.html
[研究] 匯出、匯入 Excel 檔案 (.NET)
https://shaurong.blogspot.com/2017/11/excel-net.html
[研究] [C#] 用 ClosedXML 0.90.0 在 Server端操作 Excel,安裝與試用 (NuGet安裝)
https://shaurong.blogspot.com/2017/11/c-closedxml-server-excel-nuget.html
Open XML SDK 2.5 for Microsoft Office (11/20/2012)
https://www.microsoft.com/en-us/download/details.aspx?id=30425
久未更新,只在 NuGet 更新
DocumentFormat.OpenXml 2.10.1
by: Microsoft OfficeDeveloperPlatformTeam
https://www.nuget.org/packages/DocumentFormat.OpenXml/
沒有留言:
張貼留言