Loop-help for a newbie?

[pcsailor]

Thank you, thanks so much. It's really hard being a new coder, who's not natural at it, but driven.
It seems when one question is answered, two others are not being solved.
I'm getting there

Next question:
Why can't I change the active sheet?
Here's the code:

# create workbook
wb = openpyxl.Workbook()
print('all.sheetnames:', wb.sheetnames)
# set sheets
sheet = wb.active
sheet.title = 'Daily_Rounds'
s0 = sheet
s1 = wb.create_sheet('Colors')
print('all.sheetnames:', wb.sheetnames)
print('wb.index(s0/Daily_Rounds) =', wb.index(s0))
print('wb.index(s1/Colors) =', wb.index(s1))

# help: how do you change the active sheet?
print('sheet/wb.active =', sheet)
wb.active = wb.index(s1)
print('sheet/wb.active =', sheet)

I've tried a number of different combinations, discovered how to index through the active sheet cells, all without changing the sheet from 'Daily Rounds' to 'Colors'.

How do you change the active sheet to be able to write to it?

I owe you beers!
Phil

---

Can I explain this code out?

start_col = [2, 4, 6, 8]
end_col = [x + 1 for x in start_col]
 
for i in range(1, 6):
        if i in [1, 4]:
            sheet.merge_cells(start_row=i, start_column=1, end_row=i, end_column=9)
        else:
            for col1, col2 in zip(start_col, end_col):
                sheet.merge_cells(start_row=i, start_column=col1, end_row=i, end_column=col2)

• Create a 'start' list with 2, 4, 6, & 8 integers
  
• create an 'end' list that adds +1 for every integer in the 'start' list
  
• For every value (named i) within the range 1 to 6, but not including 6:
  
• if i is either 1 or 4:
  
• merge cells starting with row range from 1 to 5, going from columns 1 to 9
  
• or else:
  
• for variables col1 & col2 in the zip() function calling start_col & end_col:
  
• merge cells within row i (range from 1 to 5), going from col1 to col2
  

I'm not exactly clear on everything here, specifically the objective of the zip() function and what value the col1 and col2 have.

I see the zip() defination but it's still not clear (must be 02:30am!):

The zip() function take iterables (zero or more), makes iterator that aggregates elements based on the iterables passed, and returns an iterator of tuples.
Return Value from zip()
The zip() function returns an iterator of tuples based on the iterable object.
If no parameters are passed, zip() returns an empty iterator
If a single iterable is passed, zip() returns an iterator of 1-tuples. Meaning, the number of elements in each tuple is 1.
If multiple iterables are passed, ith tuple contains ith Suppose, two iterables are passed; one iterable containing 3 and other containing 5 elements. Then, the returned iterator has 3 tuples. It's because iterator stops when shortest iterable is exhaused.

Is my description correct? What am I missing?
Thanks,
phil

[pcsailor]

Hi Perfringo, Hi Buran,
I appreciate your help with my coding. It will come around.
I'd also like to ask your opinion on my last post above.
I actually have your code up and running and I've dissected it a bit adding elif statements. It's working well and I've (we've) turned 60 lines of code into about 10.
Thank you,
Phil

[buran]

Your description of the code is correct. To get understanding of zip

>>> list(zip([2, 4, 6, 8], [3, 5, 7, 9]))
[(2, 3), (4, 5), (6, 7), (8, 9)]

Note, in python3 it produces zip-object, so I convert to list for you to see what it is, but zip-object is iterable so you don't need to convert it to list in order to iterate over its elements.

so, given what start_col and end_col are

for col1, col2 in zip(start_col, end_col):

is same as

for col1, col2 in [(2, 3), (4, 5), (6, 7), (8, 9)]:

each element of the list (each is 2-element tuple) is unpacked in the variables. so on first iteration col1=2 and col2=3 and so on.
I would not use the if else as perfringo - no need to evaluate the if condition 5 times. And with this approach (to pass column and row numbers to merge) you can skip the zip and use list comprehension.

columns = [(col, col+1) for col in range(2, 9, 2)]
for row in (1, 4):
    sheet.merge_cells(start_row=row, start_column=1, end_row=row, end_column=9)
for row in (2, 3, 5):
    for col1, col2 in columns:
            sheet.merge_cells(start_row=row, start_column=col1, end_row=row, end_column=col2)

can you show your current code?

[pcsailor]

This merges the entire page exactly as I need:

start_col = [2, 4, 6, 8]
end_col = [x + 1 for x in start_col]

for i in range (1, 44): # this is row range
    # merges 9 cells into 1 in 1 row
    if i in [1, 4, 24, 41, 42, 43]:
        sheet.merge_cells(start_row=i, start_column=1, end_row=i, end_column=9)
    # merge 2 cells into 1 in 1 row
    elif i in [2, 3, 5, 8, 101, 11, 12, 13, 17, 18, 23, 25, 29, 30, 31, 32, 40]:
        sheet.merge_cells(start_row=i, start_column=2, end_row=i, end_column=3)
        sheet.merge_cells(start_row=i, start_column=4, end_row=i, end_column=5)
        sheet.merge_cells(start_row=i, start_column=6, end_row=i, end_column=7)
        sheet.merge_cells(start_row=i, start_column=8, end_row=i, end_column=9)
    # merge 2 cells into 1 and 4 cells into 1 cell, all in 1 row
    elif  i in [22, 39]:
        sheet.merge_cells(start_row=i, start_column=2, end_row=i, end_column=3)
        sheet.merge_cells(start_row=i, start_column=6, end_row=i, end_column=9)

Note the active cells in this sheet are rows 1-43 and columns A to I. I'm working on borders and alignment next.

Thanks for the through explanation on zip and col1/2. Zip seems like a handy function I need to know better.

Phil

[buran]

the way you did it you don't need the first 2 lines. However better do

for row in (1, 4):
    sheet.merge_cells(start_row=row, start_column=1, end_row=row, end_column=9)

columns = [(col, col+1) for col in range(2, 9, 2)]
for row in [2, 3, 5, 8, 11, 12, 13, 17, 18, 23, 25, 29, 30, 31, 32, 40, 101]:
    for col1, col2 in columns:
        sheet.merge_cells(start_row=row, start_column=col1, end_row=row, end_column=col2)

for row in (1, 4):
    sheet.merge_cells(start_row=row, start_column=2, end_row=row, end_column=3)
    sheet.merge_cells(start_row=row, start_column=6, end_row=row, end_column=9)

[perfringo]

I would not use the if else as perfringo - no need to evaluate the if condition 5 times.

I used if...else to preserve same order of rows as in sample code. I don't know is it important or not.