2020-05-29
********************************************************************************
2020-06-03
如果資料筆數很多，注意效能問題，及 RAM 用量問題。
********************************************************************************
本文使用下面工具
Visual Studio Enterprise 2019 v16.6.0
Default.aspx
Default.aspx.cs
匯出後，用 Microsoft Excel 2016 開啟正常，沒有警告或錯誤。
(完)
相關文章
[研究][C#][ASP.NET][WebForm] 把 GridView 匯出成 .csv
本文使用下面工具
Visual Studio Enterprise 2019 v16.6.0
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="GridViewExportToCSVDemo.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 [MyTable]"></asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="SN" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
<asp:BoundField DataField="myID" HeaderText="myID" SortExpression="myID" />
<%--<asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />--%>
<asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />
<asp:BoundField DataField="purchaseDate" HeaderText="purchaseDate" SortExpression="purchaseDate" />
</Columns>
</asp:GridView>
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /><br />
<br />
<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
Visible="False"
AutoGenerateColumns="False" DataKeyNames="SN" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
<asp:BoundField DataField="myID" HeaderText="myID" SortExpression="myID" />
<%--<asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />--%>
<asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />
<asp:BoundField DataField="purchaseDate" HeaderText="purchaseDate" SortExpression="purchaseDate" />
</Columns>
</asp:GridView>
<asp:Button ID="Button2" runat="server" Text="Button" OnClick="Button2_Click" /><br />
<asp:Label ID="Label_MSG1" runat="server"></asp:Label>
</div>
</form>
</body>
</html> |
Default.aspx.cs
using System;
using System.IO;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace GridViewExportToCSVDemo
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
//不想匯出的列，可以隱藏掉
//GridView1.Columns[0].Visible = false;
GridViewExportToCSV(GridView1, "匯出檔名");
}
protected void Button2_Click(object sender, EventArgs e)
{
GridViewExportToCSV(GridView2, "匯出檔名");
}
private string GridViewExportToCSV(GridView gridview, string exportSubject)
{
string year = DateTime.Now.Year.ToString();
string mainFileName = year + "年" + exportSubject;
//DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
// 使用者若很多，最好連 fff 也用上
//DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";
// 若檔案名稱有中文字或特殊符號，Response.AddHeader() 輸出檔案名稱要編碼
string outFileName = HttpUtility.UrlEncode(mainFileName, System.Text.Encoding.UTF8) + "-" +
DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".csv";
// XLWorkbook.SaveAs() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
// Response.TransmitFile() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
string dirFileName = @"D:\Production\TempImageFiles\" + mainFileName + "-" +
DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".csv";
try
{
GridView gv = gridview;
bool oldAllowPaging = gv.AllowPaging;
bool oldAllowSorting = gv.AllowSorting;
gv.AllowSorting = false; // 支援排序時，標題不是文字，要改抓 LinkButton 的文字，麻煩，不如暫時取消
gv.AllowPaging = false; // 分頁狀態下，只能抓到當頁的10筆，必須取消分頁
gv.DataBind();
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/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.AddHeader("content-disposition", "attachment;filename=Export.xlsx");
//Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
//Response.AddHeader("content-disposition", "attachment;filename=" + outFileName + "\"");
//Response.AddHeader("content-disposition", "attachment;filename=" + outFileName);
//Response.ContentType = "application/CSV";
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); // 預設 .csv 是 ANSI，Excel 開啟會亂碼
StreamWriter csvFileStreamWriter = new StreamWriter(MyMemoryStream, Encoding.UTF8); // 這樣 Excel 開 .csv 不會亂碼
string csvContent = "";
int visibleIndex = 0;
// 標題純文字這樣抓
//GridView1.Columns.Count 是「非自動生成列數」。不管是否為 Visible。
//GridView1.HeaderRow.Cells.Count 是「自動生成列數」和「非自動生成列數」的總和。不管是否為 Visible。
//GridView1.Visible = false; 時，GridView1.HeaderRow.Cells[i].Visible 總是會 false。
// 「非自動生成列」會比「自動生成列」先顯示
// HeaderRow.Cells[i].Visible 不管是 true 或 false，回傳都 true
// gv.Columns[i].Visible 才回真正回傳 true 或 false
// 但是 gv.Columns[i] 只能抓「非自動生成列數」
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
if (i < gv.Columns.Count)
{
if (gv.Columns[i].Visible == true)
{
string columnName = gv.Columns[i].HeaderText;
columnName = columnName.Replace("\r", "").Replace("\n", "").Replace(",", "，");
if (csvContent == "")
{
csvContent = columnName;
}
else
{
csvContent = csvContent + "," + columnName;
}
visibleIndex++;
}
}
else
{
// GridView1.Visible = true 時，gv.HeaderRow.Cells[i].Visible 也一定 true
// GridView1.Visible = false 時，gv.HeaderRow.Cells[i].Visible 也一定 false
// 若想 GridView1.Visible 為 false 時也一定匯出，下面 if 要註解掉
//if (gv.HeaderRow.Cells[i].Visible == true)
//{
string columnName = gv.HeaderRow.Cells[i].Text;
columnName = columnName.Replace("\r", "").Replace("\n", "").Replace(",", "，");
if (csvContent == "")
{
csvContent = columnName;
}
else
{
csvContent = csvContent + "," + columnName;
}
visibleIndex++;
//}
}
}
csvContent = csvContent + "\r\n";
// 資料列這樣抓
string lineString = "";
for (int i = 0; i < gv.Rows.Count; i++)
{
visibleIndex = 0;
lineString = "";
for (int j = 0; j < gv.HeaderRow.Cells.Count; j++)
{
if (j < gv.Columns.Count)
{
if (gv.Columns[j].Visible == true)
{
// 未執行【將這個欄位轉為 TemplateField】的欄位這樣就可抓到
string dataText = gv.Rows[i].Cells[j].Text;
dataText = dataText.Replace(" ", "");
// 執行【將這個欄位轉為 TemplateField】的欄位要根據情況改成下面
if (gv.Rows[i].Cells[j].HasControls())
{
for (int k = 0; k < gv.Rows[i].Cells[j].Controls.Count; k++)
{
if (gv.Rows[i].Cells[j].Controls[k] is Label)
{
Label labelControl = gv.Rows[i].Cells[j].Controls[k] as Label;
//Label labelControl = (Label)gv.Rows[i].Cells[j].FindControl("Label1");
if (labelControl != null)
{
string labelText = labelControl.Text;
dataText = labelText;
}
}
}
}
dataText = dataText.Replace("\r", "").Replace("\n", "").Replace(",", "，");
if (lineString == "")
{
csvContent = csvContent + dataText;
lineString = "has";
}
else
{
csvContent = csvContent + "," + dataText;
}
visibleIndex++;
}
}
else
{
// GridView1.Visible = true 時，gv.HeaderRow.Cells[i].Visible 也一定 true
// GridView1.Visible = false 時，gv.HeaderRow.Cells[i].Visible 也一定 false
// 若想 GridView1.Visible 為 false 時也一定匯出，下面 if 要註解掉
//if (gv.HeaderRow.Cells[j].Visible == true)
//{
// 未執行【將這個欄位轉為 TemplateField】的欄位這樣就可抓到
string dataText = gv.Rows[i].Cells[j].Text;
// 執行【將這個欄位轉為 TemplateField】的欄位要根據情況改成下面
if (gv.Rows[i].Cells[j].HasControls())
{
for (int k = 0; k < gv.Rows[i].Cells[j].Controls.Count; k++)
{
if (gv.Rows[i].Cells[j].Controls[k] is Label)
{
Label labelControl = gv.Rows[i].Cells[j].Controls[k] as Label;
//Label labelControl = (Label)gv.Rows[i].Cells[j].FindControl("Label1");
if (labelControl != null)
{
string labelText = labelControl.Text;
dataText = labelText;
}
}
}
}
dataText = dataText.Replace("\r", "").Replace("\n", "").Replace(",", "，");
if (lineString == "")
{
csvContent = csvContent + dataText;
lineString = "has";
}
else
{
csvContent = csvContent + "," + dataText;
}
visibleIndex++;
//} // if
} // else
} // for (int j = 0; j < gv.HeaderRow.Cells.Count; j++)
csvContent = csvContent + "\r\n";
} // for (int i = 0; i < gv.Rows.Count; i++)
csvFileStreamWriter.Write(csvContent);
csvFileStreamWriter.Flush();
MyMemoryStream.Position = 0;
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
#endregion Export CSV
gv.AllowPaging = oldAllowPaging;
gv.AllowSorting = oldAllowSorting;
gv.DataBind();
}
catch (Exception ex)
{
Label_MSG1.ForeColor = System.Drawing.Color.Green;
string exMsg = "";
if (ex != null)
{
exMsg = ex.ToString();
}
else
{
exMsg = "不明錯誤。";
}
Label_MSG1.Text = exMsg;
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('" + exMsg + "');</script>");
return exMsg;
}
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('匯出成功。');</script>");
return "";
}
}
}
|
匯出後，用 Microsoft Excel 2016 開啟正常，沒有警告或錯誤。
(完)
相關文章
[研究][C#][ASP.NET][WebForm] 把 GridView 匯出成 .csv
http://shaurong.blogspot.com/2020/05/caspnetwebform-gridview-csv.html
[研究][C#][ASP.NET][WebForm] 使用 Microsoft OpenXML SDK 2.10.1 ( DocumentFormat.OpenXml ) 把 Excel .xlsx 匯入DocumentFormat.OpenXml, OpenXML
https://shaurong.blogspot.com/2020/04/caspnetwebform-microsoft-openxml-sdk.html
[研究][C#][ASP.NET][WebForm] 使用 OpenXML 2.10.1 (DocumentFormat.OpenXml) 把 GridView 匯出成 .xlsxDocumentFormat.OpenXml, OpenXML
https://shaurong.blogspot.com/2020/04/caspnetwebform-openxml-2101.html
[研究][C#][ASP.NET][WebForm] 使用 Microsoft OpenXML SDK 2.10.1 ( DocumentFormat.OpenXml ) 把 Excel .xlsx 匯入DocumentFormat.OpenXml, OpenXML
https://shaurong.blogspot.com/2020/04/caspnetwebform-microsoft-openxml-sdk.html
[研究][C#][ASP.NET][WebForm] 使用 OpenXML 2.10.1 (DocumentFormat.OpenXml) 把 GridView 匯出成 .xlsxDocumentFormat.OpenXml, OpenXML
https://shaurong.blogspot.com/2020/04/caspnetwebform-openxml-2101.html
沒有留言:
張貼留言