Pandas change row/column color background of cells

Question

I wrote a script that freeze the first row that contains the columns names, but I want to make the background with "red". I tried using style, but it did not work.

I get this error

I want to change the color only for the first row for the column names like. The column name to be with blue

Answer 1

As we discuessed, your use case actually involves the following steps.

1. Read multiple xlsx files having one sheet
2. Create another xlsx file and write everything from the xlsx files to the different sheets in the new xlsx file.

There are 2 ways in which you can do this

1. Using xlsxwriter

You can loop through each sheet, and set the format for the row/column you need.

from timestampdirectory import createdir
from xlsxwriter.utility import xl_rowcol_to_cell
import xlsxwriter
import pandas as pd
import time
import os
import pandas.io.formats.excel
pandas.io.formats.excel.ExcelFormatter.header_style = None


def svnanalysis():
    dest = createdir()
    dfSvnUsers              = pd.read_excel(os.path.join(dest, "SvnUsers.xlsx")).fillna("N/A")
    dfSvnGroupMembership    = pd.read_excel(os.path.join(dest, "SvnGroupMembership.xlsx")).fillna("N/A")
    dfSvnRepoGroupAccess    = pd.read_excel(os.path.join(dest, "SvnRepoGroupAccess.xlsx")).fillna("N/A")
    dfsvnReposSize          = pd.read_excel(os.path.join(dest, "svnReposSize.xlsx")).fillna("N/A")
    dfsvnRepoLastChangeDate = pd.read_excel(os.path.join(dest, "svnRepoLastChangeDate.xlsx")).fillna("N/A")
    dfUserDetails           = pd.read_excel(r"C:\Users\hpoddar\Desktop\Temp\Champs\CM_UsersDetails.xlsx").fillna("N/A")

    timestr = time.strftime("%Y-%m-%d-")
    xlwriter = pd.ExcelWriter(os.path.join(dest, f'{timestr}Usage-SvnAnalysis.xlsx'))
    
    dfUserDetails.to_excel(xlwriter, sheet_name='UserDetails', index=False)
    dfSvnUsers.to_excel(xlwriter, sheet_name='SvnUsers', index=False)
    dfSvnGroupMembership.to_excel(xlwriter, sheet_name='SvnGroupMembership', index=False)
    dfSvnRepoGroupAccess.to_excel(xlwriter, sheet_name='SvnRepoGroupAccess', index=False)
    dfsvnReposSize.to_excel(xlwriter, sheet_name='svnReposSize', index=False)
    dfsvnRepoLastChangeDate.to_excel(xlwriter, sheet_name='svnRepoLastChangeDate', index=False)

    for column in dfSvnUsers:
        column_width = max(dfSvnUsers[column].astype(str).map(len).max(), len(column))
        col_idx = dfSvnUsers.columns.get_loc(column)
        
        # Width of column `col_idx` set to column_width.
        xlwriter.sheets['SvnUsers'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['UserDetails'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['SvnGroupMembership'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['SvnRepoGroupAccess'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['svnReposSize'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['svnRepoLastChangeDate'].set_column(col_idx, col_idx, column_width)

    workbook = xlwriter.book
    # fomrat the header row
    cell_format = workbook.add_format({'bg_color': 'yellow'})
    cell_format.set_bold()
    cell_format.set_font_color('red')
    cell_format.set_border(1)

    # for each sheet in Usage-SvnAnalysis
    for sheet_name in xlwriter.sheets:
        ws = xlwriter.sheets[sheet_name]
        ws.freeze_panes(1, 0) # Freeze the first row.

        ws.conditional_format('A1:{}1'.format(chr(65 + ws.dim_colmax)), {'type': 'no_blanks', 'format': cell_format})
        
    xlwriter.close()

    print("UsageSvnAnalysis.xlsx a fost exportat cu succes continand ca sheet toate xlsx anterioare")


svnanalysis()

2. Using openpyxl, you can read the xlsx again, go through each sheets and format the cells you need

from openpyxl.styles import PatternFill, Border, Font, Side
from timestampdirectory import createdir
import openpyxl 
import time
import os

dest=createdir()
timestr = time.strftime("%Y-%m-%d-")

xls = openpyxl.load_workbook(os.path.join(dest, f'{timestr}Usage-SvnAnalysis.xlsx'))

# create styles
font_style = Font(bold=True)
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
fill_cell = PatternFill(patternType='solid',fgColor='35FC03')

for sheet_name in xls.sheetnames:
    ws = xls[sheet_name]
    
    # apply style to first row first column
    ws.cell(row=1, column=1).font = font_style
    ws.cell(row=1, column=1).border = thin_border
    ws.cell(row=1, column=1).fill = fill_cell

    # to color the entire row
    # Enumerate the cells in the first row
    for cell in ws["1:1"]:
        cell.font = font_style
        cell.border = thin_border
        cell.fill = fill_cell
        
xls.save(os.path.join(dest, f'{timestr}Usage-SvnAnalysis.xlsx'))

Output :