How To Store and Manage Worksheets Using OpenPyXL?

Method

Newly created worksheet objects are automatically added to the `worksheets` collection. You can extract objects from this collection using indexing or iteration, and also remove objects from it.

To access the `worksheets` collection of a workbook object:

>>> sheets=wb.worksheets

>>> sheets[0].title

‘Sheet’

>>> sheets[1].title

‘MySheet’

 

Accessing the active worksheet:

>>> ws=wb.active

If you don’t know the worksheet name but know the index, use the `sheetnames` property to get all sheet names and reference the sheet by index:

>>> names = wb.sheetnames

>>> ws4 = wb[names[0]]

 

To remove a worksheet from the collection:

>>> wb.remove(ws)

Check how many objects are in the collection:

>>> sheets=wb.worksheets

>>> len(sheets)

Sample Code

#Managing and Storing Worksheets

#Import load_workbook function
from openpyxl import load_workbook

#Load the workbook
wb=load_workbook(filename='wb2.xlsx')
#Create a new worksheet
ws1=wb.create_sheet()
#Get the worksheet collection object
sheets=wb.worksheets
#Output the title of the first worksheet
print(sheets[0].title)
#Output the title of the second worksheet
print(sheets[1].title)

#Output the worksheet collection
print(sheets)
#Output the number of worksheets in the collection
print(len(sheets))

#Iterate through the workbook and
#output the titles of each worksheet
for sheet in wb:
    print(sheet.title)

#Delete the newly created worksheets
wb.remove(ws1)
#Re-fetch the number of worksheets
#in the collection and output it
sheets=wb.worksheets
print(len(sheets))

wb.save('test.xlsx')
wb.close()