+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: openpyxl rename sheet acting strange (/thread-38289.html)
openpyxl rename sheet acting strange - Pedroski55 - Sep-25-2022
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:
Quote:KeyError: 'Worksheet <Worksheet "vjCoQ Ltd."> does not exist.'
Can anyone see the problem? This is using Idle:
Quote:>>> 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 ... ')
RE: openpyxl rename sheet acting strange - buran - Sep-26-2022
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 tsheettsheet.cell(row=1, column=colNum).value=sv