Replacing Power Automate With OpenOffice

Power Automate stopped working, well shit.

Ok, for some context, a long time ago I made a very scuffed system for automating receipts for my fathers bike shop. The tech stack was very bad, like, I should burn it bad. But it has been working up until now. The full stack looked like so:

• Unity based client that has the “UI” parts for writing receipts using simple buttons for selecting products and entering customer information.
• NodeJS backend server that listens for new print requests
• Power Automate workflow that takes the print request from the NodeJS server and inputs that into excel
• Excel macro that does the actual printing that Power Automate calls after the workflow has put all the data in.

So, what happened? Well, a colleague bumped into the USB cable connecting the laptop hosting the NodeJS server and the printer. Not bad, but that broke the Power Automate workflow and caused it to stop. Still not bad, simple fix, just start the workflow again.
So I go connect the laptop to the internet, login with the Microsoft account¹ used for the scripts and… They are not there?
Ok, I have copies of the scripts, I can just reupload them, right? Home again, I open Power Automate on my computer and… Why are they there?
I am beyond annoyed at this point. Going back to the shop, I try to update Power Automate, shit doesn’t work. Even though there is an internet connection the installer doesn’t pull any data, Task Manager shows no network activity beyond Windows Telemetry. Ok, let’s try updating through the Microsoft store. Update?, oh no… Well it also tries to update and it doesn’t do anything. Sigh

My only options at this point are re-installing windows / trying windows update or switching to a different automation tool.

I chose the later.

woe be uppon OpenOffice

I knew of office suites that aren’t Microsoft, but I never really considered them before, as I have a subscription to office via the family plan. I knew of OpenOffice because it was installed on the school computers I used to use (subtle foreshadowing). It also appears to have an API I can use, so I went with that.

For the server I am gonna use ASP .NET with a minimal API.

One dotnet new later and I need to find a way to speak UNO. What’s UNO?
UNO is a badly named² component model used by OpenOffice. It is a way to interact with the office suite from other programming languages. Oh, also to note, there is an existing C# library that is ALSO called UNO, but it is something completely different and has nothing to do with OpenOffice, love it.

First hurdle: The assemblies are not loading.

Unhandled exception. System.IO.FileNotFoundException: Could not load file or assembly 'cli_cppuhelper, Version=1.0.23.0, Culture=neutral, PublicKeyToken=ce2cb7e279207b9e'. The system cannot find the file specified.
File name: 'cli_cppuhelper, Version=1.0.23.0, Culture=neutral, PublicKeyToken=ce2cb7e279207b9e'
   at Program.<Main>$(String[] args)

After some intense searching I found out that the assemblies are made for .NET Framework 2. Oh no.

Second hurdle: .NET Framework 2 is ancient. I don’t have any experience doing Web Development with .NET Framework 2, or if I even can, so I made a second project, a console app using .NET Framework 2. Even though I was trying to avoid a scuffed tech stack, there is no way around this.

Unhandled Exception: System.BadImageFormatException: Could not load file or assembly 'cli_cppuhelper, Version=1.0.23.0, Culture=neutral, PublicKeyToken=ce2cb7e279207b9e' or one of its dependencies. An attempt was made to load a program with an incorrect format.
File name: 'cli_cppuhelper, Version=1.0.23.0, Culture=neutral, PublicKeyToken=ce2cb7e279207b9e'
   at Gourmand.Interop.Program.Main(String[] args)

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

Uhh, what? Ok, so it seems I am trying to load a 32 bit assembly in a 64 bit process. I change the project settings to x86 and it works!

First, let’s bootstrap our UNO runtime.

private static XComponentContext _componentContext = null;
private static XMultiServiceFactory _msFactory = null;
private static XComponentLoader _componentLoader = null;

private static XSpreadsheetDocument _spreadsheetDocument = null;
private static XSpreadsheet _spreadsheet = null;

public static void StartOpenOfficeLoader()
{
    _componentContext = uno.util.Bootstrap.bootstrap();
    _msFactory =
        (XMultiServiceFactory)_componentContext.getServiceManager();
    _componentLoader = (XComponentLoader)_msFactory.createInstance
        ("com.sun.star.frame.Desktop");
}

public static void CreateWorkbook()
{
    var xComponent =
        _componentLoader.loadComponentFromURL
            ("private:factory/scalc", "_blank", 0, []);

    _spreadsheetDocument = (XSpreadsheetDocument)xComponent;
    var xSheets = _spreadsheetDocument.getSheets();
    var xIndexAccess = (XIndexAccess)xSheets;
    var any = xIndexAccess.getByIndex(0);
    _spreadsheet = (XSpreadsheet)any.Value;
}

Now, we can create a new workbook. Next step is to add data to it.

private static XCell GetCellByName(string cellName)
{
    var cellRange = _spreadsheet.getCellRangeByName(cellName);
    return cellRange.getCellByPosition(nColumn: 0, nRow: 0);
}

This method allows us to get a cell by its name, like “A1”. From there the XCell interface has methods for setting values and text.

One step that is still missing is that I need to clear the previous data. The contact information is simple as I am just overwriting the same cells, but the products are a bit more tricky. I need to clear all the rows in the products table before adding new ones. The getCellRangeByName method (as expeceted) also works for ranges. It returns an XCellRange interface. That interface on it’s own doesn’t have any methods for getting an array of cells. What you can do is to get the `XColumnRowRange´ interface from it. This interface has methods for getting row counts, which can then be simply be iterated over to get each cell in the range.

private static XCell[] GetCells(string range)
{
    var cellRange = _spreadsheet.getCellRangeByName(range);
    var columnRowRange = (XColumnRowRange)cellRange;
    var cells = new List<XCell>();

    for (var i = 0; i < columnRowRange.getColumns().getCount(); i++)
    {
        for (var j = 0; j < columnRowRange.getRows().getCount(); j++)
        {
            cells.Add(cellRange.getCellByPosition(i, j));
        }
    }

    return cells.ToArray();
}

From there it is just a matter of iterating over the cells and setting their values to empty strings.

Final hurdle: Printing

Printing in theory should be simple. Following the docs, all we need to do is get the XPrintable interface from the document and then call the print method. Simple, right?

_printable.print([]);

Yeah it is actually that simple. Without any parameters it just prints to the default printer with default settings.

So why was this a hurdle? The guide I was reading was using statics like Print.usePrinter and calls like XPrintable xp = Lo.qi(XPrintable.class, doc); which threw me off. I had no static Print object, nor do I know what Lo.qi is. I still don’t know what that is, but I found out that XPrintable can be directly casted from the document object.
Reading helps.

closing thoughts

This wasn’t the worst experience I’ve had, but it was definitely up there. The examples all being in Java and having to use .NET Framework 2 were the main pain points. The UNO API is confusing to use sometimes, but the documentation wasn’t all too bad. I am just happy to have a working solution again.

Really, as with any project, I am more annoyed that it took so long to figure it out. Reading Java examples and trying to translate them to C# was a pain.

0/10, would not recommend trying to use UNO.

Honestly the worst part was that I learnt from a friend during this whole ordeal was that appearently Microsoft has existing automation support³ for Excel through COM interop which I should have just used in the first place. This in theory would have worked the same way as the OpenOffice solution, but would require a licensed copy of Excel⁴.

In the same vein, I also learnt that LibreOffice is the more maintained version of OpenOffice, so possibly switching to that in the future might be a good idea. The UNO API is basically the same from what I can tell so switching should be easy.