View source for LibreOffice Calc NumToWords Extension Complete Guide

= LibreOffice Calc NumToWords Extension - Complete Solution & Troubleshooting Guide =

== Overview ==

A LibreOffice Calc add-in that converts numbers to their English word representation using the custom `=NUMTOWORDS()` spreadsheet function. This page documents the '''working solution''' and the '''complete debugging journey''' including all 15+ issues encountered and resolved.

=== Quick Start - Working Solution ===

The extension is available in two versions:
* '''Working Python Version:''' `libreoffice-calc-numbers-to-words/python/NumToWordsPy.oxt`
* '''Experimental Java Version:''' `libreoffice-calc-numbers-to-words/java/dist/NumToWords.oxt` (for reference)

=== Installation ===

==== Option 1 - GUI (Recommended) ====

1. Download `NumToWordsPy.oxt` from the repository
2. Open LibreOffice Calc
3. Go to '''Tools → Extension Manager'''
4. Click '''Add''' and select `NumToWordsPy.oxt`
5. '''Restart LibreOffice Calc completely'''
6. Test with: `=NUMTOWORDS(123)`

==== Option 2 - Command Line ====

<syntaxhighlight lang="bash">
# Download the extension
curl -LO https://github.com/xunema/libreoffice-calc-numbers-to-words/releases/download/v2.0.0/NumToWordsPy.oxt

# Close LibreOffice first!
pkill soffice

# Clear cache (important!)
rm -rf ~/.config/libreoffice/4/user/uno_packages/cache/
rm -rf ~/.config/libreoffice/4/user/extensions/

# Install
unopkg add NumToWordsPy.oxt

# Verify
unopkg list | grep numtowords
</syntaxhighlight>

=== Usage ===

<syntaxhighlight>
=NUMTOWORDS(number)
=NUMTOWORDS(number, formatStyle)
</syntaxhighlight>

{| class="wikitable"
|+ Format Styles
! formatStyle !! Mode !! Example Output
|-
| 0 (default) || Cardinal || one hundred and twenty-three
|-
| 1 || Ordinal || forty-second
|-
| 2 || Currency || ninety-nine dollars and ninety-nine cents
|}

=== Examples ===

<syntaxhighlight>
=NUMTOWORDS(123)            → one hundred and twenty-three
=NUMTOWORDS(42, 1)          → forty-second
=NUMTOWORDS(99.99, 2)       → ninety-nine dollars and ninety-nine cents
=NUMTOWORDS(-5)             → minus five
=NUMTOWORDS(1000000)        → one million
=NUMTOWORDS(12.34)          → twelve point three four
</syntaxhighlight>

---

== The Core Technique - Why This Works ==

=== The "Invisible XPropertySet" Pattern ===

This is the '''critical secret''' that makes LibreOffice Calc Add-Ins work:

LibreOffice '''auto-injects''' the calling cell's `XPropertySet` as the '''first invisible argument''' to your Python method. This is '''NOT listed in the XCU file''' and is handled transparently by the UNO bridge.

'''Python Method Signature:'''
<syntaxhighlight lang="python">
def numToWords(self, number, formatStyle=None):
    # Receives: (self, XPropertySet, number, formatStyle)
    # But XPropertySet is INVISIBLE - LibreOffice injects it automatically!
    pass
</syntaxhighlight>

'''XCU Parameters (ONLY list the visible ones):'''
<syntaxhighlight lang="xml">
<node oor:name="Parameters">
  <node oor:name="number" oor:op="replace">...</node>      <!-- Index 0 in XCU -->
  <node oor:name="formatStyle" oor:op="replace">          <!-- Index 1 in XCU -->
    <prop oor:name="IsOptional"><value>true</value></prop>
  </node>
</node>
</syntaxhighlight>

'''Key Points:'''
* The Python method receives 3 arguments: `(self, XPropertySet, number, formatStyle)`
* The XCU only declares 2 parameters: `number` and `formatStyle`
* The `XPropertySet` is injected by LibreOffice '''invisibly'''
* The XCU parameter count must match the '''visible''' parameters only!

=== Architecture Pattern (Following libnumbertext) ===

The working extension follows the '''hybrid Python+Java pattern''' from the proven [https://github.com/Numbertext/libnumbertext libnumbertext] project:

1. '''Python UNO Bridge''' (`numtowords.uno.py`)
   * Handles UNO registration via `unohelper.ImplementationHelper`
   * Provides `g_ImplementationHelper.addImplementation()`
   * No complex factory methods needed

2. '''Plain Python Logic'''
   * Core conversion in pure Python (no Java required!)
   * Simple algorithm, no UNO dependencies in logic

3. '''XCU Registration''' (`CalcAddIns.xcu`)
   * Declares function to Calc's formula engine
   * Uses `IsOptional` flag for optional parameters
   * CompatibilityName matches implementation exactly

4. '''RDB Type Library''' (`NumToWords.rdb`)
   * Compiled IDL interface
   * Tells LibreOffice the exact parameter types
   '''Critical for avoiding Err:504!'''

5. '''Legacy Manifest''' (`META-INF/manifest.xml`)
   * Uses `type=Python` (not modern `.components`)
   * Includes RDB entry for type library

---

== Complete Debugging Chronicle - All 15+ Issues Encountered ==

=== Issue 1: Inner Class $ Notation Problem ===

'''Error:''' `Cannot determine registration class!`

'''Cause:''' Using Java inner class with `$` notation in `.components` file:
<syntaxhighlight lang="xml">
<!-- WRONG -->
<implementation name="com.numbertext.converter.NumToWords$_NumToWords">
</syntaxhighlight>

'''Solution:''' Use top-level class only, no inner classes:
<syntaxhighlight lang="xml">
<!-- CORRECT -->
<implementation name="com.numbertext.converter.NumToWordsImpl">
</syntaxhighlight>

---

=== Issue 2: Disconnected Factory Methods ===

'''Error:''' `Cannot determine registration class!`

'''Cause:''' `__getComponentFactory` and `__writeRegistryServiceInfo` were in `NumToWords.java`, but `.components` pointed to `NumToWordsImpl.java`. LibreOffice looks for these methods via reflection on the class named in the `<implementation>` tag.

'''Solution:''' Move factory methods into the implementation class:
<syntaxhighlight lang="java">
public class NumToWordsImpl extends WeakBase implements ... {
    public static XSingleComponentFactory __getComponentFactory(String sImplName) {
        // Factory logic here
    }
    
    public static boolean __writeRegistryServiceInfo(XRegistryKey regKey) {
        // Registration logic here
    }
}
</syntaxhighlight>

---

=== Issue 3: Java Version Mismatch ===

'''Error:''' `Cannot determine registration class!` (silent failure)

'''Cause:''' Compiling with Java 21 (default in Ubuntu 24.04) but LibreOffice configured to use Java 11 JRE. The `UnsupportedClassVersionError` manifests as generic registration failure.

'''Detection:'''
<syntaxhighlight lang="bash">
javap -verbose NumToWordsImpl.class | grep "major version"
# major version 65 = Java 21 ❌
# major version 55 = Java 11 ✅
</syntaxhighlight>

'''Solution:''' Compile with `--release 11` flag:
<syntaxhighlight lang="bash">
javac --release 11 -d build ...
</syntaxhighlight>

---

=== Issue 4: Corrupted Registry Cache ===

'''Error:''' `Insert duplicate implementation name` or `Cannot determine registration class!`

'''Cause:''' Previous broken installations left zombie entries in `~/.config/libreoffice/4/user/uno_packages/cache/`

'''Solution:''' Aggressive cache clearing:
<syntaxhighlight lang="bash">
pkill soffice
sleep 2
rm -rf ~/.config/libreoffice/4/user/uno_packages/cache/*
rm -rf ~/.config/libreoffice/4/user/extensions/tmp/*
</syntaxhighlight>

---

=== Issue 5: Conflicting Manifest Entries (The "Final Boss") ===

'''Error:''' `Cannot determine registration class!`

'''Cause:''' `META-INF/manifest.xml` contained BOTH:
* Legacy: `application/vnd.sun.star.uno-component;type=Java`
* Modern: `application/vnd.sun.star.uno-components`

LibreOffice processes legacy first, finds no `RegistrationClassName`, aborts before reaching modern entry.

'''Solution:''' Use ONLY the legacy Python approach:
<syntaxhighlight lang="xml">
<manifest:file-entry
    manifest:media-type="application/vnd.sun.star.uno-component;type=Python"
    manifest:full-path="numtowords.uno.py"/>
</syntaxhighlight>

---

=== Issue 6: Missing RDB Type Library ===

'''Error:''' `Err:504` (Error in parameter list)

'''Cause:''' No `.rdb` file included in extension. LibreOffice has no type map, cannot hand cell values to Python.

'''Solution:''' Create IDL and compile to RDB:
<syntaxhighlight lang="bash">
# Create IDL
# Compile
unoidl-write types.rdb offapi.rdb XOcNumToWords.idl oc_numtowords.rdb

# Add to manifest
<manifest:file-entry
    manifest:media-type="application/vnd.sun.star.uno-typelibrary;type=RDB"
    manifest:full-path="oc_numtowords.rdb"/>
</syntaxhighlight>

---

=== Issue 7: Incorrect Python *args Pattern ===

'''Error:''' `Err:504` or `#NAME?`

'''Cause:''' Attempted to use `*args` to handle variable arguments:
<syntaxhighlight lang="python">
# WRONG - LibreOffice doesn't support *args
def ocnumtowords(self, *args):
    ...
</syntaxhighlight>

'''Solution:''' Use explicit parameters matching IDL exactly:
<syntaxhighlight lang="python">
# CORRECT - Explicit signature
def numToWords(self, number, formatStyle=None):
    # XPropertySet is injected invisibly before 'number'
    ...
</syntaxhighlight>

---

=== Issue 8: XCU Parameter Count Mismatch ===

'''Error:''' `Err:504`

'''Cause:''' Listed all 3 parameters in XCU (including invisible XPropertySet):
<syntaxhighlight lang="xml">
<!-- WRONG -->
<node oor:name="Parameters">
  <node oor:name="prop"/>    <!-- Don't list this! -->
  <node oor:name="number"/>
  <node oor:name="formatStyle"/>
</node>
</syntaxhighlight>

'''Solution:''' Only list visible parameters:
<syntaxhighlight lang="xml">
<!-- CORRECT -->
<node oor:name="Parameters">
  <node oor:name="number"/>        <!-- Visible param 1 -->
  <node oor:name="formatStyle"/>  <!-- Visible param 2 -->
</node>
</syntaxhighlight>

---

=== Issue 9: Missing IsOptional Flag ===

'''Error:''' `Err:504` when calling with single argument

'''Cause:''' Optional parameter not marked as optional in XCU:
<syntaxhighlight lang="xml">
<!-- WRONG -->
<node oor:name="formatStyle" oor:op="replace">
  <!-- No IsOptional property -->
</node>
</syntaxhighlight>

'''Solution:''' Add `IsOptional` property:
<syntaxhighlight lang="xml">
<!-- CORRECT -->
<node oor:name="formatStyle" oor:op="replace">
  <prop oor:name="IsOptional" oor:type="xs:boolean">
    <value>true</value>
  </prop>
</node>
</syntaxhighlight>

---

=== Issue 10: Method Name Case Sensitivity ===

'''Error:''' `#NAME?` or function not found

'''Cause:''' XCU node name doesn't match Python method name exactly:
<syntaxhighlight lang="xml">
<!-- WRONG -->
<node oor:name="ocnumtowords">  <!-- lowercase -->
</syntaxhighlight>

<syntaxhighlight lang="python">
# Python method
def ocNumToWords(self, ...):  # camelCase - mismatch!
</syntaxhighlight>

'''Solution:''' Names must match exactly (case-sensitive):
<syntaxhighlight lang="xml">
<!-- CORRECT -->
<node oor:name="numToWords">
</syntaxhighlight>

<syntaxhighlight lang="python">
# Python method
def numToWords(self, ...):  # Exact match!
</syntaxhighlight>

---

=== Issue 11: CompatibilityName Format ===

'''Error:''' Function not appearing in Calc

'''Cause:''' Incorrect `CompatibilityName` format:
<syntaxhighlight lang="xml">
<!-- WRONG -->
<prop oor:name="CompatibilityName">
  <value>AutoAddIn.NumToWords.numToWords</value>
</prop>
</syntaxhighlight>

'''Solution:''' Use simple dotted format:
<syntaxhighlight lang="xml">
<!-- CORRECT -->
<prop oor:name="CompatibilityName">
  <value>com.numbertext.converter.NumToWordsPy.numToWords</value>
</prop>
</syntaxhighlight>

---

=== Issue 12: IDL Parameter Naming (Underscores vs CamelCase) ===

'''Error:''' RDB compilation fails

'''Cause:''' Using underscores in IDL parameter names:
<syntaxhighlight lang="idl">
// WRONG
string numToWords([in] any format_style);  // Underscore not allowed!
</syntaxhighlight>

'''Solution:''' Use camelCase in IDL:
<syntaxhighlight lang="idl">
// CORRECT
string numToWords([in] any formatStyle);
</syntaxhighlight>

---

=== Issue 13: RDB Not Included in OXT ===

'''Error:''' `Err:504` even with correct IDL

'''Cause:''' Build script didn't include `.rdb` in the OXT package:
<syntaxhighlight lang="bash">
# WRONG - Missing rdb/
zip -r extension.oxt oc_reg.uno.py oc_CalcAddIn.xcu META-INF/
</syntaxhighlight>

'''Solution:''' Include RDB directory:
<syntaxhighlight lang="bash">
# CORRECT
zip -r extension.oxt oc_reg.uno.py oc_CalcAddIn.xcu META-INF/ rdb/
</syntaxhighlight>

---

=== Issue 14: Headless Test Environment Limitations ===

'''Error:''' Extension registers but functions return `Err:504`

'''Cause:''' Testing via `unopkg` and headless Python UNO connection doesn't fully initialize the AddIn service. Calc's formula engine needs complete GUI context.

'''Solution:''' '''Must test in real LibreOffice Calc GUI:'''
1. Install via Extension Manager GUI
2. Full restart of LibreOffice
3. Test in actual spreadsheet

The CLI test environment cannot fully validate AddIn functionality!

---

=== Issue 15: Service Name Mismatch ===

'''Error:''' Extension installs but not recognized as AddIn

'''Cause:''' Service name doesn't include `com.sun.star.sheet.AddIn`:
<syntaxhighlight lang="python">
# WRONG
SERVICE_NAME = "com.numbertext.converter"  # Missing AddIn service!
</syntaxhighlight>

'''Solution:''' Must include AddIn service:
<syntaxhighlight lang="python">
# CORRECT
SERVICE_NAME = "com.sun.star.sheet.AddIn"
# Or tuple of services:
("com.numbertext.converter", "com.sun.star.sheet.AddIn")
</syntaxhighlight>

---

== Project Structure ==

=== Working Python Extension ===

<pre>
libreoffice-calc-numbers-to-words/python/
├── NumToWordsPy.oxt              # Ready-to-install OXT
├── numtowords.uno.py            # Python UNO component
├── CalcAddIns.xcu               # Calc function registration
├── NumToWords.rdb               # Compiled UNO type library
├── description.xml              # Extension metadata
└── META-INF/manifest.xml        # OXT manifest (legacy Python type)
</pre>

=== Key Files Explained ===

'''numtowords.uno.py:'''
* Implements `XAddIn`, `XServiceInfo`, `XLocalizable`
* Uses `unohelper.ImplementationHelper` for registration
* Core conversion logic in pure Python
* '''Method receives invisible XPropertySet!'''

'''CalcAddIns.xcu:'''
* Registers function with Calc's formula engine
* Only declares '''visible''' parameters
* Uses `IsOptional` for optional arguments
* CompatibilityName matches implementation

'''NumToWords.rdb:'''
* Compiled from `NumToWords.idl`
* Provides type information for UNO bridge
* '''Critical for parameter passing!'''

'''META-INF/manifest.xml:'''
* Legacy Python component type
* Includes RDB reference
* No modern `.components` approach

---

== Complete Build Commands ==

=== For Python Extension (Working) ===

<syntaxhighlight lang="bash">
# 1. Create IDL
# 2. Compile IDL to RDB
unoidl-write /usr/lib/libreoffice/program/types.rdb \
    /usr/lib/libreoffice/program/types/offapi.rdb \
    idl/XNumToWords.idl \
    NumToWords.rdb

# 3. Package OXT
zip -r NumToWordsPy.oxt \
    numtowords.uno.py \
    CalcAddIns.xcu \
    NumToWords.rdb \
    description.xml \
    META-INF/

# 4. Install
unopkg add NumToWordsPy.oxt
</syntaxhighlight>

---

== Testing ==

=== Verification Steps ===

1. '''Check extension is registered:'''
<syntaxhighlight lang="bash">
unopkg list | grep numtowords
# Should show: com.numbertext.converter.NumToWordsPy
</syntaxhighlight>

2. '''Check RDB is loaded:'''
<syntaxhighlight lang="bash">
# Look for RDB in bundled packages
unopkg list | grep -A 10 numtowords | grep "uno-typelibrary"
</syntaxhighlight>

3. '''Test in LibreOffice Calc GUI:'''
<syntaxhighlight>
=NUMTOWORDS(123)      # Should return: one hundred and twenty-three
=NUMTOWORDS(42, 1)    # Should return: forty-second
=NUMTOWORDS(99, 2)    # Should return: ninety-nine dollars
</syntaxhighlight>

---

== Lessons Learned ==

=== Key Takeaways ===

1. '''Python is easier than Java''' for LibreOffice extensions
   * `unohelper.ImplementationHelper` handles registration
   * No complex factory methods needed
   * No Java version compatibility issues

2. '''The invisible XPropertySet is critical'''
   * LibreOffice injects it automatically
   * Don't list it in XCU
   * Don't try to handle it with `*args`

3. '''RDB type library is mandatory'''
   * Without it, you get `Err:504`
   * Compile IDL to RDB using `unoidl-write`
   * Include RDB in OXT manifest

4. '''CLI testing has limitations'''
   * `unopkg` can install but not fully test
   * AddIn functions need GUI context
   * Always test in real LibreOffice Calc

5. '''Match names exactly'''
   * Python method name ↔ XCU node name
   * Case-sensitive!
   * CompatibilityName format matters

---

== References ==

* '''Working Extension:''' [https://github.com/xunema/libreoffice-calc-numbers-to-words python/NumToWordsPy.oxt]
* '''Pattern Reference:''' [https://github.com/Numbertext/libnumbertext libnumbertext]
* '''LibreOffice Wiki:''' [https://wiki.documentfoundation.org/Documentation/SDKGuide/Calc_Add-ins Calc Add-ins Documentation]
* '''UNO Bridge:''' [https://api.libreoffice.org/ LibreOffice API]

---

== Version History ==

* '''v2.0.0''' - Working Python implementation (current)
* '''v1.0.0''' - Original Python attempt (failed registration)
* '''Java attempts''' - Multiple iterations (all failed, documented above)

---

''Documentation created by OpenCode AI Agent (kimi-k2.5)''
''Last updated: February 27, 2026''

[[Category:Development & Tools]]
[[Category:Open Source]]
[[Category:LibreOffice]]
[[Category:Python]]
[[Category:Troubleshooting]]