[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (三)用 System.IO.Compression 取代 SharpZipLib
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/
********************************************************************************
需要 FileSystem 組件
因為ZipArchive 主要定義在 System.IO.Compression.dll，但 ZipArchive 對於「檔案壓縮/解壓」的支援（例如 ZipFile.OpenRead）需要 System.IO.Compression.FileSystem.dll 才能完整運作。
雖然你目前用的是 Stream 開 ZIP，只用到 ZipArchive 就夠了，但系統仍可能需要該組件支援。
********************************************************************************
OdsImporter.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Compression;
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)
{
string contentXml = "";
using (var archive = new ZipArchive(fileStream, ZipArchiveMode.Read, leaveOpen: true))
{
var contentEntry = archive.Entries.FirstOrDefault(e =>
string.Equals(e.FullName, "content.xml", StringComparison.OrdinalIgnoreCase));
if (contentEntry == null)
{
throw new Exception("Cannot find content.xml");
}
using (var stream = contentEntry.Open())
using (var ms = new MemoryStream())
{
stream.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 和 Default.aspx.cs 照舊。
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 (一)
[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (三)用 System.IO.Compression 取代 SharpZipLib
[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (二)欄位數量不固定
[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (一)
沒有留言:
張貼留言