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>


Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1.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:FileUpload ID="FileUpload1" runat="server" /><br />
        <%--<asp:Button ID="Button1" runat="server" Text="匯出(.ods)" OnClick="Button_Export_ODS_Click" />--%>
        <asp:Button ID="Button2" runat="server" Text="匯入(.ods)" OnClick="Button_Import_ods_by_OdsReaderWriter_DotNetZip_IonicZip_Click" /><br />
        <asp:Label ID="Label_MSG1" runat="server"></asp:Label><br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:MyDBConnectionString %>"
            SelectCommand="SELECT * FROM [MyTable]"></asp:SqlDataSource>
        <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="Field1" HeaderText="Field1" SortExpression="Field1" />
                <asp:BoundField DataField="Field2" HeaderText="Field2" SortExpression="Field2" />
            </Columns>
        </asp:GridView>
    </form>
</body>
</html>


Default.aspx.cs

using OdsReadWrite; // 需要 OdsReaderWriter.cs 檔案
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web.Configuration;
using System.Web.UI;

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

        }

        //【匯入(.ods)】
        #region === protected void Button_Import_ods_by_OdsReaderWriter_DotNetZip_IonicZip_Click(object sender, EventArgs e) ===
        protected void Button_Import_ods_by_OdsReaderWriter_DotNetZip_IonicZip_Click(object sender, EventArgs e)
        {
            Label_MSG1.Text = "";
            int lineNo = 1;
            string queryString = "";
            int readSuccessLineCount = 0;
            
            if (FileUpload1.HasFile)
            {
                string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + Path.DirectorySeparatorChar.ToString();
                //if (!System.IO.Directory.Exists(fd))
                //{
                //    System.IO.Directory.CreateDirectory(fd);
                //}
                string ext = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                if (!(ext == ".ods"))
                {
                    Label_MSG1.Text = "請上傳副檔名.ods檔案(Excel/OpenOffice/LibreOffice)";
                    ScriptManager.RegisterStartupScript(this, GetType(), "Popup1", "erroralert('" + "請上傳副檔名.ods檔案!" + "');", true);
                    return;
                }
                string fileName = fd + FileUpload1.FileName;
                FileUpload1.SaveAs(fileName);

                string mainFileName = Path.GetFileNameWithoutExtension(FileUpload1.FileName);
                string openFilename = fd + mainFileName + "-匯入結果" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".ods";
                FileUpload1.SaveAs(openFilename);

                OdsReaderWriter odsReaderWriter1 = new OdsReaderWriter();
                DataSet odsDataSet = odsReaderWriter1.ReadOdsFile(openFilename);

                string field1 = "";
                string field2 = "";

                try
                {
                    queryString = @"DELETE FROM [dbo].[MyTable]; ";

                    using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString))
                    {
                        SqlCommand command = new SqlCommand(queryString, connection);
                        connection.Open();
                        command.ExecuteNonQuery();
                        lineNo++;
                    }

                    DataTable table = odsDataSet.Tables[0]; // 只抓第一個工作表,第二個工作表是說明

                    // 列數判斷法不正確,可能某些列看起來空白,實際有東西
                    for (int i = 1; i <= table.Rows.Count - 1; i++)
                    {
                        // 第0列(i=0)是標題,忽略不讀取,故 i=1 開始
                        // 第1欄位欄位的索引值是 0
                        field1 = table.Rows[i][0].ToString().Trim();
                        if (field1 != "")
                        {
                            field2 = table.Rows[i][1].ToString().Trim();
                            queryString = @"
INSERT INTO [dbo].[MyTable]
           ([Field1]
           ,[Field2])
     VALUES
           (@Field1
           ,@Field2)
";
                            using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString))
                            {
                                SqlCommand command = new SqlCommand(queryString, connection);
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@Field1", field1);
                                command.Parameters.AddWithValue("@Field2", field2);
                                connection.Open();
                                command.ExecuteNonQuery();
                                lineNo++;
                                readSuccessLineCount++;
                            }
                        }// if
                        else
                        {
                            //匯入完成,轉址離開
                            string id1 = "1";
                            string id2 = "2";
                            string url = String.Format("/List.aspx?ID1={0}&ID2={1}", id1, id2);
                            string msg = @"匯入.ods判定資料範圍" + (table.Rows.Count - 1) + "列" + (table.Columns.Count - 1) + "欄 (不表示其中每列or每欄位都有,可見or不可見)," +
                                "成功匯入" + readSuccessLineCount + "筆資料。第" + lineNo + "筆 (Excel 第" + (lineNo + 1) + "列) 第1欄必填「分類」為空的,中斷匯入。\\n" +
                                "若無法成功匯入想要的資料,請嘗試改用.xlsx匯入。\\n" +
                                "按【確定】轉往列表畫面。";
                            Label_MSG1.Text = "匯入成功。";
                            ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + msg + "');location.href='" + url + "';</script>");
                            return;
                        }
                    } // for
                }
                catch (Exception)
                {
                    throw;
                }
            } //if (FileUpload1.HasFile)
            else
            {
                Label_MSG1.Text = "請選擇.ods檔案";
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('請選擇.ods檔案。');</script>");
                return;
            }
            Label_MSG1.Text = "匯入成功。";
            GridView1.DataBind();
        }
        #endregion === protected void Button_Import_ods_by_OdsReaderWriter_DotNetZip_IonicZip_Click(object sender, EventArgs e) ===
    }
}


實際測試可以成功匯入。

(下圖)匯入前


(下圖)選擇檔案,匯入後


(完)

相關

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

沒有留言:

張貼留言