[研究]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/
********************************************************************************
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 string fieldName1 { get; set; }
public string fieldName2 { get; set; }
public string fieldName3 { get; set; }
public string fieldName4 { get; set; }
// 可根據實際欄位添加更多屬性
}
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 = (from c in row.Descendants()
where c.Name == "{urn:oasis:names:tc:opendocument:xmlns:table:1.0}table-cell"
select c).ToList();
var dto = new DataDto();
var count = cells.Count;
var j = -1;
for (var i = 0; i < count; i++)
{
j++;
var cell = cells[i];
var attr = cell.Attribute("{urn:oasis:names:tc:opendocument:xmlns:table:1.0}number-columns-repeated");
if (attr != null)
{
if (int.TryParse(attr.Value, out int numToSkip))
{
j += numToSkip - 1;
}
}
if (i > 30) break;
if (j == 0)
{
dto.fieldName1 = cell.Value;
}
if (j == 1)
{
dto.fieldName2 = cell.Value;
}
if (j == 2)
{
dto.fieldName3 = cell.Value;
}
if (j == 3)
{
dto.fieldName4 = cell.Value;
}
// 可依需求繼續擴充欄位
}
result.ImportedData.Add(dto);
}
}
}
|
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.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 = "無匯入資料。";
}
else
{
lblMessage.Text = $"成功匯入 {result.ImportedData.Count} 筆資料。";
GridView1.DataSource = result.ImportedData;
GridView1.DataBind();
}
}
}
catch (Exception ex)
{
lblMessage.Text = "錯誤：" + ex.Message;
}
}
}
} |
結果
不顯示空白橫列，改成下面
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 string fieldName1 { get; set; }
public string fieldName2 { get; set; }
public string fieldName3 { get; set; }
public string fieldName4 { get; set; }
// 可根據實際欄位添加更多屬性
}
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 = (from c in row.Descendants()
where c.Name == "{urn:oasis:names:tc:opendocument:xmlns:table:1.0}table-cell"
select c).ToList();
var dto = new DataDto();
var count = cells.Count;
var j = -1;
for (var i = 0; i < count; i++)
{
j++;
var cell = cells[i];
var attr = cell.Attribute("{urn:oasis:names:tc:opendocument:xmlns:table:1.0}number-columns-repeated");
if (attr != null)
{
if (int.TryParse(attr.Value, out int numToSkip))
{
j += numToSkip - 1;
}
}
if (i > 30) break;
if (j == 0)
{
dto.fieldName1 = cell.Value;
}
if (j == 1)
{
dto.fieldName2 = cell.Value;
}
if (j == 2)
{
dto.fieldName3 = cell.Value;
}
if (j == 3)
{
dto.fieldName4 = cell.Value;
}
// 可依需求繼續擴充欄位
}
// 判斷是否整列皆為空
bool isEmptyRow =
string.IsNullOrWhiteSpace(dto.fieldName1) &&
string.IsNullOrWhiteSpace(dto.fieldName2) &&
string.IsNullOrWhiteSpace(dto.fieldName3) &&
string.IsNullOrWhiteSpace(dto.fieldName4);
if (!isEmptyRow)
{
result.ImportedData.Add(dto);
}
}
}
}
|
結果
(完)
相關
[研究]ASP.NET, OdsExporter 使用 C# 寫入匯出 .ods (一)
[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (三)用 System.IO.Compression 取代 SharpZipLib
[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (二)欄位數量不固定
[研究]ASP.NET, OdsImporter 使用 C# 讀取匯入 .ods (一)
沒有留言:
張貼留言