浮雲雅築: [研究] [C#] 用OdsReadWrite讀取 LibreOffice、OpenOffice 的試算表(Calc) 檔案 (.ods)(使用 .NET 4.5 的 ZipFile)

[研究] [C#] 用OdsReadWrite讀取 LibreOffice、OpenOffice 的試算表(Calc) 檔案 (.ods)(使用 .NET 4.5 的 ZipFile)

2016-12-29

本文使用的方法是把 .ods 當成 .zip，解壓後取得 content.xml 使用。

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

ODF release dates:

ODF 1.0 May 2005
ODF 1.1 February 2007
ODF 1.2 September 2012
ODF 1.3 January 2020

https://wiki.documentfoundation.org/Comparison_of_ODF_software

How to Read and Write ODF/ODS Files (OpenDocument Spreadsheets)
28 Jul 2011
https://www.codeproject.com/Articles/38425/How-to-Read-and-Write-ODF-ODS-Files-OpenDocument-2
所以 odsreadwrite.zip 可能只支援到 ODF, ODS 1.1 版

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

LibreOffice
https://zh-tw.libreoffice.org/

(下圖) 先用 Calc 做一個檔案，內容如下

(下圖) 用 Visual Studio 2015 with Update 3 建立一個 WinForm 方案

Form1.cs 內容

using System;
using System.Data;
using System.IO;
using System.Windows.Forms;
using System.Xml;
using System.Globalization;

// 需要 .NET 4.5 才支援
// "參考" => "加入參考" => "組件" => "架構"，加入 System.IO.Compression.FileSystem
using System.IO.Compression;

namespace ODSDemo
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
Stream myStream = null;
OpenFileDialog openFileDialog1 = new OpenFileDialog();

openFileDialog1.InitialDirectory = "c:\\";
openFileDialog1.Filter = "ods files (*.ods)|*.ods|All files (*.*)|*.*";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;
openFileDialog1.InitialDirectory = System.IO.Directory.GetCurrentDirectory();
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
if ((myStream = openFileDialog1.OpenFile()) != null)
{
label1.Text = openFileDialog1.FileName; // 包含路徑
string inputFilePath = openFileDialog1.FileName; // 包含路徑
//DataSet ds = ReadOdsFile(inputFilePath);
DataSet ds = ReadOdsFile2(inputFilePath);

dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = ds; // dataset
dataGridView1.DataSource = ds.Tables[0];

//using (myStream)
//{
// // Insert code to read the stream here.
//}
}
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
}

// ********************************************************************************
// 下面程式碼取自
// How to Read and Write ODFODS Files (OpenDocument Spreadsheets)
// https://www.codeproject.com/articles/38425/how-to-read-and-write-odf-ods-files-opendocument-s%20here%20for%20reference
// 解壓後取自 OdsReaderWriter.cs 檔案內容
// 一些用不上的註解掉
// ********************************************************************************
private static string[,] namespaces = new string[,]
{
{"table", "urn:oasis:names:tc:opendocument:xmlns:table:1.0"},
{"office", "urn:oasis:names:tc:opendocument:xmlns:office:1.0"},
{"style", "urn:oasis:names:tc:opendocument:xmlns:style:1.0"},
{"text", "urn:oasis:names:tc:opendocument:xmlns:text:1.0"},
{"draw", "urn:oasis:names:tc:opendocument:xmlns:drawing:1.0"},
{"fo", "urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0"},
{"dc", "http://purl.org/dc/elements/1.1/"},
{"meta", "urn:oasis:names:tc:opendocument:xmlns:meta:1.0"},
{"number", "urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0"},
{"presentation", "urn:oasis:names:tc:opendocument:xmlns:presentation:1.0"},
{"svg", "urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0"},
{"chart", "urn:oasis:names:tc:opendocument:xmlns:chart:1.0"},
{"dr3d", "urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0"},
{"math", "http://www.w3.org/1998/Math/MathML"},
{"form", "urn:oasis:names:tc:opendocument:xmlns:form:1.0"},
{"script", "urn:oasis:names:tc:opendocument:xmlns:script:1.0"},
{"ooo", "http://openoffice.org/2004/office"},
{"ooow", "http://openoffice.org/2004/writer"},
{"oooc", "http://openoffice.org/2004/calc"},
{"dom", "http://www.w3.org/2001/xml-events"},
{"xforms", "http://www.w3.org/2002/xforms"},
{"xsd", "http://www.w3.org/2001/XMLSchema"},
{"xsi", "http://www.w3.org/2001/XMLSchema-instance"},
{"rpt", "http://openoffice.org/2005/report"},
{"of", "urn:oasis:names:tc:opendocument:xmlns:of:1.2"},
{"rdfa", "http://docs.oasis-open.org/opendocument/meta/rdfa#"},
{"config", "urn:oasis:names:tc:opendocument:xmlns:config:1.0"}
};

//// Read zip stream (.ods file is zip file).
//private ZipFile GetZipFile(Stream stream)
//{
// return ZipFile.Read(stream);
//}

// Read zip file (.ods file is zip file).
//private ZipFile GetZipFile(string inputFilePath)
//{
// return ZipFile.Read(inputFilePath);
//}

//private XmlDocument GetContentXmlFile(ZipFile zipFile)
//{
// // Get file(in zip archive) that contains data ("content.xml").
// ZipEntry contentZipEntry = zipFile["content.xml"];

// // Extract that file to MemoryStream.
// Stream contentStream = new MemoryStream();
// contentZipEntry.Extract(contentStream);
// contentStream.Seek(0, SeekOrigin.Begin);

// // Create XmlDocument from MemoryStream (MemoryStream contains content.xml).
// XmlDocument contentXml = new XmlDocument();
// contentXml.Load(contentStream);

// return contentXml;
//}

private XmlDocument GetContentXmlFile2(string inputFilePath)
{
// Get file(in zip archive) that contains data ("content.xml").
//ZipEntry contentZipEntry = zipFile["content.xml"];

//string startPath = @"c:\example\start";
//string zipPath = @"c:\example\result.zip";
//string extractPath = @"c:\example\extract";
//string startPath = @"c:\example\start";
string zipPath = inputFilePath; // LibreOffice Calc
string extractPath = System.IO.Directory.GetCurrentDirectory() + "\\Extract";
Directory.Delete(extractPath, true);

// 壓縮目錄中檔案
//ZipFile.CreateFromDirectory(startPath, zipPath);

// 解壓縮
ZipFile.ExtractToDirectory(zipPath, extractPath);

// Extract that file to MemoryStream.
Stream contentStream = new MemoryStream();
//contentZipEntry.Extract(contentStream);
contentStream.Seek(0, SeekOrigin.Begin);

// Create XmlDocument from MemoryStream (MemoryStream contains content.xml).
XmlDocument contentXml = new XmlDocument();
//contentXml.Load(contentStream);
contentXml.Load(extractPath + "\\content.xml");

return contentXml;
}

private XmlNamespaceManager InitializeXmlNamespaceManager(XmlDocument xmlDocument)
{
XmlNamespaceManager nmsManager = new XmlNamespaceManager(xmlDocument.NameTable);

for (int i = 0; i < namespaces.GetLength(0); i++)
nmsManager.AddNamespace(namespaces[i, 0], namespaces[i, 1]);

return nmsManager;
}

/// <summary>
/// Read .ods file and store it in DataSet.
/// </summary>
/// <param name="inputFilePath">Path to the .ods file.</param>
/// <returns>DataSet that represents .ods file.</returns>
//public DataSet ReadOdsFile(string inputFilePath)
//{
// ZipFile odsZipFile = this.GetZipFile(inputFilePath);

// // Get content.xml file
// XmlDocument contentXml = this.GetContentXmlFile(odsZipFile);

// // Initialize XmlNamespaceManager
// XmlNamespaceManager nmsManager = this.InitializeXmlNamespaceManager(contentXml);

// DataSet odsFile = new DataSet(Path.GetFileName(inputFilePath));

// foreach (XmlNode tableNode in this.GetTableNodes(contentXml, nmsManager))
// odsFile.Tables.Add(this.GetSheet(tableNode, nmsManager));

// return odsFile;
//}

public DataSet ReadOdsFile2(string inputFilePath)
{
//ZipFile odsZipFile = this.GetZipFile(inputFilePath);

// Get content.xml file
//XmlDocument contentXml = this.GetContentXmlFile2(odsZipFile);
XmlDocument contentXml = this.GetContentXmlFile2(inputFilePath);

// Initialize XmlNamespaceManager
XmlNamespaceManager nmsManager = this.InitializeXmlNamespaceManager(contentXml);

DataSet odsFile = new DataSet(Path.GetFileName(inputFilePath));

foreach (XmlNode tableNode in this.GetTableNodes(contentXml, nmsManager))
odsFile.Tables.Add(this.GetSheet(tableNode, nmsManager));

return odsFile;
}

// In ODF sheet is stored in table:table node
private XmlNodeList GetTableNodes(XmlDocument contentXmlDocument, XmlNamespaceManager nmsManager)
{
return contentXmlDocument.SelectNodes("/office:document-content/office:body/office:spreadsheet/table:table", nmsManager);
}

private DataTable GetSheet(XmlNode tableNode, XmlNamespaceManager nmsManager)
{
DataTable sheet = new DataTable(tableNode.Attributes["table:name"].Value);

XmlNodeList rowNodes = tableNode.SelectNodes("table:table-row", nmsManager);

int rowIndex = 0;
foreach (XmlNode rowNode in rowNodes)
this.GetRow(rowNode, sheet, nmsManager, ref rowIndex);

return sheet;
}

private void GetRow(XmlNode rowNode, DataTable sheet, XmlNamespaceManager nmsManager, ref int rowIndex)
{
XmlAttribute rowsRepeated = rowNode.Attributes["table:number-rows-repeated"];
if (rowsRepeated == null || Convert.ToInt32(rowsRepeated.Value, CultureInfo.InvariantCulture) == 1)
{
while (sheet.Rows.Count < rowIndex)
sheet.Rows.Add(sheet.NewRow());

DataRow row = sheet.NewRow();

XmlNodeList cellNodes = rowNode.SelectNodes("table:table-cell", nmsManager);

int cellIndex = 0;
foreach (XmlNode cellNode in cellNodes)
this.GetCell(cellNode, row, nmsManager, ref cellIndex);

sheet.Rows.Add(row);

rowIndex++;
}
else
{
rowIndex += Convert.ToInt32(rowsRepeated.Value, CultureInfo.InvariantCulture);
}

// sheet must have at least one cell
if (sheet.Rows.Count == 0)
{
sheet.Rows.Add(sheet.NewRow());
sheet.Columns.Add();
}
}

private void GetCell(XmlNode cellNode, DataRow row, XmlNamespaceManager nmsManager, ref int cellIndex)
{
XmlAttribute cellRepeated = cellNode.Attributes["table:number-columns-repeated"];

if (cellRepeated == null)
{
DataTable sheet = row.Table;

while (sheet.Columns.Count <= cellIndex)
sheet.Columns.Add();

row[cellIndex] = this.ReadCellValue(cellNode);

cellIndex++;
}
else
{
cellIndex += Convert.ToInt32(cellRepeated.Value, CultureInfo.InvariantCulture);
}
}

private string ReadCellValue(XmlNode cell)
{
XmlAttribute cellVal = cell.Attributes["office:value"];

if (cellVal == null)
return String.IsNullOrEmpty(cell.InnerText) ? null : cell.InnerText;
else
return cellVal.Value;
}

///// <summary>
///// Writes DataSet as .ods file.
///// </summary>
///// <param name="odsFile">DataSet that represent .ods file.</param>
///// <param name="outputFilePath">The name of the file to save to.</param>
//public void WriteOdsFile(DataSet odsFile, string outputFilePath)
//{
// ZipFile templateFile = this.GetZipFile(Assembly.GetExecutingAssembly().GetManifestResourceStream("OdsReadWrite.template.ods"));

// XmlDocument contentXml = this.GetContentXmlFile(templateFile);

// XmlNamespaceManager nmsManager = this.InitializeXmlNamespaceManager(contentXml);

// XmlNode sheetsRootNode = this.GetSheetsRootNodeAndRemoveChildrens(contentXml, nmsManager);

// foreach (DataTable sheet in odsFile.Tables)
// this.SaveSheet(sheet, sheetsRootNode);

// this.SaveContentXml(templateFile, contentXml);

// templateFile.Save(outputFilePath);
//}

//private XmlNode GetSheetsRootNodeAndRemoveChildrens(XmlDocument contentXml, XmlNamespaceManager nmsManager)
//{
// XmlNodeList tableNodes = this.GetTableNodes(contentXml, nmsManager);

// XmlNode sheetsRootNode = tableNodes.Item(0).ParentNode;
// // remove sheets from template file
// foreach (XmlNode tableNode in tableNodes)
// sheetsRootNode.RemoveChild(tableNode);

// return sheetsRootNode;
//}

//private void SaveSheet(DataTable sheet, XmlNode sheetsRootNode)
//{
// XmlDocument ownerDocument = sheetsRootNode.OwnerDocument;

// XmlNode sheetNode = ownerDocument.CreateElement("table:table", this.GetNamespaceUri("table"));

// XmlAttribute sheetName = ownerDocument.CreateAttribute("table:name", this.GetNamespaceUri("table"));
// sheetName.Value = sheet.TableName;
// sheetNode.Attributes.Append(sheetName);

// this.SaveColumnDefinition(sheet, sheetNode, ownerDocument);

// this.SaveRows(sheet, sheetNode, ownerDocument);

// sheetsRootNode.AppendChild(sheetNode);
//}

//private void SaveColumnDefinition(DataTable sheet, XmlNode sheetNode, XmlDocument ownerDocument)
//{
// XmlNode columnDefinition = ownerDocument.CreateElement("table:table-column", this.GetNamespaceUri("table"));

// XmlAttribute columnsCount = ownerDocument.CreateAttribute("table:number-columns-repeated", this.GetNamespaceUri("table"));
// columnsCount.Value = sheet.Columns.Count.ToString(CultureInfo.InvariantCulture);
// columnDefinition.Attributes.Append(columnsCount);

// sheetNode.AppendChild(columnDefinition);
//}

//private void SaveRows(DataTable sheet, XmlNode sheetNode, XmlDocument ownerDocument)
//{
// DataRowCollection rows = sheet.Rows;
// for (int i = 0; i < rows.Count; i++)
// {
// XmlNode rowNode = ownerDocument.CreateElement("table:table-row", this.GetNamespaceUri("table"));

// this.SaveCell(rows[i], rowNode, ownerDocument);

// sheetNode.AppendChild(rowNode);
// }
//}

//private void SaveCell(DataRow row, XmlNode rowNode, XmlDocument ownerDocument)
//{
// object[] cells = row.ItemArray;

// for (int i = 0; i < cells.Length; i++)
// {
// XmlElement cellNode = ownerDocument.CreateElement("table:table-cell", this.GetNamespaceUri("table"));

// if (row[i] != DBNull.Value)
// {
// // We save values as text (string)
// XmlAttribute valueType = ownerDocument.CreateAttribute("office:value-type", this.GetNamespaceUri("office"));
// valueType.Value = "string";
// cellNode.Attributes.Append(valueType);

// XmlElement cellValue = ownerDocument.CreateElement("text:p", this.GetNamespaceUri("text"));
// cellValue.InnerText = row[i].ToString();
// cellNode.AppendChild(cellValue);
// }

// rowNode.AppendChild(cellNode);
// }
//}

//private void SaveContentXml(ZipFile templateFile, XmlDocument contentXml)
//{
// templateFile.RemoveEntry("content.xml");

// MemoryStream memStream = new MemoryStream();
// contentXml.Save(memStream);
// memStream.Seek(0, SeekOrigin.Begin);

// templateFile.AddEntry("content.xml", memStream);
//}

//private string GetNamespaceUri(string prefix)
//{
// for (int i = 0; i < namespaces.GetLength(0); i++)
// {
// if (namespaces[i, 0] == prefix)
// return namespaces[i, 1];
// }

// throw new InvalidOperationException("Can't find that namespace URI");
//}
}
}

(下圖) 執行畫面

(完)

相關

[研究] C# .NET 存取 OpenOffice Calc 試算表 .ods 套件評估
http://shaurong.blogspot.com/2016/12/c-net-openoffice-calc-ods.html

[研究] [C#] 讀取 LibreOffice 5.1.6 的試算表(Calc) 檔案 (.ods)(使用 .NET 4.5 的 ZipFile)
http://shaurong.blogspot.com/2016/12/c-libreoffice-516-calc-ods-net-45.html

[研究] [C#] 讀取 LibreOffice 5.1.6 的試算表(Calc) 檔案 (.ods)(使用 DotNetZip)
http://shaurong.blogspot.com/2016/12/c-libreoffice-516-calc-ods.html

浮雲雅築

2016年12月29日星期四

[研究] [C#] 用OdsReadWrite讀取 LibreOffice、OpenOffice 的試算表(Calc) 檔案 (.ods)(使用 .NET 4.5 的 ZipFile)

沒有留言:

張貼留言

2016年12月29日 星期四

[研究] [C#] 用OdsReadWrite讀取 LibreOffice、OpenOffice 的試算表(Calc) 檔案 (.ods)(使用 .NET 4.5 的 ZipFile)

沒有留言:

張貼留言

2016年12月29日星期四