Bug 133536 - Links to external files in a Calc table integrated in a Writer document can no longer be updated (steps in comment 11)
Summary: Links to external files in a Calc table integrated in a Writer document can n...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: LibreOffice (show other bugs)
Version:
(earliest affected)
6.3.0.4 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bisected, regression
Depends on:
Blocks: OLE-Objects Calc-External-Datalink
  Show dependency treegraph
 
Reported: 2020-05-30 23:42 UTC by Lothar Viel
Modified: 2024-05-06 12:39 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Example Writer document with integrated Calc table (18.52 KB, application/vnd.oasis.opendocument.text)
2020-05-30 23:47 UTC, Lothar Viel
Details
Calc table with external data (9.77 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-05-30 23:48 UTC, Lothar Viel
Details
Example Writer document with integrated Calc spreadsheet (18.54 KB, application/vnd.oasis.opendocument.text)
2020-12-22 16:07 UTC, Lothar Viel
Details
Calc spreadsheet with external data (8.88 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-12-22 16:09 UTC, Lothar Viel
Details
Example Writer document with integrated Calc spreadsheet (18.59 KB, application/vnd.oasis.opendocument.text)
2020-12-22 17:01 UTC, Lothar Viel
Details
Example Writer document with integrated Calc spreadsheet (13.99 KB, application/vnd.oasis.opendocument.text)
2020-12-23 21:32 UTC, Lothar Viel
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Lothar Viel 2020-05-30 23:42:10 UTC
Description:
If a Calc table is integrated into a Writer Document and there is a link to another, external Calc table containing data, this link can no longer be updated.

Steps to Reproduce:
In the attached example writer document ('Writer Document with integrated Calc Table.odt') a small Calc table is integrated.
In field B1 of this table, a number (here in the example 2, 3, 4 or 5) can be entered that refers to a corresponding row number of the external Calc table ('Calc Table with external Data.ods'). 
In fields B2 and B3 of the integrated Calc table, the INDEX function is used to access and display the values of the external Calc table. Accordingly, field B2 displays the value of the row number of column A of the external Calc table entered in B1. Field B3 displays the value of column B accordingly.

Actual Results:
If one or more values in the external Calc table are now changed, the Calc table integrated in the Writer document will initially display the previous value(s). 
If you click on "Edit", "Link to external files..." and "Update", the changed values from the external Calc table should also be displayed in the integrated Calc table. But this is not the case from version 6.2.8 (up to the latest version 7.0.0.0). The previous values are still displayed and there is no possibility to display the changed, current values of the external Calc table.

Expected Results:
This worked fine until LibreOffice version 6.2.5.2! 
The versions 6.2.6 and 6.2.7 were not tested by us.


Reproducible: Always


User Profile Reset: Yes



Additional Info:
none
Comment 1 Lothar Viel 2020-05-30 23:47:08 UTC
Created attachment 161444 [details]
Example Writer document with integrated Calc table
Comment 2 Lothar Viel 2020-05-30 23:48:22 UTC
Created attachment 161445 [details]
Calc table with external data
Comment 3 Buovjaga 2020-11-11 10:33:31 UTC
I tried to test, but when I modify the file link to

=INDEX('file:///home/user/libobugs/vboxshare/Calc Table with external Data.ods'#$Tabelle1.$A$1:$A$10;B1)

...on my Linux system, I get #REF! as the result. Please help me, if you can.
Comment 4 Martin Srdoš 2020-12-08 10:52:15 UTC
Please, write step by step how did you make the tables.

I did Calc tables and then I copied to writer by ctrl+shift+V. Then I am editing the original calc document and after save documents it works good also in writer. However I made my own files.

Also check that on actual version.

Version: 7.2.0.0.alpha0+ (x64)
Build ID: 4e63ec27b69fa01ff610c894c9fbf05c377a6179
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win
Locale: cs-CZ (cs_CZ); UI: en-US
Calc: CL
Comment 5 Lothar Viel 2020-12-22 16:07:35 UTC
Created attachment 168415 [details]
Example Writer document with integrated Calc spreadsheet
Comment 6 Lothar Viel 2020-12-22 16:09:19 UTC
Created attachment 168416 [details]
Calc spreadsheet with external data
Comment 7 Lothar Viel 2020-12-22 16:11:13 UTC
(In reply to Martin Srdoš from comment #4)
> Please, write step by step how did you make the tables.
> 
> I did Calc tables and then I copied to writer by ctrl+shift+V. Then I am
> editing the original calc document and after save documents it works good
> also in writer. However I made my own files.
> 
> Also check that on actual version.
> 
> Version: 7.2.0.0.alpha0+ (x64)
> Build ID: 4e63ec27b69fa01ff610c894c9fbf05c377a6179
> CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL:
> win
> Locale: cs-CZ (cs_CZ); UI: en-US
> Calc: CL

Dear Martin.
First of all, I would like to apologise for responding so late. But unfortunately I have had so much to do in the last few weeks that I haven't found the time.

My example involves two different Calc spreadsheets. One is integrated in a Writer document and should display the corresponding value of an external Calc spreadsheet file after entering a row number. The second, external spreadsheet file thus serves as a database that is to be accessed from the integrated Calc spreadsheet with the help of an INDEX command.


Error description:
If a Calc Spreadsheet is included in a Writer document and there is a link to another, external Calc Spreadsheet with data, this link can no longer be updated.

Preparations:
Please save the again simplified two documents 'Writer Document with integrated Calc Spreadsheet.odt' and 'Calc Spreadsheet with external data.ods' somewhere on your WINDOWS PC.

Steps to reproduce the error:
Open the Writer document and then also the integrated Calc spreadsheet.
In field B1 of this small spreadsheet, a number (here in the example 2, 3, 4 or 5) can be entered that refers to a corresponding row number of the external Calc spreadsheet ('Calc Spreadsheet with external data.ods'). 
In the field B2 of the integrated Calc spreadsheet, the values of the external Calc spreadsheet are accessed and displayed with the function INDEX. Accordingly, the field B2 shows the value of the row number of column A of the external Calc spreadsheet, which is entered in B1.

I.e. if, for example, the number 2 is entered in field B1 of the integrated Calc spreadsheet, the value 200 must be displayed in field B2 as it is stored in row 2, column A of the external Calc spreadsheet. If, for example, the number 3 is entered in B1 of the integrated spreadsheet, the value 300 must appear in field B2 and so on.

If one or more values of column A in the external Calc spreadsheet are modified, the integrated Calc spreadsheet in the Writer document still shows the previous values because the link to the external spreadsheet is not updated automatically. Even if the Writer document including integrated Calc spreadsheet is saved, closed and reopened, field B2 of the integrated Calc spreadsheet still shows the old values from the external spreadsheet.
To update the link of the integrated Calc spreadsheet with the now changed external Calc spreadsheet, one has to click on 'Edit' with the integrated Calc Spreadsheet open, then on 'Links to External Files...', then on 'Modify...' and now select the external Calc Spreadsheet 'Calc Spreadsheet with external Data' and click on 'Open'. Finally click on 'Update' and now the selected file including path should be displayed in the line 'Source file'. Close the window by clicking on 'Close'.
Now the modified values of the external Calc spreadsheet should be displayed in the integrated Calc spreadsheet. 

This update of the link to the external Calc spreadsheet still worked properly up to LibreOffice version 6.2.5, but from version 6.2.8 up to the latest version 7.1.0.0 beta1(x64) this no longer works! The previous values are still displayed and there is no possibility to display the modified, current values of the external Calc spreadsheet. I do not have the LibreOffice versions between version 6.2.5 and 6.2.8, so I cannot determine exactly from which version onwards the updating of the link to the external Calc spreadsheet no longer works.

If you want to test updating the link again with the old LibreOffice version 6.2.5 but no longer have access to it, I can send you a link to download from my cloud (64 bit version).
Comment 8 Lothar Viel 2020-12-22 17:01:13 UTC
Created attachment 168421 [details]
Example Writer document with integrated Calc spreadsheet

I have simplified the example Writer document with integrated Calc spreadsheet again to better understand the error.
Comment 9 Lothar Viel 2020-12-22 17:10:11 UTC
The easiest way to reproduce the behaviour I have described is to simply copy the two documents ('Example Writer document with integated Calc spreadsheet.odt' and 'Calc spreadsheet with external Data.ods') to the root directory of drive D: and open the Writer document from there. Otherwise, the path to the external Calc spreadsheet in cell B2 of the integrated Calc document must be adjusted accordingly in the INDEX command.
Comment 10 Martin Srdoš 2020-12-22 19:50:33 UTC
Yes, it shows just previous values. I download only writer document and the values are there. Without calc source.

Which way do you creating the integrated spredsheet? Just copy the table and paste (ctrl+shift+v) as spredsheet? For me it works same badly also in the old version 6.2.5.

When I create it, it works, but when I save everything, close and open, it stop work. Also in 6.2.4.

Please tel us how do you create the tables in writer. I don't know, if it is important, but maybe would. Probably we don't want test existing documents in different versions, but we want to test creating such documents.

P.S. Please upload again the writer document. It was uploaded as patch and it is not possible to download it.
Comment 11 Lothar Viel 2020-12-23 21:32:58 UTC
Created attachment 168459 [details]
Example Writer document with integrated Calc spreadsheet

I have uploaded the example Writer document with the integrated Calc spreadsheet again and not marked it as 'patch'.

I created the Calc spreadsheet integrated in the Writer document as follows:
Click on 'Insert' in the open Writer document, then on 'Object', on 'OLE Object...' and on 'LibreOfficeDev 7.1 Spreadsheet'.
In the Calc Spreadsheet thus created, enter a number between 2 and 5 in cell B1 and the following function in cell B2:

=INDEX('file:///D:/Calc Spreadsheet with external Data.ods'#$Tabelle1.$A$1:$A$10;B1)

This should cause the corresponding value from the external Calc spreadsheet to appear in cell B2: 200, 300, 400 or 500, depending on the number in cell B1. The prerequisite is, of course, that the Calc spreadsheet with the external values is located in the path specified in the INDEX function.

Afterwards, please save the Writer document with the integrated Calc spreadsheet under any name and close it. 

Now open the Calc spreadsheet with the external values and change one or more values in column A (e.g. in cell A3 the number 300 to 333). Then save and close the spreadsheet and open the Writer document and the integrated Calc spreadsheet again and enter the number 3 in cell B1 (confirm with Enter, of course), then the previous value 300 is still displayed in cell B2, as the link to the external calc spreadsheet is not automatically updated. 

To update this link, you have to click on 'Edit', 'Link to External Files...' and 'Update', as I already described yesterday. If the path displayed under 'Sourcefile' does not correspond to the actual path of the Calc spreadsheet, the correct path must be selected with the button 'Modify...' before clicking on 'Update'. Finally, click on 'Close'. 

Now the modified value, namely 333, should actually be displayed in cell B2, but this is not the case! The previous value 300 is still displayed!

Just now I found out something very interesting:

If a new calc spreadsheet is created in a Writer as described above, updating the link to the external calc spreadsheet works absolutely perfectly if the update is carried out as described above ('Edit', Link to External Files...'). However, if the Writer document with the integrated calc spreadsheet is saved and closed and then opened again, the updating of the link no longer works!
Comment 12 QA Administrators 2020-12-24 03:54:03 UTC Comment hidden (obsolete)
Comment 13 Buovjaga 2020-12-24 06:12:41 UTC
(In reply to Lothar Viel from comment #11)
> Created attachment 168459 [details]
> Example Writer document with integrated Calc spreadsheet
> 
> I have uploaded the example Writer document with the integrated Calc
> spreadsheet again and not marked it as 'patch'.

You didn't have to do that as I just changed the mime type of the one you uploaded as patch and unchecked the patch checkbox. You can edit the details of your attachments after the fact and here you can see a cheatsheet of mime types: https://wiki.documentfoundation.org/QA/Bugzilla/Attachments#Current_Manual_Fixing
Comment 14 Buovjaga 2020-12-24 15:47:59 UTC
(In reply to Lothar Viel from comment #11)
> I created the Calc spreadsheet integrated in the Writer document as follows:
> Click on 'Insert' in the open Writer document, then on 'Object', on 'OLE
> Object...' and on 'LibreOfficeDev 7.1 Spreadsheet'.
> In the Calc Spreadsheet thus created, enter a number between 2 and 5 in cell
> B1 and the following function in cell B2:
> 
> =INDEX('file:///D:/Calc Spreadsheet with external
> Data.ods'#$Tabelle1.$A$1:$A$10;B1)
> 
> This should cause the corresponding value from the external Calc spreadsheet
> to appear in cell B2: 200, 300, 400 or 500, depending on the number in cell
> B1. The prerequisite is, of course, that the Calc spreadsheet with the
> external values is located in the path specified in the INDEX function.
> 
> Afterwards, please save the Writer document with the integrated Calc
> spreadsheet under any name and close it. 
> 
> Now open the Calc spreadsheet with the external values and change one or
> more values in column A (e.g. in cell A3 the number 300 to 333). Then save
> and close the spreadsheet and open the Writer document and the integrated
> Calc spreadsheet again and enter the number 3 in cell B1 (confirm with
> Enter, of course), then the previous value 300 is still displayed in cell
> B2, as the link to the external calc spreadsheet is not automatically
> updated. 
> 
> To update this link, you have to click on 'Edit', 'Link to External
> Files...' and 'Update', as I already described yesterday. If the path
> displayed under 'Sourcefile' does not correspond to the actual path of the
> Calc spreadsheet, the correct path must be selected with the button
> 'Modify...' before clicking on 'Update'. Finally, click on 'Close'. 
> 
> Now the modified value, namely 333, should actually be displayed in cell B2,
> but this is not the case! The previous value 300 is still displayed!

Thanks, I reproduce with the steps.

The link updating must be attempted while still editing the OLE object.

Arch Linux 64-bit
Version: 7.2.0.0.alpha0+
Build ID: b36a40a809206a57160108b3361f42420f619c2e
CPU threads: 8; OS: Linux 5.9; UI render: default; VCL: kf5
Locale: fi-FI (fi_FI.UTF-8); UI: en-US
Calc: threaded
Built on 23 December 2020
Comment 15 Buovjaga 2020-12-24 15:56:38 UTC
Bibisected with linux-64-6.3 to
https://git.libreoffice.org/core/commit/c357d5fbb0f2191577a1eae0a03f68e4cf51e0ba
Postpone loading of all external references, including INDIRECT()

Adding Cc: to Eike Rathke

To bibisect, I just kept opening the .odt I created from scratch according to the steps, double-clicked the embedded Calc object, selected Edit - Links to External Files and clicked Update.
Comment 16 QA Administrators 2022-12-25 03:21:38 UTC Comment hidden (obsolete)
Comment 17 Arundel 2024-03-20 23:57:31 UTC
I have a very similar problem, but with the linking of cells from a main sheet to cells in an embedded OLE sheet. I use the terms "source cell" as the source cell in the main sheet, and "destination cell" as the destination cell in the embedded OLE sheet.

I used two methods to set up the link: the GUI, and the Paste Special > Link. 
1) With the GUI method, I enter the equals sign in the inputbar for the destination cell, then select a source from the main sheet with some text, and press enter. Some file URL syntax is generated in the inputbar.
2) With Paste Special method, I select the source cell in the main sheet, copy, then Paste Special > Link for the destination cell. Some file URL syntax is generated in the inputbar.

On freshly inserted OLE's with only one destination cell set up with method 1: 
1) the destination cell's value is correct.
2) Edit > Links to external files... > Update can update the destination cell's value indefinitely.

After a reload: I encounter different destination values, all conveying the same outcome (the links not working); when I redirect the link to another source cell through method 1 or 2, the destination cell's value becomes 0; and when I press Edit > Links to external files... > Update, no update happens to the destination cell's value.

I even witnessed after I linked, the destination cell's value becoming an old version of the source cell's. But I haven't had the time to find the reproduction steps for this.

Also, as my understanding of what is supposed to happen when entering the OLE edit mode comes from OpenOffice, there is a major step missing that I never witness in LibreOffice: the prompt for updating the links. OpenOffice always shows such prompt. So even if there would not be the mentioned bug, the prompt missing is also very annoying as you would need to manually update them.

My GUI is in Dutch so I have translated some terms. Both OpenOffice and LibreOffice have this problem, and it seems to me that you need very similar steps to reproduce the bug in both. I used OpenOffice v4.1.15 and LibreOffice v7.6.5.2. The main sheet has been saved, with both programs at these versions. 

Version: 7.6.5.2 (X86_64) / LibreOffice Community
Build ID: 38d5f62f85355c192ef5f1dd47c5c0c0c6d6598b
CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: nl-NL (nl_NL); UI: nl-NL
Calc: threaded

This bug is critical as embedded modularity is non-existent for my sheets. I was so happy to break free from the main grid by embedding another grid (OLE), but then I discovered this.. Please fix it!

OP, does your bug still occur? Who can reproduce mine?
Comment 18 Heiko Tietze 2024-05-06 10:57:08 UTC
*** Bug 160954 has been marked as a duplicate of this bug. ***