Calc replacing all Chart Data Ranges with Data Tables

English
, , , ,
#13

Hi,
I see this post is quite old, but yesterday I’ve had a very similar situation on my laptop running LibO 6.0.6: suddenly, all Calc files it saved in .xlsx format (Office 2007-2013) did lose all the references to their source data and got a data table instead. Very frustrating, since those charts must be updated every day to keep track of a progress in my work.

Quite surprisingly, the problem was not present in another PC in my office, which has the same version of LibO, so I started comparing ALL the settings (Tools → Options) in the two machines.

Everything was identical, except from two options:

1. Load/Save → Microsoft Office:##

Option of loading "SmartArt to LibreOffice Shapes or reverse was not selected (in the image below, the configuration in the working machine)

image description
image description778×526 38.5 KB

2. LibreOffice Calc → Formula → Formula syntax:##

In my PC it was set to “Excel A1”, while in the other PC it was “Calc A1” (again, the image below shows the configuration in the working PC

image description
image description778×526 42.6 KB

After setting the mentioned options also in my PC, I tried saving again the spreadsheet as a .xlsx file… and apparently that did the trick. Now, if I reopen a previously exported .xlsx the charts inside retain their source data ranges.

Hope this can be useful to someone.

Regards,
Max - Italy

#14

Indeed, setting the formula syntax to Excel A1 breaks the chart’s data range references when exporting to .xlsx

That’s a bug, could you please report it? Thanks.

#15

Sure, as soon as i have a little time! :slight_smile:

#16

I’ve reported the bug. Ticket is here: https://bugs.documentfoundation.org/show_bug.cgi?id=121260

#17

Thank you!

#4

I’m looking to revise my question but don’t see a button for that or editing it (is this because I’m a new user? I can flag, close or delete, none of which is what I want to do).

Regardless, some further information on strange behaviour in this spreadsheet which may or may not be related, so may help diagnose the above.

I’ve noted that on my few sheets of source tables, where I have calculations, some of the calculations relevant to the same sheet have had the sheet name filled in in my formula. For all of them. It’s like I have triggered some action and it has decided that action means all formulae must be expanded to include the sheet name. I haven’t been copying these tables from one sheet or to another. All I’ve done is at some point rename the sheets containing the data.

So some action is triggering an action that goes through and does this. I’m beginning to suspect it might be renaming the sheet. But why?!?!

I’ve searched and found this to be similar, but not at all helpful. I’m suspecting either the same or some similar action is triggering the replacement of data ranges with data tables. How is it possible to innoculously do something, yet trigger such massive changes throughout a spreadsheet? This formula thing might seem minor, but makes complex formulae very hard to follow, and the replacing chart data ranges with data tables thing is absolutely catastrophic.

#5

Why didn’t you answer the question by @mariosv first?
Charts are a complicated thing. Even if you copy one from Calc and paste it into a Writer document the data references will be replaced by tables containing the actual data. This cannot be reversed. Same thing may happen if you store to an alien format that doesn’t know about charting in Calc. (I cannot tell from experience because I never do.)

#6

Because the charts were NEVER copied out from their source spreadsheet. For a period of time they were correctly anchored to their data range. I didn’t take any action that should have converted my charts. Honestly I’ve done something innocuous like renaming a sheet, and LibreOffice has decided to go through and expand all local formula references to include the sheet they’re on, and at the same time replacing all Chart Data Ranges with Data Tables. No alien formats or documents involved!

#7

And if you look at the comments you will see I did answer the question by @mariosv first.

#8

@rgl: Sorry! I didn’t recognize your username and misinterpreted the short comment as an advice by someone else.
Sorry again. I do not know further advice. Never experienced that in the way you describe it.
There is an old superficially related bug report tdf#85328 concerning flat ods and recently confirmed to not be fixed. (A short test by myself with V5.3.0 told otherwise confirmed also.)

#9

@rgl: Are you sure that the documents never were save to a different format (flat ods included) in between?

#10

@Lupp Yeah. Unfortunately I’ve had to abandon this. It just isn’t an economic use of my time. I did try to reproduce this earlier on, and found there was no single simple action that reliably reproduced this. So it’s an odd one. I’ve switched to Google Sheets (which last time I tried just wasn’t up to the power of LibreOffice), and I’m finding it much easier all round. It’s actually faster running that over the internet than running the local copy of the spreadsheet on the app running locally!

#11

@rgl: I don’t know GS (except the name), but I used LibO and its predecessors continuously for decades now, and a great deal for critical real-world tasks. There were some flaws and annoying bugs. However, I never actually lost data or functionality to a significant degree - and I never would trust my data to Gxxxxx.
Good luck with your decision for the next 20 years. And now let’s make econmic use of our time again, at least as far as you are concerned. I may continue to waste it.
Regards.

#12

@Lupp: I’ve used StarOffice, OpenOffice and now LibreOffice each as my main spreadsheet, for what I consider fairly complex stuff. Times I’ve been jumping through hoops, but this is the first time it’s went on a spreadsheet wide data mangling drive. Unfortunately I can’t afford to keep recreating things and hoping this time they won’t get mangled as I’ve spent most of the last week seemingly doing that. Thanks for trying. I hope the details I’ve given might help if someone else has this.

#18

This just happened to me with a file I have used for 1.5 year now with many graphics. :frowning: And my file has never been saved to alien formats. It has been an ods from scratch. I had this issue in one or another chart before, but now I just lost all my charts. Disappointing.

#19

I too have this problem. Some charts in a large ods file are converting to Data Table basis. All charts were set up using Data Ranges. I am spending way too much time converting back to Data Ranges.

#20

Web search took me here when the same thing happened to me, so hopefully this will fix it for others as well. It seems that Calc has started to default to saving spreadsheets in Flat ODS format, even when they were opened from ODS files whose existing names end in .ods instead of .fods. Saving spreadsheets in Flat ODS format triggers a bug that breaks charts with cross-sheet references as noted by @Lupp in March 2017.

Using Save As and choosing “ODS Spreadsheet (.ods)” instead of “Flat XML ODS Spreadsheet (.fods)” under “File type” saves a version whose charts are not horribly broken when re-opened, and which as far as I can tell will also subsequently be re-saved in the correct format using plain Save.

I suspect that the last-saved-as filetype is kept internally as a numeric index into a dropdown menu that got some stuff added to it before a recent release, and that Flat XML ODS Spreadsheet now occupies the dropdown menu slot that ODS Spreadsheet used to, but I have no definitive proof of that.

If you’re looking at your .ods files in your OS file browser, you can spot the ones that have accidentally been saved in Flat XML ODS format by their sheer hugeness. A small spreadsheet that would normally fit in under 60KB when genuinely been saved in ODS format bloats to 1.5MB when XMLified; it seems that the Flat XML versions are not compressed in any way, they’re just raw XML text. But the only way making that observation is going to help you avoid your charts getting ruined is if you spot it before closing the spreadsheet you just saved.

Yes, this is pernicious. But it’s also yet another demonstration of why good backup hygiene is an absolute necessity, not merely a nice-to-have or a maybe-to-be-contemplated.

#21

This is about issues where the version of LibO (and the OS?) can be very important.
There was the possibly related bug tdf#121260 (alraedy mentioned by
@MassimoMula. It got a last fix with V7.3.0.
Is your version V7.3 or higher? Otherwise try an upgrade.
The other possibly related bug tdf#85328, already mentioned by myself was reported to still be open for V7.6.0.alpha.
What are your current versions?

#22

Version: 7.5.6.2 (X86_64) / LibreOffice Community
Build ID: 50(Build:2)
CPU threads: 2; OS: Linux 6.4; UI render: default; VCL: x11
Locale: en-AU (en_AU.UTF-8); UI: en-US
Debian package version: 4:7.5.6-1
Calc: threaded

tdf#85328 is undoubtedly the bug behind the chart data loss, but perhaps it’s worth opening a new one about this behaviour where using Save on an .ods spreadsheet prepared with an earlier version of Calc saves a Flat XML ODS version over the existing .ods rather than using a .fods extension to mark it visibly as Flat XML.

#23

I have found another way to make Calc save a spreadsheet that looks like it’s in ODS format when it’s actually in Flat XML ODS format.

Steps to reproduce:

  1. Use a Linux-based desktop file browser to create a new empty file named test.ods.

  2. Double-click test.ods. If LibreOffice Calc is your installation’s default handler for .ods files, Calc will open it.

  3. Enter some data, then choose File->Save or hit Ctrl-S. Calc will save your data as test.ods.

  4. Quit Calc.

  5. In the desktop file browser, right-click test.ods and choose Open With->your favourite plaintext editor.

Expected result: text editor either shows gibberish or refuses to open the file on the grounds that it contains binary data, as is the normal behaviour when trying to open an ODS file with a text editor.

Actual result: text editor opens the file and reveals it to contain XML-formatted text data.

On reflection, this is almost certainly the workflow I used to create my own broken spreadsheet, so I no longer believe that the issue originally reported by @rgl is due to some mismatch of dropdown menu index meanings across LibreOffice versions.

Still annoying, though. I would have expected Calc to be smart enough to choose the default save format for an initially zero-length file from its filename extension rather than using a format that would normally require a different extension.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.