Potential issue in Styler.to_excel() with Styler.set_properties()

[vkchan]

Code Sample, a copy-pastable example if possible

df = pd.DataFrame((np.random.rand(5, 4)*100), columns=list('ABCD'))
df_style = df.style.set_properties(**{'background-color': 'black',
                                       'color': 'lawngreen',
                                       'border-color': 'white'})
display(df_style)
df_style.to_excel('test.xlsx')

Problem description

I was using the Styler.set_properties() function to format the Pandas Dataframe, while it (df_style) shows up nicely in Jupyter Notebook, it caused an error in the Styler.to_excel() function. Could you please advise how I can fix it?

[Issue also posted in stackoverflow.com: https://stackoverflow.com/questions/59161827/error-in-pandas-styler-set-properties-with-styler-to-excel]

Expected Output

Error in Styler.to_excel():

TypeError: tuple indices must be integers or slices, not dict

Output of pd.show_versions()

[paste the output of pd.show_versions() here below this line]
INSTALLED VERSIONS

commit : None
python : 3.7.5.final.0
python-bits : 64
OS : Linux
OS-release : 4.15.0-1054-aws
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : C.UTF-8
LOCALE : en_US.UTF-8

pandas : 0.25.3
numpy : 1.17.3
pytz : 2019.3
dateutil : 2.8.0
pip : 19.3.1
setuptools : 42.0.1.post20191125
Cython : 0.29.14
pytest : 5.3.0
hypothesis : None
sphinx : 2.2.1
blosc : None
feather : None
xlsxwriter : 1.2.6
lxml.etree : 4.4.1
html5lib : 1.0.1
pymysql : None
psycopg2 : 2.8.4 (dt dec pq3 ext lo64)
jinja2 : 2.10.3
IPython : 7.9.0
pandas_datareader: None
bs4 : 4.8.1
bottleneck : 1.3.1
fastparquet : None
gcsfs : None
lxml.etree : 4.4.1
matplotlib : 3.1.1
numexpr : 2.7.0
odfpy : None
openpyxl : 3.0.1
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : 1.3.1
sqlalchemy : 1.3.11
tables : 3.6.1
xarray : None
xlrd : 1.2.0
xlwt : 1.3.0
xlsxwriter : 1.2.6

[attack68]

For more context, CSS borders have complex rules, for example you can express a border in one line with: border: 1px solid red;

Or you can do the same thing for each side: border-left: 1px solid green.

Or you can specify sub-properties: border-color: blue; border-width: 2px;

Or you can use short hand to specify 'all', 'top/bottom, left/right' or 'top left/right bottom' or 'top right bottom left': border-color: blue; border-color: blue green; border-color: blue green red; border-color: blue green red yellow;

Although there seems to be some complex border writing methods in pandas they don't seem to work with interpreting Styler CSS at all.

The following throws an error due to the border-color css rule:

df = pd.DataFrame((np.random.rand(5, 4)*100), columns=list('ABCD'))
df_style = df.style.set_properties(**{'background-color': 'black',
                                       'color': 'lawngreen',
                                       'border-color': 'white'})
df_style.to_excel('test.xlsx')

The following does not throw an error but the border is not rendered to Excel, instead only the background-color and font color are rendered.

df = pd.DataFrame((np.random.rand(5, 4)*100), columns=list('ABCD'))
df_style = df.style.set_properties(**{'background-color': 'black',
                                       'color': 'lawngreen',
                                       'border': 'medium solid red'})
df_style.to_excel('test.xlsx')

I can't debug the reason, and I don't yet know the best solution. Is there the resident Excel from CSS expert who can comment?

[attack68]

closed as recorded in master tracker #42276