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
14from typing import (
15 IO,
16 TYPE_CHECKING,
17 Any,
18 Generic,
19 Literal,
20 Self,
21 TypeVar,
22 Union,
23 cast,
24 overload,
25)
26import warnings
27import zipfile
29from pandas._config import config
31from pandas._libs import lib
32from pandas.compat._optional import (
33 get_version,
34 import_optional_dependency,
35)
36from pandas.errors import EmptyDataError
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_file_like,
47 is_float,
48 is_integer,
49 is_list_like,
50)
52from pandas.core.frame import DataFrame
53from pandas.util.version import Version
55from pandas.io.common import (
56 IOHandles,
57 get_handle,
58 stringify_path,
59 validate_header_arg,
60)
61from pandas.io.excel._util import (
62 fill_mi_header,
63 get_default_engine,
64 get_writer,
65 maybe_convert_usecols,
66 pop_header_name,
67)
68from pandas.io.parsers import TextParser
69from pandas.io.parsers.readers import validate_integer
71if TYPE_CHECKING:
72 from types import TracebackType
74 from pandas._typing import (
75 DtypeArg,
76 DtypeBackend,
77 ExcelWriterIfSheetExists,
78 FilePath,
79 HashableT,
80 IntStrT,
81 ReadBuffer,
82 SequenceNotStr,
83 StorageOptions,
84 WriteExcelBuffer,
85 )
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 Parameters
205 ----------
206 io : str, ExcelFile, xlrd.Book, path object, or file-like object
207 Any valid string path is acceptable. The string could be a URL. Valid
208 URL schemes include http, ftp, s3, and file. For file URLs, a host is
209 expected. A local file could be: ``file://localhost/path/to/table.xlsx``.
211 If you want to pass in a path object, pandas accepts any ``os.PathLike``.
213 By file-like object, we refer to objects with a ``read()`` method,
214 such as a file handle (e.g. via builtin ``open`` function)
215 or ``StringIO``.
217 sheet_name : str, int, list, or None, default 0
218 Strings are used for sheet names. Integers are used in zero-indexed
219 sheet positions (chart sheets do not count as a sheet position).
220 Lists of strings/integers are used to request multiple sheets.
221 When ``None``, will return a dictionary containing DataFrames for each sheet.
223 Available cases:
225 * Defaults to ``0``: 1st sheet as a `DataFrame`
226 * ``1``: 2nd sheet as a `DataFrame`
227 * ``"Sheet1"``: Load sheet with name "Sheet1"
228 * ``[0, 1, "Sheet5"]``: Load first, second and sheet named "Sheet5"
229 as a dict of `DataFrame`
230 * ``None``: Returns a dictionary containing DataFrames for each sheet.
232 header : int, list of int, default 0
233 Row (0-indexed) to use for the column labels of the parsed
234 DataFrame. If a list of integers is passed those row positions will
235 be combined into a ``MultiIndex``. Use None if there is no header.
236 names : array-like, default None
237 List of column names to use. If file contains no header row,
238 then you should explicitly pass header=None.
239 index_col : int, str, list of int, default None
240 Column (0-indexed) to use as the row labels of the DataFrame.
241 Pass None if there is no such column. If a list is passed,
242 those columns will be combined into a ``MultiIndex``. If a
243 subset of data is selected with ``usecols``, index_col
244 is based on the subset.
246 Missing values will be forward filled to allow roundtripping with
247 ``to_excel`` for ``merged_cells=True``. To avoid forward filling the
248 missing values use ``set_index`` after reading the data instead of
249 ``index_col``.
250 usecols : str, list-like, or callable, default None
251 * If None, then parse all columns.
252 * If str, then indicates comma separated list of Excel column letters
253 and column ranges (e.g. "A:E" or "A,C,E:F"). Ranges are inclusive of
254 both sides.
255 * If list of int, then indicates list of column numbers to be parsed
256 (0-indexed).
257 * If list of string, then indicates list of column names to be parsed.
258 * If callable, then evaluate each column name against it and parse the
259 column if the callable returns ``True``.
261 Returns a subset of the columns according to behavior above.
262 dtype : Type name or dict of column -> type, default None
263 Data type for data or columns. E.g. {'a': np.float64, 'b': np.int32}
264 Use ``object`` to preserve data as stored in Excel and not interpret dtype,
265 which will necessarily result in ``object`` dtype.
266 If converters are specified, they will be applied INSTEAD
267 of dtype conversion.
268 If you use ``None``, it will infer the dtype of each column based on the data.
269 engine : {'openpyxl', 'calamine', 'odf', 'pyxlsb', 'xlrd'}, default None
270 If io is not a buffer or path, this must be set to identify io.
271 Engine compatibility :
273 - ``openpyxl`` supports newer Excel file formats.
274 - ``calamine`` supports Excel (.xls, .xlsx, .xlsm, .xlsb)
275 and OpenDocument (.ods) file formats.
276 - ``odf`` supports OpenDocument file formats (.odf, .ods, .odt).
277 - ``pyxlsb`` supports Binary Excel files.
278 - ``xlrd`` supports old-style Excel files (.xls).
280 When ``engine=None``, the following logic will be used to determine the engine:
282 - If ``path_or_buffer`` is an OpenDocument format (.odf, .ods, .odt),
283 then `odf <https://pypi.org/project/odfpy/>`_ will be used.
284 - Otherwise if ``path_or_buffer`` is an xls format, ``xlrd`` will be used.
285 - Otherwise if ``path_or_buffer`` is in xlsb format, ``pyxlsb`` will be used.
286 - Otherwise ``openpyxl`` will be used.
288 converters : dict, default None
289 Dict of functions for converting values in certain columns. Keys can
290 either be integers or column labels, values are functions that take one
291 input argument, the Excel cell content, and return the transformed
292 content.
293 true_values : list, default None
294 Values to consider as True.
295 false_values : list, default None
296 Values to consider as False.
297 skiprows : list-like, int, or callable, optional
298 Line numbers to skip (0-indexed) or number of lines to skip (int) at the
299 start of the file. If callable, the callable function will be evaluated
300 against the row indices, returning True if the row should be skipped and
301 False otherwise. An example of a valid callable argument would be ``lambda
302 x: x in [0, 2]``.
303 nrows : int, default None
304 Number of rows to parse. Does not include header rows.
305 na_values : scalar, str, list-like, or dict, default None
306 Additional strings to recognize as NA/NaN. If dict passed, specific
307 per-column NA values. By default the following values are interpreted
308 as NaN: '', '#N/A', '#N/A N/A', '#NA', '-1.#IND', '-1.#QNAN', '-NaN', '-nan',
309 '1.#IND', '1.#QNAN', '<NA>', 'N/A', 'NA', 'NULL', 'NaN', 'None',
310 'n/a', 'nan', 'null'.
311 keep_default_na : bool, default True
312 Whether or not to include the default NaN values when parsing the data.
313 Depending on whether ``na_values`` is passed in, the behavior is as follows:
315 * If ``keep_default_na`` is True, and ``na_values`` are specified,
316 ``na_values`` is appended to the default NaN values used for parsing.
317 * If ``keep_default_na`` is True, and ``na_values`` are not specified, only
318 the default NaN values are used for parsing.
319 * If ``keep_default_na`` is False, and ``na_values`` are specified, only
320 the NaN values specified ``na_values`` are used for parsing.
321 * If ``keep_default_na`` is False, and ``na_values`` are not specified, no
322 strings will be parsed as NaN.
324 Note that if `na_filter` is passed in as False, the ``keep_default_na`` and
325 ``na_values`` parameters will be ignored.
326 na_filter : bool, default True
327 Detect missing value markers (empty strings and the value of na_values). In
328 data without any NAs, passing ``na_filter=False`` can improve the
329 performance of reading a large file.
330 verbose : bool, default False
331 Indicate number of NA values placed in non-numeric columns.
332 parse_dates : bool, list-like, or dict, default False
333 The behavior is as follows:
335 * ``bool``. If True -> try parsing the index.
336 * ``list`` of int or names. e.g. If [1, 2, 3] -> try parsing columns 1, 2, 3
337 each as a separate date column.
338 * ``list`` of lists. e.g. If [[1, 3]] -> combine columns 1 and 3 and parse as
339 a single date column.
340 * ``dict``, e.g. {'foo' : [1, 3]} -> parse columns 1, 3 as date and call
341 result 'foo'
343 If a column or index contains an unparsable date, the entire column or
344 index will be returned unaltered as an object data type. If you don`t want to
345 parse some cells as date just change their type in Excel to "Text".
346 For non-standard datetime parsing, use ``pd.to_datetime`` after
347 ``pd.read_excel``.
349 Note: A fast-path exists for iso8601-formatted dates.
350 date_format : str or dict of column -> format, default ``None``
351 If used in conjunction with ``parse_dates``, will parse dates according to this
352 format. For anything more complex,
353 please read in as ``object`` and then apply :func:`to_datetime` as-needed.
355 .. versionadded:: 2.0.0
357 thousands : str, default None
358 Thousands separator for parsing string columns to numeric. Note that
359 this parameter is only necessary for columns stored as TEXT in Excel,
360 any numeric columns will automatically be parsed, regardless of display
361 format.
362 decimal : str, default '.'
363 Character to recognize as decimal point for parsing string columns to numeric.
364 Note that this parameter is only necessary for columns stored as TEXT in Excel,
365 any numeric columns will automatically be parsed, regardless of display
366 format.(e.g. use ',' for European data).
367 comment : str, default None
368 Comments out remainder of line. Pass a character or characters to this
369 argument to indicate comments in the input file. Any data between the
370 comment string and the end of the current line is ignored.
371 skipfooter : int, default 0
372 Rows at the end to skip (0-indexed).
373 storage_options : dict, optional
374 Extra options that make sense for a particular storage connection, e.g.
375 host, port, username, password, etc. For HTTP(S) URLs the key-value pairs
376 are forwarded to ``urllib.request.Request`` as header options. For other
377 URLs (e.g. starting with "s3://", and "gcs://") the key-value pairs are
378 forwarded to ``fsspec.open``. Please see ``fsspec`` and ``urllib`` for more
379 details, and for more examples on storage options refer `here
380 <https://pandas.pydata.org/docs/user_guide/io.html?
381 highlight=storage_options#reading-writing-remote-files>`_.
383 dtype_backend : {'numpy_nullable', 'pyarrow'}
384 Back-end data type applied to the resultant :class:`DataFrame`
385 (still experimental). If not specified, the default behavior
386 is to not use nullable data types. If specified, the behavior
387 is as follows:
389 * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame`
390 * ``"pyarrow"``: returns pyarrow-backed nullable
392 :class:`ArrowDtype` :class:`DataFrame`
394 .. versionadded:: 2.0
396 engine_kwargs : dict, optional
397 Arbitrary keyword arguments passed to excel engine.
399 Returns
400 -------
401 DataFrame or dict of DataFrames
402 DataFrame from the passed in Excel file. See notes in sheet_name
403 argument for more information on when a dict of DataFrames is returned.
405 See Also
406 --------
407 DataFrame.to_excel : Write DataFrame to an Excel file.
408 DataFrame.to_csv : Write DataFrame to a comma-separated values (csv) file.
409 read_csv : Read a comma-separated values (csv) file into DataFrame.
410 read_fwf : Read a table of fixed-width formatted lines into DataFrame.
412 Notes
413 -----
414 For specific information on the methods used for each Excel engine, refer to the
415 pandas
416 :ref:`user guide <io.excel_reader>`
418 Examples
419 --------
420 The file can be read using the file name as string or an open file object:
422 >>> pd.read_excel("tmp.xlsx", index_col=0) # doctest: +SKIP
423 Name Value
424 0 string1 1
425 1 string2 2
426 2 #Comment 3
428 >>> pd.read_excel(open("tmp.xlsx", "rb"), sheet_name="Sheet3") # doctest: +SKIP
429 Unnamed: 0 Name Value
430 0 0 string1 1
431 1 1 string2 2
432 2 2 #Comment 3
434 Index and header can be specified via the `index_col` and `header` arguments
436 >>> pd.read_excel("tmp.xlsx", index_col=None, header=None) # doctest: +SKIP
437 0 1 2
438 0 NaN Name Value
439 1 0.0 string1 1
440 2 1.0 string2 2
441 3 2.0 #Comment 3
443 Column types are inferred but can be explicitly specified
445 >>> pd.read_excel(
446 ... "tmp.xlsx", index_col=0, dtype={"Name": str, "Value": float}
447 ... ) # doctest: +SKIP
448 Name Value
449 0 string1 1.0
450 1 string2 2.0
451 2 #Comment 3.0
453 True, False, and NA values, and thousands separators have defaults,
454 but can be explicitly specified, too. Supply the values you would like
455 as strings or lists of strings!
457 >>> pd.read_excel(
458 ... "tmp.xlsx", index_col=0, na_values=["string1", "string2"]
459 ... ) # doctest: +SKIP
460 Name Value
461 0 NaN 1
462 1 NaN 2
463 2 #Comment 3
465 Comment lines in the excel input file can be skipped using the
466 ``comment`` kwarg.
468 >>> pd.read_excel("tmp.xlsx", index_col=0, comment="#") # doctest: +SKIP
469 Name Value
470 0 string1 1.0
471 1 string2 2.0
472 2 None NaN
473 """
474 check_dtype_backend(dtype_backend)
475 should_close = False
476 if engine_kwargs is None:
477 engine_kwargs = {}
479 if not isinstance(io, ExcelFile):
480 should_close = True
481 io = ExcelFile(
482 io,
483 storage_options=storage_options,
484 engine=engine,
485 engine_kwargs=engine_kwargs,
486 )
487 elif engine and engine != io.engine:
488 raise ValueError(
489 "Engine should not be specified when passing "
490 "an ExcelFile - ExcelFile already has the engine set"
491 )
493 try:
494 data = io.parse(
495 sheet_name=sheet_name,
496 header=header,
497 names=names,
498 index_col=index_col,
499 usecols=usecols,
500 dtype=dtype,
501 converters=converters,
502 true_values=true_values,
503 false_values=false_values,
504 skiprows=skiprows,
505 nrows=nrows,
506 na_values=na_values,
507 keep_default_na=keep_default_na,
508 na_filter=na_filter,
509 verbose=verbose,
510 parse_dates=parse_dates,
511 date_format=date_format,
512 thousands=thousands,
513 decimal=decimal,
514 comment=comment,
515 skipfooter=skipfooter,
516 dtype_backend=dtype_backend,
517 )
518 finally:
519 # make sure to close opened file handles
520 if should_close:
521 io.close()
522 return data
525_WorkbookT = TypeVar("_WorkbookT")
528class BaseExcelReader(Generic[_WorkbookT]):
529 book: _WorkbookT
531 def __init__(
532 self,
533 filepath_or_buffer,
534 storage_options: StorageOptions | None = None,
535 engine_kwargs: dict | None = None,
536 ) -> None:
537 if engine_kwargs is None:
538 engine_kwargs = {}
540 self.handles = IOHandles(
541 handle=filepath_or_buffer, compression={"method": None}
542 )
543 if not isinstance(filepath_or_buffer, (ExcelFile, self._workbook_class)):
544 self.handles = get_handle(
545 filepath_or_buffer, "rb", storage_options=storage_options, is_text=False
546 )
548 if isinstance(self.handles.handle, self._workbook_class):
549 self.book = self.handles.handle
550 elif hasattr(self.handles.handle, "read"):
551 # N.B. xlrd.Book has a read attribute too
552 self.handles.handle.seek(0)
553 try:
554 self.book = self.load_workbook(self.handles.handle, engine_kwargs)
555 except Exception:
556 self.close()
557 raise
558 else:
559 raise ValueError(
560 "Must explicitly set engine if not passing in buffer or path for io."
561 )
563 @property
564 def _workbook_class(self) -> type[_WorkbookT]:
565 raise NotImplementedError
567 def load_workbook(self, filepath_or_buffer, engine_kwargs) -> _WorkbookT:
568 raise NotImplementedError
570 def close(self) -> None:
571 if hasattr(self, "book"):
572 if hasattr(self.book, "close"):
573 # pyxlsb: opens a TemporaryFile
574 # openpyxl: https://stackoverflow.com/questions/31416842/
575 # openpyxl-does-not-close-excel-workbook-in-read-only-mode
576 self.book.close()
577 elif hasattr(self.book, "release_resources"):
578 # xlrd
579 # https://github.com/python-excel/xlrd/blob/2.0.1/xlrd/book.py#L548
580 self.book.release_resources()
581 self.handles.close()
583 @property
584 def sheet_names(self) -> list[str]:
585 raise NotImplementedError
587 def get_sheet_by_name(self, name: str):
588 raise NotImplementedError
590 def get_sheet_by_index(self, index: int):
591 raise NotImplementedError
593 def get_sheet_data(self, sheet, rows: int | None = None):
594 raise NotImplementedError
596 def raise_if_bad_sheet_by_index(self, index: int) -> None:
597 n_sheets = len(self.sheet_names)
598 if index >= n_sheets:
599 raise ValueError(
600 f"Worksheet index {index} is invalid, {n_sheets} worksheets found"
601 )
603 def raise_if_bad_sheet_by_name(self, name: str) -> None:
604 if name not in self.sheet_names:
605 raise ValueError(f"Worksheet named '{name}' not found")
607 def _check_skiprows_func(
608 self,
609 skiprows: Callable,
610 rows_to_use: int,
611 ) -> int:
612 """
613 Determine how many file rows are required to obtain `nrows` data
614 rows when `skiprows` is a function.
616 Parameters
617 ----------
618 skiprows : function
619 The function passed to read_excel by the user.
620 rows_to_use : int
621 The number of rows that will be needed for the header and
622 the data.
624 Returns
625 -------
626 int
627 """
628 i = 0
629 rows_used_so_far = 0
630 while rows_used_so_far < rows_to_use:
631 if not skiprows(i):
632 rows_used_so_far += 1
633 i += 1
634 return i
636 def _calc_rows(
637 self,
638 header: int | Sequence[int] | None,
639 index_col: int | Sequence[int] | None,
640 skiprows: Sequence[int] | int | Callable[[int], object] | None,
641 nrows: int | None,
642 ) -> int | None:
643 """
644 If nrows specified, find the number of rows needed from the
645 file, otherwise return None.
648 Parameters
649 ----------
650 header : int, list of int, or None
651 See read_excel docstring.
652 index_col : int, str, list of int, or None
653 See read_excel docstring.
654 skiprows : list-like, int, callable, or None
655 See read_excel docstring.
656 nrows : int or None
657 See read_excel docstring.
659 Returns
660 -------
661 int or None
662 """
663 if nrows is None:
664 return None
665 if header is None:
666 header_rows = 1
667 elif is_integer(header):
668 header = cast(int, header)
669 header_rows = 1 + header
670 else:
671 header = cast(Sequence, header)
672 header_rows = 1 + header[-1]
673 # If there is a MultiIndex header and an index then there is also
674 # a row containing just the index name(s)
675 if is_list_like(header) and index_col is not None:
676 header = cast(Sequence, header)
677 if len(header) > 1:
678 header_rows += 1
679 if skiprows is None:
680 return header_rows + nrows
681 if is_integer(skiprows):
682 skiprows = cast(int, skiprows)
683 return header_rows + nrows + skiprows
684 if is_list_like(skiprows):
686 def f(skiprows: Sequence, x: int) -> bool:
687 return x in skiprows
689 skiprows = cast(Sequence, skiprows)
690 return self._check_skiprows_func(partial(f, skiprows), header_rows + nrows)
691 if callable(skiprows):
692 return self._check_skiprows_func(
693 skiprows,
694 header_rows + nrows,
695 )
696 # else unexpected skiprows type: read_excel will not optimize
697 # the number of rows read from file
698 return None
700 def parse(
701 self,
702 sheet_name: str | int | list[int] | list[str] | None = 0,
703 header: int | Sequence[int] | None = 0,
704 names: SequenceNotStr[Hashable] | range | None = None,
705 index_col: int | Sequence[int] | None = None,
706 usecols=None,
707 dtype: DtypeArg | None = None,
708 true_values: Iterable[Hashable] | None = None,
709 false_values: Iterable[Hashable] | None = None,
710 skiprows: Sequence[int] | int | Callable[[int], object] | None = None,
711 nrows: int | None = None,
712 na_values=None,
713 verbose: bool = False,
714 parse_dates: list | dict | bool = False,
715 date_format: dict[Hashable, str] | str | None = None,
716 thousands: str | None = None,
717 decimal: str = ".",
718 comment: str | None = None,
719 skipfooter: int = 0,
720 dtype_backend: DtypeBackend | lib.NoDefault = lib.no_default,
721 **kwds,
722 ):
723 validate_header_arg(header)
724 validate_integer("nrows", nrows)
726 ret_dict = False
728 # Keep sheetname to maintain backwards compatibility.
729 sheets: list[int] | list[str]
730 if isinstance(sheet_name, list):
731 sheets = sheet_name
732 ret_dict = True
733 elif sheet_name is None:
734 sheets = self.sheet_names
735 ret_dict = True
736 elif isinstance(sheet_name, str):
737 sheets = [sheet_name]
738 else:
739 sheets = [sheet_name]
741 # handle same-type duplicates.
742 sheets = cast(Union[list[int], list[str]], list(dict.fromkeys(sheets).keys()))
744 output = {}
746 last_sheetname = None
747 for asheetname in sheets:
748 last_sheetname = asheetname
749 if verbose:
750 print(f"Reading sheet {asheetname}")
752 if isinstance(asheetname, str):
753 sheet = self.get_sheet_by_name(asheetname)
754 else: # assume an integer if not a string
755 sheet = self.get_sheet_by_index(asheetname)
757 file_rows_needed = self._calc_rows(header, index_col, skiprows, nrows)
758 data = self.get_sheet_data(sheet, file_rows_needed)
759 if hasattr(sheet, "close"):
760 # pyxlsb opens two TemporaryFiles
761 sheet.close()
762 usecols = maybe_convert_usecols(usecols)
764 if not data:
765 output[asheetname] = DataFrame()
766 continue
768 output = self._parse_sheet(
769 data=data,
770 output=output,
771 asheetname=asheetname,
772 header=header,
773 names=names,
774 index_col=index_col,
775 usecols=usecols,
776 dtype=dtype,
777 skiprows=skiprows,
778 nrows=nrows,
779 true_values=true_values,
780 false_values=false_values,
781 na_values=na_values,
782 parse_dates=parse_dates,
783 date_format=date_format,
784 thousands=thousands,
785 decimal=decimal,
786 comment=comment,
787 skipfooter=skipfooter,
788 dtype_backend=dtype_backend,
789 **kwds,
790 )
792 if last_sheetname is None:
793 raise ValueError("Sheet name is an empty list")
795 if ret_dict:
796 return output
797 else:
798 return output[last_sheetname]
800 def _parse_sheet(
801 self,
802 data: list,
803 output: dict,
804 asheetname: str | int | None = None,
805 header: int | Sequence[int] | None = 0,
806 names: SequenceNotStr[Hashable] | range | None = None,
807 index_col: int | Sequence[int] | None = None,
808 usecols=None,
809 dtype: DtypeArg | None = None,
810 skiprows: Sequence[int] | int | Callable[[int], object] | None = None,
811 nrows: int | None = None,
812 true_values: Iterable[Hashable] | None = None,
813 false_values: Iterable[Hashable] | None = None,
814 na_values=None,
815 parse_dates: list | dict | bool = False,
816 date_format: dict[Hashable, str] | str | None = None,
817 thousands: str | None = None,
818 decimal: str = ".",
819 comment: str | None = None,
820 skipfooter: int = 0,
821 dtype_backend: DtypeBackend | lib.NoDefault = lib.no_default,
822 **kwds,
823 ):
824 is_list_header = False
825 is_len_one_list_header = False
826 if is_list_like(header):
827 assert isinstance(header, Sequence)
828 is_list_header = True
829 if len(header) == 1:
830 is_len_one_list_header = True
832 if is_len_one_list_header:
833 header = cast(Sequence[int], header)[0]
835 # forward fill and pull out names for MultiIndex column
836 header_names = None
837 if header is not None and is_list_like(header):
838 assert isinstance(header, Sequence)
840 header_names = []
841 control_row = [True] * len(data[0])
843 for row in header:
844 if is_integer(skiprows):
845 assert isinstance(skiprows, int)
846 row += skiprows
848 if row > len(data) - 1:
849 raise ValueError(
850 f"header index {row} exceeds maximum index "
851 f"{len(data) - 1} of data.",
852 )
854 data[row], control_row = fill_mi_header(data[row], control_row)
856 if index_col is not None:
857 header_name, _ = pop_header_name(data[row], index_col)
858 header_names.append(header_name)
860 # If there is a MultiIndex header and an index then there is also
861 # a row containing just the index name(s)
862 has_index_names = False
863 if is_list_header and not is_len_one_list_header and index_col is not None:
864 index_col_set: set[int]
865 if isinstance(index_col, int):
866 index_col_set = {index_col}
867 else:
868 assert isinstance(index_col, Sequence)
869 index_col_set = set(index_col)
871 # We have to handle mi without names. If any of the entries in the data
872 # columns are not empty, this is a regular row
873 assert isinstance(header, Sequence)
874 if len(header) < len(data):
875 potential_index_names = data[len(header)]
876 has_index_names = all(
877 x == "" or x is None
878 for i, x in enumerate(potential_index_names)
879 if not control_row[i] and i not in index_col_set
880 )
882 if is_list_like(index_col):
883 # Forward fill values for MultiIndex index.
884 if header is None:
885 offset = 0
886 elif isinstance(header, int):
887 offset = 1 + header
888 else:
889 offset = 1 + max(header)
891 # GH34673: if MultiIndex names present and not defined in the header,
892 # offset needs to be incremented so that forward filling starts
893 # from the first MI value instead of the name
894 if has_index_names:
895 offset += 1
897 # Check if we have an empty dataset
898 # before trying to collect data.
899 if offset < len(data):
900 assert isinstance(index_col, Sequence)
902 for col in index_col:
903 last = data[offset][col]
905 for row in range(offset + 1, len(data)):
906 if data[row][col] == "" or data[row][col] is None:
907 data[row][col] = last
908 else:
909 last = data[row][col]
911 # GH 12292 : error when read one empty column from excel file
912 try:
913 parser = TextParser(
914 data,
915 names=names,
916 header=header,
917 index_col=index_col,
918 has_index_names=has_index_names,
919 dtype=dtype,
920 true_values=true_values,
921 false_values=false_values,
922 skiprows=skiprows,
923 nrows=nrows,
924 na_values=na_values,
925 skip_blank_lines=False, # GH 39808
926 parse_dates=parse_dates,
927 date_format=date_format,
928 thousands=thousands,
929 decimal=decimal,
930 comment=comment,
931 skipfooter=skipfooter,
932 usecols=usecols,
933 dtype_backend=dtype_backend,
934 **kwds,
935 )
937 output[asheetname] = parser.read(nrows=nrows)
939 if header_names:
940 output[asheetname].columns = output[asheetname].columns.set_names(
941 header_names
942 )
944 except EmptyDataError:
945 # No Data, return an empty DataFrame
946 output[asheetname] = DataFrame()
948 except Exception as err:
949 err.args = (f"{err.args[0]} (sheet: {asheetname})", *err.args[1:])
950 raise err
952 return output
955@set_module("pandas")
956class ExcelWriter(Generic[_WorkbookT]):
957 """
958 Class for writing DataFrame objects into excel sheets.
960 Default is to use:
962 * `xlsxwriter <https://pypi.org/project/XlsxWriter/>`__ for xlsx files if xlsxwriter
963 is installed otherwise `openpyxl <https://pypi.org/project/openpyxl/>`__
964 * `odf <https://pypi.org/project/odfpy/>`__ for ods files
966 See :meth:`DataFrame.to_excel` for typical usage.
968 The writer should be used as a context manager. Otherwise, call `close()` to save
969 and close any opened file handles.
971 Parameters
972 ----------
973 path : str or typing.BinaryIO
974 Path to xls or xlsx or ods file.
975 engine : str (optional)
976 Engine to use for writing. If None, defaults to
977 ``io.excel.<extension>.writer``. NOTE: can only be passed as a keyword
978 argument.
979 date_format : str, default None
980 Format string for dates written into Excel files (e.g. 'YYYY-MM-DD').
981 datetime_format : str, default None
982 Format string for datetime objects written into Excel files.
983 (e.g. 'YYYY-MM-DD HH:MM:SS').
984 mode : {'w', 'a'}, default 'w'
985 File mode to use (write or append). Append does not work with fsspec URLs.
986 storage_options : dict, optional
987 Extra options that make sense for a particular storage connection, e.g.
988 host, port, username, password, etc. For HTTP(S) URLs the key-value pairs
989 are forwarded to ``urllib.request.Request`` as header options. For other
990 URLs (e.g. starting with "s3://", and "gcs://") the key-value pairs are
991 forwarded to ``fsspec.open``. Please see ``fsspec`` and ``urllib`` for more
992 details, and for more examples on storage options refer `here
993 <https://pandas.pydata.org/docs/user_guide/io.html?
994 highlight=storage_options#reading-writing-remote-files>`_.
996 if_sheet_exists : {'error', 'new', 'replace', 'overlay'}, default 'error'
997 How to behave when trying to write to a sheet that already
998 exists (append mode only).
1000 * error: raise a ValueError.
1001 * new: Create a new sheet, with a name determined by the engine.
1002 * replace: Delete the contents of the sheet before writing to it.
1003 * overlay: Write contents to the existing sheet without first removing,
1004 but possibly over top of, the existing contents.
1006 engine_kwargs : dict, optional
1007 Keyword arguments to be passed into the engine. These will be passed to
1008 the following functions of the respective engines:
1010 * xlsxwriter: ``xlsxwriter.Workbook(file, **engine_kwargs)``
1011 * openpyxl (write mode): ``openpyxl.Workbook(**engine_kwargs)``
1012 * openpyxl (append mode): ``openpyxl.load_workbook(file, **engine_kwargs)``
1013 * odf: ``odf.opendocument.OpenDocumentSpreadsheet(**engine_kwargs)``
1015 See Also
1016 --------
1017 read_excel : Read an Excel sheet values (xlsx) file into DataFrame.
1018 read_csv : Read a comma-separated values (csv) file into DataFrame.
1019 read_fwf : Read a table of fixed-width formatted lines into DataFrame.
1021 Notes
1022 -----
1023 For compatibility with CSV writers, ExcelWriter serializes lists
1024 and dicts to strings before writing.
1026 Examples
1027 --------
1028 Default usage:
1030 >>> df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"]) # doctest: +SKIP
1031 >>> with pd.ExcelWriter("path_to_file.xlsx") as writer:
1032 ... df.to_excel(writer) # doctest: +SKIP
1034 To write to separate sheets in a single file:
1036 >>> df1 = pd.DataFrame([["AAA", "BBB"]], columns=["Spam", "Egg"]) # doctest: +SKIP
1037 >>> df2 = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"]) # doctest: +SKIP
1038 >>> with pd.ExcelWriter("path_to_file.xlsx") as writer:
1039 ... df1.to_excel(writer, sheet_name="Sheet1") # doctest: +SKIP
1040 ... df2.to_excel(writer, sheet_name="Sheet2") # doctest: +SKIP
1042 You can set the date format or datetime format:
1044 >>> from datetime import date, datetime # doctest: +SKIP
1045 >>> df = pd.DataFrame(
1046 ... [
1047 ... [date(2014, 1, 31), date(1999, 9, 24)],
1048 ... [datetime(1998, 5, 26, 23, 33, 4), datetime(2014, 2, 28, 13, 5, 13)],
1049 ... ],
1050 ... index=["Date", "Datetime"],
1051 ... columns=["X", "Y"],
1052 ... ) # doctest: +SKIP
1053 >>> with pd.ExcelWriter(
1054 ... "path_to_file.xlsx",
1055 ... date_format="YYYY-MM-DD",
1056 ... datetime_format="YYYY-MM-DD HH:MM:SS",
1057 ... ) as writer:
1058 ... df.to_excel(writer) # doctest: +SKIP
1060 You can also append to an existing Excel file:
1062 >>> with pd.ExcelWriter("path_to_file.xlsx", mode="a", engine="openpyxl") as writer:
1063 ... df.to_excel(writer, sheet_name="Sheet3") # doctest: +SKIP
1065 Here, the `if_sheet_exists` parameter can be set to replace a sheet if it
1066 already exists:
1068 >>> with pd.ExcelWriter(
1069 ... "path_to_file.xlsx",
1070 ... mode="a",
1071 ... engine="openpyxl",
1072 ... if_sheet_exists="replace",
1073 ... ) as writer:
1074 ... df.to_excel(writer, sheet_name="Sheet1") # doctest: +SKIP
1076 You can also write multiple DataFrames to a single sheet. Note that the
1077 ``if_sheet_exists`` parameter needs to be set to ``overlay``:
1079 >>> with pd.ExcelWriter(
1080 ... "path_to_file.xlsx",
1081 ... mode="a",
1082 ... engine="openpyxl",
1083 ... if_sheet_exists="overlay",
1084 ... ) as writer:
1085 ... df1.to_excel(writer, sheet_name="Sheet1")
1086 ... df2.to_excel(writer, sheet_name="Sheet1", startcol=3) # doctest: +SKIP
1088 You can store Excel file in RAM:
1090 >>> import io
1091 >>> df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])
1092 >>> buffer = io.BytesIO()
1093 >>> with pd.ExcelWriter(buffer) as writer:
1094 ... df.to_excel(writer)
1096 You can pack Excel file into zip archive:
1098 >>> import zipfile # doctest: +SKIP
1099 >>> df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"]) # doctest: +SKIP
1100 >>> with zipfile.ZipFile("path_to_file.zip", "w") as zf:
1101 ... with zf.open("filename.xlsx", "w") as buffer:
1102 ... with pd.ExcelWriter(buffer) as writer:
1103 ... df.to_excel(writer) # doctest: +SKIP
1105 You can specify additional arguments to the underlying engine:
1107 >>> with pd.ExcelWriter(
1108 ... "path_to_file.xlsx",
1109 ... engine="xlsxwriter",
1110 ... engine_kwargs={"options": {"nan_inf_to_errors": True}},
1111 ... ) as writer:
1112 ... df.to_excel(writer) # doctest: +SKIP
1114 In append mode, ``engine_kwargs`` are passed through to
1115 openpyxl's ``load_workbook``:
1117 >>> with pd.ExcelWriter(
1118 ... "path_to_file.xlsx",
1119 ... engine="openpyxl",
1120 ... mode="a",
1121 ... engine_kwargs={"keep_vba": True},
1122 ... ) as writer:
1123 ... df.to_excel(writer, sheet_name="Sheet2") # doctest: +SKIP
1124 """
1126 # Defining an ExcelWriter implementation (see abstract methods for more...)
1128 # - Mandatory
1129 # - ``write_cells(self, cells, sheet_name=None, startrow=0, startcol=0)``
1130 # --> called to write additional DataFrames to disk
1131 # - ``_supported_extensions`` (tuple of supported extensions), used to
1132 # check that engine supports the given extension.
1133 # - ``_engine`` - string that gives the engine name. Necessary to
1134 # instantiate class directly and bypass ``ExcelWriterMeta`` engine
1135 # lookup.
1136 # - ``save(self)`` --> called to save file to disk
1137 # - Mostly mandatory (i.e. should at least exist)
1138 # - book, cur_sheet, path
1140 # - Optional:
1141 # - ``__init__(self, path, engine=None, **kwargs)`` --> always called
1142 # with path as first argument.
1144 # You also need to register the class with ``register_writer()``.
1145 # Technically, ExcelWriter implementations don't need to subclass
1146 # ExcelWriter.
1148 _engine: str
1149 _supported_extensions: tuple[str, ...]
1151 def __new__(
1152 cls,
1153 path: FilePath | WriteExcelBuffer | ExcelWriter,
1154 engine: str | None = None,
1155 date_format: str | None = None,
1156 datetime_format: str | None = None,
1157 mode: str = "w",
1158 storage_options: StorageOptions | None = None,
1159 if_sheet_exists: ExcelWriterIfSheetExists | None = None,
1160 engine_kwargs: dict | None = None,
1161 ) -> Self:
1162 # only switch class if generic(ExcelWriter)
1163 if cls is ExcelWriter:
1164 if engine is None or (isinstance(engine, str) and engine == "auto"):
1165 if isinstance(path, str):
1166 ext = os.path.splitext(path)[-1][1:]
1167 else:
1168 ext = "xlsx"
1170 try:
1171 engine = config.get_option(f"io.excel.{ext}.writer")
1172 if engine == "auto":
1173 engine = get_default_engine(ext, mode="writer")
1174 except KeyError as err:
1175 raise ValueError(f"No engine for filetype: '{ext}'") from err
1177 # for mypy
1178 assert engine is not None
1179 # error: Incompatible types in assignment (expression has type
1180 # "type[ExcelWriter[Any]]", variable has type "type[Self]")
1181 cls = get_writer(engine) # type: ignore[assignment]
1183 return object.__new__(cls)
1185 @property
1186 def supported_extensions(self) -> tuple[str, ...]:
1187 """Extensions that writer engine supports."""
1188 return self._supported_extensions
1190 @property
1191 def engine(self) -> str:
1192 """Name of engine."""
1193 return self._engine
1195 @property
1196 def sheets(self) -> dict[str, Any]:
1197 """Mapping of sheet names to sheet objects."""
1198 raise NotImplementedError
1200 @property
1201 def book(self) -> _WorkbookT:
1202 """
1203 Book instance. Class type will depend on the engine used.
1205 This attribute can be used to access engine-specific features.
1206 """
1207 raise NotImplementedError
1209 def _write_cells(
1210 self,
1211 cells,
1212 sheet_name: str | None = None,
1213 startrow: int = 0,
1214 startcol: int = 0,
1215 freeze_panes: tuple[int, int] | None = None,
1216 autofilter_range: str | None = None,
1217 ) -> None:
1218 """
1219 Write given formatted cells into Excel an excel sheet
1221 Parameters
1222 ----------
1223 cells : generator
1224 cell of formatted data to save to Excel sheet
1225 sheet_name : str, default None
1226 Name of Excel sheet, if None, then use self.cur_sheet
1227 startrow : upper left cell row to dump data frame
1228 startcol : upper left cell column to dump data frame
1229 freeze_panes: int tuple of length 2
1230 contains the bottom-most row and right-most column to freeze
1231 autofilter_range: str, default None
1232 column ranges to add automatic filters to, for example "A1:D5"
1233 """
1234 raise NotImplementedError
1236 def _save(self) -> None:
1237 """
1238 Save workbook to disk.
1239 """
1240 raise NotImplementedError
1242 def __init__(
1243 self,
1244 path: FilePath | WriteExcelBuffer | ExcelWriter,
1245 engine: str | None = None,
1246 date_format: str | None = None,
1247 datetime_format: str | None = None,
1248 mode: str = "w",
1249 storage_options: StorageOptions | None = None,
1250 if_sheet_exists: ExcelWriterIfSheetExists | None = None,
1251 engine_kwargs: dict[str, Any] | None = None,
1252 ) -> None:
1253 # validate that this engine can handle the extension
1254 if isinstance(path, str):
1255 ext = os.path.splitext(path)[-1]
1256 self.check_extension(ext)
1258 # use mode to open the file
1259 if "b" not in mode:
1260 mode += "b"
1261 # use "a" for the user to append data to excel but internally use "r+" to let
1262 # the excel backend first read the existing file and then write any data to it
1263 mode = mode.replace("a", "r+")
1265 if if_sheet_exists not in (None, "error", "new", "replace", "overlay"):
1266 raise ValueError(
1267 f"'{if_sheet_exists}' is not valid for if_sheet_exists. "
1268 "Valid options are 'error', 'new', 'replace' and 'overlay'."
1269 )
1270 if if_sheet_exists and "r+" not in mode:
1271 raise ValueError("if_sheet_exists is only valid in append mode (mode='a')")
1272 if if_sheet_exists is None:
1273 if_sheet_exists = "error"
1274 self._if_sheet_exists = if_sheet_exists
1276 # cast ExcelWriter to avoid adding 'if self._handles is not None'
1277 self._handles = IOHandles(
1278 cast(IO[bytes], path), compression={"compression": None}
1279 )
1280 if not isinstance(path, ExcelWriter):
1281 self._handles = get_handle(
1282 path, mode, storage_options=storage_options, is_text=False
1283 )
1284 self._cur_sheet = None
1286 if date_format is None:
1287 self._date_format = "YYYY-MM-DD"
1288 else:
1289 self._date_format = date_format
1290 if datetime_format is None:
1291 self._datetime_format = "YYYY-MM-DD HH:MM:SS"
1292 else:
1293 self._datetime_format = datetime_format
1295 self._mode = mode
1297 @property
1298 def date_format(self) -> str:
1299 """
1300 Format string for dates written into Excel files (e.g. 'YYYY-MM-DD').
1301 """
1302 return self._date_format
1304 @property
1305 def datetime_format(self) -> str:
1306 """
1307 Format string for dates written into Excel files (e.g. 'YYYY-MM-DD').
1308 """
1309 return self._datetime_format
1311 @property
1312 def if_sheet_exists(self) -> str:
1313 """
1314 How to behave when writing to a sheet that already exists in append mode.
1315 """
1316 return self._if_sheet_exists
1318 def __fspath__(self) -> str:
1319 return getattr(self._handles.handle, "name", "")
1321 def _get_sheet_name(self, sheet_name: str | None) -> str:
1322 if sheet_name is None:
1323 sheet_name = self._cur_sheet
1324 if sheet_name is None: # pragma: no cover
1325 raise ValueError("Must pass explicit sheet_name or set _cur_sheet property")
1326 return sheet_name
1328 def _value_with_fmt(
1329 self, val
1330 ) -> tuple[
1331 int | float | bool | str | datetime.datetime | datetime.date, str | None
1332 ]:
1333 """
1334 Convert numpy types to Python types for the Excel writers.
1336 Parameters
1337 ----------
1338 val : object
1339 Value to be written into cells
1341 Returns
1342 -------
1343 Tuple with the first element being the converted value and the second
1344 being an optional format
1345 """
1346 fmt = None
1348 if is_integer(val):
1349 val = int(val)
1350 elif is_float(val):
1351 val = float(val)
1352 elif is_bool(val):
1353 val = bool(val)
1354 elif is_decimal(val):
1355 val = Decimal(val)
1356 elif isinstance(val, datetime.datetime):
1357 fmt = self._datetime_format
1358 elif isinstance(val, datetime.date):
1359 fmt = self._date_format
1360 elif isinstance(val, datetime.timedelta):
1361 val = val.total_seconds() / 86400
1362 fmt = "0"
1363 else:
1364 val = str(val)
1365 # GH#56954
1366 # Excel's limitation on cell contents is 32767 characters
1367 # xref https://support.microsoft.com/en-au/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
1368 if len(val) > 32767:
1369 warnings.warn(
1370 f"Cell contents too long ({len(val)}), "
1371 "truncated to 32767 characters",
1372 UserWarning,
1373 stacklevel=find_stack_level(),
1374 )
1375 return val, fmt
1377 @classmethod
1378 def check_extension(cls, ext: str) -> Literal[True]:
1379 """
1380 checks that path's extension against the Writer's supported
1381 extensions. If it isn't supported, raises UnsupportedFiletypeError.
1382 """
1383 if ext.startswith("."):
1384 ext = ext[1:]
1385 if not any(ext in extension for extension in cls._supported_extensions):
1386 raise ValueError(f"Invalid extension for engine '{cls.engine}': '{ext}'")
1387 return True
1389 # Allow use as a contextmanager
1390 def __enter__(self) -> Self:
1391 return self
1393 def __exit__(
1394 self,
1395 exc_type: type[BaseException] | None,
1396 exc_value: BaseException | None,
1397 traceback: TracebackType | None,
1398 ) -> None:
1399 self.close()
1401 def close(self) -> None:
1402 """synonym for save, to make it more file-like"""
1403 self._save()
1404 self._handles.close()
1407XLS_SIGNATURES = (
1408 b"\x09\x00\x04\x00\x07\x00\x10\x00", # BIFF2
1409 b"\x09\x02\x06\x00\x00\x00\x10\x00", # BIFF3
1410 b"\x09\x04\x06\x00\x00\x00\x10\x00", # BIFF4
1411 b"\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1", # Compound File Binary
1412)
1413ZIP_SIGNATURE = b"PK\x03\x04"
1414PEEK_SIZE = max(map(len, (*XLS_SIGNATURES, ZIP_SIGNATURE)))
1417def inspect_excel_format(
1418 content_or_path: FilePath | ReadBuffer[bytes],
1419 storage_options: StorageOptions | None = None,
1420) -> str | None:
1421 """
1422 Inspect the path or content of an excel file and get its format.
1424 Adopted from xlrd: https://github.com/python-excel/xlrd.
1426 Parameters
1427 ----------
1428 content_or_path : str or file-like object
1429 Path to file or content of file to inspect. May be a URL.
1430 storage_options : dict, optional
1431 Extra options that make sense for a particular storage connection, e.g.
1432 host, port, username, password, etc. For HTTP(S) URLs the key-value pairs
1433 are forwarded to ``urllib.request.Request`` as header options. For other
1434 URLs (e.g. starting with "s3://", and "gcs://") the key-value pairs are
1435 forwarded to ``fsspec.open``. Please see ``fsspec`` and ``urllib`` for more
1436 details, and for more examples on storage options refer `here
1437 <https://pandas.pydata.org/docs/user_guide/io.html?
1438 highlight=storage_options#reading-writing-remote-files>`_.
1440 Returns
1441 -------
1442 str or None
1443 Format of file if it can be determined.
1445 Raises
1446 ------
1447 ValueError
1448 If resulting stream is empty.
1449 BadZipFile
1450 If resulting stream does not have an XLS signature and is not a valid zipfile.
1451 """
1452 with get_handle(
1453 content_or_path, "rb", storage_options=storage_options, is_text=False
1454 ) as handle:
1455 stream = handle.handle
1456 stream.seek(0)
1457 buf = stream.read(PEEK_SIZE)
1458 if buf is None:
1459 raise ValueError("stream is empty")
1460 assert isinstance(buf, bytes)
1461 peek = buf
1462 stream.seek(0)
1464 if any(peek.startswith(sig) for sig in XLS_SIGNATURES):
1465 return "xls"
1466 elif not peek.startswith(ZIP_SIGNATURE):
1467 return None
1469 with zipfile.ZipFile(stream) as zf:
1470 # Workaround for some third party files that use forward slashes and
1471 # lower case names.
1472 component_names = {
1473 name.replace("\\", "/").lower() for name in zf.namelist()
1474 }
1476 if "xl/workbook.xml" in component_names:
1477 return "xlsx"
1478 if "xl/workbook.bin" in component_names:
1479 return "xlsb"
1480 if "content.xml" in component_names:
1481 return "ods"
1482 return "zip"
1485@set_module("pandas")
1486class ExcelFile:
1487 """
1488 Class for parsing tabular Excel sheets into DataFrame objects.
1490 See read_excel for more documentation.
1492 Parameters
1493 ----------
1494 path_or_buffer : str, bytes, pathlib.Path,
1495 A file-like object, xlrd workbook or openpyxl workbook.
1496 If a string or path object, expected to be a path to a
1497 .xls, .xlsx, .xlsb, .xlsm, .odf, .ods, or .odt file.
1498 engine : str, default None
1499 If io is not a buffer or path, this must be set to identify io.
1500 Supported engines: ``xlrd``, ``openpyxl``, ``odf``, ``pyxlsb``, ``calamine``
1501 Engine compatibility :
1503 - ``xlrd`` supports old-style Excel files (.xls).
1504 - ``openpyxl`` supports newer Excel file formats.
1505 - ``odf`` supports OpenDocument file formats (.odf, .ods, .odt).
1506 - ``pyxlsb`` supports Binary Excel files.
1507 - ``calamine`` supports Excel (.xls, .xlsx, .xlsm, .xlsb)
1508 and OpenDocument (.ods) file formats.
1510 The engine `xlrd <https://xlrd.readthedocs.io/en/latest/>`_
1511 now only supports old-style ``.xls`` files.
1512 When ``engine=None``, the following logic will be
1513 used to determine the engine:
1515 - If ``path_or_buffer`` is an OpenDocument format (.odf, .ods, .odt),
1516 then `odf <https://pypi.org/project/odfpy/>`_ will be used.
1517 - Otherwise if ``path_or_buffer`` is an xls format,
1518 ``xlrd`` will be used.
1519 - Otherwise if ``path_or_buffer`` is in xlsb format,
1520 `pyxlsb <https://pypi.org/project/pyxlsb/>`_ will be used.
1521 - Otherwise if `openpyxl <https://pypi.org/project/openpyxl/>`_ is installed,
1522 then ``openpyxl`` will be used.
1523 - Otherwise if ``xlrd >= 2.0`` is installed, a ``ValueError`` will be raised.
1525 .. warning::
1527 Please do not report issues when using ``xlrd`` to read ``.xlsx`` files.
1528 This is not supported, switch to using ``openpyxl`` instead.
1529 storage_options : dict, optional
1530 Extra options that make sense for a particular storage connection, e.g.
1531 host, port, username, password, etc. For HTTP(S) URLs the key-value pairs
1532 are forwarded to ``urllib.request.Request`` as header options. For other
1533 URLs (e.g. starting with "s3://", and "gcs://") the key-value pairs are
1534 forwarded to ``fsspec.open``. Please see ``fsspec`` and ``urllib`` for more
1535 details, and for more examples on storage options refer `here
1536 <https://pandas.pydata.org/docs/user_guide/io.html?
1537 highlight=storage_options#reading-writing-remote-files>`_.
1538 engine_kwargs : dict, optional
1539 Arbitrary keyword arguments passed to excel engine.
1541 See Also
1542 --------
1543 DataFrame.to_excel : Write DataFrame to an Excel file.
1544 DataFrame.to_csv : Write DataFrame to a comma-separated values (csv) file.
1545 read_csv : Read a comma-separated values (csv) file into DataFrame.
1546 read_fwf : Read a table of fixed-width formatted lines into DataFrame.
1548 Examples
1549 --------
1550 >>> file = pd.ExcelFile("myfile.xlsx") # doctest: +SKIP
1551 >>> with pd.ExcelFile("myfile.xls") as xls: # doctest: +SKIP
1552 ... df1 = pd.read_excel(xls, "Sheet1") # doctest: +SKIP
1553 """
1555 from pandas.io.excel._calamine import CalamineReader
1556 from pandas.io.excel._odfreader import ODFReader
1557 from pandas.io.excel._openpyxl import OpenpyxlReader
1558 from pandas.io.excel._pyxlsb import PyxlsbReader
1559 from pandas.io.excel._xlrd import XlrdReader
1561 _engines: Mapping[str, Any] = {
1562 "xlrd": XlrdReader,
1563 "openpyxl": OpenpyxlReader,
1564 "odf": ODFReader,
1565 "pyxlsb": PyxlsbReader,
1566 "calamine": CalamineReader,
1567 }
1569 def __init__(
1570 self,
1571 path_or_buffer,
1572 engine: str | None = None,
1573 storage_options: StorageOptions | None = None,
1574 engine_kwargs: dict | None = None,
1575 ) -> None:
1576 if engine_kwargs is None:
1577 engine_kwargs = {}
1579 if engine is not None and engine not in self._engines:
1580 raise ValueError(f"Unknown engine: {engine}")
1582 # Always a string
1583 self._io = stringify_path(path_or_buffer)
1585 if engine is None:
1586 # Only determine ext if it is needed
1587 ext: str | None = None
1589 if not isinstance(
1590 path_or_buffer, (str, os.PathLike, ExcelFile)
1591 ) and not is_file_like(path_or_buffer):
1592 # GH#56692 - avoid importing xlrd if possible
1593 if import_optional_dependency("xlrd", errors="ignore") is None:
1594 xlrd_version = None
1595 else:
1596 import xlrd
1598 xlrd_version = Version(get_version(xlrd))
1600 if xlrd_version is not None and isinstance(path_or_buffer, xlrd.Book):
1601 ext = "xls"
1603 if ext is None:
1604 ext = inspect_excel_format(
1605 content_or_path=path_or_buffer, storage_options=storage_options
1606 )
1607 if ext is None:
1608 raise ValueError(
1609 "Excel file format cannot be determined, you must specify "
1610 "an engine manually."
1611 )
1613 engine = config.get_option(f"io.excel.{ext}.reader")
1614 if engine == "auto":
1615 engine = get_default_engine(ext, mode="reader")
1617 assert engine is not None
1618 self.engine = engine
1619 self.storage_options = storage_options
1621 self._reader = self._engines[engine](
1622 self._io,
1623 storage_options=storage_options,
1624 engine_kwargs=engine_kwargs,
1625 )
1627 def __fspath__(self):
1628 return self._io
1630 def parse(
1631 self,
1632 sheet_name: str | int | list[int] | list[str] | None = 0,
1633 header: int | Sequence[int] | None = 0,
1634 names: SequenceNotStr[Hashable] | range | None = None,
1635 index_col: int | Sequence[int] | None = None,
1636 usecols=None,
1637 converters=None,
1638 true_values: Iterable[Hashable] | None = None,
1639 false_values: Iterable[Hashable] | None = None,
1640 skiprows: Sequence[int] | int | Callable[[int], object] | None = None,
1641 nrows: int | None = None,
1642 na_values=None,
1643 parse_dates: list | dict | bool = False,
1644 date_format: str | dict[Hashable, str] | None = None,
1645 thousands: str | None = None,
1646 comment: str | None = None,
1647 skipfooter: int = 0,
1648 dtype_backend: DtypeBackend | lib.NoDefault = lib.no_default,
1649 **kwds,
1650 ) -> DataFrame | dict[str, DataFrame] | dict[int, DataFrame]:
1651 """
1652 Parse specified sheet(s) into a DataFrame.
1654 Equivalent to read_excel(ExcelFile, ...) See the read_excel
1655 docstring for more info on accepted parameters.
1657 Parameters
1658 ----------
1659 sheet_name : str, int, list, or None, default 0
1660 Strings are used for sheet names. Integers are used in zero-indexed
1661 sheet positions (chart sheets do not count as a sheet position).
1662 Lists of strings/integers are used to request multiple sheets.
1663 When ``None``, will return a dictionary containing DataFrames for
1664 each sheet.
1665 header : int, list of int, default 0
1666 Row (0-indexed) to use for the column labels of the parsed
1667 DataFrame. If a list of integers is passed those row positions will
1668 be combined into a ``MultiIndex``. Use None if there is no header.
1669 names : array-like, default None
1670 List of column names to use. If file contains no header row,
1671 then you should explicitly pass header=None.
1672 index_col : int, str, list of int, default None
1673 Column (0-indexed) to use as the row labels of the DataFrame.
1674 Pass None if there is no such column. If a list is passed,
1675 those columns will be combined into a ``MultiIndex``. If a
1676 subset of data is selected with ``usecols``, index_col
1677 is based on the subset.
1679 Missing values will be forward filled to allow roundtripping with
1680 ``to_excel`` for ``merged_cells=True``. To avoid forward filling the
1681 missing values use ``set_index`` after reading the data instead of
1682 ``index_col``.
1683 usecols : str, list-like, or callable, default None
1684 * If None, then parse all columns.
1685 * If str, then indicates comma separated list of Excel column letters
1686 and column ranges (e.g. "A:E" or "A,C,E:F"). Ranges are inclusive of
1687 both sides.
1688 * If list of int, then indicates list of column numbers to be parsed
1689 (0-indexed).
1690 * If list of string, then indicates list of column names to be parsed.
1691 * If callable, then evaluate each column name against it and parse the
1692 column if the callable returns ``True``.
1694 Returns a subset of the columns according to behavior above.
1695 converters : dict, default None
1696 Dict of functions for converting values in certain columns. Keys can
1697 either be integers or column labels, values are functions that take one
1698 input argument, the Excel cell content, and return the transformed
1699 content.
1700 true_values : list, default None
1701 Values to consider as True.
1702 false_values : list, default None
1703 Values to consider as False.
1704 skiprows : list-like, int, or callable, optional
1705 Line numbers to skip (0-indexed) or number of lines to skip (int) at the
1706 start of the file. If callable, the callable function will be evaluated
1707 against the row indices, returning True if the row should be skipped and
1708 False otherwise. An example of a valid callable argument would be ``lambda
1709 x: x in [0, 2]``.
1710 nrows : int, default None
1711 Number of rows to parse.
1712 na_values : scalar, str, list-like, or dict, default None
1713 Additional strings to recognize as NA/NaN. If dict passed, specific
1714 per-column NA values.
1715 parse_dates : bool, list-like, or dict, default False
1716 The behavior is as follows:
1718 * ``bool``. If True -> try parsing the index.
1719 * ``list`` of int or names. e.g. If [1, 2, 3] -> try parsing columns 1, 2, 3
1720 each as a separate date column.
1721 * ``list`` of lists. e.g. If [[1, 3]] -> combine columns 1 and 3 and
1722 parse as a single date column.
1723 * ``dict``, e.g. {'foo' : [1, 3]} -> parse columns 1, 3 as date and call
1724 result 'foo'
1726 If a column or index contains an unparsable date, the entire column or
1727 index will be returned unaltered as an object data type. If you
1728 don`t want to parse some cells as date just change their type
1729 in Excel to "Text".For non-standard datetime parsing, use
1730 ``pd.to_datetime`` after ``pd.read_excel``.
1732 Note: A fast-path exists for iso8601-formatted dates.
1733 date_format : str or dict of column -> format, default ``None``
1734 If used in conjunction with ``parse_dates``, will parse dates
1735 according to this format. For anything more complex,
1736 please read in as ``object`` and then apply :func:`to_datetime` as-needed.
1737 thousands : str, default None
1738 Thousands separator for parsing string columns to numeric. Note that
1739 this parameter is only necessary for columns stored as TEXT in Excel,
1740 any numeric columns will automatically be parsed, regardless of display
1741 format.
1742 comment : str, default None
1743 Comments out remainder of line. Pass a character or characters to this
1744 argument to indicate comments in the input file. Any data between the
1745 comment string and the end of the current line is ignored.
1746 skipfooter : int, default 0
1747 Rows at the end to skip (0-indexed).
1748 dtype_backend : {'numpy_nullable', 'pyarrow'}
1749 Back-end data type applied to the resultant :class:`DataFrame`
1750 (still experimental). If not specified, the default behavior
1751 is to not use nullable data types. If specified, the behavior
1752 is as follows:
1754 * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame`
1755 * ``"pyarrow"``: returns pyarrow-backed nullable
1756 :class:`ArrowDtype` :class:`DataFrame`
1758 .. versionadded:: 2.0
1759 **kwds : dict, optional
1760 Arbitrary keyword arguments passed to excel engine.
1762 Returns
1763 -------
1764 DataFrame or dict of DataFrames
1765 DataFrame from the passed in Excel file.
1767 See Also
1768 --------
1769 read_excel : Read an Excel sheet values (xlsx) file into DataFrame.
1770 read_csv : Read a comma-separated values (csv) file into DataFrame.
1771 read_fwf : Read a table of fixed-width formatted lines into DataFrame.
1773 Examples
1774 --------
1775 >>> df = pd.DataFrame([[1, 2, 3], [4, 5, 6]], columns=["A", "B", "C"])
1776 >>> df.to_excel("myfile.xlsx") # doctest: +SKIP
1777 >>> file = pd.ExcelFile("myfile.xlsx") # doctest: +SKIP
1778 >>> file.parse() # doctest: +SKIP
1779 """
1780 return self._reader.parse(
1781 sheet_name=sheet_name,
1782 header=header,
1783 names=names,
1784 index_col=index_col,
1785 usecols=usecols,
1786 converters=converters,
1787 true_values=true_values,
1788 false_values=false_values,
1789 skiprows=skiprows,
1790 nrows=nrows,
1791 na_values=na_values,
1792 parse_dates=parse_dates,
1793 date_format=date_format,
1794 thousands=thousands,
1795 comment=comment,
1796 skipfooter=skipfooter,
1797 dtype_backend=dtype_backend,
1798 **kwds,
1799 )
1801 @property
1802 def book(self):
1803 """
1804 Gets the Excel workbook.
1806 Workbook is the top-level container for all document information.
1808 Returns
1809 -------
1810 Excel Workbook
1811 The workbook object of the type defined by the engine being used.
1813 See Also
1814 --------
1815 read_excel : Read an Excel file into a pandas DataFrame.
1817 Examples
1818 --------
1819 >>> file = pd.ExcelFile("myfile.xlsx") # doctest: +SKIP
1820 >>> file.book # doctest: +SKIP
1821 <openpyxl.workbook.workbook.Workbook object at 0x11eb5ad70>
1822 >>> file.book.path # doctest: +SKIP
1823 '/xl/workbook.xml'
1824 >>> file.book.active # doctest: +SKIP
1825 <openpyxl.worksheet._read_only.ReadOnlyWorksheet object at 0x11eb5b370>
1826 >>> file.book.sheetnames # doctest: +SKIP
1827 ['Sheet1', 'Sheet2']
1828 """
1829 return self._reader.book
1831 @property
1832 def sheet_names(self):
1833 """
1834 Names of the sheets in the document.
1836 This is particularly useful for loading a specific sheet into a DataFrame when
1837 you do not know the sheet names beforehand.
1839 Returns
1840 -------
1841 list of str
1842 List of sheet names in the document.
1844 See Also
1845 --------
1846 ExcelFile.parse : Parse a sheet into a DataFrame.
1847 read_excel : Read an Excel file into a pandas DataFrame. If you know the sheet
1848 names, it may be easier to specify them directly to read_excel.
1850 Examples
1851 --------
1852 >>> file = pd.ExcelFile("myfile.xlsx") # doctest: +SKIP
1853 >>> file.sheet_names # doctest: +SKIP
1854 ["Sheet1", "Sheet2"]
1855 """
1856 return self._reader.sheet_names
1858 def close(self) -> None:
1859 """close io if necessary"""
1860 self._reader.close()
1862 def __enter__(self) -> Self:
1863 return self
1865 def __exit__(
1866 self,
1867 exc_type: type[BaseException] | None,
1868 exc_value: BaseException | None,
1869 traceback: TracebackType | None,
1870 ) -> None:
1871 self.close()