How to read a specific Worksheet with Openpyxl, instead of active sheet?

Question

I have an existing excel file, with Data on the first and second sheet, I should read both with Python.

Openpyxl reads data from the first/active sheet.

from openpyxl import Workbook, load_workbook

wb = load_workbook('Test.xlsx')
ws = wb.active

It works fine, until I am always on the first sheet.

I tried this:

wb.active = 1  # or
wb.active = 2

Didn't work.

I would like to read sheets not depending on activation, but based on the name of them. (Or Sheet position) I need something like this:

wb = load_workbook('Test.xlsx')
ws1 = wb.Sheet1
ws2 = wb.Sheet2

So, how can I read data from different sheets, without clicking on them?

Or, how can I activate the actually needed sheet?

Does this answer your question? [How to use openpyxl to modify data in specific sheet in python](https://stackoverflow.com/questions/50952548/how-to-use-openpyxl-to-modify-data-in-specific-sheet-in-python) — Tomerikoo, Jun 19 '23 at 13:04
https://stackoverflow.com/questions/36814050/get-sheet-by-name-using-openpyxl — Tomerikoo, Jun 19 '23 at 13:05

Tami · Answer 1 · 2022-01-11T01:12:43.603

2

I found it!

ws2 = wb["Sheet2"]

And then I just call ws2 and it works well. :) For Example:

ws2['A1'].value

edited Jan 11 '22 at 01:12

answered Jan 09 '22 at 15:57

Tami

29
1
3

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 09 '22 at 21:02

Jonathan L · Answer 2 · 2023-08-23T21:52:22.627

You can loop through wb.worksheets or wb.sheetnames, look up worksheet by index (1-based) or by sheet name. Here is sample script:

from openpyxl import Workbook, load_workbook

input_file = 'inputs/my_sample.xlsx'  # update as needed
wb: Workbook = load_workbook(filename=input_file)

def get_worksheet_by_index(wb: Workbook, sheet_idx: int, set_as_active: bool = False):
    # sheet_idx is 1-based
    ws = None
    for idx, worksheet in enumerate(wb.worksheets):
        if idx + 1 == sheet_idx:
            ws = worksheet
            if set_as_active:
                wb.active = ws  # set current worksheet as the active worksheet
            break
    return ws


def get_worksheet_by_name(wb: Workbook, sheet_name: str, set_as_active: bool = False):
    ws = None
    for sheetname in wb.sheetnames:
        if sheetname == sheet_name:
            ws = wb[sheetname]
            if set_as_active:
                wb.active = ws
            break
    return ws


ws = get_worksheet_by_index(wb, 2)
print(ws.title)

ws = get_worksheet_by_name(wb, 'my_sheet_name')  # update as needed
print(ws.title)

How to read a specific Worksheet with Openpyxl, instead of active sheet?

2 Answers2