U Qi@s\dZddlmZddlmZmZddlZddlZddlm Z m Z m Z m Z m Z mZmZddlZddlZddlmZddlmZmZddlmZdd lmZdd lmZdd lmZm Z dd l!m"Z"m#Z#m$Z$m%Z%ddl&m'm(Z)dd l*m+Z+ddl,m-Z-ddl.m/Z/m0Z0ddl1m2Z2ddl3m4Z4GdddZ5Gddde5Z6GdddZ7GdddZ8dS)zC Utilities for conversion to writer-agnostic Excel representation. ) annotations) lru_cachereduceN)AnyCallableHashableIterableMappingSequencecast) is_list_like) IndexLabelStorageOptions)doc)find_stack_level)missing)is_float is_scalar) DataFrameIndex MultiIndex PeriodIndex) _shared_docs) CSS4_COLORS) CSSResolver CSSWarning)get_level_lengths) pprint_thingc@s,eZdZdZeZd ddddddddZdS) ExcelCellrowcolvalstyle mergestartmergeendNint int | NoneNone)r r!r$r%returncCs(||_||_||_||_||_||_dSNr)selfr r!r"r#r$r%r,;/tmp/pip-unpacked-wheel-58ppya8e/pandas/io/formats/excel.py__init__=s zExcelCell.__init__)NNN)__name__ __module__ __qualname__Z __fields__ __slots__r.r,r,r,r-r9s rc s0eZdZdddddddddfdd ZZS) CssExcelCellr&z dict | Nonez3dict[tuple[int, int], list[tuple[str, Any]]] | NoneCallable | Noner()r r!r# css_stylescss_rowcss_col css_converterr)c  sR|r2|r2dd|||fD} t| } || }tjf||||d| dS)NcSsi|]\}}||qSr,lower).0propr"r,r,r- ]sz)CssExcelCell.__init__..r r!r"r#) frozensetitemssuperr.) r+r r!r"r#r5r6r7r8kwargsZdeclaration_dictZunique_declarations __class__r,r-r.Os   zCssExcelCell.__init__)r/r0r1r. __classcell__r,r,rCr-r3Nsr3c@s.eZdZUdZeZdddddddZdddddddddddddd Zdddd Zd d d d dZ dddDZ de d<dgdddddZ e ZdddddZddddd Zd!dd"d#d$Zd!d%d"d&d'Zd!dd"d(d)Zd!d*d"d+d,Zd!d-d"d.d/Zdddd0d1d2Zddd3d4d5Zdd6d7d8d9Zd:d6d;dd?d@Zd!dAd"dBdCZd!dDd"dEdFZd!d*d"dGdHZd!d*d"dIdJZd!dKd"dLdMZdKddNdOdPZ d!d*d"dQdRZ!d!dKd"dSdTZ"d!dUd"dVdWZ#dXdYdZd[Z$ddd\d]d^Z%d:d_d`dadbZ&d:d:d`dcddZ'd:d_d`dedfZ(dS)hCSSToExcelConverteraK 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)rGztext-topZmiddleZbaselinerIz text-bottomTF) boldZbolderZ600Z700Z800Z900normalZlighterZ100Z200Z300Z400500)rKitalicZoblique)Zserifz sans-serifZcursiveZfantasycCsi|]}||qSr,r9)r;r#r,r,r-r=szCSSToExcelConverter.) dashedZ mediumDashDotZ dashDotDothairdottedmediumDashDotDotdoubleZdashDotZ slantDashDot mediumDashedzdict[str, str] | None inheritedN str | Noner()rXr)cCs2|dk r|||_nd|_tdd|j|_dS)N)maxsize) compute_cssrXr_call_uncached _call_cached)r+rXr,r,r-r.szCSSToExcelConverter.__init__z str | frozenset[tuple[str, str]]zdict[str, dict[str, str]]) declarationsr)cCs ||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])r+r^r,r,r-__call__szCSSToExcelConverter.__call__cCs|||j}||Sr*)r[rX build_xlstyle)r+r^Z propertiesr,r,r-r\sz"CSSToExcelConverter._call_uncachedzMapping[str, str])propsr)csN||||||||||d}dddfdd ||S)N) alignmentZborderfillfontZ number_formatdict[str, str | None]r()dr)csFt|D]4\}}|dkr$||=q t|tr ||s ||=q dS)z4Remove key where value is None, through nested dictsN)listr@ isinstancedict)rfkv remove_noner,r-rms z6CSSToExcelConverter.build_xlstyle..remove_none)build_alignment build_border build_fill build_fontbuild_number_format)r+raoutr,rlr-r`s  z!CSSToExcelConverter.build_xlstylezdict[str, bool | str | None]cCs|d||||dS)Nz text-align) horizontalvertical wrap_text)get_get_vertical_alignment_get_is_wrap_textr+rar,r,r-rnsz#CSSToExcelConverter.build_alignmentcCs|d}|r|j|SdS)Nzvertical-align)rw VERTICAL_MAP)r+raZvertical_alignr,r,r-rxs  z+CSSToExcelConverter._get_vertical_alignmentz bool | NonecCs"|ddkrdSt|ddkS)Nz white-space)Znowrapprezpre-line)rwboolrzr,r,r-rysz%CSSToExcelConverter._get_is_wrap_textz dict[str, dict[str, str | None]]csfdddDS)Ncsfi|]^}|d|dd|dd|dd|ddqS)zborder-z-stylez-widthz-color)r#color) _border_stylerwcolor_to_excel)r;Zsiderar+r,r-r= s z4CSSToExcelConverter.build_border..rGrightrIleftr,rzr,rr-ro s z CSSToExcelConverter.build_border)r#widthr~cCs|dkr|dkr|dkrdS|dkr0|dkr0dS|dkrg?rgffffff@ZmediumZthick)_width_to_float)r+rrr,r,r-rPs z#CSSToExcelConverter._get_width_namefloat)rr)cCs|dkr d}||S)NZ2pt) _pt_to_float)r+rr,r,r-rZsz#CSSToExcelConverter._width_to_floatstr) pt_stringr)cCs|dstt|dS)Npt)endswithAssertionErrorrrstrip)r+rr,r,r-r_sz CSSToExcelConverter._pt_to_float)racCs&|d}|dkr"||ddSdS)Nzbackground-color)NZ transparentrr)ZfgColorZ patternType)rwr)r+raZ fill_colorr,r,r-rpcs zCSSToExcelConverter.build_fillrecCs,|d}t|tr |ddn|}d|iS)Nz number-format§;Z format_code)rwrhrreplace)r+raZfcr,r,r-rrjs z'CSSToExcelConverter.build_number_formatz$dict[str, bool | float | str | None]c Csv||}||}|r |dnd||||||||d|krNdndd|kpZd||d||d S)Nr underlinesinglez line-throughr~) namefamilysizerJrMrstriker~Zshadow) _get_font_names_get_decoration_select_font_family_get_font_size _get_is_bold_get_is_italicrrw _get_shadow)r+ra font_names decorationr,r,r-rqos   zCSSToExcelConverter.build_fontcCs|d}|r|j|SdS)Nz font-weight)rwBOLD_MAP)r+raZweightr,r,r-rs  z CSSToExcelConverter._get_is_boldcCs|d}|r|j|SdS)Nz font-style)rw ITALIC_MAP)r+raZ font_styler,r,r-rs  z"CSSToExcelConverter._get_is_italicz Sequence[str]cCs"|d}|dk r|SdSdS)Nztext-decorationr,)rwsplit)r+rarr,r,r-rs z#CSSToExcelConverter._get_decoration)rr)cCsd|kr dSdS)Nrrr,)r+rr,r,r-_get_underlinesz"CSSToExcelConverter._get_underlinecCs d|krttd|dSdS)Nz text-shadowz ^[^#(]*[1-9])r}researchrzr,r,r-rszCSSToExcelConverter._get_shadowcCstd|dd}g}|D]f}|dddkrF|dddd}n.|dddkrl|ddd d}n|}|r||q|S) Nz(?x) ( "(?:[^"]|\\")+" | '(?:[^']|\\')+' | [^'",]+ )(?=,|\s*$) z font-familyrN"z\"'z\')rfindallrwrstripappend)r+raZfont_names_tmprrr,r,r-rs   z#CSSToExcelConverter._get_font_namesz float | NonecCs |d}|dkr|S||S)Nz font-size)rwr)r+rarr,r,r-rs z"CSSToExcelConverter._get_font_sizer'r)cCs&d}|D]}|j|}|rq"q|Sr*) FAMILY_MAPrw)r+rrrr,r,r-rs  z'CSSToExcelConverter._select_font_family)r"r)cCsb|dkr dS||r ||Sz |j|WStk r\tjdt|ttdYnXdS)NzUnhandled color format: r) _is_hex_color_convert_hex_to_excel NAMED_COLORSKeyErrorrrrrrr+r"r,r,r-rs     z"CSSToExcelConverter.color_to_excelr}) color_stringr)cCst|dS)N#)r} startswith)r+rr,r,r-rsz!CSSToExcelConverter._is_hex_colorcCsH|d}||r<|dd|dd|ddS|SdS)NrrrOrN)lstrip_is_shorthand_colorupperr+rcoder,r,r-rs  (z)CSSToExcelConverter._convert_hex_to_excelcCs<|d}t|dkrdSt|dkr*dStd|dS)zcCheck if color code is shorthand. #FFF is a shorthand as opposed to full #FFFFFF. rTFzUnexpected color N)rlen ValueErrorrr,r,r-rs    z'CSSToExcelConverter._is_shorthand_color)N))r/r0r1__doc__rrr{rrrr__annotations__r.rr[r_r\r`rnrxryrorrrrrprrrqrrrrrrrrrrrrr,r,r,r-rFgs   7  rFc @seZdZdZdZdZd8d d d d d dd d ddd ddZeddddZddZ ddddZ ddddZ dddd Z ddd!d"Z ddd#d$Zddd%d&Zed dd'd(Zd)dd*d+d,Zddd-d.Zeed/d0d9d d)d)d3d d4dd5d6d7ZdS):ExcelFormattera9 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@rNTFinfrrYzSequence[Hashable] | NonezSequence[Hashable] | boolr}zIndexLabel | Noner4r() na_rep float_formatcolsheaderindex index_label merge_cellsinf_repstyle_converterr)c Csd|_||_t|tsB||_|j|j}| dkr:t} | |_n d|_d|_||_ |dk rt t | |j sxtdt t | |j t t|krtd|j|d|_ |j j |_ ||_||_||_||_||_| |_dS)Nrz,passes columns are not ALL present dataframez.Not all names specified in 'columns' are found)columns) rowcounterrrhrstylerZ_computedatarFrdfrr intersectionrrsetZreindexrrrrrr) r+rrrrrrrrrrr,r,r-r.s2     zExcelFormatter.__init__z dict[str, dict[str, str | bool]]rcCs ddidddddddddS) NrJTrrrHrG)rtru)rdZbordersrbr,r+r,r,r- header_styleAszExcelFormatter.header_stylecCst|rt|r|j}nJt|rdt|r4|j}n0t|rLd|j}n|jdk rdt |j|}t |dddk r|t d|S)N-tzinfoz{Excel does not support datetimes with timezones. Please ensure that datetimes are timezone unaware before writing to Excel.) rrZisnarrZisposinf_scalarrZisneginf_scalarrrgetattrrrr,r,r- _format_valueNs   zExcelFormatter._format_valuezIterable[ExcelCell]ccs|jjdkr|jstd|js*|js*dS|j}|j|jddd}t|}d}d}|jrzt |j jt rzt |j jdd}|jrHt |jD]\}}t||||jdVqt t||j|jD]\}\}}} || } |D]d\} } d\} }| dkr ||| | } }t||| d| | |jt|jdd|| |j| |d VqqnXt t|D]J\} } d tt| }t||| d||jt|jdd|| |jd VqT||_dS) NrNz]Writing to Excel with MultiIndex columns and no index ('index'=False) is not yet implemented.FZsparsifyZadjoinnamesrr>NN ctx_columns r r!r"r#r5r6r7r8r$r%.r r!r"r#r5r6r7r8)rZnlevelsrNotImplementedError _has_aliasesrformatrrrhrrr enumeraterrrziplevelscodestaker@r3rrrjoinmaprr)r+r level_strs level_lengths coloffsetlnumrspansr level_codesvaluesispan_valr$r%rkr,r,r-_format_header_mi`sv          z ExcelFormatter._format_header_mic cs|js |jrd}|jr6d}t|jjtr6t|jjj}|j}|jrt t |j|_t|jt|jkrt dt|jdt|jd|j}t |D]6\}}t |j||||jt|jddd||jdVqdS)NrrNzWriting z cols but got z aliasesrr)rrrrhrrrrrr r rrr3rrrrr)r+rZcolnamescolindexZcolnamer,r,r-_format_header_regulars2  z%ExcelFormatter._format_header_regularcstjtr}n}d}jjjrddjjjDdgtj}t ddt dd|rfdd t |D}j d 7_ t ||S) Nr,cSsg|]}|dk r|ndqSNrr,)r;xr,r,r- sz1ExcelFormatter._format_header..rcSs|o|Sr*r,)ryr,r,r-z/ExcelFormatter._format_header..cSs|dkSrr,)rr,r,r-rr c3s$|]\}}tj||jVqdSr*)rrr)r;rr"rr,r- sz0ExcelFormatter._format_header..rN)rhrrrrrrrrrrrr itertoolschain)r+genZgen2r r,rr-_format_headers     zExcelFormatter._format_headercCs"t|jjtr|S|SdSr*)rhrrr_format_hierarchical_rows_format_regular_rowsrr,r,r- _format_bodyszExcelFormatter._format_bodyc cs2|js |jr|jd7_|jr|jrJt|jtttj t frJ|jd}n(|jrdt|jt rd|j}n|j jj d}t|jtr|jd7_|r|jdk rt|jdd||jV|j j}t|j jtr|j j}t|D]6\}}t|j|d||jt|jdd|d|jdVqd}nd}||EdHdS)NrNrF ctx_indexr)rrrrrrhrgtuplenpndarrayrrrrrrrrrZ to_timestamprr3rrr_generate_body)r+rZ index_valuesidxZidxvalrr,r,r-rs@       z#ExcelFormatter._format_regular_rowsccs|js |jr|jd7_d}|jr|jjj}|jrRt|jtt t j t frR|j}t|j trr|jrr|jd7_tj|r|jdk rt|D] \}}t|jd|||jVq|jr|jjjdddd}t|}t||jjj|jjjD]\}}} |j| |j|jd} |D]h\} } d\} }| dkrB|j| | d} |}t|j| || | |jt|j dd| ||j!| |d Vq|d7}qn^t|jjD]P}t|D]8\}}t|j||||jt|j dd|||j!d Vq|d7}q|"|EdHdS) NrNrFTr)Z allow_fillZ fill_valuerrrr)#rrrrrrrrhrgrrrrrrrcomZ any_not_nonerrrrrrrrrZ _can_hold_naZ _na_valuer@r3rrrr)r+ZgcolidxZ index_labelsZcidxrrrrrrrrrr$r%Z indexcolvalsrZ indexcolvalr,r,r-rs        z(ExcelFormatter._format_hierarchical_rowscCs t|jS)z1Whether the aliases for column names are present.)r rrr,r,r-r]szExcelFormatter._has_aliasesr&)rr)c csntt|jD]Z}|jjdd|f}t|D]8\}}t|j||||dt|j dd|||j dVq.qdS)Nctxr) rangerrrZilocrr3rrrr)r+rZcolidxZseriesrr"r,r,r-rbs zExcelFormatter._generate_bodyccs2t||D]}||j|_|VqdSr*)r r rrrr")r+cellr,r,r-get_formatted_cellsrsz"ExcelFormatter.get_formatted_cellsstorage_options)rSheet1rztuple[int, int] | Noner) sheet_namestartrowstartcol freeze_panesenginerr)c Csddlm}|jj\} } | |jks,| |jkrPtd| d| d|jd|j|} t||rhd} n||||d}d} z|j | ||||d W5| r| Xd S) a  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} .. versionadded:: 1.2.0 r) ExcelWriterz-This sheet is too large! Your sheet size is: z, z Max sheet size is: F)r#rT)r r!r"N) Zpandas.io.excelr$rshapemax_rowsmax_colsrrrhcloseZ _write_cells) r+writerrr r!r"r#rr$Znum_rowsZnum_colsZformatted_cellsZ need_saver,r,r-writews2    zExcelFormatter.write) rNNTTNFrN)rrrNNN)r/r0r1rr&r'r.propertyrrrrrrrrrrrrrr*r,r,r,r-rsD "- E.O r)9r __future__r functoolsrrr rtypingrrrrr r r rZnumpyrZpandas._libs.libr Zpandas._typingr rZpandas.util._decoratorsrZpandas.util._exceptionsrZpandas.core.dtypesrZpandas.core.dtypes.commonrrZpandasrrrrZpandas.core.commoncorecommonrZpandas.core.shared_docsrZpandas.io.formats._color_datarZpandas.io.formats.cssrrZpandas.io.formats.formatrZpandas.io.formats.printingrrr3rFrr,r,r,r-s6 $