"""

Read an Excel file into a pandas DataFrame.

Supports `xls`, `xlsx`, `xlsm`, `xlsb`, `odf`, `ods` and `odt` file extensions

read from a local filesystem or URL. Supports an option to read

a single sheet or a list of sheets.

Parameters

----------

io : str, bytes, ExcelFile, xlrd.Book, path object, or file-like object

Any valid string path is acceptable. The string could be a URL. Valid

URL schemes include http, ftp, s3, and file. For file URLs, a host is

expected. A local file could be: ``file://localhost/path/to/table.xlsx``.

If you want to pass in a path object, pandas accepts any ``os.PathLike``.

By file-like object, we refer to objects with a ``read()`` method,

such as a file handle (e.g. via builtin ``open`` function)

or ``StringIO``.

sheet_name : str, int, list, or None, default 0

Strings are used for sheet names. Integers are used in zero-indexed

sheet positions (chart sheets do not count as a sheet position).

Lists of strings/integers are used to request multiple sheets.

Specify None to get all worksheets.

Available cases:

* Defaults to ``0``: 1st sheet as a `DataFrame`

* ``1``: 2nd sheet as a `DataFrame`

* ``"Sheet1"``: Load sheet with name "Sheet1"

* ``[0, 1, "Sheet5"]``: Load first, second and sheet named "Sheet5"

as a dict of `DataFrame`

* None: All worksheets.

header : int, list of int, default 0

Row (0-indexed) to use for the column labels of the parsed

DataFrame. If a list of integers is passed those row positions will

be combined into a ``MultiIndex``. Use None if there is no header.

names : array-like, default None

List of column names to use. If file contains no header row,

then you should explicitly pass header=None.

index_col : int, str, list of int, default None

Column (0-indexed) to use as the row labels of the DataFrame.

Pass None if there is no such column. If a list is passed,

those columns will be combined into a ``MultiIndex``. If a

subset of data is selected with ``usecols``, index_col

is based on the subset.

Missing values will be forward filled to allow roundtripping with

``to_excel`` for ``merged_cells=True``. To avoid forward filling the

missing values use ``set_index`` after reading the data instead of

``index_col``.

usecols : str, list-like, or callable, default None

* If None, then parse all columns.

* If str, then indicates comma separated list of Excel column letters

and column ranges (e.g. "A:E" or "A,C,E:F"). Ranges are inclusive of

both sides.

* If list of int, then indicates list of column numbers to be parsed

(0-indexed).

* If list of string, then indicates list of column names to be parsed.

* If callable, then evaluate each column name against it and parse the

column if the callable returns ``True``.

Returns a subset of the columns according to behavior above.

dtype : Type name or dict of column -> type, default None

Data type for data or columns. E.g. {{'a': np.float64, 'b': np.int32}}

Use `object` to preserve data as stored in Excel and not interpret dtype.

If converters are specified, they will be applied INSTEAD

of dtype conversion.

engine : str, default None

If io is not a buffer or path, this must be set to identify io.

Supported engines: "xlrd", "openpyxl", "odf", "pyxlsb".

Engine compatibility :

- "xlrd" supports old-style Excel files (.xls).

- "openpyxl" supports newer Excel file formats.

- "odf" supports OpenDocument file formats (.odf, .ods, .odt).

- "pyxlsb" supports Binary Excel files.

.. versionchanged:: 1.2.0

The engine `xlrd <https://xlrd.readthedocs.io/en/latest/>`_

now only supports old-style ``.xls`` files.

When ``engine=None``, the following logic will be

used to determine the engine:

- If ``path_or_buffer`` is an OpenDocument format (.odf, .ods, .odt),

then `odf <https://pypi.org/project/odfpy/>`_ will be used.

- Otherwise if ``path_or_buffer`` is an xls format,

``xlrd`` will be used.

- Otherwise if ``path_or_buffer`` is in xlsb format,

``pyxlsb`` will be used.

.. versionadded:: 1.3.0

- Otherwise ``openpyxl`` will be used.

.. versionchanged:: 1.3.0

converters : dict, default None

Dict of functions for converting values in certain columns. Keys can

either be integers or column labels, values are functions that take one

input argument, the Excel cell content, and return the transformed

content.

true_values : list, default None

Values to consider as True.

false_values : list, default None

Values to consider as False.

skiprows : list-like, int, or callable, optional

Line numbers to skip (0-indexed) or number of lines to skip (int) at the

start of the file. If callable, the callable function will be evaluated

against the row indices, returning True if the row should be skipped and

False otherwise. An example of a valid callable argument would be ``lambda

x: x in [0, 2]``.

nrows : int, default None

Number of rows to parse.

na_values : scalar, str, list-like, or dict, default None

Additional strings to recognize as NA/NaN. If dict passed, specific

per-column NA values. By default the following values are interpreted

as NaN: '"""

fill("', '".join(sorted(STR_NA_VALUES)), 70, subsequent_indent=" ")

"""'.

keep_default_na : bool, default True

Whether or not to include the default NaN values when parsing the data.

Depending on whether `na_values` is passed in, the behavior is as follows:

* If `keep_default_na` is True, and `na_values` are specified, `na_values`

is appended to the default NaN values used for parsing.

* If `keep_default_na` is True, and `na_values` are not specified, only

the default NaN values are used for parsing.

* If `keep_default_na` is False, and `na_values` are specified, only

the NaN values specified `na_values` are used for parsing.

* If `keep_default_na` is False, and `na_values` are not specified, no

strings will be parsed as NaN.

Note that if `na_filter` is passed in as False, the `keep_default_na` and

`na_values` parameters will be ignored.

na_filter : bool, default True

Detect missing value markers (empty strings and the value of na_values). In

data without any NAs, passing na_filter=False can improve the performance

of reading a large file.

verbose : bool, default False

Indicate number of NA values placed in non-numeric columns.

parse_dates : bool, list-like, or dict, default False

The behavior is as follows:

* bool. If True -> try parsing the index.

* list of int or names. e.g. If [1, 2, 3] -> try parsing columns 1, 2, 3

each as a separate date column.

* list of lists. e.g. If [[1, 3]] -> combine columns 1 and 3 and parse as

a single date column.

* dict, e.g. {{'foo' : [1, 3]}} -> parse columns 1, 3 as date and call

result 'foo'

If a column or index contains an unparsable date, the entire column or

index will be returned unaltered as an object data type. If you don`t want to

parse some cells as date just change their type in Excel to "Text".

For non-standard datetime parsing, use ``pd.to_datetime`` after ``pd.read_excel``.

Note: A fast-path exists for iso8601-formatted dates.

date_parser : function, optional

Function to use for converting a sequence of string columns to an array of

datetime instances. The default uses ``dateutil.parser.parser`` to do the

conversion. Pandas will try to call `date_parser` in three different ways,

advancing to the next if an exception occurs: 1) Pass one or more arrays

(as defined by `parse_dates`) as arguments; 2) concatenate (row-wise) the

string values from the columns defined by `parse_dates` into a single array

and pass that; and 3) call `date_parser` once for each row using one or

more strings (corresponding to the columns defined by `parse_dates`) as

arguments.

.. deprecated:: 2.0.0

Use ``date_format`` instead, or read in as ``object`` and then apply

:func:`to_datetime` as-needed.

date_format : str or dict of column -> format, default ``None``

If used in conjunction with ``parse_dates``, will parse dates according to this

format. For anything more complex,

please read in as ``object`` and then apply :func:`to_datetime` as-needed.

.. versionadded:: 2.0.0

thousands : str, default None

Thousands separator for parsing string columns to numeric. Note that

this parameter is only necessary for columns stored as TEXT in Excel,

any numeric columns will automatically be parsed, regardless of display

format.

decimal : str, default '.'

Character to recognize as decimal point for parsing string columns to numeric.

Note that this parameter is only necessary for columns stored as TEXT in Excel,

any numeric columns will automatically be parsed, regardless of display

format.(e.g. use ',' for European data).

.. versionadded:: 1.4.0

comment : str, default None

Comments out remainder of line. Pass a character or characters to this

argument to indicate comments in the input file. Any data between the

comment string and the end of the current line is ignored.

skipfooter : int, default 0

Rows at the end to skip (0-indexed).

{storage_options}

.. versionadded:: 1.2.0

dtype_backend : {{"numpy_nullable", "pyarrow"}}, defaults to NumPy backed DataFrames

Which dtype_backend to use, e.g. whether a DataFrame should have NumPy

arrays, nullable dtypes are used for all dtypes that have a nullable

implementation when "numpy_nullable" is set, pyarrow is used for all

dtypes if "pyarrow" is set.

The dtype_backends are still experimential.

.. versionadded:: 2.0

engine_kwargs : dict, optional

Arbitrary keyword arguments passed to excel engine.

Returns

-------

DataFrame or dict of DataFrames

DataFrame from the passed in Excel file. See notes in sheet_name

argument for more information on when a dict of DataFrames is returned.

See Also

--------

DataFrame.to_excel : Write DataFrame to an Excel file.

DataFrame.to_csv : Write DataFrame to a comma-separated values (csv) file.

read_csv : Read a comma-separated values (csv) file into DataFrame.

read_fwf : Read a table of fixed-width formatted lines into DataFrame.

Notes

-----

For specific information on the methods used for each Excel engine, refer to the pandas

:ref:`user guide <io.excel_reader>`

Examples

--------

The file can be read using the file name as string or an open file object:

>>> pd.read_excel('tmp.xlsx', index_col=0) # doctest: SKIP

Name Value

0 string1 1

1 string2 2

2 #Comment 3

>>> pd.read_excel(open('tmp.xlsx', 'rb'),

... sheet_name='Sheet3') # doctest: SKIP

Unnamed: 0 Name Value

0 0 string1 1

1 1 string2 2

2 2 #Comment 3

Index and header can be specified via the `index_col` and `header` arguments

>>> pd.read_excel('tmp.xlsx', index_col=None, header=None) # doctest: SKIP

0 1 2

0 NaN Name Value

1 0.0 string1 1

2 1.0 string2 2

3 2.0 #Comment 3

Column types are inferred but can be explicitly specified

>>> pd.read_excel('tmp.xlsx', index_col=0,

... dtype={{'Name': str, 'Value': float}}) # doctest: SKIP

Name Value

0 string1 1.0

1 string2 2.0

2 #Comment 3.0

True, False, and NA values, and thousands separators have defaults,

but can be explicitly specified, too. Supply the values you would like

as strings or lists of strings!

>>> pd.read_excel('tmp.xlsx', index_col=0,

... na_values=['string1', 'string2']) # doctest: SKIP

Name Value

0 NaN 1

1 NaN 2

2 #Comment 3

Comment lines in the excel input file can be skipped using the `comment` kwarg

>>> pd.read_excel('tmp.xlsx', index_col=0, comment='#') # doctest: SKIP

Name Value

0 string1 1.0

1 string2 2.0

2 None NaN

"""

io,

# sheet name is str or int -> DataFrame

sheet_name: str | int = ...,

*,

header: int | Sequence[int] | None = ...,

names: list[str] | None = ...,

index_col: int | Sequence[int] | None = ...,

usecols: int

| str

| Sequence[int]

| Sequence[str]

| Callable[[str], bool]

| None = ...,

dtype: DtypeArg | None = ...,

engine: Literal["xlrd", "openpyxl", "odf", "pyxlsb"] | None = ...,

converters: dict[str, Callable] | dict[int, Callable] | None = ...,

true_values: Iterable[Hashable] | None = ...,

false_values: Iterable[Hashable] | None = ...,

skiprows: Sequence[int] | int | Callable[[int], object] | None = ...,

nrows: int | None = ...,

na_values=...,

keep_default_na: bool = ...,

na_filter: bool = ...,

verbose: bool = ...,

parse_dates: list | dict | bool = ...,

date_parser: Callable | lib.NoDefault = ...,

date_format: dict[Hashable, str] | str | None = ...,

thousands: str | None = ...,

decimal: str = ...,

comment: str | None = ...,

skipfooter: int = ...,

storage_options: StorageOptions = ...,

dtype_backend: DtypeBackend | lib.NoDefault = ...,

) -> DataFrame:

io,

# sheet name is list or None -> dict[IntStrT, DataFrame]

sheet_name: list[IntStrT] | None,

*,

header: int | Sequence[int] | None = ...,

names: list[str] | None = ...,

index_col: int | Sequence[int] | None = ...,

usecols: int

| str

| Sequence[int]

| Sequence[str]

| Callable[[str], bool]

| None = ...,

dtype: DtypeArg | None = ...,

engine: Literal["xlrd", "openpyxl", "odf", "pyxlsb"] | None = ...,

converters: dict[str, Callable] | dict[int, Callable] | None = ...,

true_values: Iterable[Hashable] | None = ...,

false_values: Iterable[Hashable] | None = ...,

skiprows: Sequence[int] | int | Callable[[int], object] | None = ...,

nrows: int | None = ...,

na_values=...,

keep_default_na: bool = ...,

na_filter: bool = ...,

verbose: bool = ...,

parse_dates: list | dict | bool = ...,

date_parser: Callable | lib.NoDefault = ...,

date_format: dict[Hashable, str] | str | None = ...,

thousands: str | None = ...,

decimal: str = ...,

comment: str | None = ...,

skipfooter: int = ...,

storage_options: StorageOptions = ...,

dtype_backend: DtypeBackend | lib.NoDefault = ...,

) -> dict[IntStrT, DataFrame]:

io,

sheet_name: str | int | list[IntStrT] | None = 0,

*,

header: int | Sequence[int] | None = 0,

names: list[str] | None = None,

index_col: int | Sequence[int] | None = None,

usecols: int

| str

| Sequence[int]

| Sequence[str]

| Callable[[str], bool]

| None = None,

dtype: DtypeArg | None = None,

engine: Literal["xlrd", "openpyxl", "odf", "pyxlsb"] | None = None,

converters: dict[str, Callable] | dict[int, Callable] | None = None,

true_values: Iterable[Hashable] | None = None,

false_values: Iterable[Hashable] | None = None,

skiprows: Sequence[int] | int | Callable[[int], object] | None = None,

nrows: int | None = None,

na_values=None,

keep_default_na: bool = True,

na_filter: bool = True,

verbose: bool = False,

parse_dates: list | dict | bool = False,

date_parser: Callable | lib.NoDefault = lib.no_default,

date_format: dict[Hashable, str] | str | None = None,

thousands: str | None = None,

decimal: str = ".",

comment: str | None = None,

skipfooter: int = 0,

storage_options: StorageOptions = None,

dtype_backend: DtypeBackend | lib.NoDefault = lib.no_default,

engine_kwargs: dict | None = None,

) -> DataFrame | dict[IntStrT, DataFrame]:

check_dtype_backend(dtype_backend)

should_close = False

if engine_kwargs is None:

engine_kwargs = {}

if not isinstance(io, ExcelFile):

should_close = True

io = ExcelFile(

io,

storage_options=storage_options,

engine=engine,

engine_kwargs=engine_kwargs,

)

elif engine and engine != io.engine:

raise ValueError(

"Engine should not be specified when passing "

"an ExcelFile - ExcelFile already has the engine set"

)

try:

data = io.parse(

sheet_name=sheet_name,

header=header,

names=names,

index_col=index_col,

usecols=usecols,

dtype=dtype,

converters=converters,

true_values=true_values,

false_values=false_values,

skiprows=skiprows,

nrows=nrows,

na_values=na_values,

keep_default_na=keep_default_na,

na_filter=na_filter,

verbose=verbose,

parse_dates=parse_dates,

date_parser=date_parser,

date_format=date_format,

thousands=thousands,

decimal=decimal,

comment=comment,

skipfooter=skipfooter,

dtype_backend=dtype_backend,

)

finally:

# make sure to close opened file handles

if should_close:

io.close()

def __init__(

self,

filepath_or_buffer,

storage_options: StorageOptions = None,

engine_kwargs: dict | None = None,

) -> None:

if engine_kwargs is None:

engine_kwargs = {}

# First argument can also be bytes, so create a buffer

if isinstance(filepath_or_buffer, bytes):

filepath_or_buffer = BytesIO(filepath_or_buffer)

self.handles = IOHandles(

handle=filepath_or_buffer, compression={"method": None}

)

if not isinstance(filepath_or_buffer, (ExcelFile, self._workbook_class)):

self.handles = get_handle(

filepath_or_buffer, "rb", storage_options=storage_options, is_text=False

)

if isinstance(self.handles.handle, self._workbook_class):

self.book = self.handles.handle

elif hasattr(self.handles.handle, "read"):

# N.B. xlrd.Book has a read attribute too

self.handles.handle.seek(0)

try:

self.book = self.load_workbook(self.handles.handle, engine_kwargs)

except Exception:

self.close()

raise

else:

raise ValueError(

"Must explicitly set engine if not passing in buffer or path for io."

)

@property

@abc.abstractmethod

def _workbook_class(self):

pass

@abc.abstractmethod

def load_workbook(self, filepath_or_buffer, engine_kwargs):

pass

def close(self) -> None:

if hasattr(self, "book"):

if hasattr(self.book, "close"):

# pyxlsb: opens a TemporaryFile

# openpyxl: https://stackoverflow.com/questions/31416842/

# openpyxl-does-not-close-excel-workbook-in-read-only-mode

self.book.close()

elif hasattr(self.book, "release_resources"):

# xlrd

# https://github.com/python-excel/xlrd/blob/2.0.1/xlrd/book.py#L548

self.book.release_resources()

self.handles.close()

@property

@abc.abstractmethod

def sheet_names(self) -> list[str]:

pass

@abc.abstractmethod

def get_sheet_by_name(self, name: str):

pass

@abc.abstractmethod

def get_sheet_by_index(self, index: int):

pass

@abc.abstractmethod

def get_sheet_data(self, sheet, rows: int | None = None):

pass

def raise_if_bad_sheet_by_index(self, index: int) -> None:

n_sheets = len(self.sheet_names)

if index >= n_sheets:

raise ValueError(

f"Worksheet index {index} is invalid, {n_sheets} worksheets found"

)

def raise_if_bad_sheet_by_name(self, name: str) -> None:

if name not in self.sheet_names:

raise ValueError(f"Worksheet named '{name}' not found")

def _check_skiprows_func(

self,

skiprows: Callable,

rows_to_use: int,

) -> int:

"""

Determine how many file rows are required to obtain `nrows` data

rows when `skiprows` is a function.

Parameters

----------

skiprows : function

The function passed to read_excel by the user.

rows_to_use : int

The number of rows that will be needed for the header and

the data.

Returns

-------

int

"""

i = 0

rows_used_so_far = 0

while rows_used_so_far < rows_to_use:

if not skiprows(i):

rows_used_so_far = 1

i = 1

return i

def _calc_rows(

self,

header: int | Sequence[int] | None,

index_col: int | Sequence[int] | None,

skiprows: Sequence[int] | int | Callable[[int], object] | None,

nrows: int | None,

) -> int | None:

"""

If nrows specified, find the number of rows needed from the

file, otherwise return None.

Parameters

----------

header : int, list of int, or None

See read_excel docstring.

index_col : int, list of int, or None

See read_excel docstring.

skiprows : list-like, int, callable, or None

See read_excel docstring.

nrows : int or None

See read_excel docstring.

Returns

-------

int or None

"""

if nrows is None:

return None

if header is None:

header_rows = 1

elif is_integer(header):

header = cast(int, header)

header_rows = 1 header

else:

header = cast(Sequence, header)

header_rows = 1 header[-1]

# If there is a MultiIndex header and an index then there is also

# a row containing just the index name(s)

if is_list_like(header) and index_col is not None:

header = cast(Sequence, header)

if len(header) > 1:

header_rows = 1

if skiprows is None:

return header_rows nrows

if is_integer(skiprows):

skiprows = cast(int, skiprows)

return header_rows nrows skiprows

if is_list_like(skiprows):

def f(skiprows: Sequence, x: int) -> bool:

return x in skiprows

skiprows = cast(Sequence, skiprows)

return self._check_skiprows_func(partial(f, skiprows), header_rows nrows)

if callable(skiprows):

return self._check_skiprows_func(

skiprows,

header_rows nrows,

)

# else unexpected skiprows type: read_excel will not optimize

# the number of rows read from file

return None

def parse(

self,

sheet_name: str | int | list[int] | list[str] | None = 0,

header: int | Sequence[int] | None = 0,

names=None,

index_col: int | Sequence[int] | None = None,

usecols=None,

dtype: DtypeArg | None = None,

true_values: Iterable[Hashable] | None = None,

false_values: Iterable[Hashable] | None = None,

skiprows: Sequence[int] | int | Callable[[int], object] | None = None,

nrows: int | None = None,

na_values=None,

verbose: bool = False,

parse_dates: list | dict | bool = False,

date_parser: Callable | lib.NoDefault = lib.no_default,

date_format: dict[Hashable, str] | str | None = None,

thousands: str | None = None,

decimal: str = ".",

comment: str | None = None,

skipfooter: int = 0,

dtype_backend: DtypeBackend | lib.NoDefault = lib.no_default,

**kwds,

):

validate_header_arg(header)

validate_integer("nrows", nrows)

ret_dict = False

# Keep sheetname to maintain backwards compatibility.

sheets: list[int] | list[str]

if isinstance(sheet_name, list):

sheets = sheet_name

ret_dict = True

elif sheet_name is None:

sheets = self.sheet_names

ret_dict = True

elif isinstance(sheet_name, str):

sheets = [sheet_name]

else:

sheets = [sheet_name]

# handle same-type duplicates.

sheets = cast(Union[List[int], List[str]], list(dict.fromkeys(sheets).keys()))

output = {}

last_sheetname = None

for asheetname in sheets:

last_sheetname = asheetname

if verbose:

print(f"Reading sheet {asheetname}")

if isinstance(asheetname, str):

sheet = self.get_sheet_by_name(asheetname)

else: # assume an integer if not a string

sheet = self.get_sheet_by_index(asheetname)

file_rows_needed = self._calc_rows(header, index_col, skiprows, nrows)

data = self.get_sheet_data(sheet, file_rows_needed)

if hasattr(sheet, "close"):

# pyxlsb opens two TemporaryFiles

sheet.close()

usecols = maybe_convert_usecols(usecols)

if not data:

output[asheetname] = DataFrame()

continue

is_list_header = False

is_len_one_list_header = False

if is_list_like(header):

assert isinstance(header, Sequence)

is_list_header = True

if len(header) == 1:

is_len_one_list_header = True

if is_len_one_list_header:

header = cast(Sequence[int], header)[0]

# forward fill and pull out names for MultiIndex column

header_names = None

if header is not None and is_list_like(header):

assert isinstance(header, Sequence)

header_names = []

control_row = [True] * len(data[0])

for row in header:

if is_integer(skiprows):

assert isinstance(skiprows, int)

row = skiprows

if row > len(data) - 1:

raise ValueError(

f"header index {row} exceeds maximum index "

f"{len(data) - 1} of data.",

)

data[row], control_row = fill_mi_header(data[row], control_row)

if index_col is not None:

header_name, _ = pop_header_name(data[row], index_col)

header_names.append(header_name)

# If there is a MultiIndex header and an index then there is also

# a row containing just the index name(s)

has_index_names = False

if is_list_header and not is_len_one_list_header and index_col is not None:

index_col_list: Sequence[int]

if isinstance(index_col, int):

index_col_list = [index_col]

else:

assert isinstance(index_col, Sequence)

index_col_list = index_col

# We have to handle mi without names. If any of the entries in the data

# columns are not empty, this is a regular row

assert isinstance(header, Sequence)

if len(header) < len(data):

potential_index_names = data[len(header)]

potential_data = [

x

for i, x in enumerate(potential_index_names)

if not control_row[i] and i not in index_col_list

]

has_index_names = all(x == "" or x is None for x in potential_data)

if is_list_like(index_col):

# Forward fill values for MultiIndex index.

if header is None:

offset = 0

elif isinstance(header, int):

offset = 1 header

else:

offset = 1 max(header)

# GH34673: if MultiIndex names present and not defined in the header,

# offset needs to be incremented so that forward filling starts

# from the first MI value instead of the name

if has_index_names:

offset = 1

# Check if we have an empty dataset

# before trying to collect data.

if offset < len(data):

assert isinstance(index_col, Sequence)

for col in index_col:

last = data[offset][col]

for row in range(offset 1, len(data)):

if data[row][col] == "" or data[row][col] is None:

data[row][col] = last

else:

last = data[row][col]

# GH 12292 : error when read one empty column from excel file

try:

parser = TextParser(

data,

names=names,

header=header,

index_col=index_col,

has_index_names=has_index_names,

dtype=dtype,

true_values=true_values,

false_values=false_values,

skiprows=skiprows,

nrows=nrows,

na_values=na_values,

skip_blank_lines=False, # GH 39808

parse_dates=parse_dates,

date_parser=date_parser,

date_format=date_format,

thousands=thousands,

decimal=decimal,

comment=comment,

skipfooter=skipfooter,

usecols=usecols,

dtype_backend=dtype_backend,

**kwds,

)

output[asheetname] = parser.read(nrows=nrows)

if header_names:

output[asheetname].columns = output[asheetname].columns.set_names(

header_names

)

except EmptyDataError:

# No Data, return an empty DataFrame

output[asheetname] = DataFrame()

except Exception as err:

err.args = (f"{err.args[0]} (sheet: {asheetname})", *err.args[1:])

raise err

if last_sheetname is None:

raise ValueError("Sheet name is an empty list")

if ret_dict:

return output

else:

"""

Class for writing DataFrame objects into excel sheets.

Default is to use:

* `xlsxwriter <https://pypi.org/project/XlsxWriter/>`__ for xlsx files if xlsxwriter

is installed otherwise `openpyxl <https://pypi.org/project/openpyxl/>`__

* `odswriter <https://pypi.org/project/odswriter/>`__ for ods files

See ``DataFrame.to_excel`` for typical usage.

The writer should be used as a context manager. Otherwise, call `close()` to save

and close any opened file handles.

Parameters

----------

path : str or typing.BinaryIO

Path to xls or xlsx or ods file.

engine : str (optional)

Engine to use for writing. If None, defaults to

``io.excel.<extension>.writer``. NOTE: can only be passed as a keyword

argument.

date_format : str, default None

Format string for dates written into Excel files (e.g. 'YYYY-MM-DD').

datetime_format : str, default None

Format string for datetime objects written into Excel files.

(e.g. 'YYYY-MM-DD HH:MM:SS').

mode : {{'w', 'a'}}, default 'w'

File mode to use (write or append). Append does not work with fsspec URLs.

{storage_options}

.. versionadded:: 1.2.0

if_sheet_exists : {{'error', 'new', 'replace', 'overlay'}}, default 'error'

How to behave when trying to write to a sheet that already

exists (append mode only).

* error: raise a ValueError.

* new: Create a new sheet, with a name determined by the engine.

* replace: Delete the contents of the sheet before writing to it.

* overlay: Write contents to the existing sheet without first removing,

but possibly over top of, the existing contents.

.. versionadded:: 1.3.0

.. versionchanged:: 1.4.0

Added ``overlay`` option

engine_kwargs : dict, optional

Keyword arguments to be passed into the engine. These will be passed to

the following functions of the respective engines:

* xlsxwriter: ``xlsxwriter.Workbook(file, **engine_kwargs)``

* openpyxl (write mode): ``openpyxl.Workbook(**engine_kwargs)``

* openpyxl (append mode): ``openpyxl.load_workbook(file, **engine_kwargs)``

* odswriter: ``odf.opendocument.OpenDocumentSpreadsheet(**engine_kwargs)``

.. versionadded:: 1.3.0

Notes

-----

For compatibility with CSV writers, ExcelWriter serializes lists

and dicts to strings before writing.

Examples

--------

Default usage:

>>> df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"]) # doctest: SKIP

>>> with pd.ExcelWriter("path_to_file.xlsx") as writer:

... df.to_excel(writer) # doctest: SKIP

To write to separate sheets in a single file: