When I edit a spreadsheet with pyexcel it loses its appearance

Question

Hello, when I try to add new values in a spreadsheet ods she loses all her "style" and keeps only the values. Follow the code:

import pyexcel

sheet = pyexcel.get_sheet(file_name="dados.ods")
sheet.row += [0, 0, 0]
sheet.save_as("example_series_filter.ods")

It works perfectly, but as I said I lose the appearance of the spreadsheet PS: python3 usage and have the libraries properly installed

Answer 1

It is in the description of the Pyexcel package -

Known constraints:

Fonts, Colors and Charts are not supported.

That is, this package, although convenient, currently simply discards the appearance information of the spreadsheet.

You may find another package that preserves the information - but unfortunately, although there are other packages, the focus to handle spreadsheets is always to reach the numbers - and anything goes for the appearance.

One thing that would probably work there is you manipualr manualmetne some aspects of the ODS file - the ODS, just like the XLSX are files of type "zip" with XML files and aggregated images inside. These pacoes, in Python uses the ziplib and Python’s own xml tools to manipulate these spreadsheets. Complicated logic is how to maintain the structure of these XML’s where the data is.

Now, if you lsiatr the contents of an ods file with a tool like "unzip", you will see several . xml files inside. Part of the formatting information the file . ods is in the other files . xml - while the numeric content itself is always in the file content.xml. The pyexcel package probably simply recreates all the aruqivo ODS from scratch, and thus creates the other xml (styles.xml, `meta.xml) etc...) blank or with as little content as possible to be valid.

You can do it this way - I won’t put the full Python code because I would need to do some tests and it would be a lot of work (remember that the answers here are always voluntary) - but you have to do it there - doing the tests in the same interactive mode

• copy the file . ods to another, creating a backup (use the module shutil do Python)
• manipulate the original file as pyexcel exactly as it is doing now
• use Python ziplib to grab the file contents.xml inside the file recorded by pyexcel, and puts it inside the copied file in the first step.

Ready - at least some style information - those that are not directly inline in Contents.xml will be preserved. (The problem is that in my experience much of the formatting information is inside the same Contents.xml - but if you do so far, soon you start to have enough confidence to mnipualr the xml inside the content.xml directly).

Another alternative is to use a Python script that uses Libreoffice itself - in this case, instead of manipulating the file directly, you use the Libreoffice API calls (named "Pyuno") to directly control the program, and insert the data into an existing spreadsheet. Libreoffice, claor, has full support for all worksheet features - not only formatting, like graphics, etc...and would preserve anything you don’t touch.