0

I have over 50 workbooks that I want to combine into one workbook as 50 sheets, with formatting, coloring, filling, etc. still in tact. This is what I tried:

import pandas as pd
import os
from openpyxl import load_workbook
from openpyxl import Workbook


path = "mypath"
directory = os.listdir(f'{path}')
files = [f for f in directory if f[-4:] == 'xlsx']
combined = Workbook()
ws = combined.active

for item in files:
    wb = load_workbook(filename = f'{path}/{item}')
    sheet = wb.sheetnames
    data = pd.read_excel(f'{path}/{item}',sheet_name=f'{sheet[0]}')
    data.to_excel(f'{path}/combined.xlsx',sheet_name=f'{sheet[0]}',header=None,index=None)

There were a couple issues with the result: 1. It overwrote the sheet each iteration, so the final workbook had 1 sheet, with information of only the last workbook. 2. The sheet did not retain the formatting

I'm essentially trying to copy each sheet into one workbook as I would using Excel's copy sheet command without having to do it 50 times.

YummyBlueberries
  • 37
  • 7
  • just looking at it. the sheet[0] is gonna overwrite just the first sheet.. soln ( use enumerate to get the index and use that as the sheet index to be saved as.) – Yatish Kadam Sep 27 '19 at 18:25
  • as for the formatting i would suggest using pandas maybe ? that is an easy way to read and write excel files. – Yatish Kadam Sep 27 '19 at 18:27
  • Possible duplicate of [Writing to an Excel spreadsheet](https://stackoverflow.com/questions/13437727/writing-to-an-excel-spreadsheet) – Yatish Kadam Sep 27 '19 at 18:28
  • I'd do this without Pandas. Open the workbooks in read-only mode and go row by row. – Charlie Clark Sep 28 '19 at 11:30

0 Answers0