learn_openpyxl

openpyxl - 2. Workbook and Worksheet Objects

2.1 Creating Workbooks

Use Workbook() class constructor to create a new Excel workbook:

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(2,3).value = "Hello Excel!"

workbook.save("my_new_workbook.xlsx")

File: 2.1_workbook-worksheet/2.1_Create-Workbools.py

By default, a new workbook is created with a single, empty worksheet.

You can add more worksheets as needed (using later commands).

Source code reference: Workbook() in openpyxl/workbook/workbook.py:

def __init__(self,
            write_only=False,
            iso_dates=False,
            ):
  self._sheets = []
  self._pivots = []
  self._active_sheet_index = 0
  self.defined_names = DefinedNameDict()
  self._external_links = []
  self.properties = DocumentProperties()
  self.custom_doc_props = CustomPropertyList()
  self.security = DocumentSecurity()
  self.__write_only = write_only
  self.shared_strings = IndexedList()

  self._setup_styles()

  self.loaded_theme = None
  self.vba_archive = None
  self.is_template = False
  self.code_name = None
  self.epoch = WINDOWS_EPOCH
  self.encoding = "utf-8"
  self.iso_dates = iso_dates

  if not self.write_only:
      self._sheets.append(Worksheet(self))

  self.rels = RelationshipList()
  self.calculation = CalcProperties()
  self.views = [BookView()]

2.2 Accessing Worksheets

Several methods provide access to worksheets within a workbook.

The most common method is accessing the active worksheet (the one currently selected in Excel), using workbook.active.

You can also access worksheets by name or index, see below example:

from openpyxl import load_workbook

workbook = load_workbook("my_workbook.xlsx")

# Accessing the active worksheet:
active_worksheet = workbook.active
print(active_worksheet.cell(row=4,column=4).value)

# Accessing a worksheet by name:
worksheet_by_name = workbook["Sheet3"]
print(worksheet_by_name.cell(row=3,column=3).value)

# Accessing a worksheet by index (index starts from 0):
worksheet_by_index = workbook.worksheets[1]
print(worksheet_by_index.cell(row=2,column=2).value)

# Check if a worksheet exists before accessing it (avoid errors):
if "Sheet2" in workbook.sheetnames:
    worksheet2 = workbook["Sheet2"]
    print(worksheet2.cell(2,2).value)

if "Sheet10" in workbook.sheetnames:
    worksheet10 = workbook["Sheet10"]
else:
    print("Sheet10 is not existed!")

Source code reference:

2.3 Creating Worksheets

Adding new worksheets is straightforward using the create_sheet() method. This method takes the sheet name as the first argument, and optional arguments for index (position within the workbook) and before (insert before another sheet):

from openpyxl import Workbook

workbook = Workbook()

# Create a new worksheet name "Data" at the end
new_worksheet = workbook.create_sheet(title="Data")

# Create a new worksheet named "Summary" before the sheet named "Data"
summary_worksheet = workbook.create_sheet(
    title = "Summary",
    index = 1
    # before = new_worksheet # TypeError: Workbook.create_sheet() got an unexpected keyword argument 'before'
)

sales_worksheet = workbook.create_sheet(
    title = "Sales",
    index = 2
    # before = new_worksheet # TypeError: Workbook.create_sheet() got an unexpected keyword argument 'before'
)

workbook.save("workbook_with_sheets.xlsx")

Source code reference:

2.4 Deleting Worksheets

To remove a worksheet, use the remove() method:

from openpyxl import load_workbook

workbook = load_workbook("my_workbook.xlsx")

# Deleting a worksheet by name
sheet_to_delete = workbook["Sheet3"]
workbook.remove(sheet_to_delete)

# Deleting a worksheet by index
workbook.remove(workbook.worksheets[1])

workbook.save("my_workbook_modified.xlsx")

Note: ensure to save the workbook after deleting a sheet.

Source code reference:

2.5 Renaming Worksheets

Change a worksheet’s name using the title attribute:

from openpyxl import load_workbook

workbook = load_workbook("my_workbook.xlsx")

worksheet = workbook["Sheet2"]
worksheet.title = "New Sheet Name"

workbook.save("my_workbook_renamed.xlsx")

Source code reference:

2.6 Working with Multiple Workbooks

openpyxl allows you to work with multiple workbooks simultaneously.

Just load or create multiple workbook objects:

from openpyxl import Workbook, load_workbook

workbook1 = load_workbook("my_workbook.xlsx")
workbook2 = Workbook()

print(workbook1.sheetnames)

# some processing
workbook1.remove(workbook1.worksheets[3])
new_sheet = workbook2.create_sheet("new sheet")

print(workbook1.sheetnames)
print(workbook1.properties)

workbook1.save("my_workbook_modified.xlsx")
workbook2.save("file2.xlsx")

2.7 Workbook Properties

Below table lists main properties in a workbook:

Property Description
active The currently active worksheet.
worksheets A list of all worksheets in the workbook.
sheetnames A list of the names of all worksheets.
properties Access to file properties using the Workbook.properties attribute, allowing setting things like author, last modified by, etc.

Source code reference:

Remember to always save your changes using workbook.save("filename.xlsx") to persist modifications to the Excel file.

Handle potential FileNotFoundError exceptions if the specified file doesn’t exist when loading a workbook.

Also, it’s good practice to close the workbook using workbook.close() after you’re finished working with it to release resources, though it’s automatically handled by Python’s garbage collection.

Last Updated at: 12/30/2025, 9:08:21 PM

This site is open source. Improve this page.