浮雲雅築: OpenXML

顯示具有 OpenXML 標籤的文章。顯示所有文章

2025年3月31日星期一

[研究]哪個元件 OpenXML, ClosedXML, Aspose Cells, ExcelDataReader, NPOI 匯出成 .xlsx 會較省記憶體、較快？

[研究]哪個元件 OpenXML, ClosedXML, Aspose Cells, ExcelDataReader, NPOI 匯出成 .xlsx 會較省記憶體、較快？

2025-03-31

ASP.NET, WebForm, 從 SQL Server 讀取20萬筆資料，用哪個元件 OpenXML, ClosedXML, Aspose Cells, ExcelDataReader, NPOI 匯出成 .xlsx 會較省記憶體、較快

ChatGPT 說：

在處理大量資料（如 20 萬筆資料）並將其匯出為 .xlsx 格式時，選擇合適的元件對於效能與記憶體使用至關重要。以下是你列出來的幾個元件的比較，根據記憶體消耗與效能來評估：

1. OpenXML SDK

記憶體消耗：OpenXML 是處理 .xlsx 格式的底層 API，直接與 Excel 檔案結構互動，因此它的記憶體消耗相對較低。它不會加載整個工作簿進記憶體，而是逐步寫入檔案，因此適合處理大量資料。
效能：效能相對較快，特別是在處理簡單的資料寫入時。它的低層級操作使其可以有效處理大量資料，而不會因為大量的記憶體消耗而產生效能瓶頸。
缺點：操作上相對較為繁瑣，需手動處理各種 Excel 的細節（如格式化、資料填充等）。
結論：如果記憶體消耗是主要考量，OpenXML SDK 是處理大量資料時效能最好且最省記憶體的選擇。

2. ClosedXML

記憶體消耗：ClosedXML 會加載整個工作簿進記憶體，這意味著當處理大量資料時，它的記憶體消耗會比較高。對於 20 萬筆資料，記憶體消耗會比 OpenXML 高。
效能：ClosedXML 提供高層次的 API，使得開發人員能夠快速上手，但它的效能在處理大型資料集時會受到記憶體限制。
優點：API 使用簡單，開發過程中可以很容易地進行格式化和其他 Excel 特性設定。
缺點：處理大型資料集時，記憶體消耗和效能可能會成為瓶頸。
結論：如果開發速度和易用性比效能更重要，ClosedXML 是不錯的選擇，但在處理大量資料時記憶體消耗會較高。

3. Aspose.Cells

記憶體消耗：Aspose.Cells 是一個商業元件，對於大型資料集的處理有很好的效能，記憶體消耗比 OpenXML 稍高，但仍然可以處理較大資料集。對於 20 萬筆資料，它的效能表現會較為穩定。
效能：Aspose.Cells 提供非常豐富的功能和強大的性能，特別是在處理大型文件和複雜格式化時非常優秀。然而，它是商業授權的元件，會有額外成本。
優點：功能強大，能夠處理複雜的格式、公式和圖表，並且效能較為穩定。
缺點：商業授權，需支付費用。
結論：如果需要高度功能性和穩定性，並且能夠接受商業授權的費用，Aspose.Cells 是一個很好的選擇，但對於 20 萬筆資料來說，記憶體消耗會相對較高。

4. ExcelDataReader

記憶體消耗：ExcelDataReader 主要用於讀取 Excel 檔案，而不是寫入，它的記憶體消耗相對較低。它支援 .xlsx 格式的讀取，但並不直接支援寫入檔案，因此不適用於匯出資料。
效能：效能快速，特別是在處理讀取 Excel 檔案時，但不適用於資料匯出。
結論：ExcelDataReader 主要用於讀取資料，並不適用於從 SQL Server 匯出資料到 .xlsx 格式。

5. NPOI

記憶體消耗：NPOI 在處理大型資料集時表現良好，它的記憶體消耗比 ClosedXML 要低，但會比 OpenXML 高一些。它不會加載整個工作簿到記憶體中，這使得它在記憶體消耗上比 ClosedXML 更有優勢。
效能：效能相對較快，特別是當資料不需要複雜的格式化時。它的底層操作較為簡單，能夠有效處理大量資料，且記憶體消耗較為節省。
缺點：API 相對較為底層，對於需要高階格式化和功能的需求，可能會顯得繁瑣。
結論：NPOI 是一個不錯的選擇，特別是在需要低記憶體消耗的情況下，它在處理大量資料時表現不錯，且比 ClosedXML 更為輕量。

結論：

最省記憶體且效能最佳：OpenXML SDK。這是處理大量資料時最節省記憶體並且效能最好的選擇。
較簡單的高層次 API，效能較佳：NPOI。對於大量資料的匯出，它的記憶體消耗比 ClosedXML 低，並且效能也相對較好。
需要高功能性（格式化、圖表等），且能接受費用：Aspose.Cells，但記憶體消耗會較高。
如果你的主要關注點是處理 20 萬筆資料的效能與記憶體消耗，推薦使用 OpenXML SDK 或 NPOI，根據你的需求選擇適合的 API 層級。

(完)

2023年12月22日星期五

[研究][ASP.NET]ClosedXML 的 OpenXml 從 2.20.0 升級 3.0.0 成功，執行失敗

[研究][ASP.NET]ClosedXML 的 OpenXml 從 2.20.0 升級 3.0.0 成功，執行失敗

[研究][ASP.NET]找不到方法: 'DocumentFormat.OpenXml.OpenXmlElementList DocumentFormat.OpenXml.OpenXmlElement.get_ChildElements()'。

2023-12-22、2024-02-26更新

環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + ClosedXML

********************************************************************************

(下圖) Click 可看 100% 圖

********************************************************************************

'/' 應用程式中發生伺服器錯誤。

找不到方法: 'DocumentFormat.OpenXml.OpenXmlElementList DocumentFormat.OpenXml.OpenXmlElement.get_ChildElements()'。

描述: 在執行目前 Web 要求的過程中發生未處理的例外狀況。請檢閱堆疊追蹤以取得錯誤的詳細資訊，以及在程式碼中產生的位置。

例外狀況詳細資訊: System.MissingMethodException: 找不到方法: 'DocumentFormat.OpenXml.OpenXmlElementList DocumentFormat.OpenXml.OpenXmlElement.get_ChildElements()'。

********************************************************************************

Method not found: 'DocumentFormat.OpenXml.OpenXmlElementList DocumentFormat.OpenXml.OpenXmlElement.get_ChildElements()'. #1608
https://github.com/dotnet/Open-XML-SDK/issues/1608

Migration to v3.0.0
https://learn.microsoft.com/en-us/office/open-xml/migration/migrate-v2-to-v3

********************************************************************************

參考

https://github.com/dotnet/Open-XML-SDK/issues/1608

實際測試發現是 NuGet 把 DocumentFormat.OpenXml 從 2.20.0 升級到 3.0.0 後發生問題， DocumentFormat.OpenXml.Framework 可以維持 3.0.0，不放心就一起也降到 2.20.0

檢查 packages.config 可知目前版本，

  <package id="DocumentFormat.OpenXml" version="3.0.0" targetFramework="net48" />
  <package id="DocumentFormat.OpenXml.Framework" version="3.0.0" targetFramework="net48" />

降版後是

  <package id="DocumentFormat.OpenXml" version="2.20.0" targetFramework="net48" />
  <package id="DocumentFormat.OpenXml.Framework" version="2.20.0" targetFramework="net48" />

實際測試，把 DocumentFormat.OpenXml.Framework 移除也正常運作。

********************************************************************************

請問 DocumentFormat.OpenXml 和 DocumentFormat.OpenXml.Framework 套件差異，後者必須存在嗎 ?

ChatGPT

截至我最後一次更新知識庫的時間（2022年1月），並沒有關於DocumentFormat.OpenXml.Framework的明確資訊。然而，根據我了解的情況，DocumentFormat.OpenXml是一個用於處理Office文件（如Word、Excel和PowerPoint）的.NET程式庫，而DocumentFormat.OpenXml.Framework則可能是一個與OpenXml相關的框架或工具集。

如果你遇到DocumentFormat.OpenXml.Framework的問題，建議檢查官方文檔或相關資源，以獲得最新的資訊。通常來說，開發人員使用DocumentFormat.OpenXml來處理Open XML文件，而Framework可能是一些附加的工具或支援額外功能的套件。確保查閱官方文檔以確定如何正確使用這些套件。

請注意，.NET生態系統中的套件和工具可能會經常更新，因此建議查看官方文檔或相關社群以獲取最新的資訊。

********************************************************************************

Open XML SDK 的最近更新
[3.0.0] - 2023-11-15
https://learn.microsoft.com/zh-cn/office/open-xml/what-s-new-in-the-open-xml-sdk
https://learn.microsoft.com/zh-tw/office/open-xml/what-s-new-in-the-open-xml-sdk

核心基礎結構現在包含在新的套件DocumentFormat.OpenXml.Framework 中。類型化類別仍位於DocumentFormat.OpenXml 中。這意味著，可以引用DocumentFormat.OpenXml，但仍編譯相同的類型，但如果想要較小的套件，可以只依賴框架包。
減少了DocumentFormat.OpenXml.Framework (#1471) 以外的套件的不必要的目標框架

********************************************************************************

2024-02-26 補

想要測試撰寫 ClosedXML 0.102.2 + DocumentFormat.OpenXml 3.0.0 的程式，發現 NuGet 安裝 ClosedXML 0.102.2 最新版，預設搭配 DocumentFormat.OpenXml 2.16.0，升級到 3.0.0 或 3.0.1 時，被禁止了。去年底是可以升級，但是執行時會失敗；現在直接禁止升級，算是省了麻煩。

嚴重性程式碼說明專案檔案行隱藏項目狀態

錯誤無法解析相依性。'DocumentFormat.OpenXml 3.0.1' 與 'ClosedXML 0.102.2 條件約束: DocumentFormat.OpenXml (>= 2.16.0 && < 3.0.0)' 不相容。

********************************************************************************

(完)

相關

2020年9月9日星期三

[研究][ASP.NET][WebForm]使用 DocumentFormat.OpenXml 2.11.3 讀取、寫入 Excel .xlsx 的 Cell 值

[研究][ASP.NET][WebForm]使用 DocumentFormat.OpenXml 2.11.3 讀取、寫入 Excel .xlsx 的 Cell 值

2020-09-09

Visual Studio 2019 + Web Application ( WebForm )

NuGet 安裝 DocumentFormat.OpenXml 2.11.3

Default.axpx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="OpenXMLTest.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:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
            <br />
            <asp:Label ID="Label_Message" 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.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

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

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string openFileName = HttpContext.Current.Server.MapPath("Test.xlsx");

            // Read
            string value = GetCellValue(openFileName, "工作表1", "A1");
            Label_Message.Text = value;

            // Write
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(openFileName, true))
            {
                WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "工作表1");

                if (worksheetPart != null)
                {
                    //Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);
                    //Cell cell = GetCell(worksheetPart.Worksheet, "C",3 );
                    Cell cell = InsertCellInWorksheet("C", 5, worksheetPart);
                    cell.CellValue = new CellValue("寫入文字");
                    //cell.DataType =new EnumValue<CellValues>(CellValues.Number);
                    cell.DataType = new EnumValue<CellValues>(CellValues.String);

                    //var cellText = GetCell(spreadSheet.WorkbookPart, "A", "1");

                    // Save the worksheet.
                    worksheetPart.Worksheet.Save();
                    spreadSheet.Save();
                }
            }
        }
        // --------------------------------------------------------------------------------
        // https://docs.microsoft.com/en-us/office/open-xml/how-to-retrieve-the-values-of-cells-in-a-spreadsheet
        // Retrieve the value of a cell, given a file name, sheet name, 
        // and address name.
        public static string GetCellValue(string fileName, string sheetName, string addressName)
        {
            string value = null;

            // Open the spreadsheet document for read-only access.
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
            {
                // Retrieve a reference to the workbook part.
                WorkbookPart wbPart = document.WorkbookPart;

                // Find the sheet with the supplied name, and then use that 
                // Sheet object to retrieve a reference to the first worksheet.
                Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
                  Where(s => s.Name == sheetName).FirstOrDefault();

                // Throw an exception if there is no sheet.
                if (theSheet == null)
                {
                    throw new ArgumentException("sheetName");
                }

                // Retrieve a reference to the worksheet part.
                WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));

                // Use its Worksheet property to get a reference to the cell 
                // whose address matches the address you supplied.
                Cell theCell = wsPart.Worksheet.Descendants<Cell>().
                  Where(c => c.CellReference == addressName).FirstOrDefault();

                // If the cell does not exist, return an empty string.
                if (theCell.InnerText.Length > 0)
                {
                    value = theCell.InnerText;

                    // If the cell represents an integer number, you are done. 
                    // For dates, this code returns the serialized value that 
                    // represents the date. The code handles strings and 
                    // Booleans individually. For shared strings, the code 
                    // looks up the corresponding value in the shared string 
                    // table. For Booleans, the code converts the value into 
                    // the words TRUE or FALSE.
                    if (theCell.DataType != null)
                    {
                        switch (theCell.DataType.Value)
                        {
                            case CellValues.SharedString:

                                // For shared strings, look up the value in the
                                // shared strings table.
                                var stringTable =
                                    wbPart.GetPartsOfType<SharedStringTablePart>()
                                    .FirstOrDefault();

                                // If the shared string table is missing, something 
                                // is wrong. Return the index that is in
                                // the cell. Otherwise, look up the correct text in 
                                // the table.
                                if (stringTable != null)
                                {
                                    value =
                                        stringTable.SharedStringTable
                                        .ElementAt(int.Parse(value)).InnerText;
                                }
                                break;

                            case CellValues.Boolean:
                                switch (value)
                                {
                                    case "0":
                                        value = "FALSE";
                                        break;
                                    default:
                                        value = "TRUE";
                                        break;
                                }
                                break;
                        }
                    }
                }
            }
            return value;
        }
        // --------------------------------------------------------------------------------
        private WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
        {
            IEnumerable<Sheet> sheets =
               document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
               Elements<Sheet>().Where(s => s.Name == sheetName);

            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.

                return null;
            }

            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)
                 document.WorkbookPart.GetPartById(relationshipId);
            return worksheetPart;
        }
        // --------------------------------------------------------------------------------
        // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
        // If the cell already exists, returns it. 
        private Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (cell.CellReference.Value.Length == cellReference.Length)
                    {
                        if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                        {
                            refCell = cell;
                            break;
                        }
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                worksheet.Save();
                return newCell;
            }
        }
        // --------------------------------------------------------------------------------
    }
}

執行前

(下圖) 執行後

(完)

相關文件

[研究][ASP.NET][WebForm]使用 DocumentFormat.OpenXml 2.11.3 讀取、寫入 Excel .xlsx 的 Cell 值

http://shaurong.blogspot.com/2020/09/aspnetwebform-documentformatopenxml.html

[研究][ASP.NET][WebForm]使用 ClosedXML 0.95.3 讀取、寫入 Excel .xlsx 的 Cell 值

http://shaurong.blogspot.com/2020/09/aspnetwebform-closedxml-0953-excel-xlsx.html

2020年4月25日星期六

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

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

2020-04-25
2020-04-27 更新

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="OpenXMLExportTest.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:FileUpload ID="FileUpload1" runat="server" /><br />
&nbsp;<asp:Button ID="Button_Import_by_OpenXML" runat="server" Text="Import" OnClick="Button_Import_by_OpenXML_Click" />
            <br />
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>" 
                SelectCommand="SELECT * FROM [MyTable]"></asp:SqlDataSource>
            <br />
            <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="cname" HeaderText="cname" SortExpression="cname" Visible="true" />
                    <%--<asp:BoundField DataField="addr" HeaderText="addr" SortExpression="addr" />--%>
                    <asp:TemplateField HeaderText="addr" SortExpression="addr">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("addr") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Bind("addr") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
            <asp:Label ID="Label_MSG1" runat="server"></asp:Label>
        </div>
    </form>
</body>
</html>

Default.axpx.cs

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web.Configuration;
using System.Web.UI.WebControls;

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

        }
        protected void Button_Import_by_OpenXML_Click(object sender, EventArgs e)

        {
            Label_MSG1.Text = "";
            string queryString = "";
            bool importSuccess = true;
            if (FileUpload1.HasFile)
            {
                //string fd = (string)ConfigurationManager.AppSettings["InformationSystemFiles"] + "\\";
                string fd = @"D:\Production\TempImageFiles";
                if (!System.IO.Directory.Exists(fd))
                {
                    System.IO.Directory.CreateDirectory(fd);
                }
                string extFilename = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                if (extFilename != ".xlsx")
                {
                    Label_MSG1.Text = "請上傳副檔名.xlsx檔案";
                    //ScriptManager.RegisterStartupScript(this, GetType(), "Popup1", "erroralert('" + "請上傳副檔名.xlsx檔案！" + "');", true);
                    return;
                }
                //string FileName = fd + ID + System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                //string fileName = "D:\\Production\\TempImageFiles\\" + System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                string fileName = "D:\\Production\\TempImageFiles\\" + FileUpload1.FileName;
                FileUpload1.SaveAs(fileName);

                //using (SpreadsheetDocument doc = SpreadsheetDocument.Open(@"F:\OpenXml.xlsx", false))
                try
                {
                    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
                    {
                        WorkbookPart wbPart = doc.WorkbookPart;
                        Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.FirstOrDefault();
                        Worksheet worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet;
                        SheetData sheetData = (SheetData)worksheet.ChildElements.FirstOrDefault();
                        int i = 0;
                        foreach (var row in sheetData.ChildElements)
                        {
                            //foreach (var cell in (row as Row).ChildElements)
                            //{
                            //    var cellValue = (cell as Cell).CellValue;
                            //    if (cellValue != null)
                            //    {
                            //        Console.WriteLine(cellValue.Text);
                            //    }
                            //}
                            // 跳過標題列
                            if (i == 0)
                            {
                                i++;
                                continue;
                            }
                            string SN = ((Cell)row.ChildElements[0]).CellValue.Text;
                            string cname = ((Cell)row.ChildElements[1]).CellValue.Text;
                            string addr = ((Cell)row.ChildElements[2]).CellValue.Text;
                            // SN 不匯入，不管了
                            //　檢查 cname
                            if (string.IsNullOrEmpty(cname))
                            {
                                Label_MSG1.Text = "cname不可空";
                                return;
                            }
                            //　檢查「年份」
                            if (string.IsNullOrEmpty(addr))
                            {
                                Label_MSG1.Text = "addr不可空";
                                return;
                            }
                            //----
                            queryString = queryString + @"INSERT INTO [dbo].[MyTable] ([cname],[addr])
                                VALUES (N'" + cname + "',N'" + addr + "'); ";
                        } // foreach

                        using (SqlConnection connection = new SqlConnection(

                            WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString))
                        {
                            SqlCommand command = new SqlCommand(queryString, connection);
                            command.Connection.Open();
                            command.ExecuteNonQuery();

                            GridView1.DataSourceID = "";
                            GridView1.DataSourceID = "SqlDataSource1";
                            GridView1.DataBind();
                            Label_MSG1.ForeColor = System.Drawing.Color.Green;
                            Label_MSG1.Text = "匯入成功！";
                            Page.ClientScript.RegisterStartupScript(Page.GetType(), "message",

                                "<script language='javascript' defer>alert('匯入成功！');</script>");

                        } // using
                    } // using
                }
                catch (Exception ex)
                {
                    //throw;
                    if (ex != null)
                    {
                        Label_MSG1.Text = ex.Message.ToString();
                    }
                    else
                    {
                        Label_MSG1.Text = "匯入失敗！";
                        Page.ClientScript.RegisterStartupScript(Page.GetType(), "message",

                            "<script language='javascript' defer>alert('匯入失敗！');</script>");
                    }
                    Label_MSG1.ForeColor = System.Drawing.Color.Red;
                    importSuccess = false;
                }
            } //if (FileUpload1.HasFile)
            else
            {
                Label_MSG1.Text = "請選擇.xlsx檔案";
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message",

                    "<script language='javascript' defer>alert('請選擇.xlsx檔案！');</script>");
                return;
            }
        }
    }
}

日期欄位檢查方式

//----
// 檢查「購置日期」 myDate

// 檢查「日期」格式 YYYY/MM/DD，但 YYYY/M/D、YYYY/MM/D、YYYY/M/DD 不行
//string regularExpressions = @"^([2][0]\d{2}\/([0]\d|[1][0-2])\/([0-2]\d|[3][0-1]))$|^([2][0]\d{2}\/([0]\d|[1][0-2])\/([0-2]\d|[3][0-1]))$";
//(19 | 20)[0 - 9]{ 2}[- /.] (0[1-9]|1[012])[- /.] (0[1-9]|[12] [0-9]|3[01])

// YYYY/MM/DD、YYYY/MM/D、YYYY/M/DD、YYYY/M/D 都可以，但 9999/9/99 會過
// 「2020/3/1 上午 12:00:00」實際測試也會過 ( Why ?) DateTime.TryParse 得到相同值；
string regularExpressions = @"^(\d{4}(?:/\d{1,2}){2})";
Match m = Regex.Match(myDate, regularExpressions);

if (m.Success)
{
 DateTime temp;
 if (DateTime.TryParse(myDate, out temp))
 {
  //myDate=temp.ToString("yyyy/MM/dd HH:mm:ss");
  // Excel 儲存格格式若為【日期】，ClosedXML 對「2020/3/1」回傳「2020/3/1 上午 00:00:00」
  // Excel 儲存格格式若為【文字】，ClosedXML 對「2020/3/1」回傳「2020/3/1」
  // 插入 SQL Server 會出現【從字元字串轉換成日期及/或時間時，轉換失敗。】錯誤，要想辦拿拿掉【上午】兩字
  myDate = temp.ToString("yyyy/MM/dd");
 }
 else
 {
  Label_ExportImportMSG.Text = "<font color=red>第 " + lineNo.ToString() + "行，「購置日期」您輸入為「" + myDate + "」，日期不合理。</font>";
  return;
 }
 //return true;
}
else
{
 Label_ExportImportMSG.Text = "<font color=red>第 " + lineNo.ToString() + "行，「購置日期」您輸入為「" + myDate +

     "」，格式不符 YYYY/MM/DD 或 YYYY/MM/D 或 YYYY/M/DD 或 YYYY/MM/DD。</font>";
 return;
}
//----

(完)

相關文章

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

訂閱：文章 (Atom)

2025年3月31日 星期一

[研究]哪個元件 OpenXML, ClosedXML, Aspose Cells, ExcelDataReader, NPOI 匯出成 .xlsx 會較省記憶體、較快？

2023年12月22日 星期五

[研究][ASP.NET]ClosedXML 的 OpenXml 從 2.20.0 升級 3.0.0 成功，執行失敗

2020年9月9日 星期三

[研究][ASP.NET][WebForm]使用 DocumentFormat.OpenXml 2.11.3 讀取、寫入 Excel .xlsx 的 Cell 值

2020年4月25日 星期六

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

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

2025年3月31日星期一

2023年12月22日星期五

2020年9月9日星期三

2020年4月25日星期六