[研究][ASP.NET]使用 ClosedXML 0.95.4 匯出、寫入 .xlsx(暫存、不暫存檔)
2021-02-17
2021-03-16 更新
2022-03-23增加格線、自適應欄寬、水平向上對整
2022-06-13如果不存檔而直接匯出，紅字部分註解掉
環境：Visual Studio 2019 + ASP.NET + WebForm + C# + NuGet 安裝 ClosedXML 0.95.4
packages.conf
<?xml version="1.0" encoding="utf-8"?> <packages> <package id="ClosedXML" version="0.95.4" targetFramework="net472" /> <package id="DocumentFormat.OpenXml" version="2.7.2" targetFramework="net472" /> <package id="ExcelNumberFormat" version="1.0.10" targetFramework="net472" /> <package id="Microsoft.CodeDom.Providers.DotNetCompilerPlatform" version="2.0.1" targetFramework="net472" /> <package id="Microsoft.CSharp" version="4.7.0" targetFramework="net472" /> <package id="System.IO.FileSystem.Primitives" version="4.0.1" targetFramework="net472" /> <package id="System.IO.Packaging" version="4.0.0" targetFramework="net472" /> </packages> |
Web.Config
<?xml version="1.0" encoding="utf-8"?> <!-- 如需如何設定 ASP.NET 應用程式的詳細資訊，請前往 https://go.microsoft.com/fwlink/?LinkId=169433 --> <configuration> <connectionStrings> <add name="TestDBConnectionString" connectionString="Data Source=.;Initial Catalog=TestDB;User ID=sa;Password=P@ssw0rd" providerName="System.Data.SqlClient" /> </connectionStrings> <system.web> <compilation debug="true" targetFramework="4.7.2"/> <httpRuntime targetFramework="4.7.2"/> </system.web> <system.codedom> <compilers> <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701"/> <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\"Web\" /optionInfer+"/> </compilers> </system.codedom> <appSettings> <add key="TempFolder" value="D:\WWW\Temp" /> </appSettings> </configuration> |
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ExportXlsxByClosedTest.Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>"
SelectCommand="SELECT * FROM [Table1]"></asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True" SortExpression="id" />
<asp:BoundField DataField="FieldText" HeaderText="FieldText" SortExpression="FieldText" />
<asp:BoundField DataField="FieldDateTime" HeaderText="FieldDateTime" SortExpression="FieldDateTime" />
<asp:BoundField DataField="FieldInt" HeaderText="FieldInt" SortExpression="FieldInt" />
<asp:CheckBoxField DataField="FieldBit" HeaderText="FieldBit" SortExpression="FieldBit" />
</Columns>
</asp:GridView>
<asp:Button ID="Button_Export_Xlsx_By_ClosedXML" runat="server"
OnClick="Button_Export_Xlsx_By_ClosedXML_Click" Text="匯出(.xlsx)" /><br />
<asp:Label ID="Label_MSG1" runat="server"></asp:Label>
</div>
</form>
</body>
</html>
|
Default.aspx.cs
using ClosedXML.Excel; using System; using System.Collections.Generic; using System.Configuration; using System.Data.SqlClient; using System.IO; using System.Linq; using System.Text; using System.Web; using System.Web.Configuration; using System.Web.UI; using System.Web.UI.WebControls; namespace ExportXlsxByClosedTest { public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button_Export_Xlsx_By_ClosedXML_Click(object sender, EventArgs e) { string mainFileName = "匯出檔案名稱"; string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + @"\"; if (!System.IO.Directory.Exists(fd)) { System.IO.Directory.CreateDirectory(fd); } string dateTimeString = DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff"); // 若檔案名稱有中文字或特殊符號，Response.AddHeader() 輸出檔案名稱要編碼 string outFileName = HttpUtility.UrlEncode( mainFileName, System.Text.Encoding.UTF8) + "-" + dateTimeString + ".xlsx"; // XLWorkbook.SaveAs() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼 // Response.TransmitFile() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼 string dirFileName = fd + mainFileName + "-" + dateTimeString + ".xlsx"; try { Response.Clear(); Response.Buffer = true; Response.Charset = ""; #region Export // [EXCEL] Excel打開是亂碼？快速找回資料的最好方法！ // http://blog.e-happy.com.tw/excel-excel%E6%89%93%E9%96%8B%E6%98%AF%E4%BA%82%E7%A2%BC%EF%BC%9F%E5%BF%AB%E9%80%9F%E6%89%BE%E5%9B%9E%E8%B3%87%E6%96%99%E7%9A%84%E6%9C%80%E5%A5%BD%E6%96%B9%E6%B3%95%EF%BC%81/ // Excel開啟CSV時的中文編碼問題補遺 //https://blog.darkthread.net/blog/csv-encoding-again/ //Response.ContentType = "application/vnd.oasis.opendocument.spreadsheet"; // ODF/.ods //Response.AddHeader("content-disposition", "attachment;filename=" + outFileName); using (MemoryStream myMemoryStream = new MemoryStream()) { //StreamWriter odsFileStreamWriter = new StreamWriter(MyMemoryStream); //StreamReader odsFileStreamReader = new StreamReader(new MemoryStream()); string dataText = ""; var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("工作表1"); // 否則會出現類似 The range 工作表1!A1:U57 overlaps with the worksheet's autofilter. 錯誤訊息
ws.AutoFilter.IsEnabled = false; // 定義欄位名稱列
string[] columnNameArray = { "id", "FieldText", "FieldDateTime", "FieldInt", "FieldBit" };
// Column number must be between 1 and 16384
for (int i = 0; i < columnNameArray.Count(); i++)
{
ws.Cell(1, i+1).Value = columnNameArray[i];
}
// 讀取資料，資料寫入「工作表1」
string queryString = @"
--DECLARE @FieldText nvarchar(50)
--SET @FieldText=N'abc'
SELECT * FROM [TestDB].[dbo].[Table1]
";
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
//command.Parameters.Clear();
//command.Parameters.AddWithValue("@FieldText", queryFieldText);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
int rowIndex = 1; // 1 是標題列
while (reader.Read())
{
rowIndex++;
for (int i = 0; i < reader.FieldCount; i++)
{
//reader[i]的 i 要從 0 開始
dataText = reader[i].ToString();
// 規則運算式語言 - 快速參考 | Microsoft Docs
// https://docs.microsoft.com/zh-tw/dotnet/standard/base-types/regular-expression-language-quick-reference
// 換掉特殊字元 (自己評估)
dataText = dataText.Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace(",", "，").Replace("'", "′").Replace(@"""", "”");
dataText = dataText.Replace("\a", "").Replace("\b", "").Replace("\t", "").Replace("\v", "").Replace("\f", "").Replace("\\", "");
// Column number must be between 1 and 16384
ws.Cell(rowIndex, i+1).Value = dataText;
}
}
//設定區域範圍的 儲存格框線
//ws.Ranges("A1:F5").Style.Border.TopBorder = XLBorderStyleValues.Thin;
string endCell = "S" + rowIndex.ToString();
ws.Ranges("A1:" + endCell).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Ranges("A1:" + endCell).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Ranges("A1:" + endCell).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Ranges("A1:" + endCell).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Columns().AdjustToContents();// 自適應欄寬，這要在資料寫完後，檔案儲存關閉前再做
ws.Ranges("A1:" + endCell).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; // 水平向上對齊
Response.Clear();
Response.Buffer = true;
Response.ContentEncoding = Encoding.GetEncoding(950); //950就是所謂的BIG5
//Response.AddHeader("Content-Disposition", "attachment;filename=\"" +
// HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + "-" +
// DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
Response.AddHeader("Content-Disposition", "attachment;filename=\"" + outFileName);
//'Excel 2003 : "application/vnd.ms-excel"
//'Excel 2007 : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
//Extension MIME Type
//.xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
//.xltx application/vnd.openxmlformats-officedocument.spreadsheetml.template
//.potx application/vnd.openxmlformats-officedocument.presentationml.template
//.ppsx application/vnd.openxmlformats-officedocument.presentationml.slideshow
//.pptx application/vnd.openxmlformats-officedocument.presentationml.presentation
//.sldx application/vnd.openxmlformats-officedocument.presentationml.slide
//.docx application/vnd.openxmlformats-officedocument.wordprocessingml.document
//.dotx application/vnd.openxmlformats-officedocument.wordprocessingml.template
//.xlam application/vnd.ms-excel.addin.macroEnabled.12
//.xlsb application/vnd.ms-excel.sheet.binary.macroEnabled.12
//Response.ContentType = "application/ms-excel";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.TransmitFile(@"C:\Program Files (x86)\IIS Express\Export.xlsx");
//dirFileName = dirFileName.Replace('\\', '/'); //轉換 Windows, Linux 不同路徑斜線
//dirFileName = dirFileName.Replace("/", "\\"); // 轉換斜線 / 成 \ ，因 \ 是特殊符號，要以 \\ 表示
// 方法1: 直接輸出
wb.SaveAs(myMemoryStream);
Response.BinaryWrite(myMemoryStream.ToArray());
// myMemoryStream.WriteTo(Response.OutputStream); //works too
Response.Flush();
Response.Close();
// 方法2: 若需要寫入檔案再輸出
wb.SaveAs(dirFileName); // 存檔，Debug 或其他用途
////Response.WriteFile(savePath);
//Response.TransmitFile(dirFileName); // 把存檔輸出
// 輸出結束
Response.End();
Label_MSG1.ForeColor = System.Drawing.Color.Green;
Label_MSG1.Text = "匯出成功。";
// 上面 Response 輸出檔案，所以下面 JavaScript alert 是不會跳出的
//Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('匯出成功。');</script>");
}
}
#endregion Export ODS
}
catch (Exception ex)
{
Label_MSG1.ForeColor = System.Drawing.Color.Red;
string exMsg = "不明錯誤。";
if (ex != null)
{
exMsg = ex.ToString();
}
Label_MSG1.Text = exMsg;
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('匯出失敗。');</script>");
// Exception Message 內容可能導致 JavaScript alert 無法顯示，所以下面這行可能無法跳出對話盒視窗。
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('" + exMsg + "');</script>");
}
}
// ----------
}
} |
不過根據以往經驗，元件直接產生的 .xlsx，和用 Excel 建立的 .xlsx，實際的檔案骨架 schema 好像沒有 100% 相同，前者用某些元件或版本匯入可能正常或會出錯，但前者用 Microsoft Excel 軟體直接開啟正常。
或可考慮先用 Excel 2016 或 Excel 2019 做好一個沒填寫內容的 活頁簿 (Workbook) 和 工作表1 (Worksheet)，複製後 (避免多人同開開起讀寫)，再拿來寫入、關閉、匯出。
(完)
相關
[研究][ASP.NET]ClosedXML元件常用屬性或方法http://shaurong.blogspot.com/2022/04/aspnetclosedxml.html
[研究][ASP.NET]使用 ClosedXML 0.95.4 讀取匯入 .xlsx 到資料庫(暫存、不暫存檔)
http://shaurong.blogspot.com/2021/02/aspnet-closedxml-0954-xlsx.html
[研究][ASP.NET]使用 ClosedXML 0.95.4 匯出、寫入 .xlsx(暫存、不暫存檔)
http://shaurong.blogspot.com/2021/02/aspnet-closedxml-0954-xlsx_17.html
沒有留言:
張貼留言