[研究]ASP.NET,WebForm,免ODS套件寫入匯出.ods
2024-10-29
環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019 + SQL Server Management Studio (SSMS) 19
********************************************************************************
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default2.aspx.cs"
Inherits="WebApplication1.Default2" %>
<!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">
</form>
</body>
</html>
|
Default.aspx.cs
using System;
using System.IO;
using System.IO.Compression;
namespace WebApplication1
{
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
GenerateOdsFile();
}
private void GenerateOdsFile()
{
using (MemoryStream odsStream = new MemoryStream())
{
using (ZipArchive zip = new ZipArchive(odsStream, ZipArchiveMode.Create, true))
{
// 添加 mimetype 檔案
ZipArchiveEntry mimeEntry = zip.CreateEntry("mimetype", CompressionLevel.NoCompression);
using (StreamWriter writer = new StreamWriter(mimeEntry.Open()))
{
writer.Write("application/vnd.oasis.opendocument.spreadsheet");
}
// 添加必要的 META-INF/manifest.xml 文件
ZipArchiveEntry manifestEntry = zip.CreateEntry("META-INF/manifest.xml", CompressionLevel.Fastest);
using (StreamWriter writer = new StreamWriter(manifestEntry.Open()))
{
writer.Write(
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
"<manifest:manifest xmlns:manifest=\"urn:oasis:names:tc:opendocument:xmlns:manifest:1.0\">\n" +
" <manifest:file-entry manifest:full-path=\"/\" manifest:media-type=\"application/vnd.oasis.opendocument.spreadsheet\"/>\n" +
" <manifest:file-entry manifest:full-path=\"content.xml\" manifest:media-type=\"text/xml\"/>\n" +
"</manifest:manifest>"
);
}
// 添加 content.xml 文件
ZipArchiveEntry contentEntry = zip.CreateEntry("content.xml", CompressionLevel.Fastest);
using (StreamWriter writer = new StreamWriter(contentEntry.Open()))
{
writer.Write(
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
"<office:document-content xmlns:office=\"urn:oasis:names:tc:opendocument:xmlns:office:1.0\" " +
"xmlns:table=\"urn:oasis:names:tc:opendocument:xmlns:table:1.0\" " +
"xmlns:text=\"urn:oasis:names:tc:opendocument:xmlns:text:1.0\" " +
"xmlns:style=\"urn:oasis:names:tc:opendocument:xmlns:style:1.0\" " +
"office:version=\"1.2\">\n" +
"<office:body><office:spreadsheet><table:table table:name=\"Sheet1\" table:style-name=\"ta1\">\n" +
"<table:table-column table:style-name=\"co1\" table:number-columns-repeated=\"2\"/>\n" +
"<table:table-row table:style-name=\"ro1\"><table:table-cell><text:p>資料1</text:p></table:table-cell>" +
"<table:table-cell><text:p>資料2</text:p></table:table-cell></table:table-row>\n" +
"<table:table-row table:style-name=\"ro1\"><table:table-cell><text:p>資料3</text:p></table:table-cell>" +
"<table:table-cell><text:p>資料4</text:p></table:table-cell></table:table-row>\n" +
"</table:table></office:spreadsheet></office:body></office:document-content>"
);
}
}
odsStream.Position = 0;
Response.Clear();
Response.ContentType = "application/vnd.oasis.opendocument.spreadsheet";
Response.AddHeader("Content-Disposition", "attachment; filename=sample.ods");
odsStream.CopyTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
//---
}
}
|
結果 LibreOffice 7.6 正常讀取，但 Excel 2021開啟有警告，修復後正常顯示。這是因為Excel 對 .ods 文件有較嚴格的要求，尤其是 XML 標籤和屬性的結構。敝人嘗試多次，暫時仍無法解決，或許可以一開始用 LibreOffice建立一個正常的 template.ods 給程式插入資料使用。
Default.aspx.cs
using System;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Xml.Linq;
namespace WebApplication1
{
public partial class Default3 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string odsFilePath = @"C:\Users\Administrator\Downloads\Template2.ods";
string targetOdsPath = @"C:\Users\Administrator\Downloads\S2.ods";
File.Copy(odsFilePath, targetOdsPath, overwrite: true);
InsertDataToODS(targetOdsPath);
}
public void InsertDataToODS(string odsFilePath)
{
// 定義要插入的資料
var data = new string[,]
{
{ "A1", "B1" },
{ "A2", "B2" }
};
// 打開 .ods 檔案 (實際上是 Zip 檔案)
using (var zip = ZipFile.Open(odsFilePath, ZipArchiveMode.Update))
{
// 尋找 content.xml
var entry = zip.GetEntry("content.xml");
if (entry == null)
{
throw new FileNotFoundException("content.xml not found in the .ods file.");
}
// 讀取 content.xml 並載入 XML 文件
XDocument doc;
using (var stream = entry.Open())
{
doc = XDocument.Load(stream);
}
// 尋找要插入資料的表格位置 (這裡假設是 Sheet1)
XNamespace ns = "urn:oasis:names:tc:opendocument:xmlns:table:1.0";
var sheet = doc.Descendants(ns + "table").FirstOrDefault();
if (sheet != null)
{
// 生成 2x2 表格的 Row 和 Cell
for (int i = 0; i < 2; i++)
{
var row = new XElement(ns + "table-row");
for (int j = 0; j < 2; j++)
{
var cell = new XElement(ns + "table-cell",
new XElement(ns + "p", data[i, j]));
row.Add(cell);
}
sheet.Add(row);
}
}
// 將修改後的 XML 內容重新寫入 content.xml
using (var stream = entry.Open())
{
stream.SetLength(0); // 清除原始內容
doc.Save(stream);
}
}
}
//---
}
}
|
實際測試，還是有問題，在無 ODS 套件情況，想自己或靠 ChatGPT 的 Code 產出 Excel 2021 可以正常讀取的 .ods 並不容易，運氣好是警告，然後可以讀取，運氣不好是完全不能讀取。
(完)
相關
沒有留言:
張貼留言