0

How do I programmatically delete a worksheet in an open document spreadsheet, preferably in Python?

I have looked at https://pypi.python.org/pypi/pyexcel-ods/0.0.3 but I don't see any documentation on how to do this.

And if I run data.update({"WORKSHEET1": "",}), I merely delete the worksheets I want to keep and the contents of the WORKSHEET1, which I want to delete entirely.

Thanks

zadrozny
  • 1,631
  • 3
  • 22
  • 27

2 Answers2

1

The short answer is: read it back as OrderedDict and then delete key(your sheet name) and save the modified dictionaries to a file.

Solution A. An example solution using ezodf:

>>> import ezodf
>>> doc = ezodf.opendoc("sample.ods")
>>> list(doc.sheets.names())
['Sheet1', 'Sheet2', 'Sheet3']
>>> del doc.sheets[1]
>>> doc.save()
>>> exit()

More documentation can be found here

Solution B. An example solution using pyexcel-ods: 1. Let's set up the example file:

>>> from pyexcel_ods import ODSWriter
>>> from collections import OrderedDict
>>> data = OrderedDict()
>>> data.update({"Sheet 1": [[1, 2, 3], [4, 5, 6]]})
>>> data.update({"Sheet 2": [["row 1", "row 2", "row 3"]]})
>>> writer = ODSWriter("your_file.ods")
>>> writer.write(data)
>>> writer.close()

2. Let's read it back:

>>> from pyexcel_ods import ODSBook
>>> book2 = ODSBook("your_file.ods")
>>> data=book2.sheets()
>>> data
OrderedDict([(u'Sheet 1', [[1.0, 2.0, 3.0], [4.0, 5.0, 6.0]]), (u'Sheet 2', [[u'row 1', u'row 2', u'row 3']])])

3. Now delete "Sheet 1":

>>> data.pop('Sheet 1')
[[1.0, 2.0, 3.0], [4.0, 5.0, 6.0]]
>>> data
OrderedDict([(u'Sheet 2', [[u'row 1', u'row 2', u'row 3']])])

4. Then save it to a file of your choice:

>>> writer2=ODSWriter("your_file2.ods")
>>> writer2.write(data)
>>> writer2.close()

5. Let's read it back and verify:

>>> book3=ODSBook("your_file2.ods")
>>> book3.sheets()
OrderedDict([(u'Sheet 2', [[u'row 1', u'row 2', u'row 3']])])
chfw
  • 4,502
  • 2
  • 29
  • 32
  • Thanks. But won't this erase all the metadata (eg, b/g color, comments on cells, etc)? – zadrozny Jan 24 '15 at 05:15
  • @zadrozny yes, it will. This library does not surport meta data. – chfw Jan 25 '15 at 01:45
  • Thank you for confirming. I haven't found a good explanation of how to split and merge the xml files in a spreadsheet. Know of one? – zadrozny Jan 31 '15 at 03:15
  • @zadrozny, please see my updated solution A. I haven't tested with your file. Please backup your original file before trying it. As a side note, pyexce-ods was created for data processing rather than sheet management. I believe ezodf would help you out on more sophisticated use cases – chfw Jan 31 '15 at 21:48
0

Original question is:

delete a worksheet in an open document spreadsheet

Answer: same, as one would do in StarBasic:

# get the model of document
    model = XSCRIPTCONTEXT.getDocument()
# get all sheets
    sheets = model.Sheets
# delete a sheet by name
    sheets.removeByName("Sheet2")

No need to operate with external libraries, imports, Dicts, ...

Does that help?

ngulam
  • 995
  • 1
  • 6
  • 11