Search code examples
pythonpandas

Pandas to Excel - make part of the text bold


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.


Solution

  • 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.