浮雲雅築: [研究]ASP.NET,WebForm,免ODS套件匯入讀取.ods

2024年10月29日星期二

[研究]ASP.NET,WebForm,免ODS套件匯入讀取.ods

[研究]ASP.NET,WebForm,免ODS套件匯入讀取.ods

2024-10-28

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

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

【免ODS套件讀取.ods】

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:GridView ID="GridView1" runat="server"></asp:GridView>
    </form>
</body>
</html>

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Compression;
using System.Text;
using System.Xml.Linq;

namespace WebApplication1
{
    public class OdsReader
    {
        public static List<List<string>> ReadOdsFile(string filePath)
        {
            List<List<string>> data = new List<List<string>>();

            using (ZipArchive archive = ZipFile.OpenRead(filePath))
            {
                // 找到 content.xml 檔案
                ZipArchiveEntry contentEntry = archive.GetEntry("content.xml");
                if (contentEntry != null)
                {
                    using (StreamReader reader = new StreamReader(contentEntry.Open(), Encoding.UTF8))
                    {
                        string xmlContent = reader.ReadToEnd();
                        XDocument doc = XDocument.Parse(xmlContent);

                        // 解析 XML 檔案中的資料
                        foreach (var row in doc.Descendants("{urn:oasis:names:tc:opendocument:xmlns:table:}table-row"))
                        {
                            List<string> rowData = new List<string>();
                            foreach (var cell in row.Descendants("{urn:oasis:names:tc:opendocument:xmlns:table:}table-cell"))
                            {
                                string cellValue = cell.Element("{urn:oasis:names:tc:opendocument:xmlns:text:}p")?.Value ?? string.Empty;
                                rowData.Add(cellValue);
                            }
                            data.Add(rowData);
                        }
                    }
                }
            }
            return data;
        }
    }
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                //string filePath = Server.MapPath("~/path/to/your/file.ods");
                string filePath = @"C:\Users\Administrator\Downloads\Sample.ods";
                List<List<string>> odsData = ReadOdsFile(filePath);

                // 將資料綁定到 GridView
                BindGridView(odsData);
            }
        }
        private List<List<string>> ReadOdsFile(string filePath)
        {
            List<List<string>> data = new List<List<string>>();

            using (ZipArchive archive = ZipFile.OpenRead(filePath))
            {
                ZipArchiveEntry contentEntry = archive.GetEntry("content.xml");
                if (contentEntry != null)
                {
                    using (StreamReader reader = new StreamReader(contentEntry.Open(), Encoding.UTF8))
                    {
                        string xmlContent = reader.ReadToEnd();
                        XDocument doc = XDocument.Parse(xmlContent);

                        // 設定 XML 命名空間
                        XNamespace nsTable = "urn:oasis:names:tc:opendocument:xmlns:table:1.0";
                        XNamespace nsText = "urn:oasis:names:tc:opendocument:xmlns:text:1.0";

                        // 解析 table-row 和 table-cell 元素
                        foreach (var row in doc.Descendants(nsTable + "table-row"))
                        {
                            List<string> rowData = new List<string>();
                            foreach (var cell in row.Descendants(nsTable + "table-cell"))
                            {
                                string cellValue = cell.Element(nsText + "p")?.Value ?? string.Empty;
                                rowData.Add(cellValue);
                            }
                            data.Add(rowData);
                        }
                    }
                }
            }

            return data;
        }

        private void BindGridView(List<List<string>> odsData)
        {
            // 將資料轉換為 DataTable
            var dataTable = new System.Data.DataTable();

            // 動態添加列
            for (int i = 0; i < odsData[0].Count; i++)
            {
                dataTable.Columns.Add($"Column{i + 1}");
            }

            // 添加資料行
            foreach (var row in odsData)
            {
                dataTable.Rows.Add(row.ToArray());
            }

            // 綁定資料到 GridView
            GridView1.DataSource = dataTable;
            GridView1.DataBind();
        }
        //---
    }
}

結果

下圖，若 Sample.ods 是 LibreOffice 7.6 建立的

下圖，若 Sample.ods 是 Excel 2021 建立的

其實一開始 ChatGPT 寫的程式完全不能用，後來把 XDocument doc = XDocument.Parse(xmlContent); 讀取到的 doc 丟給 ChatGPT，才改出可以用的程式。不確定此程式通用性多高，doc讀取到內容如下

<?xml version="1.0"?>

+
<office:document-content office:version="1.3"
	xmlns:presentation="urn:oasis:names:tc:opendocument:
	xmlns:presentation:1.0"
	xmlns:css3t="http://www.w3.org/TR/css3-text/"
	xmlns:grddl="http://www.w3.org/2003/g/data-view#"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:xsd="http://www.w3.org/2001/XMLSchema"
	xmlns:dom="http://www.w3.org/2001/xml-events"
	xmlns:formx="urn:openoffice:names:experimental:ooxml-odf-interop:
	xmlns:form:1.0"
	xmlns:script="urn:oasis:names:tc:opendocument:
	xmlns:script:1.0"
	xmlns:form="urn:oasis:names:tc:opendocument:
	xmlns:form:1.0"
	xmlns:math="http://www.w3.org/1998/Math/MathML"
	xmlns:field="urn:openoffice:names:experimental:ooo-ms-interop:
	xmlns:field:1.0"
	xmlns:loext="urn:org:documentfoundation:names:experimental:office:
	xmlns:loext:1.0"
	xmlns:xhtml="http://www.w3.org/1999/xhtml"
	xmlns:drawooo="http://openoffice.org/2010/draw"
	xmlns:calcext="urn:org:documentfoundation:names:experimental:calc:
	xmlns:calcext:1.0"
	xmlns:tableooo="http://openoffice.org/2009/table"
	xmlns:xforms="http://www.w3.org/2002/xforms"
	xmlns:of="urn:oasis:names:tc:opendocument:
	xmlns:of:1.2"
	xmlns:oooc="http://openoffice.org/2004/calc"
	xmlns:ooow="http://openoffice.org/2004/writer"
	xmlns:number="urn:oasis:names:tc:opendocument:
	xmlns:datastyle:1.0"
	xmlns:table="urn:oasis:names:tc:opendocument:
	xmlns:table:1.0"
	xmlns:chart="urn:oasis:names:tc:opendocument:
	xmlns:chart:1.0"
	xmlns:svg="urn:oasis:names:tc:opendocument:
	xmlns:svg-compatible:1.0"
	xmlns:dr3d="urn:oasis:names:tc:opendocument:
	xmlns:dr3d:1.0"
	xmlns:draw="urn:oasis:names:tc:opendocument:
	xmlns:drawing:1.0"
	xmlns:rpt="http://openoffice.org/2005/report"
	xmlns:text="urn:oasis:names:tc:opendocument:
	xmlns:text:1.0"
	xmlns:style="urn:oasis:names:tc:opendocument:
	xmlns:style:1.0"
	xmlns:meta="urn:oasis:names:tc:opendocument:
	xmlns:meta:1.0"
	xmlns:dc="http://purl.org/dc/elements/1.1/"
	xmlns:xlink="http://www.w3.org/1999/xlink"
	xmlns:fo="urn:oasis:names:tc:opendocument:
	xmlns:xsl-fo-compatible:1.0"
	xmlns:ooo="http://openoffice.org/2004/office"
	xmlns:office="urn:oasis:names:tc:opendocument:
	xmlns:office:1.0">

(完)

相關

浮雲雅築

2024年10月29日星期二

[研究]ASP.NET,WebForm,免ODS套件匯入讀取.ods

沒有留言:

張貼留言

2024年10月29日 星期二

[研究]ASP.NET,WebForm,免ODS套件匯入讀取.ods

沒有留言:

張貼留言

2024年10月29日星期二