0

With Python and openpyxl:

Is it possible to color a single character in a cell's text? I'd like to color the character for a diamond (U+25C6) red. Currently I'm doing this:

WorkSheetOne.cell(row=CombBarLineDataOffsetFromTop+6, column=1, value="Monthly Average -◆-") # you should see the diamond here
WorkSheetOne['A' + str(CombBarLineDataOffsetFromTop+6)].font = Font(bold=True)
WorkSheetOne['A' + str(CombBarLineDataOffsetFromTop+6)].alignment = Alignment(horizontal="center")

It displays the diamond and I can edit the excel file and make the diamond red but I have so many of these I need to get the Python to do it.

martineau
  • 119,623
  • 25
  • 170
  • 301
Mattman85208
  • 1,858
  • 2
  • 29
  • 51
  • Excel cells can contain RTF text. [This question about EPPlus](https://stackoverflow.com/questions/9973240/how-can-i-create-multistyled-cell-with-epplus-library-for-excel) shows how RTF sections with different styling can be added to the same cell. [openpyxl doesn't seem to support this](https://stackoverflow.com/questions/28774757/editing-workbooks-with-rich-text-in-openpyxl). `xlsx` is "just" a zipped bunch of XML files though. You could create an Excel sheet with the styling you want, open it with `openypyxl` and inspect the cell's contents – Panagiotis Kanavos Jan 23 '18 at 17:27
  • Can't be done in openpyxl and no plans ever to support it. – Charlie Clark Jan 23 '18 at 18:21
  • Even if you set the value to the correct XML string with [set_explicit_value](http://openpyxl.readthedocs.io/en/default/api/openpyxl.cell.cell.html?highlight=cell#openpyxl.cell.cell.Cell.set_explicit_value), openpyxl will treat it as a string and store it using HTML encoding. Xlsxwriter on the other hand [supports rich text](http://xlsxwriter.readthedocs.io/example_rich_strings.html). You should consider changing packages or avoiding rich text – Panagiotis Kanavos Jan 24 '18 at 08:42

1 Answers1

0

According to my knowledge cell is smallest piece you can apply style.

You can do script in VBA, and do some magic that way.

Michał Zaborowski
  • 3,911
  • 2
  • 19
  • 39
  • Excel's format is a package of zipped XML files. If it can be done with Excel, the value is visible in the file and can be replicated. VBA can't do what Excel itself can't do – Panagiotis Kanavos Jan 23 '18 at 17:16
  • Maybe I was not enough strict. Question was about using particular library. In that library you can't apply styling on part of cell. However you can add VBA. You can add styling to XML file, but that changes will be lost when operating with the library. So - you can choose your poison. – Michał Zaborowski Jan 23 '18 at 23:02
  • I understood what you meant. That's not correct. You don't need VBA to style anything. You can't *use* VBA in most scenarios either - it requires a local installation of Excel, a sheet with macros and security permissions to allow it to run. You can't do that in any web server and most desktop scenarios that require Excel. – Panagiotis Kanavos Jan 24 '18 at 08:19
  • What I can say - works for me. If you don't agree. No problem - at least at my side. – Michał Zaborowski Jan 24 '18 at 09:04