U Dx`*@sdZddlmZmZmZddlmZddlmZddl Z ddl Z ddl m Z zddlmZWnek rtd ZYnXdd lmZdd lmZmZmZmZmZmZmZmZmZmZmZmZdd l m!Z!dd l"m#Z#ddl$m%Z%ddl&m'Z'ddl(m)Z)ddl*m+Z+ddl,m-Z-m.Z.ddl/m0Z0m1Z1m2Z2ddl3m4Z4ddl5m6Z6ddl7m8Z8ddl9m:Z:ddl;mZ>ddl?m@Z@dZAddZBdd ZCGd!d"d"ZDd ed d#fd$d%ZEdS)&zRead an xlsx file into Python)ZipFile ZIP_DEFLATED BadZipfile)exc_info)BytesION)TableDefinition)KEEP_VBAF)InvalidFileException) ARC_SHARED_STRINGSARC_COREARC_CONTENT_TYPES ARC_WORKBOOK ARC_THEME COMMENTS_NSSHARED_STRINGS EXTERNAL_LINKXLTMXLTXXLSMXLSX) MergedCell) CommentSheet)read_string_table)WorkbookParser)apply_stylesheet)DocumentProperties)ManifestOverride)RelationshipListget_dependents get_rels_path)ReadOnlyWorksheet)WorksheetReader) Chartsheet)Table)SpreadsheetDrawing) fromstring) find_images)z.xlsxz.xlsmz.xltxz.xltmcCsnt|d}|s`tj|d}|tkr`|dkr8d}n |dkrFd}nd|dtf}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. readz.xlszopenpyxl 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) hasattrospathsplitextlowerSUPPORTED_FORMATSjoinr r)filenameZ is_file_likeZ file_formatmsgarchiver8lsz&_find_workbook_part../z$File contains no valid workbook part) rrrrfindDefaultsetrrpopIOError)packageZworkbook_typesctpartdefaultsZ workbook_typer8r8r9_find_workbook_partds    rIc@sbeZdZdZdeddfddZddZdd Zd d Zd d Z ddZ ddZ ddZ ddZ dS) ExcelReaderzQ Read an Excel package and dispatch the contents to the relevant modules FTcCs8t||_|j|_||_||_||_||_g|_dSN) r:r7namelist valid_files read_onlykeep_vba data_only keep_linksshared_strings)selffnrNrOrPrQr8r8r9__init__zs  zExcelReader.__init__cCs$|jt}t|}t||_dSrK)r7r*r r(r from_treerE)rSsrcrootr8r8r9 read_manifests zExcelReader.read_manifestc CsH|jt}|dk rD|jdd}|j|}t||_W5QRXdS)Nr)rEr@rPartNamer7openrrR)rSrFZ strings_pathrWr8r8r9 read_stringss  zExcelReader.read_stringscCst|j}t|j|jdd|jd|_|j|jj}g|_ |j |_ |j |_ |jttfk|_|jrttdt|_|jD]}|j||j|q||j r|j|_||_dS)Nr)rQa)rIrErr7rZrQparserparsewb_sheetsrPZ _data_onlyrNZ _read_onlyr;rrtemplaterOrrr vba_archiverMwritestrr*Z_archive)rSZwb_partr`namer8r8r9 read_workbooks   zExcelReader.read_workbookcCs,t|jkr(t|jt}t||j_dSrK) r rMr(r7r*rrVr` properties)rSrWr8r8r9read_propertiess zExcelReader.read_propertiescCst|jkr|jt|j_dSrK)rrMr7r*r`Z loaded_themerSr8r8r9 read_themes zExcelReader.read_themec Cs|j}t|}g}||jkr(t|j|}|j|d}|}W5QRXt|}t |} |j | _ |j | _ |j | |tj} | D]*}t|j|j\} } | D]} | | qqdS)Nr-)targetr"rMr!r7r[r*r(r%rVr`_parentretitleZ _add_sheetr@r' _rel_typer) add_chart)rSsheetrelZ sheet_path rels_pathrelsrWxmlnodecsdrawingschartsimagescr8r8r9read_chartsheets"     zExcelReader.read_chartsheetc Csd}|jD]t\}}|j|jkr&qd|jkr>|||qt|j}t}||jkrdt|j |}|j rt |j |j |j|j}|j|_|j j|qn<|j |j}|j |j }||_t|||j|j}||tD]} |j | j} tt| } | jD]`\} } z| || _ WnFt!k rb|| }t"|t#r^t$%|&|j'|j(YqYnXqq|j j)r|j*r||j*j|_*nd|_*|j+D].}|j |} t| }t,|}|-|q|t.j/}|D]L}t0|j |j\}}|D]}|1||j2q|D]}|3||j2q q|t4j5}|D]D} | j6}|j |} t| }t4|}|jj7|j8|_9|:|q6|j|_qdS)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_sheetsrkrMTyper{r"r r!r7rNr#r`rerRstateZ sheet_stateraappendr[Z create_sheetZ_relsr$rPZbind_allr@rr*rrVr(commentscommentAttributeError isinstancerwarningswarnformatrmZ coordinatercZlegacy_drawingZtablesr&Z add_tabler'rnr)roanchorZ add_imagerZrel_typeZTargetZ pivot_cachesZcacheIdcacheZ add_pivot)rSZcomment_warningrprqrrrswsfhZ ws_parserr-rWZ comment_sheetrefrrztrttablerwrxryZimZ pivot_relZ pivot_pathtreeZpivotr8r8r9read_worksheetssp              zExcelReader.read_worksheetscCs\|||||t|j|j||j |j sX|j dSrK) rYr\rfrhrjrr7r`rr^Z assign_namesrNcloserir8r8r9r*s zExcelReader.readN)__name__ __module__ __qualname____doc__r rUrYr\rfrhrjr{rr*r8r8r8r9rJts HrJTcCst|||||}||jS)aOpen 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. )rJr*r`)r5rNrOrPrQreaderr8r8r9 load_workbook s r)Frzipfilerrrsysrioros.pathr/rZopenpyxl.pivot.tablertestsr ImportErrorZopenpyxl.utils.exceptionsr Zopenpyxl.xml.constantsr r r rrrrrrrrrZ openpyxl.cellrZopenpyxl.comments.comment_sheetrstringsrZworkbookrZopenpyxl.styles.stylesheetrZopenpyxl.packaging.corerZopenpyxl.packaging.manifestrrZopenpyxl.packaging.relationshipr r!r"Zopenpyxl.worksheet._read_onlyr#Zopenpyxl.worksheet._readerr$Zopenpyxl.chartsheetr%Zopenpyxl.worksheet.tabler&Z$openpyxl.drawing.spreadsheet_drawingr'Zopenpyxl.xml.functionsr(rwr)r3r:rIrJrr8r8r8r9sH     8             !-