Pandas: Create Excel worksheets with color tabs

3
votes

I have a total of 4 (df1, df2, df3, df4) pandas data frames. I would like to create a single excel sheet with 4 worksheets named 'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4' and would like to see each worksheet tab with different colors.

I have been trying with pandas but I cannot able to set the color. Please advise.

import padas as pd

writer = pd.ExcelWriter('example.xlsx', 
                        engine='xlsxwriter')

df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')
df3.to_excel(writer, sheet_name='Sheet3')
df4.to_excel(writer, sheet_name='Sheet4')

writer.save()
2
pythonexcelpandas
pandas.pydata.org/pandas-docs/stable/user_guide/style.html Check this link – Surya Tej
Also do you want the sheet backgrounds to be of different colors or the sheetnames at the bottom to be of different colors? – Surya Tej

2 Answers

2
votes

I guess you might be looking for this ...if you add worksheet then it will conflict with already in use error. First add your df and do other stuff with writer and xlsxwriter engine.

import pandas as pd
import xlsxwriter


df1 = pd.DataFrame([1,2,3,4,5,6])

writer = pd.ExcelWriter('example.xlsx', engine='xlsxwriter')

df1.to_excel(writer, sheet_name= 'Sheet1')

worksheet1 = writer.sheets['Sheet1']
worksheet1.set_tab_color('green')

writer.save()
1
votes

Does to_excel has a tab color function?

https://xlsxwriter.readthedocs.io/example_tab_colors.html

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html

If it doesn't then you cannot do this. You must use the xlsxwriter directly and have it write your data into a new worksheet after you set the tab color. 

import xlsxwriter

workbook = xlsxwriter.Workbook('tab_colors.xlsx')

# Set up some worksheets.
worksheet1 = workbook.add_worksheet()

# Set tab colors
worksheet1.set_tab_color('red')