pandas: how to format cells after exporting to Excel

Question

I am exporting some pandas dataframes to Excel:

df.to_excel(writer, sheet)
wb = writer.book
ws = writer.sheets[sheet]
ws.write(1, 4, "DataFrame contains ...")
writer.save()

I understand I can use the format class: http://xlsxwriter.readthedocs.org/format.html to format cells as I write them to Excel. However, I can't figure out a way to apply a formatting style after cells have already been written to Excel. E.g. how do I set to bold and horizontally align to the centre the item in row = 2 and column = 3 of the dataframne I have exported to Excel?

Because I need to format only certain rows and only certain columns of the dataframe which is output to Excel in one go with df.to_excel(writer,sheet) as above — Pythonista anonymous
– Pythonista anonymous, Commented Jan 18, 2016 at 15:13

George Petrov · Accepted Answer · 2016-01-18 16:25:40Z

1

It should look like this:

new_style = wb.add_format().set_bold().set_align('center')
ws.apply_style(sheet, 'C2', new_style)

According to apply_style() that need to be added to XLSXwriter:

def apply_style(self, sheet_name, cell, cell_format_dict):
        """Apply style for any cell, with value or not. Overwrites cell with joined 
        cell_format_dict and existing format and with existing or blank value"""

        written_cell_data = self.written_cells[sheet_name].get(cell)
        if written_cell_data:
            existing_value, existing_cell_format_dict = self.written_cells[sheet_name][cell]
            updated_format = dict(existing_cell_format_dict or {}, **cell_format_dict)
        else:
            existing_value = None
            updated_format = cell_format_dict

        self.write_cell(sheet_name, cell, existing_value, updated_format)

OR you can write so:

new_style = wb.add_format().set_bold().set_align('center')
ws.write(2, 3, ws.written_cells[sheet].get('C2), new_style)

edited Jan 18, 2016 at 16:25

answered Jan 18, 2016 at 15:29

George Petrov

2,8591 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pythonista anonymous Over a year ago

I'm confused: do you mean you have modified the classes of XLSxwriter so as to add apply_style() ? I couldn't find apply_style documented anywhere.

George Petrov Over a year ago

Yes, you need to add this func to your XLSXwriter.

ctrl-alt-delete Over a year ago

Is it possible to set_bg_color a range of cells rather than whole column?

Collectives™ on Stack Overflow

pandas: how to format cells after exporting to Excel

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related