Moving sheets from excel files into one workbook using openpyxl

Question

So I've been trying to code a script which loads all excel files from a specific location and moves worksheets inside these files into one workbook. I'm ending with and error:

AttributeError: 'DataFrame' object has no attribute 'DataFrame'.

I'm pretty new to this so I would really appreciate any tip on how to make that work. I can stick only with openpyxl because at the moment I cannot install xlrd module on my workstation.

from pandas import ExcelWriter
import glob
import pandas as pd
import openpyxl

writer = ExcelWriter("output.xlsx")
for filename in glob.glob (r"C:\path\*.xlsx"):
    wb = openpyxl.load_workbook(filename)
    for ws in wb.sheetnames:
        ws = wb[ws]
        print (ws)
        data = ws.values
        columns = next(data)[0:]
        df= pd.DataFrame(data, columns=columns)
        print(df)
        for df in df.DataFrame:
            df.to_excel([writer,sheet_name= ws)

writer.save()

Does this answer your question? [How to concatenate three excels files xlsx using python?](https://stackoverflow.com/questions/15793349/how-to-concatenate-three-excels-files-xlsx-using-python) — Tomerikoo, Mar 01 '20 at 19:11

Jay Kakadiya · Accepted Answer · 2020-03-01T20:22:23.343

0

first you have to use sheet_name as a string not an object and another thing is last for loop is not needed as we loop through sheet names.

from pandas import ExcelWriter
import glob
import pandas as pd
import openpyxl


writer = ExcelWriter("output.xlsx")
for filename in glob.glob (r"C:\path\*.xlsx"):
    wb = openpyxl.load_workbook(filename)
    for ws in wb.sheetnames:
        ws1 = wb[ws]
        data = ws1.values
        columns = next(data)[0:]
        df= pd.DataFrame(data, columns=columns)
        df.to_excel(writer,sheet_name=ws,index = False)

writer.save()

edited Mar 01 '20 at 20:22

answered Mar 01 '20 at 20:05

Jay Kakadiya

501
1
5
12

it gives me an error: > Traceback (most recent call last): > File "C:/path/test.py", line 16, in > columns = next(data)[0:] > StopIteration – Michał Mar 01 '20 at 20:19
also make sure use ws1 in data = ws.values – Jay Kakadiya Mar 01 '20 at 20:26
Location is okay, I see frames being loaded and I changed ws to ws1. – Michał Mar 01 '20 at 20:29
unfortunately yes. – Michał Mar 01 '20 at 20:47
I have tried it on the different workstation, it works.Thanks for help – Michał Mar 02 '20 at 22:21
Cool glad to hear – Jay Kakadiya Mar 02 '20 at 23:23

Moving sheets from excel files into one workbook using openpyxl

1 Answers1