CODEWITHSUNDEEP
pythonexcelopenpyxl
Penny
Penny

Reputation: 1280

How to write multiple sheets into a new excel, without overwriting each other?

I'm trying to write multiple excels' column A into a new excel's column A (assuming all the excels have one worksheet each.) I've written some code, which can write one excel's column A into the new excel's column A; but if there are multiple excels, the new excel's column A will be overwritten multiple times. So how could I just add all the column As to the new excel sheet one after another without overwriting each other? Below are my code:

import os, openpyxl

path = os.getcwd()

def func(file):
    for file in os.listdir(path):
        if file.endswith('.xlsx'):
            wb = openpyxl.load_workbook(file)
            sheet = wb.active
            colA = sheet['A']
            wb = openpyxl.Workbook()
            r = 1
            for i in colA:
                sheet = wb.active       
                sheet.cell(row=r, column=1).value = i.value
                r += 1
                wb.save('new.xlsx')
func(file)

Thank you so much!!

Upvotes: 0

Views: 1344

Answers (1)

ewcz
ewcz

Reputation: 13087

you could proceed for example as:

import os, openpyxl

path = os.getcwd()


def func(outputFile):
    c = 0

    #create output workbook
    wbOut = openpyxl.Workbook()
    sheetOut = wbOut.active

    for fName in os.listdir(path):
        if fName.endswith('.xlsx'):
            c += 1 #move to the next column in output

            wb = openpyxl.load_workbook(fName)
            sheet = wb.active #input sheet

            #for r in range(1, sheet.max_row+1):
            #    sheetOut.cell(row=r, column=c).value = sheet.cell(row = r, column = 1).value

            for r, cell in enumerate(sheet['A']):
                sheetOut.cell(row = r+1, column = c).value = cell.value

    wbOut.save(outputFile)

#"concatenate" all columns A into one single column
def funcAppend(outputFile):
    wbOut = openpyxl.Workbook()
    sheetOut = wbOut.active

    r = 1
    for fName in os.listdir(path):
        if fName.endswith('.xlsx'):
            wb = openpyxl.load_workbook(fName)
            sheet = wb.active

            for cell in sheet['A']:
                sheetOut.cell(row = r, column = 1).value = cell.value
                r += 1
    wbOut.save(outputFile)

func('test.xlsx')

Upvotes: 1

Related Questions