[Calc] Set value for a cell with Javascript

[Mr.Dandy]

Dear forum,

This code set a string for a cell

Code: Select all

importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.sheet.XSpreadsheets);
importClass(Packages.com.sun.star.table.XCellRange);
importClass(Packages.com.sun.star.text.XTextRange);
importClass(Packages.com.sun.star.container.XIndexAccess);

       oDoc = XSCRIPTCONTEXT.getDocument();
       xSpreadsheetDocument = UnoRuntime.queryInterface(XSpreadsheetDocument, oDoc);
       oSpreadsheets = xSpreadsheetDocument.getSheets();
       oIndexAccess = UnoRuntime.queryInterface(XIndexAccess, oSpreadsheets);
       oSheet = oIndexAccess.getByIndex(0);
       oCellRange = UnoRuntime.queryInterface(XCellRange, oSheet);
       OutRange = oCellRange.getCellRangeByName("A1");
       oTextRange = UnoRuntime.queryInterface(XTextRange,OutRange);
       oTextRange.setString("12345");

But I need to set A1 cell with 12345 as value.
I don't know what class I should call to do it.

Thanks

[robleyd]

Isn't there a .setValue ?? Try using an inspection tool such as MRI to see what attributes are available.

[Villeroy]

You put the string "12345" into the cell which is the only method which will never put a value.
There is method setValue of interface .table.XCell
There is method setFormula(string) of the same interface.
And a single cell has string property FormulaLocal which is the localized formula as shown in the formula bar.

xCell.setValue(1234.56) sets the value directly
xCell.setFormula("1234.56") does effectively the same.

Then there is .sheet.XCellRangeFormula which can write formulas to a whole range. For a single cell it looks like this:
XCellRangeFormula.setFormulaArray( [["1234.56"]] )

and of course XCellRangeData:
XCellRangeData.setDataArray( [[1234.56]] )

OutRange.setPropertyValue("FormulaLocal", "1234.56") does the same but only if the point is the valid decimal separator. Otherwise it will put a string.


What you do by setting the string directly is equivalent to
xCell.setFormula("'1234.56")
XCellRangeFormula.setFormulaArray( [["'1234.56"]] )
OutRange.setPropertyValue("FormulaLocal", "'1234.56")
all formula strings with a leading apostroph

or XCellRangeData.setDataArray( [["1234.56"]] ) [string without apostroph]

[Mr.Dandy]

There is method setValue of interface .table.XCell
xCell.setValue(1234.56) sets the value directly
xCell.setFormula("1234.56") does effectively the same.

I can't do it.
Did you a sample code to declare and setting this?

[Villeroy]

MRI can do that for you. It can record Java with all the type declarations. Changing this to JS is simple. Hint: A text range does not support method setValue