How to iterate over worksheets in workbook, openpyxl

How to iterate over worksheets in workbook, openpyxl

You can iterate over worksheets in a workbook using the openpyxl library in Python. Here's how you can do it:

import openpyxl # Load the Excel workbook workbook = openpyxl.load_workbook('your_workbook.xlsx') # Iterate over the worksheets in the workbook for sheet_name in workbook.sheetnames: # Access the worksheet by name worksheet = workbook[sheet_name] # You can now work with the worksheet, for example: # Accessing cell values: cell_value = worksheet['A1'].value print(f"Worksheet: {sheet_name}, Cell A1: {cell_value}") # Iterating over rows: for row in worksheet.iter_rows(min_row=1, max_row=worksheet.max_row, values_only=True): print(row) # Don't forget to close the workbook when you're done workbook.close()

In this code:

  1. We use openpyxl.load_workbook('your_workbook.xlsx') to load the Excel workbook. Replace 'your_workbook.xlsx' with the path to your Excel file.

  2. We then use a for loop to iterate over the worksheet names in the workbook using workbook.sheetnames. This gives us a list of worksheet names.

  3. Inside the loop, we access each worksheet by name using workbook[sheet_name].

  4. You can work with the worksheet, such as accessing cell values, iterating over rows, or performing any other operations specific to your needs.

  5. Finally, make sure to close the workbook using workbook.close() when you're done with it to release system resources.

This code allows you to iterate over all the worksheets in an Excel workbook and perform various operations on each sheet.

Examples

  1. Iterating over worksheets in a workbook using openpyxl:

    • Description: Open a workbook using openpyxl, then iterate over its worksheets to access each sheet individually.
    from openpyxl import load_workbook # Load the workbook wb = load_workbook('workbook.xlsx') # Iterate over worksheets in the workbook for sheet in wb.worksheets: print(sheet.title)

  2. Accessing worksheet properties while iterating:

    • Description: Iterate over worksheets in a workbook and access properties such as title, dimensions, or sheet format.
    from openpyxl import load_workbook # Load the workbook wb = load_workbook('workbook.xlsx') # Iterate over worksheets and access properties for sheet in wb.worksheets: print(f"Sheet Title: {sheet.title}") print(f"Sheet Dimensions: {sheet.dimensions}") print(f"Sheet Format: {sheet.sheet_format}")

  3. Iterating over worksheets and extracting data:

    • Description: Iterate over worksheets in a workbook and extract data from each sheet for further processing.
    from openpyxl import load_workbook # Load the workbook wb = load_workbook('workbook.xlsx') # Iterate over worksheets and extract data for sheet in wb.worksheets: for row in sheet.iter_rows(values_only=True): print(row)

  4. Iterating over worksheets and accessing specific cells:

    • Description: Iterate over worksheets in a workbook and access specific cells or ranges within each sheet.
    from openpyxl import load_workbook # Load the workbook wb = load_workbook('workbook.xlsx') # Iterate over worksheets and access specific cells for sheet in wb.worksheets: print(sheet['A1'].value) print(sheet['B2:C5'].value)

  5. Iterating over worksheets and modifying data:

    • Description: Iterate over worksheets in a workbook and modify data within each sheet, such as updating cell values or formatting.
    from openpyxl import load_workbook # Load the workbook wb = load_workbook('workbook.xlsx') # Iterate over worksheets and modify data for sheet in wb.worksheets: sheet['A1'] = 'New Value' sheet['B2'].font = Font(bold=True)

  6. Iterating over worksheets and copying data between sheets:

    • Description: Iterate over worksheets in a workbook and copy data between sheets, either within the same workbook or across different workbooks.
    from openpyxl import load_workbook, Workbook from openpyxl.utils import range_boundaries # Load source workbook wb_source = load_workbook('source.xlsx') # Create target workbook wb_target = Workbook() # Iterate over worksheets and copy data for sheet_source in wb_source.worksheets: sheet_target = wb_target.create_sheet(title=sheet_source.title) for row in sheet_source.iter_rows(values_only=True): sheet_target.append(row)

  7. Iterating over worksheets and performing analysis:

    • Description: Iterate over worksheets in a workbook and perform analysis or calculations on data within each sheet.
    from openpyxl import load_workbook # Load the workbook wb = load_workbook('workbook.xlsx') # Iterate over worksheets and perform analysis for sheet in wb.worksheets: total_sum = sum(cell.value for row in sheet.iter_rows() for cell in row if isinstance(cell.value, (int, float))) print(f"Total sum in {sheet.title}: {total_sum}")

  8. Iterating over worksheets and checking for specific conditions:

    • Description: Iterate over worksheets in a workbook and check for specific conditions or patterns within each sheet.
    from openpyxl import load_workbook # Load the workbook wb = load_workbook('workbook.xlsx') # Iterate over worksheets and check for specific conditions for sheet in wb.worksheets: if 'Total' in sheet.title: print(f"Found sheet with 'Total' in title: {sheet.title}")

  9. Iterating over worksheets and saving modified workbook:

    • Description: Iterate over worksheets in a workbook, make modifications, and then save the modified workbook.
    from openpyxl import load_workbook # Load the workbook wb = load_workbook('workbook.xlsx') # Iterate over worksheets and make modifications for sheet in wb.worksheets: sheet['A1'] = 'Modified Value' # Save the modified workbook wb.save('modified_workbook.xlsx')

  10. Iterating over worksheets and deleting specific sheets:

    • Description: Iterate over worksheets in a workbook and delete specific sheets based on certain criteria.
    from openpyxl import load_workbook # Load the workbook wb = load_workbook('workbook.xlsx') # Iterate over worksheets and delete specific sheets for sheet in wb.worksheets: if 'Template' in sheet.title: del wb[sheet.title] # Save the modified workbook wb.save('trimmed_workbook.xlsx')

More Tags

rx-android tracking onmouseover tfs array-formulas click windows-firewall youtube-iframe-api swifty-json overflow-menu

More Python Questions

More Livestock Calculators

More Mortgage and Real Estate Calculators

More Bio laboratory Calculators

More Genetics Calculators