openpyxl nested for loop help - Printable Version

openpyxl nested for loop help - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: openpyxl nested for loop help (/thread-21822.html)

openpyxl nested for loop help - rmrten - Oct-16-2019

Hi i am new here and into coding with python ( thanks for patience :) ).
I am trying to write simple for loop which will use openpyxl to "search and replace" inside excel file and do this with the values in the list and save results as separate excel file for each list value.

import openpyxl
from openpyxl import load_workbook

wb = openpyxl.load_workbook('questions.xlsx')
ws = wb.worksheets[0]
ws2 = wb.worksheets[1]

list = ["value1", "value2"]


for account in list:
    for i in range(1, 3):
        if ws.cell(column=1, row=i).value is not None:
            ws.cell(column=1, row=i).value = ws.cell(
                column=1, row=i).value.replace(str("X"), account)
        for i in range(1, 125):
            if ws2.cell(column=2, row=i).value is not None:
                ws2.cell(column=2, row=i).value = ws2.cell(
                    column=2, row=i).value.replace(str("X"), account)
    wb.save('./test/' + 'questions_' + account + '.xlsx')

My result now is:
Two separate files called questions_value1.xlsx and questions_value2.xlsx
But inside both excel files "X" is replaced by value1 only.

Result i am looking for:
Two separate excel files called questions_value1.xlsx and questions_value2.xlsx where:
"X" will be replaced by value1 in file questions_value1.xlsx and by value2 in questions_value2.xlsx.

Can someone please explain to me where is the "catch"? I did a lot of reading and googling back and forth but end up only with broken code and messed up head :)

Thanks.

RE: openpyxl nested for loop help - LocalFolder - Oct-16-2019

Hello,

Newbie here too :)
For what I understand, at loop 1 you replace all the "X" by "value1" in the object wb in memory. At loop 2 no "X" to be replaced wil be found in wb.

Creating a new destination file object at each loop can allow you to save the X replaced without impacting the original file

import openpyxl
from openpyxl import load_workbook
from openpyxl import Workbook #add
 
wb = openpyxl.load_workbook('questions.xlsx')
ws = wb.worksheets[0]
ws2 = wb.worksheets[1]
 
list = ["value1", "value2"]

for account in list:
    wdest=Workbook() #add
    wdest.create_sheet('result') #add
    Sheetdest=wdest['result'] #add
    for i in range(1, 3):
        if ws.cell(column=1, row=i).value is not None:
            Sheetdest.cell(column=1, row=i).value = ws.cell(column=1, row=i).value.replace(str("X"), account) #modify
        for i in range(1, 20):
            if ws2.cell(column=2, row=i).value is not None:
                Sheetdest.cell(column=2, row=i).value = ws2.cell(column=2, row=i).value.replace(str("X"), account)#modify
    wdest.save('./test/' +'questions_' + account + '.xlsx') #modify

Have a nice day :)

RE: openpyxl nested for loop help - baquerik - Oct-16-2019

On a different note, you are mentioning "openpyxl" 3 times making it redudant.

Instead you could do:

import openpyxl

wb = openpyxl.load_workbook('questions.xlsx')

from openpyxl import load_workbook
 
wb = load_workbook('questions.xlsx')

from openpyxl import load_workbook as lowo
 
wb = lowo('questions.xlsx')

RE: openpyxl nested for loop help - stullis - Oct-16-2019

LocalFolder got it right.

Also, do not use "list" as a variable name. That name is already in use for the list type and assigning a value to it overwrites that connection in your program. If you were to use list() later in the program, it would raise an error. At the very least, you can add an underscore (list_) to distinguish it.