cMhMddlmZddlZddlmZmZmZddlZddl m Z ddl m Z ddl mZddlmZmZddlmZmZerdd lmZdd lmZdd lmZmZmZmZmZmZGd d eZ GddedZ!dS)) annotationsN) TYPE_CHECKINGAnycast)import_optional_dependency)doc) _shared_docs)BaseExcelReader ExcelWriter)combine_kwargsvalidate_freeze_panesWorkbook) Serialisable)ExcelWriterIfSheetExistsFilePath ReadBufferScalarStorageOptionsWriteExcelBuffercbeZdZdZdZ d1d2fd Zed3dZed4dZd5dZ e d6dZ e dZ e d Z e d!Ze d7d#Ze d$Ze d%Ze d&Ze d'Ze d(Z d8d9d0ZxZS):OpenpyxlWriteropenpyxl)z.xlsxz.xlsmNwpath)FilePath | WriteExcelBuffer | ExcelWriterengine str | None date_formatdatetime_formatmodestrstorage_optionsStorageOptions | Noneif_sheet_existsExcelWriterIfSheetExists | None engine_kwargsdict[str, Any] | NonereturnNonec  Vddlm} t|| }t|||||d|jvrnddlm}  | |jj fi||_ n-#t$r |jj wxYw|jj ddS | di||_ n-#t$r |jj wxYw|jjr,|j|jjddSdS)Nrr)r!r#r%r'r+ load_workbook)openpyxl.workbookrr super__init___moderr._handleshandle_book TypeErrorcloseseekbook worksheetsremove) selfrrrr r!r#r%r'kwargsrr. __class__s Y/var/www/html/test/jupyter/venv/lib/python3.11/site-packages/pandas/io/excel/_openpyxl.pyr2zOpenpyxlWriter.__init__,s /.....&}f==   ++'     4:   . . . . . . *]4=+?QQ=QQ     $**,,,  M % %a ( ( ( ( ( %X66 66     $**,,, y# :   !5a!899999 : :sA''*B6 C*C.rc|jS)z Book instance of class openpyxl.workbook.Workbook. This attribute can be used to access engine-specific features. )r6r=s r@r:zOpenpyxlWriter.book[s zdict[str, Any]c8fdjjD}|S)z(Mapping of sheet names to sheet objects.c,i|]}|j|Sr/)r:).0namer=s r@ z)OpenpyxlWriter.sheets..gs!IIID$ $IIIrC)r: sheetnames)r=results` r@sheetszOpenpyxlWriter.sheetsds)JIIIDI4HIII rCc|j|jjd|jvrDt |jjt js"|jjdSdSdS)z( Save workbook to disk. r,N)r:saver4r5r3 isinstancemmaptruncaterBs r@_savezOpenpyxlWriter._savejsk t}+,,, 4:  j1Ety&Q&Q  M ) ) + + + + +    rC style_dictdictdict[str, Serialisable]cddi}i}|D]B\}}|||}t|d|d}||}|r|||<C|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. bordersborder _convert_to_cdSNr/)xs r@z9OpenpyxlWriter._convert_to_style_kwargs..sDrC)itemsgetgetattr)clsrS_style_key_map style_kwargskv _conv_to_xnew_vs r@_convert_to_style_kwargsz'OpenpyxlWriter._convert_to_style_kwargsss0$X.02 $$&& ( (DAq""1a((A &8Q&8&8..IIJJqMME ("' QrCc^ddlm}t|tr ||S|di|S)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)ColorNr/)openpyxl.stylesrjrOr")ra color_specrjs r@_convert_to_colorz OpenpyxlWriter._convert_to_colorsN, *))))) j# & & '5$$ $5&&:&& &rCcddlm}ddddddd }i}|D];\}}|||}|d kr||}|||<<|d i|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)Fontsizebolditalic underline strikethrough vertAlign)szbiustrike vertaligncolorr/)rkror^r_rm)ra font_dictro _font_key_map font_kwargsrdres r@_convert_to_fontzOpenpyxlWriter._convert_to_fonts8 )(((((%$    OO%%  DAq!!!Q''AG||))!,,KNNt""k"""rCc,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 )maprm)rastop_seqs r@_convert_to_stopzOpenpyxlWriter._convert_to_stops 3((333rC fill_dictcddlm}m}ddddddd}ddi}i}i}|D]}\}} ||} ||} | dvr|| } | d kr|| } | r| || <k| r| || <s| ||<| ||<~ |d i|S#t$r |d i|cYSwxYw) 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 PatternFill fill_type start_color end_color) patternType patterntypefgColorfgcolorbgColorbgcolortype)rrstopNr/)rkrrr^r_rmrr7) rarrr_pattern_fill_key_map_gradient_fill_key_map pfill_kwargs gfill_kwargsrdrepkgks r@_convert_to_fillzOpenpyxlWriter._convert_to_fillsp2        '&$$"" ! ! #.v!6  OO%% $ $DAq&**1--B'++A..B111))!,,V||((++ $#$ R   $#$ R  "# Q"# Q 0;.... . 0 0 0<//,// / / / 0s-B55C  C c ddlm}ddi}t|tr ||Si}|D];\}}|||}|dkr||}|||<<|di|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)Side border_stylestyle)rr|r/)rkrrOr"r^r_rm)ra side_specr _side_key_map side_kwargsrdres r@_convert_to_sidezOpenpyxlWriter._convert_to_side5s" )((((('1 i % % )4i((( ( OO%%  DAq!!!Q''AG||))!,,KNNt""k"""rCcddlm}ddd}i}|D]T\}}|||}|dkr||}|dvr||}|||<U|di|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)Border diagonalUp diagonalDown) diagonalup diagonaldownr|)leftrighttopbottomdiagonalr/)rkrr^r_rmr)ra border_dictr_border_key_map border_kwargsrdres r@_convert_to_borderz!OpenpyxlWriter._convert_to_borderVs2 +*****)5~VV %%'' ! !DAq##Aq))AG||))!,,BBB((++ M!  v&& &&&rCcddlm}|di|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) Alignmentr/)rkr)raalignment_dictrs r@_convert_to_alignmentz$OpenpyxlWriter._convert_to_alignment~s,& .-----y**>***rCc|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 format_coder/)ranumber_format_dicts r@_convert_to_number_formatz(OpenpyxlWriter._convert_to_number_formats "-00rCcddlm}|di|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) Protectionr/)rkr)raprotection_dictrs r@_convert_to_protectionz%OpenpyxlWriter._convert_to_protections, /.....z,,O,,,rCr sheet_namestartrowintstartcol freeze_panestuple[int, int] | Nonec ||}i}||jvr|jdkrd|jvr|jdkrK|j|}|j|}|j|=|j||} n}|jdkrtd|d|jdkr|j|} nFtd|jd |j|} n |j} || _t|rUttttf|}| |d d z|d d z | _|D]} | || jzd z|| jzd z } || j\| _} | r| | _i} | jrJt-| j}||} | || j} | ||<| r+| D]\}}t5| ||| j| j| || jzd z|| jzd z|| jzd z|| jzd z | r|| jzd z}|| jzd z}|| jzd z}|| jzd z}t=||d zD]g}t=||d zD]Q}||kr||kr| ||} | D]\}}t5| ||RhdS)Nnewr,replaceerrorzSheet '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) start_row start_column end_columnend_row)rr)_get_sheet_namerL_if_sheet_existsr3r:index create_sheet ValueErrortitler rtuplercellrrcol_value_with_fmtvalvalue number_formatrr"r_rhr^setattr mergestartmergeend merge_cellsrange)r=cellsrrrr _style_cacheold_wks target_indexwksrxcellfmtrckeyrdre first_rowlast_row first_collast_colrrs r@ _write_cellszOpenpyxlWriter._write_cellss))*55 ;=  $ $)>%)G)Gtz!!(I55"k*5G#'9??7#;#;L *-)00\JJCC*g55$>*>>>*i77+j1CC$UD1UUU k*-)((**C"CI  . . c3h>>L"xx Oa' Q!0C (  C , 5, 5DHHtx'!+Htx4G!4KE $33DH== EK *&)#;=Lz 5$*oo+//44 '#'#@#@#L#LL(4L% )(..00))DAqE1a((((*t}/H&1A5!)DH!4q!8'$-7!;$t6:   5 (48 3a 7I'$/9A=H (48 3a 7I'$-7!;H$Y1 ==55#(HqL#A#A55C"i//C94D4D ($'HHCSH$A$AE(4(:(:(<(<551 'q! 4 4 4 45 5M, 5, 5rC)NNNrNNN)rrrrrrr rr!r"r#r$r%r&r'r(r)r*)r)r)r)rD)r)r*)rSrTr)rU)rrD)NrrN) rrrrrrrrr)r*)__name__ __module__ __qualname___engine_supported_extensionsr2propertyr:rLrR classmethodrhrmrrrrrrrrr __classcell__r?s@r@rr(sG. ""&&*15;?/3-:-:-:-:-:-:-:^XX ,,,,!!![!F''['8-#-#[-#^44[4"<0<0<0[<0|##[#@%'%'[%'N++[+,11[1"--[-*"&/3 X5X5X5X5X5X5X5X5X5rCrceZdZeed ddfd Zed d Zd!dZed"dZ d#dZ d$dZ d%dZ d&d'dZ xZS)(OpenpyxlReaderr#)r#Nfilepath_or_bufferFilePath | ReadBuffer[bytes]r$r' dict | Noner)r*cntdt|||dS)a2 Reader using openpyxl engine. Parameters ---------- filepath_or_buffer : str, path object or Workbook Object to be parsed. {storage_options} engine_kwargs : dict, optional Arbitrary keyword arguments passed to excel engine. r)r#r'N)rr1r2)r=rr#r'r?s r@r2zOpenpyxlReader.__init__sG$ #:...  +'      rCtype[Workbook]cddlm}|S)Nrr)rr)r=rs r@_workbook_classzOpenpyxlReader._workbook_class/s%%%%%%rCrc2ddlm}dddd}||fi||zS)Nrr-TF) read_only data_only keep_links)rr.)r=rr'r.default_kwargss r@r.zOpenpyxlReader.load_workbook5sN +*****'+$eTT}    -   rC list[str]c.d|jjDS)Ncg|] }|j Sr/)r)rGsheets r@ z.OpenpyxlReader.sheet_names..Cs>>> >>>rC)r:r;rBs r@ sheet_nameszOpenpyxlReader.sheet_namesAs>>)=>>>>rCrHr"cF|||j|Sr[)raise_if_bad_sheet_by_namer:)r=rHs r@get_sheet_by_namez OpenpyxlReader.get_sheet_by_nameEs" ''---yrCrrcP|||jj|Sr[)raise_if_bad_sheet_by_indexr:r;)r=rs r@get_sheet_by_indexz!OpenpyxlReader.get_sheet_by_indexIs& ((///y#E**rCrcddlm}m}|jdS|j|kr t jS|j|kr5t|j}||jkr|St|jS|jS)Nr) TYPE_ERROR TYPE_NUMERIC) openpyxl.cell.cellrrr data_typenpnanrfloat)r=rrrrs r@ _convert_cellzOpenpyxlReader._convert_cellMs        : 2 ^z ) )6M ^| + +dj//Cdj   $$ $zrCfile_rows_needed int | Nonelist[list[Scalar]]c4 jjr|g}d}t|jD]s\}}fd|D}|r.|ddkr"||r |ddk"|r|}|||t||krnt|d|dz}t|dkrHtd|D td|D krdg fd|D}|S) Nc:g|]}|Sr/)r)rGrr=s r@r z1OpenpyxlReader.get_sheet_data..hs'FFF$T//55FFFrCrrrc34K|]}t|VdSr[lenrGdata_rows r@ z0OpenpyxlReader.get_sheet_data..ws(??hCMM??????rCc34K|]}t|VdSr[r$r&s r@r(z0OpenpyxlReader.get_sheet_data..xs(66X3x==666666rCc@g|]}|t|z zzSr/r$)rGr' empty_cell max_widths r@r z1OpenpyxlReader.get_sheet_data..zs>  CMM 9ZGGrC) r:rreset_dimensions enumeraterowspopappendr%maxmin) r=r rdatalast_row_with_data row_numberr converted_rowr+r,s ` @@r@get_sheet_datazOpenpyxlReader.get_sheet_data_s 9  %  " " $ $ $#%(44  OJFFFF#FFFM $M"$5$;$;!!###  $M"$5$;$; 0%/" KK & & &+D =M0M0M,(1,,- t99q==??$?????I6666666BB,.4 $(  rC)NN)rrr#r$r'rr)r*)r)r)rrr)r)r)r)rHr")rr)r)rr[)rrr)r)rrrrr r2rrr.r rrrr8rrs@r@rrsS&7899926%)       :9 0X     ???X?++++&59         rCrr)" __future__rrPtypingrrrnumpyrpandas.compat._optionalrpandas.util._decoratorsrpandas.core.shared_docsr pandas.io.excel._baser r pandas.io.excel._utilr r rr!openpyxl.descriptors.serialisablerpandas._typingrrrrrrrrr/rCr@rCs""""""  >>>>>>''''''000000  !!!!!!>>>>>>j5j5j5j5j5[j5j5j5Zjjjjj_Z0jjjjjrC