I am trying to understand how I can add to my current script where I'm able to make changes at sheet level. I want to be able to delete columns from the worksheets in my flat file here. For example, if a column is called 'company' I want to delete it so that my final wb.save drops those columns. I have multiple column names i want to drop from all sheets in the wb-

cols_to_drop = ['Company','Type','Firstname','lastname']

My code so far where I have managed to delete a specific sheet from a file and update colnames is below-

from openpyxl import load_workbook
import os
column_name_update_map = {'LocationName': 'Company Name','StreetAddress':'Address','City':'City','State':'State',
                                  'Zip':'Zip','GeneralPhone':'Phone Number','GeneralEmail':'Email','DateJoined':'Status Date',
                                  'Date Removed':'Status Date'} 

        
for file in os.listdir("C:/Users/hhh/Desktop/aaa/python/Matching"):
    if file.startswith("TVC"):
        wb = load_workbook(file)
        if 'Opt-Ins' in wb.sheetnames:
            wb.remove(wb['Opt-Ins'])
            wb.remove(wb['New Voting Members'])
            wb.remove(wb['Temporary Members'])

        for ws in wb:
            for header in next(ws.rows):
                try:
                    header.value = column_name_update_map[header.value]
                except KeyError:
                    pass
        
        wb.save(file   " (updated headers).xlsx")

This part of the code works perfectly and gives me the desired result. however, I'm unable to apply a dataframe logic like df.drop(['Company', 'Type', 'Firstname'], axis=1) since it is a workbook and not a dataframe

CodePudding user response：

Since you've tagged the question as pandas, you could just use pandas to read and drop:

for file in os.listdir("C:/Users/hhh/Desktop/aaa/python/Matching"):
    if file.startswith("TVC"):
        dfs = pd.read_excel(file, sheet_name=None)
        output = dict()
        for ws, df in dfs.items():
            if ws in ["Opt-Ins", "New Voting Members", "Temporary Members"]:
                continue
            #drop unneeded columns
            temp = df.drop(cols_to_drop, errors="ignore", axis=1)
            #rename columns
            temp = temp.rename(columns=column_name_update_map)
            #drop empty columns
            temp = temp.dropna(how="all", axis=1)
            output[ws] = temp
        writer = pd.ExcelWriter(f'{file.replace(".xlsx","")} (updated headers).xlsx')
        for ws, df in output.items():
            df.to_excel(writer, index=None, sheet_name=ws)
        writer.save()
        writer.close()