The goal of this exercise is to drive OpenOffice or LibreOffice through a C# application and to use a hidden spreadsheet as a back-end. Please Note: the following snippet/application is only a proof of concept. Information about OpenOffice automation is not all that easy to get. A good starting point though is located at OpenOffice.org: http://api.openoffice.org/docs/common/ref/com/sun/star/module-ix.html
This post is aimed at the intermediate programmer, who knows their way around the Visual Studio. If you are looking for some hints and pointers about OpenOffice or LibreOffice automation, I hope that you find some here.
I'll present three parts:
1) a base class responsible for opening, hiding or showing, and closing Open Office itself,
2) a spreadsheet class derived from that base class, for some spreadsheet specific tasks and
3) ideas for a simple wrapper application using those classes and some more hints about manipulating a spreadsheet.
This should send you on your way for your own experiments and designs.
Also please note: Since this is only a concept, I have omitted all error checking. I leave that to you, have fun. This post is about the mechanics, not a polished (and presentable) application! Look at this as my collection of notes.
I hope that the naming and commenting of this code explains the code. Now, let's get to it.
The Base Class
First task for the base class is to open or connect to an instance of OpenOffice and to show or hide it. I show or hide it with the parameter "show" after connection to an OpenOffice instance. I also provide a component loader, and of course a way of closing OpenOffice.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using unoidl.com.sun.star.lang;
using unoidl.com.sun.star.uno;
using unoidl.com.sun.star.frame;
using System.Diagnostics;
using System.Runtime.InteropServices;
/** This is a base class for OpenOffice.
* Class OfficeBase opens or connects to office
*
* Argument
* param name="show" to show or hide openoffice; true = show, false = hide
*
* NOTE: At this point only a concept. No error checking.
*/
public class OfficeBase
{
// show Open office open-window if true
public const bool showBootStrap = true;
// SW_SHOWMAXIMIZED to maximize the window
private const int SW_SHOWNORMAL = 1;
// SW_SHOWMINIMIZED to minimize the window
private const int SW_SHOWMINIMIZED = 2;
// SW_SHOWNORMAL to make the window be normal size
private const int SW_SHOWMAXIMIZED = 3;
private XComponentContext HnComponentContext;
private XMultiServiceFactory HnMultiServiceFactory;
private XComponentLoader HnComponentLoader;
[DllImport("user32.dll")]
public static extern bool ShowWindowAsync(IntPtr hWnd, int cmdShow);
// property read only
public XComponentLoader ComponentLoader
{
get
{
return HnComponentLoader;
}
}
/// <summary>
/// start OpenOffice hidden or visible, initialise HnComponentLoader
/// </summary>
/// <param name="show"></param>
public OfficeBase(bool show)
{
HnMultiServiceFactory = connect();
// hide app?
if (!show)
{
// retrieve LibreOffice process
Process[] processes = Process.GetProcessesByName("soffice.bin");
// minimize soffice
foreach (Process p in processes)
{
// get mainwindow handle
IntPtr pFoundWindow = p.MainWindowHandle;
// Use Handle to minimize
if (!pFoundWindow.Equals(IntPtr.Zero))
{
ShowWindowAsync(pFoundWindow, SW_SHOWMINIMIZED);
}
}
}
// get a component loader
HnComponentLoader = (XComponentLoader)HnMultiServiceFactory.createInstance("com.sun.star.frame.Desktop");
}
/// <summary>
/// Close main Open office app
/// </summary>
public void OfficeBaseStop()
{
//close xdesktop
XDesktop xDesk = (XDesktop)HnComponentLoader;
xDesk.terminate();
}
// Connect to a running office that is accepting connections.
// Return: The ServiceManager to instantiate office components.
private XMultiServiceFactory connect()
{
HnComponentContext = uno.util.Bootstrap.bootstrap();
return (XMultiServiceFactory)HnComponentContext.getServiceManager();
}
}
Next up is the spreadsheet class, derived from the base class. Main purpose is to provide a spreadsheet.using unoidl.com.sun.star.lang;
using unoidl.com.sun.star.sheet;
using unoidl.com.sun.star.beans;
/** This is a helper class for spreadsheet operations.
* Base class myOfficeBase connects to office
* Child class creates a spreadsheet document.
*
* Argument
* strFileName : Complete path and filename with extension
* If argument strFileName is empty, new empty Spreadsheet is created.
*
* NOTE: At this point only a concept. No error checking.
*/
public class SheetHelperClass : OfficeBase
{
// show SpreadSheet if true
public const bool showSpreadSheet = true;
private XSpreadsheetDocument SpreadSheetDoc;
/// <summary>
/// strFileName: Complete path and Filename with extension.
/// If strFileName is empty, new empty spreadsheet document is created
/// </summary>
/// <param name="FileName"></param>
/// <param name="showBoot"></param>
/// <param name="showSpreadSheet"></param>
public SheetHelperClass(string FileName, bool showBoot, bool spreadSheetVisible)
: base(showBoot)
{
// Create a new spreadsheet document
SpreadSheetDoc = GetSpreadSheetDocument(FileName, spreadSheetVisible);
}
// Creates a spreadsheet document.
// strFileName: Complete path and Filename with extension.
// If strFileName is empty, new empty Spreadsheet is created
// Return: The XSpreadsheetDocument interface of the document.
private unoidl.com.sun.star.sheet.XSpreadsheetDocument GetSpreadSheetDocument(string SpreadSheetFileName, bool makeVisible)
{
// If filename is empty create a new sheet.
string SpreadSheetUrl;
if (string.IsNullOrEmpty(SpreadSheetFileName))
SpreadSheetUrl = "private:factory/scalc";
else
SpreadSheetUrl = SpreadSheetFileName;
// get a property
PropertyValue SpreadSheetProperty = new unoidl.com.sun.star.beans.PropertyValue();
// use property to have spreadsheet hidden or shown
SpreadSheetProperty.Name = "Hidden";
// false to see spreadsheet, true to hide if
SpreadSheetProperty.Value = new uno.Any(!makeVisible);
// make that an array
PropertyValue[] SpreadSheetPropertyValue = new unoidl.com.sun.star.beans.PropertyValue[1];
SpreadSheetPropertyValue[0] = SpreadSheetProperty;
// load sheet
XComponent spreadSheetComponent = ComponentLoader.loadComponentFromURL(
SpreadSheetUrl, "_blank", 0,
SpreadSheetPropertyValue);
// return Spreadsheet Document
return (unoidl.com.sun.star.sheet.XSpreadsheetDocument)spreadSheetComponent;
}
/// <summary>
/// Returns the spreadsheet with the specified index (0-based).
/// </summary>
/// <param name="nIndex">The index of the sheet.</param>
/// <returns>spreadsheet with the specified index (0-based).</returns>
public unoidl.com.sun.star.sheet.XSpreadsheet getSpreadsheet(int nIndex)
{
// Collection of sheets
unoidl.com.sun.star.sheet.XSpreadsheets xSheets =
SpreadSheetDoc.getSheets();
unoidl.com.sun.star.container.XIndexAccess xSheetsIA =
(unoidl.com.sun.star.container.XIndexAccess)xSheets;
unoidl.com.sun.star.sheet.XSpreadsheet xSheet =
(unoidl.com.sun.star.sheet.XSpreadsheet)
xSheetsIA.getByIndex(nIndex).Value;
return xSheet;
}
/// <summary>
/// Close Spreadsheet object and remove xDesktop
/// </summary>
public void Dispose()
{
//close xcomponent
unoidl.com.sun.star.util.XCloseable sheetDocToClose = (unoidl.com.sun.star.util.XCloseable)SpreadSheetDoc;
sheetDocToClose.close(false);
//terminate openOffice
OfficeBaseStop();
}
}
Just put these classes into your project and don't forget the right namespace:namespace YourLibreOfficeSpreadSheetNameSpace
{
All that's left to do is building a user form to use these classes. Following are some ideas to do so. You will notice a lot of hard wired parts. That is to just have a quick playground... public partial class frmMain : Form
{
public SheetHelperClass SpreadSheetHelper;
public unoidl.com.sun.star.sheet.XSpreadsheet WorkingSpreadSheet;
And some experimenting:
private void btnIncreaseValue_Click(object sender, EventArgs e)
{
// define a cell
unoidl.com.sun.star.table.XCell xCell = WorkingSpreadSheet.getCellByPosition(1, 0);
// Set cell value. Increase by one
xCell.setValue(xCell.getValue() + 1);
}
private void btnOpenExistingSpreadSheet_Click(object sender, EventArgs e)
{
// Create Spreadsheet object, hide everything
SpreadSheetHelper =
new SheetHelperClass("file:///Z:\\H\\CSharp\\Sample\\SampleSheet.ods", !SheetHelperClass.showBootStrap, !SheetHelperClass.showSpreadSheet);
// get Spreadsheet Sheet 1, index 0-based
WorkingSpreadSheet = SpreadSheetHelper.getSpreadsheet(0);
// populate TextBox on unser interface with formula
// define a cell
unoidl.com.sun.star.table.XCell xCell = null;
// get cell
xCell = WorkingSpreadSheet.getCellRangeByName("C1").getCellByPosition(0, 0);
// display cell formula from sheet in textbox
tbFormula.Text = xCell.getFormula().ToString();
}
private void btnCloseSpreadSheet_Click(object sender, EventArgs e)
{
SpreadSheetHelper.Dispose();
tbFormula.Text = "Closed";
}
private void btnSetValue_Click(object sender, EventArgs e)
{
// define a cell
unoidl.com.sun.star.table.XCell xCell = null;
//get a cell
xCell = WorkingSpreadSheet.getCellByPosition(1, 0);
// Set cell value.
xCell.setValue(1234);
}
private void btnGetValue_Click(object sender, EventArgs e)
{
// define a cell
unoidl.com.sun.star.table.XCell xCell = null;
//get calculated cell
xCell = WorkingSpreadSheet.getCellByPosition(2, 0);
// Show cell value.
MessageBox.Show(xCell.getValue().ToString());
}
private void btnSetFormula_Click(object sender, EventArgs e)
{
// define a cell
unoidl.com.sun.star.table.XCell xCell = null;
//get calculated cell
xCell = WorkingSpreadSheet.getCellRangeByName("C1").getCellByPosition(0, 0);
if (!string.IsNullOrEmpty(tbFormula.Text.ToString()))
xCell.setFormula(tbFormula.Text.ToString());
else
MessageBox.Show("Input null or empty");
}
Layout of loaded sample spread sheet: Cell position(2,0), or C1, is the result cell with an initial formula of =B1*10+B2. Cell B2 is a constant factor and B1 an input set by btnSetValue_Click.This is written for Windows XP SP3, LibreOffice 3.4 using Visual C# 2010 Express. I hope you found some answers to your questions, and have fun with further experiments.
