python panas to_excel set cells background color

user_2038912 0
Viewed 532

I see worksheet.conditional_format() a lot, but how do I do unconditional formatting?
I've been using a condition that's almost always true... value!=2147483647(some value)

worksheet.conditional_format('B3:K12', 
      {'type':'cell', 'criteria':'!=', 'value':2147483647, 'format':my_format})

I see some functions that seem to set the format as you write cells, but I'm using Pandas, and hope I can set the format on the DataFrame or on the worksheet after running df.to_excel(..) as I can with conditional_format()

worksheet.set_row(   row,        height, cell_format, options)
worksheet.set_column(first, last, width, cell_format, options)
#  But I want to set a subset not a whole row/column
worksheet.write       (0, 0, 'Foo', cell_format)
worksheet.write_string(1, 0, 'Bar', cell_format)
worksheet.write_number(2, 0, 3,     cell_format)
worksheet.write_blank (3, 0, '',    cell_format)
#  And I'm not looking forward to writing every value myself.

I think my question has value because others must be having this problem too.
But I did not find a good solution.

https://xlsxwriter.readthedocs.io/working_with_pandas.html
XlsxWriter and Pandas provide very little support for formatting the output data from a dataframe apart from default formatting such as the header and index cells and any cells that contain dates or datetimes. In addition it isn’t possible to format any cells that already have a default format applied.
#
If you require very controlled formatting of the dataframe output then you would probably be better off using Xlsxwriter directly with raw data taken from Pandas. However, some formatting options are available.

https://www.ojdo.de/wp/2019/10/pandas-to-excel-with-openpyxl/
# for general styling, one has to iterate over all cells individually

https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html
The Styler creates an HTML table

I'm disappointed.

pandas excel colors
1 Answers

It looks like the best option is to write the data to the worksheet again, with a format.
I don't know if writing data twice has any hazards.
This works to do unconditional formatting but writes data twice.
To avoid writing the values twice, to_excel() could write a subset and then add formatted items.
BUT if you want to do more than one cell.. then you write that format with no values. It seems inconsistent. Why can't I write a format to a cell with no value?

import pandas as pd
df = pd.DataFrame(data={ 'col1':['col1v0','col1v1'], 'col2':['col2v0','col2v1'], })
with pd.ExcelWriter('output.xlsx') as writer:
    df.to_excel(writer, sheet_name='Sheet1')
    #
    workbook  = writer.book
    format_bg_red = workbook.add_format({'bg_color': 'red'})
    #
    worksheet = writer.sheets['Sheet1']
    worksheet.write('B2', 'NewItem', format_bg_red) # write again
    worksheet.set_row(1, height=22, cell_format=format_bg_red)
user_20389120
Related