Bug 81396 - XLSX IMPORT: Data plots in chart not visible when cells have formula, needs Ctrl+Shift+F9 (Steps in Comment 5)
Summary: XLSX IMPORT: Data plots in chart not visible when cells have formula, needs C...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Chart (show other bugs)
Version:
(earliest affected)
4.2.5.2 release
Hardware: x86 (IA32) Windows (All)
: highest critical
Assignee: Markus Mohrhard
URL:
Whiteboard: target:5.1.0 target:7.2.0 target:7.1.3
Keywords: possibleRegression
Depends on:
Blocks: OOXML-Chart
  Show dependency treegraph
 
Reported: 2014-07-15 18:43 UTC by Tom S.
Modified: 2021-04-10 08:38 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
test ods file with chart (80.06 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-16 04:32 UTC, Kevin Suo
Details
test ods file (85.93 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-18 02:52 UTC, Kevin Suo
Details
the exported XLSX file (12.16 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-07-18 03:01 UTC, Kevin Suo
Details
Configuration that makes it works. registrymodifications.xcu (deleted)
2014-07-22 19:34 UTC, m_a_riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Tom S. 2014-07-15 18:43:18 UTC
Open a spreadsheet and create two columns of data.  Plot the data on a line chart. Annotate chart. Plot is visible and annotations including axis and grid settings are working.  Save and close sheet.  Re open sheet.  When loaded the chart and data and grid and annotations are present but the plot is not visible on the grid.  Only one point is visible and is the last data point on the columns of data. Using MS office open and save formats (xlsx in this case).  Have not tried native format.

Also picking a file to open from a file explorer list always opens the libra office file behind the file explorer window which is annoying.
 
Using:
Win 7 x64
Core i5
All updates for win 7 installed.
version 4.2.5.2 just installed.
Comment 1 Kevin Suo 2014-07-16 04:32:57 UTC
Created attachment 102886 [details]
test ods file with chart

Hi Tom,
when I plot a line chart and save as XLSX, the plots are not missing, but the plot color are changed.

Steps to observe:
1. Save the attached ods file to XLSX.

So: please confirm whether the plots are missing, or the plots color are changed (to white?) so that you can not see them.
Comment 2 Tom S. 2014-07-16 17:26:19 UTC
#1
Open test chart.ods file.
Plot shows diamonds as orange and squares as dark blue.
Save as MS XLSx.
Open xlsx file and plots are rendered in light blue for both plots not 
orange and dark blue as before.
#2
Open Test chart.ods file.
Change plot to connect dots.
Save as ms xlsx. Won't let me save.  xlsx and other forms not on pick 
list.  Had to save ods, close ods and reopen then save to xlsx.
Open xlsx file and find no change to plots. Colors as in ods and dots 
connected.

In my file I am plotting one column of data against another column not 
against the cell number as you show in the test file.  In my case the 
plot is gone except for one dot which is the last data point in the two 
columns.  I moved the mouse around hoping to hit another data point in 
case it was white but no go.  Opening the data series format dialog 
shows the original dark blue color so it has not changed.  There seem to 
be several bugs causing multiple but similar behavior.

-Tom S.

On 7/15/2014 9:32 PM, bugzilla-daemon@freedesktop.org wrote:
>
> *Comment # 1 <https://bugs.freedesktop.org/show_bug.cgi?id=81396#c1> 
> on bug 81396 <https://bugs.freedesktop.org/show_bug.cgi?id=81396> from 
> Kevin Suo <mailto:suokunlong@gmail.com> *
> Createdattachment 102886  <attachment.cgi?id=102886>  [details]  <attachment.cgi?id=102886&action=edit>
> test ods file with chart
>
> Hi Tom,
> when I plot a line chart and save as XLSX, the plots are not missing, but the
> plot color are changed.
>
> Steps to observe:
> 1. Save the attached ods file to XLSX.
>
> So: please confirm whether the plots are missing, or the plots color are
> changed (to white?) so that you can not see them.
> ------------------------------------------------------------------------
> You are receiving this mail because:
>
>   * You reported the bug.
>
Comment 3 Kevin Suo 2014-07-17 00:47:52 UTC
Tom, please attache an (ods) file from which we can reproduce the data series lost issue. I am not able to repro from scratch.

PS. next time you make comments in this bug, please do it directly from the webpage, replying from email will add unwanted information to bugzilla.

Thanks.
Comment 4 Kevin Suo 2014-07-17 02:08:55 UTC
(In reply to comment #2)
> #2
> Open Test chart.ods file.
> Change plot to connect dots.
> Save as ms xlsx. Won't let me save.  xlsx and other forms not on pick 
> list.

I do not confirm this. XLSX is in the pick list. Are you sure you are saving using libreoffice, not Apache OpenOffice? (AOO do not have xlsx in the list).
Comment 5 Kevin Suo 2014-07-18 02:52:23 UTC
Created attachment 103011 [details]
test ods file

Tom has send me a test file. Based on his file, I can now confirm this critical bug.

Steps to reproduce:
1. Save the attached test ods file as xlsx;
2. Reopen the xlsx file in libreoffice.

Current Behaviour:
All plots are not shown, except the one plot which has no formula in it's cell.
A CTRL+SHIFT+F9 will bring all plots back again.
The exported xlsx file opens fine in MSO 2010, so this is an XLSX IMPORT problem.

This affects all chart types.

Reproducibe with:

Version: 4.3.1.0.0+, Build ID: 5536b127a105123de6f64dcf8f53dfa277faef3c, TinderBox: Win-x86@42, Branch:libreoffice-4-3, Time: 2014-07-16_10:15:31

and 4.2.6.1, Build ID: 5fdddf655fba363e34f755715238d0943a44857e

Windows XP SP3.
Comment 6 Kevin Suo 2014-07-18 03:01:01 UTC
The exported xlsx file opens file in Apache OpenOffice 4.2. It's a possible regression.
Comment 7 Kevin Suo 2014-07-18 03:01:23 UTC
Created attachment 103012 [details]
the exported XLSX file
Comment 8 ign_christian 2014-07-21 15:34:47 UTC
(In reply to comment #5)
Tested with attached file, 
Also confirm that hard recalc needed in: 4.1.6.2, 4.0.6.2 
Not reproduced in: 3.6.7.2 (plot & line still exists, but charts slightly stretched after saving)

IF reproduce from scratch with XY chart, 
Hard recalc not resolved the issue in: 4.1.6.2, 4.0.6.2, 3.6.7.2
Issue solved with hard recalc in: 4.2.6.1

Plot & line still exist in 3.6.7.2 with attached file, but missing if reproduce from scratch. Perhaps not a regression.

Tested under Ubuntu 12.04 x86
Comment 9 m_a_riosv 2014-07-21 21:50:33 UTC
With 4.2.5.2 changing the option in:
Menu/Tools/Options/LibreOffice calc/Formula - Recalculation on file load Excel 2077 and newer to always recalculate, do the job.

Works for me with:
Win7x64
Version: 4.4.0.0.alpha0+ Build ID: 32551a546c83874f08aba9c0adb7a38230fa2e36
   TinderBox: Win-x86@39, Branch:master, Time: 2014-07-19_07:47:52

I think this option was implemented to reduce the loading time with large files. Hard recalc is an usual action in calc, in fact some functions need it to be recalculated.

OTH, It's not too much clear for me why this one is in MAB, I think we need to be more strict about that, not the first developer commenting about he doesn't take too much care on MABs. 

I think the problem now is how to find the patch to ask the dev for backport at least to 4.3
Comment 10 ign_christian 2014-07-22 02:37:48 UTC
Yes if we set "Always Recalculate" to Excel 2007 and newer, it works as expected.
Confirmed in 4.3.0.3 and 4.2.6.1

But I think not many users know about this, maybe some notice in release notes or documentation would be helpful to many users.
Comment 11 Kevin Suo 2014-07-22 03:14:12 UTC
(In reply to comment #9)
> With 4.2.5.2 changing the option in:
> Menu/Tools/Options/LibreOffice calc/Formula - Recalculation on file load
> Excel 2077 and newer to always recalculate, do the job.
Yes this is true, but it's only a workaround.

> Works for me with:
> Win7x64
> Version: 4.4.0.0.alpha0+ Build ID: 32551a546c83874f08aba9c0adb7a38230fa2e36
>    TinderBox: Win-x86@39, Branch:master, Time: 2014-07-19_07:47:52

I am not sure what you mean by "works for me". Does the chart loaded with all plots shown correctly when you open with 4.4.0.0.alpha0+?

I tried with 4.4.0.0.alpha0+ TinderBox: Win-x86@42, Branch:master, Time: 2014-07-21_06:04:54, it's the same bug behaviour.


> I think this option was implemented to reduce the loading time with large files.

Under "Menu/Tools/Options/LibreOffice calc/Formula - Recalculation on file load", it's also set as "Never" for ODS format, but chart loads OK in ODS file.
I think it may be related.

It's OK to implement sth to speedup the loading of xlsx file, but should make sure that what we see are corroct. (For this case, the chart shown is totally wrong!)

> Hard recalc is an usual action in calc, in fact some functions need it to be recalculated.

As a normal user, they should never know this. I even do not know the "CTRL+SHIFT+F9" feature before I come to bugzilla and find it in the bug reports in which someone tells to "Hard Recalc".  

Also, Hald Recalc is not on the UI "Tools - Cell Contents". What a normal user may find is to use the "Recalculate" submenu, but that does not solve the problem.

Just like Tom S. who reported this bug, he did not find the reason why his chart is showing only one plot. He must have tried his best to find a solution, but did not find it. 
I then got his test file, and spent 2 hours and finally find out the reason.

If his speadsheet has formulas in all the cells, there would be not even one plot shown, which will be even harder to find the reason. 
See also this one in our Chinese libreoffice discuss forum:
http://libreofficechina.org/thread-173-1-1.html

> I think the problem now is how to find the patch to ask the dev for backport
> at least to 4.3
It's still reproducible in daily builds, so no patch.
Comment 12 Kevin Suo 2014-07-22 03:18:05 UTC
(In reply to comment #11)

> > With 4.2.5.2 changing the option in:
> > Menu/Tools/Options/LibreOffice calc/Formula - Recalculation on file load
> > Excel 2077 and newer to always recalculate, do the job.
> Yes this is true, but it's only a workaround.

I mean, this "XLSX: always recalculate" option is not selected by default in a fresh user profile.
By defalut, "ODS" is also set to "Never recalculate", but shows correct chart when load.
Comment 13 m_a_riosv 2014-07-22 19:34:51 UTC
Created attachment 103297 [details]
Configuration that makes it works. registrymodifications.xcu

Investigating a bit, I have found that with 4.4 works for me with the attached configuration file (in the root profile directory), and with a resetting of the user profile not without change the option or with a hard recalc.

But I'm not able to find which option is different in Menu/Tools/Options with this file, perhaps a hidden option makes the difference.

Maybe someone is able to find what it's the option making the difference with the attached file.
Comment 14 tommy27 2014-11-30 05:51:46 UTC
still reproducible under Win8.1 using 4.5.0.0.alpha0+
Build ID: 84a6d8eeaab540e5b2ea3baffd919903dff8c247
TinderBox: Win-x86@39, Branch:master, Time: 2014-11-22_23:41:04
Locale: it_IT

moving to mab4.3 list since 4.2.x is end of life
Comment 15 Markus Mohrhard 2015-10-24 07:50:25 UTC
Most likely the fix in https://gerrit.libreoffice.org/#/c/19568/ is correct. Actually I fixed the same problem in the past already.
Comment 16 Commit Notification 2015-10-25 16:31:28 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

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

the cells need to be imported before we handle charts, tdf#81396

It will be available in 5.1.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 17 Laurent Balland 2015-11-25 17:51:20 UTC
Bug fixed with:
Version: 5.1.0.0.alpha1+
Build ID: 229583f298b9de05e1ad1727e233d597995099e3-GL
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-11-22_23:09:05
Locale: fr-FR (fr_FR)

Thanks Markus :)
Comment 18 Robinson Tryon (qubit) 2015-12-15 22:13:19 UTC Comment hidden (obsolete)
Comment 19 Xisco Faulí 2018-02-26 11:32:06 UTC
The content of attachment 103297 [details] has been deleted
Comment 20 Commit Notification 2021-04-08 18:31:18 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/9a59068fc31d7150e255ada91c59b8299636f185

tdf#81396: chart2_import: Add unittest

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 21 Commit Notification 2021-04-09 09:15:54 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/30f222c91fa816a7863bf4bfc4a36e503e0bf2d3

tdf#141416: partial revert of the fix for tdf#81396

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 22 VLB 2021-04-09 10:57:21 UTC
Maybe it is known if this bug is related to bug 86321
Comment 23 Commit Notification 2021-04-10 08:38:44 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "libreoffice-7-1":

https://git.libreoffice.org/core/commit/77e95e208c9d22eb1350d75135e09426c16a6726

tdf#141416: partial revert of the fix for tdf#81396

It will be available in 7.1.3.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.