浮雲雅築: [研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (二)欄位數量不固定

[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (二)欄位數量不固定

2025-06-02

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

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

參考

Reading OpenDocument spreadsheets using C#
August 24, 2012
https://gunnarpeipman.com/reading-opendocument-spreadsheets-using-c/

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

packages.conf

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

</packages>

OdsImporter.cs (使用 SharpZipLib)

using ICSharpCode.SharpZipLib.Zip;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Xml.Linq;

namespace WebApplication1
{
    public class ImportResult
    {
        public List<DataDto> ImportedData { get; set; } = new List<DataDto>();
        public List<string> Errors { get; set; } = new List<string>();
    }

    public class DataDto
    {
        public Dictionary<int, string> Fields { get; set; } = new Dictionary<int, string>();
    }

    public class OdsImporter
    {
        public OdsImporter()
        {
        }

        public string[] SupportedFileExtensions
        {
            get { return new[] { "ods" }; }
        }

        public ImportResult Import(Stream fileStream, long companyId, short year)
        {
            string contentXml = GetContentXml(fileStream);

            var result = new ImportResult();
            var doc = XDocument.Parse(contentXml);

            var rows = doc.Descendants("{urn:oasis:names:tc:opendocument:xmlns:table:1.0}table-row").Skip(1);

            foreach (var row in rows)
            {
                ImportRow(row, companyId, year, result);
            }

            return result;
        }

        private static string GetContentXml(Stream fileStream)
        {
            var contentXml = "";

            using (var zipInputStream = new ZipInputStream(fileStream))
            {
                ZipEntry contentEntry = null;
                while ((contentEntry = zipInputStream.GetNextEntry()) != null)
                {
                    if (!contentEntry.IsFile)
                        continue;
                    if (contentEntry.Name.ToLower() == "content.xml")
                        break;
                }

                if (contentEntry == null || contentEntry.Name.ToLower() != "content.xml")
                {
                    throw new Exception("Cannot find content.xml");
                }

                using (var ms = new MemoryStream())
                {
                    zipInputStream.CopyTo(ms);
                    contentXml = Encoding.UTF8.GetString(ms.ToArray());
                }
            }
            return contentXml;
        }

        private void ImportRow(XElement row, long companyId, short year, ImportResult result)
        {
            var cells = row.Descendants()
                           .Where(c => c.Name == "{urn:oasis:names:tc:opendocument:xmlns:table:1.0}table-cell")
                           .ToList();

            var dto = new DataDto();
            int columnIndex = 0;

            foreach (var cell in cells)
            {
                int repeat = 1;
                var repeatAttr = cell.Attribute("{urn:oasis:names:tc:opendocument:xmlns:table:1.0}number-columns-repeated");
                if (repeatAttr != null && int.TryParse(repeatAttr.Value, out int repeatCount))
                {
                    repeat = repeatCount;
                }

                string cellValue = GetCellText(cell);

                for (int i = 0; i < repeat; i++)
                {
                    dto.Fields[columnIndex] = cellValue;
                    columnIndex++;
                }
            }

            result.ImportedData.Add(dto);
        }

        private string GetCellText(XElement cell)
        {
            var textP = cell.Descendants("{urn:oasis:names:tc:opendocument:xmlns:text:1.0}p").FirstOrDefault();
            return textP != null ? textP.Value.Trim() : string.Empty;
        }
    }
}

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">
        <div style="margin:20px;">
            <h2>匯入 .ods 檔案</h2>
            <asp:FileUpload ID="FileUpload1" runat="server" />
            <asp:Button ID="btnImport" runat="server" Text="匯入" OnClick="btnImport_Click" />
            <br /><br />
            <asp:Label ID="lblMessage" runat="server" ForeColor="Red"></asp:Label>
            <br /><br />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" />
        </div>
    </form>
</body>
</html>

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

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

        }
        protected void btnImport_Click(object sender, EventArgs e)
        {
            if (!FileUpload1.HasFile)
            {
                lblMessage.Text = "請選擇一個 .ods 檔案。";
                return;
            }

            var file = FileUpload1.PostedFile;
            if (!file.FileName.EndsWith(".ods", StringComparison.OrdinalIgnoreCase))
            {
                lblMessage.Text = "僅支援 .ods 檔案。";
                return;
            }

            try
            {
                OdsImporter importer = new OdsImporter();
                using (var stream = file.InputStream)
                {
                    var result = importer.Import(stream, companyId: 0, year: 0);

                    if (result.ImportedData.Count == 0)
                    {
                        lblMessage.Text = "無匯入資料。";
                        GridView1.DataSource = null;
                        GridView1.DataBind();
                        return;
                    }

                    // 取得所有有值的欄位索引
                    var usedColumnIndexes = result.ImportedData
                        .SelectMany(d => d.Fields)
                        .Where(kvp => !string.IsNullOrWhiteSpace(kvp.Value))
                        .Select(kvp => kvp.Key)
                        .Distinct()
                        .OrderBy(k => k)
                        .ToList();

                    var table = new DataTable();

                    foreach (var colIndex in usedColumnIndexes)
                    {
                        table.Columns.Add("欄位" + (colIndex + 1));
                    }

                    foreach (var dto in result.ImportedData)
                    {
                        // 檢查這筆資料是否「至少一格有值」
                        bool hasAnyValue = usedColumnIndexes.Any(idx =>
                            dto.Fields.ContainsKey(idx) && !string.IsNullOrWhiteSpace(dto.Fields[idx]));

                        if (!hasAnyValue)
                            continue; // 整列都是空的，跳過

                        var row = table.NewRow();
                        foreach (var colIndex in usedColumnIndexes)
                        {
                            dto.Fields.TryGetValue(colIndex, out string value);
                            row["欄位" + (colIndex + 1)] = value;
                        }
                        table.Rows.Add(row);
                    }

                    if (table.Rows.Count == 0)
                    {
                        lblMessage.Text = "所有資料列皆為空白，無匯入。";
                        GridView1.DataSource = null;
                        GridView1.DataBind();
                        return;
                    }

                    lblMessage.Text = $"成功匯入 {table.Rows.Count} 筆資料。";
                    GridView1.DataSource = table;
                    GridView1.DataBind();
                }
            }
            catch (Exception ex)
            {
                lblMessage.Text = "錯誤：" + ex.Message;
            }
        }

    }
}

結果

(完)

相關

[研究]ASP.NET, OdsExporter 使用 C# 寫入匯出 .ods (一)

https://shaurong.blogspot.com/2025/06/aspnet-odsexporter-c-ods.html

[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (三)用 System.IO.Compression 取代 SharpZipLib

https://shaurong.blogspot.com/2025/06/aspnet-odsimporter-c-ods_88.html

[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (二)欄位數量不固定

https://shaurong.blogspot.com/2025/06/aspnet-odsimporter-c-ods_2.html

[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (一)

https://shaurong.blogspot.com/2025/06/aspnet-odsimporter-c-ods.html

浮雲雅築

2025年6月2日星期一

[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (二)欄位數量不固定

沒有留言:

張貼留言

2025年6月2日 星期一

[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (二)欄位數量不固定

沒有留言:

張貼留言

2025年6月2日星期一