B
    ÔùÓ`â*  ã               @   s²  d Z ddlmZmZmZ ddlmZ ddlmZ ddl	Z
ddlZddlmZ yddlmZ W n ek
rt   d	ZY nX dd
lmZ ddlmZmZmZmZmZmZmZmZmZ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+ ddl,m-Z-m.Z. ddl/m0Z0m1Z1m2Z2 ddl3m4Z4 ddl5m6Z6 ddl7m8Z8 ddl9m:Z: ddl;m<Z< ddl=m>Z> ddl?m@Z@ dZAdd„ ZBdd „ ZCG d!d"„ d"ƒZDd	ed	d#fd$d%„ZEdS )&zRead an xlsx file into Pythoné    )ÚZipFileÚZIP_DEFLATEDÚ
BadZipfile)Úexc_info)ÚBytesION)ÚTableDefinitioné   )ÚKEEP_VBAF)ÚInvalidFileException)ÚARC_SHARED_STRINGSÚARC_COREÚARC_CONTENT_TYPESÚARC_WORKBOOKÚ	ARC_THEMEÚCOMMENTS_NSÚSHARED_STRINGSÚEXTERNAL_LINKÚXLTMÚXLTXÚXLSMÚXLSX)Ú
MergedCell)ÚCommentSheeté   )Úread_string_table)ÚWorkbookParser)Úapply_stylesheet)ÚDocumentProperties)ÚManifestÚOverride)ÚRelationshipListÚget_dependentsÚget_rels_path)ÚReadOnlyWorksheet)ÚWorksheetReader)Ú
Chartsheet)ÚTable)ÚSpreadsheetDrawing)Ú
fromstring)Úfind_images)z.xlsxz.xlsmz.xltxz.xltmc             C   sn   t | dƒ}|s`tj | ¡d  ¡ }|tkr`|dkr8d}n |dkrFd}nd|d t¡f }t|ƒ‚t| d	ƒ}|S )
a¢  
    Does a first check whether filename is a string or a file-like
    object. If it is a string representing a filename, a check is done
    for supported formats by checking the given file-extension. If the
    file-extension is not in SUPPORTED_FORMATS an InvalidFileException
    will raised. Otherwise the filename (resp. file-like object) will
    forwarded to zipfile.ZipFile returning a ZipFile-Instance.
    Úreadéÿÿÿÿz.xlszŠopenpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format.z.xlsbz|openpyxl does not support binary format .xlsb, please convert this file to .xlsx format if you want to open it with openpyxlzropenpyxl does not support %s file format, please check you can open it with Excel first. Supported formats are: %sú,Úr)	ÚhasattrÚosÚpathÚsplitextÚlowerÚSUPPORTED_FORMATSÚjoinr
   r   )ÚfilenameZis_file_likeZfile_formatÚmsgÚarchive© r8   ú9/tmp/pip-unpacked-wheel-zhp1095k/openpyxl/reader/excel.pyÚ_validate_archiveC   s    	

r:   c             C   sj   t tttg}x|D ]}|  |¡}|r|S qW dd„ | jD ƒ}|t|ƒ@ }|r^tdt | 	¡ ƒS t
dƒ‚d S )Nc             S   s   h | ]
}|j ’qS r8   )ÚContentType)Ú.0Úpr8   r8   r9   ú	<setcomp>l   s    z&_find_workbook_part.<locals>.<setcomp>ú/z$File contains no valid workbook part)r   r   r   r   ÚfindZDefaultÚsetr   r   ÚpopÚIOError)ÚpackageZworkbook_typesÚctÚpartÚdefaultsZworkbook_typer8   r8   r9   Ú_find_workbook_partd   s    

rH   c               @   sb   e Zd ZdZdedd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S )ÚExcelReaderzQ
    Read an Excel package and dispatch the contents to the relevant modules
    FTc             C   s8   t |ƒ| _| j ¡ | _|| _|| _|| _|| _g | _d S )N)	r:   r7   ÚnamelistÚvalid_filesÚ	read_onlyÚkeep_vbaÚ	data_onlyÚ
keep_linksÚshared_strings)ÚselfÚfnrL   rM   rN   rO   r8   r8   r9   Ú__init__z   s    
zExcelReader.__init__c             C   s$   | j  t¡}t|ƒ}t |¡| _d S )N)r7   r*   r   r(   r   Ú	from_treerD   )rQ   ÚsrcÚrootr8   r8   r9   Úread_manifest…   s    zExcelReader.read_manifestc          	   C   sH   | j  t¡}|d k	rD|jdd … }| j |¡�}t|ƒ| _W d Q R X d S )Nr   )rD   r@   r   ÚPartNamer7   Úopenr   rP   )rQ   rE   Zstrings_pathrU   r8   r8   r9   Úread_strings‹   s
    zExcelReader.read_stringsc             C   s´   t | jƒ}t| j|jdd … | jd�| _| j ¡  | jj}g |_	| j
|_| j|_|jttfk|_| jrœttƒ dtƒ|_x$| jD ]}|j || j |¡¡ q~W | jrª| j|_|| _d S )Nr   )rO   Úa)rH   rD   r   r7   rX   rO   ÚparserÚparseÚwbÚ_sheetsrN   Z
_data_onlyrL   Z
_read_onlyr;   r   r   ÚtemplaterM   r   r   r   Úvba_archiverK   Úwritestrr*   Z_archive)rQ   Zwb_partr^   Únamer8   r8   r9   Úread_workbook“   s    

zExcelReader.read_workbookc             C   s,   t | jkr(t| j t ¡ƒ}t |¡| j_d S )N)	r   rK   r(   r7   r*   r   rT   r^   Z
properties)rQ   rU   r8   r8   r9   Úread_propertiesª   s    
zExcelReader.read_propertiesc             C   s   t | jkr| j t ¡| j_d S )N)r   rK   r7   r*   r^   Zloaded_theme)rQ   r8   r8   r9   Ú
read_theme°   s    
zExcelReader.read_themec          	   C   sÀ   |j }t|ƒ}g }|| jkr(t| j|ƒ}| j |d¡�}| ¡ }W d Q R X t|ƒ}t 	|¡}	| j
|	_|j|	_| j
 |	¡ | tj¡}
x6|
D ].}t| j|j ƒ\}}x|D ]}|	 |¡ q¦W qŠW d S )Nr-   )Útargetr"   rK   r!   r7   rY   r*   r(   r%   rT   r^   Ú_parentrc   ÚtitleZ
_add_sheetr@   r'   Ú	_rel_typer)   Ú	add_chart)rQ   ÚsheetÚrelZ
sheet_pathÚ	rels_pathÚrelsrU   ÚxmlÚnodeÚcsÚdrawingsÚchartsÚimagesÚcr8   r8   r9   Úread_chartsheetµ   s"    



zExcelReader.read_chartsheetc             C   sª  d}�xž| j  ¡ D �]Ž\}}|j| jkr*qd|jkrB|  ||¡ qt|jƒ}tƒ }|| jkrht| j	|ƒ}| j
ržt| j|j|j| jƒ}|j|_| jj |¡ qn<| j	 |j¡}| j |j¡}||_t||| j| jƒ}| ¡  x˜| t¡D ]Š}	| j	 |	j¡}
t t|
ƒ¡}xh|jD ]^\}}y||| _ W nD t!k
�rh   || }t"|t#ƒ�rdt$ %| &|j'|j(¡¡ �wY nX �qW qæW | jj)�r–|j*�r–||j* j|_*nd |_*x8|j+D ].}| j	 |¡}
t|
ƒ}t, |¡}| -|¡ �q¤W | t.j/¡}x\|D ]T}t0| j	|jƒ\}}x|D ]}| 1||j2¡ �qW x|D ]}| 3||j2¡ �q"W �qèW | t4j5¡}xL|D ]D}	|	j6}| j	 |¡}
t|
ƒ}t4 |¡}| j j7|j8 |_9| :|¡ �qRW |j|_qW d S )Nz~Cell '{0}':{1} is part of a merged range but has a comment which will be removed because merged cells cannot contain any data.Z
chartsheet);r\   Zfind_sheetsrg   rK   ZTyperw   r"   r    r!   r7   rL   r#   r^   rc   rP   ÚstateZsheet_stater_   ÚappendrY   Zcreate_sheetZ_relsr$   rN   Zbind_allr@   r   r*   r   rT   r(   ÚcommentsÚcommentÚAttributeErrorÚ
isinstancer   ÚwarningsÚwarnÚformatri   Z
coordinatera   Zlegacy_drawingÚtablesr&   Z	add_tabler'   rj   r)   rk   ÚanchorZ	add_imager   Zrel_typeZTargetZpivot_cachesZcacheIdÚcacheZ	add_pivot)rQ   Zcomment_warningrl   rm   rn   ro   ÚwsÚfhZ	ws_parserr-   rU   Zcomment_sheetÚrefr{   rv   Útrp   Útablers   rt   ru   ZimZ	pivot_relZ
pivot_pathÚtreeZpivotr8   r8   r9   Úread_worksheetsË   sp    








zExcelReader.read_worksheetsc             C   s\   |   ¡  |  ¡  |  ¡  |  ¡  |  ¡  t| j| jƒ |  ¡  | j	 
¡  | jsX| j ¡  d S )N)rW   rZ   rd   re   rf   r   r7   r^   rŠ   r\   Zassign_namesrL   Úclose)rQ   r8   r8   r9   r*     s    
zExcelReader.readN)Ú__name__Ú
__module__Ú__qualname__Ú__doc__r	   rS   rW   rZ   rd   re   rf   rw   rŠ   r*   r8   r8   r8   r9   rI   t   s   
HrI   Tc             C   s   t | ||||ƒ}| ¡  |jS )aµ  Open the given filename and return the workbook

    :param filename: the path to open or a file-like object
    :type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile`

    :param read_only: optimised for reading, content cannot be edited
    :type read_only: bool

    :param keep_vba: preseve vba content (this does NOT mean you can use it)
    :type keep_vba: bool

    :param data_only: controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet
    :type data_only: bool

    :param keep_links: whether links to external workbooks should be preserved. The default is True
    :type keep_links: bool

    :rtype: :class:`openpyxl.workbook.Workbook`

    .. note::

        When using lazy load, all worksheets will be :class:`openpyxl.worksheet.iter_worksheet.IterableWorksheet`
        and the returned workbook will be read-only.

    )rI   r*   r^   )r5   rL   rM   rN   rO   Úreaderr8   r8   r9   Úload_workbook   s    r‘   )Fr�   Úzipfiler   r   r   Úsysr   Úior   Úos.pathr/   r~   Zopenpyxl.pivot.tabler   Útestsr	   ÚImportErrorZopenpyxl.utils.exceptionsr
   Zopenpyxl.xml.constantsr   r   r   r   r   r   r   r   r   r   r   r   Zopenpyxl.cellr   Zopenpyxl.comments.comment_sheetr   Ústringsr   Zworkbookr   Zopenpyxl.styles.stylesheetr   Zopenpyxl.packaging.corer   Zopenpyxl.packaging.manifestr   r   Zopenpyxl.packaging.relationshipr    r!   r"   Zopenpyxl.worksheet._read_onlyr#   Zopenpyxl.worksheet._readerr$   Zopenpyxl.chartsheetr%   Zopenpyxl.worksheet.tabler&   Z$openpyxl.drawing.spreadsheet_drawingr'   Zopenpyxl.xml.functionsr(   rs   r)   r3   r:   rH   rI   r‘   r8   r8   r8   r9   Ú<module>   sD   
8! -