B
    ÔùÓ`ë=  ã               @   sp  d Z ddlmZ ddlmZ ddlmZ ddlmZmZ ddl	m
Z
 ddlmZmZmZ ddlmZmZ dd	lmZ dd
lmZ ddlmZmZ ddlmZmZmZ ddlmZ ddlm Z  ddl!m"Z" ddl#m$Z$ ddl%m&Z& ddl'm(Z(m)Z)m*Z* ddl+m,Z,m-Z- ddl.m/Z/ ddl0m1Z1 ddl2m3Z3 ddl4m5Z5 ddl6m7Z7 ddl8m9Z9 ddl:m;Z; ddl<m=Z= de Z>de Z?de Z@d e ZAd!e ZBd"e ZCd#e ZDd$e ZEd%e ZFd&e ZGd'e ZHd(e ZId)e ZJd*e ZKd+e ZLd,e ZMd-e ZNd.e ZOd/e ZPd0e ZQd1e ZRd2e ZSd3e ZTd4e ZUd5e ZVd6e ZWd7e ZXd8e ZYd9d:„ ZZG d;d<„ d<e[ƒZ\G d=d>„ d>e[ƒZ]d?S )@zReader for a single worksheet.é    )Úcopy)Úwarn)Ú	iterparse)ÚCellÚ
MergedCell)ÚText)ÚColumnDimensionÚRowDimensionÚSheetFormatProperties)ÚSHEET_MAIN_NSÚ	EXT_TYPES)ÚConditionalFormatting)Ú
Translator)Úget_column_letterÚcoordinate_to_tuple)Ú
from_excelÚfrom_ISO8601ÚWINDOWS_EPOCH)ÚExtensionListé   )Ú
AutoFilter)ÚHeaderFooter)ÚHyperlinkList)Ú
MergeCells)ÚPageMarginsÚPrintOptionsÚPrintPageSetup)ÚRowBreakÚColBreak)ÚSheetProtection)ÚScenarioList)ÚSheetViewList)ÚDataValidationList)ÚTablePartList)ÚWorksheetProperties)ÚSheetDimension)ÚRelatedz{%s}cz{%s}vz{%s}fz{%s}mergeCellsz{%s}isz{%s}colz{%s}rowz{%s}conditionalFormattingz{%s}legacyDrawingz{%s}sheetProtectionz
{%s}extLstz{%s}hyperlinksz{%s}tablePartsz{%s}printOptionsz{%s}pageMarginsz{%s}pageSetupz{%s}headerFooterz{%s}autoFilterz{%s}dataValidationsz{%s}sheetPrz{%s}sheetViewsz{%s}sheetFormatPrz{%s}rowBreaksz{%s}colBreaksz{%s}scenariosz{%s}sheetDataz{%s}dimensionz{%s}customSheetViewsc             C   s(   d| ksd| ksd| kr t | ƒS t| ƒS )z,Convert numbers as string to an int or floatÚ.ÚEÚe)ÚfloatÚint)Úvalue© r-   ú>/tmp/pip-unpacked-wheel-zhp1095k/openpyxl/worksheet/_reader.pyÚ_cast_numberN   s    r/   c               @   sŠ   e Zd Zdeeƒ eƒ fdd„Zdd„ Zdd„ Zdd	„ Zd
d„ Z	dd„ Z
dd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ ZdS )ÚWorkSheetParserFc             C   s–   d  | _ | _|| _|| _|| _|| _i | _i | _d | _| _	t
ƒ | _|| _|| _i | _i | _g | _d| _tƒ | _g | _d | _d | _tƒ | _tƒ | _d S )Nr   F)Zmin_rowZmin_colÚepochÚsourceÚshared_stringsÚ	data_onlyÚshared_formulaeÚarray_formulaeÚrow_counterÚcol_counterr#   ÚtablesÚdate_formatsÚtimedelta_formatsÚrow_dimensionsÚcolumn_dimensionsZnumber_formatsZkeep_vbar   Ú
hyperlinksÚ
formattingÚlegacy_drawingÚmerged_cellsr   Ú
row_breaksr   Ú
col_breaks)ÚselfÚsrcr3   r4   r1   r:   r;   r-   r-   r.   Ú__init__W   s*    zWorkSheetParser.__init__c       
      c   s>  t | jt| jt| jt| jt| j	t
| jt| jt| ji}tdtftdtftdtftdtftdtftdtftdtftdtft d	t!ft"d
t#ft$dt%ft&dt'ft(dt)fi}t*| j+ƒ}xŽ|D ]†\}}|j,}||krÜ|| |ƒ | -¡  q°||k�r|| }|d  .|¡}t/| |d |ƒ | -¡  q°|t0kr°|  1|¡}	| -¡  |	V  q°W d S )NÚprint_optionsÚpage_marginsÚ
page_setupr   Úauto_filterÚdata_validationsÚsheet_propertiesÚviewsÚsheet_formatÚ	scenariosr9   r>   rA   r   r   )2ÚCOL_TAGÚparse_column_dimensionsÚPROT_TAGÚparse_sheet_protectionÚEXT_TAGÚparse_extensionsÚCF_TAGÚparse_formattingÚ
LEGACY_TAGÚparse_legacyÚROW_BREAK_TAGÚparse_row_breaksÚCOL_BREAK_TAGÚparse_col_breaksÚCUSTOM_VIEWS_TAGÚparse_custom_viewsÚ	PRINT_TAGr   ÚMARGINS_TAGr   ÚPAGE_TAGr   Ú
HEADER_TAGr   Ú
FILTER_TAGr   ÚVALIDATION_TAGr"   ÚPROPERTIES_TAGr$   Ú	VIEWS_TAGr!   Ú
FORMAT_TAGr
   ÚSCENARIOS_TAGr    Ú	TABLE_TAGr#   ÚHYPERLINK_TAGr   Ú	MERGE_TAGr   r   r2   ÚtagÚclearÚ	from_treeÚsetattrÚROW_TAGÚ	parse_row)
rD   Z
dispatcherÚ
propertiesÚitÚ_ÚelementZtag_nameÚpropÚobjÚrowr-   r-   r.   Úparseq   sH    





zWorkSheetParser.parsec             C   sN   t | jƒ}x>|D ]6\}}|jtkr2t |¡}|jS |jtkr>P | ¡  qW dS )z@
        Get worksheet dimensions if they are provided.
        N)	r   r2   rm   ÚDIMENSION_TAGr%   ro   Z
boundariesÚDATA_TAGrn   )rD   rt   Z_eventrv   Zdimr-   r-   r.   Úparse_dimensions    s    



z WorkSheetParser.parse_dimensionsc             C   sº  |  dd¡}|  d¡}|  dd¡}|r.t|ƒ}|dkr<d }n| td ¡pJd }|rdt|ƒ\}}|| _n|  jd7  _| j| j }}| js¦| t	¡d k	r¦d}|  
|¡}�n|d k	�rx|dk�r"t|ƒ}|| jk�rvd	}yt|| j|| jkd
�}W n2 tk
�r   d ||¡}t|ƒ d}d}Y nX nT|dk�r<| jt|ƒ }n:|dk�rTtt|ƒƒ}n"|dk�rdd}n|d	k�rªt|ƒ}n2|dk�rª| t¡}	|	d k	�rªd}t |	¡}
|
j}|||||dœS )NÚtÚnÚrÚsr   Z	inlineStrr   ÚfÚd)Ú	timedeltaz|Cell {0} is marked as a date but the serial value {1} is outside the limits for dates. The cell will be treated as an error.r)   z#VALUE!ÚbÚstr)ry   Úcolumnr,   Ú	data_typeÚstyle_id)Úgetr+   ÚfindtextÚ	VALUE_TAGr   r8   r7   r4   ÚfindÚFORMULA_TAGÚparse_formular/   r:   r   r1   r;   Ú
ValueErrorÚformatr   r3   Úboolr   ÚINLINE_STRINGr   ro   Úcontent)rD   rv   rˆ   Ú
coordinater‰   r,   ry   r‡   ÚmsgÚchildZrichtextr-   r-   r.   Ú
parse_cell±   sX    











zWorkSheetParser.parse_cellc             C   sž   |  t¡}| d¡}| d¡}d}|jdk	r6||j7 }|dkrPt|jƒ| j|< nJ|dkrš| d¡}|| jkr‚| j| }| |¡}n|dkršt	||ƒ| j|< |S )zC
        possible formulae types: shared, array, datatable
        r~   r€   ú=NÚarrayZsharedÚsi)
r�   rŽ   rŠ   ÚtextÚdictÚattribr6   r5   Ztranslate_formular   )rD   rv   ZformulaZformula_typer•   r,   ÚidxZtransr-   r-   r.   r�   é   s     







zWorkSheetParser.parse_formulac             C   s0   t |jƒ}tt|d ƒƒ}||d< || j|< d S )NÚminÚindex)r�   rž   r   r+   r=   )rD   ÚcolÚattrsr‡   r-   r-   r.   rQ     s    
z'WorkSheetParser.parse_column_dimensionsc                sÈ   t |jƒ}d|krnyt|d ƒˆ _W q| tk
rj   t|d ƒ}| ¡ rTt|ƒˆ _nt|d › d�ƒ‚Y q|X nˆ  jd7  _dˆ _dd„ |D ƒ}|ddh r¬|ˆ jt	ˆ jƒ< ‡ fdd	„|D ƒ}ˆ j|fS )
Nr€   z is not a valid row numberr   r   c             S   s   h | ]}|  d ¡s|’qS )Ú{)Ú
startswith)Ú.0Úkr-   r-   r.   ú	<setcomp>  s    z,WorkSheetParser.parse_row.<locals>.<setcomp>Zspansc                s   g | ]}ˆ   |¡‘qS r-   )r˜   )r¦   Úel)rD   r-   r.   ú
<listcomp>  s    z-WorkSheetParser.parse_row.<locals>.<listcomp>)
r�   rž   r+   r7   r�   r*   Ú
is_integerr8   r<   r†   )rD   ry   r£   ÚvalÚkeysÚcellsr-   )rD   r.   rr   	  s     
zWorkSheetParser.parse_rowc          
   C   sT   yt  |¡}| j |¡ W n4 tk
rN } zd|› �}t|ƒ W d d }~X Y nX d S )NzKFailed to load a conditional formatting rule. It will be discarded. Cause: )r   ro   r?   ÚappendÚ	TypeErrorr   )rD   rv   Úcfr)   r–   r-   r-   r.   rW   "  s    

z WorkSheetParser.parse_formattingc             C   s2   t  |¡}| d¡}|d k	r(| |d¡ || _d S )NÚpasswordT)r   ro   rŠ   Úset_passwordÚ
protection)rD   rv   r´   r²   r-   r-   r.   rS   +  s
    

z&WorkSheetParser.parse_sheet_protectionc             C   sB   t  |¡}x2|jD ](}t |j ¡ d¡}d |¡}t|ƒ qW d S )NÚUnknownz2{0} extension is not supported and will be removed)	r   ro   Úextr   rŠ   ÚuriÚupperr‘   r   )rD   rv   ZextLstr)   Zext_typer–   r-   r-   r.   rU   3  s
    

z WorkSheetParser.parse_extensionsc             C   s   t  |¡}|j| _d S )N)r&   ro   Úidr@   )rD   rv   rx   r-   r-   r.   rY   ;  s    
zWorkSheetParser.parse_legacyc             C   s   t  |¡}|| _d S )N)r   ro   rB   )rD   rv   Úbrkr-   r-   r.   r[   @  s    
z WorkSheetParser.parse_row_breaksc             C   s   t  |¡}|| _d S )N)r   ro   rC   )rD   rv   rº   r-   r-   r.   r]   E  s    
z WorkSheetParser.parse_col_breaksc             C   s   t ƒ | _tƒ | _d S )N)r   rB   r   rC   )rD   rv   r-   r-   r.   r_   J  s    z"WorkSheetParser.parse_custom_viewsN)Ú__name__Ú
__module__Ú__qualname__r   ÚsetrF   rz   r}   r˜   r�   rQ   rr   rW   rS   rU   rY   r[   r]   r_   r-   r-   r-   r.   r0   U   s    /8	r0   c               @   sh   e Zd ZdZdd„ Zdd„ Zdd„ Zdd	„ Zd
d„ Zdd„ Z	dd„ Z
dd„ Zdd„ Zdd„ Zdd„ ZdS )ÚWorksheetReaderz4
    Create a parser and apply it to a workbook
    c             C   s0   || _ t||||jj|jj|jjƒ| _g | _d S )N)Úwsr0   Úparentr1   Z_date_formatsZ_timedelta_formatsÚparserr9   )rD   rÀ   Z
xml_sourcer3   r4   r-   r-   r.   rF   V  s
    zWorksheetReader.__init__c             C   s¢   x|| j  ¡ D ]n\}}xd|D ]\}| jjj|d  }t| j|d |d |d�}|d |_|d |_|| jj|d |d f< qW qW | j j	| j_
| jjrž| jj| j_d S )Nr‰   ry   r‡   )ry   r‡   Zstyle_arrayr,   rˆ   )rÂ   rz   rÀ   rÁ   Ú_cell_stylesr   Ú_valuerˆ   Z_cellsr6   Zformula_attributesZmax_rowZ_current_row)rD   rŸ   ry   ÚcellÚstyleÚcr-   r-   r.   Ú
bind_cells^  s    


 zWorksheetReader.bind_cellsc             C   sN   xH| j jD ]<}x6|jD ],}|jd k	r6| jjj|j |_|| jj|< qW q
W d S )N)	rÂ   r?   ÚrulesZdxfIdrÀ   rÁ   Z_differential_stylesZdxfZconditional_formatting)rD   r±   Zruler-   r-   r.   Úbind_formattingk  s
    
zWorksheetReader.bind_formattingc             C   s4   x.| j jjD ] }| jj|j }| j |j¡ qW d S )N)rÂ   r9   Z	tablePartrÀ   Ú_relsr¹   r¯   ÚTarget)rD   r~   Úrelr-   r-   r.   Úbind_tabless  s    zWorksheetReader.bind_tablesc             C   sp   ddl m} ddlm} | jjs$d S g }x6| jjjD ](}|| j|jƒ}| j 	|¡ | 
|¡ q4W ||ƒ| j_d S )Nr   )ÚMultiCellRange)ÚMergedCellRange)Zopenpyxl.worksheet.cell_rangerÏ   Zopenpyxl.worksheet.mergerÐ   rÂ   rA   Z	mergeCellrÀ   ÚrefZ_clean_merge_ranger¯   )rD   rÏ   rÐ   ÚrangesZcrZmcrr-   r-   r.   Úbind_merged_cellsy  s    z!WorksheetReader.bind_merged_cellsc             C   s°   xª| j jjD ]œ}|jr,| jj|j }|j|_d|jkr€xp| j|j D ]6}x0|D ](}yt	|ƒ|_W qN t
k
rt   Y qNX qNW qDW q| j|j }t|tƒr¢|  |j¡}||_qW d S )Nú:)rÂ   r>   Ú	hyperlinkr¹   rÀ   rË   rÌ   ÚtargetrÑ   r   ÚAttributeErrorÚ
isinstancer   Únormalize_merged_cell_linkr•   )rD   ÚlinkrÍ   ry   rÅ   r-   r-   r.   Úbind_hyperlinks‡  s    


zWorksheetReader.bind_hyperlinksc             C   s0   x*| j jD ]}||kr
| j j|jd Ž S q
W dS )zš
        Returns the appropriate cell to which a hyperlink, which references a merged cell at the specified coordinates,
        should be bound.
        r   N)rÀ   rA   rÅ   Útop)rD   ZcoordÚrngr-   r-   r.   rÙ   š  s    z*WorksheetReader.normalize_merged_cell_linkc             C   sZ   xT| j j ¡ D ]D\}}d|kr<t|d ƒ}| jjj| |d< t| jf|Ž| jj|< qW d S )NrÆ   )rÂ   r=   Úitemsr+   rÀ   rÁ   rÃ   r   )rD   r¢   ZcdÚkeyr-   r-   r.   Úbind_col_dimensions£  s
    z#WorksheetReader.bind_col_dimensionsc             C   s^   xX| j j ¡ D ]H\}}d|kr<t|d ƒ}| jjj| |d< t| jf|Ž| jjt|ƒ< qW d S )Nr�   )rÂ   r<   rÞ   r+   rÀ   rÁ   rÃ   r	   )rD   ry   Úrdrß   r-   r-   r.   Úbind_row_dimensions«  s
    z#WorksheetReader.bind_row_dimensionsc             C   s6   x0dD ](}t | j|d ƒ}|d k	rt| j||ƒ qW d S )N)rG   rH   rI   r   rJ   rK   rL   rM   rN   rB   rC   rO   r@   r´   )ÚgetattrrÂ   rp   rÀ   )rD   r§   Úvr-   r-   r.   Úbind_properties³  s    
zWorksheetReader.bind_propertiesc             C   sD   |   ¡  |  ¡  |  ¡  |  ¡  |  ¡  |  ¡  |  ¡  |  ¡  d S )N)rÈ   rÓ   rÛ   rÊ   rà   râ   rÎ   rå   )rD   r-   r-   r.   Úbind_all¿  s    zWorksheetReader.bind_allN)r»   r¼   r½   Ú__doc__rF   rÈ   rÊ   rÎ   rÓ   rÛ   rÙ   rà   râ   rå   ræ   r-   r-   r-   r.   r¿   Q  s   	r¿   N)^rç   r   Úwarningsr   Zopenpyxl.xml.functionsr   Zopenpyxl.cellr   r   Zopenpyxl.cell.textr   Zopenpyxl.worksheet.dimensionsr   r	   r
   Zopenpyxl.xml.constantsr   r   Zopenpyxl.formatting.formattingr   Zopenpyxl.formula.translater   Zopenpyxl.utilsr   r   Zopenpyxl.utils.datetimer   r   r   Zopenpyxl.descriptors.excelr   Úfiltersr   Zheader_footerr   rÕ   r   Úmerger   Úpager   r   r   Z	pagebreakr   r   r´   r   Zscenarior    rM   r!   Zdatavalidationr"   Útabler#   rs   r$   Z
dimensionsr%   Úrelatedr&   ZCELL_TAGrŒ   rŽ   rl   r“   rP   rq   rV   rX   rR   rT   rk   rj   r`   ra   rb   rc   rd   re   rf   rg   rh   rZ   r\   ri   r|   r{   r^   r/   Úobjectr0   r¿   r-   r-   r-   r.   Ú<module>   st    }