Append excel files using pandas

Question

Can use either of these not just pandas import csv pandas openpyxl import load_workbook openpyxl.utils import get_column_letter xlrd glob

Want to combine multiple excel files: I have tried this simpleapproach->

# collect excel content into list of dataframes
data = []
for excel_file in excel_files:
    data.append(pd.read_excel(excel_file, engine="openpyxl"))
    data.append(gapDf)

# concatenate dataframes horizontally
df = pd.concat(data, axis=1)
# save combined data to excel
df.to_excel(excelAutoNamed, index=False)

Problem Above approach messes with the formatting of how the excel file initially was because it just adds everything to a dataframe list and appends it** which gets rid of column highlights ,bolding

Instead of converting them to df then concating, how to add multiple excel files to one workbook

example

Idea keep track of the previous excel files last column b/c to leave some gap to write the next excel file on the workBook. (quickNote: trying a dynamic approach bc not all excel files have the same # of columns)

Answer 1

Try this. Based partially off Oscar's answer https://stackoverflow.com/a/68800310/3589122

import openpyxl
from copy import copy
import os

# Tuple of filenames
filenames = ("spreadsheet1.xlsx", 
             "spreadsheet2.xlsx", 
             "spreadsheet3.xlsx", 
             "spreadsheet4.xlsx", 
            )

# Create a new workbook
new_wb = openpyxl.Workbook()
new_ws = new_wb.active

# column_num is the next column number to be written to in the new spreadsheet
column_num = 1

# Read each workbook, and copy each column, cell by cell, to the new spreadsheet
for filename in filenames:
    wb = openpyxl.load_workbook(filename)
    ws = wb.active
    
    for column in ws.iter_cols():
        for cell in column:
            new_cell = new_ws.cell(row=cell.row, column=column_num, value=cell.value)  
            
            # Styles have to be manually copied
            if cell.has_style:
                new_cell.font = copy(cell.font)
                new_cell.border = copy(cell.border)
                new_cell.fill = copy(cell.fill)
                new_cell.number_format = copy(cell.number_format)
                new_cell.protection = copy(cell.protection)
                new_cell.alignment = copy(cell.alignment)
        
        # Preserve the same column width
        width = ws.column_dimensions[cell.column_letter].width
        new_ws.column_dimensions[new_cell.column_letter].width = width
        
        column_num += 1    

# Save the new workbook to disk
new_filename = "combined.xlsx"
new_wb.save(new_filename)

# Launch the new spreadsheet
os.startfile(new_filename)