U
|
¬ý°d?N ã @ sÔ d dl mZ d dlZd dlmZmZmZmZ d dlZ d dl
|
m Z m Z m Z mZmZ d dlmZ d dlmZ d dlmZ d dlmZmZ d d lmZmZ er°d d
|
l m Z d d l m Z G d d d eZ G dd deZ!dS )é )Ú annotationsN)Ú TYPE_CHECKINGÚAnyÚTupleÚcast)ÚFilePathÚ
|
ReadBufferÚScalarÚStorageOptionsÚWriteExcelBuffer)Úimport_optional_dependency)Údoc)Ú _shared_docs)ÚBaseExcelReaderÚ ExcelWriter)Úcombine_kwargsÚvalidate_freeze_panes)Ú Serialisable©ÚWorkbookc s e Zd ZdZdZd5dddddddd d
|
d fd d ZeddddZeddddZd
|
dddZ e
|
dddddZ e
|
d d Z e
|
d d Z e
|
d d! Ze
|
dd"d#d$Ze
|
d%d& Ze
|
d'd( Ze
|
d)d* Ze
|
d+d, Ze
|
d-d. Zd6dd0d0d1d
|
d2d3d4Z ZS )7ÚOpenpyxlWriterÚopenpyxl)z.xlsxz.xlsmNÚwz)FilePath | WriteExcelBuffer | ExcelWriterz
|
str | NoneÚstrr
|
zdict[str, Any] | NoneÚNone) ÚpathÚengineÚ date_formatÚdatetime_formatÚmodeÚstorage_optionsÚif_sheet_existsÚ engine_kwargsÚreturnc sà ddl m}
|
t|| }t j|||||d d| jkrddlm} z| | jj f|| _
|
W n" t k
|
rz | jj ¡ Y nX | jj d¡ nPz|
|
f || _
|
W n" t k
|
r¾ | jj ¡ Y nX | jjrÜ| j | jjd ¡ d S )Nr r )r r r! r" úr+©Ú load_workbook)Úopenpyxl.workbookr r ÚsuperÚ__init__Ú_moder r& Ú_handlesÚhandleÚ_bookÚ TypeErrorÚcloseÚseekÚbookÚ
|
worksheetsÚremove) Úselfr r r r r r r! r" Úkwargsr r& ©Ú __class__© úPd:\z\workplace\vscode\pyvenv\venv\Lib\site-packages\pandas/io/excel/_openpyxl.pyr) + s0
|
û
|
|
zOpenpyxlWriter.__init__r ©r# c C s | j S )z
|
Book instance of class openpyxl.workbook.Workbook.
|
|
This attribute can be used to access engine-specific features.
|
)r- ©r4 r8 r8 r9 r1 Z s zOpenpyxlWriter.bookzdict[str, Any]c s fdd j jD }|S )z(Mapping of sheet names to sheet objects.c s i | ]}| j | qS r8 )r1 )Ú.0Únamer; r8 r9 Ú
|
<dictcomp>f s z)OpenpyxlWriter.sheets.<locals>.<dictcomp>)r1 Z
|
sheetnames)r4 Úresultr8 r; r9 Úsheetsc s zOpenpyxlWriter.sheetsc C s: | j | jj¡ d| jkr6t| jjtjs6| jj ¡ dS )z(
|
Save workbook to disk.
|
r$ N)r1 Úsaver+ r, r* Ú
|
isinstanceÚmmapÚtruncater; r8 r8 r9 Ú_savei s zOpenpyxlWriter._saveÚdictzdict[str, Serialisable])Ú
|
style_dictr# c C sX ddi}i }| ¡ D ]>\}}| ||¡}t| d| dd }||}|r|||< q|S )aÁ
|
Convert a style_dict to a set of kwargs suitable for initializing
|
or updating-on-copy an openpyxl v2 style object.
|
|
Parameters
|
----------
|
style_dict : dict
|
A dict with zero or more of the following keys (or their synonyms).
|
'font'
|
'fill'
|
'border' ('borders')
|
'alignment'
|
'number_format'
|
'protection'
|
|
Returns
|
-------
|
style_kwargs : dict
|
A dict with the same, normalized keys as ``style_dict`` but each
|
value has been replaced with a native openpyxl style object of the
|
appropriate class.
|
ZbordersZborderZ _convert_to_c S s d S ©Nr8 )Úxr8 r8 r9 Ú<lambda> ó z9OpenpyxlWriter._convert_to_style_kwargs.<locals>.<lambda>)ÚitemsÚgetÚgetattr)ÚclsrG Z_style_key_mapÚ style_kwargsÚkÚvZ
|
_conv_to_xZnew_vr8 r8 r9 Ú_convert_to_style_kwargsr s
|
z'OpenpyxlWriter._convert_to_style_kwargsc C s, ddl m} t|tr ||S |f |S dS )aß
|
Convert ``color_spec`` to an openpyxl v2 Color object.
|
|
Parameters
|
----------
|
color_spec : str, dict
|
A 32-bit ARGB hex string, or a dict with zero or more of the
|
following keys.
|
'rgb'
|
'indexed'
|
'auto'
|
'theme'
|
'tint'
|
'index'
|
'type'
|
|
Returns
|
-------
|
color : openpyxl.styles.Color
|
r )ÚColorN)Úopenpyxl.stylesrT rB r )rO Z
|
color_specrT r8 r8 r9 Ú_convert_to_color s
|
z OpenpyxlWriter._convert_to_colorc C sd ddl m} ddddddd }i }| ¡ D ].\}}| ||¡}|d
|
krP| |¡}|||< q*|f |S ) aµ
|
Convert ``font_dict`` to an openpyxl v2 Font object.
|
|
Parameters
|
----------
|
font_dict : dict
|
A dict with zero or more of the following keys (or their synonyms).
|
'name'
|
'size' ('sz')
|
'bold' ('b')
|
'italic' ('i')
|
'underline' ('u')
|
'strikethrough' ('strike')
|
'color'
|
'vertAlign' ('vertalign')
|
'charset'
|
'scheme'
|
'family'
|
'outline'
|
'shadow'
|
'condense'
|
|
Returns
|
-------
|
font : openpyxl.styles.Font
|
r )ÚFontÚsizeZboldZitalicZ underlineZ strikethroughZ vertAlign)ÚszÚbÚiÚuÚstrikeZ vertalignÚcolor)rU rW rL rM rV )rO Z font_dictrW Z _font_key_mapZ font_kwargsrQ rR r8 r8 r9 Ú_convert_to_font³ s ú
|
|
z OpenpyxlWriter._convert_to_fontc C s t | j|S )a
|
Convert ``stop_seq`` to a list of openpyxl v2 Color objects,
|
suitable for initializing the ``GradientFill`` ``stop`` parameter.
|
|
Parameters
|
----------
|
stop_seq : iterable
|
An iterable that yields objects suitable for consumption by
|
``_convert_to_color``.
|
|
Returns
|
-------
|
stop : list of openpyxl.styles.Color
|
)ÚmaprV )rO Zstop_seqr8 r8 r9 Ú_convert_to_stopã s z OpenpyxlWriter._convert_to_stop)Ú fill_dictc C sØ ddl m}m} ddddddd}ddi}i }i }| ¡ D ]l\}} | |¡}
|
| |¡} |
|
dkrh| | ¡} | d krz| | ¡} |
|
r| ||
|
< q:| r| || < q:| ||< | ||< q:z |f |W S tk
|
rÒ |f | Y S X d
|
S ) a¶
|
Convert ``fill_dict`` to an openpyxl v2 Fill object.
|
|
Parameters
|
----------
|
fill_dict : dict
|
A dict with one or more of the following keys (or their synonyms),
|
'fill_type' ('patternType', 'patterntype')
|
'start_color' ('fgColor', 'fgcolor')
|
'end_color' ('bgColor', 'bgcolor')
|
or one or more of the following keys (or their synonyms).
|
'type' ('fill_type')
|
'degree'
|
'left'
|
'right'
|
'top'
|
'bottom'
|
'stop'
|
|
Returns
|
-------
|
fill : openpyxl.styles.Fill
|
r )Ú GradientFillÚ PatternFillZ fill_typeÚ start_colorÚ end_color)Z patternTypeZ patterntypeZfgColorZfgcolorZbgColorZbgcolorÚtype)re rf ÚstopN)rU rc rd rL rM rV ra r. ) rO rb rc rd Z_pattern_fill_key_mapZ_gradient_fill_key_mapZ pfill_kwargsZ gfill_kwargsrQ rR ÚpkZgkr8 r8 r9 Ú_convert_to_fillõ s8 ú
|
|
|
|
|
|
|
z OpenpyxlWriter._convert_to_fillc C sn ddl m} ddi}t|tr(||dS i }| ¡ D ].\}}| ||¡}|dkrZ| |¡}|||< q4|f |S )a
|
Convert ``side_spec`` to an openpyxl v2 Side object.
|
|
Parameters
|
----------
|
side_spec : str, dict
|
A string specifying the border style, or a dict with zero or more
|
of the following keys (or their synonyms).
|
'style' ('border_style')
|
'color'
|
|
Returns
|
-------
|
side : openpyxl.styles.Side
|
r )ÚSideZ border_styleÚstyle)rl r^ )rU rk rB r rL rM rV )rO Z side_specrk Z _side_key_mapZ side_kwargsrQ rR r8 r8 r9 Ú_convert_to_side4 s
|
|
|
|
z OpenpyxlWriter._convert_to_sidec C sn ddl m} ddd}i }| ¡ D ]@\}}| ||¡}|dkrH| |¡}|dkrZ| |¡}|||< q"|f |S )aq
|
Convert ``border_dict`` to an openpyxl v2 Border object.
|
|
Parameters
|
----------
|
border_dict : dict
|
A dict with zero or more of the following keys (or their synonyms).
|
'left'
|
'right'
|
'top'
|
'bottom'
|
'diagonal'
|
'diagonal_direction'
|
'vertical'
|
'horizontal'
|
'diagonalUp' ('diagonalup')
|
'diagonalDown' ('diagonaldown')
|
'outline'
|
|
Returns
|
-------
|
border : openpyxl.styles.Border
|
r )ÚBorderZ
|
diagonalUpZ diagonalDown)Z
|
diagonalupZ diagonaldownr^ )ÚleftÚrightÚtopÚbottomZdiagonal)rU rn rL rM rV rm )rO Z border_dictrn Z_border_key_mapZ border_kwargsrQ rR r8 r8 r9 Ú_convert_to_borderU s
|
|
|
|
z!OpenpyxlWriter._convert_to_borderc C s ddl m} |f |S )aà
|
Convert ``alignment_dict`` to an openpyxl v2 Alignment object.
|
|
Parameters
|
----------
|
alignment_dict : dict
|
A dict with zero or more of the following keys (or their synonyms).
|
'horizontal'
|
'vertical'
|
'text_rotation'
|
'wrap_text'
|
'shrink_to_fit'
|
'indent'
|
Returns
|
-------
|
alignment : openpyxl.styles.Alignment
|
r )Ú Alignment)rU rt )rO Zalignment_dictrt r8 r8 r9 Ú_convert_to_alignment} s z$OpenpyxlWriter._convert_to_alignmentc C s |d S )aO
|
Convert ``number_format_dict`` to an openpyxl v2.1.0 number format
|
initializer.
|
|
Parameters
|
----------
|
number_format_dict : dict
|
A dict with zero or more of the following keys.
|
'format_code' : str
|
|
Returns
|
-------
|
number_format : str
|
Z format_coder8 )rO Znumber_format_dictr8 r8 r9 Ú_convert_to_number_format s z(OpenpyxlWriter._convert_to_number_formatc C s ddl m} |f |S )a'
|
Convert ``protection_dict`` to an openpyxl v2 Protection object.
|
|
Parameters
|
----------
|
protection_dict : dict
|
A dict with zero or more of the following keys.
|
'locked'
|
'hidden'
|
|
Returns
|
-------
|
r )Ú
|
Protection)rU rw )rO Zprotection_dictrw r8 r8 r9 Ú_convert_to_protection¦ s z%OpenpyxlWriter._convert_to_protectionr Úintztuple[int, int] | None)Ú
|
sheet_nameÚstartrowÚstartcolÚ freeze_panesr# c C sº | |¡}i }|| jkr¶| jdkr¶d| jkrª| jdkrd| j| }| j |¡}| j|= | j ||¡} q´| jdkrtd| dq´| jdkr| j| } q´td| j d qÆ| j| } n| j ¡ } || _t |rt
|
t t t f |}| j |d
|
d |d d d | _|D ]¬}
|
| j ||
|
j d ||
|
j d d } | |
|
j¡\| _} | rL| | _i } |
|
jrt|
|
j}| |¡} | d kr| |
|
j¡} | ||< | r°| ¡ D ]\}}t| || q|
|
jd k r|
|
j d k r| j ||
|
j d ||
|
j d ||
|
j d ||
|
j d d | r||
|
j d }||
|
j d }||
|
j d }||
|
j d }t ||d D ]b}t ||d D ]L}||kr|||kr|q`| j ||d} | ¡ D ]\}}t| || qq`qNqd S )NÚnewr$ ÚreplaceÚerrorzSheet 'z7' already exists and if_sheet_exists is set to 'error'.Úoverlayú'z^' is not valid for if_sheet_exists. Valid options are 'error', 'new', 'replace' and 'overlay'.r é )ÚrowÚcolumn)Z start_rowZ start_columnZ
|
end_columnZend_row)r
r ) Z_get_sheet_namer@ Z_if_sheet_existsr* r1 ÚindexZ create_sheetÚ
|
ValueErrorÚtitler r r ry Úcellr} r ÚcolZ_value_with_fmtÚvalÚvalueZ number_formatrl r rM rS rL ÚsetattrZ
|
mergestartZmergeendZ merge_cellsÚrange)r4 Úcellsrz r{ r| r} Z _style_cacheZold_wksZ target_indexZwksr ZxcellÚfmtrP ÚkeyrQ rR Z first_rowZlast_rowZ first_colZlast_colr r r8 r8 r9 Ú _write_cells¹ s
|
|
|
|
|
|
ÿ
|
ÿ
|
|
|
|
ÿ
|
ÿ
|
|
|
ü
|
zOpenpyxlWriter._write_cells)NNNr NNN)Nr r N)Ú__name__Ú
|
__module__Ú __qualname__Z_engineZ_supported_extensionsr) Úpropertyr1 r@ rE Ú classmethodrS rV r_ ra rj rm rs ru rv rx r Ú __classcell__r8 r8 r6 r9 r ' sP ÷$/ #
|
|
/
|
>
|
|
'
|
|
|
úr c s e Zd Zeed dd#dddd fdd Zed
|
d Zdd d dZeddddZ ddddZ
|
ddddZ ddd d Z d$d d d d!d"Z ZS )%ÚOpenpyxlReaderr ©r Nz FilePath | ReadBuffer[bytes]r
|
r )Úfilepath_or_bufferr r# c s t d t j||d dS )zË
|
Reader using openpyxl engine.
|
|
Parameters
|
----------
|
filepath_or_buffer : str, path object or Workbook
|
Object to be parsed.
|
{storage_options}
|
r r N)r r( r) )r4 r r r6 r8 r9 r) s zOpenpyxlReader.__init__c C s ddl m} |S )Nr r )r r )r4 r r8 r8 r9 Ú_workbook_class' s z OpenpyxlReader._workbook_class)r c C s ddl m} ||ddddS )Nr r% TF)Ú read_onlyZ data_onlyZ
|
keep_links)r r& )r4 r r& r8 r8 r9 r& - s ÿz OpenpyxlReader.load_workbookz list[str]r: c C s dd | j jD S )Nc S s g | ]
|
}|j qS r8 )r )r< Úsheetr8 r8 r9 Ú
|
<listcomp>6 s z.OpenpyxlReader.sheet_names.<locals>.<listcomp>)r1 r2 r; r8 r8 r9 Ú sheet_names4 s zOpenpyxlReader.sheet_namesr )r= c C s | |¡ | j| S rH )Zraise_if_bad_sheet_by_namer1 )r4 r= r8 r8 r9 Úget_sheet_by_name8 s
|
z OpenpyxlReader.get_sheet_by_namery )r c C s | |¡ | jj| S rH )Zraise_if_bad_sheet_by_indexr1 r2 )r4 r r8 r8 r9 Úget_sheet_by_index< s
|
z!OpenpyxlReader.get_sheet_by_indexr c C s` ddl m}m} |jd kr dS |j|kr.tjS |j|krZt|j}||jkrP|S t|jS |jS )Nr )Ú
|
TYPE_ERRORÚ TYPE_NUMERICÚ ) Zopenpyxl.cell.cellr£ r¤ r Z data_typeÚnpÚnanry Úfloat)r4 r r£ r¤ r r8 r8 r9 Ú _convert_cell@ s
|
|
|
|
|
|
z OpenpyxlReader._convert_cellz
|
int | Nonezlist[list[Scalar]])Úfile_rows_neededr# c sä j jr| ¡ g }d}t|jD ]^\}}fdd|D }|rV|d dkrV| ¡ q<|r^|}| |¡ |d k r"t||kr" qq"|d |d
}t|dkràtdd |D t d d |D k ràdg fd
|
d|D }|S ) Néÿÿÿÿc s g | ]} |¡qS r8 )r© )r< r r; r8 r9 r [ s z1OpenpyxlReader.get_sheet_data.<locals>.<listcomp>r¥ r r c s s | ]}t |V qd S rH ©Úlen©r< Zdata_rowr8 r8 r9 Ú <genexpr>j s z0OpenpyxlReader.get_sheet_data.<locals>.<genexpr>c s s | ]}t |V qd S rH r¬ r® r8 r8 r9 r¯ k s c s g | ]}|t | qS r8 r¬ r® )Ú
|
empty_cellÚ max_widthr8 r9 r m s ÿ)
|
r1 r Zreset_dimensionsÚ enumerateÚrowsÚpopÚappendr ÚmaxÚmin)r4 r rª ÚdataZlast_row_with_dataZ
|
row_numberr Z converted_rowr8 )r° r± r4 r9 Úget_sheet_dataR s,
|
|
þz OpenpyxlReader.get_sheet_data)N)N)r r r r r r) r r r& r r¡ r¢ r© r¹ r r8 r8 r6 r9 r s ý
|
ÿr )"Ú
|
__future__r rC Útypingr r r r Únumpyr¦ Zpandas._typingr r r r
|
r Zpandas.compat._optionalr Zpandas.util._decoratorsr Zpandas.core.shared_docsr Zpandas.io.excel._baser r Zpandas.io.excel._utilr r Z!openpyxl.descriptors.serialisabler r' r r r r8 r8 r8 r9 Ú<module> s" p
|
