cMhdZddlmZddlmZmZmZmZddlZddl Z ddl Z ddl m Z m Z mZmZddlZddlZddlmZddlmZddlmZdd lmZdd lmZmZdd lmZm Z m!Z!m"Z"ddl#m$cm%Z&dd l'm(Z(dd l)m*Z*ddl+m,Z,m-Z-ddl.m/Z/ddl0m1Z1e rddl2m3Z3m4Z4m5Z5m6Z6ddlm7Z7GddZ8Gdde8Z9GddZ:GddZ;dS)zC Utilities for conversion to writer-agnostic Excel representation. ) annotations)HashableIterableMappingSequenceN) TYPE_CHECKINGAnyCallablecast) is_list_like)doc)find_stack_level)missing)is_float is_scalar) DataFrameIndex MultiIndex PeriodIndex) _shared_docs) CSS4_COLORS) CSSResolver CSSWarning)get_level_lengths) pprint_thing)FilePath IndexLabelStorageOptionsWriteExcelBuffer ExcelWriterc&eZdZdZeZ d d d ZdS) ExcelCellrowcolvalstyle mergestartmergeendNr%intr&r) int | Noner*returnNonecZ||_||_||_||_||_||_dSNr$)selfr%r&r'r(r)r*s W/var/www/html/test/jupyter/venv/lib/python3.11/site-packages/pandas/io/formats/excel.py__init__zExcelCell.__init__Cs2 $  )NNN) r%r+r&r+r)r,r*r,r-r.)__name__ __module__ __qualname__ __fields__ __slots__r3r4r2r#r#?sBIJI!%#!!!!!!!r4r#c eZdZdfd ZxZS) CssExcelCellr%r+r&r( dict | None css_styles3dict[tuple[int, int], list[tuple[str, Any]]] | Nonecss_rowcss_col css_converterCallable | Noner-r.c  |rB|r@d|||fD} t| } || }tjd||||d| dS)Nc>i|]\}}||Sr:lower).0propr's r2 z)CssExcelCell.__init__..cs3   &/dC c   r4r%r&r'r(r:) frozensetitemssuperr3) r1r%r&r'r(r>r@rArBkwargsdeclaration_dictunique_declarations __class__s r2r3zCssExcelCell.__init__Us  7- 7  3=gw>N3O    #,,<,B,B,D,D"E"E !M"566EJScs%JJ6JJJJJr4)r%r+r&r+r(r=r>r?r@r+rAr+rBrCr-r.)r5r6r7r3 __classcell__)rRs@r2r<r<TsGKKKKKKKKKKr4r<cteZdZUdZeZdddddddZdddddddddddddd Zdddd Zd d d d dZ ddDZ de d<dKdLdZ e ZdMdZdMdZdNdZdOd!ZdPd"ZdQd$ZdRd&ZdSd*ZdTd,ZdUd.ZdVd1ZdWd2ZdXd4ZdYd6ZdQd7ZdQd8ZdZd:Zd[d<Z dQd=Z!dZd>Z"d\d@Z#d]dCZ$d^dEZ%d_dHZ&d`dIZ'd_dJZ(dS)aCSSToExcelConverteraK A callable for converting CSS declarations to ExcelWriter styles Supports parts of CSS 2.2, with minimal CSS 3.0 support (e.g. text-shadow), focusing on font styling, backgrounds, borders and alignment. Operates by first computing CSS styles in a fairly generic way (see :meth:`compute_css`) then determining Excel style properties from CSS properties (see :meth:`build_xlstyle`). Parameters ---------- inherited : str, optional CSS declarations understood to be the containing scope for the CSS processed by :meth:`__call__`. topcenterbottom)rVztext-topmiddlebaselinerXz text-bottomTF) boldbolder600700800900normallighter100200300400500)raitalicoblique)serifz sans-serifcursivefantasyc8i|]}||Sr:rF)rHr(s r2rJzCSSToExcelConverter.s2   ur4) dashed mediumDashDot dashDotDothairdottedmediumDashDotDotdoubledashDot slantDashDot mediumDashedzdict[str, str] | None inheritedN str | Noner-r.c||||_nd|_tj|j|_dSr0) compute_cssr| functoolscache_call_uncached _call_cached)r1r|s r2r3zCSSToExcelConverter.__init__sC  !--i88DNN!DN&OD,?@@r4 declarations str | frozenset[tuple[str, str]]dict[str, dict[str, str]]c,||S)a Convert CSS declarations to ExcelWriter style. Parameters ---------- declarations : str | frozenset[tuple[str, str]] CSS string or set of CSS declaration tuples. e.g. "font-weight: bold; background: blue" or {("font-weight", "bold"), ("background", "blue")} Returns ------- xlstyle : dict A style as interpreted by ExcelWriter when found in ExcelCell.style. )r)r1rs r2__call__zCSSToExcelConverter.__call__s&  ...r4cb|||j}||Sr0)rr| build_xlstyle)r1r propertiess r2rz"CSSToExcelConverter._call_uncacheds/%%lDNCC !!*---r4propsMapping[str, str]c||||||||||d}dfd ||S)N) alignmentborderfillfont number_formatddict[str, str | None]r-r.ct|D]0\}}|||= t|tr||s||=1dS)z4Remove key where value is None, through nested dictsN)listrM isinstancedict)rkv remove_nones r2rz6CSSToExcelConverter.build_xlstyle..remove_noneskQWWYY ! !19!4((!KNNN!aD  ! !r4)rrr-r.)build_alignment build_border build_fill build_fontbuild_number_format)r1routrs @r2rz!CSSToExcelConverter.build_xlstyles--e44''..OOE**OOE**!55e<<    ! ! ! ! ! !  C r4dict[str, bool | str | None]c|d||||dS)Nz text-align) horizontalvertical wrap_text)get_get_vertical_alignment_get_is_wrap_textr1rs r2rz#CSSToExcelConverter.build_alignmentsD ))L1144U;;//66   r4ch|d}|r|j|SdS)Nzvertical-align)r VERTICAL_MAP)r1rvertical_aligns r2rz+CSSToExcelConverter._get_vertical_alignments8#344  9$((88 8tr4 bool | Nonec^|ddSt|ddvS)Nz white-space)nowrapprezpre-line)rboolrs r2rz%CSSToExcelConverter._get_is_wrap_text s2 99] # # +4E-(0MMNNNr4 dict[str, dict[str, str | None]]c$fddDS)NcJi|]}|d|dd|dd|dd|ddS)zborder-z-stylez-widthz-color)r(color) _border_stylercolor_to_excel)rHsiderr1s r2rJz4CSSToExcelConverter.build_border..s     ++II444455II444455'' 2HD2H2H2H(I(IJJ ,,UYY7M7M7M7M-N-NOO     r4rVrightrXleftr:rs``r2rz CSSToExcelConverter.build_borders6      ;    r4r(widthrcP|||dS||dS|dvrdS||}|dS|dvr|S|dkrdS|dkr|dvrdSdS|dkr|dvrdSd S||jvr |j|Stjd t |t t dS) Nnone)rhidden)Ngrooveridgeinsetoutsetsolidrxrv)ruthinrwrrr{zUnhandled border style format:  stacklevel)_get_width_nameBORDER_STYLE_MAPwarningswarnreprrr)r1r(rr width_names r2rz!CSSToExcelConverter._border_styles =U]u}4 =U]6 & & &6))%00  6 I I I  H  8 H  ---x%% H  ---x!> d+ + +(/ / M?$u++??+--     6r4 width_inputc`||}|dkrdS|dkrdS|dkrdSdS)Ngh㈵>g?rgffffff@mediumthick)_width_to_float)r1rrs r2rz#CSSToExcelConverter._get_width_nameVsD$$[11 4<<4 S[[6 S[[8wr4floatc4|d}||S)N2pt) _pt_to_float)r1rs r2rz#CSSToExcelConverter._width_to_float`s =E  '''r4 pt_stringstrct|dsJt|dS)Npt)endswithrrstrip)r1rs r2rz CSSToExcelConverter._pt_to_floates8!!$'''''Y%%d++,,,r4ch|d}|dvr||ddSdS)Nzbackground-color)N transparentrr)fgColor patternType)rr)r1r fill_colors r2rzCSSToExcelConverter.build_fillisFYY122 : : :#22:>>wWW W ; :r4rc|d}t|tr|ddn|}d|iS)Nz number-format§; format_code)rrrreplace)r1rfcs r2rz'CSSToExcelConverter.build_number_formatpsE YY ' '&0S&9&9 ARZZc " " "rr""r4$dict[str, bool | float | str | None]c ||}||}|r|dnd||||||||d|vrdndd|vpd||d||d S)Nr underlinesinglez line-throughr) namefamilysizer[rhrstrikershadow) _get_font_names_get_decoration_select_font_family_get_font_size _get_is_bold_get_is_italicrr _get_shadow)r1r font_names decorations r2rzCSSToExcelConverter.build_fontus))%00 ))%00 %/9JqMMT..z::''..%%e,,))%00&1Z&?&?((T%3<((7););<<&&u--   r4ch|d}|r|j|SdS)Nz font-weight)rBOLD_MAP)r1rweights r2rz CSSToExcelConverter._get_is_bolds6=))  -=$$V,, ,tr4ch|d}|r|j|SdS)Nz font-style)r ITALIC_MAP)r1r font_styles r2rz"CSSToExcelConverter._get_is_italics6YY|,,  3?&&z22 2tr4 Sequence[str]c\|d}||SdS)Nztext-decorationr:)rsplit)r1rrs r2rz#CSSToExcelConverter._get_decorations1YY011  !##%% %2r4rcd|vrdSdS)Nrrr:)r1rs r2_get_underlinez"CSSToExcelConverter._get_underlines * $ $8tr4c^d|vr(ttjd|dSdS)Nz text-shadowz ^[^#(]*[1-9])rresearchrs r2rzCSSToExcelConverter._get_shadows1 E ! ! .% 2FGGHH Htr4cptjd|dd}g}|D]}|dddkr|dddd}nA|dddkr|ddd d}n|}|r|||S) Nz(?x) ( "(?:[^"]|\\")+" | '(?:[^']|\\')+' | [^'",]+ )(?=,|\s*$) z font-familyrj"z\"'z\')rfindallrrstripappend)r1rfont_names_tmprrs r2rz#CSSToExcelConverter._get_font_namess  IImR ( (    " ( (DBQBx3AbDz))%55bqbSAbDz))%55zz|| (!!$'''r4 float | Nonec^|d}||S||S)Nz font-size)rr)r1rrs r2rz"CSSToExcelConverter._get_font_sizes1yy%% <K  &&&r4rr,cPd}|D] }|j|}|rn!|Sr0) FAMILY_MAPr)r1rrrs r2rz'CSSToExcelConverter._select_font_familysD  D_((..F   r4r'c|dS||r||S |j|S#t$r;t jdt |ttYnwxYwdS)NzUnhandled color format: r) _is_hex_color_convert_hex_to_excel NAMED_COLORSKeyErrorrrrrrr1r's r2rz"CSSToExcelConverter.color_to_excels ;4   c " " 3--c22 2 $S) )    M649966+--        ts =ABB color_stringrcFt|dS)N#)r startswith)r1rs r2rz!CSSToExcelConverter._is_hex_colorsL++C00111r4c|d}||r5|ddz|ddzz|ddzzS|S)Nrrrkrj)lstrip_is_shorthand_colorupperr1rcodes r2rz)CSSToExcelConverter._convert_hex_to_excelsm""3''  # #L 1 1 GaK$q'A+-Q! ;BBDD D::<< r4c|d}t|dkrdSt|dkrdStd|)zcCheck if color code is shorthand. #FFF is a shorthand as opposed to full #FFFFFF. rTFzUnexpected color )r"len ValueErrorr%s r2r#z'CSSToExcelConverter._is_shorthand_colorsV ""3'' t99>>4 YY!^^5???@@ @r4r0)r|r}r-r.)rrr-r)rrr-r)rrr-r)rrr-r})rrr-r)rrr-r)r(r}rr}rr})rr}r-r})rr}r-r)rrr-r)rr)rrr-r)rrr-r)rrr-r)rrr-r})rrr-r)rrr-r,)r'r}r-r})rrr-r)rrr-r))r5r6r7__doc__rrrrrrr__annotations__r3rrrrrrrrrrrrrrrrrrrrrrrrrrrr#r:r4r2rUrUms"L LH"J J  (%$$$AAAAA+--K////*.... 0     OOOO     5555n(((( ----XXXX####     $    4'''' "2222     A A A A A Ar4rUceZdZdZdZdZ d;dd Z d>d!Z d>d"Z d>d#Z d>d$Zd>d%Zed?d&Zd@d)Zd>d*Zeed+, dAdBd:ZdS)CExcelFormattera9 Class for formatting a DataFrame to a list of ExcelCells, Parameters ---------- df : DataFrame or Styler na_rep: na representation float_format : str, default None Format string for floating point numbers cols : sequence, optional Columns to write header : bool or sequence of str, default True Write out column names. If a list of string is given it is assumed to be aliases for the column names index : bool, default True output row names (index) index_label : str or sequence, default None Column label for index column(s) if desired. If None is given, and `header` and `index` are True, then the index names are used. A sequence should be given if the DataFrame uses MultiIndex. merge_cells : bool, default False Format MultiIndex and Hierarchical Rows as merged cells. inf_rep : str, default `'inf'` representation for np.inf values (which aren't representable in Excel) A `'-'` sign will be added in front of -inf. style_converter : callable, optional This translates Styler styles (CSS) into ExcelWriter styles. Defaults to ``CSSToExcelConverter()``. It should have signature css_declarations string -> excel style. This is only called for body cells. ii@r NTFinfna_repr float_formatr}colsSequence[Hashable] | NoneheaderSequence[Hashable] | boolindexr index_labelIndexLabel | None merge_cellsinf_repstyle_converterrCr-r.c d|_||_t|ts?||_|j|j}| t} | |_nd|_d|_||_ |tt| |j stdtt| |j tt|krtd|||_ |j j |_ ||_||_||_||_||_| |_dS)Nrz,passes columns are not ALL present dataframez.Not all names specified in 'columns' are found)columns) rowcounterr1rrstyler_computedatarUr<dfr*r intersectionr>rsetreindexr2r7r8r5r:r;) r1rCr1r2r3r5r7r8r:r;r<s r2r3zExcelFormatter.__init__sI "i(( (DK K " " "B&"5"7"74CD DK#'D   uT{{// ;;<< OMNNN5;;++BJ7788CD NNJJOPPPjjj..DGw ( & & r4 dict[str, dict[str, str | bool]]c"ddidddddddddS) Nr[TrrrWrV)rr)rbordersrr:r1s r2 header_stylezExcelFormatter.header_styleGs=TN   )1eDD   r4clt|rtj|r|j}nht |rYtj|r|j}n=tj|r d|j}n|jt|j|z}t|ddtd|S)N-tzinfoz{Excel does not support datetimes with timezones. Please ensure that datetimes are timezone unaware before writing to Excel.) rrisnar1risposinf_scalarr;isneginf_scalarr2rgetattrr+rs r2 _format_valuezExcelFormatter._format_valueTs S>> 5gl3// 5+CC c]] 5&s++ 5l(-- 5($,((".D-344 3$ ' ' 3@   r4Iterable[ExcelCell]c#pK|jjdkr|jstd|js |jsdS|j}||jd}t|}d}d}|jrAt|j jtr"t|j jddz }|jrt|jD]\}}t||||jV tt#||j|jD]\}\}}} || } |D]_\} } d\} }| dkr ||| z| z}} t-||| zdz| | |jt/|jdd|| |j| | V`ntt#|D]j\} } d t7t8| }t-||| zdz||jt/|jdd|| |j Vk||_dS) Nrjz]Writing to Excel with MultiIndex columns and no index ('index'=False) is not yet implemented.Fsparsify include_namesrrKNN ctx_columns r%r&r'r(r>r@rArBr)r*.r%r&r'r(r>r@rArB)r>nlevelsr7NotImplementedError _has_aliasesr5 _format_multir:rrrCrr* enumeratenamesr#rKziplevelscodestakerMr<rRr@r<joinmaprr?)r1r> level_strs level_lengths coloffsetlnumrspansre level_codesvaluesispan_valr)r*rs r2_format_header_miz ExcelFormatter._format_header_mifs < ! # #: )D ! T[  F,**%U+  **55   : 2*TW]J?? 2DGM!,--1I  + ' 66   d!+ 7@M7>7=AA77  22ufk [11#(;;==KAx+5(J!||/3Y]X5MH & %MA-"1I"/#*4; t#L#L $ !&*&:#-!)          ,'sJ'788   6HHSv6677"!A )+&t{M4HH "&"6       r4c #K|js|jr4d}|jr?d}t|jjt rt |jjj}|j}|jrtt|j|_t |jt |jkr:tdt |jdt |jd|j}t|D]F\}}t|j||z||jt!|jddd||jVEdSdS)NrrjzWriting z cols but got z aliasesrZr])r`r5r7rrCrr*rcr>r rr+rbr<r?rKrRr@r<)r1rlcolnamescolindexcolnames r2_format_header_regularz%ExcelFormatter._format_header_regularsw     Iz 9 dgmZ889 #DGM$7 8 8I|H  '"8T[99 t{##s4<'8'888$>3t|#4#4>>#&t{#3#3>>> ;%.x%8%8  !'" 9,+&t{M4HH$"&"6       '  $  r4ctjtr}n}d}jjjrdjjjDdgtjzz}tj dd|Dr+fdt|D}xj dz c_ tj||S)Nr:cg|]}||nd S)Nr r:rHxs r2 z1ExcelFormatter._format_header..s KKK! 112KKKr4r c |o|Sr0r:)r|ys r2z/ExcelFormatter._format_header..s QW1r4c3"K|] }|dkV dS)r Nr:r{s r2 z0ExcelFormatter._format_header..s&6L6L1qBw6L6L6L6L6L6Lr4c3VK|]#\}}tj||jV$dSr0)r#r?rK)rHrvr'r1s r2rz0ExcelFormatter._format_header..sN%#doxd>OPPr4rj)rr>rrsrxrCr7rcr*rreducerbr? itertoolschain)r1gengen2r%s` r2_format_headerzExcelFormatter._format_headers dlJ / / 0((**CC--//C$& 7=  %KKtw}7JKKKODL!!O""C 4 46L6L6L6L6LMM %)231$sD)))r4ct|jjtr|S|Sr0)rrCr7r_format_hierarchical_rows_format_regular_rowsrJs r2 _format_bodyzExcelFormatter._format_bodys< dgmZ 0 0 /1133 3,,.. .r4c #K|js|jr|xjdz c_|jr}|jr@t |jt ttj tfr|jd}n@|jr"t |jtr|j}n|j jj d}t |jtr|xjdz c_|r*|jdur!t!|jdz d||jV|j j}t |j jt$r|j j}t)|D]D\}}t+|j|zd||jt-|jdd|d|jVEd}nd}||Ed{VdS)NrjrF ctx_indexr])r`r5r?r7r8rrtuplenpndarrayrrrCrcr>rr#rKr to_timestamprbr<rRr@r<_generate_body)r1r8 index_valuesidxidxvalrls r2rz#ExcelFormatter._format_regular_rowss   !  ! OOq OO :%  5J 4 E"B%% 5#.q1 ! 5j1A3&G&G 5". "gm1!4 $, 33 %1$ Xt{%77! 3Q TEVWWWWW 7=L$'-55 <#w}99;; (66   V"#-+&t{KFF"&"6       III&&y11111111111r4c#4K|js|jr|xjdz c_d}|jrR|jjj}|jr9t|jtttj tfr|j}t|j tr|jr|xjdz c_t!j|r?|jdur6t%|D]&\}}t'|jdz |||jV'|jr |jjdd}t-|}t/||jjj|jjjD]\}}} || |j|j} |D]i\} } d\} }| dkr|j| z| zdz } |}t=|j| z|| | |jt?|j dd| ||j!| | Vj|dz }not/|jjD][}t%|D]D\}}t=|j|z|||jt?|j dd|||j! VE|dz }\|"|Ed{VdS) NrjrFTrV) allow_fill fill_valuerYrr[r])#r`r5r?r7rCrcr8rrrrrrr>rr:com any_not_nonerbr#rKrarrdrerfrg _can_hold_na _na_valuerMr<rRr@r<r)r1gcolidx index_labelscidxrrjrkrnrerorprqrrr)r* indexcolvalsr indexcolvals r2rz(ExcelFormatter._format_hierarchical_rowss5   !  ! OOq OO :E !7=.L 0J 4 E"B%% 0 $/ $, 33 %8H %1$. X4;e3K3K"+L"9"9XXJD$#DOa$7tTEVWWWWWW1 !!W]88!9 !2* = = 25!47=#79L33!!.E6;$[[##)#6#)#3)F (-{{}} 8/9, H#a<<)-1)'1%-       qLGG5!<%($7 ! !L,5l,C,C  ([* $# 5 ' +"&"3'.t{K'N'N$'$+*.*>       qLGG&&w///////////r4c*t|jS)z1Whether the aliases for column names are present.)r r5rJs r2r`zExcelFormatter._has_aliasescsDK(((r4rlr+c #"Ktt|jD]j}|jjdd|f}t |D]B\}}t |j|z||z|dt|j dd|||j VCkdS)Nctxr]) ranger*r>rCilocrbr<r?rRr@r<)r1rlcolidxseriesrqr's r2rzExcelFormatter._generate_bodyhsC --..  FW\!!!V),F#F++  3"!+*&t{E4@@""&"6          r4c#Ktj||D]%}||j|_|V&dSr0)rrrrrSr')r1cells r2get_formatted_cellsz"ExcelFormatter.get_formatted_cellsxseOD$7$7$9$94;L;L;N;NOO  D))$(33DHJJJJ  r4storage_options)rSheet1rwriter)FilePath | WriteExcelBuffer | ExcelWriter sheet_namestartrowstartcol freeze_panestuple[int, int] | NoneengineStorageOptions | None engine_kwargsr=c ddlm} |jj\} } | |jks | |jkr%t d| d| d|jd|j|i}|} t|| rd} n| ||||}d } | | |||| | r| dSdS#| r| wwxYw) ab writer : path-like, file-like, or ExcelWriter object File path or existing ExcelWriter sheet_name : str, default 'Sheet1' Name of sheet which will contain DataFrame startrow : upper left cell row to dump data frame startcol : upper left cell column to dump data frame freeze_panes : tuple of integer (length 2), default None Specifies the one-based bottommost row and rightmost column that is to be frozen engine : string, default None write engine to use if writer is a path - you can also set this via the options ``io.excel.xlsx.writer``, or ``io.excel.xlsm.writer``. {storage_options} engine_kwargs: dict, optional Arbitrary keyword arguments passed to excel engine. rr z-This sheet is too large! Your sheet size is: z, z Max sheet size is: NF)rrrT)rrr) pandas.io.excelr!rCshapemax_rowsmax_colsr+rr _write_cellsclose)r1rrrrrrrrr!num_rowsnum_colsformatted_cells need_saves r2writezExcelFormatter.write}siD 0/////!W]( dm # #x$-'?'?GGGHGG&*mGG7;}GG   M2244 fk * * II [ /+ F I    !!)        y   s CC) r NNTTNFr0N)r1rr2r}r3r4r5r6r7rr8r9r:rr;rr<rCr-r.)r-rG)r-rT)r-r)rlr+r-rT)rrrNNNN)rrrrrr+rr+rrrr}rrrr=r-r.)r5r6r7r,rrr3propertyrKrSrsrxrrrrr`rrr rrr:r4r2r/r/s@HH #'*.,0)-!+/+++++Z    X  $CCCCJ>****,//// ,2,2,2,2\M0M0M0M0^)))X)   S&78999#/3!15%)DDDD:9DDDr4r/)rs7#"""""   ))))))''''''444444&&&&&&  ! 000000555555766666333333##"""""!!!!!!!!*KKKKK9KKK2FAFAFAFAFAFAFAFAR LLLLLLLLLLr4