Python/Openpyxl: change cell A1 in all excel worksheets within a workbook to equal the name of the worksheet tab

jubilee 0
Viewed 37

I am new to python and I bet I am coming to you guys with an extremely easy question. In the below code, I am trying to do the following steps using python and openpyxl. The first two steps work fine, but the code fails once I add in step 3. Could someone please tell me why my for loop isn't working? I know it looks very wrong now, but I have tried so many possible combinations that I am positive I am getting further from what's correct.

This code is attempting to:

  1. Create a new excel workbook

  2. Create new worksheets within the workbook based on a list of sheet names.

  3. Set cell A1 of each worksheet equal to the worksheet name. IE: if the worksheet tab is called "Total Pivot", then I want cell A1 in that sheet to contain the text "Total Pivot". The code worked well until I added this portion. It is currently giving me an error that says-- 'WriteOnlyWorksheet' object is not subscriptable.

Thanks so much for any help. I will be very grateful for any advice. :)

from openpyxl import Workbook

#Create new workbook
Report1_workbook = Workbook('Report 1.xlsx')

#List of worksheets to create in Report 1
report1sheets = ["Total Summary", "Interest Summary", "Price Summary", "Total Pivot", "Total Pivot Expanded"]

#Creating worksheets with tab names in the above list.
namereport1sheets = [Report1_workbook.create_sheet(title=x) for x in report1sheets]

#Setting cell A1 in every sheet to be equal to the sheet title-- THIS IS WHAT IS CAUSING AN ERROR

for sheet in Report1_workbook.sheetnames:
    for i in range(len(report1sheets)):
        sheet["A1"].value = report1sheets[i]

Report1_workbook.save('Report 1.xlsx')
openpyxl python-3.x excel python
1 Answers

The reason for your error is because you of the line Report1_workbook = Workbook('Report 1.xlsx'). This will open a file in Write-only mode and cell.value will not work. Also, if you use .worksheet and .title instead of the multiple loops, it is cleaner. The shortest/quickest way to fix it is to use append(). Updated code below...

from openpyxl import Workbook
#Create new workbook
Report1_workbook = Workbook('Report 1.xlsx')

#List of worksheets to create in Report 1
report1sheets = ["Total Summary", "Interest Summary", "Price Summary", "Total Pivot", "Total Pivot Expanded"]

#Creating worksheets with tab names in the above list.
namereport1sheets = [Report1_workbook.create_sheet(title=x) for x in report1sheets]

for sheet in Report1_workbook.worksheets: # For each worksheet.. 
    sheet.append([sheet.title])  ## Use append and use sheet.title=name of sheet

Report1_workbook.save('Report 1.xlsx')

For sake of completeness, if you want to use cell, you can use the below code. You will need to use Report1_workbook = Workbook() at the start and you can use cell.value(). But, note that this code will create a default worksheet Sheet which will be in addition to the sheets you create. However, you can specific which cell you want to write you data to, unlike using append() which will start writing from cell A1

from openpyxl import Workbook

#Create new workbook
Report1_workbook = Workbook()

#List of worksheets to create in Report 1
report1sheets = ["Total Summary", "Interest Summary", "Price Summary", "Total Pivot", "Total Pivot Expanded"]

#Creating worksheets with tab names in the above list.
namereport1sheets = [Report1_workbook.create_sheet(title=x) for x in report1sheets]

for sheet in Report1_workbook.worksheets:
    sheet.cell(row=1,column=1).value = sheet.title  ##Using sheet.cell

Report1_workbook.save('Report 1.xlsx')
Redox0
Related