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]

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

So the whole function looks like this and works:

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