U b!I@sddlmZddlZddlmZmZddlZddlm Z m Z m Z m Z ddl mZddlmZmZddlmZmZerddlmZGd d d eZGd d d eZdS) ) annotationsN) TYPE_CHECKINGAny)FilePath ReadBufferScalarStorageOptions)import_optional_dependency)BaseExcelReader ExcelWriter)combine_kwargsvalidate_freeze_panes) SerialisablecseZdZdZdZd(ddddd fd d Zd d ZedddddZeddZ eddZ eddZ eddZ eddZ eddZedd Zed!d"Zed#d$Zd)d&d'ZZS)*OpenpyxlWriteropenpyxl)z.xlsxz.xlsmNwstrrz str | Nonezdict[str, Any] | Nonemodestorage_optionsif_sheet_exists engine_kwargsc  sddlm} t|| }tj|||||ddjkr|ddlm} | jj f|_ jj dfddj j D_ n(| f|_ j jrj j jddS)NrWorkbookrr+ load_workbookcsi|]}|j|qS)book).0nameselfr=/tmp/pip-unpacked-wheel-ck39h295/pandas/io/excel/_openpyxl.py Dsz+OpenpyxlWriter.__init__..)Zopenpyxl.workbookrr super__init__rrrhandleshandlerseekZ sheetnamessheets worksheetsremove) r"pathengineZ date_formatZdatetime_formatrrrrkwargsrr __class__r!r#r&$s"     zOpenpyxlWriter.__init__cCs:|j|jjd|jkr6t|jjtjs6|jjdS)z( Save workbook to disk. rN)rsaver'r(r isinstancemmaptruncater!rrr#r2MszOpenpyxlWriter.savedictzdict[str, Serialisable]) style_dictreturncCs\ddi}i}|D]B\}}||kr,||}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_cSsdSNr)xrrr#tz9OpenpyxlWriter._convert_to_style_kwargs..)itemsgetattr)clsr7Z_style_key_map style_kwargskvZ _conv_to_xZnew_vrrr#_convert_to_style_kwargsVs z'OpenpyxlWriter._convert_to_style_kwargscCs,ddlm}t|tr||S|f|SdS)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.stylesrDr3r)r?Z color_specrDrrr#_convert_to_color{s  z OpenpyxlWriter._convert_to_colorcCshddlm}ddddddd }i}|D]2\}}||krB||}|d krT||}|||<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)FontsizeZboldZitalicZ underlineZ strikethroughZ vertAlign)szbiustrikeZ vertaligncolor)rErGr=rF)r?Z font_dictrGZ _font_key_mapZ font_kwargsrArBrrr#_convert_to_fonts     zOpenpyxlWriter._convert_to_fontcCs 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 )maprF)r?Zstop_seqrrr#_convert_to_stopszOpenpyxlWriter._convert_to_stopc Csddlm}m}ddddddd}ddi}i}i}|D]\}} d} } ||krZ||} ||krj||} | d kr||| } | d kr|| } | r| || <q:| r| || <q:| ||<| ||<q:z |f|WStk r|f|YSXdS) 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 patterntypeZfgColorZfgcolorZbgColorZbgcolortypeN)rTrUstop)rErRrSr=rFrQ TypeError) r?Z fill_dictrRrSZ_pattern_fill_key_mapZ_gradient_fill_key_mapZ pfill_kwargsZ gfill_kwargsrArBpkZgkrrr#_convert_to_fills>       zOpenpyxlWriter._convert_to_fillcCsrddlm}ddi}t|tr(||dSi}|D]2\}}||krL||}|dkr^||}|||<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_stylestyle)r\rN)rEr[r3rr=rF)r?Z side_specr[Z _side_key_mapZ side_kwargsrArBrrr#_convert_to_sides     zOpenpyxlWriter._convert_to_sidecCsrddlm}ddd}i}|D]D\}}||kr:||}|dkrL||}|dkr^||}|||<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 diagonaldownrN)leftrighttopZbottomZdiagonal)rEr^r=rFr])r?Z border_dictr^Z_border_key_mapZ border_kwargsrArBrrr#_convert_to_border?s     z!OpenpyxlWriter._convert_to_bordercCsddlm}|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)rErc)r?Zalignment_dictrcrrr#_convert_to_alignmenths z$OpenpyxlWriter._convert_to_alignmentcCs|dS)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_coder)r?Znumber_format_dictrrr#_convert_to_number_formatsz(OpenpyxlWriter._convert_to_number_formatcCsddlm}|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)rErf)r?Zprotection_dictrfrrr#_convert_to_protections z%OpenpyxlWriter._convert_to_protectionrcCs||}i}||jkr|jdkrd|jkr|jdkrn|j|}|j|}|j|=|j||} | |j|<q|jdkrtd|dq|jdkr|j|} qtd|jd q|j|} n|j} || _| |j|<t |r| j |d d |d d d | _ |D]} | j || j d || j d d } || j\| _} | rN| | _i} | jrt| j}||} | dkr|| j} | ||<| r| D]\}}t| ||q| jdk r| jdk r| j|| j d || j d || jd || jd d | r|| j d }|| jd }|| j d }|| jd }t||d D]b}t||d D]L}||kr~||kr~qb| j ||d} | D]\}}t| ||qqbqPqdS)NnewrreplaceerrorzSheet '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)rowcolumn)Z start_rowZ start_columnZ end_columnZend_row)rorn)Z_get_sheet_namer*rrrindexZ create_sheet ValueErrortitler cell freeze_panesrncolZ_value_with_fmtvalvalueZ number_formatr\rgetrCr=setattrZ mergestartZmergeendZ merge_cellsrange)r"cellsZ sheet_nameZstartrowZstartcolrtZ _style_cacheZold_wksZ target_indexZwksrsZxcellfmtr@keyrArBZ first_rowZlast_rowZ first_colZlast_colrnrurrr# write_cellss                           zOpenpyxlWriter.write_cells)NNNrNNN)NrrN)__name__ __module__ __qualname__r.Zsupported_extensionsr&r2 classmethodrCrFrOrQrZr]rbrdrergr~ __classcell__rrr0r#r sH) $  0  A ! (   rcseZdZd!ddddfdd Zedd Zdd d d Zed dddZddddZddddZ dddddZ ddddd Z Z S)"OpenpyxlReaderNzFilePath | ReadBuffer[bytes]rNone)filepath_or_bufferrr8cstdtj||ddS)a. Reader using openpyxl engine. Parameters ---------- filepath_or_buffer : str, path object or Workbook Object to be parsed. storage_options : dict, optional passed to fsspec for appropriate URLs (see ``_get_filepath_or_buffer``) r)rN)r r%r&)r"rrr0rr#r&szOpenpyxlReader.__init__cCsddlm}|S)Nrr)rr)r"rrrr#_workbook_classs zOpenpyxlReader._workbook_class)rcCsddlm}||ddddS)NrrTF) read_onlyZ data_onlyZ keep_links)rr)r"rrrrr#rs zOpenpyxlReader.load_workbookz list[str])r8cCsdd|jjDS)NcSsg|] }|jqSr)rr)rsheetrrr# sz.OpenpyxlReader.sheet_names..)rr+r!rrr# sheet_namesszOpenpyxlReader.sheet_namesr)r cCs|||j|Sr9)Zraise_if_bad_sheet_by_namer)r"r rrr#get_sheet_by_name s z OpenpyxlReader.get_sheet_by_nameint)rpcCs|||jj|Sr9)Zraise_if_bad_sheet_by_indexrr+)r"rprrr#get_sheet_by_index$s z!OpenpyxlReader.get_sheet_by_indexboolr) convert_floatr8cCsfddlm}m}|jdkrdS|j|kr.tjS|j|kr`|rVt|j}||jkr`|Sn t|jS|jS)Nr) TYPE_ERROR TYPE_NUMERIC) Zopenpyxl.cell.cellrrrwZ data_typenpnanrfloat)r"rsrrrrvrrr# _convert_cell(s      zOpenpyxlReader._convert_cellzlist[list[Scalar]]csjjr|g}d}t|jD]H\}}fdd|D}|rX|ddkrX|q>|r`|}||q"|d|d}t|dkrtdd|Dt d d|Dkrdgfd d|D}|S) Ncsg|]}|qSr)r)rrs)rr"rr#rFsz1OpenpyxlReader.get_sheet_data..rrmrcss|]}t|VqdSr9lenrZdata_rowrrr# Ssz0OpenpyxlReader.get_sheet_data..css|]}t|VqdSr9rrrrr#rTscs g|]}|t|qSrrr) empty_cell max_widthrr#rVs) rrZreset_dimensions enumerateZrowspopappendrmaxmin)r"rrdataZlast_row_with_dataZ row_numberrnZ converted_rowr)rrrr"r#get_sheet_data>s(    zOpenpyxlReader.get_sheet_data)N) rrrr&propertyrrrrrrrrrrr0r#rs r) __future__rr4typingrrZnumpyrZpandas._typingrrrrZpandas.compat._optionalr Zpandas.io.excel._baser r Zpandas.io.excel._utilr r Z!openpyxl.descriptors.serialisablerrrrrrr#s   _