Openpyxl - Empty Cells being added when copy and pasting data-CodePudding

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.

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')

CodePudding user response：

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