I want to freeze the column names and like from large files when im scrolling down to have always the name of the columns.

This is my script where im also creating the excel file with multiple excels as sheets.

import numpy as np
import pandas as pd
from timestampdirectory import  createdir
import openpyxl
import xlsxwriter
from openpyxl import workbook
from openpyxl import worksheet

import os
import time
def svnanalysis():

    dest = createdir()
    dfSvnUsers = pd.read_excel(os.path.join(dest, "SvnUsers.xlsx"))
    dfSvnUsers.fillna("N/A", inplace=True)
    dfSvnGroupMembership = pd.read_excel(os.path.join(dest, "SvnGroupMembership.xlsx"))
    dfSvnRepoGroupAccess = pd.read_excel(os.path.join(dest, "SvnRepoGroupAccess.xlsx"))
    dfsvnReposSize = pd.read_excel(os.path.join(dest, "svnReposSize.xlsx"))
    dfsvnRepoLastChangeDate = pd.read_excel(os.path.join(dest, "svnRepoLastChangeDate.xlsx"))
    dfUserDetails = pd.read_excel(r"D:\GIT-files\Automate-Stats\SVN_sample_files\CM_UsersDetails.xlsx")
    dfUserDetails.fillna("N/A", inplace=True)

    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)
        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)

    # xlwriter.freeze_columns(1, 0)  # # Freeze the first row.

    xlwriter.close()

    #usage = pd.read_excel(os.path.join(dest,f'{timestr}Usage-SvnAnalysis.xlsx'))
    #usage.style.set_table_styles([
     #   {'selector': 'thead th', 'props': 'position: sticky; top:0; background-color:red;'},
      #  {'selector': 'tbody th', 'props': 'position: sticky; left:0; background-color:green;'}
    #]).to_html()


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

At the end of the script with the "#"(commented lines) its what i tried, on the part with table_styles... all works but its not freezing or changeing the color for first row of each collor (nome of collumn)

this is the exported excel:

and bassicly now when im scrolling down the column names should always appears and have "blue" background but as i say n the # line of code all works but its not applying idk why

CodePudding user response：

try:

with pd.ExcelWriter(Your_FilePath, engine='xlsxwriter') as writer:
    dfUserDetails.to_excel(xlwriter, sheet_name='UserDetails',index = False)

    # etc etc ...

    for sht_name in writer.sheets:
            ws = writer.sheets[sht_name]
            ws.freeze_panes(1, 0)

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

CodePudding user response：

    for dfSvnUsers in xlwriter.sheets:
        ws = xlwriter.sheets['SvnUsers']
        ws.freeze_panes(1, 0)

        ws.style.set_table_styles([
        {'selector': 'thead th', 'props': 'position: sticky; top:0; background-color:red;'},
        {'selector': 'tbody th', 'props': 'position: sticky; left:0; background-color:green;'}
        ]).to_html()

so i updated the script, now work and freeze the first row with column names for the specific sheet, but i tried to change also the collor of that background in "red" and didnt work,