Is there a way to make part of the string/cell bold in Pandas? I'm working with plenty of textual data, exporting to Excel and then using it in Word as linked content, so I can't format it after it gets exported since the data changes pretty often.
Simplified example:
import pandas as pd
df = pd.DataFrame([["Hello world."], ["Hello bold world."]], columns = ["example"])
writer = pd.ExcelWriter('sample.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sample', index=False)
writer.save()
I want to make "bold" in bold. Is there a way or another way how to highlight just part of the string?
I've tested using **bold**
, <b>bold</b>
, '\033[1mbold\033[0m'
but no success at all.
Thanks to @NoobVB I was pointed in the right direction: write_rich_string
. Found this great article describing it as a cookbook and turned it into a working solution:
import pandas as pd
from pandas.io.excel._xlsxwriter import XlsxWriter
class RichExcelWriter(XlsxWriter):
def __init__(self, *args, **kwargs):
super(RichExcelWriter, self).__init__(*args, **kwargs)
def _value_with_fmt(self, val):
if type(val) == list:
return val, None
return super(RichExcelWriter, self)._value_with_fmt(val)
def _write_cells(self, cells, sheet_name=None, startrow=0, startcol=0, freeze_panes=None):
sheet_name = self._get_sheet_name(sheet_name)
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.add_worksheet(sheet_name)
#add handler to the worksheet when it's created
wks.add_write_handler(list, lambda worksheet, row, col, list, style: worksheet._write_rich_string(row, col, *list))
self.sheets[sheet_name] = wks
super(RichExcelWriter, self)._write_cells(cells, sheet_name, startrow, startcol, freeze_panes)
writer = RichExcelWriter('sample.xlsx')
workbook = writer.book
bold = workbook.add_format({'bold': True})
df = pd.DataFrame([["Hello world."], [["Hello", bold, "bold", "world."]]], columns = ["example"])
df.to_excel(writer, sheet_name='Sample', index=False)
writer.close()
This is the easiest way I figured out so far.