openpyxl rename sheet acting strange

[Pedroski55]

I have a big sales Excel file. I want to extract data according to a criteria and save it to another Excel

When I make the new Excel, it has 1 sheet called Sheet. So I want to rename that.

I get a tuple with the name of the column I want to sort by, in this case column company_name.

I get the name of the company I want to find in any row, in this case vjCoQ Ltd.

I get the column number for this column, in this case 3.

So mytup = ('company_name', 'vjCoQ Ltd.', 3)

When I do this, I get an error and I don't know why. The tsheet 'vjCoQ Ltd.' is clearly there but I get:

KeyError: 'Worksheet <Worksheet "vjCoQ Ltd."> does not exist.'

Can anyone see the problem? This is using Idle:

>>> targetFile = openpyxl.Workbook()
>>> tsheet = targetFile.active
>>> tsheet
<Worksheet "Sheet">
>>> tsheet.title = mytup[1]
>>> tsheets = targetFile.sheetnames
>>> tsheets
['vjCoQ Ltd.']
>>> maxCol = sourceFile[sheet].max_column
>>> tsheet
<Worksheet "vjCoQ Ltd.">
>>> for colNum in range(1, maxCol + 1):
sv = sourceFile[sheet].cell(row=1, column=colNum).value
targetFile[tsheet].cell(row=1, column=colNum).value=sv


Traceback (most recent call last):
File "<pyshell#24>", line 3, in <module>
targetFile[tsheet].cell(row=1, column=colNum).value=sv
File "/home/pedro/.local/lib/python3.6/site-packages/openpyxl/workbook/workbook.py", line 273, in __getitem__
raise KeyError("Worksheet {0} does not exist.".format(key))
KeyError: 'Worksheet <Worksheet "vjCoQ Ltd."> does not exist.'
>>>

The only way I found to get around that is get the sheet names again and set tsheet = tsheets[0]

1 2 3

tsheets = targetFile.sheetnames # this works tsheet = tsheets[0]

So the whole function looks like this and works:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

def makeXL(mytup):         print('Now making the taget file ... ')         # open a target file to save the data we want         # make a new XL         targetFile = openpyxl.Workbook()         # a new wb only has 1 sheet called Sheet         tsheet = targetFile.active         # this doesn't work         tsheet.title = mytup[1]         tsheets = targetFile.sheetnames         # this works         tsheet = tsheets[0]                maxCol = sourceFile[sheet].max_column         # first write the headers to target file         for colNum in range(1, maxCol + 1):                    sv = sourceFile[sheet].cell(row=1, column=colNum).value             targetFile[tsheet].cell(row=1, column=colNum).value=sv         savename = path2XL + mytup[1] + '_customers.xlsx'         targetFile.save(savename)         print('target file made and headers inserted ... ')

[buran]

tsheet is Worksheet instance. I believe it expects string as key, not sheet object.

Also I think, instead of targetFile[tsheet] you can use directly tsheet

tsheet.cell(row=1, column=colNum).value=sv