Duplicate Chart in OpenOffice Calc without losing link to the sheet's data

Question

I have created and formatted a chart and need the same one again to be filled with other data. The problem: In OpenOffice Calc 4.1.1, a chart's data is unlinked from the original data/sheet, as soon as the chart is copied (Ctrl+C, Ctrl+V, or also Ctrl+X, Ctrl+V). The chart's copy has its own data table - and I cannot set the data range to use in this copy. As the underlying data will change, manually changing the chart's data table is no sensible solution.

It's possible to copy the whole sheet, but as both data sets are in one sheet, I'd like to have both charts on the same sheet. I also did not find a way to move the chart from one sheet to another without losing the link between the sheet's data and the chart.

Any ideas anyone? Or did I maybe just miss the obvious?

Answer 1

In the meantime, I came up with two possible solutions.

Solution 1 is not really an answer to the question: LibreOffice will open the ODS-files as natively as OpenOffice. When copying a chart in LibreOffice, the chart's copy retains the link to the data (tested with LibreOffice v.4.2.8.2 under Linux). This works only, if the names of all data series were set via reference to a table cell (or not set at all), not set as a fixed string.

Solution 2 is to edit the ODS-file directly, which is handsome for a small number of charts. I went into the file, modified the XML tree, and was mildly surprised that the following worked:

1. Create a copy of the ODS file and change extension to ZIP
2. Extract content.xml
3. Look for the right sheet (Place some unique text before the chart to quickly find it) and locate the chart (see below).
4. Copy the <draw:frame> node of the XML tree.
5. Save content.xml and reintegrate in the ZIP file, restore extension ODS.
6. Open the file in OpenOffice.
7. You shall now have two copies of the chart at exactly the same position - drag one to another position and edit the data range.

The chart in the content.xml looks like this:

<draw:frame ...><draw:object ...><text:p/></draw:object><draw:image .../></draw:frame>

Warning: Copying the <draw:frame> without copying related ressources may cause in inconsistent file. Always work with a copy. OpenOffice seems to automatically copy the ressources - but I tested this only for the most recent charts that was created in the file.

Note: More simple solutions (maybe within OpenOffice) are still welcome, of course.