[研究]ASP.NET 使用 NPOI 匯出寫入 Excel (.xlsx) 檔案
[研究]ASP.NET 把 ClosedXML 匯出寫入改寫為使用 NPOI 匯出寫入 Excel (.xlsx) 檔案
2025-03-31
環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019 + SQL Server Management Studio (SSMS) 19
********************************************************************************
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"
Inherits="WebApplication1.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">
<asp:Button ID="Button_Export_Xlsx_by_ClosedXML" runat="server"
OnClick="Button_Export_Xlsx_by_ClosedXML_Click" Text="匯出(ClosedXML)"
CssClass="btn btn-info btn-xs" /><br /> <asp:Button ID="Button_Export_Xlsx_by_NPOI" runat="server"
OnClick="Button_Export_Xlsx_by_NPOI_Click" Text="匯出(NPOI)"
CssClass="btn btn-info btn-xs" /><br /> <asp:Label ID="Label_MSG1" runat="server"></asp:Label>
<br />
</form>
</body>
</html>
|
Default.aspx.cs
using ClosedXML.Excel;
using System;
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;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace WebApplication1
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
// 匯出查詢結果(.xlsx)
#region == protected void Button_Export_Xlsx_by_ClosedXML_Click(object sender, EventArgs e) ==
protected void Button_Export_Xlsx_by_ClosedXML_Click(object sender, EventArgs e)
{
//string mainFileName = "匯出檔案名稱";
string mainFileName = "匯出";
string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + Path.DirectorySeparatorChar.ToString();
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())
{
string dataText = "";
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("工作表1");
// 定義欄位名稱列
string[] columnNameArray = { "欄位1", "欄位2", "欄位3" };
// 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]
//";
//string queryString = SqlDataSource1.SelectCommand;
string queryString = @"SELECT * FROM [MyDB].[dbo].[MyTable]";
//queryString = SqlDataSource1.SelectCommand;
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
using (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;
}
}
}
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)
{
throw;
}
}
#endregion
// 匯出查詢結果(.xlsx)
#region == protected void Button_Export_Xlsx_by_NPOI_Click(object sender, EventArgs e) ==
protected void Button_Export_Xlsx_by_NPOI_Click(object sender, EventArgs e)
{
string mainFileName = "匯出";
string dateTimeString = DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff");
string outFileName = HttpUtility.UrlEncode(mainFileName, System.Text.Encoding.UTF8) + "-" + dateTimeString + ".xlsx";
try
{
using (MemoryStream ms = new MemoryStream())
{
IWorkbook workbook = new XSSFWorkbook(); // 使用 XSSFWorkbook (XLSX 格式)
ISheet sheet = workbook.CreateSheet("工作表1");
// 設定標題列
string[] columnNames = { "欄位1", "欄位2", "欄位3" };
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < columnNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(columnNames[i]);
}
// 讀取資料
string queryString = @"SELECT * FROM [MyDB].[dbo].[MyTable]";
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int rowIndex = 1;
while (reader.Read())
{
IRow row = sheet.CreateRow(rowIndex++);
for (int i = 0; i < reader.FieldCount; i++)
{
string dataText = reader[i].ToString()
.Replace("\r", "").Replace("\n", "").Replace("\t", "")
.Replace(",", "，").Replace("'", "′").Replace("\"", "”")
.Replace("\\", "").Replace("\a", "").Replace("\b", "")
.Replace("\v", "").Replace("\f", "");
row.CreateCell(i).SetCellValue(dataText);
}
}
}
}
// **避免 MemoryStream 被 NPOI 關閉**
workbook.Write(ms);
ms.Flush();
// 設定 Response 參數
Response.Clear();
Response.Buffer = true;
Response.ContentEncoding = Encoding.UTF8;
Response.AddHeader("Content-Disposition", "attachment;filename=\"" + outFileName + "\"");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// 輸出檔案
Response.BinaryWrite(ms.ToArray());
Response.Flush();
Response.End();
}
}
catch (Exception ex)
{
Label_MSG1.ForeColor = System.Drawing.Color.Red;
Label_MSG1.Text = "匯出失敗：" + ex.Message;
}
}
#endregion
}
}
|
敝人實際20萬筆測試速度，有時ClosedXML快，有時 NPOI快。
(完)
相關
