[研究][ASP.NET][WebForm] 匯出寫入 .csv
2021-02-09
逗號分隔值 - 維基百科，自由的百科全書https://zh.wikipedia.org/wiki/%E9%80%97%E5%8F%B7%E5%88%86%E9%9A%94%E5%80%BC
逗號分隔值（Comma-Separated Values，CSV，有時也稱為字元分隔值，因為分隔字元也可以不是逗號）
環境：Visual Studio 2019 + ASP.NET + WebForm + C#
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ExportCsvTest.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_Csv" runat="server"
OnClick="Button_Export_Csv_Click" Text="匯出(.csv)" /><br />
<asp:Label ID="Label_MSG1" runat="server"></asp:Label>
</div>
</form>
</body>
</html> |
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 ExportCsvTest
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button_Export_Csv_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 + ".csv";
// XLWorkbook.SaveAs() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
// Response.TransmitFile() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
string dirFileName = fd + mainFileName + "-" +
dateTimeString + ".csv";
try
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
#region Export CSV
// [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/octet-stream";
Response.ContentEncoding = Encoding.UTF8;
//Response.ContentType = "application/vnd.ms-csv";
Response.AddHeader("content-disposition", "attachment;filename=" + outFileName);
using (MemoryStream MyMemoryStream = new MemoryStream())
{
//StreamWriter csvFileStreamWriter = new StreamWriter(MyMemoryStream);
StreamWriter csvFileStreamWriter = new StreamWriter(MyMemoryStream, Encoding.UTF8);
//string csvContent = "";
StringBuilder csvContent = new StringBuilder();
string dataText = "";
// 讀取資料，資料寫入「工作表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();
// 欄位名稱
csvContent.Append("id,FieldText,FieldDateTime,FieldInt,FieldBit\r\n");
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
//Console.WriteLine(String.Format("{0}", reader[0]));
for (int i = 0; i < reader.FieldCount; i++)
{
dataText = reader[i].ToString();
// 規則運算式語言 - 快速參考 | Microsoft Docs
// https://docs.microsoft.com/zh-tw/dotnet/standard/base-types/regular-expression-language-quick-reference
// 換掉影響 CSV 顯示效果的字元
dataText = dataText.Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace(",", "，").Replace("'", "′").Replace(@"""", "”");
// 換掉可能影響 .ods 的不可見字元
//dataText = dataText.Replace("\a", "").Replace("\b", "").Replace("\t", "").Replace("\v", "").Replace("\f", "").Replace("\\", "");
// .NET C# 不能用，語法錯誤
//s = Regex.Replace(s, @"[^/x21-/x7E]", "");
//dataText = Regex.Replace(dataText, @"[^0x20-0x7E]", "");
//dataText = Regex.Replace(dataText, @"[^/x7E-/x20]", "");
// .NET C# 可用，語法正確，但中文字會被過濾掉，只剩 ASCII 可見字元
//dataText = Regex.Replace(dataText, @"[^\u0021-\u007E]", "");
// 不是第一個欄位(i=0)時，前面要加上逗號
if (i != 0)
csvContent.Append(",");
csvContent.Append(dataText);
}
csvContent.Append("\r\n"); // 每行最後加上換行
}
}
csvFileStreamWriter.Write(csvContent);
csvFileStreamWriter.Flush();
MyMemoryStream.Position = 0;
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
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 CSV
}
catch (Exception ex)
{
Label_MSG1.ForeColor = System.Drawing.Color.Green;
string exMsg = "不明錯誤。";
if (ex != null)
{
exMsg = ex.Message.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] 匯出寫入 .csv
https://shaurong.blogspot.com/2021/02/aspnet-csv.html
[研究]ASP.NET WebForm 用 ClosedXML 匯出 CSV ANSI 和 CSV UTF-8 ( .csv)（逗號分隔檔）https://shaurong.blogspot.com/2024/02/aspnet-webform-closedxml-csv-ansi-csv.html
沒有留言:
張貼留言