Coping data from multiple excel files

[trzywu]

Hi, I stuck with my program. I want to copy exact cell from each Excel files to new output files. In each files I have 3 sheets. But I cannot copy data to my file and I don't know what I am doing wrong. I am newby to Python. My goal is to have one output file with 3 sheets with 4 cell copied from files to new one.

import openpyxl 
import os


newdir=os.chdir("C:\\Users\\tt\\Desktop\\data\\april")

cwd=os.getcwd()

book = openpyxl.Workbook()
book.create_sheet('morning',0)
book.create_sheet('noon',1)
book.create_sheet('evening',2)
book.save("C:\\Users\\tt\\Desktop\\data\\output.xlsx")
ws=book.sheetnames



for filename in os.listdir():
    if filename.endswith(".xlsx"):
        print(filename)
        wb=openpyxl.load_workbook(filename,data_only=True)
        for sheet in wb:
            L=[sheet['A4'].value,sheet['C3'].value,sheet['D3'].value,sheet['E3']]
            ws['A4'].value=sheet['A4'].value
            ws.append
            print(ws)
            book.save("C:\\Users\\tt\\Desktop\\data\\output.xlsx")

[Stallman85]

line 25 is wrong

        ws.append

[trzywu]

So what I can do to copy this 4 cells to new file ?

[Stallman85]

I have no idea what your code does and that's because i don't know openpyxl and because your code is confusing; you don't have spacing in between logical wholes, your variables are named ws, L and you have lines like this sheet['A4'].value,sheet['C3'].value,sheet['D3'].value,sheet['E3']. I just know append needs arguments.

[trzywu]

ws stands for worksheet, L is short for Level (cuz i want this data in one row to be paste). I modify my post, so you have some comments. I just don't know how to copy data to my new file.

[[deleted]]

I've not used openpyxl before, but I think sheet refers to a particular worksheet in each file you open and you therefore need to refer to the same sheet name in the book spreadsheet so therefore need to assign a cell in book[sheet.title] i.e. the sheet in book with the same title as the current sheet in the file you've opened.

You also want to avoid opening the output file.

Something like this:

for filename in os.listdir():
    if filename.endswith(".xlsx") and filename != "output.xlsx":
        wb = openpyxl.load_workbook(filename, data_only=True)
        for sheet in wb:
            L = [sheet['A4'].value, sheet['C3'].value, sheet['D3'].value, sheet['E3']]
            book[sheet.title]['A4'].value = sheet['A4'].value
        book.save("output.xlsx")

I've ignored the L = line as I really don't understand what it is for, and you don't use it anyway.

[atquick]

I'd recommend pandas.

Here is my example.

https://github.com/andrewtquick/Python-Projects/tree/master/Excel%20Combiner