Loop-help for a newbie?

[buran]

I think the problem is you have missing {i}, i.e. row number

sheet.merge_cells(f'{pair[0]}:{pair[1]}{i}')

should be

sheet.merge_cells(f'{pair[0]}{i}:{pair[1]}{i}')

[pcsailor]

Hi Buran,
That's a typo within the forum I believe. With the {i} there, the script does not even run. On my file, without that f-string line, the code prints out with the f-string. When I add the sheet.merge_cells, error.
Here's the entire code file I am using if you want to try in it idle:

#!/usr/bin/env python3

# imports
import os
import openpyxl
from openpyxl.styles import Font#, Style # HELP: Style not importing
from openpyxl.styles import PatternFill

# Set directory
print(os.getcwd())
os.chdir('C:\\Users\\pcurtis7\\Desktop\\_myScripts\\python_excel')
print(os.getcwd())

# create workbook
wb = openpyxl.Workbook()
wb.save('daily_log_v1-loops.xlsx')
print('type(wb):', type(wb))
print('wb.sheetnames:', wb.sheetnames)
print('wb.active:', wb.active)

# set sheets
sheet = wb.active
print('wb.active:', wb.active)
sheet.title = 'Daily_Rounds'
print('wb.active:', wb.active)
print('sheet.title:', sheet.title)
wb.save('daily_log_v1-loops.xlsx')

# Merge Cells
# sheet.merge_cells(':')

cols = list(zip(['B', 'D', 'F', 'H'], ['C', 'E', 'G', 'I'])) # same as this: "cols = [('B', 'C'), ('D', 'E'), ('F', 'G'), ('H', 'I')]"
for i in range(10, 13):
    for pair in cols:
        print(f'{pair[0]}{i}:{pair[1]}{i}')
        sheet.merge_cells(f'{pair[0]{i}}:{pair[1]}{i}')

'''
sheet.merge_cells('A1:I1')
sheet.merge_cells('B2:C2')
sheet.merge_cells('D2:E2')
sheet.merge_cells('F2:G2')
sheet.merge_cells('H2:I2')
sheet.merge_cells('B3:C3')
sheet.merge_cells('D3:E3')
sheet.merge_cells('F3:G3')
sheet.merge_cells('H3:I3')
sheet.merge_cells('A4:I4')
sheet.merge_cells('B5:C5')
sheet.merge_cells('D5:E5')
sheet.merge_cells('F5:G5')
sheet.merge_cells('H5:I5')
sheet.merge_cells('B8:C8')
sheet.merge_cells('D8:E8')
sheet.merge_cells('F8:G8')
sheet.merge_cells('H8:I8')
sheet.merge_cells('B10:C10')
sheet.merge_cells('D10:E10')
sheet.merge_cells('F10:G10')
sheet.merge_cells('H10:I10')
sheet.merge_cells('B11:C11')
sheet.merge_cells('D11:E11')
sheet.merge_cells('F11:G11')
sheet.merge_cells('H11:I11')
sheet.merge_cells('B12:C12')
sheet.merge_cells('D12:E12')
sheet.merge_cells('F12:G12')
sheet.merge_cells('H12:I12')
sheet.merge_cells('B13:C13')
sheet.merge_cells('D13:E13')
sheet.merge_cells('F13:G13')
sheet.merge_cells('H13:I13')
sheet.merge_cells('B17:C17')
sheet.merge_cells('D17:E17')
sheet.merge_cells('F17:G17')
sheet.merge_cells('H17:I17')
sheet.merge_cells('B18:C18')
sheet.merge_cells('D18:E18')
sheet.merge_cells('F18:G18')
sheet.merge_cells('H18:I18')
sheet.merge_cells('B22:C22')
sheet.merge_cells('D22:E22')
sheet.merge_cells('F22:G22')
sheet.merge_cells('H22:I22')
sheet.merge_cells('B23:C23')
sheet.merge_cells('D23:E23')
sheet.merge_cells('F23:G23')
sheet.merge_cells('H23:I23')
sheet.merge_cells('A24:I24')
sheet.merge_cells('B25:C25')
sheet.merge_cells('D25:E25')
sheet.merge_cells('F25:G25')
sheet.merge_cells('H25:I25')
sheet.merge_cells('B29:C29')
sheet.merge_cells('D29:E29')
sheet.merge_cells('F29:G29')
sheet.merge_cells('H29:I29')
sheet.merge_cells('B30:C30')
sheet.merge_cells('D30:E30')
sheet.merge_cells('F30:G30')
sheet.merge_cells('H30:I30')
sheet.merge_cells('B31:C31')
sheet.merge_cells('D31:E31')
sheet.merge_cells('F31:G31')
sheet.merge_cells('H31:I31')
sheet.merge_cells('B32:C32')
sheet.merge_cells('D32:E32')
sheet.merge_cells('F32:G32')
sheet.merge_cells('H32:I32')
sheet.merge_cells('B39:C39')
sheet.merge_cells('D39:E39')
sheet.merge_cells('F39:G39')
sheet.merge_cells('H39:I39')
sheet.merge_cells('B40:C40')
sheet.merge_cells('D40:E40')
sheet.merge_cells('F40:G40')
sheet.merge_cells('H40:I40')
sheet.merge_cells('A41:I41')
sheet.merge_cells('A42:I42')
sheet.merge_cells('A43:I43')
wb.save('daily_log_v1-loops.xlsx')
'''

# Header Rows
# sheet[''].value = ''
sheet['A1'].value = 'Note: When doing rounds be aware for unusual smells, sounds, sights, or anything not normal.'
sheet['A2'].value = 'Engineer Initials:'
sheet['B2'].value = 'Int.__________'
sheet['D2'].value = 'Int.__________'
sheet['F2'].value = 'Int.__________'
sheet['H2'].value = 'Int.__________'
sheet['A3'].value = 'Time of Round'
sheet['B3'].value = '02:00'
sheet['D3'].value = '08:00'
sheet['F3'].value = '14:00'
sheet['H3'].value = '20:00'



# Engineer Round Values

# Hz
sheet['C6'].value = 'Hz'
sheet['E6'].value = 'Hz'
sheet['G6'].value = 'Hz'
sheet['I6'].value = 'Hz'
sheet['C7'].value = 'Hz'
sheet['E7'].value = 'Hz'
sheet['G7'].value = 'Hz'
sheet['I7'].value = 'Hz'
sheet['C9'].value = 'Hz'
sheet['E9'].value = 'Hz'
sheet['G9'].value = 'Hz'
sheet['I9'].value = 'Hz'
sheet['C14'].value = 'Hz'
sheet['E14'].value = 'Hz'
sheet['G14'].value = 'Hz'
sheet['I14'].value = 'Hz'
sheet['C15'].value = 'Hz'
sheet['E15'].value = 'Hz'
sheet['G15'].value = 'Hz'
sheet['I15'].value = 'Hz'
sheet['C16'].value = 'Hz'
sheet['E16'].value = 'Hz'
sheet['G16'].value = 'Hz'
sheet['I16'].value = 'Hz'
sheet['C19'].value = 'Hz'
sheet['E19'].value = 'Hz'
sheet['G19'].value = 'Hz'
sheet['I19'].value = 'Hz'
sheet['C20'].value = 'Hz'
sheet['E20'].value = 'Hz'
sheet['G20'].value = 'Hz'
sheet['I20'].value = 'Hz'
sheet['C26'].value = 'Hz'
sheet['E26'].value = 'Hz'
sheet['G26'].value = 'Hz'
sheet['I26'].value = 'Hz'
sheet['C27'].value = 'Hz'
sheet['E27'].value = 'Hz'
sheet['G27'].value = 'Hz'
sheet['I27'].value = 'Hz'
sheet['C28'].value = 'Hz'
sheet['E28'].value = 'Hz'
sheet['G28'].value = 'Hz'
sheet['I28'].value = 'Hz'
sheet['C33'].value = 'Hz'
sheet['E33'].value = 'Hz'
sheet['G33'].value = 'Hz'
sheet['I33'].value = 'Hz'
sheet['C34'].value = 'Hz'
sheet['E34'].value = 'Hz'
sheet['G34'].value = 'Hz'
sheet['I34'].value = 'Hz'
sheet['C35'].value = 'Hz'
sheet['E35'].value = 'Hz'
sheet['G35'].value = 'Hz'
sheet['I35'].value = 'Hz'
sheet['C36'].value = 'Hz'
sheet['E36'].value = 'Hz'
sheet['G36'].value = 'Hz'
sheet['I36'].value = 'Hz'
sheet['C37'].value = 'Hz'
sheet['E37'].value = 'Hz'
sheet['G37'].value = 'Hz'
sheet['I37'].value = 'Hz'

# Notes
sheet['A41'].value = 'Notes:'



# Styling
# Samples
# Darkest to lightest
sheet['K3'].value = '696969'
sheet['K2'].value = '808080'
sheet['K4'].value = 'A9A9A9'
sheet['K5'].value = 'C0C0C0'
# these two look beige
sheet['K1'].value = 'D3D3D3'
sheet['K6'].value = 'DCDCDC'

sheet['J1'].fill = PatternFill(fgColor='D3D3D3', fill_type = 'solid')
sheet['J2'].fill = PatternFill(fgColor='808080', fill_type = 'solid')
sheet['J3'].fill = PatternFill(fgColor='696969', fill_type = 'solid')
sheet['J4'].fill = PatternFill(fgColor='A9A9A9', fill_type = 'solid')
sheet['J5'].fill = PatternFill(fgColor='C0C0C0', fill_type = 'solid')
sheet['J6'].fill = PatternFill(fgColor='DCDCDC', fill_type = 'solid')
# End

# Active Cells
sheet['A1'].fill = PatternFill(fgColor='DCDCDC', fill_type = 'solid')
#A2
sheet['A2'].fill = PatternFill(fgColor='DCDCDC', fill_type = 'solid')
sheet['B2'].fill = PatternFill(fgColor='DCDCDC', fill_type = 'solid')
sheet['D2'].fill = PatternFill(fgColor='DCDCDC', fill_type = 'solid')
sheet['F2'].fill = PatternFill(fgColor='DCDCDC', fill_type = 'solid')
sheet['H2'].fill = PatternFill(fgColor='DCDCDC', fill_type = 'solid')
# A3
sheet['A3'].fill = PatternFill(fgColor='DCDCDC', fill_type = 'solid')
sheet['B3'].fill = PatternFill(fgColor='DCDCDC', fill_type = 'solid')
sheet['D3'].fill = PatternFill(fgColor='DCDCDC', fill_type = 'solid')
sheet['F3'].fill = PatternFill(fgColor='DCDCDC', fill_type = 'solid')
sheet['H3'].fill = PatternFill(fgColor='DCDCDC', fill_type = 'solid')

sheet['A4'].fill = PatternFill(fgColor='808080', fill_type = 'solid')
sheet['A24'].fill = PatternFill(fgColor='808080', fill_type = 'solid')
sheet['A41'].fill = PatternFill(fgColor='A9A9A9', fill_type = 'solid')

wb.save('daily_log_v1-loops.xlsx')

[buran]

note that your line is not correct again - you have put {i} within the curly braces for row[0]

sheet.merge_cells(f'{pair[0]{i}}:{pair[1]}{i}')

shoud be

sheet.merge_cells(f'{pair[0]}{i}:{pair[1]}{i}')

[pcsailor]

Hey Buran,

You are 100% correct! I had an extra curly brace in that line causing the error.
Now it's woring like a charm with good Excel output.
Sad to say, it's not the first time I've made a typo that someone else has corrected.
Being a career systems and operational marine engineer, it's humbling how of my attention to the coding details is the cause of my current problem.
:sigh
Thanks Buran!
Phil

[pcsailor]

Is this line of code:

sheet.merge_cells(f'{pair[0]}{i}:{pair[1]}{i}')

, what is are the 0 and 1 indexes referring to?
The 10 and 13 arguments within range?
Thanks,
Phil

[buran]

in perfringo's code you have

for pair in cols

in each iteration pair will be 2-elemt tuple, e.g. ('B', 'C')
0 and 1 refer to index of each element, i.e. pair[0] is 'B' and pair[1] is 'C'.

Compare it to my code where I used col1, and col2 to unpack the tuple directly in two variables, instead of using one variable that is container and use index to access its elements

[pcsailor]

I am still having difficultly and I wonder if I can get some opinions. I think I would prefer a solution not using f-strings (yet). No to be picky, but f-strings have an issue with Openpyxl's cellRange() int type and the simpler solution for me right now is better. So how could I loop this code below using %-formatting or str.formating?

# sheet.merge_cells(start_row=, start_column=, end_row=, end_column=)
sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=9) # Note:

sheet.merge_cells(start_row=2, start_column=2, end_row=2, end_column=3) # Int.__
sheet.merge_cells(start_row=2, start_column=4, end_row=2, end_column=5) # Int.__
sheet.merge_cells(start_row=2, start_column=6, end_row=2, end_column=7) # Int.__
sheet.merge_cells(start_row=2, start_column=8, end_row=2, end_column=9) # Int.__

sheet.merge_cells(start_row=3, start_column=2, end_row=3, end_column=3) # 02:00
sheet.merge_cells(start_row=3, start_column=4, end_row=3, end_column=5) # 08:00
sheet.merge_cells(start_row=3, start_column=6, end_row=3, end_column=7) # 16:00
sheet.merge_cells(start_row=3, start_column=8, end_row=3, end_column=9) # 20:00

sheet.merge_cells(start_row=4, start_column=1, end_row=4, end_column=9) # server Rm #1

sheet.merge_cells(start_row=5, start_column=2, end_row=5, end_column=3) # Yes or No
sheet.merge_cells(start_row=5, start_column=4, end_row=5, end_column=5) # Yes or No
sheet.merge_cells(start_row=5, start_column=6, end_row=5, end_column=7) # Yes or No
sheet.merge_cells(start_row=5, start_column=8, end_row=5, end_column=9) # Yes or No

Thanks for any input.
Phil

[buran]

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

for row in (2, 3, 5):
    for scol, ecol in zip(range(2, 9, 2), range(3, 10, 2)):
        sheet.merge_cells(start_row=row, start_column=scol, end_row=row, end_column=ecol)

note that f-string is just a way to create the string. It should not interfere with openpyxl. Here is the example from post#7 with str.format method

for row in range(10, 13):
    for col1, col2 in zip('BDFH', 'CEGI'):
        print('{0}{2}:{1}{2}'.format(col1, col2, i))

if you have issue, show us what the problem is, pretty sure it's not the f-string. If openpyxl accept string as input to sheet.merge it does not matter how you construct the string or even if you just pass a variable.

[perfringo]

I was ninjad by buran but I almost finished my answer below so I decided to post it anyway.

Initial problem was with this format:

sheet.merge_cells('B10:C10')

Notice that value inside braces is explicitly string.

Now there is problem with this format:

sheet.merge_cells(start_row=2, start_column=2, end_row=2, end_column=3)

Notice that this is not string anymore.

Probably you should define ranges and loop them, something along these lines:

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)

[buran]

openpyxl except both ways to define the range to merge
, i.e. string or explicit start/erd row/col as integers

from the docs

>>> from openpyxl.workbook import Workbook
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> ws.merge_cells('A2:D2')
>>> ws.unmerge_cells('A2:D2')
>>>
>>> # or equivalently
>>> ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
>>> ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

so if you face problem, it's somewhere else, not the way merge cells are defined