Using OpenOffice / LibreOffice remotely through C# .net

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.