CODEWITHSUNDEEP
pythonexcelpandaspandas.excelwriter
Simen Russnes
Simen Russnes

Reputation: 2260

Color part of text in cell with Pandas/ExcelWriter

How can I color part of text written to an excel file with a pandas dataframe and ExcelWriter?

I've set up to format cells with background color and text color and different things, but can you apply the formatting to only the first of comma separated values in a cell of values being a string "1, -3, 5"?

So far I have the following ways to format individual cells, but I couldn't find in the documentation anywhere how to apply text color to part of the text in a cell, which is possible to do with the Microsoft Excel GUI.

import pandas as pd
excel_writer = pd.ExcelWriter("bob_saget.xlsx", engine='xlsxwriter')
sheet_name = "Full House"
data_frame = ...
headers = ...

data_frame.to_excel(excel_writer, sheet_name=sheet_name, index=False)
workbook = excel_writer.book
worksheet = excel_writer.sheets[sheet_name]
color_format = workbook.add_format({'bg_color': 'orange', 'border': True})
gray_cell_bg = workbook.add_format({'bg_color': '#d8d8d8'})

# using worksheet.write, re-writing the value in the cell and adding formatting
worksheet.write('B1', headers[1], color_format)

# hack solution to apply a background
worksheet.conditional_format("C4", {'type': 'text',
                                          'criteria': 'not containing',
                                          'value': '$$$$$$$',
                                          'format': gray_cell_bg})

excel_writer.save()

Upvotes: 3

Views: 4858

Answers (1)

jmcnamara
jmcnamara

Reputation: 41554

You can format parts of a string with XlsxWriter using write_rich_string().

enter image description here

You will probably need to overwrite some of the cells written by Pandas to achieve this. Also, it can't be done via a conditional format.

Upvotes: 6

Related Questions