Justinaquino: Imported from gi7b wiki

2026-03-06T10:07:30Z

Imported from gi7b wiki

New page

= 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> 
<node oor:name="formatStyle" oor:op="replace"> 
<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">

<implementation name="com.numbertext.converter.NumToWords$_NumToWords">
</syntaxhighlight>

'''Solution:''' Use top-level class only, no inner classes:
<syntaxhighlight lang="xml">

<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">

<node oor:name="Parameters">
<node oor:name="prop"/> 
<node oor:name="number"/>
<node oor:name="formatStyle"/>
</node>
</syntaxhighlight>

'''Solution:''' Only list visible parameters:
<syntaxhighlight lang="xml">

<node oor:name="Parameters">
<node oor:name="number"/> 
<node oor:name="formatStyle"/> 
</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">

<node oor:name="formatStyle" oor:op="replace">

</node>
</syntaxhighlight>

'''Solution:''' Add `IsOptional` property:
<syntaxhighlight lang="xml">

<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">

<node oor:name="ocnumtowords"> 
</syntaxhighlight>

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

'''Solution:''' Names must match exactly (case-sensitive):
<syntaxhighlight lang="xml">

<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">

<prop oor:name="CompatibilityName">
<value>AutoAddIn.NumToWords.numToWords</value>
</prop>
</syntaxhighlight>

'''Solution:''' Use simple dotted format:
<syntaxhighlight lang="xml">

<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]]

LibreOffice Calc NumToWords Extension Complete Guide - Revision history

Justinaquino: Imported from gi7b wiki