2024年3月29日 星期五

[研究]ASP.NET,WebForm,用 OdsReaderWriter + DotNetZip (Ionic.Zip) 1.16.0 共用匯出 .ods

[研究]ASP.NET,WebForm,用 OdsReaderWriter + DotNetZip (Ionic.Zip) 1.16.0 共用匯出 .ods

2024-03-29

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

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

注意

  • OdsReaderWriter.cs 和 template.ods 從下面文章取得

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

  • OdsReaderWriter.cs 中

//ZipFile templateFile = this.GetZipFile(Assembly.GetExecutingAssembly().GetManifestResourceStream("OdsReadWrite.template.ods"));

改成

ZipFile templateFile = this.GetZipFile(File.OpenRead(HttpContext.Current.Server.MapPath("/template.ods")));


  • NuGet 要安裝 DotNetZip,要 using

using Ionic.Zip; // Ionic.Zip 被 DotNetZip 取代,NuGet 安裝 DotNetZip,但是 using 還是用舊名稱 Ionic.Zip

  • Web.Config 中暫時存放路徑,要手動建立好。


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

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>


OdsCommon.cs

using Ionic.Zip; // Ionic.Zip 被 DotNetZip 取代,NuGet 安裝 DotNetZip,但是 using 還是用舊名稱 Ionic.Zip
using OdsReadWrite; // 需要 修改過的 OfdReadWrite.cs 檔案
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;

namespace WebApplication2
{
    public class OdsCommon
    {
        //【匯出(.ods)】
        #region === public void ExportODS(string odsFilename, string sqlDataSourceSelectCommand) ===
        public static void ExportODS(string odsFilename,
            string[] columnNameArray,
            string sqlDataSourceSelectCommand,
            SqlParameter[] parameters)
        {
            // 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 = odsFilename;

            // 若檔案名稱有中文字或特殊符號,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
            {
                System.Web.HttpContext.Current.Response.Clear();
                System.Web.HttpContext.Current.Response.Buffer = true;
                System.Web.HttpContext.Current.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";
                System.Web.HttpContext.Current.Response.ContentType = "application/vnd.oasis.opendocument.spreadsheet";    // ODF/.ods
                System.Web.HttpContext.Current.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 = SqlDataSource.SelectCommand;
                    string queryString = sqlDataSourceSelectCommand;
                    using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString))
                    {
                        SqlCommand command = new SqlCommand(queryString, connection);
                        //command.Parameters.Clear();
                        //command.Parameters.AddWithValue("@cname", cname);
                        //command.Parameters.AddWithValue("@myemail", myemail);

                        if (parameters != null)
                        {
                            foreach (SqlParameter parameter in parameters)
                            {
                                command.Parameters.Add(parameter);
                            }
                        }

                        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();
                            //odsFile.WriteOdsFile(ds, @"C:\TEMP\1.ods");
                            odsFile.WriteOdsFile(ds, dirFileName);

                            //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;
                        }
                    }
                    System.Web.HttpContext.Current.Response.TransmitFile(dirFileName);
                    System.Web.HttpContext.Current.Response.End();
                }
                #endregion Export ODS
            }
            catch (Exception)
            {
                throw;
            }
            //Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('匯出成功。');</script>");
        }
        #endregion

        //建立參數
        #region === public static SqlParameter[] ConvertParameters(ParameterCollection parameters, Page page) ===
        public static SqlParameter[] ConvertParameters(ParameterCollection parameters, Page page)
        {
            SqlParameter[] sqlParameters = new SqlParameter[parameters.Count];
            for (int i = 0; i < parameters.Count; i++)
            {
                var parameter = parameters[i];
                if (parameter is ControlParameter)
                {
                    ControlParameter controlParameter = (ControlParameter)parameter;
                    var control = page.FindControl(controlParameter.ControlID);
                    // 確保找到控制元件
                    if (control != null)
                    {
                        string value = (control as ITextControl)?.Text ?? string.Empty;
                        sqlParameters[i] = new SqlParameter(parameter.Name, value);
                    }
                    else
                    {
                        // 找不到控制元件時,應該提供預設值,而不是引發 NullReferenceException
                        sqlParameters[i] = new SqlParameter(parameter.Name, DBNull.Value);
                    }
                }
                else
                {
                    // 如果參數不是控制參數,則使用 DBNull.Value
                    sqlParameters[i] = new SqlParameter(parameter.Name, DBNull.Value);
                }
            }
            return sqlParameters;
        }
        #endregion === public static SqlParameter[] ConvertParameters(ParameterCollection parameters, Page page) ===
    }
}

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" runat="server" Text="匯出(.ods)"
            OnClick="Button_Export_ODS_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]=@Filed2 OR @Filed2=N'全部');">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList_Field2" Name="Filed2" 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.SqlClient;

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

        }

        protected void Button_Export_ODS_Click(object sender, EventArgs e)
        {
            string[] columnNameArray = { "流水號", "欄位1", "欄位2" };
            SqlParameter[] sqlParameters = OdsCommon.ConvertParameters(SqlDataSource1.SelectParameters, this.Page);
            OdsCommon.ExportODS("匯出檔案名稱", columnNameArray, SqlDataSource1.SelectCommand, sqlParameters);
        }

    }
}


實際測試可以成功匯入。






(完)

相關

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

沒有留言:

張貼留言