Bug 54854 - calc add-in functions are not evaluated as expected
Summary: calc add-in functions are not evaluated as expected
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2012-09-13 10:30 UTC by Forester
Modified: 2020-07-23 03:55 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Calc add-in component. Issues first seen using this. (3.28 KB, application/vnd.openofficeorg.extension)
2012-09-13 10:30 UTC, Forester
Details
Python script to be run as a macro to illustrate the issue decrined in the previous comment. (845 bytes, text/x-python)
2013-05-18 13:02 UTC, Forester
Details
Python macro illustrating the issue and several workarounds (2.31 KB, text/x-python)
2013-05-18 20:14 UTC, Forester
Details
Office Basic Example shows issue entering formulae (770 bytes, text/plain)
2013-05-19 17:12 UTC, Forester
Details
Example showing issue with standard add-in functions (550 bytes, text/plain)
2013-05-19 17:20 UTC, Forester
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Forester 2012-09-13 10:30:52 UTC
Created attachment 67091 [details]
Calc add-in component.  Issues first seen using this.

Problem description:

calc add-in functions are not evaluated as expected

A) UI shows #NAME? instead of the add-in function result when a simple formula
that calls the function is entered using setFormula() via the pyuno bridge.

The same formula is evaluated as expected when entered by hand via the UI.

The #NAME? condition may be cleared at the UI by selecting the cell and, at the
keyboard, typing F2 SPC RET (effectively re-entering the formula by hand).

It may also be cleared by using Find & Replace to substitute the leading the
leading "=" with "=".  The 'workaround' is also effective from a Python script
using the pyuno bridge.

These symptoms affect all add-in functions I have implemented in Python.

B) UI shows #VALUE! instead of the add-in function result when the calc
workbook is closed and then re-opened.

The #VALUE! condition may be cleared as for the #NAME? but it may also be
cleared by selecting the cell(s) and typing DEL ^Z.

Oddly these symptoms do not show with all the add-in functions I have
implemented in Python but I suspect the issue is not with the function itself.

Steps to reproduce:

1.  Install the attached add-in component.

Please review the add-in.  It seems simple enough but it is the result of
triangulation of scant information from obscure sources of dubious currency.

2.  Open LibreOffice with a UNO bridge and a new calc document.  E.g.

  soffice --calc --accept="socket,host=localhost,port=2012;urp";

From the UI, enter in a cell the formula:

  =PAGENAME(0)

The UI should show the formula evaluates to "Sheet1".

In another cell enter:

  =PAGENAME(SHEET(Sheet1.A1)=1)

The UI should show the formula evaluates to "Sheet1".

3.  From a shell console start Python as bundled with LibreOffice.  E.g.

  /opt/libreoffice3.6/program/python

and copy/paste the following one line at a time into the shell:

  import uno
  # connect with running LibreOffice
  lcxt = uno.getComponentContext()
  resolver = lcxt.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", lcxt)
  unoUrp = 'socket,host={0},port={1};urp'.format("localhost", 2012)
  rctx = resolver.resolve("uno:{0};StarOffice.ComponentContext".format(unoUrp))
  # enter formula into a cell of the open workbook
  desktop = rctx.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop",rctx)
  cell = desktop.getCurrentComponent().Sheets.getByName('Sheet1').getCellRangeByName("E5")
  cell.setFormula("=PAGENAME(SHEET(Sheet1.A1)-1)")

There should be no errors.  Beware line wrap: each assignment is one line.

The cell E5 on Sheet1 will now show #NAME? when one would expect it to show Sheet1.

Clear the #NAME? using one of the methods describe in A) of the Problem Description above.
Save, close and reopen the workbook.

The cell E5 on Sheet1 will now show #VALUE! when one would expect it to show Sheet1.

Clear the #VALUE! using one of the methods describe in A) of the Problem Description above.

Current behaviour (3.6.2):

See Problem Description above.  I will add more comments if and when I
understand the circumstances better.

Expected behaviour:

Formulae involving add-in functions should always evaluate to the correct
result regardless of:
  a) how the formula is entered (manually or by macro)
  b) regardless of circumstances in which the containing document is opened
  c) the language of implementation (here Python)

Platform (if different from the browser): 

  Debian GNU/Linux 6.0.5 (aka Squeeze, aka Stable)

Installation media:

  LibO_3.6.1_Linux_x86-64_helppack-deb_en-GB.tar.gz
  LibO_3.6.1_Linux_x86-64_install-deb_en-US.tar.gz
  LibO_3.6.1_Linux_x86-64_langpack-deb_en-GB.tar.gz
  LibO-SDK_3.6.1_Linux_x86-64_install-deb_en-US.tar.gz
Comment 1 Forester 2012-11-08 22:11:58 UTC
With 3.6.3, behaviour A) is still present but behaviour B) appears to have gone away.

In 3.5.7, there is also behaviour A) but not B).

The problem is easier to reproduce using a python script as a macro:
{{{
import uno

def bug54854():
    ctx = uno.getComponentContext()
    desktop = ctx.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop",ctx)
    cell = desktop.getCurrentComponent().Sheets.getByName('Sheet1').getCellRangeByName("E5")
    cell.setFormula("=PAGENAME(SHEET(Sheet1.A1)-1)")

g_exportedScripts = bug54854,
}}}
Comment 2 Forester 2013-03-20 19:09:48 UTC Comment hidden (obsolete)
Comment 3 Forester 2013-05-13 19:44:35 UTC
This issue IS still present in 4.0.3. Both unwanted behaviours A and B.
Comment 4 Jacques Guilleron 2013-05-13 21:03:18 UTC
Hello Forester,

These issues don't concern directly LibreOffice. You have to expose them to the extension's author, which is Paul Bryan Roberts at http://www.pbr.org. Only him can answer to you.

Have a nice day,

Jacques Guilleron
Comment 5 Jacques Guilleron 2013-05-16 03:15:17 UTC Comment hidden (obsolete)
Comment 6 Jacques Guilleron 2013-05-16 16:46:58 UTC
Hi Forester,

I wonder if, once added, we have not to close LibreOffice and reload it. The first time I use it, without reloading, I got the same results like you.

Now, for PAGENAME and with the assistant's function, if I enter "Feuille1" with double quote in French UI, I get the correct result. By trying added sheets in this way, I get correct results.

Second function PAGEINDEX work too.

Hope this try can help,

Regards,

Jacques µGuilleron
Comment 7 Jacques Guilleron 2013-05-16 17:10:42 UTC
Sorry, please reverse the names'fuctions for correct reading.

Jacques Guilleron
Comment 8 Jacques Guilleron 2013-05-16 23:42:19 UTC
Hi Forester,

New try with LO 3.6.6.4 on Windows 7.
Install PAGENAME.oxt. Close LO. Reopen. New document Calc. Insert 3 sheets.
Select A1. With Function wizard select PAGENAME formula. Enter 0. Get: Feuille1
in A1 with French UI. Get similar results in A2 and A3 with Feuille2 and Feuille3.

In A4, select PAGEINDEX formula. Enter "Feuille1" double quoted before and after text, get 0 not displayed since "0". Similar results in A5 and A6 with 1 and 2 for "Feuille2" and "Feuille3".

Functions work perfectly in this way.

Jacques Guilleron
Comment 9 Jacques Guilleron 2013-05-17 21:52:24 UTC
Hi Forester,

Its seems that changes in cell A1 are not saved, as in 
[Bug 62938] FORMATTING: Print ranges: Rows to repeat
Try in any other cell work, except that one.
Ca
Regards,

Jacques Guilleron
Comment 10 Forester 2013-05-18 12:38:32 UTC
Salut Jacques,

Last entry of mine seems not to have taken so I will try again.

First, thank you for taking an interest in this bug.  It is 8 months since I filed it and was wondering if anyone was ever going to take notice.  I have more to contribute and will try to put something together over the weekend.

Second, thank you for trying the extension out under Windows™. I am ill equipped to do so myself.  It seems you agree that the add-in functions themselves work.  
Since the bug is still UNCONFIRMED I deduce you have not tried running the macro I supplied that shows the data entry issue.  Why ?  It is the macro, not the add-in, that shows the issue that I filed as a bug report.

A bientôt,
   Forester
Comment 11 Forester 2013-05-18 13:00:45 UTC
Salut Jacques,

The PageName extension is derived from the DoobieDoo example that can be found here:  http://wiki.openoffice.org/wiki/Calc/Add-In/Python_How-To.  The example is 4 years old and was written for the other Office but I was unable to find any other documentation that did not look a lot older and out-of-date.

You can download a zip file from the DoobieDoo page.  This contains an oxt file.  I used this to install the extension (under LibreOffice 3.5.7).  It seemed to install OK and the add-in function seem to work OK as well.

I will attach a simple python script that can be run as a macro from the UI that inserts several simple formula that call the DoobieDoo functions.  The macro does not refer to Sheet1 but you may still have to modify it yourself if your numbers use a «virugle décimal» instead of a decimal point.

When I run the script I get Err:508 for the numeric functions and Err:509 for the string functions.  As with PageName, selecting a cell containing a 'bad formula'  and entering the key sequence F2, SPC, RET causes the function to be re-entered at the UI and the error goes away.

I rebuilt the DoobiBoo oxt file under 3.5.7 using my methods.  I get the same results.  This suggests the package itself is not to blame.

The errors suggest that LibreOffice (some component thereof) is unable to parse the formulae due to a syntax error.  But there is no syntax error as the F2, SPC, RET sequence confirms.

I also infer we do not see the #NAME! error because LibreOffice has given up on a syntax error and has not got as far a trying to look up the function.  I think we do not see a syntax error with the PageName functions because they take only one parameter.  We are perhaps looking at another bug here.

One thing to note is that the first formula the script tries to enter is:
    =DOOBIEMULT(2.5,1.1)
but what I see entered in the spreadsheet is:
    =DOOBIEMULT(2.5,10.1)

where did the extra 0 come from ?  I looks to me what whatever bit of code is trying to parse the formula is having a really bad day.

Ciao
Comment 12 Forester 2013-05-18 13:02:21 UTC
Created attachment 79498 [details]
Python script to be run as a macro to illustrate the issue decrined in the previous comment.
Comment 13 Forester 2013-05-18 20:14:15 UTC
Created attachment 79505 [details]
Python macro illustrating the issue and several workarounds

Salut Jacques,

Attached is a Python macro that revisits this bug.

With the PageName.oxt installed, open a new workbook.
Create at least one sheet, select any sheet.
It does not matter what the sheets are named.

In cells E2 and E3 enter the formulae:

    =PAGENAME(A1)
    =PAGEINDEX(E2)

It is easy to see the results are correct.
Now run the attached macro.

#NAME! will appear in cells E6 and E7.
This is the bad behaviour A that the original bug report complained about.
This uses the setFormula() method of .com.sun.star.table.XCell interface.
I believe this the correct way to enter a formula using the UNO API.
It seem work for built-in functions but not add-in functions.

Correct results appear in cells E10 and E11.
This works around the bug by using a replace descriptor to change '=' to '='.
The formula are 're-entered' and interpreted correctly.
Nice to see this workaround from the days before Windows™ still works.
 
Correct results appear in cells E14 and E15.
This time the 'dispatch' interface has been used to simulate user input.
Clumsy.  It is what you would get if you use 'macro record' and then
translate the !LibreOffice Basic in Python.  Yuk.

Correct results appear in cells E18 and E19.
This uses setFormula() but uses the PageName functions' full UNO Component IDs.
I don't think this should be necessary.  It does not work form the UI.

Can you run this and confirm the bug ?

Merci d'avance,
   Forester
Comment 14 Jacques Guilleron 2013-05-19 12:24:24 UTC
Hi Forester,

With this last attachment, I see quite cleary the issue and the demonstration. Even with the wizard to validate the inserted 'bad' formulas in E6 and E7 works.
it's not easy to say what is lacking here. If I find something. Now I set status to NEW.

Regards,

Jacques Guilleron
Comment 15 Forester 2013-05-19 17:12:51 UTC
Created attachment 79532 [details]
Office Basic Example shows issue entering formulae

Salut Jacques,

Many thanks for changing the bug status to NEW.

Attached is an Basic macro that tries to enter the formula
I see the same old #NAME! issue, which suggests the issue is not Python specific.
Comment 16 Forester 2013-05-19 17:20:43 UTC
Created attachment 79533 [details]
Example showing issue with standard add-in functions
Comment 17 Forester 2013-05-19 17:26:10 UTC
Salut,

My comment that went with the last attachment has got lost in the Ether.

This example uses two functions (Dec2Bin and BintoDec) that come with LibreOffice as standard but which fall into the add-in category.  I hope you don't have to translate the function names.

The script also shows the #NAME! issue.  I dont't think these functions are implemented in Python.

I saved the workbook with the two formula entered by hand and the two entered by script still showing #NAME! and had a peek at the contents:

unzip bug54854.ods
xmllint --format content.xml

Most interesting.

By for now.
   Forester
Comment 18 Forester 2014-02-24 21:41:19 UTC
Problems persists in 4.0.6, 4.1.5 and 4.2.0.
Comment 19 QA Administrators 2015-06-08 14:41:56 UTC Comment hidden (obsolete)
Comment 20 Forester 2015-10-21 11:55:25 UTC
Dear LibreOffice QA Team,

I can confirm that bad behaviour A occurs with LibreOffice 4.4.5 and 5.0.2.  I did not observe behaviour B but B has gone and returned before.

I managed to install LibreOffice 3.3.0 and observed both behaviour A and B so I have changed the version number to "inherited from OOo" as requested.  I do hope this does not mean you will declare the bug a feature and encourage me to abandon LibreOffice in favour the Apache version.

While I reported this as a problem using Python, I did as long ago as 2013 prove that it also occurs with Office Basic and 'standard' add-in functions.

Naturally I would update the status of this bug report if the bug magically fixed itself but, on account of even less progress on another bug, I still use LibreOffice 3.5.7 so I'm unlikely to notice.

Should I put this bug down on my list of things to do when I retired in 2025 ?

Happy hacking.
Comment 21 Olivier Hallot 2015-12-14 17:21:46 UTC
This may be related to bug 79588
Comment 22 Forester 2016-02-13 09:07:41 UTC
Thanks for the suggestion and apologies for taking so long to look into it.

Is bug 79588 related ?

Using the classic (Aristotelian) model of inquiry we have:
  * both have cells showing #NAME!
  * both involve add-in functions
  * this bug is apparent when first entering an add-in function
    * bug 79588 is apparent only when the workbook is re-opened
  * this bug, once an add-in function has been entered correctly, #NAME! does
    not reappear no matter how many times the workbook is closed and reopened
  * this bug concerns add-ins written in Python
    * bug 79588 concerns add-in written in Basic

Let's try the pragmatic (Modern) model of inquiry and re-examine what we already
know about this bug.

1.  Not a load issue

See comments 13/14.

Here you are asked to install the add-in and enter two function calls at the
UI.  This is successful so I infer any loading of libraries has taken place.

Then you are asked to run a macro that shows the #NAME! problem.

2.  Not a Python or a Must-Be-The-User issue

Comment 15 refers to a LibreOffice Basic macro example that shows the problem.
The attachment is not quite as I intended but it is easy to see what was needed.

Comments 16/17 refer to a Python macro that uses add-in functions that came with
LibreOffice that shows the #NAME problem.  As a wild guess, these add-functions
are not implemented in Python.

This attachment is not at all what I intended.  No matter - the functions used
are add-in functions in !LibreOffice 3.5 but not in 5.0 so some productive
tester using 5.0 might have declared "works for me" and closed the bug.


I program Python and perhaps I know a little about how to debug code but when it
comes to LibreOffice, I am a user of an API.  My inquiries have gone as far as
they can without someone who can debug LibreOffice code providing some assistance.


If one looks at it this way ... comments 13/14:
  * enter =functionname(parameters) at the UI gives us what is wanted
  * use a macro to enter = unoComponentId.functionname(parameters) likewise
  * use a macro to enter =functionname(parameters) gives #NAME!
  * enter = unoComponentId.functionname(parameters) at UI gives #NAME! too

Notice the inconsistency ?

I disassembled a workbook and found that, internally, LibreOffice stores
= unoComponentId.functionname(parameters).

It seems LibreOffice can do the conversion but doesn't when using a macro to do
data entry.  It seems like the code exists but just isn't being called on this
particular execution path.  Hypothesis:  Frame-Controller-Model.
Comment 23 QA Administrators 2017-03-06 15:04:26 UTC Comment hidden (obsolete)
Comment 24 fcyrilf 2017-05-16 09:47:15 UTC
Same bug here.
I've tested it with my file : I've doing a VB macro which I've call in a formula and when I save and reopen the .ods document, the formula isn't recognized anymore. The only work-around I've found is to edit the macro (for example add a space or remove a space) and it work again.

LibreOffice tested : 5.2.6, 5.2.7 and 5.3.2.
Comment 25 fcyrilf 2017-05-16 09:49:37 UTC
Same bug here.
I've tested it with my file : I've doing a VB macro which I've call in a formula and when I save and reopen the .ods document, the formula isn't recognized anymore. The only work-around I've found is to edit the macro (for example add a space or remove a space) and it work again for the session.

LibreOffice tested : 5.2.6, 5.2.7 and 5.3.2.
Comment 26 QA Administrators 2018-07-23 02:32:25 UTC Comment hidden (obsolete)
Comment 27 QA Administrators 2020-07-23 03:55:45 UTC
Dear Forester,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug