Bug 115983 - findAll and replaceAll methods have an unexpected behavior when they are called from the com.sun.star.sheet.SheetCellRanges service.
Summary: findAll and replaceAll methods have an unexpected behavior when they are call...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: framework (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Find-Search Cell-Selection
  Show dependency treegraph
Reported: 2018-02-24 00:01 UTC by vatairethibault
Modified: 2023-05-17 23:49 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

A piece of code that reproduce the problem. (1.86 KB, text/x-python)
2018-02-24 00:07 UTC, vatairethibault
Screenshot of result (8.44 KB, image/png)
2018-03-10 08:18 UTC, Buovjaga

Note You need to log in before you can comment on or make changes to this bug.
Description vatairethibault 2018-02-24 00:01:19 UTC
When the findAll or replaceAll methods are called from the com.sun.star.sheet.SheetCellRanges service, the result include more cells than expected if the ranges of the collection are spread over several sheets.

Steps to Reproduce:
Hereafter, a sample of code that reproduces the problem.
Step to reproduce :
1. quit LibreOffice.
2. save the script into the Scripts/python path of your user profile
3. execute the script with the command : <libo_install_path>/program/soffice 'vnd.sun.star.script:cellranges_replace.py$replace?language=Python&location=user'

Actual Results:  
If the following ranges are added to an empty SheetCellRanges collection :
- '$asheet'.$A$1:$B$2
- '$anothersheet'.$B$3:$C$4,
the searched value is found (and replaced) on the following ranges :
- '$asheet'.$A$1:$B$2
- '$anothersheet'.$B$3:$C$4
- '$anothersheet'.$A$1:$B$2
- '$asheet'.$B$3:$C$4
whereas the last two ranges are not part of the collection.

Expected Results:
The scope of the findAll and replaceAll methods should be restricted to the ranges that are parts of the collection.

Reproducible: Always

User Profile Reset: Yes

Additional Info:
This bug yet exists in LibreOffice 6.0.1.

A workaround is to browse ranges in the collection and call the findAll / replaceAll methods on each of theses ranges.

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0
Comment 1 vatairethibault 2018-02-24 00:07:57 UTC
Created attachment 140105 [details]
A piece of code that reproduce the problem.
Comment 2 Buovjaga 2018-03-08 13:57:57 UTC
I get this error when I run it:
Message: <class 'pythonscript.com.sun.star.ucb.InteractiveAugmentedIOException'>: an error occurred during file opening
  File "/home/user/libreoffice/instdir/program/pythonscript.py", line 998, in getScript
    mod = self.provCtx.getModuleByUrl( fileUri )
  File "/home/user/libreoffice/instdir/program/pythonscript.py", line 435, in getModuleByUrl
    lastRead = self.sfa.getDateTimeModified( url )

What should I do?
Comment 3 vatairethibault 2018-03-09 09:06:25 UTC
Hi Buovjaga,

I reproduce your error when the location of the script is not the expected one.
Can you check the location of the script in your user profile ?
It must be exactly "Script/python".
This path is case sensitive so "Script/Python" is not recognized by LibreOffice for example.
Comment 4 Buovjaga 2018-03-09 09:24:26 UTC
Now I tried to force the path as absolute, but it just opens LibreOffice start center and nothing happens:

How can I force it?
Comment 5 vatairethibault 2018-03-09 19:02:32 UTC
I'm not sure an absolute path is allowed.
The given path seems to be the shared profile of your build.
May be you can try 'location=shared' instead of 'location=user' in the command.

Otherwise try this way :
- open LibreOffice
- go to Tools > Macros > Organize macros > Python...
- you should see the cellranges_replace script either in 'My macros' or in 'LibreOffice macros'
- select the replace method and click on 'Run'.
Comment 6 vatairethibault 2018-03-09 20:37:02 UTC
I made a mistake in my previous message : it's not 'location=shared' but 'location=share'
Comment 7 Buovjaga 2018-03-10 08:18:08 UTC
Created attachment 140528 [details]
Screenshot of result

Thanks, location=share worked! Attached is the result, is it the incorrect one?

Arch Linux 64-bit
Build ID: b8fe96f1da2c42c04a8094ca8c57d49763b7bded
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on March 8th 2018
Comment 8 Buovjaga 2018-03-10 08:18:43 UTC
Oh, right, it gives this output in the terminal:
target collection : $asheet.$B$2:$C$3;$anothersheet.$F$6:$G$7
findAll collection : $asheet.$B$2:$C$3;$asheet.$F$6:$G$7;$anothersheet.$B$2:$C$3;$anothersheet.$F$6:$G$7
findAll count : 16
replaceAll count : 16
Comment 9 vatairethibault 2018-03-10 11:14:01 UTC
Yes that's the incorrect result.
The terminal output shows there is two ranges on two different sheets in the collection, $asheet.$B$2:$C$3 and $anothersheet.$F$6:$G$7,
but on the attached result the ranges B2:C3 and F6:G7 are replaced on the same sheet.
Comment 10 QA Administrators 2019-03-11 03:47:49 UTC Comment hidden (obsolete)
Comment 11 vatairethibault 2019-03-27 20:46:06 UTC
This bug yet exists in LibreOffice 6.2.2 :

User Profile Reset: Yes

Informations from 'Help - About LibreOffice' :
Build ID: 2b840030fec2aae0fd2658d8d4f9548af4e3518d
Threads CPU : 4; OS : Linux 4.9; UI Render : par défaut; VCL: gtk3; 
Locale : fr-FR (fr_FR.utf8); Langue IHM : fr-FR
Calc: threaded
Comment 12 QA Administrators 2021-03-27 04:07:22 UTC Comment hidden (obsolete)
Comment 13 QA Administrators 2023-03-28 03:20:27 UTC Comment hidden (obsolete)
Comment 14 vatairethibault 2023-05-17 23:49:27 UTC
This bug still exists in LibreOffice 7.5.3

User Profile Reset: Yes

Version: (X86_64) / LibreOffice Community
Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3
CPU threads: 5; OS: Linux 5.10; UI render: default; VCL: gtk3
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded