浮雲雅築: [研究]ASP.NET,WebForm,用 NuGet OdsReaderWriter 1.0.10 匯出 .ods

[研究]ASP.NET,WebForm,用 NuGet OdsReaderWriter 1.0.10 匯出 .ods

2024-03-30

環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019 + SQL Server Management Studio (SSMS) 19

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

發現 NuGet 提供 OdsReaderWriter 1.0.10，其實源頭也相同是 CodeProject 那篇，但是程式寫法有點不同；也不用自己另外手動安裝 Ionic.Zip 或 DotNetZip，會自動相依安裝。

NuGet Gallery | OdsReaderWriter 1.0.10
https://www.nuget.org/packages/OdsReaderWriter

GitHub - Zaretto/ODSReaderWriter: Open Document Spreadsheet reader writer
https://github.com/Zaretto/ODSReaderWriter
這邊直接下載 Code，敝人用 Visual Studio 2019 直接編譯是不能用。

How to Read and Write ODF/ODS Files (OpenDocument Spreadsheets) - CodeProject
https://www.codeproject.com/Articles/38425/How-to-Read-and-Write-ODF-ODS-Files-OpenDocument-2

WebApplication1

正在安裝:

DotNetZip.Reduced.1.9.1.8

OdsReaderWriter.1.0.10

OdsReaderWriter 1.0.10

https://www.nuget.org/packages/OdsReaderWriter

GPL-2.0-only

How to Read and Write ODF/ODS Files (OpenDocument Spreadsheets)

https://www.codeproject.com/Articles/38425/How-to-Read-and-Write-ODF-ODS-Files-OpenDocument-2

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

提供 odsreadwrite.zip

註：ChatGPT

The Code Project Open License (CPOL) 是由 The Code Project 社群發布的一種軟體授權。以下是關於 CPOL 的主要重點：

寬鬆的授權：CPOL 允許用戶自由使用、修改和分發軟體，不論是二進位形式還是原始碼形式。
不要求開源：與 GPL 等授權不同，CPOL 不要求衍生作品必須開源。您可以在專有項目中使用 CPOL 授權的代碼，而不必公開自己的原始碼。
歸屬要求(Attribution)：在分發軟體或衍生作品時，必須給予原作者適當的歸屬。
免責聲明：該授權通常包括免責聲明，表示軟體是“按原樣”提供的，沒有任何保證。

總結來說，CPOL 允許您在遵守歸屬和免責條款的前提下，自由使用、修改和分發軟體，而無需開源您的代碼。

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

Web.Config

<?xml version="1.0" encoding="utf-8"?>
<!--
  如需如何設定 ASP.NET 應用程式的詳細資訊，請前往
  https://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <connectionStrings>
    <add name="MyDBConnectionString" connectionString="Data Source=127.0.0.1;Initial Catalog=MyDB;User ID=sa;Password=P@ssw0rd"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.8" />
    <httpRuntime targetFramework="4.8" />
  </system.web>
  <system.codedom>
    <compilers>
      <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701" />
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+" />
    </compilers>
  </system.codedom>
  <appSettings>
    <add key="TempFolder" value="D:\Production\TempImageFiles\" />
  </appSettings>
</configuration>

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="WebApplication2.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">
        <asp:SqlDataSource ID="SqlDataSource_Field2" runat="server"
            ConnectionString="<%$ ConnectionStrings:MyDBConnectionString %>"
            SelectCommand="SELECT N'全部' AS [Field2], 0 AS Priority
            UNION
            SELECT DISTINCT [Field2], 1 AS Priority FROM [MyTable] ORDER BY Priority, [Field2]"></asp:SqlDataSource>
        <asp:DropDownList ID="DropDownList_Field2" runat="server"
            AutoPostBack="True" DataSourceID="SqlDataSource_Field2"
            DataTextField="Field2" DataValueField="Field2">
        </asp:DropDownList>
        <br />
        <asp:Button ID="Button_Export_ODS_by_OdsReaderWriter_DotNetZip_IonicZip" runat="server" Text="匯出(.ods)"
            OnClick="Button_Export_ODS_by_OdsReaderWriter_DotNetZip_IonicZip_Click" /><br />
        <asp:Label ID="Label_MSG1" runat="server" Text="Label"></asp:Label>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:MyDBConnectionString %>"
            SelectCommand="--TSQL--
            SELECT * FROM [MyTable]
            WHERE ([Field2]=@Field2 OR @Field2=N'全部');">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList_Field2" Name="Field2" PropertyName="SelectedValue" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SN" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
                <asp:BoundField DataField="Field1" HeaderText="Field1" SortExpression="Field1" />
                <asp:BoundField DataField="Field2" HeaderText="Field2" SortExpression="Field2" />
            </Columns>
        </asp:GridView>

    </form>
</body>
</html>

Default.aspx.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using Zaretto.ODS;  // NuGet ODSReaderWriter 1.0.10 使用

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

        }

        //【匯出(.ods)】
        #region === protected void Button_Export_ODS_by_OdsReaderWriter_DotNetZip_IonicZip_Click(object sender, EventArgs e) ===
        protected void Button_Export_ODS_by_OdsReaderWriter_DotNetZip_IonicZip_Click(object sender, EventArgs e)
        {
            // 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 mainFileName = "匯出檔案名稱";

            // 若檔案名稱有中文字或特殊符號，Response.AddHeader() 輸出檔案名稱要編碼
            string outFileName = HttpUtility.UrlEncode(mainFileName, System.Text.Encoding.UTF8) + "-" +
                DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".ods";

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

                #region === Export ODS ===

                // [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.ContentType = "application/vnd.oasis.opendocument.spreadsheet";    // ODF/.ods
                Response.AddHeader("content-disposition", "attachment;filename=" + outFileName);
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    StreamWriter odsFileStreamWriter = new StreamWriter(MyMemoryStream);
                    StreamReader odsFileStreamReader = new StreamReader(new MemoryStream());
                    string dataText = "";

                    DataSet ds = new DataSet("DS1");
                    //DataTable dt = new DataTable();
                    DataTable dt = ds.Tables.Add("工作表1");

                    string[] columnNameArray = {
"流水號"
,"欄位1"
,"欄位2"
        };
                    foreach (string columnName in columnNameArray)
                    {
                        DataColumn dataColumnName = new DataColumn
                        {
                            DataType = System.Type.GetType("System.String"),
                            ColumnName = columnName
                        };
                        dt.Columns.Add(dataColumnName);
                    }

                    DataRow dataColumnNameRow = dt.NewRow();
                    int ii = 0;
                    foreach (string columnName in columnNameArray)
                    {
                        dataColumnNameRow[ii] = columnName;
                        ii++;
                    }
                    dt.Rows.Add(dataColumnNameRow);

                    string queryString = SqlDataSource1.SelectCommand;
                    using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString))
                    {
                        SqlCommand command = new SqlCommand(queryString, connection);
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@Field2", DropDownList_Field2.SelectedValue);
                        connection.Open();

                        //Get All column 
                        //var columnNames = Enumerable.Range(0, reader.FieldCount)
                        //                        .Select(reader.GetName)
                        //                        .ToList();

                        ////Create headers
                        //sb.Append(string.Join(",", columnNames));

                        ////Append Line
                        //sb.AppendLine();

                        try
                        {
                            using (SqlDataReader reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    //Console.WriteLine(String.Format("{0}", reader[0]));
                                    DataRow datarow = dt.NewRow();
                                    for (int i = 0; i < reader.FieldCount; i++)
                                    {
                                        dataText = reader[i].ToString();
                                        // 換掉影響 CSV 顯示效果的字元
                                        dataText = dataText.Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace(",", "，").Replace("'", "′").Replace(@"""", "”");
                                        //if (i > 0)
                                        //{
                                        //    datarow[i - 1] = dataText;
                                        //}
                                        datarow[i] = dataText;
                                        //if (i == 1)
                                        //{
                                        //    //if (csvContent=="")
                                        //    //    csvContent = dataText;
                                        //    //else
                                        //    //csvContent = csvContent + "\r\n" + dataText;
                                        //    csvContent.Append("\r\n" + dataText);

                                        //}
                                        //else
                                        //{
                                        //    //csvContent = csvContent + "," + dataText;
                                        //    csvContent.Append("," + dataText);
                                        //}
                                    }
                                    dt.Rows.Add(datarow);
                                }
                            }

                            //OdsReaderWriter odsFile = new OdsReaderWriter();  // OdsReaderWriter.cs 使用
                            ODSReaderWriter odsFile = new ODSReaderWriter();  // NuGet OdsReaderWriter 使用，注意大小寫不同
                            //odsFile.WriteOdsFile(ds, @"C:\TEMP\1.ods");
                            odsFile.WriteOdsFile(ds, dirFileName);

                            // Write the DataSet directly to the MemoryStream
                            // 格式有問題，WriteOdsFile 可能有對 ds (DataSet) 再處理，
                            // 所以此處直接以 Xml 寫入 MyMemoryStream 是不行的
                            //ds.WriteXml(MyMemoryStream);

                            Label_MSG1.ForeColor = System.Drawing.Color.Green;
                            string msg = DateTime.Now.ToString() + "匯出成功。";
                            Label_MSG1.Text = msg;
                            Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('" + msg + "');</script>");
                        }
                        catch (Exception)
                        {
                            throw;
                        }
                    }
                    Response.TransmitFile(dirFileName);
                    Response.End();

                    // Transmit the file directly without saving to server
                    //MyMemoryStream.Position = 0;
                    //MyMemoryStream.CopyTo(Response.OutputStream);
                }
                #endregion Export ODS
            }
            catch (Exception)
            {
                throw;
            }
            Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('匯出成功。');</script>");
        }
        #endregion
    }
}

實際測試可以成功。

(完)

相關

[研究]ASP.NET,WebForm,用 NuGet OdsReaderWriter 1.0.10 匯出 .ods
https://shaurong.blogspot.com/2024/03/aspnetwebform-nuget-odsreaderwriter.html

[研究]ASP.NET,WebForm, 把 OdsReadWrite.cs 從 Ionic.Zip 或 DotNetZip 改成用 System.IO.Compression
https://shaurong.blogspot.com/2024/03/aspnetwebform-odsreadwritecs-ioniczip.html

[研究]ASP.NET,WebForm,用 OdsReaderWriter + DotNetZip (Ionic.Zip) 1.16.0 共用匯出 .ods
https://shaurong.blogspot.com/2024/03/aspnetwebform-odsreaderwriter-dotnetzip_79.html

[研究]ASP.NET,WebForm,用 OdsReaderWriter + DotNetZip (Ionic.Zip) 1.16.0 匯出 .ods
https://shaurong.blogspot.com/2024/03/aspnetwebform-odsreaderwriter-dotnetzip_23.html

[研究]ASP.NET,WebForm,用 OdsReaderWriter + DotNetZip (Ionic.Zip) 1.16.0 匯入 .ods
https://shaurong.blogspot.com/2024/03/aspnetwebform-odsreaderwriter-dotnetzip_93.html

How to Read and Write ODF/ODS Files (OpenDocument Spreadsheets) - CodeProject
https://www.codeproject.com/Articles/38425/How-to-Read-and-Write-ODF-ODS-Files-OpenDocument-2

[研究][ASP.NET]讀取、匯出、寫入、匯入 Excel .xlsx .xls ODF .ods
https://shaurong.blogspot.com/2020/04/caspnetwebform-excel-xlsx-xls-odf-ods.html

[研究][ASP.NET]使用 OdsReaderWriter + DotNetZip 1.15.0 (Ionic.Zip) 匯出寫入 .ods
https://shaurong.blogspot.com/2021/02/aspnet-odsreaderwriter-dotnetzip-1150_9.html

[研究][ASP.NET]使用 OdsReaderWriter + DotNetZip 1.15.0 讀取匯入 .ods 到資料庫
https://shaurong.blogspot.com/2021/02/aspnet-odsreaderwriter-dotnetzip-1150.html

[研究][C#]用OdsReaderWrite匯出(寫入)、匯入(讀取) ODF/.ods 檔案 (使用 Ionic.Zip、DotNetZip)
https://shaurong.blogspot.com/2020/09/caspnetwebform-ioniczip-odfods.html

[研究] [C#] 用OdsReadWrite讀取 LibreOffice、OpenOffice 的試算表(Calc) 檔案 (.ods)(使用 .NET 4.5 的 ZipFile)
https://shaurong.blogspot.com/2016/12/c-libreoffice-516-calc-ods-net-45.html

[研究] [C#] 用OdsReaderWrite讀取 LibreOffice 5.1.6 的試算表(Calc) 檔案 (.ods)(使用 DotNetZip)
https://shaurong.blogspot.com/2016/12/c-libreoffice-516-calc-ods.html

NuGet Gallery | OdsReaderWriter 1.0.10
https://www.nuget.org/packages/OdsReaderWriter

sortings/OdsReadWrite at master · Luviz/sortings · GitHub
https://github.com/Luviz/sortings/tree/master/OdsReadWrite

浮雲雅築

2024年3月30日星期六

[研究]ASP.NET,WebForm,用 NuGet OdsReaderWriter 1.0.10 匯出 .ods

沒有留言:

張貼留言

2024年3月30日 星期六

[研究]ASP.NET,WebForm,用 NuGet OdsReaderWriter 1.0.10 匯出 .ods

沒有留言:

張貼留言

2024年3月30日星期六