Calc API, retrieve the display content of XCell [answered]

cbg.jahn · March 19, 2020, 11:44am

Dear LO community:

In BeanShell (Java), how could one retrieve the display content of an XCell object of arbitrary data type, as text in appearance identical to its cell display in Calc / “Function Wizard”?

For example, when XCell.getType() == CellContentType.FORMULA, for formulae of date type, of string type, etc., XCell.getValue() is not suited to build the display text shown by the cell display in Calc.

sheet.FormulaResult has been discussed concerning formulae results as a replacement for CellContentType, as shown below. I do not know how to translate its BASIC code to BeanShell (Java) code, due to uncertainty in the parameters for queryInterface().

A related LO feature “Function Wizard” works in interactive mode. “Function Wizard” (interactive) would become “Formula Evaluator” (automated) after suppressing the user dialog and user interaction, as discussed on superuser.com shown below. LO API does not publish the class which would behave as “Formula Evaluator” (automated) on the basis of “Function Wizard” (interactive). Similar to “Function Wizard”, “Formula Evaluator” needs to return an Object along with its display type info. (String, Date, etc.), in order to handle an XCell of arbitrary display type (output, format type, *NumberFormat*, semantics meaning of displayed data), and storage type (input, CellContentType.FORMULA).

The Apache POI project provides a class FormulaEvaluator to emulate the feature of the MS Excel VBA Application.Evaluate( formula ). There is difference between the object models of Apache POI and LO. An attempt to invoke the Apache POI FormulaEvaluator in LO Java code would not be a trivial effort.

Guidance or link to Java code illustration is appreciated. Thanks.

20161211 Macro: how to get a cell value set by a formula

Macro: how to get a cell value set by a formula

20191019 FormulaResultType return VALUE for cells with formula result #DIV/0! or #NAME?

http://bugs.documentfoundation.org/show_bug.cgi?id=70882

com.sun.star.table.XCell

http://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1table_1_1XCell.html

double XCell.getValue()

(snip) … for a formula cell the result value of a formula is returned.

com.sun.star.sheet.FormulaResult

http://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet_1_1FormulaResult.html

com.sun.star.sheet.SheetCell

http://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1sheet_1_1SheetCell.html#affda686bb18571c67c625c9adc1c85f9

table.CellContentType CellContentType → XCell.getType()

long sheet.SheetCell.FormulaResultType → table.CellContentType, XCell.getType()

long sheet.SheetCell.FormulaResultType2 → sheet.FormulaResult

20150711 LibreOffice Calc equivalent to Excel “Evaluate Formula” feature

http://www.superuser.com/questions/939363

LO / AOO, Function Wizard → select cell → ctrl + F2 → stepping through evaluation

Apache POI, Formula Evaluation

Formula Evaluation

FormulaEvaluator.evaluate( Cell cell )

FormulaEvaluator.evaluateFormulaCell( Cell cell )

FormulaEvaluator.evaluateInCell( Cell cell )

MS VBA Application.Evaluate( formula )

http://docs.microsoft.com/en-us/office/vba/api/excel.application.evaluate

MS Windows XP SP3, x32

LO 5.4.7.2, last version for XP

BeanShell 2.0.6

JRE 1.8.0.152 x32

JVM hotspot, not OpenJ9

mikekaganski · March 20, 2020, 7:08am

You ask some unclear question. Specifically, you state something (“XCell.getValue() always returns [0.0], for formulae of date type, of text (string) type, etc”), without some example data + code; then you list some Q&As that look like should had answered you…

E.g., you could provide a sample data (like “A1: foo; A2: bar; B1: formula1; B2: formula2”), and the code that does not work for you. Then we could try to see why e.g. the first link you posted (with my answer telling how to obtain string as formula result) doesn’t work for you; and why “date type” gives headaches when it shouldn’t.

cbg.jahn · March 20, 2020, 9:51am

My skill with ask.libreoffice.org is poor. My past code posting failed miserably, due to illegible, messy format (indentation, line breaks, etc.). In addition, the “Comments” section imposes limit on character count.

Could you please point me to some introduction pages on how best to post code on ask.libreoffice.org? I shall cut down the code, keeping it focused for symptom reproduction. Thanks.

mikekaganski · March 20, 2020, 9:56am

You just paste the code, select it, then click the “preformatted text” 101010 button (which simply adds four spaces in front of every code line, or wraps into `` if it’s not a whole line).

Edit your question to add it, don’t use answers or comments: it’s part of the question. Also that way, you don’t have a character limit.

cbg.jahn · March 26, 2020, 7:27am

I revised the original query after more test coverage.

LO was launced by BeanShell in a RPC remote client context via comp.helper.Bootstrap.bootstrap(), not the XScriptContext (not embedded). The source data, the employed API code, and its outcome are described below.

Two retrieval approaches.

via storage, i.e., XCell.get*().
via display, i.e., util.XNumberFormatter.

storage approach

1 type → XCell.getType().

2 formula → XCell.getFormula().

3 value → XCell.getValue().

display approach

1 type → util.NumberFormat, bit flags.

2 code → format code.

3 text → util.XNumberFormatter.convertNumberToString().

display [ … ] → the expected answer per cell display in Calc / “Function Wizard” view.

cbg.jahn · March 26, 2020, 7:38am

Source data.

row 4, ordinary data.

A4, display = “2020-03-23”, format = date, yyyy-mm-dd.

B4, display = “03-11-20”, format = date, mm/dd/yy.

C4, display = “25”, format = number, 0 decimal (integer).

D4, display = “30.00”, format = number, 2 decimal (double).

E4, display = “ST009117S”, format = text.

row 7, formulae.

A7, display = “2020-03-26”, [= A$4 + 3], format = date, yyyy-mm-dd.

B7, display = “03-01-20”, [= B$4 - 10], format = date, mm/dd/yy.

C7, display = “50”, [= C$4 * 2], format = number, 0 decimal (integer).

D7, display = “120.00”, [= D$4 * 4], format = number, 2 decimal (double).

E7, display = “ST009”, [= MID( E$4, 1, LEN(E$4) - 4 )], format = text.

cbg.jahn · March 26, 2020, 7:55am

test outcome, column A

sheet cell = test.A4 → display [2020-03-26]

storage type = [VALUE]

storage formula = [43913]

storage value = [43913.0]

display text = [2020-03-23]

display code = [YYYY-MM-DD]

display type = 2, [DATE DATETIME]

sheet cell = test.A7 → display [2020-03-26]

storage type = [FORMULA]

storage formula = [=A$4+3]

storage value = [43916.0]

display text = [43916.0]

display code = [YYYY-MM-DD]

display type = 2, [DATE DATETIME]

cbg.jahn · March 26, 2020, 7:56am

test outcome, column B

sheet cell = test.B4 → display [03-01-20]

storage type = [VALUE]

storage formula = [43901]

storage value = [43901.0]

display text = [03/11/20]

display code = [MM/DD/YY]

display type = 2, [DATE DATETIME]

sheet cell = test.B7 → display [03-01-20]

storage type = [FORMULA]

storage formula = [=B$4-10]

storage value = [43891.0]

display text = [43891.0]

display code = [MM/DD/YY]

display type = 2, [DATE DATETIME]

cbg.jahn · March 26, 2020, 7:57am

test outcome, column C

sheet cell = test.C4 → display [25]

storage type = [VALUE]

storage formula = [25]

storage value = [25.0]

display text = [25 ]

display code = [0_ ]

display type = 17, [USER NUMBER]

sheet cell = test.C7 → display [50]

storage type = [FORMULA]

storage formula = [=C$4*2]

storage value = [50.0]

display text = [50.0]

display code = [0_ ]

display type = 17, [USER NUMBER]

cbg.jahn · March 26, 2020, 7:57am

test outcome, column D

sheet cell = test.D4 → display [30.00]

storage type = [VALUE]

storage formula = [30]

storage value = [30.0]

display text = [30.00 ]

display code = [0.00_ ]

display type = 17, [USER NUMBER]

sheet cell = test.D7 → display [120.00]

storage type = [FORMULA]

storage formula = [=D$4*4]

storage value = [120.0]

display text = [120.0]

display code = [0.00_ ]

display type = 17, [USER NUMBER]

cbg.jahn · March 26, 2020, 7:58am

test outcome, column E

sheet cell = test.E4 → display [ST009117S]

storage type = [TEXT]

storage formula = [ST009117S]

storage value = [0.0]

display text = [ST009117S]

display code = [@]

display type = 256, [TEXT]

sheet cell = test.E7 → display [ST009]

storage type = [FORMULA]

storage formula = [=MID(E$4;1;LEN(E$4)-4)]

storage value = [0.0]

display text = [0.0]

display code = [General]

display type = 16, [NUMBER]

cbg.jahn · March 26, 2020, 8:02am

Observation → Display contents produced by the following API classes for formulae of date, text, etc., could deviate from the cell display in Calc / “Function Wizard”.

Mere XCell.getValue() alone.
XNumberFormatter.

Is it possible to access the API class of “Function Wizard” while suppressing its user interaction and dialog? “Function Wizard” appears to be equivalent to MS VBA Application.Evaluate(), albeit in interactive mode.

mikekaganski · March 26, 2020, 8:33am

There is no property in XCell (or specifically in ScCellObj that implements it for Calc) that holds “displayed text”.

You need to do it in steps:

Get the proper value from the cell. It depends on the type of the cell result; possibly you need to consider only numeric and textual results for simplicity (there’s also error). To do that, you need to follow the answer you cited in your question (or consider comments to tdf#70882).
Get the format string. To do that, you may use XNumberFormatsSupplier::getNumberFormats() (implemented by spreadsheet component) to obtain reference to XNumberFormats, then call its getByKey (passing the cell’s format id that you get from “FormatID” property of ScCellObj’s XPropertySet interface) to obtain XPropertySet of the format; use its “FormatString” property to get the format string.
Format the resulting string from the value and format string. You may use com.sun.star.sheet.FunctionAccess and use Calc’s TEXT function for that.

Lupp · March 26, 2020, 12:12pm

@mikekaganski: In what way did I misunderstand you?
If I inspect a cell the .String property is containing the formatted result. This is true even if the cell contains text, and I forced it to lie using the fourth position of an extended format code like in
"";"";"";"I am lying!". The entered constant content is returned by the .Formula property (not having a leading = then, of course).
(I did not study what the OQer told about the evaluation of a formula given as a string outside of a cell. Such a feature was requested more than once, mostly referring to MS Excel-VBA. There are fundamental problems I wouldn’t discuss here. Only this hint: The formula wizard, e.g. always needs to know the cell it was called from and will use it as the context for the needed interpretation of relative addresses. The term Evaluator is used in ODF specifications in a significantly different sense.)

mikekaganski · March 26, 2020, 1:33pm

In what way did I misunderstand you?

@Lupp: most possibly you understand me better than I do - and maybe I am totally wrong here. Thank you for the correct answer!

cbg.jahn · March 27, 2020, 4:31am

@Lupp and @mikekaganski: Thanks for feedback.

@Lupp: advice #1 (1st paragraph)

XCell.String would be a great solution for its display content (formatted result) of XCell, if it is accessible to BeanShell (Java). Is your “.String” in BASIC code? It failed in BeanShell (Java). Error dialogs below.

Evaluation Error: … : Cannot access field: String, on object: …,Type[com.sun.star.table.XCell]] : at Line: 127 : in file: … : x1cel .String

Could you please guide me with BeanShell (Java) code on what API class can be used to extract these unpublished XCell members (or XPropertySet) in this statement:

prop_set_of_cell = queryInterface( ???which_API_class???, xcell_object );

cbg.jahn · March 27, 2020, 4:34am

Many BASIC examples on web show direct access to object members. API does not publish the existence of these XCell members. Nor does API publish their access method by BeanShell (Java). BeanShell (Java) prohibits direct access to these undocumented class members. I have reported this difference of access permission between BASIC and BeanShell (Java) in my query, paragraph “sheet.FormulaResult has … … I do not know how to translate its BASIC code to BeanShell (Java) code, due to uncertainty in the parameters for queryInterface().”

prop_set_of_cell = queryInterface( ???which_API_class???, xcell_object );

cbg.jahn · March 30, 2020, 9:10am

@Lupp: Your tip of XCell.getText.getString in BASIC code leads to a BeanShell (Java) solution for the display content of XCell. I labeled this query as [answered]. Thanks.

For BeanShell (Java) developers / experts below.

LO Calc data deal with both the storage content and the display content. The storage content keeps the data. The display content mirrors the cell display in Calc. Storage and display contents may appear very different. For example, Date is stored as a double, indicating an offset from a reference date. The conversion from storage to display is achieved by applying the format ID (format handle) stored in the XCell. This formatting conversion is complicated in BeanShell (Java) code, as outlined by @mikekaganski. Luckily the display content is retained for retrieval, per @Lupp. It saves the tedium of format conversion every time.

cbg.jahn · March 30, 2020, 9:16am

The storage content is kept in table.XCell. The display content is kept elsewhere, in text.XTextRange. The BeanShell (Java) code follows, translated from BASIC code by @Lupp.
LO BASIC permits direct access to inherited public members, neat and convenient. Not possible for LO bridges in other languages (Java, Python, C++, etc.) at present.

XCell x1cel;

XTextRange x1txt;

String s1cel; // the display content of a XCell

x1txt = UnoRuntime.queryInterface( XTextRange.class, x1cel );

s1cel = x1txt.getString();

Lupp · March 30, 2020, 11:21am

(Strictly OT!)
@mikekaganski: I tried to send you a mail. I was blocked “(S3150)” as already with a different addressee in the same special universe. I would like to get a short note by you to be sure that I’m still reachable from DS9 or wherever they are.