ejs

Here,’s the story short:

I need to create a series of graph for highly scientific data sets. The data is calculated in MatLAB, but the export of figures in MatLAB is a pain for me.

On the other hand OpenOffice.org seems to be the right tool for the job. The latest MatLAB has the function, called ‘xlswrite’. Happy with the fact, hoping it should write the XLS file I fire it up and…

Ouch. One must have Excell installed, as the data export goes through the ActiveX. If no Excell is found, the data is exported into CSV file, overwriting the file every time. Not the Right Thing.

The job was done via ordinary ‘csvwrite’, but adding data, importing the file to OpenOffice.org Calc and producing a series of graph.

And the milestone is creating ‘odswrite’, implementing the same functionality as ‘xlswrite’ presents. As MatLAB supports .NET and OpenOffice.org can be controlled via .NET-UNO bridge, .NET implementation of the function seems to be the easiest task.

The first problem found is it’s impossible to load ‘cli_cpputype’ NET assembly on x64 systems. I’m not sure if it is because ‘cli_cpputype’ is compiled for .NET 2.0, but the problem is too seriuos.

A small .NET program, based onCreating an OpenOffice Writer Document with C# and Creating an OpenOffice Calc Document with C# was created. In the case of success, it writes 2 files to user’s desktop:  “OpenOfficeTestFile.odt” for Writer and “OpenOfficeTestFile.ods” for Calc.

Here’s the source for C#. Sorry fot the mess, it’s my first .NET and C# programm ever compiled 😉

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;

// OOorg assemblies
using unoidl.com.sun.star.lang; 
using unoidl.com.sun.star.uno; 
using unoidl.com.sun.star.bridge; 
using unoidl.com.sun.star.frame;
using unoidl.com.sun.star.text;
using unoidl.com.sun.star.beans;
using unoidl.com.sun.star.sheet;
using unoidl.com.sun.star.container;
using unoidl.com.sun.star.table;

namespace OONETTest
{
 class OpenOfficeTestApp 
 {
 //Define a file name. Change this to an existing path! 
private static string FileName      = @"OpenOfficeTestFile";  

 static void Main(string[] args)
 {
 string fDesktop = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
 string fFileName = fDesktop + "\\" + FileName;

//Call the bootstrap method to get a new ComponentContext 
 //object. If OpenOffice isn't already started this will 
 //start it and then return the ComponentContext.  

 unoidl.com.sun.star.uno.XComponentContext localContext =  uno.util.Bootstrap.bootstrap(); 

 //Get a new service manager of the MultiServiceFactory type 
 //we need this to get a desktop object and create new CLI 
 //objects.  

 unoidl.com.sun.star.lang.XMultiServiceFactory multiServiceFactory =  (unoidl.com.sun.star.lang.XMultiServiceFactory) localContext.getServiceManager(); 

 //Create a new Desktop instance using our service manager 
 //Notice: We cast our desktop object to XComponent loader 
 //so that we could load or create new documents.  

 XComponentLoader componentLoader = (XComponentLoader) multiServiceFactory.createInstance("com.sun.star.frame.Desktop" ); 

 //Create a new blank writer document using our component 
 //loader object. 
 XComponent oDoc = componentLoader.loadComponentFromURL( "private:factory/swriter",  //a blank writer document 
 "_blank", 0,       //into a blank frame use no searchflag 
 //use no additional arguments. 
 new unoidl.com.sun.star.beans.PropertyValue[0] 
 ); 

 //Cast our component to a the XText interface 
//and write some simple text into document.  

 ((unoidl.com.sun.star.text.XTextDocument)oDoc).getText().setString("Testing OpenOffice.org .NET interface"); 

 //After we insert our text, we cast our component to XStorable 
 //to save it onto the harddisk  

 ((XStorable)oDoc).storeToURL( //Convert the file path into a OpenOffice path 
 PathConverter(fFileName + ".odt"),
 new unoidl.com.sun.star.beans.PropertyValue[0]);            //no additional arguments  

 // close the Writer
 oDoc.dispose();

 // according to
 //http://www.suite101.com/content/creating-an-openoffice-calc-document-with-c-a124112

 //Create a new blank writer document using our component 
 //loader object. 
 XComponent oCalc = componentLoader.loadComponentFromURL("private:factory/scalc",  //a blank Calc document 
 "_blank", 0,       //into a blank frame use no searchflag 
 //use no additional arguments. 
 new unoidl.com.sun.star.beans.PropertyValue[0]
 );

 // get sheets
 XSpreadsheets oSheets = ((XSpreadsheetDocument)oCalc).getSheets();
 XIndexAccess oSheetsIA = (XIndexAccess) oSheets;
 // access sheet by index
 XSpreadsheet oSheet = (XSpreadsheet) oSheetsIA.getByIndex(0).Value;

//write some data:
//Each cell in a Calc sheet is indexed by its column and row, so that text (for example) is written to cell A1 by using:

 XCell oCell = oSheet.getCellByPosition( 0, 0 ); //A1
 ((XText)oCell).setString("Testing OpenOffice.org .NET interface");

oCell = oSheet.getCellByPosition( 0, 1 ); //A2
 ((XText)oCell).setString("Cost");

 //And a number can be written to B1 by using:
 oCell = oSheet.getCellByPosition( 1, 1 ); //B2
 oCell.setValue(200);

 //And the "setFormula" methods is, of course, used to enter formulae (in this example into B3):

 oCell = oSheet.getCellByPosition( 1, 2 ); //B3
 oCell.setFormula("=B2 * 1.175");

 //After we insert our text, we cast our component to XStorable 
 //to save it onto the harddisk  

 ((XStorable)oCalc).storeToURL( //Convert the file path into a OpenOffice path 
 PathConverter(fFileName + ".ods"),
 new unoidl.com.sun.star.beans.PropertyValue[0]);            //no additional arguments  

 // close Calc
 oCalc.dispose();

 MessageBox.Show("OpenOffice document '" + fFileName + ".odt' saved, \n" + "OpenOffice document '" + fFileName + ".ods' saved", "OpenOffice .NET Test App");

 } 

 /// 
 /// Convert file path  into OO file format 
 /// 
 ///

 private static string PathConverter( string file) 
 {
 try 
 {
 file = file.Replace(@"\", "/");
 return "file:///"+file; 
 }
 catch (System.Exception ex) 
 {
 throw ex; 
 }
 }
 }
}