a Of@szdZddlmZddlmZmZmZmZddlZddl Z ddl Z ddl m Z m Z mZmZddlZddlZddlmZddlmZddlmZdd lmZdd lmZmZdd lmZm Z m!Z!m"Z"ddl#m$m%Z&dd l'm(Z(dd l)m*Z*ddl+m,Z,m-Z-ddl.m/Z/ddl0m1Z1e rcsz)CssExcelCell.__init__..r#r$r%r&) frozensetitemssuperr1) r.r#r$r%r&r9r:r;r<kwargsZdeclaration_dictZunique_declarations __class__r/r0r1Us   zCssExcelCell.__init__)r2r3r4r1 __classcell__r/r/rGr0r6Tsr6c@s0eZdZUdZeZdddddddZdddddddddddddd 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#dKdXdYdZd[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)rKztext-topmiddleZbaselinerMz text-bottomTF) boldZbolderZ600Z700Z800Z900normalZlighter100Z200Z300Z400500)rPitalicZoblique)Zserifz sans-serifZcursiveZfantasycCsi|]}||qSr/r=)r?r&r/r/r0rAszCSSToExcelConverter.) dashedZ mediumDashDotZ dashDotDothairdottedmediumDashDotDotdoubleZdashDotZ slantDashDot mediumDashedzdict[str, str] | None inheritedN str | Noner+)r^r,cCs.|dur|||_nd|_t|j|_dSr-) compute_cssr^ functoolscache_call_uncached _call_cached)r.r^r/r/r0r1szCSSToExcelConverter.__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. )rd)r.rer/r/r0__call__szCSSToExcelConverter.__call__cCs|||j}||Sr-)r`r^ build_xlstyle)r.re propertiesr/r/r0rcsz"CSSToExcelConverter._call_uncachedzMapping[str, str])propsr,csN||||||||||d}dddfdd ||S)N) alignmentborderfillfontZ number_formatdict[str, str | None]r+)dr,csFt|D]4\}}|dur$||=q t|tr ||s ||=q dS)z4Remove key where value is None, through nested dictsN)listrD isinstancedict)rokv remove_noner/r0rvs z6CSSToExcelConverter.build_xlstyle..remove_none)build_alignment build_border build_fill build_fontbuild_number_format)r.rioutr/rur0rgs  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.rir/r/r0rwsz#CSSToExcelConverter.build_alignmentcCs|d}|r|j|SdS)Nzvertical-align)r VERTICAL_MAP)r.riZvertical_alignr/r/r0rs  z+CSSToExcelConverter._get_vertical_alignmentz bool | NonecCs"|ddurdSt|ddvS)Nz white-space)Znowrapprezpre-line)rboolrr/r/r0r sz%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_stylercolor_to_excel)r?Zsiderir.r/r0rAs z4CSSToExcelConverter.build_border..rKrightrMleftr/rr/rr0rxs z CSSToExcelConverter.build_border)r&widthrcCs|dur|dur|durdS|dur0|dur0dS|dvrg?rgffffff@ZmediumZthick)_width_to_float)r.rrr/r/r0rVs z#CSSToExcelConverter._get_width_namefloat)rr,cCs|dur d}||S)NZ2pt) _pt_to_float)r.rr/r/r0r`sz#CSSToExcelConverter._width_to_floatstr) pt_stringr,cCs|dsJt|dS)Npt)endswithrrstrip)r.rr/r/r0resz CSSToExcelConverter._pt_to_float)ricCs&|d}|dvr"||ddSdS)Nzbackground-color)N transparentrr)ZfgColorZ patternType)rr)r.riZ fill_colorr/r/r0ryis zCSSToExcelConverter.build_fillrncCs,|d}t|tr |ddn|}d|iS)Nz number-format§;Z format_code)rrqrreplace)r.rifcr/r/r0r{ps z'CSSToExcelConverter.build_number_formatz$dict[str, bool | float | str | None]c Csv||}||}|r |dnd||||||||d|vrNdndd|vpZd||d||d S)Nr underlinesinglez line-throughr) namefamilysizerOrSrstrikerZshadow) _get_font_names_get_decoration_select_font_family_get_font_size _get_is_bold_get_is_italicrr _get_shadow)r.ri font_names decorationr/r/r0rzus   zCSSToExcelConverter.build_fontcCs|d}|r|j|SdS)Nz font-weight)rBOLD_MAP)r.riweightr/r/r0rs  z CSSToExcelConverter._get_is_boldcCs|d}|r|j|SdS)Nz font-style)r ITALIC_MAP)r.riZ font_styler/r/r0rs  z"CSSToExcelConverter._get_is_italicz Sequence[str]cCs"|d}|dur|SdSdS)Nztext-decorationr/)rsplit)r.rirr/r/r0rs z#CSSToExcelConverter._get_decoration)rr,cCsd|vr dSdS)Nrrr/)r.rr/r/r0_get_underlinesz"CSSToExcelConverter._get_underlinecCs d|vrttd|dSdS)Nz text-shadowz ^[^#(]*[1-9])rresearchrr/r/r0rszCSSToExcelConverter._get_shadowcCstd|dd}g}|D]f}|dddkrF|dddd}n.|dddkrl|ddd d}n|}|r||q|S) Nz(?x) ( "(?:[^"]|\\")+" | '(?:[^']|\\')+' | [^'",]+ )(?=,|\s*$) z font-familyrT"z\"'z\')rfindallrrstripappend)r.riZfont_names_tmprrr/r/r0rs   z#CSSToExcelConverter._get_font_namesz float | NonecCs |d}|dur|S||S)Nz font-size)rr)r.rirr/r/r0rs z"CSSToExcelConverter._get_font_sizer*)rr,cCs&d}|D]}|j|}|rq"q|Sr-) FAMILY_MAPr)r.rrrr/r/r0rs  z'CSSToExcelConverter._select_font_family)r%r,cCs`|dur dS||r ||Sz |j|WStyZtjdt|ttdYn0dS)NzUnhandled color format: r) _is_hex_color_convert_hex_to_excel NAMED_COLORSKeyErrorrrrrr r.r%r/r/r0rs      z"CSSToExcelConverter.color_to_excelr) color_stringr,cCst|dS)N#)r startswith)r.rr/r/r0rsz!CSSToExcelConverter._is_hex_colorcCsH|d}||r<|dd|dd|ddS|SdS)NrrrUrT)lstrip_is_shorthand_colorupperr.rcoder/r/r0rs  (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/r0rs    z'CSSToExcelConverter._is_shorthand_color)N))r2r3r4__doc__rrrrrrr__annotations__r1rr`rfrcrgrwrrrxrrrrryr{rzrrrrrrrrrrrrr/r/r/r0rJms   7  rJc @seZdZdZdZdZd:d 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/d0d;d3d d)d)d4d d5d6dd7 d8d9ZdS)<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@rNTFinfrr_zSequence[Hashable] | NonezSequence[Hashable] | boolrzIndexLabel | Noner8r+) na_rep float_formatcolsheaderindex index_label merge_cellsinf_repstyle_converterr,c Csd|_||_t|tsB||_|j|j}| dur:t} | |_n d|_d|_||_ |durt 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) rowcounterrrqrstylerZ_computedatarJrdfrr intersectionrrsetZreindexrrrrrr) r.rrrrrrrrrrr/r/r0r1s2     zExcelFormatter.__init__z dict[str, dict[str, str | bool]])r,cCs ddidddddddddS) NrOTrrrLrK)r}r~)rmZbordersrjr/r.r/r/r0 header_styleGszExcelFormatter.header_stylecCst|rt|r|j}nJt|rdt|r4|j}n0t|rLd|j}n|jdurdt |j|}t |dddur|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/r0 _format_valueTs   zExcelFormatter._format_valuezIterable[ExcelCell]ccs|jjdkr|jstd|js*|js*dS|j}|j|jdd}t|}d}d}|jrxt |j jt rxt |j jdd}|jrFt |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 VqR||_dS) NrTz]Writing to Excel with MultiIndex columns and no index ('index'=False) is not yet implemented.FZsparsifyZ include_namesrrBNN ctx_columns r#r$r%r&r9r:r;r<r'r(.r#r$r%r&r9r:r;r<)rZnlevelsrNotImplementedError _has_aliasesr _format_multirrrqrrr enumeratenamesr!rziplevelscodestakerDr6rrrjoinmaprr)r.r level_strs level_lengths coloffsetlnumrspansr level_codesvaluesispan_valr'r(rtr/r/r0_format_header_mifsr          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)NrrTzWriting z cols but got z aliasesrr)rrrrqrrrrrr rrrr6rrrrr)r.rZcolnamescolindexZcolnamer/r/r0_format_header_regulars6  z%ExcelFormatter._format_header_regularcstjtr}n}d}jjjrddjjjDdgtj}t dddd|Drfd dt |D}j d 7_ t ||S) Nr/cSsg|]}|dur|ndqS)Nrr/r?xr/r/r0 z1ExcelFormatter._format_header..rcSs|o|Sr-r/)ryr/r/r0rz/ExcelFormatter._format_header..css|]}|dkVqdS)rNr/r r/r/r0 rz0ExcelFormatter._format_header..c3s$|]\}}tj||jVqdSr-)r!rr)r?r r%rr/r0rsrT)rqrrr r rrrrrareducerr itertoolschain)r.genZgen2r#r/rr0_format_headers     zExcelFormatter._format_headercCs"t|jjtr|S|SdSr-)rqrrr_format_hierarchical_rows_format_regular_rowsrr/r/r0 _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|jdurt|jdd||jV|j j}t|j jtr|j j}t|D]6\}}t|j|d||jt|jdd|d|jdVqd}nd}||EdHdS)NrTrF ctx_indexr)rrrrrrqrptuplenpndarrayrrrrrrr!rrZ to_timestamprr6rrr_generate_body)r.rZ index_valuesidxZidxvalrr/r/r0rs>      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|jdurt|D] \}}t|jd|||jVq|jr|jjjddd}t|}t||jjj|jjjD]\}}} |j| |j|jd} |D]h\} } d\} }| dkr@|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) NrTrFTr)Z allow_fillZ fill_valuerrrr)#rrrrrrrrqrprrrrrrrcomZ any_not_nonerr!rrrrrrrZ _can_hold_naZ _na_valuerDr6rrrr )r.ZgcolidxZ index_labelsZcidxrrrrrrrrr r'r(Z indexcolvalsr!Z indexcolvalr/r/r0rsv       z(ExcelFormatter._format_hierarchical_rowscCs t|jS)z1Whether the aliases for column names are present.)r rrr/r/r0rcszExcelFormatter._has_aliasesr))rr,c csntt|jD]Z}|jjdd|f}t|D]8\}}t|j||||dt|j dd|||j dVq.qdS)Nctxr) rangerrrZilocrr6rrrr)r.rZcolidxZseriesrr%r/r/r0r hs zExcelFormatter._generate_bodyccs2t||D]}||j|_|VqdSr-)rrrrrr%)r.cellr/r/r0get_formatted_cellsxsz"ExcelFormatter.get_formatted_cellsstorage_options)r'Sheet1rz)FilePath | WriteExcelBuffer | ExcelWriterztuple[int, int] | NonezStorageOptions | Noner7) writer sheet_namestartrowstartcol freeze_panesenginer' engine_kwargsr,c  Csddlm} |jj\} } | |jks,| |jkrPtd| d| d|jd|j|dur\i}|} t|| rtd} n| ||||d}d } z$|j | ||||d W| r| n| r| 0dS) 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. rrz-This sheet is too large! Your sheet size is: z, z Max sheet size is: NF)r.r'r/T)r+r,r-) Zpandas.io.excelr rshapemax_rowsmax_colsrr&rqZ _write_cellsclose)r.r)r*r+r,r-r.r'r/r Znum_rowsZnum_colsZformatted_cellsZ need_saver/r/r0write}sD"     zExcelFormatter.write) rNNTTNFrN)r(rrNNNN)r2r3r4rr1r2r1propertyrrr r rrrrrr r&r rr4r/r/r/r0rsF "- E.O r)s<