U
|
¬ý°dµá ã @ s¾ d dl mZ d dlZd dlZd dlmZ d dlmZ d dlZd dl m
|
Z
|
d dl m Z d dl mZmZmZmZmZmZmZmZmZmZmZmZ d dlZd dlm Z d d l m Z d d
|
l m Z d d l!m"Z"m#Z#m$Z$m%Z%m&Z&m'Z'm(Z( d d l)m*Z*m+Z+ d d l,m-Z- d dl.m/Z/m0Z0 d dl1m2Z2 d dl3m4Z4m5Z5m6Z6m7Z7 d dl8m9Z9 d dl:m;Z; d dl<m=Z= d dl>m?Z?m@Z@mAZAmBZB d dlCmDZDmEZEmFZFmGZGmHZH d dlImJZJ d dlKmLZL de
|
d MeNe ¡ddd d ZOedOd d d d d d d d d d d d d d d d d d d d d d d d d d d!d"d!d#d$d%d&d'd'd(d)d*d*d*d+d,d-d.d/d.d0d1d2d3d4d5d6ZPed d d d d d d d d d d d d d d d d d d d d d d d d d7d!d"d!d#d$d%d&d'd'd(d)d*d*d*d+d,d-d.d/d.d0d1d2d8d4d9d6ZPe0e;d: d;e/eOdPd dddddddddddd<d<d=d=e jQddd>dd de jQd d?d!d"d!d#d$d%d&d'd'd(d)d*d*d*d+d,d-d.d/d.d0d1d2d@d4dAd6ZPG dBdC dCejRdDZSe0e;d: d;G dEdF dFejRdDZTdGZUdHZVeWeXeYeUeVf ZZe0e;d: d;dQdId1d.dJdKdLZ[G dMdN dNZ\dS )Ré )Ú annotationsN)Úpartial)ÚBytesIO)Úfill)Ú TracebackType) ÚIOÚAnyÚCallableÚHashableÚIterableÚListÚLiteralÚMappingÚSequenceÚUnionÚcastÚoverload)Úconfig)Úlib)Ú STR_NA_VALUES)ÚDtypeArgÚ DtypeBackendÚFilePathÚIntStrTÚ
|
ReadBufferÚStorageOptionsÚWriteExcelBuffer)Ú get_versionÚimport_optional_dependency)ÚEmptyDataError)ÚAppenderÚdoc)Úcheck_dtype_backend)Úis_boolÚis_floatÚ
|
is_integerÚ is_list_like)Ú DataFrame)Ú _shared_docs)ÚVersion)Ú IOHandlesÚ
|
get_handleÚstringify_pathÚvalidate_header_arg)Úfill_mi_headerÚget_default_engineÚ
|
get_writerÚmaybe_convert_usecolsÚpop_header_name)Ú
|
TextParser)Úvalidate_integera
|
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, 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: 'z', 'éF z )Úsubsequent_indentat '.
|
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
|
|
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.
|
|
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
|
.)ÚheaderÚnamesÚ index_colÚusecolsÚdtypeÚengineÚ
|
convertersÚ true_valuesÚ false_valuesÚskiprowsÚnrowsÚ na_valuesÚkeep_default_naÚ na_filterÚverboseÚ parse_datesÚ date_parserÚ date_formatÚ thousandsÚdecimalÚcommentÚ
|
skipfooterÚstorage_optionsÚ dtype_backendz str | intúint | Sequence[int] | Nonezlist[str] | NonezHint | str | Sequence[int] | Sequence[str] | Callable[[str], bool] | NoneúDtypeArg | Nonez5Literal[('xlrd', 'openpyxl', 'odf', 'pyxlsb')] | Nonez0dict[str, Callable] | dict[int, Callable] | NoneúIterable[Hashable] | Noneú4Sequence[int] | int | Callable[[int], object] | Noneú
|
int | NoneÚboolúlist | dict | boolúCallable | lib.NoDefaultú dict[Hashable, str] | str | Noneú
|
str | NoneÚstrÚintr ú DtypeBackend | lib.NoDefaultr' )Ú
|
sheet_namer7 r8 r9 r: r; r< r= r> r? r@ rA rC rD rE rF rG rH rI rJ rK rL rM rN Úreturnc C s d S ©N© ©Úior\ r7 r8 r9 r: r; r< r= r> r? r@ rA rB rC rD rE rF rG rH rI rJ rK rL rM rN r_ r_ úLd:\z\workplace\vscode\pyvenv\venv\Lib\site-packages\pandas/io/excel/_base.pyÚ
|
read_excelg s $rc zlist[IntStrT] | Nonezdict[IntStrT, DataFrame]c C s d S r^ r_ r` r_ r_ rb rc s $rM ©rM TFÚ.z str | int | list[IntStrT] | Nonez$DataFrame | dict[IntStrT, DataFrame]c C s t | d}t| ts*d}t| ||d} n|r@|| jkr@tdz<| j|||||||| |
|
| | | |||||||||||d}W 5 |r| ¡ X |S )NFT)rM r< z_Engine should not be specified when passing an ExcelFile - ExcelFile already has the engine set)r\ r7 r8 r9 r: r; r= r> r? r@ rA rB rC rD rE rF rG rH rI rJ rK rL rN )r" Ú
|
isinstanceÚ ExcelFiler< Ú
|
ValueErrorÚcloseÚparse) ra r\ r7 r8 r9 r: r; r< r= r> r? r@ rA rB rC rD rE rF rG rH rI rJ rK rL rM rN Z should_closeÚdatar_ r_ rb rc µ sL $
|
ÿé
|
|
c @ sF e Zd Zd;dddddZeejdd Zejd d
|
Zdd d d Z eejdd ddZ
|
ejddddZ ejddddZ ejd<dddd Z ddd d d Zddd d!d"Zd#ddd$d%d&Zd'd'd(ddd)d*d+Zd,d,dddddddddd-d-ejddd.dd,ejfd/d'd'd0d1d1d(dd2d3d4d5d6dd6dd7d8d9d:ZdS )=ÚBaseExcelReaderNr ÚNone)rM r] c C sÀ t |trt|}t|dd id| _t |t| jfsFt|d|dd| _t | jj| jrb| jj| _ nZt
|
| jjdr´| jj d¡ z| | jj¡| _ W q¼ t k
|
r° | ¡ Y q¼X ntdd S ) NÚmethod)ÚhandleÚ compressionÚrbF©rM Zis_textÚreadr zCMust explicitly set engine if not passing in buffer or path for io.)rf Úbytesr r* Úhandlesrg Ú_workbook_classr+ ro ÚbookÚhasattrÚseekÚ load_workbookÚ Exceptionri rh )ÚselfÚfilepath_or_bufferrM r_ r_ rb Ú__init__ s2
|
ÿ ÿ
|
ÿzBaseExcelReader.__init__c C s d S r^ r_ ©r| r_ r_ rb rv % s z BaseExcelReader._workbook_classc C s d S r^ r_ )r| r} r_ r_ rb rz * s z BaseExcelReader.load_workbook©r] c C sF t | dr8t | jdr"| j ¡ nt | jdr8| j ¡ | j ¡ d S )Nrw ri Úrelease_resources)rx rw ri r ru r r_ r_ rb ri . s
|
|
zBaseExcelReader.closez list[str]c C s d S r^ r_ r r_ r_ rb Ú sheet_names; s zBaseExcelReader.sheet_namesrY )Únamec C s d S r^ r_ ©r| r r_ r_ rb Úget_sheet_by_name@ s z!BaseExcelReader.get_sheet_by_namerZ )Úindexc C s d S r^ r_ )r| r r_ r_ rb Úget_sheet_by_indexD s z"BaseExcelReader.get_sheet_by_indexrS )Úrowsc C s d S r^ r_ )r| Úsheetr r_ r_ rb Úget_sheet_dataH s z BaseExcelReader.get_sheet_data)r r] c C s, t | j}||kr(td| d| dd S )NzWorksheet index z is invalid, z worksheets found)Úlenr rh )r| r Zn_sheetsr_ r_ rb Úraise_if_bad_sheet_by_indexL s
|
|
ÿz+BaseExcelReader.raise_if_bad_sheet_by_index)r r] c C s || j krtd| dd S )NzWorksheet named 'z ' not found)r rh r r_ r_ rb Úraise_if_bad_sheet_by_nameS s
|
z*BaseExcelReader.raise_if_bad_sheet_by_namer )r@ Ú rows_to_user] c C s. d}d}||k r*||s |d7 }|d7 }q|S )a
|
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
|
r é r_ )r| r@ r ÚiZrows_used_so_farr_ r_ rb Ú_check_skiprows_funcW s
|
z$BaseExcelReader._check_skiprows_funcrO rR )r7 r9 r@ rA r] c C sþ |dkr dS |dkrd}n2t |r6tt|}d| }ntt|}d|d }t|rz|dk rztt|}t|dkrz|d7 }|dkr|| S t |r¨tt|}|| | S t|râdddddd }tt|}| t|||| ¡S t|rú| ||| ¡S dS )
|
a
|
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
|
Nr éÿÿÿÿr rZ rT )r@ Úxr] c S s || kS r^ r_ )r@ r r_ r_ rb Úf¦ s z%BaseExcelReader._calc_rows.<locals>.f) r% r rZ r r& r r r Úcallable)r| r7 r9 r@ rA Z header_rowsr r_ r_ rb Ú
|
_calc_rowst s8
|
|
|
|
|
|
þzBaseExcelReader._calc_rowsr Fre ú(str | int | list[int] | list[str] | NonerP rQ rT rU rV rW rX r[ )r\ r7 r9 r; r> r? r@ rA rE rF rG rH rI rJ rK rL rN c , s¬ t | td|
|
d}t|tr*|}d}n,|d kr>| j}d}nt|trP|g}n|g}tttt tt f tt
|
|¡ ¡ }i }d }|D ]ö}|}| r¦t d| t|tr¼| |¡}n
|
| |¡}| ||| |
|
¡} | || ¡} t|drô| ¡ t|}| st ||< qd} d} t|rFt|ts0td} t|dkrFd} | r^ttt |d }d } |d k r6t|r6t|tstg } dgt| d |D ]}!t| rÆt| t s¾t|!| 7 }!|!t| d krötd|! d t| d d
|
t | |! \| |!< |d k r t | |! |\}"}#| |"¡ q d}$| rÐ| sÐ|d k rÐt|t rd|gnt|tstt|t|tstt|t| k rÐ| t| }% fd d t |%D }&t d d |&D }$t|r¨|d krêd}'n"t|t r d| }'n dt!| }'|$r|'d7 }'|'t| k r¨t|ts8t|D ]j}(| |' |( })t"|'d t| D ]D}!| |! |( dks| |! |( d kr|)| |! |(< n | |! |( })q^q<zjt#| f||||$|||| |
|
| d| ||||||||d|}*|*j$|
|
d||< | r|| j% &| ¡|| _%W q t'k
|
r2 t ||< Y q t(k
|
r }+ z0|+j)d d| df|+j)dd
|+_)|+W 5 d }+~+X Y qX q|d krtd|r |S || S d S )NrA FTzReading sheet ri r r z header index z exceeds maximum index z of data.c s$ g | ] \}} | s|kr|qS r_ r_ )Ú.0r r ©Z control_rowZindex_col_listr_ rb Ú
|
<listcomp>- s þz)BaseExcelReader.parse.<locals>.<listcomp>c s s | ]}|d kp|dkV qdS )Ú Nr_ )r r r_ r_ rb Ú <genexpr>2 s z(BaseExcelReader.parse.<locals>.<genexpr>r )r8 r7 r9 Úhas_index_namesr; r> r? r@ rA rB Zskip_blank_linesrF rG rH rI rJ rK rL r: rN )rA z (sheet: ú)zSheet name is an empty list)*r- r4 rf Úlistr rY r r r rZ ÚdictÚfromkeysÚkeysÚprintr
r r r rx ri r1 r' r& r ÚAssertionErrorr r% rh r. r2 ÚappendÚ enumerateÚallÚmaxÚranger3 rs ÚcolumnsZ set_namesr r{ Úargs),r| r\ r7 r8 r9 r: r; r> r? r@ rA rB rE rF rG rH rI rJ rK rL rN ÚkwdsZret_dictÚsheetsÚoutputZlast_sheetnameZ
|
asheetnamer Zfile_rows_neededrk Zis_list_headerZis_len_one_list_headerÚ header_namesÚrowÚ header_nameÚ_r Zpotential_index_namesZpotential_dataÚoffsetÚcolÚlastÚparserÚerrr_ r rb rj ´ sþ
|
|
|
(
|
|
|
|
|
|
|
ÿ
|
þ
|
|
|
$ÿëê
|
ÿ(
|
zBaseExcelReader.parse)N)N)Ú__name__Ú
|
__module__Ú __qualname__r~ ÚpropertyÚabcÚabstractmethodrv rz ri r r
r r r r r r r Ú
|
no_defaultrj r_ r_ r_ rb rl sT ÿ
|
Bërl )Ú metaclassc @ s e Zd ZU dZded< ded< dDdd d
|
d
|
d
|
dd d d d d
|
ddZdZeddddZeddddZ ee
|
j ddddZ ee
|
j dd Z e
|
j dEd
|
d d d d d d d!Ze
|
j d dd"d#ZdFd d
|
d
|
d
|
dd d
|
d$d d% d&d'Zeddd(d)Zeddd*d+Zeddd,d-Zddd.d/Zd
|
dd0d1d2Zd3dd4d5Zedd6d7d8d9Zd dd:d;Zd<d=d>d d?d@dAZd ddBdCZdS )GÚ ExcelWritera§
|
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 removing the old
|
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:
|
|
>>> df1 = pd.DataFrame([["AAA", "BBB"]], columns=["Spam", "Egg"]) # doctest: +SKIP
|
>>> df2 = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"]) # doctest: +SKIP
|
>>> with pd.ExcelWriter("path_to_file.xlsx") as writer:
|
... df1.to_excel(writer, sheet_name="Sheet1") # doctest: +SKIP
|
... df2.to_excel(writer, sheet_name="Sheet2") # doctest: +SKIP
|
|
You can set the date format or datetime format:
|
|
>>> from datetime import date, datetime # doctest: +SKIP
|
>>> df = pd.DataFrame(
|
... [
|
... [date(2014, 1, 31), date(1999, 9, 24)],
|
... [datetime(1998, 5, 26, 23, 33, 4), datetime(2014, 2, 28, 13, 5, 13)],
|
... ],
|
... index=["Date", "Datetime"],
|
... columns=["X", "Y"],
|
... ) # doctest: +SKIP
|
>>> with pd.ExcelWriter(
|
... "path_to_file.xlsx",
|
... date_format="YYYY-MM-DD",
|
... datetime_format="YYYY-MM-DD HH:MM:SS"
|
... ) as writer:
|
... df.to_excel(writer) # doctest: +SKIP
|
|
You can also append to an existing Excel file:
|
|
>>> with pd.ExcelWriter("path_to_file.xlsx", mode="a", engine="openpyxl") as writer:
|
... df.to_excel(writer, sheet_name="Sheet3") # doctest: +SKIP
|
|
Here, the `if_sheet_exists` parameter can be set to replace a sheet if it
|
already exists:
|
|
>>> with ExcelWriter(
|
... "path_to_file.xlsx",
|
... mode="a",
|
... engine="openpyxl",
|
... if_sheet_exists="replace",
|
... ) as writer:
|
... df.to_excel(writer, sheet_name="Sheet1") # doctest: +SKIP
|
|
You can also write multiple DataFrames to a single sheet. Note that the
|
``if_sheet_exists`` parameter needs to be set to ``overlay``:
|
|
>>> with ExcelWriter("path_to_file.xlsx",
|
... mode="a",
|
... engine="openpyxl",
|
... if_sheet_exists="overlay",
|
... ) as writer:
|
... df1.to_excel(writer, sheet_name="Sheet1")
|
... df2.to_excel(writer, sheet_name="Sheet1", startcol=3) # doctest: +SKIP
|
|
You can store Excel file in RAM:
|
|
>>> import io
|
>>> df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])
|
>>> buffer = io.BytesIO()
|
>>> with pd.ExcelWriter(buffer) as writer:
|
... df.to_excel(writer)
|
|
You can pack Excel file into zip archive:
|
|
>>> import zipfile # doctest: +SKIP
|
>>> df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"]) # doctest: +SKIP
|
>>> with zipfile.ZipFile("path_to_file.zip", "w") as zf:
|
... with zf.open("filename.xlsx", "w") as buffer:
|
... with pd.ExcelWriter(buffer) as writer:
|
... df.to_excel(writer) # doctest: +SKIP
|
|
You can specify additional arguments to the underlying engine:
|
|
>>> with pd.ExcelWriter(
|
... "path_to_file.xlsx",
|
... engine="xlsxwriter",
|
... engine_kwargs={{"options": {{"nan_inf_to_errors": True}}}}
|
... ) as writer:
|
... df.to_excel(writer) # doctest: +SKIP
|
|
In append mode, ``engine_kwargs`` are passed through to
|
openpyxl's ``load_workbook``:
|
|
>>> with pd.ExcelWriter(
|
... "path_to_file.xlsx",
|
... engine="openpyxl",
|
... mode="a",
|
... engine_kwargs={{"keep_vba": True}}
|
... ) as writer:
|
... df.to_excel(writer, sheet_name="Sheet2") # doctest: +SKIP
|
rY Ú_engineztuple[str, ...]Ú_supported_extensionsNÚwztype[ExcelWriter]z)FilePath | WriteExcelBuffer | ExcelWriterrX r z6Literal[('error', 'new', 'replace', 'overlay')] | Nonez dict | None)
|
ÚclsÚpathr< rH Údatetime_formatÚmoderM Úif_sheet_existsÚ engine_kwargsr] c
|
C sÌ | t krÂ|d ks"t|tr®|dkr®t|trFtj |¡d dd
} nd} z.tjd| ddd}|dkrvt| d d
|
}W n4 t k
|
r¬ }
|
zt
|
d | d |
|
W 5 d }
|
~
|
X Y nX |d k sºt t |} t | ¡S ) NÚautor r Úxlsxú io.excel.z.writerT©ZsilentÚwriter©rÇ zNo engine for filetype: 'ú')rÀ rf rY ÚosrÅ Úsplitextr Ú
|
get_optionr/ ÚKeyErrorrh r¤ r0 ÚobjectÚ__new__) rÄ rÅ r< rH rÆ rÇ rM rÈ rÉ Úextr· r_ r_ rb rÖ C s
|
$ zExcelWriter.__new__r c C s | j S )z'Extensions that writer engine supports.)r r r_ r_ rb Úsupported_extensionsf s z ExcelWriter.supported_extensionsc C s | j S )zName of engine.)rÁ r r_ r_ rb r< k s zExcelWriter.enginezdict[str, Any]c C s dS )z(Mapping of sheet names to sheet objects.Nr_ r r_ r_ rb r p s zExcelWriter.sheetsc C s dS )z
|
Book instance. Class type will depend on the engine used.
|
|
This attribute can be used to access engine-specific features.
|
Nr_ r r_ r_ rb rw u s zExcelWriter.bookr rZ ztuple[int, int] | Nonerm )r\ ÚstartrowÚstartcolÚ freeze_panesr] c C s dS )a
|
Write given formatted cells into Excel an excel sheet
|
|
Parameters
|
----------
|
cells : generator
|
cell of formatted data to save to Excel sheet
|
sheet_name : str, default None
|
Name of Excel sheet, if None, then use self.cur_sheet
|
startrow : upper left cell row to dump data frame
|
startcol : upper left cell column to dump data frame
|
freeze_panes: int tuple of length 2
|
contains the bottom-most row and right-most column to freeze
|
Nr_ )r| Úcellsr\ rÙ rÚ rÛ r_ r_ rb Ú _write_cells~ s zExcelWriter._write_cellsc C s dS )z(
|
Save workbook to disk.
|
Nr_ r r_ r_ rb Ú_save s zExcelWriter._savezdict[str, Any] | None) rÅ r< rH rÆ rÇ rM rÈ rÉ r] c
|
C sò t |tr$tj |¡d } | | ¡ d|kr4|d7 }| dd¡}|dkrXtd| d|rld|krltd|d krxd }|| _t t
|
t t |d
|
d id | _ t |ts¶t|||d d | _ d | _|d krÌd| _n|| _|d krâd| _n|| _|| _d S )Nr ÚbÚazr+)NÚerrorÚnewÚreplaceÚoverlayrÐ z^' is not valid for if_sheet_exists. Valid options are 'error', 'new', 'replace' and 'overlay'.z7if_sheet_exists is only valid in append mode (mode='a')rá rp )rp Frr z
|
YYYY-MM-DDzYYYY-MM-DD HH:MM:SS)rf rY rÑ rÅ rÒ Úcheck_extensionrã rh Ú_if_sheet_existsr* r r rt Ú_handlesrÀ r+ Ú
|
_cur_sheetÚ _date_formatÚ_datetime_formatÚ_mode)
|
r| rÅ r< rH rÆ rÇ rM rÈ rÉ r× r_ r_ rb r~ sD
|
|
|
ÿ ÿ
|
ÿzExcelWriter.__init__c C s | j S ©u[
|
Format string for dates written into Excel files (e.g. âYYYY-MM-DDâ).
|
)ré r r_ r_ rb rH Ó s zExcelWriter.date_formatc C s | j S rì )rê r r_ r_ rb rÆ Ú s zExcelWriter.datetime_formatc C s | j S )z[
|
How to behave when writing to a sheet that already exists in append mode.
|
)ræ r r_ r_ rb rÈ á s zExcelWriter.if_sheet_existsc C s t | jjddS )Nr r )Úgetattrrç ro r r_ r_ rb Ú
|
__fspath__è s zExcelWriter.__fspath__)r\ r] c C s" |d kr| j }|d kr td|S )Nz8Must pass explicit sheet_name or set _cur_sheet property)rè rh )r| r\ r_ r_ rb Ú_get_sheet_nameë s
|
zExcelWriter._get_sheet_nameztuple[object, str | None]c C s d}t |rt|}nrt|r(t|}n`t|r:t|}nNt|tjrN| j}n:t|tj rb| j
|
}n&t|tj r| ¡ d }d}nt |}||fS )aG
|
Convert numpy types to Python types for the Excel writers.
|
|
Parameters
|
----------
|
val : object
|
Value to be written into cells
|
|
Returns
|
-------
|
Tuple with the first element being the converted value and the second
|
being an optional format
|
NiQ Ú0)r% rZ r$ Úfloatr# rT rf Údatetimerê Údateré Ú timedeltaÚ total_secondsrY )r| ÚvalÚfmtr_ r_ rb Ú_value_with_fmtò s
|
|
|
zExcelWriter._value_with_fmtz Literal[True])r× r] c sJ d¡r dd
t fdd| jD sFtd| j d dd S )
|
z
|
checks that path's extension against the Writer's supported
|
extensions. If it isn't supported, raises UnsupportedFiletypeError.
|
re r Nc 3 s | ]} |kV qd S r^ r_ )r Ú extension©r× r_ rb r s z.ExcelWriter.check_extension.<locals>.<genexpr>z Invalid extension for engine 'z': 'rÐ T)Ú
|
startswithÚanyr rh r< )rÄ r× r_ rú rb rå s
|
|
zExcelWriter.check_extensionc C s | S r^ r_ r r_ r_ rb Ú __enter__! s zExcelWriter.__enter__útype[BaseException] | NoneúBaseException | NoneúTracebackType | None©Úexc_typeÚ exc_valueÚ tracebackr] c C s | ¡ d S r^ ©ri ©r| r r r r_ r_ rb Ú__exit__$ s zExcelWriter.__exit__c C s | ¡ | j ¡ dS )z+synonym for save, to make it more file-likeN)rÞ rç ri r r_ r_ rb ri , s zExcelWriter.close)NNNrà NNN)Nr r N)NNNrà NNN) r¸ r¹ rº Ú__doc__Ú__annotations__rÖ Ú_pathr» rØ r< r¼ r½ r rw rÝ rÞ r~ rH rÆ rÈ rî rï rø Ú classmethodrå rý r ri r_ r_ r_ rb rÀ sj
|
; ÷"! ú ÷ 7" rÀ )s s s s ÐÏࡱás PKz FilePath | ReadBuffer[bytes])Úcontent_or_pathrM r] c
|
s, t | trt| } t| d|dd þ}|j}| d¡ | t¡}|dkrPtdt |ts^t | | d¡ t
|
fddt D rW 5 Q R £ d S t ¡s¨W 5 Q R £ dS t |¡}d
|
d | ¡ D }W 5 Q R X d |kræW 5 Q R £ d S d|krüW 5 Q R £ dS d|krW 5 Q R £ dS W 5 Q R £ dS Q R X dS )a8
|
Inspect the path or content of an excel file and get its format.
|
|
Adopted from xlrd: https://github.com/python-excel/xlrd.
|
|
Parameters
|
----------
|
content_or_path : str or file-like object
|
Path to file or content of file to inspect. May be a URL.
|
{storage_options}
|
|
Returns
|
-------
|
str or None
|
Format of file if it can be determined.
|
|
Raises
|
------
|
ValueError
|
If resulting stream is empty.
|
BadZipFile
|
If resulting stream does not have an XLS signature and is not a valid zipfile.
|
rq Frr r Nzstream is emptyc 3 s | ]} |¡V qd S r^ )rû )r Úsig©Úpeekr_ rb r g s z'inspect_excel_format.<locals>.<genexpr>Úxlsc S s g | ]}| d d¡ ¡ qS )ú\ú/)rã Úlower)r r r_ r_ rb r o s z(inspect_excel_format.<locals>.<listcomp>zxl/workbook.xmlrË zxl/workbook.binZxlsbz content.xmlZodsÚzip)rf rt r r+ ro ry rs Ú PEEK_SIZErh r¤ rü ÚXLS_SIGNATURESrû Ú ZIP_SIGNATUREÚzipfileÚZipFileÚnamelist)r rM ro ÚstreamÚbufÚzfZcomponent_namesr_ r rb Úinspect_excel_format< s@
|
ÿ
|
|
|
|
ÿ
|
r c @ s e Zd ZU dZddlmZ ddlmZ ddlm Z ddl
|
m Z e eee dZ de d < d1d d d dddZdd Zddd
|
d
|
d
|
d
|
d
|
d
|
d
|
d
|
d
|
dejd
|
d
|
d
|
dejfddddddddddd d d d d d d d!Zed"d# Zed$d% Zd d&d'd(Zd d&d)d*Zd+d,d-d d.d/d0Zd
|
S )2rg a¯
|
Class for parsing tabular Excel sheets into DataFrame objects.
|
|
See read_excel for more documentation.
|
|
Parameters
|
----------
|
path_or_buffer : str, bytes, path object (pathlib.Path or py._path.local.LocalPath),
|
A file-like object, xlrd workbook or openpyxl workbook.
|
If a string or path object, expected to be a path to a
|
.xls, .xlsx, .xlsb, .xlsm, .odf, .ods, or .odt file.
|
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 <https://pypi.org/project/pyxlsb/>`_ will be used.
|
|
.. versionadded:: 1.3.0
|
|
- Otherwise if `openpyxl <https://pypi.org/project/openpyxl/>`_ is installed,
|
then ``openpyxl`` will be used.
|
- Otherwise if ``xlrd >= 2.0`` is installed, a ``ValueError`` will be raised.
|
|
.. warning::
|
|
Please do not report issues when using ``xlrd`` to read ``.xlsx`` files.
|
This is not supported, switch to using ``openpyxl`` instead.
|
r )Ú ODFReader)ÚOpenpyxlReader)Ú PyxlsbReader)Ú
|
XlrdReader)ÚxlrdZopenpyxlZodfZpyxlsbzMapping[str, Any]Ú_enginesNrX r rm )r< rM r] c C s |d k r || j kr td| t|tr2t|}|| _t|| _tdddd krXd }ndd l }t
|
t |}|d krÔ|d k rt||j rd}n t ||d}|d krªtdtjd | d
|
d d }|d krÔt|dd}|d k sàt|| _|| _| j | | j|d| _d S )NzUnknown engine: r# Úignore)Úerrorsr r )r rM zLExcel file format cannot be determined, you must specify an engine manually.rÌ z.readerTrÍ rÊ ÚreaderrÏ rd )r$ rh rf rt r ra r, Ú_ior r# r) r ZBookr r rÓ r/ r¤ r< rM Ú_reader)r| Zpath_or_bufferr< rM Z xlrd_versionr# r× r_ r_ rb r~ ¸ s8
|
|
ÿÿ zExcelFile.__init__c C s | j S r^ )r( r r_ r_ rb rî ë s zExcelFile.__fspath__Fr rO rQ rR rS rU rV z str | dict[Hashable, str] | NonerZ r[ z7DataFrame | dict[str, DataFrame] | dict[int, DataFrame])r\ r7 r9 r> r? r@ rA rF rG rH rI rK rL rN r] c K s8 | j jf ||||||||| |
|
| | | |||||d|S )a8
|
Parse specified sheet(s) into a DataFrame.
|
|
Equivalent to read_excel(ExcelFile, ...) See the read_excel
|
docstring for more info on accepted parameters.
|
|
Returns
|
-------
|
DataFrame or dict of DataFrames
|
DataFrame from the passed in Excel file.
|
)r\ r7 r8 r9 r: r= r> r? r@ rA rB rF rG rH rI rK rL rN )r) rj )r| r\ r7 r8 r9 r: r= r> r? r@ rA rB rF rG rH rI rK rL rN r¬ r_ r_ rb rj î s, !îízExcelFile.parsec C s | j jS r^ )r) rw r r_ r_ rb rw % s zExcelFile.bookc C s | j jS r^ )r) r r r_ r_ rb r ) s zExcelFile.sheet_namesr c C s | j ¡ dS )zclose io if necessaryN)r) ri r r_ r_ rb ri - s zExcelFile.closec C s | S r^ r_ r r_ r_ rb rý 1 s zExcelFile.__enter__rþ rÿ r r c C s | ¡ d S r^ r r r_ r_ rb r 4 s zExcelFile.__exit__)NN)r¸ r¹ rº r Zpandas.io.excel._odfreaderr Zpandas.io.excel._openpyxlr Zpandas.io.excel._pyxlsbr! Zpandas.io.excel._xlrdr" r$ r r~ rî r r¾ rj r» rw r ri rý r r_ r_ r_ rb rg | sP
|
/ ü
|
ü3í,7
|
|
rg ).)r )N)]Ú
|
__future__r r¼ rò Ú functoolsr ra r rÑ Útextwrapr Útypesr Útypingr r r r
|
r r r r r r r r r Zpandas._configr Z pandas._libsr Zpandas._libs.parsersr Zpandas._typingr r r r r r r Zpandas.compat._optionalr r Z pandas.errorsr Zpandas.util._decoratorsr r! Zpandas.util._validatorsr" Zpandas.core.dtypes.commonr# r$ r% r& Zpandas.core.framer' Zpandas.core.shared_docsr( Zpandas.util.versionr) Zpandas.io.commonr* r+ r, r- Zpandas.io.excel._utilr. r/ r0 r1 r2 Zpandas.io.parsersr3 Zpandas.io.parsers.readersr4 ÚjoinÚsortedZ_read_excel_docrc r¾ ÚABCMetarl rÀ r r r¨ Úmapr r r rg r_ r_ r_ rb Ú<module> s 8 $ vwÿ ýßD&ßD& þàFO 0 þ?
|
