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

0 votes

I'm new to Python, therefore I'm sure my query to you guys is quite simple. I'm attempting to use Python and OpenPyXL to complete the following tasks in the code below. The third step causes the code to fail after the first two phases, which is acceptable. Please let me know why my for loop isn't working. Even if it appears to be quite wrong right now, I am certain that I am moving further away from the right course after trying so many different combinations.

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')
Nov 17, 2022 in Others by Kithuzzz
• 38,000 points
1,511 views

1 answer to this question.

0 votes

You made a mistake when you used the statement Report1 workbook = Workbook('Report 1.xlsx'). Cell. value will not function because the file will be opened in Write-only mode. Additionally, it is cleaner to use the worksheet and title rather than the several loops. The quickest/shortest fix is to use add (). Below is the updated code.

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')
I hope this helps you.

answered Nov 17, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Error Trying to Display The Names of All Excel Data Models in a Workbook

It is necessary to list ThisWorkbook.model. Not ...READ MORE

answered Apr 10, 2023 in Others by Kithuzzz
• 38,000 points
480 views
0 votes
1 answer

VBA Export as PDF and Save to Location with name as per a Cell in the worksheet

Following is the code that gets generated ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,600 points
1,934 views
0 votes
1 answer

Sum the total of a column in excel and paste the sum to a different workbook

Get Column Sum From Closed Workbook Option Explicit Sub ...READ MORE

answered Jan 26, 2023 in Others by narikkadan
• 63,600 points
592 views
0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,600 points
929 views
0 votes
1 answer

How search data in excel with openpyxl?

The method iter_rows in the library has ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,600 points
7,159 views
0 votes
1 answer

Converting all tabs of excel sheet to PDF

Using VBA, try it like this, for ...READ MORE

answered Sep 26, 2022 in Others by narikkadan
• 63,600 points
1,751 views
0 votes
1 answer

How to insert info into online excel spreadsheet with python selenium

I recently discovered one of my options, ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
1,308 views
0 votes
1 answer

Python Excel Writer (xlswriter) Insert Image from URL

Use this: url = "..../picture.jpg" data = urllib.request.urlopen(url).read() file = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,600 points
1,918 views
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,600 points
2,354 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,600 points
679 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP