Skip to content

Support of rich text in cells

Bitbucket Importer requested to merge topic/2.6/bitbucket-pr-224 into branch/2.6

Created originally on Bitbucket by Cilyan (Cilyan Olowen)

Dear all,

as I needed the feature, I invested some little time looking into the support of rich text in cells. This feature allows to set font style to only parts of a cell's text, in comparison with cell style (already supported) that styles the whole content.

I created the pull request in order to discuss with you, it is clearly not ready for integration. I planned on working further on it, but any advise/review comment is welcome as it's my first time hacking into openpyxl.

As cell style is already implemented and the rich text feature is nothing more than Font inside the shared strings, I tried to reuse as much as possible the existing code. Also, I did not want to break the compatibility of the Cell.value property since probably a lot of user program do not expect to receive rich text on that interface. So even if cell contains rich text, Cell.value returns a plain text version of it. At first, I used a separate internal property so that _value is also not changed. But in the end, I thought that user program that was low level enough to interface with Cell.internal_value should be able to distinguish between plain text and rich text content. Comments welcome on that decision.

The proposed implementation is based on a type named RichTextContent implemented in openpyxl.cell.rich_text. The name was selected so that it doesn't conflict with openpyxl.cell.text.RichText, although this later class is only used internally to read XML. Maybe it would be more intuitive to rename the original class and use RichText for the new class.

The RichTextContent class is responsible for holding the content of the cell when it is rich text instead of plain string. The class implements basic extend/append/add functions as well as + and += operators to present a (hopefully) nice interface to the user. The class is also responsible for its serialisation, although I did not inherit Serialisable to not clutter the namespace too much in a class that is supposed to be exposed to the user code. I modified write_shared_string to call the RichTextContent function when it recognises that the shared string is not plain text. The compatibility with IndexedList is ensured by the fact that objects have a hash and as such can be used as dict keys. However, this is limited to recognising the same object and not two objects that would contain the same content. A further improvement can be made by computing a hash from the content instead and implementing comparision functions (__hash__ and __eq__).

To have access to the rich text content and make modifications, one would use the new Cell.rich_text property. The Cell.rich_text propery always return a RichTextContent object even if the internal value is a plain string. This is done so that manipulation and mostly concatenation is made easy and transparent to the used. The property Cell.is_rich_text can then be used to query if internal content of a cell is indeed rich text or plain text. In the end, it doesn't matter much to the user, as RichTextContent is intelligent enough to produce plain text XML when it contains no styles. The only difference would be at the Cell._value level.

The most important limitation of the current implementation is that it doesn't write properly the xml:space="preserve" property when necessary. As such, space between text chunks are not observed. For this, I would need advice, because I could not properly understand how to support that attribute through the Serialisable interface of Text. Maybe by having a specialisation of NestedText? I am not sure.

I did not code yet an implementation for the reader. When the writer is finished and the Cell interface makes everyone happy, it should be straightforward to convert openpyxl.cell.text.Text to RichTextContent before placing the value in the cell, instead of calling Text.content.

Another discrepancy that would need care is the fact that if you save the file once, then change a cell's text and save it again, the new file will also contain strings that are no longer used. This happens the same for plain text in the current implementation. I think clearing the shared strings table before writing the file would be sufficient, but maybe not very efficient. Comments welcome.


>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws =
>>> cell = ws["A1"]
>>> cell.rich_text
Traceback (most recent call last):
  File "<pyshell#4>", line 1, in <module>
  File "C:\Anaconda3\envs\openpyxl\lib\site-packages\openpyxl\cell\", line 328, in rich_text
AttributeError: 'Cell' object as no attribute 'rich_text'
>>> from openpyxl.styles.fonts import Font
>>> bold_red = Font(name='Calibri',size=11,bold=True,italic=False,vertAlign=None,underline='none',strike=False,color='FFFF0000')
>>> cell.rich_text = "Hello "
>>> cell.rich_text += ("world", bold_red)
>>> cell.rich_text
<openpyxl.cell.rich_text.RichTextContent object at 0x0000000003C0AF98>
>>> cell.value
'Hello world'

2018-01-25 16_30_43-RichText.xlsx - Excel.png

Merge request reports