fastexcel

View Source

  1from __future__ import annotations
  2
  3import sys
  4import typing
  5from typing import TYPE_CHECKING, Callable, Literal
  6
  7if sys.version_info < (3, 10):
  8    from typing_extensions import TypeAlias
  9else:
 10    from typing import TypeAlias
 11
 12if TYPE_CHECKING:
 13    import pandas as pd
 14    import polars as pl
 15
 16from os.path import expanduser
 17from pathlib import Path
 18
 19import pyarrow as pa
 20
 21from ._fastexcel import (
 22    ArrowError,
 23    CalamineCellError,
 24    CalamineError,
 25    CannotRetrieveCellDataError,
 26    ColumnInfo,
 27    ColumnInfoNoDtype,
 28    ColumnNotFoundError,
 29    FastExcelError,
 30    InvalidParametersError,
 31    SheetNotFoundError,
 32    UnsupportedColumnTypeCombinationError,
 33    __version__,
 34    _ExcelReader,
 35    _ExcelSheet,
 36    _ExcelTable,
 37)
 38from ._fastexcel import read_excel as _read_excel
 39
 40DType = Literal["null", "int", "float", "string", "boolean", "datetime", "date", "duration"]
 41DTypeMap: TypeAlias = "dict[str | int, DType]"
 42ColumnNameFrom: TypeAlias = Literal["provided", "looked_up", "generated"]
 43DTypeFrom: TypeAlias = Literal["provided_by_index", "provided_by_name", "guessed"]
 44SheetVisible: TypeAlias = Literal["visible", "hidden", "veryhidden"]
 45
 46
 47def _recordbatch_to_polars(rb: pa.RecordBatch) -> pl.DataFrame:
 48    import polars as pl
 49
 50    df = pl.from_arrow(data=rb)
 51    assert isinstance(df, pl.DataFrame)
 52    return df
 53
 54
 55class ExcelSheet:
 56    """A class representing a single sheet in an Excel File"""
 57
 58    def __init__(self, sheet: _ExcelSheet) -> None:
 59        self._sheet = sheet
 60
 61    @property
 62    def name(self) -> str:
 63        """The name of the sheet"""
 64        return self._sheet.name
 65
 66    @property
 67    def width(self) -> int:
 68        """The sheet's width"""
 69        return self._sheet.width
 70
 71    @property
 72    def height(self) -> int:
 73        """The sheet's height, with `skip_rows` and `nrows` applied"""
 74        return self._sheet.height
 75
 76    @property
 77    def total_height(self) -> int:
 78        """The sheet's total height"""
 79        return self._sheet.total_height
 80
 81    @property
 82    def selected_columns(self) -> list[ColumnInfo]:
 83        """The sheet's selected columns"""
 84        return self._sheet.selected_columns
 85
 86    def available_columns(self) -> list[ColumnInfo]:
 87        """The columns available for the given sheet"""
 88        return self._sheet.available_columns()
 89
 90    @property
 91    def specified_dtypes(self) -> DTypeMap | None:
 92        """The dtypes specified for the sheet"""
 93        return self._sheet.specified_dtypes
 94
 95    @property
 96    def visible(self) -> SheetVisible:
 97        """The visibility of the sheet"""
 98        return self._sheet.visible
 99
100    def to_arrow(self) -> pa.RecordBatch:
101        """Converts the sheet to a pyarrow `RecordBatch`"""
102        return self._sheet.to_arrow()
103
104    def to_pandas(self) -> "pd.DataFrame":
105        """Converts the sheet to a Pandas `DataFrame`.
106
107        Requires the `pandas` extra to be installed.
108        """
109        # We know for sure that the sheet will yield exactly one RecordBatch
110        return self.to_arrow().to_pandas()
111
112    def to_polars(self) -> "pl.DataFrame":
113        """Converts the sheet to a Polars `DataFrame`.
114
115        Requires the `polars` extra to be installed.
116        """
117        return _recordbatch_to_polars(self.to_arrow())
118
119    def __repr__(self) -> str:
120        return self._sheet.__repr__()
121
122
123class ExcelTable:
124    """A class representing a single table in an Excel file"""
125
126    def __init__(self, table: _ExcelTable) -> None:
127        self._table = table
128
129    @property
130    def name(self) -> str:
131        """The name of the table"""
132        return self._table.name
133
134    @property
135    def sheet_name(self) -> str:
136        """The name of the sheet this table belongs to"""
137        return self._table.sheet_name
138
139    @property
140    def width(self) -> int:
141        """The table's width"""
142        return self._table.width
143
144    @property
145    def height(self) -> int:
146        """The table's height"""
147        return self._table.height
148
149    @property
150    def total_height(self) -> int:
151        """The table's total height"""
152        return self._table.total_height
153
154    @property
155    def offset(self) -> int:
156        """The table's offset before data starts"""
157        return self._table.offset
158
159    @property
160    def selected_columns(self) -> list[ColumnInfo]:
161        """The table's selected columns"""
162        return self._table.selected_columns
163
164    def available_columns(self) -> list[ColumnInfo]:
165        """The columns available for the given table"""
166        return self._table.available_columns()
167
168    @property
169    def specified_dtypes(self) -> DTypeMap | None:
170        """The dtypes specified for the table"""
171        return self._table.specified_dtypes
172
173    def to_arrow(self) -> pa.RecordBatch:
174        """Converts the table to a pyarrow `RecordBatch`"""
175        return self._table.to_arrow()
176
177    def to_pandas(self) -> "pd.DataFrame":
178        """Converts the table to a Pandas `DataFrame`.
179
180        Requires the `pandas` extra to be installed.
181        """
182        # We know for sure that the table will yield exactly one RecordBatch
183        return self.to_arrow().to_pandas()
184
185    def to_polars(self) -> "pl.DataFrame":
186        """Converts the table to a Polars `DataFrame`.
187
188        Requires the `polars` extra to be installed.
189        """
190        return _recordbatch_to_polars(self.to_arrow())
191
192
193class ExcelReader:
194    """A class representing an open Excel file and allowing to read its sheets"""
195
196    def __init__(self, reader: _ExcelReader) -> None:
197        self._reader = reader
198
199    @property
200    def sheet_names(self) -> list[str]:
201        """The list of sheet names"""
202        return self._reader.sheet_names
203
204    def load_sheet(
205        self,
206        idx_or_name: int | str,
207        *,
208        header_row: int | None = 0,
209        column_names: list[str] | None = None,
210        skip_rows: int | None = None,
211        n_rows: int | None = None,
212        schema_sample_rows: int | None = 1_000,
213        dtype_coercion: Literal["coerce", "strict"] = "coerce",
214        use_columns: list[str]
215        | list[int]
216        | str
217        | Callable[[ColumnInfoNoDtype], bool]
218        | None = None,
219        dtypes: DType | DTypeMap | None = None,
220    ) -> ExcelSheet:
221        """Loads a sheet lazily by index or name.
222
223        :param idx_or_name: The index (starting at 0) or the name of the sheet to load.
224        :param header_row: The index of the row containing the column labels, default index is 0.
225                           If `None`, the sheet does not have any column labels.
226                           Any rows before the `header_row` will be automatically skipped.
227        :param column_names: Overrides headers found in the document.
228                             If `column_names` is used, `header_row` will be ignored.
229        :param n_rows: Specifies how many rows should be loaded.
230                       If `None`, all rows are loaded
231        :param skip_rows: Specifies how many rows should be skipped after the `header_row`.
232                          Any rows before the `header_row` are automatically skipped.
233                          If `header_row` is `None`:
234                            - if `skip_rows` is `None` (default): it skips all empty rows
235                            at the beginning of the sheet.
236                            - if `skip_rows` is a number, it skips the specified number
237                            of rows from the start of the sheet.
238        :param schema_sample_rows: Specifies how many rows should be used to determine
239                                   the dtype of a column. Cannot be 0. A specific dtype can be
240                                   enforced for some or all columns through the `dtypes` parameter.
241                                   If `None`, all rows will be used.
242        :param dtype_coercion: Specifies how type coercion should behave. `coerce` (the default)
243                               will try to coerce different dtypes in a column to the same one,
244                               whereas `strict` will raise an error in case a column contains
245                               several dtypes. Note that this only applies to columns whose dtype
246                               is guessed, i.e. not specified via `dtypes`.
247        :param use_columns: Specifies the columns to use. Can either be:
248                            - `None` to select all columns
249                            - A list of strings and ints, the column names and/or indices
250                              (starting at 0)
251                            - A string, a comma separated list of Excel column letters and column
252                              ranges (e.g. `“A:E”` or `“A,C,E:F”`, which would result in
253                              `A,B,C,D,E` and `A,C,E,F`)
254                            - A callable, a function that takes a column and returns a boolean
255                              indicating whether the column should be used
256        :param dtypes: An optional dtype (for all columns)
257                       or dict of dtypes with keys as column indices or names.
258        """
259        return ExcelSheet(
260            self._reader.load_sheet(
261                idx_or_name=idx_or_name,
262                header_row=header_row,
263                column_names=column_names,
264                skip_rows=skip_rows,
265                n_rows=n_rows,
266                schema_sample_rows=schema_sample_rows,
267                dtype_coercion=dtype_coercion,
268                use_columns=use_columns,
269                dtypes=dtypes,
270                eager=False,
271            )
272        )
273
274    def table_names(self, sheet_name: str | None = None) -> list[str]:
275        """The list of table names.
276
277        Will return an empty list if no tables are found.
278
279        :param sheet_name: If given, will limit the list to the given sheet, will be faster
280        too.
281        """
282        return self._reader.table_names(sheet_name)
283
284    @typing.overload
285    def load_table(
286        self,
287        name: str,
288        *,
289        header_row: int | None = None,
290        column_names: list[str] | None = None,
291        skip_rows: int = 0,
292        n_rows: int | None = None,
293        schema_sample_rows: int | None = 1_000,
294        dtype_coercion: Literal["coerce", "strict"] = "coerce",
295        use_columns: list[str]
296        | list[int]
297        | str
298        | Callable[[ColumnInfoNoDtype], bool]
299        | None = None,
300        dtypes: DType | DTypeMap | None = None,
301        eager: Literal[False] = ...,
302    ) -> ExcelTable: ...
303    @typing.overload
304    def load_table(
305        self,
306        name: str,
307        *,
308        header_row: int | None = None,
309        column_names: list[str] | None = None,
310        skip_rows: int = 0,
311        n_rows: int | None = None,
312        schema_sample_rows: int | None = 1_000,
313        dtype_coercion: Literal["coerce", "strict"] = "coerce",
314        use_columns: list[str]
315        | list[int]
316        | str
317        | Callable[[ColumnInfoNoDtype], bool]
318        | None = None,
319        dtypes: DType | DTypeMap | None = None,
320        eager: Literal[True] = ...,
321    ) -> pa.RecordBatch: ...
322    def load_table(
323        self,
324        name: str,
325        *,
326        header_row: int | None = None,
327        column_names: list[str] | None = None,
328        skip_rows: int = 0,
329        n_rows: int | None = None,
330        schema_sample_rows: int | None = 1_000,
331        dtype_coercion: Literal["coerce", "strict"] = "coerce",
332        use_columns: list[str]
333        | list[int]
334        | str
335        | Callable[[ColumnInfoNoDtype], bool]
336        | None = None,
337        dtypes: DType | DTypeMap | None = None,
338        eager: bool = False,
339    ) -> ExcelTable | pa.RecordBatch:
340        """Loads a table by name.
341
342        :param name: The name of the table to load.
343        :param header_row: The index of the row containing the column labels.
344                           If `None`, the table's column names will be used.
345                           Any rows before the `header_row` will be automatically skipped.
346        :param column_names: Overrides headers found in the document.
347                             If `column_names` is used, `header_row` will be ignored.
348        :param n_rows: Specifies how many rows should be loaded.
349                       If `None`, all rows are loaded
350        :param skip_rows: Specifies how many rows should be skipped after the `header_row`.
351                          Any rows before the `header_row` are automatically skipped.
352                          If `header_row` is `None`, it skips the number of rows from the
353                          start of the sheet.
354        :param schema_sample_rows: Specifies how many rows should be used to determine
355                                   the dtype of a column. Cannot be 0. A specific dtype can be
356                                   enforced for some or all columns through the `dtypes` parameter.
357                                   If `None`, all rows will be used.
358        :param dtype_coercion: Specifies how type coercion should behave. `coerce` (the default)
359                               will try to coerce different dtypes in a column to the same one,
360                               whereas `strict` will raise an error in case a column contains
361                               several dtypes. Note that this only applies to columns whose dtype
362                               is guessed, i.e. not specified via `dtypes`.
363        :param use_columns: Specifies the columns to use. Can either be:
364                            - `None` to select all columns
365                            - A list of strings and ints, the column names and/or indices
366                              (starting at 0)
367                            - A string, a comma separated list of Excel column letters and column
368                              ranges (e.g. `“A:E”` or `“A,C,E:F”`, which would result in
369                              `A,B,C,D,E` and `A,C,E,F`)
370                            - A callable, a function that takes a column and returns a boolean
371                              indicating whether the column should be used
372        :param dtypes: An optional dtype (for all columns)
373                       or dict of dtypes with keys as column indices or names.
374        """
375        output = self._reader.load_table(  # type:ignore[call-overload,misc]
376            name=name,
377            header_row=header_row,
378            column_names=column_names,
379            skip_rows=skip_rows,
380            n_rows=n_rows,
381            schema_sample_rows=schema_sample_rows,
382            dtype_coercion=dtype_coercion,
383            use_columns=use_columns,
384            dtypes=dtypes,
385            eager=eager,
386        )
387        if eager:
388            return output
389        return ExcelTable(output)
390
391    def load_sheet_eager(
392        self,
393        idx_or_name: int | str,
394        *,
395        header_row: int | None = 0,
396        column_names: list[str] | None = None,
397        skip_rows: int | None = None,
398        n_rows: int | None = None,
399        schema_sample_rows: int | None = 1_000,
400        dtype_coercion: Literal["coerce", "strict"] = "coerce",
401        use_columns: list[str] | list[int] | str | None = None,
402        dtypes: DType | DTypeMap | None = None,
403    ) -> pa.RecordBatch:
404        """Loads a sheet eagerly by index or name.
405
406        For xlsx files, this will be faster and more memory-efficient, as it will use
407        `worksheet_range_ref` under the hood, which returns borrowed types.
408
409        Refer to `load_sheet` for parameter documentation
410        """
411        return self._reader.load_sheet(
412            idx_or_name=idx_or_name,
413            header_row=header_row,
414            column_names=column_names,
415            skip_rows=skip_rows,
416            n_rows=n_rows,
417            schema_sample_rows=schema_sample_rows,
418            dtype_coercion=dtype_coercion,
419            use_columns=use_columns,
420            dtypes=dtypes,
421            eager=True,
422        )
423
424    def load_sheet_by_name(
425        self,
426        name: str,
427        *,
428        header_row: int | None = 0,
429        column_names: list[str] | None = None,
430        skip_rows: int | None = None,
431        n_rows: int | None = None,
432        schema_sample_rows: int | None = 1_000,
433        dtype_coercion: Literal["coerce", "strict"] = "coerce",
434        use_columns: list[str]
435        | list[int]
436        | str
437        | Callable[[ColumnInfoNoDtype], bool]
438        | None = None,
439        dtypes: DType | DTypeMap | None = None,
440    ) -> ExcelSheet:
441        """Loads a sheet by name.
442
443        Refer to `load_sheet` for parameter documentation
444        """
445        return self.load_sheet(
446            name,
447            header_row=header_row,
448            column_names=column_names,
449            skip_rows=skip_rows,
450            n_rows=n_rows,
451            schema_sample_rows=schema_sample_rows,
452            dtype_coercion=dtype_coercion,
453            use_columns=use_columns,
454            dtypes=dtypes,
455        )
456
457    def load_sheet_by_idx(
458        self,
459        idx: int,
460        *,
461        header_row: int | None = 0,
462        column_names: list[str] | None = None,
463        skip_rows: int | None = None,
464        n_rows: int | None = None,
465        schema_sample_rows: int | None = 1_000,
466        dtype_coercion: Literal["coerce", "strict"] = "coerce",
467        use_columns: list[str]
468        | list[int]
469        | str
470        | Callable[[ColumnInfoNoDtype], bool]
471        | None = None,
472        dtypes: DType | DTypeMap | None = None,
473    ) -> ExcelSheet:
474        """Loads a sheet by index.
475
476        Refer to `load_sheet` for parameter documentation
477        """
478        return self.load_sheet(
479            idx,
480            header_row=header_row,
481            column_names=column_names,
482            skip_rows=skip_rows,
483            n_rows=n_rows,
484            schema_sample_rows=schema_sample_rows,
485            dtype_coercion=dtype_coercion,
486            use_columns=use_columns,
487            dtypes=dtypes,
488        )
489
490    def __repr__(self) -> str:
491        return self._reader.__repr__()
492
493
494def read_excel(source: Path | str | bytes) -> ExcelReader:
495    """Opens and loads an excel file.
496
497    :param source: The path to a file or its content as bytes
498    """
499    if isinstance(source, (str, Path)):
500        source = expanduser(source)
501    return ExcelReader(_read_excel(source))
502
503
504__all__ = (
505    ## version
506    "__version__",
507    ## main entrypoint
508    "read_excel",
509    ## Python types
510    "DType",
511    "DTypeMap",
512    # Excel reader
513    "ExcelReader",
514    # Excel sheet
515    "ExcelSheet",
516    # Column metadata
517    "DTypeFrom",
518    "ColumnNameFrom",
519    "ColumnInfo",
520    # Exceptions
521    "FastExcelError",
522    "CannotRetrieveCellDataError",
523    "CalamineCellError",
524    "CalamineError",
525    "SheetNotFoundError",
526    "ColumnNotFoundError",
527    "ArrowError",
528    "InvalidParametersError",
529    "UnsupportedColumnTypeCombinationError",
530)

__version__ = '0.13.0'

def read_excel(source: pathlib.Path | str | bytes) -> ExcelReader: View Source

495def read_excel(source: Path | str | bytes) -> ExcelReader:
496    """Opens and loads an excel file.
497
498    :param source: The path to a file or its content as bytes
499    """
500    if isinstance(source, (str, Path)):
501        source = expanduser(source)
502    return ExcelReader(_read_excel(source))

Opens and loads an excel file.

Parameters

source: The path to a file or its content as bytes

DType = typing.Literal['null', 'int', 'float', 'string', 'boolean', 'datetime', 'date', 'duration']

DTypeMap: TypeAlias = 'dict[str | int, DType]'

class ExcelReader: View Source

194class ExcelReader:
195    """A class representing an open Excel file and allowing to read its sheets"""
196
197    def __init__(self, reader: _ExcelReader) -> None:
198        self._reader = reader
199
200    @property
201    def sheet_names(self) -> list[str]:
202        """The list of sheet names"""
203        return self._reader.sheet_names
204
205    def load_sheet(
206        self,
207        idx_or_name: int | str,
208        *,
209        header_row: int | None = 0,
210        column_names: list[str] | None = None,
211        skip_rows: int | None = None,
212        n_rows: int | None = None,
213        schema_sample_rows: int | None = 1_000,
214        dtype_coercion: Literal["coerce", "strict"] = "coerce",
215        use_columns: list[str]
216        | list[int]
217        | str
218        | Callable[[ColumnInfoNoDtype], bool]
219        | None = None,
220        dtypes: DType | DTypeMap | None = None,
221    ) -> ExcelSheet:
222        """Loads a sheet lazily by index or name.
223
224        :param idx_or_name: The index (starting at 0) or the name of the sheet to load.
225        :param header_row: The index of the row containing the column labels, default index is 0.
226                           If `None`, the sheet does not have any column labels.
227                           Any rows before the `header_row` will be automatically skipped.
228        :param column_names: Overrides headers found in the document.
229                             If `column_names` is used, `header_row` will be ignored.
230        :param n_rows: Specifies how many rows should be loaded.
231                       If `None`, all rows are loaded
232        :param skip_rows: Specifies how many rows should be skipped after the `header_row`.
233                          Any rows before the `header_row` are automatically skipped.
234                          If `header_row` is `None`:
235                            - if `skip_rows` is `None` (default): it skips all empty rows
236                            at the beginning of the sheet.
237                            - if `skip_rows` is a number, it skips the specified number
238                            of rows from the start of the sheet.
239        :param schema_sample_rows: Specifies how many rows should be used to determine
240                                   the dtype of a column. Cannot be 0. A specific dtype can be
241                                   enforced for some or all columns through the `dtypes` parameter.
242                                   If `None`, all rows will be used.
243        :param dtype_coercion: Specifies how type coercion should behave. `coerce` (the default)
244                               will try to coerce different dtypes in a column to the same one,
245                               whereas `strict` will raise an error in case a column contains
246                               several dtypes. Note that this only applies to columns whose dtype
247                               is guessed, i.e. not specified via `dtypes`.
248        :param use_columns: Specifies the columns to use. Can either be:
249                            - `None` to select all columns
250                            - A list of strings and ints, the column names and/or indices
251                              (starting at 0)
252                            - A string, a comma separated list of Excel column letters and column
253                              ranges (e.g. `“A:E”` or `“A,C,E:F”`, which would result in
254                              `A,B,C,D,E` and `A,C,E,F`)
255                            - A callable, a function that takes a column and returns a boolean
256                              indicating whether the column should be used
257        :param dtypes: An optional dtype (for all columns)
258                       or dict of dtypes with keys as column indices or names.
259        """
260        return ExcelSheet(
261            self._reader.load_sheet(
262                idx_or_name=idx_or_name,
263                header_row=header_row,
264                column_names=column_names,
265                skip_rows=skip_rows,
266                n_rows=n_rows,
267                schema_sample_rows=schema_sample_rows,
268                dtype_coercion=dtype_coercion,
269                use_columns=use_columns,
270                dtypes=dtypes,
271                eager=False,
272            )
273        )
274
275    def table_names(self, sheet_name: str | None = None) -> list[str]:
276        """The list of table names.
277
278        Will return an empty list if no tables are found.
279
280        :param sheet_name: If given, will limit the list to the given sheet, will be faster
281        too.
282        """
283        return self._reader.table_names(sheet_name)
284
285    @typing.overload
286    def load_table(
287        self,
288        name: str,
289        *,
290        header_row: int | None = None,
291        column_names: list[str] | None = None,
292        skip_rows: int = 0,
293        n_rows: int | None = None,
294        schema_sample_rows: int | None = 1_000,
295        dtype_coercion: Literal["coerce", "strict"] = "coerce",
296        use_columns: list[str]
297        | list[int]
298        | str
299        | Callable[[ColumnInfoNoDtype], bool]
300        | None = None,
301        dtypes: DType | DTypeMap | None = None,
302        eager: Literal[False] = ...,
303    ) -> ExcelTable: ...
304    @typing.overload
305    def load_table(
306        self,
307        name: str,
308        *,
309        header_row: int | None = None,
310        column_names: list[str] | None = None,
311        skip_rows: int = 0,
312        n_rows: int | None = None,
313        schema_sample_rows: int | None = 1_000,
314        dtype_coercion: Literal["coerce", "strict"] = "coerce",
315        use_columns: list[str]
316        | list[int]
317        | str
318        | Callable[[ColumnInfoNoDtype], bool]
319        | None = None,
320        dtypes: DType | DTypeMap | None = None,
321        eager: Literal[True] = ...,
322    ) -> pa.RecordBatch: ...
323    def load_table(
324        self,
325        name: str,
326        *,
327        header_row: int | None = None,
328        column_names: list[str] | None = None,
329        skip_rows: int = 0,
330        n_rows: int | None = None,
331        schema_sample_rows: int | None = 1_000,
332        dtype_coercion: Literal["coerce", "strict"] = "coerce",
333        use_columns: list[str]
334        | list[int]
335        | str
336        | Callable[[ColumnInfoNoDtype], bool]
337        | None = None,
338        dtypes: DType | DTypeMap | None = None,
339        eager: bool = False,
340    ) -> ExcelTable | pa.RecordBatch:
341        """Loads a table by name.
342
343        :param name: The name of the table to load.
344        :param header_row: The index of the row containing the column labels.
345                           If `None`, the table's column names will be used.
346                           Any rows before the `header_row` will be automatically skipped.
347        :param column_names: Overrides headers found in the document.
348                             If `column_names` is used, `header_row` will be ignored.
349        :param n_rows: Specifies how many rows should be loaded.
350                       If `None`, all rows are loaded
351        :param skip_rows: Specifies how many rows should be skipped after the `header_row`.
352                          Any rows before the `header_row` are automatically skipped.
353                          If `header_row` is `None`, it skips the number of rows from the
354                          start of the sheet.
355        :param schema_sample_rows: Specifies how many rows should be used to determine
356                                   the dtype of a column. Cannot be 0. A specific dtype can be
357                                   enforced for some or all columns through the `dtypes` parameter.
358                                   If `None`, all rows will be used.
359        :param dtype_coercion: Specifies how type coercion should behave. `coerce` (the default)
360                               will try to coerce different dtypes in a column to the same one,
361                               whereas `strict` will raise an error in case a column contains
362                               several dtypes. Note that this only applies to columns whose dtype
363                               is guessed, i.e. not specified via `dtypes`.
364        :param use_columns: Specifies the columns to use. Can either be:
365                            - `None` to select all columns
366                            - A list of strings and ints, the column names and/or indices
367                              (starting at 0)
368                            - A string, a comma separated list of Excel column letters and column
369                              ranges (e.g. `“A:E”` or `“A,C,E:F”`, which would result in
370                              `A,B,C,D,E` and `A,C,E,F`)
371                            - A callable, a function that takes a column and returns a boolean
372                              indicating whether the column should be used
373        :param dtypes: An optional dtype (for all columns)
374                       or dict of dtypes with keys as column indices or names.
375        """
376        output = self._reader.load_table(  # type:ignore[call-overload,misc]
377            name=name,
378            header_row=header_row,
379            column_names=column_names,
380            skip_rows=skip_rows,
381            n_rows=n_rows,
382            schema_sample_rows=schema_sample_rows,
383            dtype_coercion=dtype_coercion,
384            use_columns=use_columns,
385            dtypes=dtypes,
386            eager=eager,
387        )
388        if eager:
389            return output
390        return ExcelTable(output)
391
392    def load_sheet_eager(
393        self,
394        idx_or_name: int | str,
395        *,
396        header_row: int | None = 0,
397        column_names: list[str] | None = None,
398        skip_rows: int | None = None,
399        n_rows: int | None = None,
400        schema_sample_rows: int | None = 1_000,
401        dtype_coercion: Literal["coerce", "strict"] = "coerce",
402        use_columns: list[str] | list[int] | str | None = None,
403        dtypes: DType | DTypeMap | None = None,
404    ) -> pa.RecordBatch:
405        """Loads a sheet eagerly by index or name.
406
407        For xlsx files, this will be faster and more memory-efficient, as it will use
408        `worksheet_range_ref` under the hood, which returns borrowed types.
409
410        Refer to `load_sheet` for parameter documentation
411        """
412        return self._reader.load_sheet(
413            idx_or_name=idx_or_name,
414            header_row=header_row,
415            column_names=column_names,
416            skip_rows=skip_rows,
417            n_rows=n_rows,
418            schema_sample_rows=schema_sample_rows,
419            dtype_coercion=dtype_coercion,
420            use_columns=use_columns,
421            dtypes=dtypes,
422            eager=True,
423        )
424
425    def load_sheet_by_name(
426        self,
427        name: str,
428        *,
429        header_row: int | None = 0,
430        column_names: list[str] | None = None,
431        skip_rows: int | None = None,
432        n_rows: int | None = None,
433        schema_sample_rows: int | None = 1_000,
434        dtype_coercion: Literal["coerce", "strict"] = "coerce",
435        use_columns: list[str]
436        | list[int]
437        | str
438        | Callable[[ColumnInfoNoDtype], bool]
439        | None = None,
440        dtypes: DType | DTypeMap | None = None,
441    ) -> ExcelSheet:
442        """Loads a sheet by name.
443
444        Refer to `load_sheet` for parameter documentation
445        """
446        return self.load_sheet(
447            name,
448            header_row=header_row,
449            column_names=column_names,
450            skip_rows=skip_rows,
451            n_rows=n_rows,
452            schema_sample_rows=schema_sample_rows,
453            dtype_coercion=dtype_coercion,
454            use_columns=use_columns,
455            dtypes=dtypes,
456        )
457
458    def load_sheet_by_idx(
459        self,
460        idx: int,
461        *,
462        header_row: int | None = 0,
463        column_names: list[str] | None = None,
464        skip_rows: int | None = None,
465        n_rows: int | None = None,
466        schema_sample_rows: int | None = 1_000,
467        dtype_coercion: Literal["coerce", "strict"] = "coerce",
468        use_columns: list[str]
469        | list[int]
470        | str
471        | Callable[[ColumnInfoNoDtype], bool]
472        | None = None,
473        dtypes: DType | DTypeMap | None = None,
474    ) -> ExcelSheet:
475        """Loads a sheet by index.
476
477        Refer to `load_sheet` for parameter documentation
478        """
479        return self.load_sheet(
480            idx,
481            header_row=header_row,
482            column_names=column_names,
483            skip_rows=skip_rows,
484            n_rows=n_rows,
485            schema_sample_rows=schema_sample_rows,
486            dtype_coercion=dtype_coercion,
487            use_columns=use_columns,
488            dtypes=dtypes,
489        )
490
491    def __repr__(self) -> str:
492        return self._reader.__repr__()

A class representing an open Excel file and allowing to read its sheets

ExcelReader(reader: _ExcelReader) View Source

197    def __init__(self, reader: _ExcelReader) -> None:
198        self._reader = reader

sheet_names: list[str] View Source

200    @property
201    def sheet_names(self) -> list[str]:
202        """The list of sheet names"""
203        return self._reader.sheet_names

The list of sheet names

def load_sheet( self, idx_or_name: int | str, *, header_row: int | None = 0, column_names: list[str] | None = None, skip_rows: int | None = None, n_rows: int | None = None, schema_sample_rows: int | None = 1000, dtype_coercion: Literal['coerce', 'strict'] = 'coerce', use_columns: Union[list[str], list[int], str, Callable[[ColumnInfoNoDtype], bool], NoneType] = None, dtypes: Union[Literal['null', 'int', 'float', 'string', 'boolean', 'datetime', 'date', 'duration'], dict[str | int, Literal['null', 'int', 'float', 'string', 'boolean', 'datetime', 'date', 'duration']], NoneType] = None) -> ExcelSheet: View Source

205    def load_sheet(
206        self,
207        idx_or_name: int | str,
208        *,
209        header_row: int | None = 0,
210        column_names: list[str] | None = None,
211        skip_rows: int | None = None,
212        n_rows: int | None = None,
213        schema_sample_rows: int | None = 1_000,
214        dtype_coercion: Literal["coerce", "strict"] = "coerce",
215        use_columns: list[str]
216        | list[int]
217        | str
218        | Callable[[ColumnInfoNoDtype], bool]
219        | None = None,
220        dtypes: DType | DTypeMap | None = None,
221    ) -> ExcelSheet:
222        """Loads a sheet lazily by index or name.
223
224        :param idx_or_name: The index (starting at 0) or the name of the sheet to load.
225        :param header_row: The index of the row containing the column labels, default index is 0.
226                           If `None`, the sheet does not have any column labels.
227                           Any rows before the `header_row` will be automatically skipped.
228        :param column_names: Overrides headers found in the document.
229                             If `column_names` is used, `header_row` will be ignored.
230        :param n_rows: Specifies how many rows should be loaded.
231                       If `None`, all rows are loaded
232        :param skip_rows: Specifies how many rows should be skipped after the `header_row`.
233                          Any rows before the `header_row` are automatically skipped.
234                          If `header_row` is `None`:
235                            - if `skip_rows` is `None` (default): it skips all empty rows
236                            at the beginning of the sheet.
237                            - if `skip_rows` is a number, it skips the specified number
238                            of rows from the start of the sheet.
239        :param schema_sample_rows: Specifies how many rows should be used to determine
240                                   the dtype of a column. Cannot be 0. A specific dtype can be
241                                   enforced for some or all columns through the `dtypes` parameter.
242                                   If `None`, all rows will be used.
243        :param dtype_coercion: Specifies how type coercion should behave. `coerce` (the default)
244                               will try to coerce different dtypes in a column to the same one,
245                               whereas `strict` will raise an error in case a column contains
246                               several dtypes. Note that this only applies to columns whose dtype
247                               is guessed, i.e. not specified via `dtypes`.
248        :param use_columns: Specifies the columns to use. Can either be:
249                            - `None` to select all columns
250                            - A list of strings and ints, the column names and/or indices
251                              (starting at 0)
252                            - A string, a comma separated list of Excel column letters and column
253                              ranges (e.g. `“A:E”` or `“A,C,E:F”`, which would result in
254                              `A,B,C,D,E` and `A,C,E,F`)
255                            - A callable, a function that takes a column and returns a boolean
256                              indicating whether the column should be used
257        :param dtypes: An optional dtype (for all columns)
258                       or dict of dtypes with keys as column indices or names.
259        """
260        return ExcelSheet(
261            self._reader.load_sheet(
262                idx_or_name=idx_or_name,
263                header_row=header_row,
264                column_names=column_names,
265                skip_rows=skip_rows,
266                n_rows=n_rows,
267                schema_sample_rows=schema_sample_rows,
268                dtype_coercion=dtype_coercion,
269                use_columns=use_columns,
270                dtypes=dtypes,
271                eager=False,
272            )
273        )

Loads a sheet lazily by index or name.

Parameters

idx_or_name: The index (starting at 0) or the name of the sheet to load.
header_row: The index of the row containing the column labels, default index is 0. If None, the sheet does not have any column labels. Any rows before the header_row will be automatically skipped.
column_names: Overrides headers found in the document. If column_names is used, header_row will be ignored.
n_rows: Specifies how many rows should be loaded. If None, all rows are loaded
skip_rows: Specifies how many rows should be skipped after the header_row. Any rows before the header_row are automatically skipped. If header_row is None:
- if skip_rows is None (default): it skips all empty rows at the beginning of the sheet.
- if skip_rows is a number, it skips the specified number of rows from the start of the sheet.
schema_sample_rows: Specifies how many rows should be used to determine the dtype of a column. Cannot be 0. A specific dtype can be enforced for some or all columns through the dtypes parameter. If None, all rows will be used.
dtype_coercion: Specifies how type coercion should behave. coerce (the default) will try to coerce different dtypes in a column to the same one, whereas strict will raise an error in case a column contains several dtypes. Note that this only applies to columns whose dtype is guessed, i.e. not specified via dtypes.
use_columns: Specifies the columns to use. Can either be:
- None to select all columns
- A list of strings and ints, the column names and/or indices (starting at 0)
- A string, a comma separated list of Excel column letters and column ranges (e.g. “A:E” or “A,C,E:F”, which would result in A,B,C,D,E and A,C,E,F)
- A callable, a function that takes a column and returns a boolean indicating whether the column should be used
dtypes: An optional dtype (for all columns) or dict of dtypes with keys as column indices or names.

def table_names(self, sheet_name: str | None = None) -> list[str]: View Source

275    def table_names(self, sheet_name: str | None = None) -> list[str]:
276        """The list of table names.
277
278        Will return an empty list if no tables are found.
279
280        :param sheet_name: If given, will limit the list to the given sheet, will be faster
281        too.
282        """
283        return self._reader.table_names(sheet_name)

The list of table names.

Will return an empty list if no tables are found.

Parameters

sheet_name: If given, will limit the list to the given sheet, will be faster too.

def load_table( self, name: str, *, header_row: int | None = None, column_names: list[str] | None = None, skip_rows: int = 0, n_rows: int | None = None, schema_sample_rows: int | None = 1000, dtype_coercion: Literal['coerce', 'strict'] = 'coerce', use_columns: Union[list[str], list[int], str, Callable[[ColumnInfoNoDtype], bool], NoneType] = None, dtypes: Union[Literal['null', 'int', 'float', 'string', 'boolean', 'datetime', 'date', 'duration'], dict[str | int, Literal['null', 'int', 'float', 'string', 'boolean', 'datetime', 'date', 'duration']], NoneType] = None, eager: bool = False) -> fastexcel.ExcelTable | pyarrow.lib.RecordBatch: View Source

323    def load_table(
324        self,
325        name: str,
326        *,
327        header_row: int | None = None,
328        column_names: list[str] | None = None,
329        skip_rows: int = 0,
330        n_rows: int | None = None,
331        schema_sample_rows: int | None = 1_000,
332        dtype_coercion: Literal["coerce", "strict"] = "coerce",
333        use_columns: list[str]
334        | list[int]
335        | str
336        | Callable[[ColumnInfoNoDtype], bool]
337        | None = None,
338        dtypes: DType | DTypeMap | None = None,
339        eager: bool = False,
340    ) -> ExcelTable | pa.RecordBatch:
341        """Loads a table by name.
342
343        :param name: The name of the table to load.
344        :param header_row: The index of the row containing the column labels.
345                           If `None`, the table's column names will be used.
346                           Any rows before the `header_row` will be automatically skipped.
347        :param column_names: Overrides headers found in the document.
348                             If `column_names` is used, `header_row` will be ignored.
349        :param n_rows: Specifies how many rows should be loaded.
350                       If `None`, all rows are loaded
351        :param skip_rows: Specifies how many rows should be skipped after the `header_row`.
352                          Any rows before the `header_row` are automatically skipped.
353                          If `header_row` is `None`, it skips the number of rows from the
354                          start of the sheet.
355        :param schema_sample_rows: Specifies how many rows should be used to determine
356                                   the dtype of a column. Cannot be 0. A specific dtype can be
357                                   enforced for some or all columns through the `dtypes` parameter.
358                                   If `None`, all rows will be used.
359        :param dtype_coercion: Specifies how type coercion should behave. `coerce` (the default)
360                               will try to coerce different dtypes in a column to the same one,
361                               whereas `strict` will raise an error in case a column contains
362                               several dtypes. Note that this only applies to columns whose dtype
363                               is guessed, i.e. not specified via `dtypes`.
364        :param use_columns: Specifies the columns to use. Can either be:
365                            - `None` to select all columns
366                            - A list of strings and ints, the column names and/or indices
367                              (starting at 0)
368                            - A string, a comma separated list of Excel column letters and column
369                              ranges (e.g. `“A:E”` or `“A,C,E:F”`, which would result in
370                              `A,B,C,D,E` and `A,C,E,F`)
371                            - A callable, a function that takes a column and returns a boolean
372                              indicating whether the column should be used
373        :param dtypes: An optional dtype (for all columns)
374                       or dict of dtypes with keys as column indices or names.
375        """
376        output = self._reader.load_table(  # type:ignore[call-overload,misc]
377            name=name,
378            header_row=header_row,
379            column_names=column_names,
380            skip_rows=skip_rows,
381            n_rows=n_rows,
382            schema_sample_rows=schema_sample_rows,
383            dtype_coercion=dtype_coercion,
384            use_columns=use_columns,
385            dtypes=dtypes,
386            eager=eager,
387        )
388        if eager:
389            return output
390        return ExcelTable(output)

Loads a table by name.

Parameters

name: The name of the table to load.
header_row: The index of the row containing the column labels. If None, the table's column names will be used. Any rows before the header_row will be automatically skipped.
column_names: Overrides headers found in the document. If column_names is used, header_row will be ignored.
n_rows: Specifies how many rows should be loaded. If None, all rows are loaded
skip_rows: Specifies how many rows should be skipped after the header_row. Any rows before the header_row are automatically skipped. If header_row is None, it skips the number of rows from the start of the sheet.
schema_sample_rows: Specifies how many rows should be used to determine the dtype of a column. Cannot be 0. A specific dtype can be enforced for some or all columns through the dtypes parameter. If None, all rows will be used.
dtype_coercion: Specifies how type coercion should behave. coerce (the default) will try to coerce different dtypes in a column to the same one, whereas strict will raise an error in case a column contains several dtypes. Note that this only applies to columns whose dtype is guessed, i.e. not specified via dtypes.
use_columns: Specifies the columns to use. Can either be:
- None to select all columns
- A list of strings and ints, the column names and/or indices (starting at 0)
- A string, a comma separated list of Excel column letters and column ranges (e.g. “A:E” or “A,C,E:F”, which would result in A,B,C,D,E and A,C,E,F)
- A callable, a function that takes a column and returns a boolean indicating whether the column should be used
dtypes: An optional dtype (for all columns) or dict of dtypes with keys as column indices or names.

def load_sheet_eager( self, idx_or_name: int | str, *, header_row: int | None = 0, column_names: list[str] | None = None, skip_rows: int | None = None, n_rows: int | None = None, schema_sample_rows: int | None = 1000, dtype_coercion: Literal['coerce', 'strict'] = 'coerce', use_columns: list[str] | list[int] | str | None = None, dtypes: Union[Literal['null', 'int', 'float', 'string', 'boolean', 'datetime', 'date', 'duration'], dict[str | int, Literal['null', 'int', 'float', 'string', 'boolean', 'datetime', 'date', 'duration']], NoneType] = None) -> pyarrow.lib.RecordBatch: View Source

392    def load_sheet_eager(
393        self,
394        idx_or_name: int | str,
395        *,
396        header_row: int | None = 0,
397        column_names: list[str] | None = None,
398        skip_rows: int | None = None,
399        n_rows: int | None = None,
400        schema_sample_rows: int | None = 1_000,
401        dtype_coercion: Literal["coerce", "strict"] = "coerce",
402        use_columns: list[str] | list[int] | str | None = None,
403        dtypes: DType | DTypeMap | None = None,
404    ) -> pa.RecordBatch:
405        """Loads a sheet eagerly by index or name.
406
407        For xlsx files, this will be faster and more memory-efficient, as it will use
408        `worksheet_range_ref` under the hood, which returns borrowed types.
409
410        Refer to `load_sheet` for parameter documentation
411        """
412        return self._reader.load_sheet(
413            idx_or_name=idx_or_name,
414            header_row=header_row,
415            column_names=column_names,
416            skip_rows=skip_rows,
417            n_rows=n_rows,
418            schema_sample_rows=schema_sample_rows,
419            dtype_coercion=dtype_coercion,
420            use_columns=use_columns,
421            dtypes=dtypes,
422            eager=True,
423        )

Loads a sheet eagerly by index or name.

For xlsx files, this will be faster and more memory-efficient, as it will use worksheet_range_ref under the hood, which returns borrowed types.

Refer to load_sheet for parameter documentation

def load_sheet_by_name( self, name: str, *, header_row: int | None = 0, column_names: list[str] | None = None, skip_rows: int | None = None, n_rows: int | None = None, schema_sample_rows: int | None = 1000, dtype_coercion: Literal['coerce', 'strict'] = 'coerce', use_columns: Union[list[str], list[int], str, Callable[[ColumnInfoNoDtype], bool], NoneType] = None, dtypes: Union[Literal['null', 'int', 'float', 'string', 'boolean', 'datetime', 'date', 'duration'], dict[str | int, Literal['null', 'int', 'float', 'string', 'boolean', 'datetime', 'date', 'duration']], NoneType] = None) -> ExcelSheet: View Source

425    def load_sheet_by_name(
426        self,
427        name: str,
428        *,
429        header_row: int | None = 0,
430        column_names: list[str] | None = None,
431        skip_rows: int | None = None,
432        n_rows: int | None = None,
433        schema_sample_rows: int | None = 1_000,
434        dtype_coercion: Literal["coerce", "strict"] = "coerce",
435        use_columns: list[str]
436        | list[int]
437        | str
438        | Callable[[ColumnInfoNoDtype], bool]
439        | None = None,
440        dtypes: DType | DTypeMap | None = None,
441    ) -> ExcelSheet:
442        """Loads a sheet by name.
443
444        Refer to `load_sheet` for parameter documentation
445        """
446        return self.load_sheet(
447            name,
448            header_row=header_row,
449            column_names=column_names,
450            skip_rows=skip_rows,
451            n_rows=n_rows,
452            schema_sample_rows=schema_sample_rows,
453            dtype_coercion=dtype_coercion,
454            use_columns=use_columns,
455            dtypes=dtypes,
456        )

Loads a sheet by name.

Refer to load_sheet for parameter documentation

def load_sheet_by_idx( self, idx: int, *, header_row: int | None = 0, column_names: list[str] | None = None, skip_rows: int | None = None, n_rows: int | None = None, schema_sample_rows: int | None = 1000, dtype_coercion: Literal['coerce', 'strict'] = 'coerce', use_columns: Union[list[str], list[int], str, Callable[[ColumnInfoNoDtype], bool], NoneType] = None, dtypes: Union[Literal['null', 'int', 'float', 'string', 'boolean', 'datetime', 'date', 'duration'], dict[str | int, Literal['null', 'int', 'float', 'string', 'boolean', 'datetime', 'date', 'duration']], NoneType] = None) -> ExcelSheet: View Source

458    def load_sheet_by_idx(
459        self,
460        idx: int,
461        *,
462        header_row: int | None = 0,
463        column_names: list[str] | None = None,
464        skip_rows: int | None = None,
465        n_rows: int | None = None,
466        schema_sample_rows: int | None = 1_000,
467        dtype_coercion: Literal["coerce", "strict"] = "coerce",
468        use_columns: list[str]
469        | list[int]
470        | str
471        | Callable[[ColumnInfoNoDtype], bool]
472        | None = None,
473        dtypes: DType | DTypeMap | None = None,
474    ) -> ExcelSheet:
475        """Loads a sheet by index.
476
477        Refer to `load_sheet` for parameter documentation
478        """
479        return self.load_sheet(
480            idx,
481            header_row=header_row,
482            column_names=column_names,
483            skip_rows=skip_rows,
484            n_rows=n_rows,
485            schema_sample_rows=schema_sample_rows,
486            dtype_coercion=dtype_coercion,
487            use_columns=use_columns,
488            dtypes=dtypes,
489        )

Loads a sheet by index.

Refer to load_sheet for parameter documentation

DTypeFrom: TypeAlias = Literal['provided_by_index', 'provided_by_name', 'guessed']

ColumnNameFrom: TypeAlias = Literal['provided', 'looked_up', 'generated']

class ColumnInfo:

This class provides information about a single column in a sheet

dtype

fastexcel.DType. The dtype of the column

dtype_from

fastexcel.DTypeFrom. How the dtype of the column was determined.

One of three possible values:

"provided_by_index": The dtype was specified via the column index
"provided_by_name": The dtype was specified via the column name
"guessed": The dtype was determined from the content of the column

column_name_from

fastexcel.ColumnNameFrom. How the name of the column was determined.

One of three possible values:

"provided": The column name was provided via the use_columns parameter
"looked_up": The column name was looked up from the data found in the sheet
"generated": The column name was generated from the column index, either because header_row was None, or because it could not be looked up

name

str. The name of the column

index

int. The index of the column

class FastExcelError(builtins.Exception):

The base class for all fastexcel errors

class CannotRetrieveCellDataError(fastexcel.FastExcelError):

Data for a given cell cannot be retrieved

class CalamineCellError(fastexcel.FastExcelError):

calamine returned an error regarding the content of the cell

class CalamineError(fastexcel.FastExcelError):

Generic calamine error

class SheetNotFoundError(fastexcel.FastExcelError):

Sheet was not found

class ColumnNotFoundError(fastexcel.FastExcelError):

Column was not found

class ArrowError(fastexcel.FastExcelError):

Generic arrow error

class InvalidParametersError(fastexcel.FastExcelError):

Provided parameters are invalid

class UnsupportedColumnTypeCombinationError(fastexcel.FastExcelError):

Column contains an unsupported type combination