Python Forum
openpyxl nested for loop help
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
openpyxl nested for loop help
#1
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.
Reply
#2
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 :)
Reply
#3
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')
or

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

from openpyxl import load_workbook as lowo
 
wb = lowo('questions.xlsx')
Reply
#4
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Big O runtime nested for loop and append yarinsh 4 1,331 Dec-31-2022, 11:50 PM
Last Post: stevendaprano
  Nested for loops - help with iterating a variable outside of the main loop dm222 4 1,532 Aug-17-2022, 10:17 PM
Last Post: deanhystad
  How do I add another loop to my nested loop greenpine 11 4,443 Jan-12-2021, 04:41 PM
Last Post: greenpine
  Error on nested loop : Invalid syntax dvazquezgu 3 3,178 Nov-25-2020, 10:04 AM
Last Post: palladium
  Nested loop indexing Morte 4 3,809 Aug-04-2020, 07:24 AM
Last Post: Morte
  Nested for loop not looping puttingwordstogether 0 1,672 Jun-16-2020, 11:15 PM
Last Post: puttingwordstogether
  Help: for loop with dictionary and nested lists mart79 1 1,834 Apr-12-2020, 02:52 PM
Last Post: TomToad
  Nested Loop for user input Ads 2 3,525 Dec-30-2019, 11:44 AM
Last Post: Ads
  nested for loop dilemma YoungGrassHopper 9 4,069 Sep-13-2019, 03:56 AM
Last Post: jsira2003
  nested for loop dilemma 2 YoungGrassHopper 12 4,785 Sep-12-2019, 02:06 PM
Last Post: YoungGrassHopper

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020