Use OpenOffice Uno CLI with C# to create a spreadsheet

Question

I have so far found a couple of sources that discuss creation of ODS files: How to create ODS documents in .Net and How to create .odt files with C#.NET?

And most interestingly an explanation for opening calc files. However this opens OpenOffice in fullscreen, what im looking for is some way to write to a Calc file (.ods) without actually opening Openoffice. So that I can write a function that just opens a savefiledialog, gets the filename and then creates and saves the .ods file.

Are there is any C# code examples available to do such a thing?

Answer 1

So I have finally resolved this issue and want to save others the hazel of going through this again. Basic points of HEADACE for me were:

1. Use forward slashes instead of backward slashes (e.g. its C:/ not C:\ )
2. The Filtername used should be set to the engine used to save the document. Possible values include writer8, calc8, MS Excel 97, so for spreadsheets you obviously need to use calc8
3. If you dont want that OpenOffice pops up in the forground and wait for it to get filled with your data, then use the PropertyValue and set Hidden to true.

Happy coding and dont forget to install the OpenOffice SDK to be able to add the unoidl references:

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);
        }
    }
}