Search code examples
pythonexcelcopyopenpyxlpaste

Openpyxl - Empty Cells being added when copy and pasting data

My script copies data from each sheet from one excel workbook to the first sheet in another workbook. The script will also sort the data based on the value in column A.

My issue is that my current output is adding empty cells between data. I'm not sure why this is happening. Does anyone know why? Thank you.

enter image description here

from openpyxl import load_workbook
from openpyxl import Workbook

wb = load_workbook('testData.xlsx')
wb2 = load_workbook('testTemplate.xlsx')

get_sheet = wb.sheetnames

for sheet in get_sheet:
    
    ws = wb[sheet]
    mr = ws.max_row
    mc = ws.max_column

    ws2 = wb2['Sheet1']
    mr2 = ws2.max_row
    mc2 = ws2.max_column

    for i in range(2, mr + 2):
        for j in range(1, mc + 1):
            if ws.cell(row=i,column=j).value == "A":
                ws2.cell(row=mr2 + 1,column=j).value = ws.cell(row=i,column=j+1).value
            elif ws.cell(row=i,column=j).value == "B":
                ws2.cell(row=mr2 + 1,column=j+1).value = ws.cell(row=i,column=j+1).value
            elif ws.cell(row=i,column=j).value == "C":
                ws2.cell(row=mr2 + 1,column=j+2).value = ws.cell(row=i,column=j+1).value
                mr2 += 1

wb2.save('testTemplate.xlsx')
Solution

  • After Debugging your code finally got the point you are applying mr2 += 1 when Value = C and you giving enter after value of C comes.

    Code which solve your problem:

    from openpyxl import load_workbook
from openpyxl import Workbook

wb = load_workbook('testData.xlsx')
wb2 = load_workbook('testTemplate.xlsx')

get_sheet = wb.sheetnames

for sheet in get_sheet:
    
    ws = wb[sheet]
    mr = ws.max_row
    mc = ws.max_column

    ws2 = wb2['Sheet1']
    mr2 = ws2.max_row
    mc2 = ws2.max_column

    for i in range(2, mr + 2):
        for j in range(1, mc + 1):
            if ws.cell(row=i,column=j).value == "A":
                ws2.cell(row=mr2 + 1,column=j).value = ws.cell(row=i,column=j+1).value
                # check wheater B or C column has black space if not do mr2 += 1
                if ws2.cell(row=mr2 + 1,column=j+1).value !=None:
                    if ws2.cell(row=mr2 + 1,column=j+2).value!=None:
                        mr2 += 1
            elif ws.cell(row=i,column=j).value == "B":
                ws2.cell(row=mr2 + 1,column=j+1).value = ws.cell(row=i,column=j+1).value
                # check wheater A or C column has black space if not do mr2 += 1
                if ws2.cell(row=mr2 + 1,column=j).value !=None:
                    if ws2.cell(row=mr2 + 1,column=j+2).value!=None:
                        mr2 += 1
            elif ws.cell(row=i,column=j).value == "C":
                ws2.cell(row=mr2 + 1,column=j+2).value = ws.cell(row=i,column=j+1).value
                # check wheater A or B column has black space if not do mr2 += 1
                if ws2.cell(row=mr2 + 1,column=j).value !=None:
                    if ws2.cell(row=mr2 + 1,column=j+1).value!=None:
                        mr2 += 1

wb2.save('testTemplate.xlsx')

    Let me know if this helps you😊