[研究]ASP.NET,WebForm,使用 ClosedXML 0.102.2 匯出、寫入 .xlsx(暫存、不暫存檔)
2024-03-29
環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019 + SQL Server Management Studio (SSMS) 19
********************************************************************************
延續下面，但改用 DataTable，而非把資料逐一放入 ws.Cell 儲存格中
[研究][ASP.NET]使用 ClosedXML 0.95.4 讀取匯入 .xlsx 到資料庫(暫存、不暫存檔)
http://shaurong.blogspot.com/2021/02/aspnet-closedxml-0954-xlsx.html
Default2.aspx
Default.aspx.cs
<%@ Page Language="C#" AutoEventWireup="true"
CodeBehind="Default2.aspx.cs" Inherits="WebApplication1.Default2" %>
<!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">
<asp:SqlDataSource ID="SqlDataSource_Field2" runat="server"
ConnectionString="<%$ ConnectionStrings:MyDBConnectionString %>"
SelectCommand="SELECT N'全部' AS [Field2], 0 AS Priority
UNION
SELECT DISTINCT [Field2], 1 AS Priority FROM [MyTable] ORDER BY Priority, [Field2]"></asp:SqlDataSource>
<asp:DropDownList ID="DropDownList_Field2" runat="server"
AutoPostBack="True" DataSourceID="SqlDataSource_Field2"
DataTextField="Field2" DataValueField="Field2">
</asp:DropDownList>
<br />
<asp:Button ID="Button_Export_Xlsx_By_ClosedXML" runat="server" Text="匯出(.xlsx)"
OnClick="Button_Export_Xlsx_By_ClosedXML_Click" /><br />
<asp:Label ID="Label_MSG1" runat="server" Text="Label"></asp:Label>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyDBConnectionString %>"
SelectCommand="--TSQL--
SELECT * FROM [MyTable]
WHERE ([Field2]=@Filed2 OR @Filed2=N'全部');">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList_Field2" Name="Filed2" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SN" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
<asp:BoundField DataField="Field1" HeaderText="Field1" SortExpression="Field1" />
<asp:BoundField DataField="Field2" HeaderText="Field2" SortExpression="Field2" />
</Columns>
</asp:GridView>
</form>
</body>
</html>
|
Default.aspx.cs
using ClosedXML.Excel;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Configuration;
namespace WebApplication1
{
public partial class Default2 : 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 = { "SN", "Field1", "Field2" };
// 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 [MyTable]
";
DataTable dataTable = new DataTable();
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.Clear();
command.Parameters.AddWithValue("@Field2", DropDownList_Field2.SelectedValue);
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dataTable);
//var ws = wb.Worksheets.Add(dataTable, WorksheetName);
ws.Cell(2, 1).InsertData(dataTable.AsEnumerable());
//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();
string endCell = "C" + (dataTable.Columns.Count+1);
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>");
}
}
// ----------
}
} |
測試成功
(完)
相關
[研究]ASP.NET,WebForm,使用 ClosedXML 0.102.2 匯出、寫入 .xlsx(暫存、不暫存檔)
https://shaurong.blogspot.com/2024/03/aspnetwebform-closedxml-01022-xlsx.html
[研究][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
沒有留言:
張貼留言