Minimum Javascript version in macros

English
, , ,
#1

(I added the calc tag mainly because that was the module I was working in. I could easily have encountered this issue in any LibreOffice module.)

I was writing a macro to solve a problem in a Calc worksheet I was working on. I was having troubles with the syntax. I wanted to use JavaScript classes. But it seems the version of JavaScript supported by LibreOffice (and probably other forks) lack support for not only classes, but let, const, the predefined class Map, for...of expressions, lambda (arrow) functions, and Object.keys(object_instance), Object.values(object_instance), and Object.entries(object_instance).

It’s like this portion of LibreOffice hasn’t been updated other than to ensure the bindings are present since the 1990s.

Incidentally, I added the following to the start of my macro. But that had no effect other than to change where the errors were being reported.

'use strict';

// jshint esversion: 8
JavaScript macro
#2

IMHO there is no support for any “version” of other languages. If you are using LibreOffice-API you have to find out how to interface to the C/Java-type interface. You may use classes in your code but to expect class-portability from python to ECMA-Script, also supporting Java/beans, while the only integrated macro-language is BASIC is much to ask…

Where did you get your bindings you refer to? (There may be more out on the net, but I have not seen much work done in JavaScript for LibreOffice…)

#5

I am guessing that you would like to find something like an npm for LO so you could program against it from nodejs. I don’t know of anything myself. FWIW, I think you might be able to go against the UNO framework from Java (Uno/Java/MavenBundles - Apache OpenOffice Wiki). Perhaps @erAck could enlighten us. It seems like a person should not be bound to the macro IDE, but would have an API library available for any of the available languages.

#22

I have absolutely no clue of anything nodejs or the like, or JavaScript for that matter.

#21

LibreOffice includes whatever ancient language specification was implemented in Mozilla Rhino used in the project.

Specifically, that’s Rhino version 1.5R5 from 2004, so the JavaScript language is no newer than of that time. Likely that’s version 3 of ECMAScript spec.

EDIT:
yes; citing “overview.html” from the tarbal:

Rhino contains

  • All the features of JavaScript 1.5

and JavaScript 1.5 is documented to comply with ECMAScript 3.

#3

image
image654×665 64.4 KB

Did you see that part of the menus? As for the bindings, it seems that the following code is legal.

importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.container.XNameAccess);
importClass(Packages.com.sun.star.table.XCellRange);
importClass(Packages.com.sun.star.table.XCell);
importClass(Packages.com.sun.star.table.CellContentType);
#4

At first: This site (or the people creating ask… didn’t like “answers” to give a some kind of continued dialogue. The mess will start, when answers are voted for etc…

Second I didn’t say you can’t use javascript. What you found is a way to organize texts as scripts an call a handler for this script. As an example: In a python-macro the first thing to do is to get something like XSCRIPTCONTEXT to have a starting point for conversation with LO. But you may also connect via sockets, if you are outside the proceds if LO… LO itself is using Python3, but you could interface from Python2…

#6

I’m debating writing a C# tool to do what I want. There do appear to be .NET bindings.

#7

I got the code running, but XCell.setFormula() doesn’t seem to be doing anything.

#8

Hello,
Have not looked at this in quite some time and know little about JavaScript but this works for me:

//xSheet = xSheetsIndexAccess.getByIndex(0);
xSheet = xSheetsNameAccess.getByName("Sheet1");
//get the XCellRange interface used to access a cell
xCll = UnoRuntime.queryInterface(XCellRange,xSheet);
//get Cell A1
xCellA1 = xCll.getCellByPosition(0,0);
//get the XCell interface used to access a cell
xC = UnoRuntime.queryInterface(XCell,xCellA1);
xC.setFormula("=a2+b2");

Result:
Screenshot at 2022-05-04 18-07-47

#9

I should note that in most cells, I need a text value. There is no overload of setValue that takes a string.

#10

Sorry, don’t know what you are noting. Thought this was in answer to

.
Also please use comments to respond and not answers. Answers are meant to actually answer the original post. Thank You.

#11

I was talking about that. How do you specify a text value for a cell? I only see setFormula. No overload of setValue exists for values of type string.

#12

Again, please us comments not answers.
.

As mentioned don’t know much about this but seems it is setString using:

importClass(Packages.com.sun.star.text.XTextRange);

See XTextRange Interface Reference
.
Edit:
Yes that works:

oTextRange = UnoRuntime.queryInterface(XTextRange,xCellA1);
oTextRange.setString("Hello");

produces:
Screenshot at 2022-05-04 19-15-37

#13

Ah. I didn’t see comment. No changes in the behavior. Still no new cell contents. Some cells will be set with setFormula (with actual formulas). Some get simple numbers and are set with setValue. Some are set with setString.

#14

Please post your code.
.
Edit:
You can see my code above and that it worked. Found this post and thought it may help > Re: [Calc] Set value for a cell with Javascript

#15

Note: This code is intended to merge the tables from Comparison of remote desktop software - Wikipedia. I’ve deleted some fields I don’t care about.

'use strict';

// jshint esversion: 8

importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.container.XNameAccess);
importClass(Packages.com.sun.star.table.XCellRange);
importClass(Packages.com.sun.star.table.XCell);
importClass(Packages.com.sun.star.table.CellContentType);
importClass(Packages.com.sun.star.text.XTextRange)

//get the document object from the scripting context
var oDoc = XSCRIPTCONTEXT.getDocument();
//get the XSpreadsheetDocument interface from the document
var xSDoc = UnoRuntime.queryInterface(XSpreadsheetDocument, oDoc);
//get the XIndexAccess interface used to access each sheet
var xSheetsNameAccess = UnoRuntime.queryInterface(XNameAccess, xSDoc.getSheets());

var cellrangeBasic = UnoRuntime.queryInterface(XCellRange, xSheetsNameAccess.getByName("Basic"));
var cellrangeOS = UnoRuntime.queryInterface(XCellRange, xSheetsNameAccess.getByName("OS_Support"));
var cellrangeFeatures = UnoRuntime.queryInterface(XCellRange, xSheetsNameAccess.getByName("Features"));
var cellrangeResults = UnoRuntime.queryInterface(XCellRange, xSheetsNameAccess.getByName("Results"));
var cellrangeRejects = UnoRuntime.queryInterface(XCellRange, xSheetsNameAccess.getByName("Rejects"));
var cellrangeIncomplete = UnoRuntime.queryInterface(XCellRange, xSheetsNameAccess.getByName("Incomplete"));
function findProductLine(strNameToFind, cellrangeToUse)
{
	var iCurRow = 1;
	var strProductNameInCurRow = getCellAutoValByPos(cellrangeToUse, 0, iCurRow);

	while(strProductNameInCurRow != null && strProductNameInCurRow != strNameToFind)
	{
		iCurRow++;
		strProductNameInCurRow = getCellAutoValByPos(cellrangeToUse, 0, iCurRow);
	}

	return strProductNameInCurRow == null ? -1 : iCurRow;
}

function getCellByPos(cellrangeToUse, iCol, iRow)
{
	return UnoRuntime.queryInterface(XCell, cellrangeToUse.getCellByPosition(iCol, iRow));
}

function getCellAutoValByPos(cellrangeToUse, iCol, iRow)
{
	var cell = getCellByPos(cellrangeToUse, iCol, iRow);

	switch(cell.getType())
	{
		case CellContentType.EMPTY:
			return null;

		case CellContentType.VALUE:
		case CellContentType.FORMULA:
			return cell.getValue();

		case CellContentType.TEXT:
		default:
			return cell.getFormula();
	}
}

function getCellBoolValByPos(cellrangeToUse, iCol, iRow)
{
	var strValFound = getCellAutoValByPos(cellrangeToUse, iCol, iRow).toLowerCase();

	if(strValFound == "?")
		return undefined;

	if(strValFound.includes("partial"))
		return "Partial";

	if(strValFound.includes("yes"))
		return true;

	return false;
}

function getCellNumValByPos(cellrangeToUse, iCol, iRow)
{
	var strValFound = getCellAutoValByPos(cellrangeToUse, iCol, iRow).toLowerCase();

	if(strValFound == "?")
		return undefined;

	var numResult = parseFloat(strValFound);

	return numResult == NaN ? undefined : numResult;
}

function Product(strName)
{
	this.strName = strName;

	var iBasicRow = findProductLine(strName, cellrangeBasic);
	var iOsSupportRow = findProductLine(strName, cellrangeOS);
	var iFeatureRow = findProductLine(strName, cellrangeFeatures);

	if(iBasicRow > -1)
	{
		this.strProtocols = getCellAutoValByPos(cellrangeBasic, 1, iBasicRow);
		this.strCreator = getCellAutoValByPos(cellrangeBasic, 2, iBasicRow);
		this.strFirstPublicReleaseDate = getCellAutoValByPos(cellrangeBasic, 2, iBasicRow);
		this.strLatestStableRelease = getCellAutoValByPos(cellrangeBasic, 3, iBasicRow);
		this.strLicense = getCellAutoValByPos(cellrangeBasic, 4, iBasicRow);
		this.bFree = getCellBoolValByPos(cellrangeBasic, 5, iBasicRow);
	}

	if(iOsSupportRow > -1)
	{
		this.bHasServer = getCellBoolValByPos(cellrangeOS, 1, iOsSupportRow);
		this.bHasClient = getCellBoolValByPos(cellrangeOS, 2, iOsSupportRow);
	}

	if(iFeatureRow > -1)
	{
		this.strListening = getCellAutoValByPos(cellrangeFeatures, 1, iFeatureRow);
		this.builtInEncryptionOpt = getCellAutoValByPos(cellrangeFeatures, 2, iFeatureRow);
		this.bFivarransferSupported = getCellBoolValByPos(cellrangeFeatures, 3, iFeatureRow);
		this.bAudioSupport = getCellBoolValByPos(cellrangeFeatures, 4, iFeatureRow);
		this.bMultiSession = getCellBoolValByPos(cellrangeFeatures, 5, iFeatureRow);
		this.bSeamlessWnds = getCellBoolValByPos(cellrangeFeatures, 6, iFeatureRow);
		this.bRemoteAssist = getCellBoolValByPos(cellrangeFeatures, 7, iFeatureRow);
		this.bIPV6 = getCellBoolValByPos(cellrangeFeatures, 8, iFeatureRow);
		this.bDragDrop = getCellBoolValByPos(cellrangeFeatures, 9, iFeatureRow);
		this.bUsbForwarding = getCellBoolValByPos(cellrangeFeatures, 10, iFeatureRow);
		this.bH264 = getCellBoolValByPos(cellrangeFeatures, 11, iFeatureRow);
		this.bBrowserSupport = getCellBoolValByPos(cellrangeFeatures, 12, iFeatureRow);
		this.bSessionPersists = getCellBoolValByPos(cellrangeFeatures, 13, iFeatureRow);
	}

	this.bFoundBasicInfo = iBasicRow > -1;
	this.bFoundOsSupportInfo = iOsSupportRow > -1;
	this.bFoundFeatureInfo = iFeatureRow > -1;

}

var listAllProductNamesFound = [];

function addAllKnownProductsInCellRange(cellrangeToUse)
{
	var iCurRow = 1;
	var strProductNameInCurRow = getCellAutoValByPos(cellrangeToUse, 0, iCurRow);

	while(strProductNameInCurRow != null)
	{
		iCurRow++;
		strProductNameInCurRow = getCellAutoValByPos(cellrangeToUse, 0, iCurRow);
		listAllProductNamesFound.push(strProductNameInCurRow);
	}
}

addAllKnownProductsInCellRange(cellrangeBasic);
addAllKnownProductsInCellRange(cellrangeOS);
addAllKnownProductsInCellRange(cellrangeFeatures);

var listAllProductDetailsFound = [];
for(var iCurProductIndex = 0; iCurProductIndex < listAllProductNamesFound.length; iCurProductIndex++)
{
	listAllProductDetailsFound.push(new Product(listAllProductNamesFound[iCurProductIndex]));
}

var iCurResultRow = 1;
var iCurRejectRow = 1;
var iCurIncompleteRow = 1;

function setCellVal(cellrangeDataDest, iCol, iRow, val)
{
	var cell = UnoRuntime.queryInterface(XCell, cellrangeDataDest.getCellByPosition(iCol, iRow));

	if(val == null)
		return;

	if(val == undefined)
		cell.setFormula("Unknown");
	else
		switch(typeof(val))
		{
			case "number":
				cell.setValue(val);

				break;

			case "string":
				UnoRuntime.QueryInterface(XTextRange, cell).setString(val);

				break;

			case "boolean":
				cell.setFormula("=" + val.toString().toUpperCase() + "()");

				break;
		}
}

for(var iCurProductIndex = 0; iCurProductIndex < listAllProductDetailsFound.length; iCurProductIndex++)
{
	var productCur = listAllProductDetailsFound[iCurProductIndex];

	var cellrangeDataDest = cellrangeResults;

	if(!productCur.bFoundBasicInfo || !productCur.bFoundOsSupportInfo || !productCur.bFoundFeatureInfo)
		cellrangeDataDest = cellrangeIncomplete;
	else if(!productCur.bFree || !productCur.bHasServer || !productCur.bHasClient || !productCur.bHasClient)
		cellrangeDataDest = cellrangeRejects;

	var iOutputRow;

	switch(cellrangeDataDest)
	{
		case cellrangeResults:
			iOutputRow = iCurResultRow++;

			break;

		case cellrangeRejects:
			iOutputRow = iCurRejectRow++;

			break;

		case cellrangeIncomplete:
			iOutputRow = iCurIncompleteRow++;

			break;
	}

	setCellVal(cellrangeDataDest, 0, iOutputRow, productCur.strName);
	setCellVal(cellrangeDataDest, 1, iOutputRow, productCur.strProtocols);
	setCellVal(cellrangeDataDest, 2, iOutputRow, productCur.strCreator);
	setCellVal(cellrangeDataDest, 3, iOutputRow, productCur.strFirstPublicReleaseDate);
	setCellVal(cellrangeDataDest, 4, iOutputRow, productCur.strLatestStableRelease);
	setCellVal(cellrangeDataDest, 5, iOutputRow, productCur.strLicense);
	setCellVal(cellrangeDataDest, 6, iOutputRow, productCur.bFree);
	setCellVal(cellrangeDataDest, 7, iOutputRow, productCur.bHasServer);
	setCellVal(cellrangeDataDest, 8, iOutputRow, productCur.bHasClient);
	setCellVal(cellrangeDataDest, 9, iOutputRow, productCur.strListening);
	setCellVal(cellrangeDataDest, 10, iOutputRow, productCur.builtInEncryptionOpt);
	setCellVal(cellrangeDataDest, 11, iOutputRow, productCur.bFivarransferSupported);
	setCellVal(cellrangeDataDest, 12, iOutputRow, productCur.bAudioSupport);
	setCellVal(cellrangeDataDest, 13, iOutputRow, productCur.bMultiSession);
	setCellVal(cellrangeDataDest, 14, iOutputRow, productCur.bSeamlessWnds);
	setCellVal(cellrangeDataDest, 15, iOutputRow, productCur.bRemoteAssist);
	setCellVal(cellrangeDataDest, 16, iOutputRow, productCur.bIPV6);
	setCellVal(cellrangeDataDest, 17, iOutputRow, productCur.bDragDrop);
	setCellVal(cellrangeDataDest, 18, iOutputRow, productCur.bUsbForwarding);
	setCellVal(cellrangeDataDest, 19, iOutputRow, productCur.bH264);
	setCellVal(cellrangeDataDest, 20, iOutputRow, productCur.bBrowserSupport);
	setCellVal(cellrangeDataDest, 21, iOutputRow, productCur.bSeamlessWnds);
}
#16

Remote desktop software comparison.ods (43.7 KB)
That’s my version of the table with the macro enclosed.

#17

Looks beyond my quick look.
.
I think trying a few smaller items as I tested may get you started. Also I expect you did create parcel-descriptor.xml? Writing Office Scripts in JavaScript

#18

I was staying within the GUI. I didn’t know of any of that.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.