Bug 58531 - FILEOPEN: Reload no longer refreshes external links in 4.0
Summary: FILEOPEN: Reload no longer refreshes external links in 4.0
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.0.beta2
Hardware: Other Windows (All)
: medium normal
Assignee: Kohei Yoshida
QA Contact:
URL:
Whiteboard: BSA target:4.1.0 target:4.0.0.2
Keywords: regression
Depends on:
Blocks: mab4.0
  Show dependency treegraph
 
Reported: 2012-12-19 18:39 UTC by Yi Ding
Modified: 2013-11-16 22:52 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
test csv file (14 bytes, text/csv)
2013-01-04 18:45 UTC, Yi Ding
Details
test ods file (7.86 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-01-04 18:45 UTC, Yi Ding
Details
test1.ods (7.37 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-01-08 16:02 UTC, Yi Ding
Details
test2.ods (7.51 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-01-08 16:02 UTC, Yi Ding
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Yi Ding 2012-12-19 18:39:05 UTC
I have a ODS file that references a CSV for certain values.  The CSV is modified dynamically by another process and I'm able to use the reload button in the file menu to import the new values from the CSV.

Unfortunately, in 4.0 I can no longer do this.  Similarly, going to the Links button in the Edit Menu and clicking refresh also does nothing.  The only way to get the new values from the CSV is to close and reopen the spreadsheet.
Operating System: Windows 7
Version: 4.0.0.0.beta1
Last worked in: 3.6.4.3 release
Comment 1 m.a.riosv 2012-12-19 23:38:12 UTC
Hi Yi,
no issue here with:
Win7x64 Ultiimate.
LibreOffice Version 4.0.0.0.beta1 (Build ID: 87906242e87d3ddb2ba9827818f2d1416d80cc7)

verify your options in Men/Tools/Options/LibreOffice calc/General - updating.
Comment 2 m.a.riosv 2012-12-21 01:17:40 UTC
Hi Yi,

Have you trying resetting the user profile?
Sometimes solve strange issues.
https://wiki.documentfoundation.org/UserProfile
Comment 3 Yi Ding 2013-01-04 18:45:14 UTC
I tried resetting my user profile, but it did not change anything.  I've attached a few test files (you might have to change the links on your machine to match your directory structure)

Basically, what you can do is open test.ods, then modify test.csv to something else.  In 3.6 test.ods would update once you hit file->reload.  This no longer happens in 4.0
Comment 4 Yi Ding 2013-01-04 18:45:41 UTC
Created attachment 72517 [details]
test csv file
Comment 5 Yi Ding 2013-01-04 18:45:52 UTC
Created attachment 72518 [details]
test ods file
Comment 6 Yi Ding 2013-01-04 18:48:30 UTC
In fact, if you just open test.ods on your computer, you'll see that test.ods doesn't even update the first time under 4.0 (I've checked with beta 2 and it's the same) leaving the bottom right cell a 1 instead of a 5.
Comment 7 m.a.riosv 2013-01-04 21:35:55 UTC
Hi Yi,
Doing a hard recalculation (Ctrl+Shif+F9), open the import box for csv files and then update, while in 3.6.4.3 the import box for csv is open when accept update at opening the file.

After a little research, I have found that changing the new option in:
Menu/Tools/Options/LOdev calc/formula - Recalculation on file load - ODF spreadsheet (not saved by LibreOffice) to always recalculate solve the issue.

But I think the issue is that does not work with the option Prompt user. So the bug is there.

On other hand, import data from csv I think is better trough Menu/Insert/Sheet or Sheet from file, because in this way you do not need to worry about how many rows are in the csv, and the new option do not interfere with the update.
Comment 8 Yi Ding 2013-01-04 21:48:50 UTC
Thanks for looking into this Marios.  It looks like the default setting for that option is "Never recalculate" which may be preferable for formulas that reference in-workbook values but definitely not (IMHO) for ones that reference other files.  Would this also affect worksheet links then between ODS files?  I think those get used by a lot of (power) users.

I also filed a similar bug about DDEs breaking after loading.  Maybe it's due to the same setting? bug 59032
Comment 9 Kohei Yoshida 2013-01-08 15:42:25 UTC
(In reply to comment #7)
> Hi Yi,
> Doing a hard recalculation (Ctrl+Shif+F9), open the import box for csv files
> and then update, while in 3.6.4.3 the import box for csv is open when accept
> update at opening the file.
> 
> After a little research, I have found that changing the new option in:
> Menu/Tools/Options/LOdev calc/formula - Recalculation on file load - ODF
> spreadsheet (not saved by LibreOffice) to always recalculate solve the issue.
> 
> But I think the issue is that does not work with the option Prompt user. So
> the bug is there.

That's in itself not a bug. The option is intended for ODS documents *not* saved by LibreOffice.  If you open one that's last saved by LibreOffice, it won't recalculate on load.  But you can (as you've already noted earlier) still hard calculate after the load.
Comment 10 Kohei Yoshida 2013-01-08 15:46:03 UTC
Although this may call for a need to separate the "update link on load" mechanism from the calculation so that the user will get prompted even when the full calculation is not performed on load.  That's something to think about for future releases.
Comment 11 Yi Ding 2013-01-08 16:00:34 UTC
The problem is that currently it prompts the user "This file contains links to other files.  Should they be updated?" and even after the user clicks yes, the link is still not updated.

This seems like a fairly clear bug rather than future feature, and IMHO a serious one, because it'll break any spreadsheet that has links to any other spreadsheet from the user perspective (yes, it'll work after doing a recalculate but the user will never know to do that, especially when they've already been prompted once).
Comment 12 Yi Ding 2013-01-08 16:02:30 UTC
Created attachment 72681 [details]
test1.ods

Adding more test files to show ODS->ODS links also broken.
Comment 13 Yi Ding 2013-01-08 16:02:53 UTC
Created attachment 72683 [details]
test2.ods
Comment 14 Kohei Yoshida 2013-01-08 16:14:49 UTC
(In reply to comment #11)
> The problem is that currently it prompts the user "This file contains links
> to other files.  Should they be updated?" and even after the user clicks
> yes, the link is still not updated.
> 
> This seems like a fairly clear bug rather than future feature, and IMHO a
> serious one, because it'll break any spreadsheet that has links to any other
> spreadsheet from the user perspective (yes, it'll work after doing a
> recalculate but the user will never know to do that, especially when they've
> already been prompted once).

Ah, so Calc prompts for the link update?  I mis-read the description then.  I thought Calc wouldn't prompt for the link update unless the doc is recalculated.

Yes, then it's a bug.
Comment 15 Yi Ding 2013-01-08 16:21:55 UTC
Comment on attachment 72518 [details]
test ods file

mime type fix
Comment 16 Yi Ding 2013-01-08 16:22:11 UTC
Comment on attachment 72681 [details]
test1.ods

mimetype fix
Comment 17 Yi Ding 2013-01-08 16:22:29 UTC
Comment on attachment 72683 [details]
test2.ods

mimetype fix
Comment 18 Yi Ding 2013-01-08 16:25:33 UTC
No problem.  Oddly on one of our older (production use) spreadsheets it doesn't prompt to update links but just does it automatically (in 3.6).  Not sure what's triggering the difference in behavior (maybe some setting?).

In the new example spreadsheets I've uploaded, there's always a prompt.
Comment 19 Kohei Yoshida 2013-01-09 19:05:29 UTC
Yeah, I can kinda guess what can be going on here...
Comment 20 Kohei Yoshida 2013-01-09 22:57:48 UTC
Where the update link dialog gets launched:
http://opengrok.libreoffice.org/xref/core/sc/source/ui/docshell/docsh4.cxx#435
Comment 21 Not Assigned 2013-01-10 17:21:36 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3d78fe6b23eb3d6552bf5ed65f3a8ead081533d1

fdo#58531: Register cells with external references at compile time.



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 22 Not Assigned 2013-01-11 11:42:29 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=260f1b19ce7340b02fce5aea948caa61ee128f44&h=libreoffice-4-0

fdo#58531: Register cells with external references at compile time.


It will be available in LibreOffice 4.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 23 Kohei Yoshida 2013-01-11 14:20:38 UTC
Now it's in 4.0.  I'll call it fixed.