Ask Your Question
0

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

asked 2020-03-19 12:44:25 +0200

cbg.jahn gravatar image

updated 2020-03-30 11:09:22 +0200

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
http://ask.libreoffice.org/en/questio...

20191019 FormulaResultType return VALUE for cells with formula result #DIV/0! or #NAME?
http://bugs.documentfoundation.org/sh...

com.sun.star.table.XCell
http://api.libreoffice.org/docs/idl/r...
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/r...

com.sun.star.sheet.SheetCell
http://api.libreoffice.org/docs/idl/r...
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/93...
LO / AOO, Function Wizard → select cell → ctrl + F2 → stepping through evaluation

Apache POI, Formula Evaluation
http://poi.apache.org/components/spre...
FormulaEvaluator.evaluate( Cell cell )
FormulaEvaluator.evaluateFormulaCell( Cell cell )
FormulaEvaluator.evaluateInCell( Cell cell )

MS VBA Application.Evaluate( formula )
http://docs.microsoft.com/en-us/offic...

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

edit retag flag offensive close merge delete

Comments

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.

Mike Kaganski gravatar imageMike Kaganski ( 2020-03-20 08:08:22 +0200 )edit

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.

cbg.jahn gravatar imagecbg.jahn ( 2020-03-20 10:51:09 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2020-03-20 10:56:06 +0200 )edit

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 gravatar imagecbg.jahn ( 2020-03-26 08:27:44 +0200 )edit

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 gravatar imagecbg.jahn ( 2020-03-26 08:38:49 +0200 )edit

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 gravatar imagecbg.jahn ( 2020-03-26 08:55:19 +0200 )edit

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 gravatar imagecbg.jahn ( 2020-03-26 08:56:10 +0200 )edit

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 gravatar imagecbg.jahn ( 2020-03-26 08:57:19 +0200 )edit

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 gravatar imagecbg.jahn ( 2020-03-26 08:57:53 +0200 )edit

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 gravatar imagecbg.jahn ( 2020-03-26 08:58:27 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2020-03-27 12:10:28 +0200

Lupp gravatar image

updated 2020-03-27 12:11:28 +0200

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: https://api.libreoffice.org/docs/idl/...

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.

edit flag offensive delete link more
0

answered 2020-03-26 09:33:00 +0200

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.
edit flag offensive delete link more

Comments

@Mike Kaganski: 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 ...(more)

Lupp gravatar imageLupp ( 2020-03-26 13:12:01 +0200 )edit

In what way did I misunderstand you?

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

Mike Kaganski gravatar imageMike Kaganski ( 2020-03-26 14:33:00 +0200 )edit

@Lupp and @Mike Kaganski: 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 gravatar imagecbg.jahn ( 2020-03-27 05:31:45 +0200 )edit

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 gravatar imagecbg.jahn ( 2020-03-27 05:34:17 +0200 )edit

@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 @Mike Kaganski. Luckily the display content is retained for retrieval, per @Lupp. It saves the tedium of format conversion every time.

cbg.jahn gravatar imagecbg.jahn ( 2020-03-30 11:10:57 +0200 )edit

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();

cbg.jahn gravatar imagecbg.jahn ( 2020-03-30 11:16:13 +0200 )edit

(Strictly OT!)
@Mike Kaganski: 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 gravatar imageLupp ( 2020-03-30 13:21:40 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-03-19 12:44:25 +0200

Seen: 78 times

Last updated: Mar 30