Multiple font style in same cell

[jamal.gou...@gmail.com]

Hello,

I have a use case where depending on some condition I have to bold and style with red either the first, second or third word of a cell's text content. The text is contained in a single cell.

My program helps consultancies in broadband deliver excel files to their customers. And they (the customers) are quite disposed to return the files for tiny details. On top of that the quality of the excels is pretty much

the way to rank the quality of such consultancies. So there is a strong need for me to be able to ship this feature.

This is quite similar to what was asked here: 

- This Stackoverflow question : Multiple styles in one cell in openpyxl

- This issue : Cell styles that have multiple font colours are not preserved

In both cases the answer is clear. Such feature is not supported for in openpyxl.

Typically in such a case I would try to hack my way through the library but I am really not familiar with excel standard. So can anyone with enough knowledge in the group share some way to achieve this?

In the issue there is this part of the answer from CharlieC that suggests that this could be possible 

It would be possible to read and write such information by essentially embedding the formatting information in the string but I'm not sure how that would be modelled in Python

Regards 

[jeff]

I had a similar use case for a co-worker. For each row, she needed selections of text (a substring) from a cell to be made font-color red. This was in an existing workbook. As you noted, openpyxl doesn't support that feature. I used the module xlsxwriter, which supports writing "rich strings": https://xlsxwriter.readthedocs.io/example_rich_strings.html . The method is [xlsxwriter worksheet object].write_rich_string().

xlsxwriter, however, can't read an existing file. I ended up reading the file into memory with openpyxl and writing it back to disk with xlsxwriter. For each row (and each cell of that row) in the openpyxl ws, write the contents to the xlsxwriter ws. I used regex to search for a specific substring (unique to each row) in the cell text, but it sounds like your condition logic needs might be simpler (first, second, or third word).

Jeff

[Charlie Clark]

On 22 Jan 2020, at 17:51, jamal.gou...@gmail.com wrote:

> In the issue there is this part of the answer from CharlieC that
> suggests
> that this could be possible

That's right and no support is planned for this kind of sub-atomic
formatting. There is a pull request that demonstrates how it can be
done.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[jamal.gou...@gmail.com]

Thank you both for your answer.
The xlsxwriter approach might add significant overhead. For my use case, I need to build an excel file with about a dozen sheets with only few cells that need that rich text formatting. If I understand well that would mean that I need to generate the excel and only then copy cell by cell to an xlswriter ws before sending back the HTTP response. (The file generation is exposed through API).

For those interested, I think that this is the PR mentioned by CharlieC is this one https://bitbucket.org/openpyxl/openpyxl/pull-requests/224/support-of-rich-text-in-cells/diff 

On Thursday, January 23, 2020 at 9:35:22 AM UTC+1, Charlie Clark wrote:

[jamal.gou...@gmail.com]

Sorry to come back with my question.

I looked over the Support Rich Text in Cells PR and tried to adapt it. 
The issue that I am facing is that the PR is based on usage of `openpyxl.writer.strings:: write_string_table `.

It basically stops the normal flow in case the `key` or `cell.value` is a `RichTextContent` and directly does `xf.write(key.to_tree())`

I figured that to achieve the same thing I need to change the current `openpyxl.cell._writer:write_cell` method and did something like this (disclaimer: it's just-see-if-it-works-first code)

from openpyxl.cell._writer import write_cell

from openpyxl.worksheet import _writer

from .rich_text import RichTextContent

def with_rich_text_writer(xf, worksheet, cell, styled=None):

if isinstance(cell.value, RichTextContent):

xf.write(cell.value.to_tree())

else:

return write_cell(xf, worksheet, cell, styled)

_writer.write_cell = with_rich_text_writer

# Then a simple test

wb = Workbook()

sheet = wb.active

cell = sheet.cell(1,1)

bold_red = Font(name='Calibri',size=11,bold=True,italic=False,vertAlign=None,underline='none',strike=False,color='FFFF0000')

# Setting directly _value for now

cell._value = RichTextContent(["Normal", ("Bold and Red", bold_red)])

# Like what is done in the PR in _bind_value

cell.data_type = "s"

save_workbook(wb, "temp.xlsx")

The problem is that nothing get's written at all.
This is not that surprising given the fact that the logic of `openpyxl.cell.write_cell` seems different from the older `write_string_table `. Now it seems to always be a `Element("c", attrs)` when `RichTextContent.to_tree` does this

def to_tree(self):

"""Used by write_shared_string to produce resulting XML.

"""

if len(self) == 1 and self._elements[0].is_plain:

# Pack as plain text

return Text(t=self._elements[0].text).to_tree("si")

text_element = Text()

for element in self._elements:

if not element.is_plain:

inline_font = self._font_to_inline_font(element.font)

else:

inline_font = None

rich_text = RichText(rPr=inline_font, t=element.text)

text_element.r.append(rich_text)

return text_element.to_tree("si")

I tried changing to `to_tree("c")` , even trying to see if it's because of the attributes returned by `_set_attributes` and added the coordinate directly on the element returned by `text_element.to_tree("c")` but always same result.
I am asking in case there is something simple I don't see because of my lack of knowledge and that would make the whole thing work ? 

Again it's just a does-it-work-at-all code I need for now. 

[jeff]

> For my use case, I need to build an excel file with about a dozen sheets with only few cells that need that rich text formatting.

If you're creating the workbook new in-memory, why not skip openpyxl and just use xlsxwriter?

[Jamal Gourinda Pro]

It’s because the workbook is not completely created from scratch.

I start with a template with some predefined styles. Actually all sheets are already present in the template and my processing is mainly looping over resources fetched from db and fill rows.

But some sheet are a bit more complicated and require that kind of rich text formatting. 

--
You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/kFoC8YqN57Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openpyxl-user...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/c43ecd36-e538-475a-91cf-64965d8dcc6a%40googlegroups.com.