2024年3月29日 星期五

[研究]ASP.NET,WebForm, 把 OdsReadWrite.cs 從 Ionic.Zip 或 DotNetZip 改成用 System.IO.Compression

[研究]ASP.NET,WebForm, 把 OdsReadWrite.cs 從 Ionic.Zip 或 DotNetZip 改成用 System.IO.Compression

2024-03-29

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

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

[研究]ASP.NET, WebForm, 比較 DotNetZip 和 System.IO.Compression 差異 ?

https://shaurong.blogspot.com/2024/03/aspnet-webform-dotnetzip.html

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

https://github.com/haf/DotNetZip.Semverd

This repository has been archived by the owner on May 15, 2024. It is now read-only.

Please use System.IO.Compression! A fork of the DotNetZip project without signing with a solution that compiles cleanly. This project aims to follow semver to avoid versioning conflicts. DotNetZip is a FAST, FREE class library and toolset for manipulating zip files. Use VB, C# or any .NET language to easily create, extract, or update zip files.

DotNetZip淘汰了,請使用System.IO.壓縮! 

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

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

下載得到 odsreadwrite.zip,包含 OdsReaderWriter.cs,其中 namespace OdsReadWrite

OdsReadWrite.cs

using System;
using System.Data;
using System.Globalization;
using System.IO;
using System.IO.Compression;    // Ionic.Zip 或 DotNetZip 改成用 System.IO.Compression
using System.Reflection;
using System.Web;
using System.Xml;
//using Ionic.Zip; // Ionic.Zip 被 DotNetZip 取代,NuGet 安裝 DotNetZip,但是 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 ZipArchive GetZipArchive(Stream stream)
        {
            return new ZipArchive(stream, ZipArchiveMode.Read);
        }

        private ZipArchive GetZipArchive(string inputFilePath)
        {
            return ZipFile.OpenRead(inputFilePath);
        }

        private XmlDocument GetContentXmlFile(ZipArchive zipArchive)
        {
            ZipArchiveEntry contentZipEntry = zipArchive.GetEntry("content.xml");

            using (Stream contentStream = contentZipEntry.Open())
            {
                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;
        }

        public DataSet ReadOdsFile(string inputFilePath)
        {
            using (ZipArchive odsZipArchive = this.GetZipArchive(inputFilePath))
            {
                XmlDocument contentXml = this.GetContentXmlFile(odsZipArchive);
                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;
            }
        }

        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)
        {
            using (ZipArchive templateArchive = this.GetZipArchive(File.OpenRead(HttpContext.Current.Server.MapPath("/template.ods"))))
            {
                XmlDocument contentXml = this.GetContentXmlFile(templateArchive);
                XmlNamespaceManager nmsManager = this.InitializeXmlNamespaceManager(contentXml);

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

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

                this.SaveContentXml(templateArchive, contentXml);

                templateArchive.Dispose();
            }
        }

        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(ZipArchive templateArchive, XmlDocument contentXml)
        {
            templateArchive.GetEntry("content.xml").Delete();

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

                var entry = templateArchive.CreateEntry("content.xml");
                using (var entryStream = entry.Open())
                {
                    memStream.CopyTo(entryStream);
                }
            }
        }

        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");
        }
    }
}



Default.aspx.cs


(下圖)匯入後

(完)

相關

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

沒有留言:

張貼留言