How to change a cell's background colour using a JavaScript macro in Calc?

nicoty · November 25, 2021, 10:22am

Hi. I’d like to change a cell’s background colour using a JavaScript macro in Calc. How would I do this? Looking through the API, I surmised that I needed to use the setPropertyValue method to change the CellBackColor property of the cell, but I’m not sure what the proper usage syntax for it should be.

This is what I’ve tried:

importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.container.XIndexAccess);
importClass(Packages.com.sun.star.table.XCellRange);
importClass(Packages.com.sun.star.table.XCell);
importClass(Packages.com.sun.star.beans.XPropertySet);
importClass(java.lang.Long);
importClass(Packages.com.sun.star.util.Color);

var cell = UnoRuntime.queryInterface(
	XCellRange,
	UnoRuntime.queryInterface(
		XIndexAccess,
		UnoRuntime.queryInterface(
			XSpreadsheetDocument,
			XSCRIPTCONTEXT.getDocument()
		).getSheets()
	).getByIndex(0)
).getCellByPosition(0, 0);

UnoRuntime.queryInterface(
	XPropertySet,
	UnoRuntime.queryInterface(XCell, cell)
).setPropertyValue("CellBackColor", new Color(new Long(16776960)));

I was hoping that running that would change the colour of the cell in sheet 0, column 0, row 0 to the colour that 16776960 corresponds to (which afaik should be yellow), but it doesn’t work, presumably because I’m using the setPropertyValue method incorrectly. As such, I’d like to know, what’s the proper way to do this and what inputs does the setPropertyValue method actually take?

Fwiw, I know that 16776960 corresponds to yellow from running the following code after having manually set the colour of the cell to yellow:

// Imports are the same as above, just removed here for conciseness..
cell.setFormula(
	"".concat(
		UnoRuntime.queryInterface(
			XPropertySet,
			UnoRuntime.queryInterface(XCell, cell)
		).getPropertyValue("CellBackColor")
	)
);

joshua4 · November 25, 2021, 3:43pm

I haven’t programmed LO with javascript or Java, but it is suspicious that you are converting your color number to a Color object. I just wouldn’t guess that UNO wants the number marshalled as an object like that. Have you tried just .setPropertyValue(“CellBackColor”, 16776960)?

nicoty · November 25, 2021, 4:16pm

Hey thanks for replying. I have indeed tried just using the native JS number type. I’ve also tried .setPropertyValue(“CellBackColor”, "16776960"), .setPropertyValue(“CellBackColor”, new Long(16776960)) and .setPropertyValue(“CellBackColor”, new Color(16776960)), as well as various permutations of all of these. None of these have worked.

I’ve discovered that the line importClass(Packages.com.sun.star.util.Color); makes the interpreter silently fail, so I’ve stopped using that and no longer use calls to Color. That said, I still haven’t figured out a solution to my problem.

joshua4 · November 26, 2021, 5:22pm

The trick is how a person marshals the property values from Rhino (javascript) to Uno. Here is a quick demonstration that seems to work for Calc 7.2.2.2:

//Mini-dmonstration of how to marshal values between
//	javascript and Calc using LibreOffice implementation of Rhino

//Import the Uno runtime interfaces
importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.uno.XInterface);
importClass(Packages.com.sun.star.beans.XPropertySet);

//Import the Uno Calc interfaces
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.container.XIndexAccess);
importClass(Packages.com.sun.star.table.XCellRange);
importClass(Packages.com.sun.star.table.XCell);
importClass(Packages.com.sun.star.text.XTextRange);

//Rhino seems to have these handled...
//importPackage(java.type);
//importClass(java.lang.Integer);

//Set test cell location in document
var sheet = 4
var row = 0
var column = 0

//Get polymorphic cell
var polyCell = UnoRuntime.queryInterface(
	XCellRange,
	UnoRuntime.queryInterface(
		XIndexAccess,
		UnoRuntime.queryInterface(
			XSpreadsheetDocument,
			XSCRIPTCONTEXT.getDocument()
		).getSheets()
	).getByIndex(sheet)
).getCellByPosition(column, row);

//Cast cell for each needed interface
var propCell = UnoRuntime.queryInterface(XPropertySet, polyCell);
var cellCell = UnoRuntime.queryInterface(XCell, polyCell);
var textCell = UnoRuntime.queryInterface(XTextRange, polyCell);

//Conversion in and out of java.lang.Integer coupled with parseInt
//	seems to be required for Uno properties wanting
//		long or short
//	but no conversion seems to be required for Uno properties wanting
//		string or float
//	and some finessing with
//		boolean

//Get current background color of test cell
var unoOldColor = new java.lang.Integer(0);
unoOldColor = propCell.getPropertyValue("CellBackColor");
var jsOldColor = parseInt(unoOldColor);

//Set text of test cell
//textCell.setString(typeof(oldColor)); //Shows 'object'
//textCell.setString(oldColor); //Shows the old color

//Setup the new value for cell background color
var jsNewColor =  jsOldColor + 70;
if (jsNewColor > 16777215) jsNewColor = 70;
var unoNewColor = new java.lang.Integer(jsNewColor);

//Setup the new value for cell font overline (remarkably nimble!)
var jsNewOverline = 3; //Single dotted overline
var unoNewOverline = new java.lang.Integer(jsNewOverline);

//Setup the new value for cell character height
var unoCharHeight = 20; //In points??

//Setup the new value for character crossed out
var unoOldCharCrossedOut = propCell.getPropertyValue("CharCrossedOut");
var unoNewCharCrossedOut = false;
if (unoOldCharCrossedOut == 'true')
{
	unoNewCharCrossedOut = false;
}
else
{
	unoNewCharCrossedOut = true;
}

//Make changes to test cell
textCell.setString(unoNewColor); //Proptery wants a string; setting to null does not change cell text
propCell.setPropertyValue("CellBackColor", unoNewColor); //Property wants a long
propCell.setPropertyValue("CharOverline", unoNewOverline); //Property wants a short
propCell.setPropertyValue("CharHeight", unoCharHeight); //Property wants a float
propCell.setPropertyValue("CharCrossedOut", unoNewCharCrossedOut); //Property wants a boolean

//End

My resources for figuring this out were:

https://docs.oracle.com/javase/8/docs/technotes/guides/scripting/prog_guide/javascript.html#A1147207

https://wiki.openoffice.org/wiki/Documentation/DevGuide/ProUNO/Java/Inexact_approximation_of_UNO_Value_Semantics

gist.github.com

https://gist.github.com/pingw33n/1089320#file_java_script_java_type_mapping.textile

JavaScriptJavaTypeMapping.textile

|_.JavaScript Value    |_.JavaScript Type|_.Java Type                             |_.Is Scriptable|_.Is Function|
|@{a:1, b:['x','y']}@  |@object@         |@org.mozilla.javascript.NativeObject@   |*+*            |-            |
|@[1,2,3]@             |@object@         |@org.mozilla.javascript.NativeArray@    |*+*            |-            |
|@1@                   |@number@         |@java.lang.Double@                      |-              |-            |
|@1.2345@              |@number@         |@java.lang.Double@                      |-              |-            |
|@NaN@                 |@number@         |@java.lang.Double@                      |-              |-            |
|@Infinity@            |@number@         |@java.lang.Double@                      |-              |-            |
|@-Infinity@           |@number@         |@java.lang.Double@                      |-              |-            |
|@true@                |@boolean@        |@java.lang.Boolean@                     |-              |-            |
|@"test"@              |@string@         |@java.lang.String@                      |-              |-            |

This file has been truncated. show original

And guessing at the java.lang.Integer and parseInt().

nicoty · November 26, 2021, 5:46pm

Ah great job figuring this out! I guess I should’ve tried using Integer. It was a mistake to assume that I needed to use Long, although in my defense, I was misled by the fact that the section about the CellBackColor property in the API documentation linked to this page which mentioned the Long type.

Now if I could just figure out how to create and delete sheets, although I think that’s a topic for another post… Anyways, thanks again!

joshua4 · November 26, 2021, 8:07pm

Villeroy clued me into using MRI. It is almost a must; you may already be using it. I study the Methods tab, and move between MRI and the docs I’m looking for. So the resources are:

https://forum.openoffice.org/en/forum/viewtopic.php?f=74&t=49294

which is the link Villeroy gave me recently. (Thanks again!) And,

http://www.openoffice.org/api/

then search under the Office API reference field.

This will add a sheet as the second sheet:

//Mini-dmonstration of how to add a sheet
//	for Calc using LibreOffice implementation of Rhino

//Import the Uno runtime interfaces
importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.uno.XInterface);

//Import the Uno Calc interfaces
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.sheet.XSpreadsheets);
importClass(Packages.com.sun.star.container.XIndexAccess);

//Rhino seems to have these handled...
//importPackage(java.type);
//importClass(java.lang.Integer);

//Get sheets object
var polySheets = UnoRuntime.queryInterface(
	XSpreadsheetDocument, XSCRIPTCONTEXT.getDocument()
	).getSheets();
var xspreadsheetsSheets = UnoRuntime.queryInterface(XSpreadsheets, polySheets);

//Insert new sheet in position 2 (i.e. index 1)
xspreadsheetsSheets.insertNewByName("NewerSheet", 1); //Will throw if name is not unique

//End

If you (nicoty) would go ahead and post a new question, then after the weekend I’ll move this over there, where it really should be.

nicoty · November 26, 2021, 8:53pm

Ah thanks again for solving another of my problems, and also for letting me know about MRI! I certainly wasn’t aware of it, but it definitely seems like a useful tool to help with learning the API. I’ll be sure to use it in the future.

Sure thing! Here it is.

nicoty · November 25, 2021, 5:25pm

I have a very hacky solution for now. What I’ve done is to set aside some cells to be “palette” cells. These cells will have their colour changed manually, then if I want to colour some other cell with the same colour as one of the palette cells, I copy the property of the desired palette cell to the target cell. Here’s a hacky implementation for demonstratation purposes, which assumes that the second cell on the first column is a manually coloured palette cell, and that the target cell is the cell directly below it:

importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.container.XIndexAccess);
importClass(Packages.com.sun.star.table.XCellRange);
importClass(Packages.com.sun.star.table.XCell);
importClass(Packages.com.sun.star.beans.XPropertySet);

var get_sheet = function (index) {
	return UnoRuntime.queryInterface(
		XIndexAccess,
		UnoRuntime.queryInterface(
			XSpreadsheetDocument,
			XSCRIPTCONTEXT.getDocument()
		).getSheets()
	).getByIndex(index);
};

var get_cell = function (sheet, column, row) {
	return UnoRuntime.queryInterface(
		XCell,
		UnoRuntime.queryInterface(XCellRange, sheet).getCellByPosition(column, row)
	);
};

var sheet = get_sheet(0);

// This cell is used to determine if any of the steps fail, since the editor/debugger that comes with LO isn't very helpful.
var debug_cell = get_cell(sheet, 0, 0);

debug_cell.setFormula("0");

var property = UnoRuntime.queryInterface(
	XPropertySet,
	UnoRuntime.queryInterface(XCell, get_cell(sheet, 0, 1))
).getPropertyValue("CellBackColor");

debug_cell.setFormula("1");

UnoRuntime.queryInterface(
	XPropertySet,
	UnoRuntime.queryInterface(XCell, get_cell(sheet, 0, 2))
).setPropertyValue("CellBackColor", property);

debug_cell.setFormula("2");

Obviously this isn’t ideal as one would have to manually load in some palette cells and hardcode their sheet and coordinates into the program, so I’d still appreciate if someone more knowledgeable would be kind enough to share the proper way to use setPropertyValue.