浮雲雅築: [研究][C#][ASP.NET][WebForm] 使用 OpenXML 2.10.1 (DocumentFormat.OpenXml) 把 GridView 匯出成 .xlsx

[研究][C#][ASP.NET][WebForm] 使用 Microsoft OpenXML SDK 2.10.1 ( DocumentFormat.OpenXml ) 把 GridView 匯出成 .xlsx

2020-04-25
2020-04-27 整個 Code 大改 (因為「非自動生成列」會比「自動生成列」、以及欄位是否 visible 問題)
2020-04-28 匯出檔名強化

Open XML SDK 2.5 for Microsoft Office
https://www.microsoft.com/en-us/download/details.aspx?id=30425

微軟官方下載只提供到 2.5 版，NuGet 才能下載到更新版。

**********

另外 OpenXML 在不同版本時，API function method 用法不完全相同。

Welcome to the Open XML SDK 2.5 for Office
2017/11/01
https://docs.microsoft.com/zh-tw/office/open-xml/open-xml-sdk

DocumentFormat.OpenXml 2.7.2
https://docs.microsoft.com/zh-tw/dotnet/api/documentformat.openxml.spreadsheet?view=openxml-2.7.2

DocumentFormat.OpenXml 2.8.1
https://docs.microsoft.com/zh-tw/dotnet/api/documentformat.openxml.spreadsheet?view=openxml-2.8.1

**********

本文使用下面工具

Visual Studio Enterprise 2019 v16.5.4
NuGet 安裝 OpenXML 2.10. 1 (DocumentFormat.OpenXml)

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication4.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="True" 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" Visible="false" />
                    <%--<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="True" 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" Visible="false" />
                    <%--<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="Label1" runat="server"></asp:Label>
        </div>
    </form>
</body>
</html>

Default.aspx.cs

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication4
{
    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;
            GridViewExportToXlsxByOpenXML(GridView1);
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            GridViewExportToXlsxByOpenXML(GridView2);
        }
        private string GridViewExportToXlsxByOpenXML(GridView gridview)
        {
            // Import
            // https://docs.microsoft.com/en-us/office/open-xml/how-to-parse-and-read-a-large-spreadsheet

            // Export
            // https://docs.microsoft.com/en-us/office/open-xml/how-to-create-a-spreadsheet-document-by-providing-a-file-name

            //Insert text into a cell in a spreadsheet document(Open XML SDK)
            //https://docs.microsoft.com/zh-tw/office/open-xml/how-to-insert-text-into-a-cell-in-a-spreadsheet

            string year = DateTime.Now.Year.ToString();
            string mainFileName = Session["jiguanname"].ToString() + "(" + Session["oid"].ToString() + ")" + year + "年設備清冊";

            //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";

            try
            {
                GridView gv = gridview;

                bool oldAllowPaging = gv.AllowPaging;
                bool oldAllowSorting = gv.AllowSorting;
                gv.AllowSorting = false; // 支援排序時，標題不是文字，要改抓 LinkButton 的文字，麻煩，不如暫時取消
                gv.AllowPaging = false; // 分頁狀態下，只能抓到當頁的10筆，必須取消分頁
                gv.DataBind();

                SpreadsheetDocument spreadsheetDocument = null;
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                //Response.AddHeader("content-disposition", "attachment;filename=Export.xlsx");

                Response.AddHeader("content-disposition", "attachment;filename=" + outFileName );
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    spreadsheetDocument = SpreadsheetDocument.Create(MyMemoryStream, SpreadsheetDocumentType.Workbook, true);
                    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
                    // Add a WorksheetPart to the WorkbookPart.
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());

                    // Add Sheets to the Workbook.
                    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
                    // Append a new worksheet and associate it with the workbook.
                    Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "工作表1" };
                    sheets.Append(sheet);
                    // Get the sheetData cell table.
                    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                    // Add a column row to the cell table.
                    Row colrow;
                    colrow = new Row() { RowIndex = 1 };
                    sheetData.Append(colrow);

                    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;
                                Cell CReceiptID = new Cell();
                                CReceiptID.CellValue = new CellValue(columnName);
                                CReceiptID.DataType = new EnumValue<CellValues>(CellValues.String);
                                colrow.InsertAt(CReceiptID, visibleIndex);
                                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;

                                Cell CReceiptID = new Cell();
                                CReceiptID.CellValue = new CellValue(columnName);
                                CReceiptID.DataType = new EnumValue<CellValues>(CellValues.String);
                                colrow.InsertAt(CReceiptID, visibleIndex);
                                visibleIndex++;
                            //}
                        }
                    }

                    // 資料列這樣抓
                    // Add rows to the cell table
                    UInt32Value DataIndex = new UInt32Value();
                    DataIndex.Value = 2;

                    for (int i = 0; i < gv.Rows.Count; i++)
                    {
                        Row row;
                        row = new Row() { RowIndex = DataIndex.Value };
                        sheetData.Append(row);
                        visibleIndex = 0;

                        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;

                                    // 執行【將這個欄位轉為 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("&nbsp;", "");

                                    Cell CellReceiptID = new Cell();
                                    CellReceiptID.CellValue = new CellValue(dataText);
                                    //CellReceiptID.DataType = new EnumValue<CellValues>(CellValues.Number);
                                    CellReceiptID.DataType = new EnumValue<CellValues>(CellValues.String);
                                    row.InsertAt(CellReceiptID, visibleIndex);
                                    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("&nbsp;", "");
Cell CellReceiptID = new Cell();
                                    CellReceiptID.CellValue = new CellValue(dataText);
                                    //CellReceiptID.DataType = new EnumValue<CellValues>(CellValues.Number);
                                    CellReceiptID.DataType = new EnumValue<CellValues>(CellValues.String);
                                    row.InsertAt(CellReceiptID, visibleIndex);
                                    visibleIndex++;
                                //} // if
                            } // else
                        } // for (int j = 0; j < gv.HeaderRow.Cells.Count; j++)
                        DataIndex.Value++;
                    } // for (int i = 0; i < gv.Rows.Count; i++)

                    worksheetPart.Worksheet.Save();
                    workbookpart.Workbook.Save();
                    spreadsheetDocument.Close();
                    MyMemoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }

                gv.AllowPaging = oldAllowPaging;
                gv.AllowSorting = oldAllowSorting;
                gv.DataBind();

            }
            catch (Exception ex)
            {
                Label1.ForeColor = System.Drawing.Color.Red;
                string exMsg = "";
                if (ex != null)
                {
                    exMsg = ex.ToString();
                }
                else
                {
                    exMsg = "不明錯誤。";
                }
                Label1.Text = exMsg;

                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('" + exMsg + "');</script>");
                return exMsg;
            }

            Label_MSG1.ForeColor = System.Drawing.Color.Green;

            Label_MSG1.Text = "匯出成功。";
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('匯出成功。');</script>");
            return "OK";
        }

        
    }
}

匯出後，用 Microsoft Excel 2016 開啟正常，沒有警告或錯誤。

(完)

相關文章

[研究][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

浮雲雅築

2020年4月25日星期六

[研究][C#][ASP.NET][WebForm] 使用 OpenXML 2.10.1 (DocumentFormat.OpenXml) 把 GridView 匯出成 .xlsx

沒有留言:

張貼留言

2020年4月25日 星期六

[研究][C#][ASP.NET][WebForm] 使用 OpenXML 2.10.1 (DocumentFormat.OpenXml) 把 GridView 匯出成 .xlsx

沒有留言:

張貼留言

2020年4月25日星期六