How to update an existing excel .ods file?

Question

Once I have read data from a sheet in a .xls file. I want to put that data in a sheet in an existing excel file with .ods extension without changing anything else in the excel file. I tried openpyxl but it doesn't support .ods files so I tried using pyexcel-ods3 but I am still not sure how to use pyexcel-ods3 to update an existing .ods file.

This is the code I tried but its for writing a new .ods excel file and not for updating an existing one

from pyexcel_ods3 import save_data

data = OrderedDict()  # from collections import OrderedDict
data.update({"Sheet 1": [[1, 2, 3], [9, 5, 6]]})
data.update({"Sheet 2": [["row 1", "row 2", "row 3"]]})
save_data("your_file.ods", data)

I tried finding the code for updating in pyexcel-ods3 doc but could not find anything.

Please let me know if there is a code for it in pyexcel-ods3. I am also open to try any new package if it can get this task done. My python version is 3.9.1 .

Answer 1

You aren't loading the original file in order for it to be changed.

The library doesn't detect that you are looking to change an existing file, if you save a file with the name of a file that exists it will overwrite the existing file.

In order to amend a file, you need to load it, potentially recreate the structure in memory, and make you changes, and then save it back to the filesystem.

The relevant piece of code from the link you posted is:

from pyexcel_ods3 import get_data

data = get_data("your_file.ods")

You can find that section here

The reason why I say "potentially recreate the structure in memory" is because there is no guarantee that it will load the formatting etc. - sometimes openpyxl will drop the formatting for example and only handle the values.