python - Editing workbooks with rich text in openpyxl

7
votes

I was wondering if openpyxl can read and/or write rich text into excel. I am aware that this question was asked once before in 2012 linked below, but I am not sure if this has changed.

As it stands load_workbook() seems to throw away rich text formatting.

As for a specific problem, I need to open, edit, and save a workbook where some cells have both superscripted and normal text in one cell. When I save the workbook, the format of the first character of the cell is applied to the rest of the cell.

Here is the to 2012 question:
How do I find the formatting for a subset of text in an Excel document cell

After looking around, it seems like rich text was implemented in openpyxl (based on the issues list on openpyxl's bitbucket):
https://bitbucket.org/openpyxl/openpyxl/issues?q=rich+text

But I am still unclear on how to use it (if I interpreted the issues list correctly at all). If it helps at all, I am actually not editing the contents of these cells simply that they don't lose formatting on save.

Any thoughts would be greatly appreciated.

Thanks! Best

1
pythonexceltextopenpyxl
It can be pretty tricky to "not edit the contents" of cells, when working at the file level (i.e. bypassing Excel entirely). The structure of Excel files is very weblike, and not very streamlike, so the concept of "localized surgical modification" of an Excel file doesn't really exist in practice. You pretty much always have to load substantially all the data into memory, then rewrite the whole thing from scratch upon saving, no matter how small your changes seem to be, intuitively. Even Excel itself does this, but it's a gigantic program, and hides most of that from the user. – John Y
So, when it comes to messing with formatting (or rather, trying to make some changes while NOT messing with formatting!), I find it is simplest to just let Excel handle everything, if you can swing it. (In other words, if you are using a computer which has Excel installed on it.) You can programmatically control a running instance of Excel via Python, if for some reason you don't want to use VBA or VBScript or a .NET language. – John Y
Thanks! I figured as much. I've been trying to avoid using packages that use COM so that I can run the script cross platform but I guess that's unavoidable (unless of course another answer crops up).. – fpes
Use xlwings for remote controlling Excel on Windows or Mac OS. – Charlie Clark
xlwings looks like a great implementation.. I'll give it a go. Thanks! – fpes

1 Answers

6
votes

Formatting below the level of the cell is not supported by openpyxl. To use it you'd have to implement your own code when writing as openpyxl just stores whatever strings it receives. Full read/write support would add a great deal of complexity.