topic Re: how to add the background color to excel sheet by python in Data Engineering

how to add the background color to excel sheet by python

databicky — Tue, 17 Jan 2023 11:32:24 GMT

i just want to add color to excel sheet by python to specific cells, and i done that, but i need to exclude the header column, then if i tried the same method to other sheet it doesn't worked.

but that bg color addition is reflected in one sheet but the same method of code is not able to use to other sheet

i just want to add color based on cell address.

Re: how to add the background color to excel sheet by python

Hubert-Dudek — Tue, 17 Jan 2023 12:00:23 GMT

Convert your dataframe to pandas on spark
color cells using style property https://spark.apache.org/docs/latest/api/python/reference/pyspark.pandas/api/pyspark.pandas.DataFrame.style.html
export to excel using pandas to_excel https://spark.apache.org/docs/latest/api/python/reference/pyspark.pandas/api/pyspark.pandas.DataFrame.to_excel.html

Re: how to add the background color to excel sheet by python

databicky — Tue, 17 Jan 2023 16:38:35 GMT

from the pandas df i added the below color, but based on the same method i am not able to add bg in other sheets. it is not reflecting those things

And how can we extend the width size of the particular column in excel by python?

Re: how to add the background color to excel sheet by python

Hubert-Dudek — Tue, 17 Jan 2023 17:02:56 GMT

Separate dataframe for every sheet:

with pd.ExcelWriter('output.xlsx') as writer:  
 
     df1.to_excel(writer, sheet_name='Sheet_name_1')
     df2.to_excel(writer, sheet_name='Sheet_name_2')

For width, but I never used that:

df.style.set_properties(subset=['column'], **{'width': '300px'})