Bottom Page

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.
Quote
#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 :)
Quote
#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')
Quote
#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.
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Nested Loop for user input Ads 2 166 Dec-30-2019, 11:44 AM
Last Post: Ads
  nested for loop dilemma YoungGrassHopper 9 424 Sep-13-2019, 03:56 AM
Last Post: jsira2003
  nested for loop dilemma 2 YoungGrassHopper 12 511 Sep-12-2019, 02:06 PM
Last Post: YoungGrassHopper
  Nested while loop problem + turtle DreamingInsanity 3 459 Jul-06-2019, 02:01 PM
Last Post: DreamingInsanity
  Index error using pop in nested loop PerksPlus 3 539 Mar-28-2019, 03:11 PM
Last Post: ichabod801
  Nested for loop strange problem mcva 2 467 Mar-16-2019, 12:53 PM
Last Post: mcva
  Nested while loop in pyramid program. raj55 2 2,156 May-04-2018, 10:05 AM
Last Post: ThiefOfTime
  Nested Loop Problem GSWes 5 1,426 Nov-12-2017, 10:22 PM
Last Post: Larz60+
  Using nested for loop with a single list mikeavison 3 1,092 Aug-12-2017, 08:13 PM
Last Post: ichabod801
  A "Nested Loop" example from python 3.3.4 to 2.6!! sarada2099 6 2,565 Dec-18-2016, 02:56 PM
Last Post: sarada2099

Forum Jump:


Users browsing this thread: 1 Guest(s)