Skip to content

spreadsheets¤

Classes for reading cells in spreadsheets.

Spreadsheet ¤

Spreadsheet(file: str)

Bases: ABC

Abstract base class for spreadsheets.

Parameters:

Name Type Description Default
file str

The location of the spreadsheet on a local hard drive or on a network drive.

 required
Source code in src/msl/io/readers/spreadsheet.py 
22
23
24
25
26
27
28
def __init__(self, file: str) -> None:
    """Abstract base class for spreadsheets.

    Args:
        file: The location of the spreadsheet on a local hard drive or on a network drive.
    """
    self._file: str = file

file property ¤

file: str

str — The location of the spreadsheet on a local hard drive or on a network drive.

read abstractmethod ¤

read(
    cells: str | None = None,
    sheet: str | None = None,
    *,
    as_datetime: bool = True,
    invalid_date: str | date | datetime | None = None,
    merged: bool = False,
    skip_rows: Iterable[int] | None = None,
) -> Any | list[tuple[Any, ...]]

Read values from the spreadsheet.

You must override this method.

Parameters:

Name Type Description Default
cells str | None

The cell(s) to read. For example, C9 will return a single value and C9:G20 will return all values in the specified range. If not specified then returns all values in the specified sheet.

 None
sheet str | None

The name of the sheet to read the value(s) from. If there is only one sheet in the spreadsheet then you do not need to specify the name of the sheet.

 None
as_datetime bool

Whether dates should be returned as datetime or date objects. If False, dates are returned as a string in the format of the spreadsheet cell.

 True
invalid_date str | date | datetime | None

If None, an error is raised if a cell contains a value that is an invalid date. If not None, all cells that contain an invalid date are replaced with the specified value.

 None
merged bool

Applies to cells that are merged with other cells. The details depend on the type of spreadsheet document that is being read. Some documents allow the hidden cells that are part of a merger to retain its unmerged value. Other documents associate the merged value only with the top-left cell and all other cells in the merger are empty.

 False
skip_rows Iterable[int] | None

Row numbers to skip. The row numbers are as shown when the spreadsheet is viewed in the spreadsheet application and are not based on the row number in the data that would be returned. For example, if cells="A3:D10" and you want to skip the data in rows A5:D5 and A7:D7 then skip_rows=[5, 7] achieves the desired result.

 None

Returns:

Type Description
Any | list[tuple[Any, ...]]

The value(s) of the requested cell(s).
Source code in src/msl/io/readers/spreadsheet.py 
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
@abstractmethod
def read(  # noqa: PLR0913
    self,
    cells: str | None = None,
    sheet: str | None = None,
    *,
    as_datetime: bool = True,
    invalid_date: str | date | datetime | None = None,
    merged: bool = False,
    skip_rows: Iterable[int] | None = None,
) -> Any | list[tuple[Any, ...]]:
    """Read values from the spreadsheet.

    !!! warning "You must override this method."

    Args:
        cells: The cell(s) to read. For example, `C9` will return a single value
            and `C9:G20` will return all values in the specified range. If not
            specified then returns all values in the specified `sheet`.
        sheet: The name of the sheet to read the value(s) from. If there is only one
            sheet in the spreadsheet then you do not need to specify the name of the sheet.
        as_datetime: Whether dates should be returned as [datetime][datetime.datetime] or
            [date][datetime.date] objects. If `False`, dates are returned as a string in
            the format of the spreadsheet cell.
        invalid_date: If `None`, an error is raised if a cell contains a value that
            is an invalid date. If not `None`, all cells that contain an invalid date are
            replaced with the specified value.
        merged: Applies to cells that are merged with other cells. The details depend
            on the type of spreadsheet document that is being read. Some documents allow
            the hidden cells that are part of a merger to retain its unmerged value. Other
            documents associate the merged value only with the top-left cell and all
            other cells in the merger are empty.
        skip_rows: Row numbers to skip. The row numbers are as shown when the spreadsheet is
            viewed in the spreadsheet application and are not based on the row number in the
            data that would be returned. For example, if `cells="A3:D10"` and you want to skip
            the data in rows `A5:D5` and `A7:D7` then `skip_rows=[5, 7]` achieves the desired
            result.

    Returns:
        The value(s) of the requested cell(s).
    """

sheet_names abstractmethod ¤

sheet_names() -> tuple[str, ...]

Get the names of all sheets in the spreadsheet.

You must override this method.

Returns:

Type Description
tuple[str, ...]

The names of all sheets.
Source code in src/msl/io/readers/spreadsheet.py 
77
78
79
80
81
82
83
84
85
@abstractmethod
def sheet_names(self) -> tuple[str, ...]:
    """Get the names of all sheets in the spreadsheet.

    !!! warning "You must override this method."

    Returns:
        The names of all sheets.
    """

to_indices staticmethod ¤

to_indices(cell: str) -> tuple[int | None, int]

Convert a string representation of a cell to row and column indices.

Parameters:

Name Type Description Default
cell str

The cell. Can be letters only (a column) or letters and a number (a column and a row).

 required

Returns:

Type Description
tuple[int | None, int]

The (row_index, column_index). If cell does not contain a row number then the row index is None. The row and column index are zero based.

Examples:

>>> to_indices("A")
(None, 0)
>>> to_indices("A1")
(0, 0)
>>> to_indices("AA10")
(9, 26)
>>> to_indices("AAA111")
(110, 702)
>>> to_indices("MSL123456")
(123455, 9293)
>>> to_indices("BIPM")
(None, 41664)
Source code in src/msl/io/readers/spreadsheet.py 
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
@staticmethod
def to_indices(cell: str) -> tuple[int | None, int]:
    """Convert a string representation of a cell to row and column indices.

    Args:
        cell: The cell. Can be letters only (a column) or letters and a number
            (a column and a row).

    Returns:
        The *(row_index, column_index)*. If `cell` does not contain a row number
            then the row index is `None`. The row and column index are zero based.

    **Examples:**
    <!-- invisible-code-block: pycon
    >>> from msl.io.readers.spreadsheet import Spreadsheet
    >>> to_indices = Spreadsheet.to_indices

    -->

    ```pycon
    >>> to_indices("A")
    (None, 0)
    >>> to_indices("A1")
    (0, 0)
    >>> to_indices("AA10")
    (9, 26)
    >>> to_indices("AAA111")
    (110, 702)
    >>> to_indices("MSL123456")
    (123455, 9293)
    >>> to_indices("BIPM")
    (None, 41664)

    ```
    """
    match = _cell_regex.match(cell)
    if not match:
        msg = f"Invalid cell {cell!r}"
        raise ValueError(msg)

    letters, numbers = match.groups()
    row = max(0, int(numbers) - 1) if numbers else None
    uppercase = string.ascii_uppercase
    col = sum((26**i) * (1 + uppercase.index(c)) for i, c in enumerate(letters[::-1]))
    return row, col - 1

to_letters staticmethod ¤

to_letters(index: int) -> str

Convert a column index to column letters.

Parameters:

Name Type Description Default
index int

The column index (zero based).

 required

Returns:

Type Description
str

The corresponding spreadsheet column letter(s).

Examples:

>>> to_letters(0)
'A'
>>> to_letters(1)
'B'
>>> to_letters(26)
'AA'
>>> to_letters(702)
'AAA'
>>> to_letters(494264)
'ABCDE'
Source code in src/msl/io/readers/spreadsheet.py 
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
@staticmethod
def to_letters(index: int) -> str:
    """Convert a column index to column letters.

    Args:
        index: The column index (zero based).

    Returns:
        The corresponding spreadsheet column letter(s).

    **Examples:**
    <!-- invisible-code-block: pycon
    >>> from msl.io.readers.spreadsheet import Spreadsheet
    >>> to_letters = Spreadsheet.to_letters

    -->

    ```pycon
    >>> to_letters(0)
    'A'
    >>> to_letters(1)
    'B'
    >>> to_letters(26)
    'AA'
    >>> to_letters(702)
    'AAA'
    >>> to_letters(494264)
    'ABCDE'

    ```
    """
    letters: list[str] = []
    uppercase = string.ascii_uppercase
    while index >= 0:
        div, mod = divmod(index, 26)
        letters.append(uppercase[mod])
        index = div - 1
    return "".join(letters[::-1])

to_ranges ¤

to_ranges(cells: str) -> tuple[bool, int, int | None, list[int]]

Convert cells to (is-range, r1, r2, columns).

Source code in src/msl/io/readers/spreadsheet.py 
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
def to_ranges(cells: str) -> tuple[bool, int, int | None, list[int]]:
    """Convert `cells` to `(is-range, r1, r2, columns)`."""
    if _single_cell_regex.match(cells):  # special case
        r1, c1 = Spreadsheet.to_indices(cells)
        if r1 is None:
            r1 = 0
        return False, r1, r1 + 1, [c1]

    r1, r2 = None, None
    cols: list[int] = []
    for c in cells.split(","):
        first, *second = c.split(":")
        ra, c1 = Spreadsheet.to_indices(first)
        if ra is not None:
            r1 = ra if r1 is None else min(r1, ra)
        rb, c2 = Spreadsheet.to_indices(second[0]) if second else (None, c1)
        if rb is not None:
            rb += 1
            r2 = rb if r2 is None else max(r2, rb)
        cols.extend(range(c1, c2 + 1))

    if r1 is None:
        r1 = 0

    return True, r1, r2, cols

Read an Excel spreadsheet (.xls and .xlsx files).

ExcelReader ¤

ExcelReader(file: PathLike, **kwargs: Any)

Bases: Spreadsheet

Read an Excel spreadsheet (.xls and .xlsx files).

This class simply provides a convenience for reading cell values (not drawings or charts) from Excel spreadsheets. It is not registered as a Reader because the information in a spreadsheet is unstructured and therefore one cannot generalize how to parse a spreadsheet to create a Root.

Parameters:

Name Type Description Default
file PathLike

The path to an Excel spreadsheet file.

 required
kwargs Any

All keyword arguments are passed to xlrd.open_workbook. You can use an encoding keyword argument as an alias for encoding_override. The default on_demand value is True.

 {}

Examples: 

from msl.io import ExcelReader
excel = ExcelReader("lab_environment.xlsx")

Source code in src/msl/io/readers/excel.py 
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
def __init__(self, file: PathLike, **kwargs: Any) -> None:
    """Read an Excel spreadsheet (*.xls* and *.xlsx* files).

    This class simply provides a convenience for reading cell values (not
    drawings or charts) from Excel spreadsheets. It is not registered as
    a [Reader][msl.io.base.Reader] because the information in a spreadsheet
    is unstructured and therefore one cannot generalize how to parse a
    spreadsheet to create a [Root][msl.io.base.Root].

    Args:
        file: The path to an Excel spreadsheet file.
        kwargs: All keyword arguments are passed to [xlrd.open_workbook][]{:target="_blank"}.
            You can use an `encoding` keyword argument as an alias for `encoding_override`.
            The default `on_demand` value is `True`.

    **Examples:**
    ```python
    from msl.io import ExcelReader
    excel = ExcelReader("lab_environment.xlsx")
    ```
    """
    f = os.fsdecode(file)
    super().__init__(f)

    # change the default on_demand value
    if "on_demand" not in kwargs:
        kwargs["on_demand"] = True

    # 'encoding' is an alias for 'encoding_override'
    encoding = kwargs.pop("encoding", None)
    if encoding is not None:
        kwargs["encoding_override"] = encoding

    self._workbook: Book = open_workbook(f, **kwargs)

close ¤

close() -> None

Close the workbook.

Source code in src/msl/io/readers/excel.py 
89
90
91
def close(self) -> None:
    """Close the workbook."""
    self._workbook.release_resources()

dimensions ¤

dimensions(sheet: str) -> tuple[int, int]

Get the number of rows and columns in a sheet.

Parameters:

Name Type Description Default
sheet str

The name of a sheet to get the dimensions of.

 required

Returns:

Type Description
tuple[int, int]

The (number of rows, number of columns) in sheet.
Source code in src/msl/io/readers/excel.py 
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
def dimensions(self, sheet: str) -> tuple[int, int]:
    """Get the number of rows and columns in a sheet.

    Args:
        sheet: The name of a sheet to get the dimensions of.

    Returns:
        The *(number of rows, number of columns)* in `sheet`.
    """
    try:
        s = self._workbook.sheet_by_name(sheet)
    except XLRDError:
        msg = f"A sheet named {sheet!r} is not in {self._file!r}"
        raise ValueError(msg) from None
    else:
        return (s.nrows, s.ncols)

read ¤

read(
    cells: str | None = None,
    sheet: str | None = None,
    *,
    as_datetime: bool = True,
    invalid_date: str | date | datetime | None = None,
    merged: bool = False,
    skip_rows: Iterable[int] | None = None,
) -> Any | list[tuple[Any, ...]]

Read cell values from the Excel spreadsheet.

Parameters:

Name Type Description Default
cells str | None

The cell(s) to read. For example,

  • C9 — a single value,
  • B — all values in column B,
  • A2:D2 — all values in row 2 for columns A B C D,
  • C9:G20 — all values in rows 9 to 20 for columns C D E F G,
  • D,F,H:K — all values in columns D F H I J K
  • F3:H10,K — all values in rows 3 to 10 for columns F G H K.

If not specified, returns all values in the specified sheet.

 None
sheet str | None

The name of the sheet to read the value(s) from. If there is only one sheet in the spreadsheet then you do not need to specify the name of the sheet.

 None
as_datetime bool

Whether dates should be returned as datetime or date objects. If False, dates are returned as an ISO 8601 string.

 True
invalid_date str | date | datetime | None

If None, an error is raised if a cell contains a value that is an invalid date. If not None, all cells that contain an invalid date are replaced with the specified value.

 None
merged bool

Applies to cells that are merged with other cells. If cells are merged, then only the top-left cell has the value and all other cells in the merger are empty. Enabling this argument is currently not supported and the value must be False.

 False
skip_rows Iterable[int] | None

Row numbers to skip. The row numbers are as shown when the spreadsheet is viewed in Excel and are not based on the row number in the data that would be returned. For example, if cells="A3:D10" and you want to skip the data in rows A5:D5 and A7:D7 then skip_rows=[5, 7] achieves the desired result.

 None

Returns:

Type Description
Any | list[tuple[Any, ...]]

The value(s) of the requested cell(s).

Examples:

>>> excel.read()
[('temperature', 'humidity'), (20.33, 49.82), (20.23, 46.06), (20.41, 47.06), (20.29, 48.32)]
>>> excel.read("B2")
49.82
>>> excel.read("A")
[('temperature',), (20.33,), (20.23,), (20.41,), (20.29,)]
>>> excel.read("B:B")
[('humidity',), (49.82,), (46.06,), (47.06,), (48.32,)]
>>> excel.read("A1:B1")
[('temperature', 'humidity')]
>>> excel.read("A2:B4")
[(20.33, 49.82), (20.23, 46.06), (20.41, 47.06)]
>>> excel.read("B,A")
[('humidity', 'temperature'), (49.82, 20.33), (46.06, 20.23), (47.06, 20.41), (48.32, 20.29)]
Source code in src/msl/io/readers/excel.py 
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
def read(  # noqa: PLR0913
    self,
    cells: str | None = None,
    sheet: str | None = None,
    *,
    as_datetime: bool = True,
    invalid_date: str | date | datetime | None = None,
    merged: bool = False,
    skip_rows: Iterable[int] | None = None,
) -> Any | list[tuple[Any, ...]]:
    """Read cell values from the Excel spreadsheet.

    Args:
        cells: The cell(s) to read. For example,

            * `C9` &mdash; a single value,
            * `B` &mdash; all values in column *B*,
            * `A2:D2` &mdash; all values in row 2 for columns *A B C D*,
            * `C9:G20` &mdash; all values in rows 9 to 20 for columns *C D E F G*,
            * `D,F,H:K` &mdash; all values in columns *D F H I J K*
            * `F3:H10,K` &mdash; all values in rows 3 to 10 for columns *F G H K*.

            If not specified, returns all values in the specified `sheet`.
        sheet: The name of the sheet to read the value(s) from. If there is only
            one sheet in the spreadsheet then you do not need to specify the name
            of the sheet.
        as_datetime: Whether dates should be returned as [datetime][datetime.datetime] or
            [date][datetime.date] objects. If `False`, dates are returned as an
            ISO 8601 string.
        invalid_date: If `None`, an error is raised if a cell contains a value that
            is an invalid date. If not `None`, all cells that contain an invalid date are
            replaced with the specified value.
        merged: Applies to cells that are merged with other cells. If cells are merged, then
            only the top-left cell has the value and all other cells in the merger are empty.
            Enabling this argument is currently not supported and the value must be `False`.
        skip_rows: Row numbers to skip. The row numbers are as shown when the spreadsheet is
            viewed in Excel and are not based on the row number in the data that would be
            returned. For example, if `cells="A3:D10"` and you want to skip the data in rows
            `A5:D5` and `A7:D7` then `skip_rows=[5, 7]` achieves the desired result.

    Returns:
        The value(s) of the requested cell(s).

    **Examples:**
    <!-- invisible-code-block: pycon
    >>> from msl.io import ExcelReader
    >>> excel = ExcelReader('./tests/samples/lab_environment.xlsx')

    -->

    ```pycon
    >>> excel.read()
    [('temperature', 'humidity'), (20.33, 49.82), (20.23, 46.06), (20.41, 47.06), (20.29, 48.32)]
    >>> excel.read("B2")
    49.82
    >>> excel.read("A")
    [('temperature',), (20.33,), (20.23,), (20.41,), (20.29,)]
    >>> excel.read("B:B")
    [('humidity',), (49.82,), (46.06,), (47.06,), (48.32,)]
    >>> excel.read("A1:B1")
    [('temperature', 'humidity')]
    >>> excel.read("A2:B4")
    [(20.33, 49.82), (20.23, 46.06), (20.41, 47.06)]
    >>> excel.read("B,A")
    [('humidity', 'temperature'), (49.82, 20.33), (46.06, 20.23), (47.06, 20.41), (48.32, 20.29)]

    ```
    """
    if merged:
        msg = "The `merged` argument must be False to read an Excel spreadsheet"
        raise ValueError(msg)

    if not sheet:
        names = self.sheet_names()
        if len(names) == 1:
            sheet_name = names[0]
        elif not names:
            msg = "Cannot determine the names of the sheets in the Excel file"
            raise ValueError(msg)
        else:
            sheets = ", ".join(repr(n) for n in names)
            msg = (
                f"{self.file!r} contains the following sheets:\n  {sheets}\n"
                f"You must specify the name of the sheet to read"
            )
            raise ValueError(msg)
    else:
        sheet_name = sheet

    try:
        _sheet = self._workbook.sheet_by_name(sheet_name)
    except XLRDError:
        msg = f"A sheet named {sheet_name!r} is not in {self._file!r}"
        raise ValueError(msg) from None

    cols: range | list[int]
    nrows, ncols = _sheet.nrows, _sheet.ncols
    if cells is None:
        is_range = True
        r1, r2, cols = 0, nrows, range(ncols)
    else:
        is_range, r1, r, cols = to_ranges(cells)
        r2 = nrows if r is None else min(r, nrows)
        cols = [c for c in cols if c < ncols]

    if r1 >= nrows or not cols:
        return [] if is_range else None

    skip: set[int] = {r - r1 - 1 for r in skip_rows} if skip_rows else set()
    data = [
        tuple(self._value(_sheet, r, c, as_datetime, invalid_date) for c in cols)
        for i, r in enumerate(range(r1, r2))
        if i not in skip
    ]

    if is_range:
        return data

    try:
        return data[0][0]
    except IndexError:
        return None

sheet_names ¤

sheet_names() -> tuple[str, ...]

Get the names of all sheets in the Excel spreadsheet.

Returns:

Type Description
tuple[str, ...]

The names of all sheets.
Source code in src/msl/io/readers/excel.py 
233
234
235
236
237
238
239
def sheet_names(self) -> tuple[str, ...]:
    """Get the names of all sheets in the Excel spreadsheet.

    Returns:
        The names of all sheets.
    """
    return tuple(self._workbook.sheet_names())

Read a Google Sheets spreadsheet.

GSheetsReader ¤

GSheetsReader(
    file: PathLike,
    *,
    account: str | None = None,
    credentials: PathLike | None = None,
)

Bases: Spreadsheet

Read a Google Sheets spreadsheet.

This class simply provides a convenience for reading cell values (not drawings or charts) from Google spreadsheets. It is not registered as a Reader because the information in a spreadsheet is unstructured and therefore one cannot generalize how to parse a spreadsheet to create a Root.

Parameters:

Name Type Description Default
file PathLike

The ID or path of a Google Sheets spreadsheet.

 required
account str | None

Since a person may have multiple Google accounts, and multiple people may run the same code, this parameter decides which token to load to authenticate with the Google API. The value can be any text (or None) that you want to associate with a particular Google account, provided that it contains valid characters for a filename. The value that you chose when you authenticated with your credentials should be used for all future instances of this class to access that particular Google account. You can associate a different value with a Google account at any time (by passing in a different account value), but you may be asked to authenticate with your credentials again, or, alternatively, you can rename the token files located in MSL_IO_DIR to match the new account value.

 None
credentials PathLike | None

The path to the client secrets OAuth credential file. This parameter only needs to be specified the first time that you authenticate with a particular Google account or if you delete the token file that was created when you previously authenticated.

 None

Examples: 

from msl.io import GSheetsReader

# Specify the path
sheets = GSheetsReader("Google Drive/registers/equipment.gsheet")

# Specify the ID
sheets = GSheetsReader("1TI3pM-534SZ5DQTEZ-7HCI04648f8ZpLGbfHWJu9FSo")

Source code in src/msl/io/readers/gsheets.py 
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
def __init__(
    self,
    file: PathLike,
    *,
    account: str | None = None,
    credentials: PathLike | None = None,
) -> None:
    """Read a Google Sheets spreadsheet.

    This class simply provides a convenience for reading cell values (not
    drawings or charts) from Google spreadsheets. It is not registered as
    a [Reader][msl.io.base.Reader] because the information in a spreadsheet
    is unstructured and therefore one cannot generalize how to parse a
    spreadsheet to create a [Root][msl.io.base.Root].

    Args:
        file: The ID or path of a Google Sheets spreadsheet.
        account: Since a person may have multiple Google accounts, and multiple people
            may run the same code, this parameter decides which token to load
            to authenticate with the Google API. The value can be any text (or
            `None`) that you want to associate with a particular Google
            account, provided that it contains valid characters for a filename.
            The value that you chose when you authenticated with your `credentials`
            should be used for all future instances of this class to access that
            particular Google account. You can associate a different value with
            a Google account at any time (by passing in a different `account`
            value), but you may be asked to authenticate with your `credentials`
            again, or, alternatively, you can rename the token files located in
            [MSL_IO_DIR][msl.io.constants.MSL_IO_DIR] to match the new `account` value.
        credentials: The path to the *client secrets* OAuth credential file. This
            parameter only needs to be specified the first time that you
            authenticate with a particular Google account or if you delete
            the token file that was created when you previously authenticated.

    **Examples:**
    ```python
    from msl.io import GSheetsReader

    # Specify the path
    sheets = GSheetsReader("Google Drive/registers/equipment.gsheet")

    # Specify the ID
    sheets = GSheetsReader("1TI3pM-534SZ5DQTEZ-7HCI04648f8ZpLGbfHWJu9FSo")
    ```
    """
    file = os.fsdecode(file)
    super().__init__(file)

    path, ext = os.path.splitext(file)  # noqa: PTH122
    folders, _ = os.path.split(path)

    self._spreadsheet_id: str
    if ext or folders or not _google_file_id_regex.match(path):
        self._spreadsheet_id = GDrive(account=account, credentials=credentials).file_id(
            path, mime_type=GSheets.MIME_TYPE
        )
    else:
        self._spreadsheet_id = path

    self._gsheets: GSheets = GSheets(account=account, credentials=credentials, read_only=True)
    self._cached_sheet_name: str | None = None

close ¤

close() -> None

Close the connection to the GSheet API service.

Source code in src/msl/io/readers/gsheets.py 
107
108
109
def close(self) -> None:
    """Close the connection to the GSheet API service."""
    self._gsheets.close()

read ¤

read(
    cells: str | None = None,
    sheet: str | None = None,
    *,
    as_datetime: bool = True,
    invalid_date: str | date | datetime | None = None,
    merged: bool = False,
    skip_rows: Iterable[int] | None = None,
) -> Any | list[tuple[Any, ...]]

Read cell values from the Google Sheets spreadsheet.

Parameters:

Name Type Description Default
cells str | None

The cell(s) to read. For example, C9 will return a single value and C9:G20 will return all values in the specified range. If not specified then returns all values in the specified sheet.

 None
sheet str | None

The name of the sheet to read the value(s) from. If there is only one sheet in the spreadsheet then you do not need to specify the name of the sheet.

 None
as_datetime bool

Whether dates should be returned as datetime or date objects. If False, dates are returned as a string in the display format of the spreadsheet cell.

 True
invalid_date str | date | datetime | None

If None, an error is raised if a cell contains a value that is an invalid date. If a datetime instance (which is the only other allowed type besides None, the other types are kept for consistency with other spreadsheet readers), all cells that contain an invalid date are replaced with the specified value.

 None
merged bool

Applies to cells that are merged with other cells. If cells are merged, then only the top-left cell has the value and all other cells in the merger are empty. Enabling this argument is currently not supported and the value must be False.

 False
skip_rows Iterable[int] | None

Row numbers to skip. The row numbers are as shown when the spreadsheet is viewed in Google Sheets and are not based on the row number in the data that would be returned. For example, if cells="A3:D10" and you want to skip the data in rows A5:D5 and A7:D7 then skip_rows=[5, 7] achieves the desired result.

 None

Returns:

Type Description
Any | list[tuple[Any, ...]]

The value(s) of the requested cell(s).

Examples:

>>> sheets.read()
[('temperature', 'humidity'), (20.33, 49.82), (20.23, 46.06), (20.41, 47.06), (20.29, 48.32)]
>>> sheets.read("B2")
49.82
>>> sheets.read("A:A")
[('temperature',), (20.33,), (20.23,), (20.41,), (20.29,)]
>>> sheets.read("A1:B1")
[('temperature', 'humidity')]
>>> sheets.read("A2:B4")
[(20.33, 49.82), (20.23, 46.06), (20.41, 47.06)]
Source code in src/msl/io/readers/gsheets.py 
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
def read(  # noqa: C901, PLR0912, PLR0913
    self,
    cells: str | None = None,
    sheet: str | None = None,
    *,
    as_datetime: bool = True,
    invalid_date: str | date | datetime | None = None,
    merged: bool = False,
    skip_rows: Iterable[int] | None = None,
) -> Any | list[tuple[Any, ...]]:
    """Read cell values from the Google Sheets spreadsheet.

    Args:
        cells: The cell(s) to read. For example, `C9` will return a single value
            and `C9:G20` will return all values in the specified range. If not
            specified then returns all values in the specified `sheet`.
        sheet: The name of the sheet to read the value(s) from. If there is only one
            sheet in the spreadsheet then you do not need to specify the name of the sheet.
        as_datetime: Whether dates should be returned as [datetime][datetime.datetime] or
            [date][datetime.date] objects. If `False`, dates are returned as a string in
            the display format of the spreadsheet cell.
        invalid_date: If `None`, an error is raised if a cell contains a value that
            is an invalid date. If a [datetime][datetime.datetime] instance (which is the
            only other allowed type besides `None`, the other types are kept for consistency
            with other spreadsheet readers), all cells that contain an invalid date are
            replaced with the specified value.
        merged: Applies to cells that are merged with other cells. If cells are merged, then
            only the top-left cell has the value and all other cells in the merger are empty.
            Enabling this argument is currently not supported and the value must be `False`.
        skip_rows: Row numbers to skip. The row numbers are as shown when the spreadsheet is
            viewed in Google Sheets and are not based on the row number in the data that would
            be returned. For example, if `cells="A3:D10"` and you want to skip the data in rows
            `A5:D5` and `A7:D7` then `skip_rows=[5, 7]` achieves the desired result.

    Returns:
        The value(s) of the requested cell(s).

    **Examples:**
    <!-- invisible-code-block: pycon
    >>> SKIP_IF_NO_GOOGLE_SHEETS_READ_TOKEN()
    >>> from msl.io import GSheetsReader
    >>> sheets = GSheetsReader('1TI3pM-534SZ5DQTEZ-7vCI04l48f8ZpLGbfEWJuCFSo', account='testing')

    -->

    ```pycon
    >>> sheets.read()
    [('temperature', 'humidity'), (20.33, 49.82), (20.23, 46.06), (20.41, 47.06), (20.29, 48.32)]
    >>> sheets.read("B2")
    49.82
    >>> sheets.read("A:A")
    [('temperature',), (20.33,), (20.23,), (20.41,), (20.29,)]
    >>> sheets.read("A1:B1")
    [('temperature', 'humidity')]
    >>> sheets.read("A2:B4")
    [(20.33, 49.82), (20.23, 46.06), (20.41, 47.06)]

    ```
    """
    if merged:
        msg = "The `merged` argument must be False to read a Google spreadsheet"
        raise ValueError(msg)

    if (invalid_date is not None) and (not isinstance(invalid_date, datetime)):
        msg = "The `invalid_date` type must be an instance of `datetime`"
        raise TypeError(msg)

    if not sheet:
        if self._cached_sheet_name:
            sheet = self._cached_sheet_name
        else:
            names = self.sheet_names()
            if len(names) != 1:
                sheet_names = ", ".join(repr(n) for n in names)
                msg = (
                    f"{self._file} contains the following sheets:\n  {sheet_names}\n"
                    f"You must specify the name of the sheet to read"
                )
                raise ValueError(msg)
            sheet = names[0]
            self._cached_sheet_name = sheet

    if cells:
        _, r1, _, _ = to_ranges(cells)
        ranges = f"{sheet}!{cells}"
    else:
        r1 = 0
        ranges = sheet

    cells_dict = self._gsheets.cells(self._spreadsheet_id, ranges=ranges)

    if sheet not in cells_dict:
        msg = f"A sheet named {sheet!r} is not in {self._file!r}"
        raise ValueError(msg)

    values: list[tuple[Any, ...]] = []
    skip: set[int] = {r - r1 - 1 for r in skip_rows} if skip_rows else set()
    for i, row in enumerate(cells_dict[sheet]):
        if i in skip:
            continue
        row_values: list[Any] = []
        for item in row:
            if item.type == GCellType.DATE:
                value = GSheets.to_datetime(item.value, invalid_date).date() if as_datetime else item.formatted
            elif item.type == GCellType.DATE_TIME:
                value = GSheets.to_datetime(item.value, invalid_date) if as_datetime else item.formatted
            else:
                value = item.value
            row_values.append(value)
        values.append(tuple(row_values))

    if not cells:
        return values

    if ":" not in cells:
        if values:
            return values[0][0]
        return None

    return values

sheet_names ¤

sheet_names() -> tuple[str, ...]

Get the names of all sheets in the Google Sheets spreadsheet.

Returns:

Type Description
tuple[str, ...]

The names of all sheets.
Source code in src/msl/io/readers/gsheets.py 
232
233
234
235
236
237
238
def sheet_names(self) -> tuple[str, ...]:
    """Get the names of all sheets in the Google Sheets spreadsheet.

    Returns:
        The names of all sheets.
    """
    return self._gsheets.sheet_names(self._spreadsheet_id)

Read an OpenDocument Spreadsheet (.ods and .fods files, Version 1.2).

ODSReader ¤

ODSReader(file: PathLike, **kwargs: Any)

Bases: Spreadsheet

Read an OpenDocument Spreadsheet (.ods and .fods files, Version 1.2).

This class simply provides a convenience for reading cell values (not drawings or charts) from OpenDocument Spreadsheets. It is not registered as a Reader because the information in a spreadsheet is unstructured and therefore one cannot generalize how to parse a spreadsheet to create a Root.

Tip

If defusedxml is installed, that package is used to parse the contents of the file instead of the xml module.

Parameters:

Name Type Description Default
file PathLike

The path to an OpenDocument Spreadsheet file.

 required
kwargs Any

All keyword arguments are ignored.

 {}

Examples: 

from msl.io import ODSReader
ods = ODSReader("lab_environment.ods")

Source code in src/msl/io/readers/ods.py 
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
def __init__(self, file: PathLike, **kwargs: Any) -> None:  # noqa: ARG002
    """Read an OpenDocument Spreadsheet (*.ods* and *.fods* files, Version 1.2).

    This class simply provides a convenience for reading cell values (not
    drawings or charts) from OpenDocument Spreadsheets. It is not registered
    as a [Reader][msl.io.base.Reader] because the information in a spreadsheet
    is unstructured and therefore one cannot generalize how to parse a
    spreadsheet to create a [Root][msl.io.base.Root].

    !!! tip
        If [defusedxml](https://pypi.org/project/defusedxml/){:target="_blank"} is installed,
        that package is used to parse the contents of the file instead of the
        [xml][]{:target="_blank"} module.

    Args:
        file: The path to an OpenDocument Spreadsheet file.
        kwargs: All keyword arguments are ignored.

    **Examples:**
    ```python
    from msl.io import ODSReader
    ods = ODSReader("lab_environment.ods")
    ```
    """
    f = os.fsdecode(file)
    super().__init__(f)

    self._spans: dict[int, tuple[int, Any]] = {}  # column-index: (spans-remaining, cell-value)
    self._row_data: dict[int, Any] = {}  # column-index: cell-value

    ext = get_extension(f).lower()
    content: Element[str]
    if ext == ".ods":
        with ZipFile(f) as z:
            try:
                content = ET.XML(z.read("content.xml"))
            except SyntaxError as e:
                e.msg += "\nThe ODS file might be password protected (which is not supported)"
                raise
    elif ext == ".fods":
        with open(f, mode="rb") as fp:  # noqa: PTH123
            content = ET.XML(fp.read())
    else:
        msg = f"Unsupported OpenDocument Spreadsheet file extension {ext!r}"
        raise ValueError(msg)

    self._tables: dict[str, Element[str]] = {
        self._attribute(t, "table", "name"): t for t in content.findall(".//table:table", namespaces=self._ns)
    }

close ¤

close() -> None

Free memory resources that are used to read the OpenDocument Spreadsheet.

Source code in src/msl/io/readers/ods.py 
104
105
106
107
108
def close(self) -> None:
    """Free memory resources that are used to read the OpenDocument Spreadsheet."""
    self._tables.clear()
    self._spans.clear()
    self._row_data.clear()

dimensions ¤

dimensions(sheet: str) -> tuple[int, int]

Get the number of rows and columns in a sheet.

Parameters:

Name Type Description Default
sheet str

The name of a sheet to get the dimensions of.

 required

Returns:

Type Description
tuple[int, int]

The (number of rows, number of columns) in sheet.
Source code in src/msl/io/readers/ods.py 
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
def dimensions(self, sheet: str) -> tuple[int, int]:
    """Get the number of rows and columns in a sheet.

    Args:
        sheet: The name of a sheet to get the dimensions of.

    Returns:
        The *(number of rows, number of columns)* in `sheet`.
    """
    table = self._tables.get(sheet)
    if table is None:
        msg = f"A sheet named {sheet!r} is not in {self._file!r}"
        raise ValueError(msg) from None

    num_cols = 0
    for col in table.findall(".//table:table-column", namespaces=self._ns):
        num_cols += int(self._attribute(col, "table", "number-columns-repeated", "1"))

    num_rows = sum(1 for _ in self._rows(table, 0, sys.maxsize - 1))
    return (num_rows, num_cols)

read ¤

read(
    cells: str | None = None,
    sheet: str | None = None,
    *,
    as_datetime: bool = True,
    invalid_date: str | date | datetime | None = None,
    merged: bool = False,
    skip_rows: Iterable[int] | None = None,
) -> Any | list[tuple[Any, ...]]

Read cell values from the OpenDocument Spreadsheet.

Parameters:

Name Type Description Default
cells str | None

The cell(s) to read. For example,

  • C9 — a single value,
  • B — all values in column B,
  • A2:D2 — all values in row 2 for columns A B C D,
  • C9:G20 — all values in rows 9 to 20 for columns C D E F G,
  • D,F,H:K — all values in columns D F H I J K
  • F3:H10,K — all values in rows 3 to 10 for columns F G H K.

If not specified, returns all values in the specified sheet.

 None
sheet str | None

The name of the sheet to read the value(s) from. If there is only one sheet in the spreadsheet then you do not need to specify the name of the sheet.

 None
as_datetime bool

Whether dates should be returned as datetime or date objects. If False, dates are returned as a string in the display format of the spreadsheet cell.

 True
invalid_date str | date | datetime | None

If None, an error is raised if a cell contains a value that is an invalid date. If not None, all cells that contain an invalid date are replaced with the specified value.

 None
merged bool

Applies to cells that are merged with other cells. If False, the value of each unmerged cell is returned, otherwise the same value is returned for all merged cells. In an OpenDocument Spreadsheet, the value of a hidden cell that is merged with a visible cell can still be retained (depends on how the merger was performed).

 False
skip_rows Iterable[int] | None

Row numbers to skip. The row numbers are as shown when the spreadsheet is viewed in the OpenDocument application and are not based on the row number in the data that would be returned. For example, if cells="A3:D10" and you want to skip the data in rows A5:D5 and A7:D7 then skip_rows=[5, 7] achieves the desired result.

 None

Returns:

Type Description
Any | list[tuple[Any, ...]]

The value(s) of the requested cell(s).

Examples:

>>> ods.read()
[('temperature', 'humidity'), (20.33, 49.82), (20.23, 46.06), (20.41, 47.06), (20.29, 48.32)]
>>> ods.read("B2")
49.82
>>> ods.read("A")
[('temperature',), (20.33,), (20.23,), (20.41,), (20.29,)]
>>> ods.read("B:B")
[('humidity',), (49.82,), (46.06,), (47.06,), (48.32,)]
>>> ods.read("A1:B1")
[('temperature', 'humidity')]
>>> ods.read("A2:B4")
[(20.33, 49.82), (20.23, 46.06), (20.41, 47.06)]
>>> ods.read("B,A")
[('humidity', 'temperature'), (49.82, 20.33), (46.06, 20.23), (47.06, 20.41), (48.32, 20.29)]
Source code in src/msl/io/readers/ods.py 
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
def read(  # noqa: C901, PLR0912, PLR0913
    self,
    cells: str | None = None,
    sheet: str | None = None,
    *,
    as_datetime: bool = True,
    invalid_date: str | date | datetime | None = None,
    merged: bool = False,
    skip_rows: Iterable[int] | None = None,
) -> Any | list[tuple[Any, ...]]:
    """Read cell values from the OpenDocument Spreadsheet.

    Args:
        cells: The cell(s) to read. For example,

            * `C9` &mdash; a single value,
            * `B` &mdash; all values in column *B*,
            * `A2:D2` &mdash; all values in row 2 for columns *A B C D*,
            * `C9:G20` &mdash; all values in rows 9 to 20 for columns *C D E F G*,
            * `D,F,H:K` &mdash; all values in columns *D F H I J K*
            * `F3:H10,K` &mdash; all values in rows 3 to 10 for columns *F G H K*.

            If not specified, returns all values in the specified `sheet`.
        sheet: The name of the sheet to read the value(s) from. If there is only
            one sheet in the spreadsheet then you do not need to specify the name
            of the sheet.
        as_datetime: Whether dates should be returned as [datetime][datetime.datetime]
            or [date][datetime.date] objects. If `False`, dates are returned as a
            string in the display format of the spreadsheet cell.
        invalid_date: If `None`, an error is raised if a cell contains a value that
            is an invalid date. If not `None`, all cells that contain an invalid date are
            replaced with the specified value.
        merged: Applies to cells that are merged with other cells. If `False`, the
            value of each unmerged cell is returned, otherwise the same value is
            returned for all merged cells. In an OpenDocument Spreadsheet, the value
            of a hidden cell that is merged with a visible cell can still be retained
            (depends on how the merger was performed).
        skip_rows: Row numbers to skip. The row numbers are as shown when the spreadsheet
            is viewed in the OpenDocument application and are not based on the row number
            in the data that would be returned. For example, if `cells="A3:D10"` and you
            want to skip the data in rows `A5:D5` and `A7:D7` then `skip_rows=[5, 7]`
            achieves the desired result.

    Returns:
        The value(s) of the requested cell(s).

    **Examples:**
    <!-- invisible-code-block: pycon
    >>> from msl.io import ODSReader
    >>> ods = ODSReader('./tests/samples/lab_environment.ods')

    -->

    ```pycon
    >>> ods.read()
    [('temperature', 'humidity'), (20.33, 49.82), (20.23, 46.06), (20.41, 47.06), (20.29, 48.32)]
    >>> ods.read("B2")
    49.82
    >>> ods.read("A")
    [('temperature',), (20.33,), (20.23,), (20.41,), (20.29,)]
    >>> ods.read("B:B")
    [('humidity',), (49.82,), (46.06,), (47.06,), (48.32,)]
    >>> ods.read("A1:B1")
    [('temperature', 'humidity')]
    >>> ods.read("A2:B4")
    [(20.33, 49.82), (20.23, 46.06), (20.41, 47.06)]
    >>> ods.read("B,A")
    [('humidity', 'temperature'), (49.82, 20.33), (46.06, 20.23), (47.06, 20.41), (48.32, 20.29)]

    ```
    """
    if not sheet:
        names = self.sheet_names()
        if len(names) == 1:
            name = names[0]
        elif not names:
            msg = "Cannot determine the names of the sheets in the OpenDocument file"
            raise ValueError(msg)
        else:
            sheets = ", ".join(repr(n) for n in names)
            msg = (
                f"{self.file!r} contains the following sheets:\n  {sheets}\n"
                f"You must specify the name of the sheet to read"
            )
            raise ValueError(msg)
    else:
        name = sheet

    table = self._tables.get(name)
    if table is None:
        msg = f"A sheet named {sheet!r} is not in {self._file!r}"
        raise ValueError(msg)

    maxsize = sys.maxsize - 1
    if cells is None:
        is_range = True
        r1, r2 = 0, maxsize
        c1, c2 = 0, maxsize
        self._row_data.clear()
    else:
        is_range, r1, r, cols = to_ranges(cells)
        r2 = maxsize if r is None else r - 1
        c1, c2 = min(cols), max(cols)
        self._row_data = dict.fromkeys(cols, b"-")  # A cell cannot contain bytes so b"-" cannot be a cell value

    self._spans.clear()
    data: list[tuple[Any, ...]] = []
    skip: set[int] = {r - r1 - 1 for r in skip_rows} if skip_rows else set()
    for i, row in enumerate(self._rows(table, r1, r2)):
        if i in skip:
            continue
        values = tuple(self._cell(name, row, c1, c2, as_datetime, merged, invalid_date))
        if values:
            data.append(values)

    if is_range:
        return data

    try:
        return data[0][0]
    except IndexError:
        return None

sheet_names ¤

sheet_names() -> tuple[str, ...]

Get the names of all sheets in the OpenDocument Spreadsheet.

Returns:

Type Description
tuple[str, ...]

The names of all sheets.
Source code in src/msl/io/readers/ods.py 
254
255
256
257
258
259
260
def sheet_names(self) -> tuple[str, ...]:
    """Get the names of all sheets in the OpenDocument Spreadsheet.

    Returns:
        The names of all sheets.
    """
    return tuple(self._tables.keys())