Thank you!
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.
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.)
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!
@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.)
@rgl: Are you sure that the documents never were save to a different format (flat ods included) in between?
@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!
@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.
@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.
This just happened to me with a file I have used for 1.5 year now with many graphics. 
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.
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.
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.
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?
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.
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:
-
Use a Linux-based desktop file browser to create a new empty file named test.ods.
-
Double-click test.ods. If LibreOffice Calc is your installation’s default handler for .ods files, Calc will open it.
-
Enter some data, then choose File->Save or hit Ctrl-S. Calc will save your data as test.ods.
-
Quit Calc.
-
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.
Can you check on the created test.ods BEFORE opening in Calc? Size? What do you see, when you open this in your text-editor?
.
If this new test.ods is actually flat xml Calc will not Change this, when you edit and save… But then the trap was laid before Calc was activated…
Quite easily reproducible:
touch empty.ods
soffice empty.ods
edit, save, it’s a Flat ODF file. Understandably, because opening the empty file it lacks the zip container and mimetype and META-INF/manifest.xml streams, so it can not be an ODF container file and must be saved as Flat ODF. Note that file name extensions are not an indicator of actual content, they are just some convention. (apart from that Windows does odd things based on file name extensions and applications try to mimic that broken behaviour).
It’s an empty (i.e. zero length) file. Empty files have no internal structure at all, so not only is it not an ODF container file when opened, it’s not a text file either. The only useful information Calc can possibly glean from the file at open time is its pathname.
.
Since there exists no existing format to preserve, Calc is completely free to choose one. Given that it already supports a convention that relates file formats to filename extensions, as demonstrated by the wording of the file format descriptions and the existence of the “automatic filename extension” option in the Save As dialog, I can see no good reason why it “must” ignore that convention under these circumstances. Nor can I see, in cases where it doesn’t even have a filename extension to use as a format hint, why it should avoid the long-established, long-standard ODS format in favour of an inefficient bloated flat XML format that only really exists for the benefit of a relatively tiny cohort of Git users.
.
Good software design conforms to the principle of least surprise, and having Calc’s default behaviour yield different results across platforms is surprising.
.
Despite the insistence of platform zealots on justifying every single piece of surprising software behaviour as a teachable moment, every method of storing file format metadata has both advantages and disadvantages. The Unix convention of relying on magic identifiers near the start of the file fails for zero-length files. The Windows convention of relying on filename extensions fails when files get imported from other platforms. The Mac convention of relying on multiple forks per file fails on filesystems that don’t support those. There is no objective standard by which any of these can reasonably be described as “broken behaviour”.
New empty files as created by every Linux desktop file browser I’m aware of are genuinely empty, i.e. zero length, i.e. have no content whatsoever. This is not the same behaviour as in Windows, where using the Windows file browser to create a new empty file actually makes Windows invoke an application hook whose job is to do the equivalent of launching the app with no file, then doing Save As with the pathname set to that of the file being created and all other options set to defaults.
.
Most Linux desktop apps that allow files to be edited in some way have a Save As option that allows the file format to be chosen automatically based on the filename extension. I was actually quite surprised to find that the closest that any of the Star Office descendants have to this is an option that works the other way around, i.e. lets you automatically append/force a filename extension based on the format you chose.
.
In any case, I can see no harm and much good in having the application treat the filename extension of any zero-length file that it’s asked to edit as a hint for the file format it’s supposed to Save in, or using a sensible default format if no such hint is available. And no, I would not rate Flat XML ODS as a sensible default format even if it didn’t trigger the chart data source bug. It’s nice to have it for interop with source code control systems but for general use? Thanks, no thanks.