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

[mikekaganski]

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]

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]

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]

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.

1. via storage, i.e., XCell.get*().
   
2. 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]

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]

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]

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]

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]

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]

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]

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”.

1. Mere XCell.getValue() alone.
   
2. 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]

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:

1. 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).
2. 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.
3. 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]

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

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]

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

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]

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

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]

(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.

[Lupp]

Quoting @cbg.jahn: “… Is your ‘.String’ in BASIC code?..”
Of course, many (not all!) things I see as “simple” properties, and access/change like variables when using the Basic IDE for object inspection, or writing statements in Basic are “full-grown” propertis with their set and get methods (still bad wording though finally accepted instead of take and give).
The API, however, works with services and interfaces and a SheetCell object as I see it supports a lot of services, amongt them com::sun::star::text::Text for formatted text cells. This service in turn exports lots of interfaces…

See: LibreOffice: SheetCell Service Reference

I personally am not interested in BeanShell, but if it pretends to offer a sufficiently complete bridge to the LibreOffice API, it must support the mentioned services an interfaces.
Try mySheetCellObject.getText.getString it should return the content as formatted due to the ‘Numbers’ format settings. This implicit dependency must be independent of the language/DE used for programming.
You can NOT get this way a string afflicted by ConditionalFormatting. That’s a very different case.