浮雲雅築: [研究][C#][ASP.NET][WebForm] 用 ClosedXML 0.95.0 把 GridView1 匯出成 Excel (.xlsx)

[研究][C#][ASP.NET][WebForm] 用 ClosedXML 0.95.0 把 GridView1 匯出成 Excel (.xlsx) 下載

2020-04-17
2020-04-20更新

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="GridViewtoDataTableTest.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 [TestTable]"></asp:SqlDataSource><br />
            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"

                AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="no" HeaderText="no" InsertVisible="False" ReadOnly="True" SortExpression="no" />
                    <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" />
                    <asp:BoundField DataField="addr" HeaderText="addr" SortExpression="addr" />

                    <asp:BoundField DataField="myname2" HeaderText="myname2" SortExpression="myname2" Visible="false" />
                    <asp:TemplateField HeaderText="addr2" SortExpression="addr2">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("addr2") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Bind("addr2") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView><br />
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /><br />
            <asp:Label ID="Label_MSG1" runat="server"></asp:Label>
        </div>
    </form>
</body>
</html>

上面 myname2 欄位因為 Visible="false"，GridViewtoDataTable() 產出結果會只剩下標題，沒有內容。
上面 addr2 欄位因為執行【將這個欄位轉為 TemplateField】，GridViewtoDataTable() 產出結果會只剩下標題。
GridViewtoDataTable() 可能要再研究改良。

Default.aspx.cs

using ClosedXML.Excel;
using System;
using System.Data;
using System.Text;
using System.Web;
using System.Web.UI.WebControls;

namespace GridViewtoDataTableTest
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            Label_MSG1.Text = "";
            if (GridView1.Rows.Count == 0)
            {
                Label_MSG1.Text = "無可匯出資料。";
                Label_MSG1.ForeColor = System.Drawing.Color.Red;
                return;
            }

            var wb = new XLWorkbook();
            DataTable dataTable = GridViewtoDataTable(GridView1);
            //Worksheet names cannot be empty
            dataTable.TableName = "工作表1";

            wb.Worksheets.Add(dataTable);

            string mainFileName = "匯出檔案";

            //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") + ".xlsx";

            // XLWorkbook.SaveAs() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
            // Response.TransmitFile() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
            string dirFileName = @"D:\Production\TempImageFiles\" + mainFileName + "-" +
                DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";

            //wb.SaveAs("Export.xlsx");
            // Visual Studio 下 Debug 會存到 C:\Program Files (x86)\IIS Express\
            wb.SaveAs(dirFileName);

            // 跳出下載視窗
            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("/", "\\");  // 轉換斜線 / 成 \  ，因 \ 是特殊符號，要以 \\ 表示
            Response.TransmitFile(dirFileName);
            Response.End();
        }

        public static DataTable GridViewtoDataTable(GridView gv)
        {
            //GridView2DataTable
            // 只會抓 <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" /> 這類的值，
            // 執行【將這個欄位轉為 TemplateField】的欄位不會被抓到。 
            gv.AllowSorting = false;    // 支援排序時，標題不是文字，要改抓 LinkButton 的文字，麻煩，不如暫時取消
            gv.AllowPaging = false; // 分頁狀態下，只能抓到當頁的10筆，必須取消分頁
            gv.DataBind();

            DataTable dt = new DataTable();
            DataColumn dc;
            DataRow dr;
            for (int i = 0; i < gv.Columns.Count; i++)
            {
                dc = new DataColumn();
                dc.ColumnName = gv.Columns[i].HeaderText;
                dt.Columns.Add(dc);
            }
            for (int i = 0; i < gv.Rows.Count; i++)
            {
                dr = dt.NewRow();
                for (int j = 0; j < gv.Columns.Count; j++)
                {
                    dr[j] = gv.Rows[i].Cells[j].Text;
                }
                dt.Rows.Add(dr);
            }
            gv.AllowSorting = true;
            gv.AllowPaging = true;
            gv.DataBind();

            return dt;
        }
    }
}

解決欄位 Visible="false" 問題，標題和資料內容都不會產出了。

public static DataTable GridViewtoDataTable(GridView gv)
{
    //GridView2DataTable
    // 只會抓 <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" /> 這類的值，
    // 執行【將這個欄位轉為 TemplateField】的欄位不會被抓到。 
    gv.AllowSorting = false;    // 支援排序時，標題不是文字，要改抓 LinkButton 的文字，麻煩，不如暫時取消
    gv.AllowPaging = false; // 分頁狀態下，只能抓到當頁的10筆，必須取消分頁
    gv.DataBind();

    DataTable dt = new DataTable();
    DataColumn dc;
    DataRow dr;
    for (int i = 0; i < gv.Columns.Count; i++)
    {
        dc = new DataColumn();
        dc.ColumnName = gv.Columns[i].HeaderText;
        if (gv.Columns[i].Visible != false)
        {
            dt.Columns.Add(dc);
        }
    }
    int index = 0;
    for (int i = 0; i < gv.Rows.Count; i++)
    {
        dr = dt.NewRow();

        index = 0;
for (int j = 0; j < gv.Columns.Count; j++)
        {

            if (gv.Columns[j].Visible != false)
            {
                //dr[j] = gv.Rows[i].Cells[j].Text;
                dr[index] = gv.Rows[i].Cells[j].Text;
                index++;
            }
        }
        dt.Rows.Add(dr);
    }
    gv.AllowSorting = true;
    gv.AllowPaging = true;
    gv.DataBind();

    return dt;
}

執行【將這個欄位轉為 TemplateField】的欄位不會被抓到或可參考下面
http://shaurong.blogspot.com/2016/03/caspnet-gridview-excel-xlsx-openxml-sdk.html

public static DataTable GridViewtoDataTable(GridView gv)
        {
            //GridView2DataTable
            // 只會抓 <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" /> 這類的值，
            // 執行【將這個欄位轉為 TemplateField】的欄位不會被抓到。 
            gv.AllowSorting = false;    // 支援排序時，標題不是文字，要改抓 LinkButton 的文字，麻煩，不如暫時取消
            gv.AllowPaging = false; // 分頁狀態下，只能抓到當頁的10筆，必須取消分頁
            gv.DataBind();

            DataTable dt = new DataTable();
            DataColumn dc;
            DataRow dr;
            for (int i = 0; i < gv.Columns.Count; i++)
            {
                dc = new DataColumn();
                dc.ColumnName = gv.Columns[i].HeaderText;
                if (gv.Columns[i].Visible != false)
                {
                    dt.Columns.Add(dc);
                }
            }
            int index = 0;
            for (int i = 0; i < gv.Rows.Count; i++)
            {
                dr = dt.NewRow();
                index = 0;
                for (int j = 0; j < gv.Columns.Count; j++)
                {
                    
                    if (gv.Columns[j].Visible != false)
                    {
                        //dr[j] = gv.Rows[i].Cells[j].Text;
                        dr[index] = gv.Rows[i].Cells[j].Text;
                        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;
                                        dr[index] = labelText;
                                    }
                                }
                            }
                        }
                        index++;
                    }
                }
                dt.Rows.Add(dr);
            }
            gv.AllowSorting = true;
            gv.AllowPaging = true;
            gv.DataBind();

            return dt;
        }

補充：如果不做下面設定

gv.AllowSorting = false;    // 支援排序時，標題不是文字，要改抓 LinkButton 的文字，麻煩，不如暫時取消

標題純文字這樣抓

// 標題純文字這樣抓
for (int i = 0; i < girdViewtest.HeaderRow.Cells.Count; i++)
{
    dt.Columns.Add(girdViewtest.HeaderRow.Cells[i].Text.Replace("&nbsp;", ""));
}

// 如果 GridView 設定為可排序，header 就要改抓 LinkButton 的文字
for (int i = 0; i < gridView.HeaderRow.Cells.Count; i++)
{
    if (gridView.HeaderRow.Cells[i].HasControls())
    {
        // 如果 GridView 設定為可排序，header 就要改抓 LinkButton 的文字
        // Linkbutton is in index 0 of the control
        if (gridView.HeaderRow.Cells[i].Controls[0] is LinkButton)
        {
   LinkButton headerControl = gridView.HeaderRow.Cells[i].Controls[0] as LinkButton;
            string headerName = headerControl.Text;
  }     
 }     
}

2020-04-27 改為下面

public static DataTable GridViewtoDataTable(GridView gv)
{
 //GridView2DataTable
 // 只會抓 <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" /> 這類的值，
 // 執行【將這個欄位轉為 TemplateField】的欄位不會被抓到。 
 gv.AllowSorting = false;    // 支援排序時，標題不是文字，要改抓 LinkButton 的文字，麻煩，不如暫時取消
 gv.AllowPaging = false; // 分頁狀態下，只能抓到當頁的10筆，必須取消分頁
 gv.DataBind();

 DataTable dt = new DataTable();
 DataColumn dc;
 DataRow dr;

 // 標題純文字這樣抓
 //for (int i = 0; i < gv.Columns.Count; i++)
 //{
 //    dc = new DataColumn();
 //    dc.ColumnName = gv.Columns[i].HeaderText;
 //    dt.Columns.Add(dc);
 //}

 // 標題純文字這樣抓
 for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
 {
  dt.Columns.Add(gv.HeaderRow.Cells[i].Text.Replace("&nbsp;", ""));
 }

 // 抓資料
 //for (int i = 0; i < gv.Rows.Count; i++)
 //{
 //    dr = dt.NewRow();
 //    for (int j = 0; j < gv.Columns.Count; j++)
 //    {
 //        dr[j] = gv.Rows[i].Cells[j].Text;
 //    }
 //    dt.Rows.Add(dr);
 //}

 foreach (GridViewRow row in gv.Rows)
 {
  DataRow datarow = dt.NewRow();
  // 用  gv.Columns.Count 可能抓到 0，用 row.Cells.Count 
  for (int i = 0; i < row.Cells.Count; i++)
  {
   datarow[i] = row.Cells[i].Text.Replace("&nbsp;", "");
  }
  dt.Rows.Add(datarow);
 }
 gv.AllowSorting = true;
 gv.AllowPaging = true;
 gv.DataBind();

 return dt;
}

(完)

浮雲雅築

2020年4月17日星期五

[研究][C#][ASP.NET][WebForm] 用 ClosedXML 0.95.0 把 GridView1 匯出成 Excel (.xlsx)

沒有留言:

張貼留言

2020年4月17日 星期五

[研究][C#][ASP.NET][WebForm] 用 ClosedXML 0.95.0 把 GridView1 匯出成 Excel (.xlsx)

沒有留言:

張貼留言

2020年4月17日星期五