Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1from __future__ import annotations
3from collections.abc import (
4 Callable,
5 Hashable,
6 Iterable,
7 Mapping,
8 Sequence,
9)
10import datetime
11from decimal import Decimal
12from functools import partial
13import os
14import sys
15from typing import (
16 IO,
17 TYPE_CHECKING,
18 Any,
19 Generic,
20 Literal,
21 Self,
22 TypeVar,
23 cast,
24 overload,
25)
26import warnings
27import zipfile
29from pandas._config.config import _global_config as config
31from pandas._libs import lib
32from pandas.compat._optional import import_optional_dependency
33from pandas.errors import (
34 EmptyDataError,
35 Pandas4Warning,
36)
37from pandas.util._decorators import (
38 set_module,
39)
40from pandas.util._exceptions import find_stack_level
41from pandas.util._validators import check_dtype_backend
43from pandas.core.dtypes.common import (
44 is_bool,
45 is_decimal,
46 is_float,
47 is_integer,
48 is_list_like,
49)
51from pandas.core.frame import DataFrame
53from pandas.io.common import (
54 IOHandles,
55 get_handle,
56 stringify_path,
57 validate_header_arg,
58)
59from pandas.io.excel._util import (
60 fill_mi_header,
61 get_default_engine,
62 get_writer,
63 maybe_convert_usecols,
64 pop_header_name,
65)
66from pandas.io.parsers import TextParser
67from pandas.io.parsers.readers import validate_integer
69if TYPE_CHECKING:
70 from types import TracebackType
72 from pandas._typing import (
73 DtypeArg,
74 DtypeBackend,
75 ExcelWriterIfSheetExists,
76 FilePath,
77 HashableT,
78 IntStrT,
79 ReadBuffer,
80 SequenceNotStr,
81 StorageOptions,
82 WriteExcelBuffer,
83 )
85EXCEL_ROWS_MAX = 1_048_576
88@overload
89def read_excel(
90 io,
91 # sheet name is str or int -> DataFrame
92 sheet_name: str | int = ...,
93 *,
94 header: int | Sequence[int] | None = ...,
95 names: SequenceNotStr[Hashable] | range | None = ...,
96 index_col: int | str | Sequence[int] | None = ...,
97 usecols: int
98 | str
99 | Sequence[int]
100 | Sequence[str]
101 | Callable[[HashableT], bool]
102 | None = ...,
103 dtype: DtypeArg | None = ...,
104 engine: Literal["xlrd", "openpyxl", "odf", "pyxlsb", "calamine"] | None = ...,
105 converters: dict[str, Callable] | dict[int, Callable] | None = ...,
106 true_values: Iterable[Hashable] | None = ...,
107 false_values: Iterable[Hashable] | None = ...,
108 skiprows: Sequence[int] | int | Callable[[int], object] | None = ...,
109 nrows: int | None = ...,
110 na_values=...,
111 keep_default_na: bool = ...,
112 na_filter: bool = ...,
113 verbose: bool = ...,
114 parse_dates: list | dict | bool = ...,
115 date_format: dict[Hashable, str] | str | None = ...,
116 thousands: str | None = ...,
117 decimal: str = ...,
118 comment: str | None = ...,
119 skipfooter: int = ...,
120 storage_options: StorageOptions = ...,
121 dtype_backend: DtypeBackend | lib.NoDefault = ...,
122) -> DataFrame: ...
125@overload
126def read_excel(
127 io,
128 # sheet name is list or None -> dict[IntStrT, DataFrame]
129 sheet_name: list[IntStrT] | None,
130 *,
131 header: int | Sequence[int] | None = ...,
132 names: SequenceNotStr[Hashable] | range | None = ...,
133 index_col: int | str | Sequence[int] | None = ...,
134 usecols: int
135 | str
136 | Sequence[int]
137 | Sequence[str]
138 | Callable[[HashableT], bool]
139 | None = ...,
140 dtype: DtypeArg | None = ...,
141 engine: Literal["xlrd", "openpyxl", "odf", "pyxlsb", "calamine"] | None = ...,
142 converters: dict[str, Callable] | dict[int, Callable] | None = ...,
143 true_values: Iterable[Hashable] | None = ...,
144 false_values: Iterable[Hashable] | None = ...,
145 skiprows: Sequence[int] | int | Callable[[int], object] | None = ...,
146 nrows: int | None = ...,
147 na_values=...,
148 keep_default_na: bool = ...,
149 na_filter: bool = ...,
150 verbose: bool = ...,
151 parse_dates: list | dict | bool = ...,
152 date_format: dict[Hashable, str] | str | None = ...,
153 thousands: str | None = ...,
154 decimal: str = ...,
155 comment: str | None = ...,
156 skipfooter: int = ...,
157 storage_options: StorageOptions = ...,
158 dtype_backend: DtypeBackend | lib.NoDefault = ...,
159) -> dict[IntStrT, DataFrame]: ...
162@set_module("pandas")
163def read_excel(
164 io,
165 sheet_name: str | int | list[IntStrT] | None = 0,
166 *,
167 header: int | Sequence[int] | None = 0,
168 names: SequenceNotStr[Hashable] | range | None = None,
169 index_col: int | str | Sequence[int] | None = None,
170 usecols: int
171 | str
172 | Sequence[int]
173 | Sequence[str]
174 | Callable[[HashableT], bool]
175 | None = None,
176 dtype: DtypeArg | None = None,
177 engine: Literal["xlrd", "openpyxl", "odf", "pyxlsb", "calamine"] | None = None,
178 converters: dict[str, Callable] | dict[int, Callable] | None = None,
179 true_values: Iterable[Hashable] | None = None,
180 false_values: Iterable[Hashable] | None = None,
181 skiprows: Sequence[int] | int | Callable[[int], object] | None = None,
182 nrows: int | None = None,
183 na_values=None,
184 keep_default_na: bool = True,
185 na_filter: bool = True,
186 verbose: bool = False,
187 parse_dates: list | dict | bool = False,
188 date_format: dict[Hashable, str] | str | None = None,
189 thousands: str | None = None,
190 decimal: str = ".",
191 comment: str | None = None,
192 skipfooter: int = 0,
193 storage_options: StorageOptions | None = None,
194 dtype_backend: DtypeBackend | lib.NoDefault = lib.no_default,
195 engine_kwargs: dict | None = None,
196) -> DataFrame | dict[IntStrT, DataFrame]:
197 """
198 Read an Excel file into a ``DataFrame``.
200 Supports `xls`, `xlsx`, `xlsm`, `xlsb`, `odf`, `ods` and `odt` file extensions
201 read from a local filesystem or URL. Supports an option to read
202 a single sheet or a list of sheets.
204 This function requires an external library depending on the
205 file format; see the ``engine`` parameter below.
207 Parameters
208 ----------
209 io : str, ExcelFile, workbook object, path object, or file-like object
210 Any valid string path is acceptable. The string could be a URL. Valid
211 URL schemes include http, ftp, s3, and file. For file URLs, a host is
212 expected. A local file could be: ``file://localhost/path/to/table.xlsx``.
214 Certain URL schemes may require additional packages. For example, S3
215 URLs require the ``s3fs`` library. See
216 :ref:`install.optional_dependencies` for a full list.
218 If you want to pass in a path object, pandas accepts any ``os.PathLike``.
220 By file-like object, we refer to objects with a ``read()`` method,
221 such as a file handle (e.g. via builtin ``open`` function)
222 or ``StringIO``.
224 A workbook object from a supported engine's library, such as an
225 ``openpyxl.Workbook``, is read with that engine.
227 sheet_name : str, int, list, or None, default 0
228 Strings are used for sheet names. Integers are used in zero-indexed
229 sheet positions (chart sheets do not count as a sheet position).
230 Lists of strings/integers are used to request multiple sheets.
231 When ``None``, will return a dictionary containing DataFrames for each sheet.
233 Available cases:
235 * Defaults to ``0``: 1st sheet as a `DataFrame`
236 * ``1``: 2nd sheet as a `DataFrame`
237 * ``"Sheet1"``: Load sheet with name "Sheet1"
238 * ``[0, 1, "Sheet5"]``: Load first, second and sheet named "Sheet5"
239 as a dict of `DataFrame`
240 * ``None``: Returns a dictionary containing DataFrames for each sheet.
242 header : int, list of int, default 0
243 Row (0-indexed) to use for the column labels of the parsed
244 DataFrame. If a list of integers is passed those row positions will
245 be combined into a ``MultiIndex``. Use None if there is no header.
246 names : array-like, default None
247 List of column names to use. If file contains no header row,
248 then you should explicitly pass header=None.
249 index_col : int, str, list of int, default None
250 Column (0-indexed) to use as the row labels of the DataFrame.
251 Pass None if there is no such column. If a list is passed,
252 those columns will be combined into a ``MultiIndex``. If a
253 subset of data is selected with ``usecols``, index_col
254 is based on the subset.
256 Missing values will be forward filled to allow roundtripping with
257 ``to_excel`` for ``merged_cells=True``. To avoid forward filling the
258 missing values use ``set_index`` after reading the data instead of
259 ``index_col``.
261 When ``header`` is a list of two or more rows (``MultiIndex``
262 columns), the row after the last header row is expected to hold the
263 index name(s) — blank if the index is unnamed — as written by
264 ``to_excel``. A file lacking this separator row can lose its first
265 data row to the index name(s); see :ref:`the user guide
266 <io.excel.reading_multiindex>` for details.
267 usecols : str, list-like, or callable, default None
268 * If None, then parse all columns.
269 * If str, then indicates comma separated list of Excel column letters
270 and column ranges (e.g. "A:E" or "A,C,E:F"). Ranges are inclusive of
271 both sides.
272 * If list of int, then indicates list of column numbers to be parsed
273 (0-indexed).
274 * If list of string, then indicates list of column names to be parsed.
275 * If callable, then evaluate each column name against it and parse the
276 column if the callable returns ``True``.
278 Returns a subset of the columns according to behavior above.
279 Element order is ignored, so ``usecols=[0, 1]`` is the same as ``[1, 0]``.
280 To instantiate a :class:`~pandas.DataFrame` with element order preserved
281 use ``pd.read_excel(path, usecols=['foo', 'bar'])[['foo', 'bar']]`` for
282 columns in ``['foo', 'bar']`` order or
283 ``pd.read_excel(path, usecols=['foo', 'bar'])[['bar', 'foo']]`` for
284 ``['bar', 'foo']`` order.
285 dtype : Type name or dict of column -> type, default None
286 Data type for data or columns. E.g. {'a': np.float64, 'b': np.int32}
287 Use ``object`` to preserve data as stored in Excel and not interpret dtype,
288 which will necessarily result in ``object`` dtype.
289 Specifying a ``dtype`` does not change how missing values are parsed;
290 values recognized as missing (see ``na_values`` and ``keep_default_na``)
291 are still read as ``NaN`` (or the dtype's NA value) rather than cast to
292 the requested ``dtype``.
293 If converters are specified, they will be applied INSTEAD
294 of dtype conversion.
295 If you use ``None``, it will infer the dtype of each column based on the data.
296 engine : {'openpyxl', 'calamine', 'odf', 'pyxlsb', 'xlrd'}, default None
297 Engine compatibility :
299 - ``openpyxl`` supports newer Excel file formats.
300 - ``calamine`` supports Excel (.xls, .xlsx, .xlsm, .xlsb)
301 and OpenDocument (.ods) file formats.
302 - ``odf`` supports OpenDocument file formats (.odf, .ods, .odt).
303 - ``pyxlsb`` supports Binary Excel files.
304 - ``xlrd`` supports old-style Excel files (.xls).
306 When ``engine=None``, the following logic will be used to determine the engine:
308 - If ``io`` is a workbook object, the engine that created it will be used.
309 - Otherwise if ``path_or_buffer`` is an OpenDocument format (.odf, .ods, .odt),
310 then `odf <https://pypi.org/project/odfpy/>`_ will be used.
311 - Otherwise if ``path_or_buffer`` is an xls format, ``xlrd`` will be used.
312 - Otherwise if ``path_or_buffer`` is in xlsb format, ``pyxlsb`` will be used.
313 - Otherwise ``openpyxl`` will be used.
315 .. deprecated:: 3.1.0
316 For xlsx/xlsm files the default engine will change from
317 ``openpyxl`` to ``calamine`` in a future version. Pass ``engine``
318 explicitly, or set the ``io.excel.xlsx.reader`` option (xlsm
319 files are format-detected as xlsx), to keep the current behavior.
321 converters : dict, default None
322 Dict of functions for converting values in certain columns. Keys can
323 either be integers or column labels, values are functions that take one
324 input argument, the Excel cell content, and return the transformed
325 content.
326 true_values : list, default None
327 Values to consider as True.
328 false_values : list, default None
329 Values to consider as False.
330 skiprows : list-like, int, or callable, optional
331 Line numbers to skip (0-indexed) or number of lines to skip (int) at the
332 start of the file. If callable, the callable function will be evaluated
333 against the row indices, returning True if the row should be skipped and
334 False otherwise. An example of a valid callable argument would be ``lambda
335 x: x in [0, 2]``.
336 nrows : int, default None
337 Number of rows to parse. Does not include header rows.
338 na_values : scalar, str, list-like, or dict, default None
339 Additional strings to recognize as NA/NaN. If dict passed, specific
340 per-column NA values. By default the following values are interpreted
341 as NaN: '', '#N/A', '#N/A N/A', '#NA', '-1.#IND', '-1.#QNAN', '-NaN', '-nan',
342 '1.#IND', '1.#QNAN', '<NA>', 'N/A', 'NA', 'NULL', 'NaN', 'None',
343 'n/a', 'nan', 'null'.
344 keep_default_na : bool, default True
345 Whether or not to include the default NaN values when parsing the data.
346 Depending on whether ``na_values`` is passed in, the behavior is as follows:
348 * If ``keep_default_na`` is True, and ``na_values`` are specified,
349 ``na_values`` is appended to the default NaN values used for parsing.
350 * If ``keep_default_na`` is True, and ``na_values`` are not specified, only
351 the default NaN values are used for parsing.
352 * If ``keep_default_na`` is False, and ``na_values`` are specified, only
353 the NaN values specified ``na_values`` are used for parsing.
354 * If ``keep_default_na`` is False, and ``na_values`` are not specified, no
355 strings will be parsed as NaN.
357 Note that if `na_filter` is passed in as False, the ``keep_default_na`` and
358 ``na_values`` parameters will be ignored.
359 na_filter : bool, default True
360 Detect missing value markers (empty strings and the value of na_values). In
361 data without any NAs, passing ``na_filter=False`` can improve the
362 performance of reading a large file.
363 verbose : bool, default False
364 Indicate number of NA values placed in non-numeric columns.
365 parse_dates : bool, list-like, or dict, default False
366 The behavior is as follows:
368 * ``bool``. If True -> try parsing the index.
369 * ``list`` of int or names. e.g. If [1, 2, 3] -> try parsing columns 1, 2, 3
370 each as a separate date column.
371 * ``list`` of lists. e.g. If [[1, 3]] -> combine columns 1 and 3 and parse as
372 a single date column.
373 * ``dict``, e.g. {'foo' : [1, 3]} -> parse columns 1, 3 as date and call
374 result 'foo'
376 If a column or index contains an unparsable date, the entire column or
377 index will be returned unaltered as an object data type. If you don`t want to
378 parse some cells as date just change their type in Excel to "Text".
379 For non-standard datetime parsing, use ``pd.to_datetime`` after
380 ``pd.read_excel``.
382 Note: A fast-path exists for iso8601-formatted dates.
383 date_format : str or dict of column -> format, default ``None``
384 If used in conjunction with ``parse_dates``, will parse dates according to this
385 format. For anything more complex,
386 please read in as ``object`` and then apply :func:`to_datetime` as-needed.
388 .. versionadded:: 2.0.0
390 thousands : str, default None
391 Thousands separator for parsing string columns to numeric. Note that
392 this parameter is only necessary for columns stored as TEXT in Excel,
393 any numeric columns will automatically be parsed, regardless of display
394 format.
395 decimal : str, default '.'
396 Character to recognize as decimal point for parsing string columns to numeric.
397 Note that this parameter is only necessary for columns stored as TEXT in Excel,
398 any numeric columns will automatically be parsed, regardless of display
399 format.(e.g. use ',' for European data).
400 comment : str, default None
401 Comments out remainder of line. Pass a character or characters to this
402 argument to indicate comments in the input file. Any data between the
403 comment string and the end of the current line is ignored.
404 skipfooter : int, default 0
405 Rows at the end to skip (0-indexed).
406 storage_options : dict, optional
407 Extra options that make sense for a particular storage connection, e.g.
408 host, port, username, password, etc. For HTTP(S) URLs the key-value pairs
409 are forwarded to ``urllib.request.Request`` as header options. For other
410 URLs (e.g. starting with "s3://", and "gcs://") the key-value pairs are
411 forwarded to ``fsspec.open``. Please see ``fsspec`` and ``urllib`` for more
412 details, and for more examples on storage options refer `here
413 <https://pandas.pydata.org/docs/user_guide/io.html?
414 highlight=storage_options#reading-writing-remote-files>`_.
416 dtype_backend : {'numpy_nullable', 'pyarrow'}
417 Back-end data type applied to the resultant :class:`DataFrame`
418 (still experimental). If not specified, the default behavior
419 is to not use nullable data types. If specified, the behavior
420 is as follows:
422 * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame`
423 * ``"pyarrow"``: returns pyarrow-backed nullable :class:`ArrowDtype`
424 :class:`DataFrame`
426 .. versionadded:: 2.0
428 engine_kwargs : dict, optional
429 Arbitrary keyword arguments passed to excel engine.
431 Returns
432 -------
433 DataFrame or dict of DataFrames
434 DataFrame from the passed in Excel file. See notes in sheet_name
435 argument for more information on when a dict of DataFrames is returned.
437 See Also
438 --------
439 DataFrame.to_excel : Write DataFrame to an Excel file.
440 DataFrame.to_csv : Write DataFrame to a comma-separated values (csv) file.
441 read_csv : Read a comma-separated values (csv) file into DataFrame.
442 read_fwf : Read a table of fixed-width formatted lines into DataFrame.
444 Notes
445 -----
446 For specific information on the methods used for each Excel engine, refer to the
447 pandas
448 :ref:`user guide <io.excel_reader>`
450 Examples
451 --------
452 The file can be read using the file name as string or an open file object:
454 >>> pd.read_excel("tmp.xlsx", index_col=0) # doctest: +SKIP
455 Name Value
456 0 string1 1
457 1 string2 2
458 2 #Comment 3
460 >>> pd.read_excel(open("tmp.xlsx", "rb"), sheet_name="Sheet3") # doctest: +SKIP
461 Unnamed: 0 Name Value
462 0 0 string1 1
463 1 1 string2 2
464 2 2 #Comment 3
466 Index and header can be specified via the `index_col` and `header` arguments
468 >>> pd.read_excel("tmp.xlsx", index_col=None, header=None) # doctest: +SKIP
469 0 1 2
470 0 NaN Name Value
471 1 0.0 string1 1
472 2 1.0 string2 2
473 3 2.0 #Comment 3
475 Column types are inferred but can be explicitly specified
477 >>> pd.read_excel(
478 ... "tmp.xlsx", index_col=0, dtype={"Name": str, "Value": float}
479 ... ) # doctest: +SKIP
480 Name Value
481 0 string1 1.0
482 1 string2 2.0
483 2 #Comment 3.0
485 True, False, and NA values, and thousands separators have defaults,
486 but can be explicitly specified, too. Supply the values you would like
487 as strings or lists of strings!
489 >>> pd.read_excel(
490 ... "tmp.xlsx", index_col=0, na_values=["string1", "string2"]
491 ... ) # doctest: +SKIP
492 Name Value
493 0 NaN 1
494 1 NaN 2
495 2 #Comment 3
497 Comment lines in the excel input file can be skipped using the
498 ``comment`` kwarg.
500 >>> pd.read_excel("tmp.xlsx", index_col=0, comment="#") # doctest: +SKIP
501 Name Value
502 0 string1 1.0
503 1 string2 2.0
504 2 None NaN
505 """
506 check_dtype_backend(dtype_backend)
507 should_close = False
508 if engine_kwargs is None:
509 engine_kwargs = {}
511 if not isinstance(io, ExcelFile):
512 should_close = True
513 io = ExcelFile(
514 io,
515 storage_options=storage_options,
516 engine=engine,
517 engine_kwargs=engine_kwargs,
518 )
519 elif engine and engine != io.engine:
520 raise ValueError(
521 "Engine should not be specified when passing "
522 "an ExcelFile - ExcelFile already has the engine set"
523 )
525 try:
526 data = io._reader.parse(
527 sheet_name=sheet_name,
528 header=header,
529 names=names,
530 index_col=index_col,
531 usecols=usecols,
532 dtype=dtype,
533 converters=converters,
534 true_values=true_values,
535 false_values=false_values,
536 skiprows=skiprows,
537 nrows=nrows,
538 na_values=na_values,
539 keep_default_na=keep_default_na,
540 na_filter=na_filter,
541 verbose=verbose,
542 parse_dates=parse_dates,
543 date_format=date_format,
544 thousands=thousands,
545 decimal=decimal,
546 comment=comment,
547 skipfooter=skipfooter,
548 dtype_backend=dtype_backend,
549 )
550 finally:
551 # make sure to close opened file handles
552 if should_close:
553 io.close()
554 return data
557_WorkbookT = TypeVar("_WorkbookT")
560class BaseExcelReader(Generic[_WorkbookT]):
561 book: _WorkbookT
563 def __init__(
564 self,
565 filepath_or_buffer,
566 storage_options: StorageOptions | None = None,
567 engine_kwargs: dict | None = None,
568 ) -> None:
569 if engine_kwargs is None:
570 engine_kwargs = {}
572 self.handles = IOHandles(
573 handle=filepath_or_buffer, compression={"method": None}
574 )
575 if not isinstance(filepath_or_buffer, (ExcelFile, self._workbook_class)):
576 self.handles = get_handle(
577 filepath_or_buffer, "rb", storage_options=storage_options, is_text=False
578 )
580 if isinstance(self.handles.handle, self._workbook_class):
581 self.book = self.handles.handle
582 elif hasattr(self.handles.handle, "read"):
583 # N.B. xlrd.Book has a read attribute too
584 self.handles.handle.seek(0)
585 try:
586 self.book = self.load_workbook(self.handles.handle, engine_kwargs)
587 except Exception:
588 self.close()
589 raise
590 else:
591 raise ValueError(
592 "Must explicitly set engine if not passing in buffer or path for io."
593 )
595 @property
596 def _workbook_class(self) -> type[_WorkbookT]:
597 raise NotImplementedError
599 def load_workbook(self, filepath_or_buffer, engine_kwargs) -> _WorkbookT:
600 raise NotImplementedError
602 def close(self) -> None:
603 if hasattr(self, "book"):
604 if hasattr(self.book, "close"):
605 # pyxlsb: opens a TemporaryFile
606 # openpyxl: https://stackoverflow.com/questions/31416842/
607 # openpyxl-does-not-close-excel-workbook-in-read-only-mode
608 self.book.close()
609 elif hasattr(self.book, "release_resources"):
610 # xlrd
611 # https://github.com/python-excel/xlrd/blob/2.0.1/xlrd/book.py#L548
612 self.book.release_resources()
613 self.handles.close()
615 @property
616 def sheet_names(self) -> list[str]:
617 raise NotImplementedError
619 def get_sheet_by_name(self, name: str):
620 raise NotImplementedError
622 def get_sheet_by_index(self, index: int):
623 raise NotImplementedError
625 def get_sheet_data(self, sheet, rows: int | None = None):
626 raise NotImplementedError
628 def raise_if_bad_sheet_by_index(self, index: int) -> None:
629 n_sheets = len(self.sheet_names)
630 if index >= n_sheets:
631 raise ValueError(
632 f"Worksheet index {index} is invalid, {n_sheets} worksheets found"
633 )
635 def raise_if_bad_sheet_by_name(self, name: str) -> None:
636 if name not in self.sheet_names:
637 raise ValueError(f"Worksheet named '{name}' not found")
639 def _check_skiprows_func(
640 self,
641 skiprows: Callable,
642 rows_to_use: int,
643 ) -> int | None:
644 """
645 Determine how many file rows are required to obtain `nrows` data
646 rows when `skiprows` is a function.
648 Parameters
649 ----------
650 skiprows : function
651 The function passed to read_excel by the user.
652 rows_to_use : int
653 The number of rows that will be needed for the header and
654 the data.
656 Returns
657 -------
658 int
659 """
660 i = 0
661 rows_used_so_far = 0
662 while rows_used_so_far < rows_to_use:
663 if not skiprows(i):
664 rows_used_so_far += 1
665 i += 1
666 if i >= EXCEL_ROWS_MAX:
667 return None
668 return i
670 def _calc_rows(
671 self,
672 header: int | Sequence[int] | None,
673 index_col: int | Sequence[int] | None,
674 skiprows: Sequence[int] | int | Callable[[int], object] | None,
675 nrows: int | None,
676 ) -> int | None:
677 """
678 If nrows specified, find the number of rows needed from the
679 file, otherwise return None.
682 Parameters
683 ----------
684 header : int, list of int, or None
685 See read_excel docstring.
686 index_col : int, str, list of int, or None
687 See read_excel docstring.
688 skiprows : list-like, int, callable, or None
689 See read_excel docstring.
690 nrows : int or None
691 See read_excel docstring.
693 Returns
694 -------
695 int or None
696 """
697 if nrows is None:
698 return None
699 if header is None:
700 header_rows = 1
701 elif is_integer(header):
702 header = cast("int", header)
703 header_rows = 1 + header
704 else:
705 header = cast("Sequence", header)
706 header_rows = 1 + header[-1]
707 # If there is a MultiIndex header and an index then there is also
708 # a row containing just the index name(s)
709 if is_list_like(header) and index_col is not None:
710 header = cast("Sequence", header)
711 if len(header) > 1:
712 header_rows += 1
713 if skiprows is None:
714 return header_rows + nrows
715 if is_integer(skiprows):
716 skiprows = cast("int", skiprows)
717 return header_rows + nrows + skiprows
718 if is_list_like(skiprows):
720 def f(skiprows: Sequence, x: int) -> bool:
721 return x in skiprows
723 skiprows = cast("Sequence", skiprows)
724 return self._check_skiprows_func(partial(f, skiprows), header_rows + nrows)
725 if callable(skiprows):
726 return self._check_skiprows_func(
727 skiprows,
728 header_rows + nrows,
729 )
730 # else unexpected skiprows type: read_excel will not optimize
731 # the number of rows read from file
732 return None
734 def parse(
735 self,
736 sheet_name: str | int | list[int] | list[str] | None = 0,
737 header: int | Sequence[int] | None = 0,
738 names: SequenceNotStr[Hashable] | range | None = None,
739 index_col: int | Sequence[int] | None = None,
740 usecols=None,
741 dtype: DtypeArg | None = None,
742 true_values: Iterable[Hashable] | None = None,
743 false_values: Iterable[Hashable] | None = None,
744 skiprows: Sequence[int] | int | Callable[[int], object] | None = None,
745 nrows: int | None = None,
746 na_values=None,
747 verbose: bool = False,
748 parse_dates: list | dict | bool = False,
749 date_format: dict[Hashable, str] | str | None = None,
750 thousands: str | None = None,
751 decimal: str = ".",
752 comment: str | None = None,
753 skipfooter: int = 0,
754 dtype_backend: DtypeBackend | lib.NoDefault = lib.no_default,
755 **kwds,
756 ):
757 validate_header_arg(header)
758 validate_integer("nrows", nrows)
760 ret_dict = False
762 # Keep sheetname to maintain backwards compatibility.
763 sheets: list[int] | list[str]
764 if isinstance(sheet_name, list):
765 sheets = sheet_name
766 ret_dict = True
767 elif sheet_name is None:
768 sheets = self.sheet_names
769 ret_dict = True
770 elif isinstance(sheet_name, str):
771 sheets = [sheet_name]
772 else:
773 sheets = [sheet_name]
775 # handle same-type duplicates.
776 sheets = cast("list[int] | list[str]", list(dict.fromkeys(sheets).keys()))
778 output = {}
780 last_sheetname = None
781 for asheetname in sheets:
782 last_sheetname = asheetname
783 if verbose:
784 print(f"Reading sheet {asheetname}")
786 if isinstance(asheetname, str):
787 sheet = self.get_sheet_by_name(asheetname)
788 else: # assume an integer if not a string
789 sheet = self.get_sheet_by_index(asheetname)
791 file_rows_needed = self._calc_rows(header, index_col, skiprows, nrows)
792 data = self.get_sheet_data(sheet, file_rows_needed)
793 if hasattr(sheet, "close"):
794 # pyxlsb opens two TemporaryFiles
795 sheet.close()
796 usecols = maybe_convert_usecols(usecols)
798 if not data:
799 output[asheetname] = DataFrame()
800 continue
802 output = self._parse_sheet(
803 data=data,
804 output=output,
805 asheetname=asheetname,
806 header=header,
807 names=names,
808 index_col=index_col,
809 usecols=usecols,
810 dtype=dtype,
811 skiprows=skiprows,
812 nrows=nrows,
813 true_values=true_values,
814 false_values=false_values,
815 na_values=na_values,
816 parse_dates=parse_dates,
817 date_format=date_format,
818 thousands=thousands,
819 decimal=decimal,
820 comment=comment,
821 skipfooter=skipfooter,
822 dtype_backend=dtype_backend,
823 **kwds,
824 )
826 if last_sheetname is None:
827 raise ValueError("Sheet name is an empty list")
829 if ret_dict:
830 return output
831 else:
832 return output[last_sheetname]
834 def _parse_sheet(
835 self,
836 data: list,
837 output: dict,
838 asheetname: str | int | None = None,
839 header: int | Sequence[int] | None = 0,
840 names: SequenceNotStr[Hashable] | range | None = None,
841 index_col: int | Sequence[int] | None = None,
842 usecols=None,
843 dtype: DtypeArg | None = None,
844 skiprows: Sequence[int] | int | Callable[[int], object] | None = None,
845 nrows: int | None = None,
846 true_values: Iterable[Hashable] | None = None,
847 false_values: Iterable[Hashable] | None = None,
848 na_values=None,
849 parse_dates: list | dict | bool = False,
850 date_format: dict[Hashable, str] | str | None = None,
851 thousands: str | None = None,
852 decimal: str = ".",
853 comment: str | None = None,
854 skipfooter: int = 0,
855 dtype_backend: DtypeBackend | lib.NoDefault = lib.no_default,
856 **kwds,
857 ):
858 is_list_header = False
859 is_len_one_list_header = False
860 if is_list_like(header):
861 assert isinstance(header, Sequence)
862 is_list_header = True
863 if len(header) == 1:
864 is_len_one_list_header = True
866 if is_len_one_list_header:
867 header = cast("Sequence[int]", header)[0]
869 # forward fill and pull out names for MultiIndex column
870 header_names = None
871 if header is not None and is_list_like(header):
872 assert isinstance(header, Sequence)
874 header_names = []
875 control_row = [True] * len(data[0])
877 for row in header:
878 if is_integer(skiprows):
879 assert isinstance(skiprows, int)
880 row += skiprows
882 if row > len(data) - 1:
883 raise ValueError(
884 f"header index {row} exceeds maximum index "
885 f"{len(data) - 1} of data.",
886 )
888 data[row], control_row = fill_mi_header(data[row], control_row)
890 if index_col is not None:
891 header_name, _ = pop_header_name(data[row], index_col)
892 header_names.append(header_name)
894 # If there is a MultiIndex header and an index then there is also
895 # a row containing just the index name(s)
896 has_index_names = False
897 if is_list_header and not is_len_one_list_header and index_col is not None:
898 index_col_set: set[int]
899 if isinstance(index_col, int):
900 index_col_set = {index_col}
901 else:
902 assert isinstance(index_col, Sequence)
903 index_col_set = set(index_col)
905 # We have to handle mi without names. If any of the entries in the data
906 # columns are not empty, this is a regular row
907 assert isinstance(header, Sequence)
908 if len(header) < len(data):
909 potential_index_names = data[len(header)]
910 has_index_names = all(
911 x == "" or x is None
912 for i, x in enumerate(potential_index_names)
913 if not control_row[i] and i not in index_col_set
914 )
916 if is_list_like(index_col):
917 # Forward fill values for MultiIndex index.
918 if header is None:
919 offset = 0
920 elif isinstance(header, int):
921 offset = 1 + header
922 else:
923 offset = 1 + max(header)
925 # GH34673: if MultiIndex names present and not defined in the header,
926 # offset needs to be incremented so that forward filling starts
927 # from the first MI value instead of the name
928 if has_index_names:
929 offset += 1
931 # Check if we have an empty dataset
932 # before trying to collect data.
933 if offset < len(data):
934 assert isinstance(index_col, Sequence)
936 for col in index_col:
937 last = data[offset][col]
939 for row in range(offset + 1, len(data)):
940 if data[row][col] == "" or data[row][col] is None:
941 data[row][col] = last
942 else:
943 last = data[row][col]
945 # GH 12292 : error when read one empty column from excel file
946 try:
947 parser = TextParser(
948 data,
949 names=names,
950 header=header,
951 index_col=index_col,
952 has_index_names=has_index_names,
953 dtype=dtype,
954 true_values=true_values,
955 false_values=false_values,
956 skiprows=skiprows,
957 nrows=nrows,
958 na_values=na_values,
959 skip_blank_lines=False, # GH 39808
960 parse_dates=parse_dates,
961 date_format=date_format,
962 thousands=thousands,
963 decimal=decimal,
964 comment=comment,
965 skipfooter=skipfooter,
966 usecols=usecols,
967 dtype_backend=dtype_backend,
968 **kwds,
969 )
971 output[asheetname] = parser.read(nrows=nrows)
973 if header_names:
974 output[asheetname].columns = output[asheetname].columns.set_names(
975 header_names
976 )
978 except EmptyDataError:
979 # No Data, return an empty DataFrame
980 output[asheetname] = DataFrame()
982 except Exception as err:
983 err.args = (f"{err.args[0]} (sheet: {asheetname})", *err.args[1:])
984 raise err
986 return output
989@set_module("pandas")
990class ExcelWriter(Generic[_WorkbookT]):
991 """
992 Class for writing DataFrame objects into excel sheets.
994 The default ``engine`` is chosen based on the file extension. The values
995 below are the engine strings (i.e. valid values for the ``engine``
996 keyword); the package each one wraps is shown in parentheses:
998 * ``.xlsx``: ``"xlsxwriter"`` (`XlsxWriter
999 <https://pypi.org/project/XlsxWriter/>`__) if installed, otherwise
1000 ``"openpyxl"`` (`openpyxl <https://pypi.org/project/openpyxl/>`__).
1001 * ``.xlsm``: ``"openpyxl"`` (`openpyxl
1002 <https://pypi.org/project/openpyxl/>`__).
1003 * ``.ods``: ``"odf"`` (`odfpy <https://pypi.org/project/odfpy/>`__).
1005 These defaults can be overridden via the ``engine`` argument or by
1006 setting the ``io.excel.<extension>.writer`` option.
1008 See :meth:`DataFrame.to_excel` for typical usage.
1010 The writer should be used as a context manager. Otherwise, call `close()` to save
1011 and close any opened file handles.
1013 Parameters
1014 ----------
1015 path : str or typing.BinaryIO
1016 Path to xlsx, xlsm, or ods file.
1017 engine : {'openpyxl', 'xlsxwriter', 'odf'}, optional
1018 Engine to use for writing, given as the engine string (not the
1019 package name). Which engines are accepted depends on the file
1020 extension:
1022 * ``.xlsx``: ``"openpyxl"`` or ``"xlsxwriter"``.
1023 * ``.xlsm``: ``"openpyxl"``.
1024 * ``.ods``: ``"odf"`` (provided by the ``odfpy`` package).
1026 If None, defaults to ``io.excel.<extension>.writer``. NOTE: can only
1027 be passed as a keyword argument.
1028 date_format : str, default None
1029 Format string for dates written into Excel files (e.g. 'YYYY-MM-DD').
1030 datetime_format : str, default None
1031 Format string for datetime objects written into Excel files.
1032 (e.g. 'YYYY-MM-DD HH:MM:SS').
1033 mode : {'w', 'a'}, default 'w'
1034 File mode to use (write or append). Append does not work with fsspec
1035 URLs.
1037 .. warning::
1039 In append mode the existing workbook is read and completely
1040 rewritten by the engine, so any content the engine cannot
1041 represent (e.g. VBA macros in ``.xlsm`` files, charts or images)
1042 is silently lost and the workbook may be otherwise altered.
1043 storage_options : dict, optional
1044 Extra options that make sense for a particular storage connection, e.g.
1045 host, port, username, password, etc. For HTTP(S) URLs the key-value pairs
1046 are forwarded to ``urllib.request.Request`` as header options. For other
1047 URLs (e.g. starting with "s3://", and "gcs://") the key-value pairs are
1048 forwarded to ``fsspec.open``. Please see ``fsspec`` and ``urllib`` for more
1049 details, and for more examples on storage options refer `here
1050 <https://pandas.pydata.org/docs/user_guide/io.html?
1051 highlight=storage_options#reading-writing-remote-files>`_.
1053 if_sheet_exists : {'error', 'new', 'replace', 'overlay'}, default 'error'
1054 How to behave when trying to write to a sheet that already
1055 exists (append mode only).
1057 * error: raise a ValueError.
1058 * new: Create a new sheet, with a name determined by the engine.
1059 * replace: Delete the contents of the sheet before writing to it.
1060 * overlay: Write contents to the existing sheet without first removing,
1061 but possibly over top of, the existing contents.
1063 engine_kwargs : dict, optional
1064 Keyword arguments to be passed into the engine. The bullets below
1065 are keyed by the ``engine`` string (not the package name); for
1066 each engine, ``engine_kwargs`` is forwarded as follows:
1068 * ``"xlsxwriter"``: ``xlsxwriter.Workbook(file, **engine_kwargs)``
1069 * ``"openpyxl"`` (write mode): ``openpyxl.Workbook(**engine_kwargs)``
1070 * ``"openpyxl"`` (append mode):
1071 ``openpyxl.load_workbook(file, **engine_kwargs)``
1072 * ``"odf"``:
1073 ``odf.opendocument.OpenDocumentSpreadsheet(**engine_kwargs)``
1075 See Also
1076 --------
1077 read_excel : Read an Excel sheet values (xlsx) file into DataFrame.
1078 read_csv : Read a comma-separated values (csv) file into DataFrame.
1079 read_fwf : Read a table of fixed-width formatted lines into DataFrame.
1081 Notes
1082 -----
1083 For compatibility with CSV writers, ExcelWriter serializes lists
1084 and dicts to strings before writing.
1086 Examples
1087 --------
1088 Default usage:
1090 >>> df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"]) # doctest: +SKIP
1091 >>> with pd.ExcelWriter("path_to_file.xlsx") as writer:
1092 ... df.to_excel(writer) # doctest: +SKIP
1094 To write to separate sheets in a single file:
1096 >>> df1 = pd.DataFrame([["AAA", "BBB"]], columns=["Spam", "Egg"]) # doctest: +SKIP
1097 >>> df2 = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"]) # doctest: +SKIP
1098 >>> with pd.ExcelWriter("path_to_file.xlsx") as writer:
1099 ... df1.to_excel(writer, sheet_name="Sheet1") # doctest: +SKIP
1100 ... df2.to_excel(writer, sheet_name="Sheet2") # doctest: +SKIP
1102 You can set the date format or datetime format:
1104 >>> from datetime import date, datetime # doctest: +SKIP
1105 >>> df = pd.DataFrame(
1106 ... [
1107 ... [date(2014, 1, 31), date(1999, 9, 24)],
1108 ... [datetime(1998, 5, 26, 23, 33, 4), datetime(2014, 2, 28, 13, 5, 13)],
1109 ... ],
1110 ... index=["Date", "Datetime"],
1111 ... columns=["X", "Y"],
1112 ... ) # doctest: +SKIP
1113 >>> with pd.ExcelWriter(
1114 ... "path_to_file.xlsx",
1115 ... date_format="YYYY-MM-DD",
1116 ... datetime_format="YYYY-MM-DD HH:MM:SS",
1117 ... ) as writer:
1118 ... df.to_excel(writer) # doctest: +SKIP
1120 You can also append to an existing Excel file:
1122 >>> with pd.ExcelWriter("path_to_file.xlsx", mode="a", engine="openpyxl") as writer:
1123 ... df.to_excel(writer, sheet_name="Sheet3") # doctest: +SKIP
1125 Here, the `if_sheet_exists` parameter can be set to replace a sheet if it
1126 already exists:
1128 >>> with pd.ExcelWriter(
1129 ... "path_to_file.xlsx",
1130 ... mode="a",
1131 ... engine="openpyxl",
1132 ... if_sheet_exists="replace",
1133 ... ) as writer:
1134 ... df.to_excel(writer, sheet_name="Sheet1") # doctest: +SKIP
1136 You can also write multiple DataFrames to a single sheet. Note that the
1137 ``if_sheet_exists`` parameter needs to be set to ``overlay``:
1139 >>> with pd.ExcelWriter(
1140 ... "path_to_file.xlsx",
1141 ... mode="a",
1142 ... engine="openpyxl",
1143 ... if_sheet_exists="overlay",
1144 ... ) as writer:
1145 ... df1.to_excel(writer, sheet_name="Sheet1")
1146 ... df2.to_excel(writer, sheet_name="Sheet1", startcol=3) # doctest: +SKIP
1148 You can store Excel file in RAM:
1150 >>> import io
1151 >>> df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])
1152 >>> buffer = io.BytesIO()
1153 >>> with pd.ExcelWriter(buffer) as writer:
1154 ... df.to_excel(writer)
1156 You can pack Excel file into zip archive:
1158 >>> import zipfile # doctest: +SKIP
1159 >>> df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"]) # doctest: +SKIP
1160 >>> with zipfile.ZipFile("path_to_file.zip", "w") as zf:
1161 ... with zf.open("filename.xlsx", "w") as buffer:
1162 ... with pd.ExcelWriter(buffer) as writer:
1163 ... df.to_excel(writer) # doctest: +SKIP
1165 You can specify additional arguments to the underlying engine:
1167 >>> with pd.ExcelWriter(
1168 ... "path_to_file.xlsx",
1169 ... engine="xlsxwriter",
1170 ... engine_kwargs={"options": {"nan_inf_to_errors": True}},
1171 ... ) as writer:
1172 ... df.to_excel(writer) # doctest: +SKIP
1174 In append mode, ``engine_kwargs`` are passed through to
1175 openpyxl's ``load_workbook``:
1177 >>> with pd.ExcelWriter(
1178 ... "path_to_file.xlsx",
1179 ... engine="openpyxl",
1180 ... mode="a",
1181 ... engine_kwargs={"keep_vba": True},
1182 ... ) as writer:
1183 ... df.to_excel(writer, sheet_name="Sheet2") # doctest: +SKIP
1184 """
1186 # Defining an ExcelWriter implementation (see abstract methods for more...)
1188 # - Mandatory
1189 # - ``write_cells(self, cells, sheet_name=None, startrow=0, startcol=0)``
1190 # --> called to write additional DataFrames to disk
1191 # - ``_supported_extensions`` (tuple of supported extensions), used to
1192 # check that engine supports the given extension.
1193 # - ``_engine`` - string that gives the engine name. Necessary to
1194 # instantiate class directly and bypass ``ExcelWriterMeta`` engine
1195 # lookup.
1196 # - ``save(self)`` --> called to save file to disk
1197 # - Mostly mandatory (i.e. should at least exist)
1198 # - book, cur_sheet, path
1200 # - Optional:
1201 # - ``__init__(self, path, engine=None, **kwargs)`` --> always called
1202 # with path as first argument.
1204 # You also need to register the class with ``register_writer()``.
1205 # Technically, ExcelWriter implementations don't need to subclass
1206 # ExcelWriter.
1208 _engine: str
1209 _supported_extensions: tuple[str, ...]
1211 def __new__(
1212 cls,
1213 path: FilePath | WriteExcelBuffer | ExcelWriter,
1214 engine: str | None = None,
1215 date_format: str | None = None,
1216 datetime_format: str | None = None,
1217 mode: str = "w",
1218 storage_options: StorageOptions | None = None,
1219 if_sheet_exists: ExcelWriterIfSheetExists | None = None,
1220 engine_kwargs: dict | None = None,
1221 ) -> Self:
1222 # only switch class if generic(ExcelWriter)
1223 if cls is ExcelWriter:
1224 if engine is None or (isinstance(engine, str) and engine == "auto"):
1225 if isinstance(path, str):
1226 ext = os.path.splitext(path)[-1][1:]
1227 else:
1228 ext = "xlsx"
1230 try:
1231 engine = config["io"]["excel"][ext]["writer"]
1232 if engine == "auto":
1233 engine = get_default_engine(ext, mode="writer")
1234 except KeyError as err:
1235 raise ValueError(f"No engine for filetype: '{ext}'") from err
1237 # for mypy
1238 assert engine is not None
1239 # error: Incompatible types in assignment (expression has type
1240 # "type[ExcelWriter[Any]]", variable has type "type[Self]")
1241 cls = get_writer(engine) # type: ignore[assignment]
1243 return object.__new__(cls)
1245 @property
1246 def supported_extensions(self) -> tuple[str, ...]:
1247 """Extensions that writer engine supports."""
1248 return self._supported_extensions
1250 @property
1251 def engine(self) -> str:
1252 """Name of engine."""
1253 return self._engine
1255 @property
1256 def sheets(self) -> dict[str, Any]:
1257 """Mapping of sheet names to sheet objects."""
1258 raise NotImplementedError
1260 @property
1261 def book(self) -> _WorkbookT:
1262 """
1263 Book instance. Class type will depend on the engine used.
1265 This attribute can be used to access engine-specific features.
1266 """
1267 raise NotImplementedError
1269 def _write_cells(
1270 self,
1271 cells,
1272 sheet_name: str | None = None,
1273 startrow: int = 0,
1274 startcol: int = 0,
1275 freeze_panes: tuple[int, int] | None = None,
1276 autofilter_range: str | None = None,
1277 ) -> None:
1278 """
1279 Write given formatted cells into Excel an excel sheet
1281 Parameters
1282 ----------
1283 cells : generator
1284 cell of formatted data to save to Excel sheet
1285 sheet_name : str, default None
1286 Name of Excel sheet, if None, then use self.cur_sheet
1287 startrow : upper left cell row to dump data frame
1288 startcol : upper left cell column to dump data frame
1289 freeze_panes: int tuple of length 2
1290 contains the bottom-most row and right-most column to freeze
1291 autofilter_range: str, default None
1292 column ranges to add automatic filters to, for example "A1:D5"
1293 """
1294 raise NotImplementedError
1296 def _save(self) -> None:
1297 """
1298 Save workbook to disk.
1299 """
1300 raise NotImplementedError
1302 def __init__(
1303 self,
1304 path: FilePath | WriteExcelBuffer | ExcelWriter,
1305 engine: str | None = None,
1306 date_format: str | None = None,
1307 datetime_format: str | None = None,
1308 mode: str = "w",
1309 storage_options: StorageOptions | None = None,
1310 if_sheet_exists: ExcelWriterIfSheetExists | None = None,
1311 engine_kwargs: dict[str, Any] | None = None,
1312 ) -> None:
1313 # validate that this engine can handle the extension
1314 if isinstance(path, str):
1315 ext = os.path.splitext(path)[-1]
1316 self.check_extension(ext)
1318 # use mode to open the file
1319 if "b" not in mode:
1320 mode += "b"
1321 # use "a" for the user to append data to excel but internally use "r+" to let
1322 # the excel backend first read the existing file and then write any data to it
1323 mode = mode.replace("a", "r+")
1325 if if_sheet_exists not in (None, "error", "new", "replace", "overlay"):
1326 raise ValueError(
1327 f"'{if_sheet_exists}' is not valid for if_sheet_exists. "
1328 "Valid options are 'error', 'new', 'replace' and 'overlay'."
1329 )
1330 if if_sheet_exists and "r+" not in mode:
1331 raise ValueError("if_sheet_exists is only valid in append mode (mode='a')")
1332 if if_sheet_exists is None:
1333 if_sheet_exists = "error"
1334 self._if_sheet_exists = if_sheet_exists
1336 # cast ExcelWriter to avoid adding 'if self._handles is not None'
1337 self._handles = IOHandles(
1338 cast("IO[bytes]", path), compression={"compression": None}
1339 )
1340 if not isinstance(path, ExcelWriter):
1341 self._handles = get_handle(
1342 path, mode, storage_options=storage_options, is_text=False
1343 )
1344 self._cur_sheet = None
1346 if date_format is None:
1347 self._date_format = "YYYY-MM-DD"
1348 else:
1349 self._date_format = date_format
1350 if datetime_format is None:
1351 self._datetime_format = "YYYY-MM-DD HH:MM:SS"
1352 else:
1353 self._datetime_format = datetime_format
1355 self._mode = mode
1357 @property
1358 def date_format(self) -> str:
1359 """
1360 Format string for dates written into Excel files (e.g. 'YYYY-MM-DD').
1361 """
1362 return self._date_format
1364 @property
1365 def datetime_format(self) -> str:
1366 """
1367 Format string for dates written into Excel files (e.g. 'YYYY-MM-DD').
1368 """
1369 return self._datetime_format
1371 @property
1372 def if_sheet_exists(self) -> str:
1373 """
1374 How to behave when writing to a sheet that already exists in append mode.
1375 """
1376 return self._if_sheet_exists
1378 def __fspath__(self) -> str:
1379 return getattr(self._handles.handle, "name", "")
1381 def _get_sheet_name(self, sheet_name: str | None) -> str:
1382 if sheet_name is None:
1383 sheet_name = self._cur_sheet
1384 if sheet_name is None: # pragma: no cover
1385 raise ValueError("Must pass explicit sheet_name or set _cur_sheet property")
1386 return sheet_name
1388 def _value_with_fmt(
1389 self, val
1390 ) -> tuple[
1391 int | float | bool | str | datetime.datetime | datetime.date, str | None
1392 ]:
1393 """
1394 Convert numpy types to Python types for the Excel writers.
1396 Parameters
1397 ----------
1398 val : object
1399 Value to be written into cells
1401 Returns
1402 -------
1403 Tuple with the first element being the converted value and the second
1404 being an optional format
1405 """
1406 fmt = None
1408 if is_integer(val):
1409 val = int(val)
1410 elif is_float(val):
1411 val = float(val)
1412 elif is_bool(val):
1413 val = bool(val)
1414 elif is_decimal(val):
1415 val = Decimal(val)
1416 elif isinstance(val, datetime.datetime):
1417 fmt = self._datetime_format
1418 elif isinstance(val, datetime.date):
1419 fmt = self._date_format
1420 elif isinstance(val, datetime.timedelta):
1421 val = val.total_seconds() / 86400
1422 fmt = "0"
1423 else:
1424 val = str(val)
1425 # GH#56954
1426 # Excel's limitation on cell contents is 32767 characters
1427 # xref https://support.microsoft.com/en-au/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
1428 if len(val) > 32767:
1429 warnings.warn(
1430 "Cell contents too long, truncated to 32767 characters",
1431 UserWarning,
1432 stacklevel=find_stack_level(),
1433 )
1434 return val, fmt
1436 @classmethod
1437 def check_extension(cls, ext: str) -> Literal[True]:
1438 """
1439 checks that path's extension against the Writer's supported
1440 extensions. If it isn't supported, raises UnsupportedFiletypeError.
1441 """
1442 if ext.startswith("."):
1443 ext = ext[1:]
1444 if not any(ext in extension for extension in cls._supported_extensions):
1445 raise ValueError(f"Invalid extension for engine '{cls.engine}': '{ext}'")
1446 return True
1448 # Allow use as a contextmanager
1449 def __enter__(self) -> Self:
1450 return self
1452 def __exit__(
1453 self,
1454 exc_type: type[BaseException] | None,
1455 exc_value: BaseException | None,
1456 traceback: TracebackType | None,
1457 ) -> None:
1458 self.close()
1460 def close(self) -> None:
1461 """synonym for save, to make it more file-like"""
1462 self._save()
1463 self._handles.close()
1466XLS_SIGNATURES = (
1467 b"\x09\x00\x04\x00\x07\x00\x10\x00", # BIFF2
1468 b"\x09\x02\x06\x00\x00\x00\x10\x00", # BIFF3
1469 b"\x09\x04\x06\x00\x00\x00\x10\x00", # BIFF4
1470 b"\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1", # Compound File Binary
1471)
1472ZIP_SIGNATURE = b"PK\x03\x04"
1473PEEK_SIZE = max(map(len, (*XLS_SIGNATURES, ZIP_SIGNATURE)))
1476def inspect_excel_format(
1477 content_or_path: FilePath | ReadBuffer[bytes],
1478 storage_options: StorageOptions | None = None,
1479) -> str | None:
1480 """
1481 Inspect the path or content of an excel file and get its format.
1483 Adopted from xlrd: https://github.com/python-excel/xlrd.
1485 Parameters
1486 ----------
1487 content_or_path : str or file-like object
1488 Path to file or content of file to inspect. May be a URL.
1489 storage_options : dict, optional
1490 Extra options that make sense for a particular storage connection, e.g.
1491 host, port, username, password, etc. For HTTP(S) URLs the key-value pairs
1492 are forwarded to ``urllib.request.Request`` as header options. For other
1493 URLs (e.g. starting with "s3://", and "gcs://") the key-value pairs are
1494 forwarded to ``fsspec.open``. Please see ``fsspec`` and ``urllib`` for more
1495 details, and for more examples on storage options refer `here
1496 <https://pandas.pydata.org/docs/user_guide/io.html?
1497 highlight=storage_options#reading-writing-remote-files>`_.
1499 Returns
1500 -------
1501 str or None
1502 Format of file if it can be determined.
1504 Raises
1505 ------
1506 ValueError
1507 If resulting stream is empty.
1508 BadZipFile
1509 If resulting stream does not have an XLS signature and is not a valid zipfile.
1510 """
1511 with get_handle(
1512 content_or_path, "rb", storage_options=storage_options, is_text=False
1513 ) as handle:
1514 stream = handle.handle
1515 stream.seek(0)
1516 buf = stream.read(PEEK_SIZE)
1517 if buf is None:
1518 raise ValueError("stream is empty")
1519 assert isinstance(buf, bytes)
1520 peek = buf
1521 stream.seek(0)
1523 if any(peek.startswith(sig) for sig in XLS_SIGNATURES):
1524 return "xls"
1525 elif not peek.startswith(ZIP_SIGNATURE):
1526 return None
1528 with zipfile.ZipFile(stream) as zf:
1529 # Workaround for some third party files that use forward slashes and
1530 # lower case names.
1531 component_names = {
1532 name.replace("\\", "/").lower() for name in zf.namelist()
1533 }
1535 if "xl/workbook.xml" in component_names:
1536 return "xlsx"
1537 if "xl/workbook.bin" in component_names:
1538 return "xlsb"
1539 if "content.xml" in component_names:
1540 return "ods"
1541 return "zip"
1544# GH#46352 - engine name -> (module, class) of the workbook objects it reads
1545_WORKBOOK_CLASSES = {
1546 "xlrd": ("xlrd", "Book"),
1547 "openpyxl": ("openpyxl", "Workbook"),
1548 "odf": ("odf.opendocument", "OpenDocument"),
1549 "pyxlsb": ("pyxlsb", "Workbook"),
1550 "calamine": ("python_calamine", "CalamineWorkbook"),
1551}
1554def _infer_engine_from_workbook(obj) -> str | None:
1555 """
1556 Return the engine whose library created the workbook ``obj``, if any.
1557 """
1558 for engine, (module_name, class_name) in _WORKBOOK_CLASSES.items():
1559 # GH#56692 - avoid importing optional dependencies; such a workbook
1560 # can only exist if the caller already imported its module.
1561 module = sys.modules.get(module_name)
1562 if module is not None and isinstance(obj, getattr(module, class_name)):
1563 return engine
1564 return None
1567@set_module("pandas")
1568class ExcelFile:
1569 """
1570 Class for parsing tabular Excel sheets into DataFrame objects.
1572 See read_excel for more documentation.
1574 Parameters
1575 ----------
1576 path_or_buffer : str, bytes, pathlib.Path,
1577 A file-like object or a workbook object from a supported engine.
1578 If a string or path object, expected to be a path to a
1579 .xls, .xlsx, .xlsb, .xlsm, .odf, .ods, or .odt file.
1580 engine : str, default None
1581 Supported engines: ``xlrd``, ``openpyxl``, ``odf``, ``pyxlsb``, ``calamine``
1582 Engine compatibility :
1584 - ``xlrd`` supports old-style Excel files (.xls).
1585 - ``openpyxl`` supports newer Excel file formats.
1586 - ``odf`` supports OpenDocument file formats (.odf, .ods, .odt).
1587 - ``pyxlsb`` supports Binary Excel files.
1588 - ``calamine`` supports Excel (.xls, .xlsx, .xlsm, .xlsb)
1589 and OpenDocument (.ods) file formats.
1591 The engine `xlrd <https://xlrd.readthedocs.io/en/latest/>`_
1592 now only supports old-style ``.xls`` files.
1593 When ``engine=None``, the following logic will be
1594 used to determine the engine:
1596 - If ``path_or_buffer`` is a workbook object, the engine that created it
1597 will be used.
1598 - Otherwise if ``path_or_buffer`` is an OpenDocument format (.odf, .ods, .odt),
1599 then `odf <https://pypi.org/project/odfpy/>`_ will be used.
1600 - Otherwise if ``path_or_buffer`` is an xls format,
1601 ``xlrd`` will be used.
1602 - Otherwise if ``path_or_buffer`` is in xlsb format,
1603 `pyxlsb <https://pypi.org/project/pyxlsb/>`_ will be used.
1604 - Otherwise if `openpyxl <https://pypi.org/project/openpyxl/>`_ is installed,
1605 then ``openpyxl`` will be used.
1606 - Otherwise if ``xlrd >= 2.0`` is installed, a ``ValueError`` will be raised.
1608 .. deprecated:: 3.1.0
1609 For xlsx/xlsm files the default engine will change from
1610 ``openpyxl`` to ``calamine`` in a future version. Pass ``engine``
1611 explicitly, or set the ``io.excel.xlsx.reader`` option (xlsm
1612 files are format-detected as xlsx), to keep the current behavior.
1614 .. warning::
1616 Please do not report issues when using ``xlrd`` to read ``.xlsx`` files.
1617 This is not supported, switch to using ``openpyxl`` instead.
1618 storage_options : dict, optional
1619 Extra options that make sense for a particular storage connection, e.g.
1620 host, port, username, password, etc. For HTTP(S) URLs the key-value pairs
1621 are forwarded to ``urllib.request.Request`` as header options. For other
1622 URLs (e.g. starting with "s3://", and "gcs://") the key-value pairs are
1623 forwarded to ``fsspec.open``. Please see ``fsspec`` and ``urllib`` for more
1624 details, and for more examples on storage options refer `here
1625 <https://pandas.pydata.org/docs/user_guide/io.html?
1626 highlight=storage_options#reading-writing-remote-files>`_.
1627 engine_kwargs : dict, optional
1628 Arbitrary keyword arguments passed to excel engine.
1630 See Also
1631 --------
1632 DataFrame.to_excel : Write DataFrame to an Excel file.
1633 DataFrame.to_csv : Write DataFrame to a comma-separated values (csv) file.
1634 read_csv : Read a comma-separated values (csv) file into DataFrame.
1635 read_fwf : Read a table of fixed-width formatted lines into DataFrame.
1637 Examples
1638 --------
1639 >>> file = pd.ExcelFile("myfile.xlsx") # doctest: +SKIP
1640 >>> with pd.ExcelFile("myfile.xls") as xls: # doctest: +SKIP
1641 ... df1 = pd.read_excel(xls, "Sheet1") # doctest: +SKIP
1642 """
1644 from pandas.io.excel._calamine import CalamineReader
1645 from pandas.io.excel._odfreader import ODFReader
1646 from pandas.io.excel._openpyxl import OpenpyxlReader
1647 from pandas.io.excel._pyxlsb import PyxlsbReader
1648 from pandas.io.excel._xlrd import XlrdReader
1650 _engines: Mapping[str, Any] = {
1651 "xlrd": XlrdReader,
1652 "openpyxl": OpenpyxlReader,
1653 "odf": ODFReader,
1654 "pyxlsb": PyxlsbReader,
1655 "calamine": CalamineReader,
1656 }
1658 def __init__(
1659 self,
1660 path_or_buffer,
1661 engine: str | None = None,
1662 storage_options: StorageOptions | None = None,
1663 engine_kwargs: dict | None = None,
1664 ) -> None:
1665 if engine_kwargs is None:
1666 engine_kwargs = {}
1668 if engine is not None and engine not in self._engines:
1669 raise ValueError(f"Unknown engine: {engine}")
1671 # Always a string
1672 self._io = stringify_path(path_or_buffer)
1674 if engine is None:
1675 # GH#68086 - must precede inspect_excel_format, which seeks.
1676 engine = _infer_engine_from_workbook(path_or_buffer)
1678 if engine is None:
1679 ext = inspect_excel_format(
1680 content_or_path=path_or_buffer, storage_options=storage_options
1681 )
1682 if ext is None:
1683 raise ValueError(
1684 "Excel file format cannot be determined, you must specify "
1685 "an engine manually."
1686 )
1688 engine = config["io"]["excel"][ext]["reader"]
1689 if engine == "auto":
1690 engine = get_default_engine(ext, mode="reader")
1691 # GH#56542 - the calamine engine will become the default
1692 # for xlsx/xlsm. Warn while calamine is available so the
1693 # switch is actionable; an explicit engine or the
1694 # io.excel.xlsx.reader option silences this. xlsm files
1695 # are format-sniffed as xlsx, so ext is never "xlsm" here.
1696 if (
1697 ext == "xlsx"
1698 and engine == "openpyxl"
1699 and import_optional_dependency("python_calamine", errors="ignore")
1700 is not None
1701 ):
1702 warnings.warn(
1703 "The default engine for reading 'xlsx' files "
1704 "will change from 'openpyxl' to 'calamine' in a "
1705 "future version. Pass engine='openpyxl' (or set "
1706 "the 'io.excel.xlsx.reader' option) to keep the "
1707 "current engine and silence this warning.",
1708 Pandas4Warning,
1709 stacklevel=find_stack_level(),
1710 )
1712 assert engine is not None
1713 self.engine = engine
1714 self.storage_options = storage_options
1716 self._reader = self._engines[engine](
1717 self._io,
1718 storage_options=storage_options,
1719 engine_kwargs=engine_kwargs,
1720 )
1722 def __fspath__(self):
1723 return self._io
1725 def parse(
1726 self,
1727 sheet_name: str | int | list[int] | list[str] | None = 0,
1728 header: int | Sequence[int] | None = 0,
1729 names: SequenceNotStr[Hashable] | range | None = None,
1730 index_col: int | Sequence[int] | None = None,
1731 usecols=None,
1732 converters=None,
1733 true_values: Iterable[Hashable] | None = None,
1734 false_values: Iterable[Hashable] | None = None,
1735 skiprows: Sequence[int] | int | Callable[[int], object] | None = None,
1736 nrows: int | None = None,
1737 na_values=None,
1738 parse_dates: list | dict | bool = False,
1739 date_format: str | dict[Hashable, str] | None = None,
1740 thousands: str | None = None,
1741 comment: str | None = None,
1742 skipfooter: int = 0,
1743 dtype_backend: DtypeBackend | lib.NoDefault = lib.no_default,
1744 **kwds,
1745 ) -> DataFrame | dict[str, DataFrame] | dict[int, DataFrame]:
1746 """
1747 Parse specified sheet(s) into a DataFrame.
1749 .. deprecated:: 3.1.0
1750 Use :func:`pd.read_excel` instead.
1752 Equivalent to read_excel(ExcelFile, ...) See the read_excel
1753 docstring for more info on accepted parameters.
1755 Parameters
1756 ----------
1757 sheet_name : str, int, list, or None, default 0
1758 Strings are used for sheet names. Integers are used in zero-indexed
1759 sheet positions (chart sheets do not count as a sheet position).
1760 Lists of strings/integers are used to request multiple sheets.
1761 When ``None``, will return a dictionary containing DataFrames for
1762 each sheet.
1763 header : int, list of int, default 0
1764 Row (0-indexed) to use for the column labels of the parsed
1765 DataFrame. If a list of integers is passed those row positions will
1766 be combined into a ``MultiIndex``. Use None if there is no header.
1767 names : array-like, default None
1768 List of column names to use. If file contains no header row,
1769 then you should explicitly pass header=None.
1770 index_col : int, str, list of int, default None
1771 Column (0-indexed) to use as the row labels of the DataFrame.
1772 Pass None if there is no such column. If a list is passed,
1773 those columns will be combined into a ``MultiIndex``. If a
1774 subset of data is selected with ``usecols``, index_col
1775 is based on the subset.
1777 Missing values will be forward filled to allow roundtripping with
1778 ``to_excel`` for ``merged_cells=True``. To avoid forward filling the
1779 missing values use ``set_index`` after reading the data instead of
1780 ``index_col``.
1782 When ``header`` is a list of two or more rows (``MultiIndex``
1783 columns), the row after the last header row is expected to hold
1784 the index name(s) — blank if the index is unnamed — as written by
1785 ``to_excel``. A file lacking this separator row can lose its first
1786 data row to the index name(s); see :ref:`the user guide
1787 <io.excel.reading_multiindex>` for details.
1788 usecols : str, list-like, or callable, default None
1789 * If None, then parse all columns.
1790 * If str, then indicates comma separated list of Excel column letters
1791 and column ranges (e.g. "A:E" or "A,C,E:F"). Ranges are inclusive of
1792 both sides.
1793 * If list of int, then indicates list of column numbers to be parsed
1794 (0-indexed).
1795 * If list of string, then indicates list of column names to be parsed.
1796 * If callable, then evaluate each column name against it and parse the
1797 column if the callable returns ``True``.
1799 Returns a subset of the columns according to behavior above.
1800 Element order is ignored, so ``usecols=[0, 1]`` is the same as
1801 ``[1, 0]``. To instantiate a :class:`~pandas.DataFrame` with element
1802 order preserved use
1803 ``pd.read_excel(path, usecols=['foo', 'bar'])[['foo', 'bar']]`` for
1804 columns in ``['foo', 'bar']`` order or
1805 ``pd.read_excel(path, usecols=['foo', 'bar'])[['bar', 'foo']]`` for
1806 ``['bar', 'foo']`` order.
1807 converters : dict, default None
1808 Dict of functions for converting values in certain columns. Keys can
1809 either be integers or column labels, values are functions that take one
1810 input argument, the Excel cell content, and return the transformed
1811 content.
1812 true_values : list, default None
1813 Values to consider as True.
1814 false_values : list, default None
1815 Values to consider as False.
1816 skiprows : list-like, int, or callable, optional
1817 Line numbers to skip (0-indexed) or number of lines to skip (int) at the
1818 start of the file. If callable, the callable function will be evaluated
1819 against the row indices, returning True if the row should be skipped and
1820 False otherwise. An example of a valid callable argument would be ``lambda
1821 x: x in [0, 2]``.
1822 nrows : int, default None
1823 Number of rows to parse.
1824 na_values : scalar, str, list-like, or dict, default None
1825 Additional strings to recognize as NA/NaN. If dict passed, specific
1826 per-column NA values.
1827 parse_dates : bool, list-like, or dict, default False
1828 The behavior is as follows:
1830 * ``bool``. If True -> try parsing the index.
1831 * ``list`` of int or names. e.g. If [1, 2, 3] -> try parsing columns 1, 2, 3
1832 each as a separate date column.
1833 * ``list`` of lists. e.g. If [[1, 3]] -> combine columns 1 and 3 and
1834 parse as a single date column.
1835 * ``dict``, e.g. {'foo' : [1, 3]} -> parse columns 1, 3 as date and call
1836 result 'foo'
1838 If a column or index contains an unparsable date, the entire column or
1839 index will be returned unaltered as an object data type. If you
1840 don`t want to parse some cells as date just change their type
1841 in Excel to "Text".For non-standard datetime parsing, use
1842 ``pd.to_datetime`` after ``pd.read_excel``.
1844 Note: A fast-path exists for iso8601-formatted dates.
1845 date_format : str or dict of column -> format, default ``None``
1846 If used in conjunction with ``parse_dates``, will parse dates
1847 according to this format. For anything more complex,
1848 please read in as ``object`` and then apply :func:`to_datetime` as-needed.
1849 thousands : str, default None
1850 Thousands separator for parsing string columns to numeric. Note that
1851 this parameter is only necessary for columns stored as TEXT in Excel,
1852 any numeric columns will automatically be parsed, regardless of display
1853 format.
1854 comment : str, default None
1855 Comments out remainder of line. Pass a character or characters to this
1856 argument to indicate comments in the input file. Any data between the
1857 comment string and the end of the current line is ignored.
1858 skipfooter : int, default 0
1859 Rows at the end to skip (0-indexed).
1860 dtype_backend : {'numpy_nullable', 'pyarrow'}
1861 Back-end data type applied to the resultant :class:`DataFrame`
1862 (still experimental). If not specified, the default behavior
1863 is to not use nullable data types. If specified, the behavior
1864 is as follows:
1866 * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame`
1867 * ``"pyarrow"``: returns pyarrow-backed nullable
1868 :class:`ArrowDtype` :class:`DataFrame`
1870 .. versionadded:: 2.0
1871 **kwds : dict, optional
1872 Arbitrary keyword arguments passed to excel engine.
1874 Returns
1875 -------
1876 DataFrame or dict of DataFrames
1877 DataFrame from the passed in Excel file.
1879 See Also
1880 --------
1881 read_excel : Read an Excel sheet values (xlsx) file into DataFrame.
1882 read_csv : Read a comma-separated values (csv) file into DataFrame.
1883 read_fwf : Read a table of fixed-width formatted lines into DataFrame.
1885 Examples
1886 --------
1887 >>> df = pd.DataFrame([[1, 2, 3], [4, 5, 6]], columns=["A", "B", "C"])
1888 >>> df.to_excel("myfile.xlsx") # doctest: +SKIP
1889 >>> file = pd.ExcelFile("myfile.xlsx") # doctest: +SKIP
1890 >>> file.parse() # doctest: +SKIP
1891 """
1892 warnings.warn(
1893 # GH#58247
1894 f"{type(self).__name__}.parse is deprecated. Use pd.read_excel instead.",
1895 Pandas4Warning,
1896 stacklevel=find_stack_level(),
1897 )
1898 return self._reader.parse(
1899 sheet_name=sheet_name,
1900 header=header,
1901 names=names,
1902 index_col=index_col,
1903 usecols=usecols,
1904 converters=converters,
1905 true_values=true_values,
1906 false_values=false_values,
1907 skiprows=skiprows,
1908 nrows=nrows,
1909 na_values=na_values,
1910 parse_dates=parse_dates,
1911 date_format=date_format,
1912 thousands=thousands,
1913 comment=comment,
1914 skipfooter=skipfooter,
1915 dtype_backend=dtype_backend,
1916 **kwds,
1917 )
1919 @property
1920 def book(self):
1921 """
1922 Gets the Excel workbook.
1924 Workbook is the top-level container for all document information.
1926 Returns
1927 -------
1928 Excel Workbook
1929 The workbook object of the type defined by the engine being used.
1931 See Also
1932 --------
1933 read_excel : Read an Excel file into a pandas DataFrame.
1935 Examples
1936 --------
1937 >>> file = pd.ExcelFile("myfile.xlsx") # doctest: +SKIP
1938 >>> file.book # doctest: +SKIP
1939 <openpyxl.workbook.workbook.Workbook object at 0x11eb5ad70>
1940 >>> file.book.path # doctest: +SKIP
1941 '/xl/workbook.xml'
1942 >>> file.book.active # doctest: +SKIP
1943 <openpyxl.worksheet._read_only.ReadOnlyWorksheet object at 0x11eb5b370>
1944 >>> file.book.sheetnames # doctest: +SKIP
1945 ['Sheet1', 'Sheet2']
1946 """
1947 return self._reader.book
1949 @property
1950 def sheet_names(self):
1951 """
1952 Names of the sheets in the document.
1954 This is particularly useful for loading a specific sheet into a DataFrame when
1955 you do not know the sheet names beforehand.
1957 Returns
1958 -------
1959 list of str
1960 List of sheet names in the document.
1962 See Also
1963 --------
1964 ExcelFile.parse : Parse a sheet into a DataFrame.
1965 read_excel : Read an Excel file into a pandas DataFrame. If you know the sheet
1966 names, it may be easier to specify them directly to read_excel.
1968 Examples
1969 --------
1970 >>> file = pd.ExcelFile("myfile.xlsx") # doctest: +SKIP
1971 >>> file.sheet_names # doctest: +SKIP
1972 ["Sheet1", "Sheet2"]
1973 """
1974 return self._reader.sheet_names
1976 def close(self) -> None:
1977 """close io if necessary"""
1978 self._reader.close()
1980 def __enter__(self) -> Self:
1981 return self
1983 def __exit__(
1984 self,
1985 exc_type: type[BaseException] | None,
1986 exc_value: BaseException | None,
1987 traceback: TracebackType | None,
1988 ) -> None:
1989 self.close()