How do I type annotate a worksheet, or more specifically, a read-only worksheet?

Question

I have types-openpyxl installed.

I load a workbook in read-only mode, and then access a worksheet from it:

from pathlib import Path
import openpyxl as xl
from openpyxl.worksheet._read_only import ReadOnlyWorksheet

excel_file = Path("./xl.xlsx")
wb: xl.Workbook = xl.load_workbook(
    excel_file, read_only=True, data_only=True, keep_links=False
)
ws: ReadOnlyWorksheet = wb["hello world"]
x = ws[1]

This results in the mypy error:

Invalid self argument "ReadOnlyWorksheet" to attribute function "__getitem__" with type "Callable[[Worksheet, int], tuple[Cell, ...]]"

How do I type annotate ws?

Answer 1

At runtime, Workbook.__getitem__() returns either Worksheet, ReadOnlyWorksheet, WriteOnlyWorksheet or Chartsheet.

The most precise type for ws is thus:

from openpyxl.chartsheet.chartsheet import Chartsheet
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.worksheet._read_only import ReadOnlyWorksheet
from openpyxl.worksheet._write_only import WriteOnlyWorksheet

ws: Worksheet | ReadOnlyWorksheet | WriteOnlyWorksheet | Chartsheet

This is also reflected in the stubs using the following type-checking-time-only symbols:

class _WorksheetLike(Worksheet, WriteOnlyWorksheet, ReadOnlyWorksheet): ...
class _WorksheetOrChartsheetLike(Chartsheet, _WorksheetLike): ...

class Workbook:
    ...
    def __getitem__(self, key: str) -> _WorksheetOrChartsheetLike: ...

As _WorksheetOrChartsheetLike is a subtype of all four aforementioned types, wb["hello world"]'s result should be assignable to a variable whose type is one or a combination of them.

In your case, since you have read_only=True, you want just ReadOnlyWorksheet:

from openpyxl.worksheet._read_only import ReadOnlyWorksheet

ws: ReadOnlyWorksheet = wb["hello world"]

Mypy raises an error for ws[0] due to the following type-checking-time-only assignment:

class ReadOnlyWorksheet:
    ...
    __getitem__ = Worksheet.__getitem__

This is a bug in typeshed (which I have reported and fixed). A workaround is to import _WorksheetOrChartsheetLike under if TYPE_CHECKING:

if TYPE_CHECKING:
    from openpyxl.workbook.workbook import _WorksheetOrChartsheetLike

# Must be quoted, or __future__.annotations must present
ws: '_WorksheetOrChartsheetLike' = wb["hello world"]