How should I assign each worksheet to its own variable?

from openpyxl import *

#wb1 is the unallocated pupils
wb1 = load_workbook('unallocated.xlsx')

#wb2 will be the final allocations
wb2 = Workbook()
wb2.save("allocations.xlsx")
wb2 = load_workbook('allocations.xlsx')

After that I need to find a way that allows me to map each worksheet in wb1 to the name of the worksheet. So the end product should be something like ws1 = [name of sheet 1], ws2 = [name of sheet 2], and so on.

for sheet in wb1:
    sheet.title = wb1[sheet.title]

This does not work – it doesn't put a str around it and moreover it give the type error:

Traceback (most recent call last):
  File "C:/Users/Family guest/Desktop/CEP final project/CEP final proj.py", line 13, 
    in <module>
    sheet.title = wb1[sheet.title]
  File "C:\Users\Family guest\AppData\Local\Programs\Python\Python37\lib
      \site-packages\openpyxl\workbook\child.py", line 93, in title
    m = INVALID_TITLE_REGEX.search(value)
TypeError: expected string or bytes-like object

How should I do it?

You probably wanted this:

for num, sheet in enumerate(wb1, 1):
    vars()["ws{}".format(num)] = sheet.title

To construct the names ws1, ws2, ..., we use the .format() method to append the string representation of the num number to the "ws" string.

To (indirectly) create variables with those names, we add them as keys to the vars() dictionary.

(The built-in function vars() will give you the updatable dictionary of all used names, so you may add new items to them.

The built-in function enumerate() will give you something as list of pairs (1, worksheet1), (2, worksheet2), and so on.)

How should I assign each worksheet to its own variable?

Related

Recent Posts