浮雲雅築: [研究] [C#] 使用 OpenOffice 4.1.3 SDK 讀取試算表(Calc) 檔案 (.ods)

[研究] [C#] 使用 OpenOffice 4.1.3 SDK 讀取試算表(Calc) 檔案 (.ods)

2016-12-30

先簡單說結論
OpenOffice 4.1.3 SDK 只支援 .NET 2，但不支援 .NET 4.x
LibreOffice 5.3.0 SDK 支援 .NET 4.x，但不支援 x64

Apache OpenOffice 4.1.3 SDK 下載
https://www.openoffice.org/download/sdk/

工具：Visual Studio 2015 with Update 3

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

下載安裝 OpenOffice 4.1.3 SDK
方案的 "參考" 加入 C:\Program Files(x86)\OpenOffice 4\sdk\cli 目錄的
cli_basetypes, cli_cppuhelper, cli_oootypes, cli_ure, and cli_uretypes.dll

其中 WinForm 程式 Form1.cs 片段換成下面

private void button2_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; // 包含路徑

fileName = openFileDialog1.FileName; // 包含路徑

XComponentContext context = Bootstrap.bootstrap();
XMultiServiceFactory factory = (XMultiServiceFactory)context.getServiceManager();
XComponentLoader loader = (XComponentLoader)factory.createInstance("com.sun.star.frame.Desktop");

PropertyValue[] args1 = new PropertyValue[1];
args1[0] = new PropertyValue();
args1[0].Name = "Hidden";
args1[0].Value = new uno.Any((Boolean)true);

//XSpreadsheetDocument doc = (XSpreadsheetDocument)loader.loadComponentFromURL("file:///c:/a.ods", "_blank", 0, args1);
XSpreadsheetDocument doc = (XSpreadsheetDocument)loader.loadComponentFromURL("file:///" + fileName, "_blank", 0, args1);
XSpreadsheets sheets = doc.getSheets();
//XSpreadsheet sheet = (XSpreadsheet)sheets.getByName("Sheet1").Value;
XSpreadsheet sheet = (XSpreadsheet)sheets.getByName("工作表1").Value;

XCell cell = sheet.getCellByPosition(2, 2);
label2.Text = cell.ToString();

cell.setValue(1000);
cell = sheet.getCellByPosition(2, 3);
cell.setFormula("Title");

XStorable xstorable = (XStorable)doc;
PropertyValue[] storeProps = new PropertyValue[1];
storeProps[0] = new PropertyValue();
storeProps[0].Name = "Overwrite";
storeProps[0].Value = new uno.Any((Boolean)true);

try
{
String sURL = "file:///c:/a2.ods";
xstorable.storeAsURL(sURL, storeProps);
if (doc != null)
{
XCloseable xCloseable = (XCloseable)doc;
xCloseable.close(true);
doc = null;
}
}
catch (unoidl.com.sun.star.uno.Exception ex)
{
MessageBox.Show(ex.Message);
}

//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 (unoidl.com.sun.star.uno.Exception ex)
{
MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
}

(下圖)執行後發生錯誤，Apache OpenOffice 4.1.3 SDK 疑似支援 .NET 2，但不支援 .NET 4.x

*****

用 .NET 2.0 改寫一次

WinForm 程式 Form1.cs 片段

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;

// 下載安裝 OpenOffice 4.1.3 SDK
// "參考" 加入 C:\Program Files(x86)\OpenOffice 4\sdk\cli 目錄的
// cli_basetypes, cli_cppuhelper, cli_oootypes, cli_ure, and cli_uretypes.dll
using unoidl.com.sun.star.uno;
using unoidl.com.sun.star.lang;
using unoidl.com.sun.star.frame;
using unoidl.com.sun.star.beans;
using unoidl.com.sun.star.sheet;
using unoidl.com.sun.star.container;
using unoidl.com.sun.star.table;
using unoidl.com.sun.star.text;
using uno.util;

using unoidl.com.sun.star.util;
//using unoidl.com.sun.star.sdbc;

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

private string fileName = "";
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; // 包含路徑

fileName = openFileDialog1.FileName; // 包含路徑

//XComponentContext context = Bootstrap.bootstrap();
XComponentContext oStrap = uno.util.Bootstrap.bootstrap();
XMultiServiceFactory oServMan = (XMultiServiceFactory)oStrap.getServiceManager();
XComponentLoader loader = (XComponentLoader)oServMan.createInstance("com.sun.star.frame.Desktop");
string url = @"private:factory/scalc";
PropertyValue[] loadProps = new PropertyValue[1];
loadProps[0] = new PropertyValue();
loadProps[0].Name = "Hidden";
loadProps[0].Value = new uno.Any(true);
//PropertyValue[] loadProps = new PropertyValue[0];
XComponent document = loader.loadComponentFromURL(url, "_blank", 0, loadProps);

PropertyValue[] args1 = new PropertyValue[1];
args1[0] = new PropertyValue();
args1[0].Name = "Hidden";
args1[0].Value = new uno.Any((Boolean)true);

//XSpreadsheetDocument doc = (XSpreadsheetDocument)loader.loadComponentFromURL("file:///c:/a.ods", "_blank", 0, args1);
XSpreadsheetDocument doc = (XSpreadsheetDocument)loader.loadComponentFromURL("file:///" + fileName, "_blank", 0, args1);

XSpreadsheets sheets = doc.getSheets();
//XSpreadsheet sheet = (XSpreadsheet)sheets.getByName("Sheet1").Value;
XSpreadsheet sheet = (XSpreadsheet)sheets.getByName("工作表1").Value;

XCell cell = sheet.getCellByPosition(2, 2);
label1.Text = cell.ToString();

cell.setValue(1000);
cell = sheet.getCellByPosition(2, 3);
cell.setFormula("Title");

XStorable xstorable = (XStorable)doc;
PropertyValue[] storeProps = new PropertyValue[1];
storeProps[0] = new PropertyValue();
storeProps[0].Name = "Overwrite";
storeProps[0].Value = new uno.Any((Boolean)true);

try
{
String sURL = "file:///c:/a2.ods";
xstorable.storeAsURL(sURL, storeProps);
if (doc != null)
{
XCloseable xCloseable = (XCloseable)doc;
xCloseable.close(true);
doc = null;
}
}
catch (unoidl.com.sun.star.uno.Exception ex)
{
MessageBox.Show(ex.Message);
}

//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 (System.Exception ex)
{
MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
}
}
}

(下圖) 編譯有警告，似乎只支援 x86，不支援 x64

OpenOffice 4.1.3 SDK 安裝目錄為 C:\Program Files(x86)\OpenOffice 4\sdk\ 也可知道

(下圖) 實際執行也不行

以下是雜七雜八參考資料

**********

http://jehupc.exblog.jp/12764408/

XComponentContext context = Bootstrap.bootstrap();

XMultiServiceFactory factory = (XMultiServiceFactory)context.getServiceManager();

XComponentLoader loader = (XComponentLoader)factory.createInstance("com.sun.star.frame.Desktop");

PropertyValue[] args1 = new PropertyValue[1];

args1[0] = new PropertyValue();

args1[0].Name = "Hidden";

args1[0].Value = new uno.Any((Boolean)true);

XSpreadsheetDocument doc = (XSpreadsheetDocument)loader.loadComponentFromURL("file:///c:/a.ods", "_blank", 0, args1);

XSpreadsheets sheets = doc.getSheets();

XSpreadsheet sheet = (XSpreadsheet)sheets.getByName("Sheet1").Value;

XCell cell = sheet.getCellByPosition(2, 2);

cell.setValue(1000);

cell = sheet.getCellByPosition(2, 3);

cell.setFormula("Title");

XStorable xstorable = (XStorable)doc;

PropertyValue[] storeProps = new PropertyValue[1];

storeProps[0] = new PropertyValue();

storeProps[0].Name = "Overwrite";

storeProps[0].Value = new uno.Any((Boolean)true);

try

{

String sURL = "file:///c:/a2.ods" ;

xstorable.storeAsURL(sURL, storeProps);

if (doc != null)

{

XCloseable xCloseable = (XCloseable)doc;

xCloseable.close(true);

doc = null;

}

catch (unoidl.com.sun.star.uno.Exception ex)

{

MessageBox.Show(ex.Message);

}

**********

https://wiki.openoffice.org/wiki/Documentation/DevGuide/ProUNO/CLI/Writing_Client_Programs

**********

Use OpenOffice Uno CLI with C# to create a spreadsheet

http://stackoverflow.com/questions/4743738/use-openoffice-uno-cli-with-c-sharp-to-create-a-spreadsheet

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using unoidl.com.sun.star.uno;

using unoidl.com.sun.star.lang;

using unoidl.com.sun.star.frame;

using unoidl.com.sun.star.beans;

using unoidl.com.sun.star.sheet;

using unoidl.com.sun.star.container;

using unoidl.com.sun.star.table;

using unoidl.com.sun.star.text;

namespace TimeScanner {

class ReportGenerator {

private const string fileName =

@"file:///C:/Documents and Settings/My Documents/Hours Report.ods";

//Concrete Methods

internal XComponent openCalcSheet() {

XComponentContext oStrap = uno.util.Bootstrap.bootstrap();

XMultiServiceFactory oServMan = (XMultiServiceFactory)oStrap.getServiceManager();

XComponentLoader desktop = (XComponentLoader)oServMan.createInstance("com.sun.star.frame.Desktop");

string url = @"private:factory/scalc";

PropertyValue[] loadProps = new PropertyValue[1];

loadProps[0] = new PropertyValue();

loadProps[0].Name = "Hidden";

loadProps[0].Value = new uno.Any(true);

//PropertyValue[] loadProps = new PropertyValue[0];

XComponent document = desktop.loadComponentFromURL(url, "_blank", 0, loadProps);

return document;

}

public void writeToSheet(XComponent document) {

XSpreadsheets oSheets = ((XSpreadsheetDocument)document).getSheets();

XIndexAccess oSheetsIA = (XIndexAccess) oSheets;

XSpreadsheet sheet = (XSpreadsheet) oSheetsIA.getByIndex(0).Value;

XCell cell = sheet.getCellByPosition( 0, 0 ); //A1

((XText)cell).setString("Cost");

cell = sheet.getCellByPosition( 1, 0 ); //B1

cell.setValue(200);

cell = sheet.getCellByPosition( 1, 2 ); //B3

cell.setFormula("=B1 * 1.175");

}

public void saveCalcSheet(XComponent oDoc) {

PropertyValue[] propVals = new PropertyValue[1];

propVals[0] = new PropertyValue();

propVals[0].Name = "FilterName";

propVals[0].Value = new uno.Any("calc8");

((XStorable)oDoc).storeToURL(fileName, propVals);

}

Calc/API/Programming

https://wiki.openoffice.org/wiki/Calc/API/Programming

很多範例，但為 C++

How to read ODS Open Office Calc spreadsheet document using Bytescout Spreadsheet for .NET

https://bytescout.com/products/developer/spreadsheetsdk/bytescoutxls_read_ods_open_office_calc_spreadsheet.html

**********

http://www.it1352.com/387358.html

Tutorial for implementing OpenOffice in C++/C#/Java

https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=60522

Read ODS (Open Office Calc) Files

https://s3.amazonaws.com/bytescout.com/files/help/BytescoutSpreadsheetSDK/html/71015e6a-b760-44c5-b53a-0b9c15f7d366.htm

(完)

相關

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

浮雲雅築

2016年12月30日星期五

[研究] [C#] 使用 OpenOffice 4.1.3 SDK 讀取試算表(Calc) 檔案 (.ods)

沒有留言:

張貼留言

2016年12月30日 星期五

[研究] [C#] 使用 OpenOffice 4.1.3 SDK 讀取試算表(Calc) 檔案 (.ods)

沒有留言:

張貼留言

2016年12月30日星期五