Bug 117563 - LibreOffice Calc: Links to External Files - Linked Ranges are saved incorrectly - Update Links overwrites data and removes links
Summary: LibreOffice Calc: Links to External Files - Linked Ranges are saved incorrect...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-External-Datalink
  Show dependency treegraph
 
Reported: 2018-05-11 14:16 UTC by KSN
Modified: 2018-09-10 16:23 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Contains source, destination and screenshot files as mentioned in the bug report (132.37 KB, application/zip)
2018-05-11 14:23 UTC, KSN
Details
Contains source, destination and screenshot files as mentioned in the bug report (132.37 KB, application/zip)
2018-05-11 14:26 UTC, KSN
Details

Note You need to log in before you can comment on or make changes to this bug.
Description KSN 2018-05-11 14:16:17 UTC
Description:
In LibreOffice Calc, it is possible to insert links to Named Ranges in external files, which can then be updated when the file is opened or updated on demand.

This functionality was working in versions 4.2.5.2 through to at least 5.0.6.3.

From at least Calc version 5.3.0.1 (not able to determine from which version  the problem started), the linked ranges (can be seen using Navigator <F5>-->Linked Ranges) are being incorrectly stated, leading to overwriting of data from other links.

This functionality was being used to consolidate Time Sheets of 35 employees, but with a recent upgrade of LibreOffice to version 6.0.2.1, we are unable to get the correct data as many employees time data is overwritten by ranges containing other employees time data.

Steps to Reproduce:
1. Take the files (File1.ods to File6.ods and Conso.ods) sent with this bug report and copy them to a folder
2. Open the file Conso.ods
3. DO NOT update the links when the dialog appears
4. Click on Menu: Edit-->Links (version 5.3) or Links to External files.. (version 6.0)
5. You will see 6 files to be updated (Screenshot1).
6. Select all files, using the Shift key
7. Click Update

Actual Results:  
1. After update, only 4 files will show in the dialog box
2. Link to Files 3 and 4 have disappeared
3. Data of Files 3, 4 has been completely overwritten and data of File5 has only 2 lines.  File 6 data has come in, but at the wrong position.

The Linked Ranges have changed after the saving of the file.

Thus, the Linked Ranges show as:

1. For File1: A2:B3001 (incorrect)
2. For File2: A4001:B10999 (incorrect)
3. For File3: A8001:B18999 (incorrect)
4. For File4: A12001:B26999 (incorrect)
5. For File5: A16001:B34999 (incorrect)
6. For File6: A20001:B42999 (incorrect)

Expected Results:
In each of the files File1.ods to File6.ods, the range "Data" covers lines A2:B3000 (2999 lines).

The linked ranges are as follows:
1. Cell A2: Link to File1.ods (Range "Data")
2. Cell A4001: Link to File2.ods (Range "Data")
3. Cell A8001: Link to File3.ods (Range "Data")
4. Cell A12001: Link to File4.ods (Range "Data")
5. Cell A16001: Link to File5.ods (Range "Data")
6. Cell A20001: Link to File6.ods (Range "Data")

In "Navigator" <F5>, double clicking on the "Data" of each of the Linked Ranges should show the starting and ending cells as given in the enclosed file "CellRanges.ods".

Thus, Linked Ranges show as:

1. For File1: A2:B3000
2. For File2: A4001:B6999
3. For File3: A8001:B10999
4. For File4: A12001:B14999
and so on for File5 and File6


Reproducible: Always


User Profile Reset: No



Additional Info:
Appears to be similar to Bug Report 106074 and 76047, though this problem is restricted to link to .ods files and the destination file is also a .ods file

What appears to be happening is that the Linked Ranges are re-defined during the Save File process and the LAST row of the Linked range becomes:

"Starting Range Row number" + "Starting Range Row number" + "Rows of the Imported Range" - 2:
e.g. 
For the first range (File1), this becomes:
2 + 2 + 2999 -2 = 3003 -2 = 3001
Thus, the range becomes:
A2:B(3003-2) i.e. A2:B3001

For the second range (File2), this becomes:
4001 + 4001 + 2999 -2 = 11001 -2 = 10999
Thus, the range becomes:
A4001:B(11001-2) i.e. A4001:B10999

For the third range (File3), this becomes:
8001 + 8001 + 2999 -2 = 18999
Thus, the range becomes:
A8001:B(19001-2) i.e. A8001:B18999

and so on...

This can be seen by double-clicking on the File "ConsoOnFirstSave.ods" and going to the Linked Ranges (Navigator or <F5>) and selecting each of the ranges.



User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.139 Safari/537.36
Comment 1 KSN 2018-05-11 14:23:15 UTC
Created attachment 142033 [details]
Contains source, destination and screenshot files as mentioned in the bug report
Comment 2 KSN 2018-05-11 14:26:40 UTC Comment hidden (obsolete)
Comment 3 Korrawit Pruegsanusak 2018-05-12 04:55:50 UTC Comment hidden (obsolete)
Comment 4 Buovjaga 2018-06-14 16:20:08 UTC
(In reply to KSN from comment #0)
> This functionality was working in versions 4.2.5.2 through to at least
> 5.0.6.3.

Not sure what is up with this, but I can reproduce the problem
- on Linux with 3.3.0, 3.6.7.2
- on Windows with 3.5.0, 4.3.0, 4.4.7.2, 5.0.2.2...

+ master
Comment 5 KSN 2018-06-29 11:55:51 UTC Comment hidden (no-value)
Comment 6 KSN 2018-07-19 04:51:04 UTC Comment hidden (no-value)
Comment 7 KSN 2018-07-30 04:14:09 UTC Comment hidden (no-value)
Comment 8 Xisco Faulí 2018-09-10 16:23:37 UTC
> Actual Results:  
> 1. After update, only 4 files will show in the dialog box
> 2. Link to Files 3 and 4 have disappeared
> 3. Data of Files 3, 4 has been completely overwritten and data of File5 has
> only 2 lines.  File 6 data has come in, but at the wrong position.


After updating the link, I still see 6 files in the external links dialog

Version: 6.2.0.0.alpha0+
Build ID: 433fce6571d4b9121374047324a7d2d2722ac3e4
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); Calc: threaded

and

Versió: 6.0.6.2
ID de la construcció: 1:6.0.6-0ubuntu0.16.04.1
Fils de CPU: 4; SO: Linux 4.15; Renderitzador de la IU: per defecte; VCL: gtk3; 
Configuració local: ca-ES (ca_ES.UTF-8); Calc: group

@KSN, which version of LibreOffice are you using ?