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

[研究]ASP.NET, OdsExporter 使用 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/

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

OdsExporter.cs

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

namespace WebApplication1
{
    public class OdsExporter
    {
        public MemoryStream ExportToStream(DataTable table)
        {
            var memoryStream = new MemoryStream();

            using (var archive = new ZipArchive(memoryStream, ZipArchiveMode.Create, leaveOpen: true))
            {
                // mimetype（必須為第一個，且不壓縮）
                var mimetypeEntry = archive.CreateEntry("mimetype", CompressionLevel.NoCompression);
                using (var writer = new StreamWriter(mimetypeEntry.Open(), Encoding.ASCII))
                {
                    writer.Write("application/vnd.oasis.opendocument.spreadsheet");
                }

                // META-INF/manifest.xml
                var manifestEntry = archive.CreateEntry("META-INF/manifest.xml");
                using (var writer = new StreamWriter(manifestEntry.Open(), Encoding.UTF8))
                {
                    writer.Write(@"<?xml version=""1.0"" encoding=""UTF-8""?>
<manifest:manifest xmlns:manifest=""urn:oasis:names:tc:opendocument:xmlns:manifest:1.0"">
  <manifest:file-entry manifest:full-path=""/"" manifest:media-type=""application/vnd.oasis.opendocument.spreadsheet""/>
  <manifest:file-entry manifest:full-path=""content.xml"" manifest:media-type=""text/xml""/>
</manifest:manifest>");
                }

                // content.xml
                var contentEntry = archive.CreateEntry("content.xml");
                using (var writer = new StreamWriter(contentEntry.Open(), Encoding.UTF8))
                {
                    writer.Write(GenerateContentXml(table));
                }
            }

            memoryStream.Position = 0; // 重設串流位置供下載
            return memoryStream;
        }

        private string GenerateContentXml(DataTable table)
        {
            XNamespace office = "urn:oasis:names:tc:opendocument:xmlns:office:1.0";
            XNamespace tableNs = "urn:oasis:names:tc:opendocument:xmlns:table:1.0";
            XNamespace text = "urn:oasis:names:tc:opendocument:xmlns:text:1.0";

            var document = new XDocument(
                new XDeclaration("1.0", "UTF-8", "yes"),
                new XElement(office + "document-content",
                    new XAttribute(XNamespace.Xmlns + "office", office),
                    new XAttribute(XNamespace.Xmlns + "table", tableNs),
                    new XAttribute(XNamespace.Xmlns + "text", text),
                    new XAttribute(office + "version", "1.2"),
                    new XElement(office + "body",
                        new XElement(office + "spreadsheet",
                            new XElement(tableNs + "table",
                                new XAttribute(tableNs + "name", "Sheet1"),

                                // 欄位列
                                new XElement(tableNs + "table-row",
                                    from DataColumn col in table.Columns
                                    select new XElement(tableNs + "table-cell",
                                        new XAttribute(office + "value-type", "string"),
                                        new XElement(text + "p", col.ColumnName)
                                    )
                                ),

                                // 資料列
                                from DataRow row in table.Rows
                                select new XElement(tableNs + "table-row",
                                    from DataColumn col in table.Columns
                                    select new XElement(tableNs + "table-cell",
                                        new XAttribute(office + "value-type", "string"),
                                        new XElement(text + "p", row[col]?.ToString() ?? "")
                                    )
                                )
                            )
                        )
                    )
                )
            );

            return document.ToString(SaveOptions.DisableFormatting);
        }
    }
}

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:Button ID="btnExport" runat="server" Text="從資料庫匯出為 ODS" OnClick="btnExport_Click" /><br />
        <asp:Label ID="lblMessage" runat="server"></asp:Label>
    </form>
</body>
</html>

Default.aspx.cs

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

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

        }
        protected void btnExport_Click(object sender, EventArgs e)
        {
            try
            {
                // 1. 從資料庫取得 DataTable
                var table = GetDataTableFromDatabase();

                if (table == null || table.Rows.Count == 0)
                {
                    lblMessage.Text = "資料表為空，無法匯出。";
                    return;
                }

                // 2. 匯出成 ODS 並下載
                OdsExporter exporter = new OdsExporter();
                using (var stream = exporter.ExportToStream(table))
                {
                    Response.Clear();
                    Response.ContentType = "application/vnd.oasis.opendocument.spreadsheet";
                    Response.AddHeader("Content-Disposition", "attachment; filename=匯出.ods");
                    Response.BinaryWrite(stream.ToArray());
                    Response.End();
                }
            }
            catch (Exception ex)
            {
                lblMessage.Text = "匯出時發生錯誤：" + ex.Message;
            }
        }

        private DataTable GetDataTableFromDatabase()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            using (var conn = new SqlConnection(connectionString))
            using (var cmd = new SqlCommand("SELECT [SN], [Field1], [Field2] FROM [MyDB].[dbo].[MyTable]", conn))
            using (var adapter = new SqlDataAdapter(cmd))
            {
                var table = new DataTable();
                conn.Open();
                adapter.Fill(table);
                return table;
            }
        }
    }
}

結果