浮雲雅築: [研究][ASP.NET]使用 OdsReaderWriter + DotNetZip 1.15.0 讀取匯入 .ods 到資料庫

[研究][ASP.NET]使用 OdsReaderWriter + DotNetZip 1.15.0 (Ionic.Zip) 讀取匯入 .ods 到資料庫

2021-02-08

ODF release dates:

ODF 1.0 May 2005
ODF 1.1 February 2007
ODF 1.2 September 2012
ODF 1.3 January 2020

https://wiki.documentfoundation.org/Comparison_of_ODF_software

How to Read and Write ODF/ODS Files (OpenDocument Spreadsheets)
28 Jul 2011
https://www.codeproject.com/Articles/38425/How-to-Read-and-Write-ODF-ODS-Files-OpenDocument-2
所以 odsreadwrite.zip 可能只支援到 ODF, ODS 1.1 版

環境：Visual Studio 2019 + ASP.NET + WebForm + C# + NuGet 安裝 NPOI 2.5.2

暫存目錄要設定程式或 IIS_IUSRS 或 IUSR 可以寫入。

( 如果是 deploy 發佈到 ASP.NET + IIS 網站， IIS_IUSRS 可以寫入即可)

( 如果是 deploy 發佈到 IIS +某種 CGI 網站， IUSR 需可以寫入)

Web.Config

<?xml version="1.0" encoding="utf-8"?>

<!--
  如需如何設定 ASP.NET 應用程式的詳細資訊，請前往
  https://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
	<connectionStrings>
		<add name="TestDBConnectionString" connectionString="Data Source=.;Initial Catalog=TestDB;User ID=sa;Password=P@ssw0rd"
		  providerName="System.Data.SqlClient" />
	</connectionStrings>
	<system.web>
		<compilation debug="true" targetFramework="4.7.2"/>
		<httpRuntime targetFramework="4.7.2"/>
	</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:\WWW\Temp" />
	</appSettings>
</configuration>

packages.config

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="DotNetZip" version="1.15.0" targetFramework="net472" />
  <package id="Microsoft.CodeDom.Providers.DotNetCompilerPlatform" version="2.0.1" targetFramework="net472" />
</packages>

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ImportXlsxByOdsReaderWriterDotNetZipTest.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 />
            <asp:Button ID="Button_Import_Xlsx_by_OdsReaderWriter_DotNetZip_IonicZip" 
                runat="server" Text="匯入(.ods)"
                OnClick="Button_Import_Xlsx_by_OdsReaderWriter_DotNetZip_IonicZip_Click" /><br />
            <br />
            <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>"
                SelectCommand="SELECT * FROM [Table1]"></asp:SqlDataSource>
            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
                AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True" SortExpression="id" />
                    <asp:BoundField DataField="FieldText" HeaderText="FieldText" SortExpression="FieldText" />
                    <asp:BoundField DataField="FieldDateTime" HeaderText="FieldDateTime" SortExpression="FieldDateTime" />
                    <asp:BoundField DataField="FieldInt" HeaderText="FieldInt" SortExpression="FieldInt" />
                    <asp:CheckBoxField DataField="FieldBit" HeaderText="FieldBit" SortExpression="FieldBit" />
                </Columns>
            </asp:GridView>
            <asp:Label ID="Label_ExportImportMSG" runat="server"></asp:Label><br />
            <asp:Label ID="Label_MSG1" runat="server"></asp:Label>
        </div>
    </form>
</body>
</html>

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
using OdsReadWrite;   //  要 using  namespace ，才能存取到 OdsReadWriter 這個 class

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

        }

        protected void Button_Import_Xlsx_by_OdsReaderWriter_DotNetZip_IonicZip_Click(object sender, EventArgs e)
        {
            /*
            OdsReaderWriter.cs 和 template.ods 取自下面網站
            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
           
            template.ods 請自己用 Excel、OpenOffice 或 LibreOffice 的 Calc 建立一個空的即可。
           
            NuGet 要安裝 Ionic.Zip 套件。(Ionic.Zip 已經淘汰，請改用DotNetZip)
            */

            Label_MSG1.Text = "";
            Label_ExportImportMSG.Text = "";

            string queryString = "";
            string errMsg = "";
            string url = "";
            string msg = "";

            string fieldText = "";
            string fieldDateTime = "";
            DateTime fieldDateTime2 = DateTime.Now;
            string fieldInt = "";
            string fieldBit = "";

            int rowCount = 0;
            int columnCount = 0;
            int successCount = 0;
            int lineNo = 0;

            try
            {
                if (!FileUpload1.HasFile)
                {
                    Label_ExportImportMSG.Text = "<font color=red>請選擇.ods檔案</font>";
                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message",
                        "<script language='javascript' defer>alert('請選擇.ods檔案。');</script>");
                    return;
                }
                else
                {
                    string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + @"\";
                    if (!System.IO.Directory.Exists(fd))
                    {
                        System.IO.Directory.CreateDirectory(fd);
                    }
                    string ext = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                    //if (!((ext == ".ods") || (ext == ".csv")))
                    if (!(ext == ".ods"))
                    {
                        Label_ExportImportMSG.Text = "<font color=red>請上傳副檔名.ods檔案</font>";
                        Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('"
                                + "請上傳副檔名.ods檔案！" + "');</script>");
                        return;
                    }

                    string fileName = fd + FileUpload1.FileName;
                    FileUpload1.SaveAs(fileName);

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

                    //queryString = @"DELETE FROM [dbo].[Table1] WHERE id=@id; ";
                    queryString = @"DELETE FROM [dbo].[Table1]; ";
                    using (SqlConnection connection = new SqlConnection(
                        WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString))
                    {
                        SqlCommand command = new SqlCommand(queryString, connection);
                        connection.Open();
                        command.ExecuteNonQuery();
                    }

                    OdsReaderWriter odsFile = new OdsReaderWriter();
                    DataSet odsDataSet = odsFile.ReadOdsFile(openFilename);
                    DataTable table = odsDataSet.Tables[0]; // 只抓第一個工作表，第二個工作表是說明
                    rowCount = table.Rows.Count;
                    columnCount = table.Columns.Count;

                    for (int i = 0; i <= table.Rows.Count - 1; i++)
                    {
                        fieldText = "";
                        fieldDateTime = "";
                        fieldInt = "";
                        fieldBit = "";
                        lineNo = i;

                        if (i == 0)
                        {
                            // 標題列不讀取
                        }
                        else
                        {
                            // 非標題列
                            fieldText = table.Rows[i][0].ToString().Trim();

                            if (fieldText == "")
                            {
                                // 第一欄位必填，null 或 "" 就假設後面都沒有資料
                                errMsg = "#1匯入.ods共" + rowCount + "橫列" + columnCount
                                    + "行(欄位) (含標題列，若看不到那麼多橫列，可能填寫了不可見字元)，第"
                                    + (i - 1) + "筆 (Excel 第" + i.ToString() + "列) 第1欄位必填沒有資料，匯入中斷，若資料已經全部匯入，可不用理會訊息。\\n";
                                //ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + errMsg + "');</script>");
                                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('"
                                    + errMsg + "');</script>");
                                GridView1.DataBind();   // 更新畫面
                                return;
                            }
                            else
                            {
                                // 該列假設有資料
                                fieldDateTime = table.Rows[i][1].ToString().Trim();
                                bool convert = DateTime.TryParse(fieldDateTime, out fieldDateTime2);
                                if (convert)
                                {
                                    fieldDateTime = fieldDateTime2.ToString("yyyy/MM/dd HH:mm:ss.fff");
                                }
                                else
                                {
                                    fieldDateTime = null;
                                }
                                fieldInt = table.Rows[i][2].ToString().Trim();
                                fieldBit = table.Rows[i][3].ToString().Trim();

                                // --------------------------------------------------------------------------------
                                // e.Values 中欄位名稱的大小寫要符合前端 Bind 的欄位名稱
                                // 檢察欄位長度、必填、關聯要求
                                // errMsg = CheckRecord(fieldText,fieldDateTime,fieldInt,fieldBit);
                                // if (errMsg != "")
                                // {
                                // Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", 
                                //    "<script language='javascript' defer>alert('第" + lineNo + 
                                //    "筆匯入失敗，匯入中斷！" + errMsg + "');</script>");
                                //    return;
                                // }
                                // --------------------------------------------------------------------------------

                                queryString = @"
INSERT INTO [dbo].[Table1]
           ([FieldText]
           ,[FieldDateTime]
           ,[FieldInt]
           ,[FieldBit])
     VALUES
           (N'" + fieldText + "'" +
           ",'" + fieldDateTime + "'" +
           ",'" + fieldInt + "'" +
           ",'" + fieldBit + "'); ";

                                using (SqlConnection connection = new SqlConnection(
                                    WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString))
                                {
                                    SqlCommand command = new SqlCommand(queryString, connection);
                                    //command.Parameters.Clear();
                                    //command.Parameters.AddWithValue("@Value", value);
                                    connection.Open();
                                    command.ExecuteNonQuery();
                                    successCount++;
                                }
                            } // if (fieldText == "")
                        } // if (i == 0)
                    } // for

                    //string url = "/List.aspx?Ref1=" + Request.QueryString["Ref1"].ToString() + "&Ref2=" + 
                    //  Request.QueryString["Ref2"].ToString() + "&Ref3=" + Request.QueryString["Ref3"].ToString();
                    url = "#";
                    msg = "#2匯入.ods共" + rowCount + "橫列" + columnCount +
                        "行(欄位) (含標題列，若看不到那麼多橫列，可能填寫了不可見字元)，成功匯入" + successCount + "筆。\\n";
                    ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('"
                        + msg + "');location.href='" + url + "';</script>");
                } // if (!FileUpload1.HasFile)
            }
            catch (Exception ex)
            {
                string exceptionMsg = "不明錯誤。";
                if (ex != null)
                {
                    exceptionMsg = ex.Message.ToString();
                }
                Label_MSG1.ForeColor = System.Drawing.Color.Red;
                Label_MSG1.Text = errMsg + "<br />" + exceptionMsg;
                errMsg = "#3匯入.ods共" + rowCount + "橫列" + columnCount
                    + "行(欄位) (含標題列，若看不到那麼多橫列，可能填寫了不可見字元)，第"
                    + (lineNo - 1) + "筆 (Excel 第" + lineNo.ToString() + "列)匯入失敗。\\n";
                //ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + errMsg + "');</script>");
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('"
                    + errMsg + "');</script>");

                // 會因為 Exception 內容關係，JavaScript alert 對話盒視窗可能不會跳出顯示
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('"
                    + exceptionMsg + "');</script>");
            }
            GridView1.DataBind();   // 更新畫面
        }
    }
}

OdsReaderWriter.cs

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

using System;
using System.Data;
using System.Globalization;
using System.IO;
using System.Reflection;
using System.Xml;

// Ionic.Zip 已經淘汰，請改用 DotNetZip
// 不管 NuGet 安裝 DotNetZip 或 Ionic.Zip，Using 都是 Ionic.Zip
using Ionic.Zip;    

namespace OdsReadWrite
{
    internal sealed class OdsReaderWriter
    {
        // Namespaces. We need this to initialize XmlNamespaceManager so that we can search XmlDocument.
        private static string[,] namespaces = new string[,] 
        {
            {"table", "urn:oasis:names:tc:opendocument:xmlns:table:1.0"},
            {"office", "urn:oasis:names:tc:opendocument:xmlns:office:1.0"},
            {"style", "urn:oasis:names:tc:opendocument:xmlns:style:1.0"},
            {"text", "urn:oasis:names:tc:opendocument:xmlns:text:1.0"},            
            {"draw", "urn:oasis:names:tc:opendocument:xmlns:drawing:1.0"},
            {"fo", "urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0"},
            {"dc", "http://purl.org/dc/elements/1.1/"},
            {"meta", "urn:oasis:names:tc:opendocument:xmlns:meta:1.0"},
            {"number", "urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0"},
            {"presentation", "urn:oasis:names:tc:opendocument:xmlns:presentation:1.0"},
            {"svg", "urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0"},
            {"chart", "urn:oasis:names:tc:opendocument:xmlns:chart:1.0"},
            {"dr3d", "urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0"},
            {"math", "http://www.w3.org/1998/Math/MathML"},
            {"form", "urn:oasis:names:tc:opendocument:xmlns:form:1.0"},
            {"script", "urn:oasis:names:tc:opendocument:xmlns:script:1.0"},
            {"ooo", "http://openoffice.org/2004/office"},
            {"ooow", "http://openoffice.org/2004/writer"},
            {"oooc", "http://openoffice.org/2004/calc"},
            {"dom", "http://www.w3.org/2001/xml-events"},
            {"xforms", "http://www.w3.org/2002/xforms"},
            {"xsd", "http://www.w3.org/2001/XMLSchema"},
            {"xsi", "http://www.w3.org/2001/XMLSchema-instance"},
            {"rpt", "http://openoffice.org/2005/report"},
            {"of", "urn:oasis:names:tc:opendocument:xmlns:of:1.2"},
            {"rdfa", "http://docs.oasis-open.org/opendocument/meta/rdfa#"},
            {"config", "urn:oasis:names:tc:opendocument:xmlns:config:1.0"}
        };

        // Read zip stream (.ods file is zip file).
        private ZipFile GetZipFile(Stream stream)
        {
            return ZipFile.Read(stream);
        }

        // Read zip file (.ods file is zip file).
        private ZipFile GetZipFile(string inputFilePath)
        {
            return ZipFile.Read(inputFilePath);
        }

        private XmlDocument GetContentXmlFile(ZipFile zipFile)
        {
            // Get file(in zip archive) that contains data ("content.xml").
            ZipEntry contentZipEntry = zipFile["content.xml"];  

            // Extract that file to MemoryStream.
            Stream contentStream = new MemoryStream();
            contentZipEntry.Extract(contentStream);
            contentStream.Seek(0, SeekOrigin.Begin);

            // Create XmlDocument from MemoryStream (MemoryStream contains content.xml).
            XmlDocument contentXml = new XmlDocument();
            contentXml.Load(contentStream);

            return contentXml;
        }

        private XmlNamespaceManager InitializeXmlNamespaceManager(XmlDocument xmlDocument)
        {
            XmlNamespaceManager nmsManager = new XmlNamespaceManager(xmlDocument.NameTable);

            for (int i = 0; i < namespaces.GetLength(0); i++)
                nmsManager.AddNamespace(namespaces[i, 0], namespaces[i, 1]);

            return nmsManager;
        }

        /// <summary>
        /// Read .ods file and store it in DataSet.
        /// </summary>
        /// <param name="inputFilePath">Path to the .ods file.</param>
        /// <returns>DataSet that represents .ods file.</returns>
        public DataSet ReadOdsFile(string inputFilePath)
        {
            ZipFile odsZipFile = this.GetZipFile(inputFilePath);

            // Get content.xml file
            XmlDocument contentXml = this.GetContentXmlFile(odsZipFile);

            // Initialize XmlNamespaceManager
            XmlNamespaceManager nmsManager = this.InitializeXmlNamespaceManager(contentXml);

            DataSet odsFile = new DataSet(Path.GetFileName(inputFilePath));

            foreach (XmlNode tableNode in this.GetTableNodes(contentXml, nmsManager))
                odsFile.Tables.Add(this.GetSheet(tableNode, nmsManager));

            return odsFile;
        }

        // In ODF sheet is stored in table:table node
        private XmlNodeList GetTableNodes(XmlDocument contentXmlDocument, XmlNamespaceManager nmsManager)
        {
            return contentXmlDocument.SelectNodes("/office:document-content/office:body/office:spreadsheet/table:table", nmsManager);
        }

        private DataTable GetSheet(XmlNode tableNode, XmlNamespaceManager nmsManager)
        {
            DataTable sheet = new DataTable(tableNode.Attributes["table:name"].Value);

            XmlNodeList rowNodes = tableNode.SelectNodes("table:table-row", nmsManager);

            int rowIndex = 0;
            foreach (XmlNode rowNode in rowNodes)
                this.GetRow(rowNode, sheet, nmsManager, ref rowIndex);

            return sheet;
        }

        private void GetRow(XmlNode rowNode, DataTable sheet, XmlNamespaceManager nmsManager, ref int rowIndex)
        {
            XmlAttribute rowsRepeated = rowNode.Attributes["table:number-rows-repeated"];
            if (rowsRepeated == null || Convert.ToInt32(rowsRepeated.Value, CultureInfo.InvariantCulture) == 1)
            {
                while (sheet.Rows.Count < rowIndex)
                    sheet.Rows.Add(sheet.NewRow());

                DataRow row = sheet.NewRow();

                XmlNodeList cellNodes = rowNode.SelectNodes("table:table-cell", nmsManager);

                int cellIndex = 0;
                foreach (XmlNode cellNode in cellNodes)
                    this.GetCell(cellNode, row, nmsManager, ref cellIndex);

                sheet.Rows.Add(row);

                rowIndex++;
            }
            else
            {
                rowIndex += Convert.ToInt32(rowsRepeated.Value, CultureInfo.InvariantCulture);
            }

            // sheet must have at least one cell
            if (sheet.Rows.Count == 0)
            {
                sheet.Rows.Add(sheet.NewRow());
                sheet.Columns.Add();
            }
        }

        private void GetCell(XmlNode cellNode, DataRow row, XmlNamespaceManager nmsManager, ref int cellIndex)
        {
            XmlAttribute cellRepeated = cellNode.Attributes["table:number-columns-repeated"];

            if (cellRepeated == null)
            {
                DataTable sheet = row.Table;

                while (sheet.Columns.Count <= cellIndex)
                    sheet.Columns.Add();

                row[cellIndex] = this.ReadCellValue(cellNode);

                cellIndex++;
            }
            else
            {
                cellIndex += Convert.ToInt32(cellRepeated.Value, CultureInfo.InvariantCulture);
            }
        }

        private string ReadCellValue(XmlNode cell)
        {
            XmlAttribute cellVal = cell.Attributes["office:value"];

            if (cellVal == null)
                return String.IsNullOrEmpty(cell.InnerText) ? null : cell.InnerText;
            else
                return cellVal.Value;
        }

        /// <summary>
        /// Writes DataSet as .ods file.
        /// </summary>
        /// <param name="odsFile">DataSet that represent .ods file.</param>
        /// <param name="outputFilePath">The name of the file to save to.</param>
        public void WriteOdsFile(DataSet odsFile, string outputFilePath)
        {
            ZipFile templateFile = this.GetZipFile(Assembly.GetExecutingAssembly().GetManifestResourceStream("OdsReadWrite.template.ods"));
            //ZipFile templateFile = this.GetZipFile(File.OpenRead(@"C:\CODE\ODSExport\ODSExport\bin\template.ods"));
            //ZipFile templateFile = this.GetZipFile(File.OpenRead(Server.MapPath("/") + "template.ods");
            //ZipFile templateFile = this.GetZipFile(File.OpenRead(HttpContext.Current.Server.MapPath("/template.ods")));

            XmlDocument contentXml = this.GetContentXmlFile(templateFile);

            XmlNamespaceManager nmsManager = this.InitializeXmlNamespaceManager(contentXml);

            XmlNode sheetsRootNode = this.GetSheetsRootNodeAndRemoveChildrens(contentXml, nmsManager);

            foreach (DataTable sheet in odsFile.Tables)
                this.SaveSheet(sheet, sheetsRootNode);

            this.SaveContentXml(templateFile, contentXml);

            templateFile.Save(outputFilePath);
        }

        private XmlNode GetSheetsRootNodeAndRemoveChildrens(XmlDocument contentXml, XmlNamespaceManager nmsManager)
        {
            XmlNodeList tableNodes = this.GetTableNodes(contentXml, nmsManager);

            XmlNode sheetsRootNode = tableNodes.Item(0).ParentNode;
            // remove sheets from template file
            foreach (XmlNode tableNode in tableNodes)
                sheetsRootNode.RemoveChild(tableNode);

            return sheetsRootNode;
        }

        private void SaveSheet(DataTable sheet, XmlNode sheetsRootNode)
        {
            XmlDocument ownerDocument = sheetsRootNode.OwnerDocument;

            XmlNode sheetNode = ownerDocument.CreateElement("table:table", this.GetNamespaceUri("table"));

            XmlAttribute sheetName = ownerDocument.CreateAttribute("table:name", this.GetNamespaceUri("table"));
            sheetName.Value = sheet.TableName;
            sheetNode.Attributes.Append(sheetName);

            this.SaveColumnDefinition(sheet, sheetNode, ownerDocument);

            this.SaveRows(sheet, sheetNode, ownerDocument);

            sheetsRootNode.AppendChild(sheetNode);
        }

        private void SaveColumnDefinition(DataTable sheet, XmlNode sheetNode, XmlDocument ownerDocument)
        {
            XmlNode columnDefinition = ownerDocument.CreateElement("table:table-column", this.GetNamespaceUri("table"));

            XmlAttribute columnsCount = ownerDocument.CreateAttribute("table:number-columns-repeated", this.GetNamespaceUri("table"));
            columnsCount.Value = sheet.Columns.Count.ToString(CultureInfo.InvariantCulture);
            columnDefinition.Attributes.Append(columnsCount);

            sheetNode.AppendChild(columnDefinition);
        }

        private void SaveRows(DataTable sheet, XmlNode sheetNode, XmlDocument ownerDocument)
        {
            DataRowCollection rows = sheet.Rows;
            for (int i = 0; i < rows.Count; i++)
            {
                XmlNode rowNode = ownerDocument.CreateElement("table:table-row", this.GetNamespaceUri("table"));

                this.SaveCell(rows[i], rowNode, ownerDocument);

                sheetNode.AppendChild(rowNode);
            }
        }

        private void SaveCell(DataRow row, XmlNode rowNode, XmlDocument ownerDocument)
        {
            object[] cells = row.ItemArray;

            for (int i = 0; i < cells.Length; i++)
            {
                XmlElement cellNode = ownerDocument.CreateElement("table:table-cell", this.GetNamespaceUri("table"));

                if (row[i] != DBNull.Value)
                {
                    // We save values as text (string)
                    XmlAttribute valueType = ownerDocument.CreateAttribute("office:value-type", this.GetNamespaceUri("office"));
                    valueType.Value = "string";
                    cellNode.Attributes.Append(valueType);

                    XmlElement cellValue = ownerDocument.CreateElement("text:p", this.GetNamespaceUri("text"));
                    cellValue.InnerText = row[i].ToString();
                    cellNode.AppendChild(cellValue);
                }

                rowNode.AppendChild(cellNode);
            }
        }

        private void SaveContentXml(ZipFile templateFile, XmlDocument contentXml)
        {
            templateFile.RemoveEntry("content.xml");

            MemoryStream memStream = new MemoryStream();
            contentXml.Save(memStream);
            memStream.Seek(0, SeekOrigin.Begin);

            templateFile.AddEntry("content.xml", memStream);
        }

        private string GetNamespaceUri(string prefix)
        {
            for (int i = 0; i < namespaces.GetLength(0); i++)
            {
                if (namespaces[i, 0] == prefix)
                    return namespaces[i, 1];
            }

            throw new InvalidOperationException("Can't find that namespace URI");
        }
    }
}

實際使用經驗，.ods元件和.ods檔案相容性，比不上.xlsx元件和.xlsx檔案的相容性。

.ods元件有時候會讀取不到.ods檔案欄位中資料，甚至很多橫列內容相同時，可能只能讀取到一筆。(敝人自己做不出，但某些使用者不知如何做出的，拿來匯入確實有問題)

不管是 OdsReaderWriter + DotNetZip 或商業付費的 Aspose.Cells 都如此，但是 Aspose.Cells 正常機率高於 OdsReaderWriter + DotNetZip 方式。

.ods元件讀.ods檔案正常機率有多高，粗估高達 99% 以上，但約萬名使用者，1% 異常就很有得應付處理了。

(完)

浮雲雅築

2021年2月9日星期二

[研究][ASP.NET]使用 OdsReaderWriter + DotNetZip 1.15.0 讀取匯入 .ods 到資料庫

沒有留言:

張貼留言

2021年2月9日 星期二

[研究][ASP.NET]使用 OdsReaderWriter + DotNetZip 1.15.0 讀取匯入 .ods 到資料庫

沒有留言:

張貼留言

2021年2月9日星期二