Openpyxl - Setting Active Sheet (page.append not working as expected)

Question

I've tried implementing multiple solutions from existing posts, like this one, to no avail: openpyxl Set Active Sheet

I have a workbook with n number of sheets that I want to iteratively step through, and apply header information to. As far as I can tell, using wb.active = i is setting the active worksheet, but when I follow up with page.append(header), I end up with the header appended n times, ONLY to the index 0 sheet. This is essentially the same q as the link above, but the solution doesn't seem to work.

What am I missing here? I wonder if I need to specify an index for page.append(), but that doesn't seem to be a valid argument for that func.

CODE

header = ['Time [sec]', 'Altitude [km]', 'Velocity [km/s]']
for i in range(len(wb.sheetnames)):
    wb.active = i
    print(wb.active)
    page.append(header)
    wb.save(path)

CONSOLE (verifies that the wb.active function is working, but the sheets specified aren't being appended)

<Worksheet "ORB1">
<Worksheet "ORB2">
<Worksheet "ORB3">
<Worksheet "ORB4">
<Worksheet "ORB5">

Here is another version which produces the same result (5x headers applied only to the first sheet).

header = ['Time [sec]', 'Altitude [km]', 'Velocity [km/s]']
for i, s in enumerate(wb.sheetnames):
    page.append(header)
    wb.save(path)

The code is incomplete – what is `page`? Whatever it is, it won't be affected by the loop. Also, `ws = wb.active` is mere convenience to return the worksheet that is visibly active in Excel; you should **not** use it in the way you do here. Much better to loop over the workbook or sheetnames directly. — Charlie Clark, Jan 25 '22 at 14:17

score -1 · Answer 1 · answered Jan 24 '22 at 18:52

-1

This one is SOLVED but I want to keep the q up because the solution is... weird. Earlier in the code I was assigning page = wb.active, and then later using page.append(header).

The issue with that ^, has to do with the format for setting the active sheet. wb.active is used such that wb.active = sheet_index, rather than the typical function structure where wb.active(sheet_index).

Because of this bizarre arg format, simplifying "wb.active" to "page" breaks this function.

TLDR: This does not work...

page = wb.active
page.append(header)

You must use...

wb.active.append(header)

No idea why that function has such a strange structure, but I suspect I'm not the only person to have had this issue.

answered Jan 24 '22 at 18:52

Austin Prater

1

The statement is entirely incorrect. If you assign `page = wb.active` the `page.append(…)` is **exactly** the same as `wb.active.append(…)` – Charlie Clark Jan 25 '22 at 14:19
In that case, I'm stumped. With page.append() it only populates the first index, and with wb.active.append() it populates the actually active sheet. Everything else is identical – Austin Prater Jan 26 '22 at 17:13
I assume you're only doing the assignment once and this is outside the loop. You should include all relevant code. But, as I said above. this approach is the wrong way to loop over worrksheets. – Charlie Clark Jan 26 '22 at 19:09

Openpyxl - Setting Active Sheet (page.append not working as expected)

1 Answers1