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 in Others by Kithuzzz
• 20,660 points
68 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 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
529 views
0 votes
1 answer
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18 in Others by narikkadan
• 37,660 points
33 views
0 votes
1 answer

How do I protect all worksheet in an Excel workbook with a single click?

VBA Code : Dim ws as Worksheet Dim pwd ...READ MORE

answered Oct 22 in Others by narikkadan
• 37,660 points
33 views
0 votes
1 answer

How search data in excel with openpyxl?

The method iter_rows in the library has changed, refer ...READ MORE

answered Oct 2 in Others by narikkadan
• 37,660 points
224 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 in Others by narikkadan
• 37,660 points
205 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 in Others by narikkadan
• 37,660 points
82 views
0 votes
1 answer

Python Excel Writer (xlswriter) Insert Image from URL

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

answered Nov 17 in Others by narikkadan
• 37,660 points
64 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 in Others by narikkadan
• 37,660 points
23 views
0 votes
1 answer

Change the size of one cell in excel 2016

I'm sorry, but it's not feasible. A ...READ MORE

answered Oct 8 in Others by narikkadan
• 37,660 points
48 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