o ȷe4@sddlZddlZddlZddlZddlZddlmZddlmZddlm Z ddl m Z ddl m Z ddlmZmZmZmZdd lmZdd lmZdd lmZdd lmZdd lmZddlmZddlm Z ddl!m"Z"ddl#m$Z$ddl%m&Z&ddl'm(Z(ddl)m*Z*ddl+m,Z,ddl-m.Z.ddl/m0Z0ddl1m2Z2ddl3m4Z4ddl3m5Z5ddl3m6Z6ddl3m7Z7ddl3m8Z8ddl3m9Z9Gdd d ej:Z;Gd!d"d"e<Z=dS)#N)datetime)Decimal)Fractionunpack)warn)ZipFileZipInfo ZIP_DEFLATED LargeZipFile) xmlwriter) Worksheet) Chartsheet)SharedStringTable)FormatPackager)xl_cell_to_rowcol) ChartArea)ChartBar) ChartColumn) ChartDoughnut) ChartLine)ChartPie) ChartRadar) ChartScatter) ChartStock)InvalidWorksheetName)DuplicateWorksheetName)UndefinedImageSize)UnsupportedImageFormat)FileCreateError) FileSizeErrorcs&eZdZdZeZeZdfdd ZddZ ddZ dd d Z dd d Z dd dZ ddZdddZddZddZdddZddZdddZdddZd d!Zd"d#Zd$d%Zd&d'Zd(d)Zdd*d+Zd,d-Zd.d/Zd0d1Zdd2d3Zdd4d5Z d6d7Z!d8d9Z"d:d;Z#dd?Z%d@dAZ&dBdCZ'dDdEZ(dFdGZ)dHdIZ*dJdKZ+dLdMZ,dNdOZ-dPdQZ.dRdSZ/dTdUZ0dVdWZ1dXdYZ2dZd[Z3d\d]Z4d^d_Z5d`daZ6dbdcZ7dddeZ8dfdgZ9dhdiZ:djdkZ;dldmZdrdsZ?dtduZ@dvdwZAdxdyZBdzd{ZCd|d}ZDd~dZEZFS)Workbookz= A class for writing the Excel XLSX Workbook file. Ncs|duri}tt|||_|dd|_|dd|_|dd|_|dd|_|dd|_ |d d|_ |d d|_ |d d|_ |d d|_ |d d|_|dd|_|dd|_|di|_|dd|_|jdkrzd|_|drd|_nd|_t|_d|_d|_d|_d|_d|_d|_d|_d|_g|_g|_g|_ i|_!g|_"g|_#i|_$g|_%i|_&g|_'d|_(d|_)g|_*g|_+g|_,i|_-g|_.t/0|_1d|_2d|_3d|_4d|_5d|_6d|_7d|_8t9|_:d|_;d|_g|_?d|_@d|_Ad|_Bd|_Cd|_Dd|_Ed|_Fd|_Gd|_H|j r$d|_ |jr1|Idddn|Id di|Id!di|_J|j durQ|Id"|j i|_ dSdS)#z Constructor. Ntmpdir date_1904Fstrings_to_numbersstrings_to_formulasTstrings_to_urlsnan_inf_to_errorsdefault_date_formatconstant_memory in_memoryexcel2003_styleremove_timezoneuse_future_functionsdefault_format_propertiesmax_url_lengthi use_zip64rSheetChart>%Xautoig)xf_index font_familyr= hyperlink num_format)Ksuperr$__init__filenamegetr%r&r'r(r)r*r+r,r-r.r/r0r1r2 allow_zip64 WorksheetMetaworksheet_metaselected fileclosed filehandle internal_fh sheet_name chart_namesheetname_countchartname_countworksheets_objschartsdrawings sheetnamesformats xf_formatsxf_format_indices dxf_formatsdxf_format_indicespalette font_countnum_format_count defined_names named_ranges custom_colorsdoc_propertiescustom_propertiesrutcnow createtime num_vml_filesnum_comment_filesx_windowy_window window_width window_height tab_ratior str_table vba_project vba_is_stream vba_codename image_typesimages border_count fill_count drawing_count calc_mode calc_on_loadcalc_id has_comments read_only has_metadata add_formatdefault_url_format)selfrCoptions __class__J/var/www/ideatree/venv/lib/python3.10/site-packages/xlsxwriter/workbook.pyrB?s     zWorkbook.__init__cCs|S)z0Return self object to use with "with" statement.rr{rrr __enter__szWorkbook.__enter__cCs |dS)z-Close workbook when exiting "with" statement.N)close)r{typevalue tracebackrrr__exit__s zWorkbook.__exit__cC|dur|j}|j||dS)z Add a new worksheet to the Excel workbook. Args: name: The worksheet name. Defaults to 'Sheet1', etc. Returns: Reference to a worksheet object. Nworksheet_class)r _add_sheet)r{namerrrr add_worksheet zWorkbook.add_worksheetcCr)z Add a new chartsheet to the Excel workbook. Args: name: The chartsheet name. Defaults to 'Sheet1', etc. Returns: Reference to a chartsheet object. Nr)chartsheet_classr)r{rrrrradd_chartsheetrzWorkbook.add_chartsheetcCsJ|j}|jrdddd}|r||t||j|j}|j||S)z Add a new Format to the Excel Workbook. Args: properties: The format properties. Returns: Reference to a Format object. Arial ) font_name font_sizetheme) r1copyr.updaterrVrXrTappend)r{ propertiesformat_properties xf_formatrrrrys   zWorkbook.add_formatcCs|d}|durtddS|dkrt|}nP|dkr!t|}nG|dkr*t|}n>|dkr3t|}n5|dkr             zWorkbook.add_chartFcCs@|stj|std|dS|jdurd|_||_||_dS)z Add a vbaProject binary to the Excel workbook. Args: vba_project: The vbaProject binary file name. is_stream: vba_project is an in memory byte stream. Returns: Nothing. z'VBA project binary file '%s' not found.rN ThisWorkbook)ospathexistsrrmrkrl)r{rk is_streamrrradd_vba_project s   zWorkbook.add_vba_projectc Cs~|js9z|Wnty}zt|d}~wty#tdwd|_|jr5|D]}|q.dSdSt ddS)z Call finalization code and close file. Args: None. Returns: Nothing. NzBFilesize would require ZIP64 extensions. Use workbook.use_zip64().Tz'Calling close() on already closed file.) rI_store_workbookIOErrorr"r r#r, worksheets _opt_closer)r{e worksheetrrrr6s       zWorkbook.closecCsB|r t|dd|_nd|_|rt|dd|_dSd|_dS)z Set the size of a workbook window. Args: width: Width of the window in pixels. height: Height of the window in pixels. Returns: Nothing. i`r9r:N)intrgrh)r{widthheightrrrset_sizeUs   zWorkbook.set_sizecCs>|durdS|dks|dkrtd|dSt|d|_dS)z Set the ratio between worksheet tabs and the horizontal slider. Args: tab_ratio: The tab ratio, 0 <= tab_ratio <= 100 Returns: Nothing. Nrdz-Tab ratio '%d' outside: 0 <= tab_ratio <= 100r)rrri)r{rirrr set_tab_ratiols  zWorkbook.set_tab_ratiocCs ||_dS)z Set the document properties such as Title, Author etc. Args: properties: Dictionary of document properties. Returns: Nothing. N)r_)r{rrrrset_properties zWorkbook.set_propertiescCs|dus|durtddS|dur8t|trd}nt|tr"d}nt|tr*d}nt|ttttfr6d}nd}|dkrA|d }|dkrQt |d krQtd |t |d kr]td ||j |||fdS) a' Set a custom document property. Args: name: The name of the custom property. value: The value of the custom property. property_type: The type of the custom property. Optional. Returns: Nothing. NzGThe name and value parameters must be non-None in set_custom_property()rbooldate number_intnumbertextz%Y-%m-%dT%H:%M:%SZr3zbLength of 'value' parameter exceeds Excel's limit of 255 characters in set_custom_property(): '%s'zaLength of 'name' parameter exceeds Excel's limit of 255 characters in set_custom_property(): '%s') r isinstancerrrfloatrrstrftimelenr`r)r{rr property_typerrrset_custom_propertys0      zWorkbook.set_custom_propertycCs6||_|dkr d|_n|dkrd|_|r||_dSdS)z Set the Excel calculation mode for the workbook. Args: mode: String containing one of: * manual * auto_except_tables * auto Returns: Nothing. manualFauto_except_tables autoNoTableN)rsrtru)r{moderurrr set_calc_modes zWorkbook.set_calc_modecCsd}d}|dr|d}td}||}|r6|d}|d}||}|dur5td|dSnd}td |tjrFtd |rNtd |dStd |r\td |dStd|shtd|rptd|dS|j |||dgdS)z Create a defined name in the workbook. Args: name: The defined name. formula: The cell or range that the defined name refers to. Returns: Nothing. N=z ^(.*)!(.*)$r z)Unknown sheet name '%s' in defined_name()rz^[\w\\][\w\\.]*$z^\dz0Invalid Excel characters in defined_name(): '%s'z"^[a-zA-Z][a-zA-Z]?[a-dA-D]?[0-9]+$z3Name looks like a cell name in defined_name(): '%s'z^[rcRC]$z^[rcRC]\d+[rcRC]\d+$z6Invalid name '%s' like a RC cell ref in defined_name()F) startswithlstriprecompilematchgroup_get_sheet_indexrUNICODEr\r)r{rformula sheet_index sheetname sheet_partsrrrr define_names>              zWorkbook.define_namecC|jS)z Return a list of the worksheet objects in the workbook. Args: None. Returns: A list of worksheet objects. )rPrrrrr s zWorkbook.worksheetscCs |j|S)z Return a worksheet object in the workbook using the sheetname. Args: name: The name of the worksheet. Returns: A worksheet object or None. )rSrDr{rrrrget_worksheet_by_names zWorkbook.get_worksheet_by_namecCr)a Get the default url format used when a user defined format isn't specified with write_url(). The format is the hyperlink style defined by Excel for the default theme. Args: None. Returns: A format object. )rzrrrrget_default_url_format%s zWorkbook.get_default_url_formatcC d|_dS)z Allow ZIP64 extensions when writing xlsx file zip container. Args: None. Returns: Nothing. TN)rErrrrr44rzWorkbook.use_zip64cCs|dur ||_dSd|_dS)z Set the VBA name for the workbook. By default the workbook is referred to as ThisWorkbook in VBA. Args: name: The VBA name for the workbook. Returns: Nothing. Nr)rmrrrr set_vba_nameAs  zWorkbook.set_vba_namecCr)z Set the Excel "Read-only recommended" option when saving a file. Args: None. Returns: Nothing. rN)rwrrrrread_only_recommendedRrzWorkbook.read_only_recommendedcCsf|||||||||| | d| dS)Nworkbook) _prepare_format_properties_xml_declaration_write_workbook_write_file_version_write_file_sharing_write_workbook_pr_write_book_views _write_sheets_write_defined_names_write_calc_pr _xml_end_tag _xml_closerrrr_assemble_xml_filees  zWorkbook._assemble_xml_filec Csz t|jdt|jd}Wnty}z|d}~ww|}|s'||jj dkr9d|j d_ d|j d_ |D] }|j |jj krId|_q=|jr]|D] }|jdur\|qQ|||||||||||j||j|}d}t|D]i\}}|\}} } |jrt | d} |j!| _"| r|#| |$q|#| |$%dqt&'d} t()|| | fz |*|| t(+|Wqt,y}zt-|t.|dD] } t(+|| dq|d}~ww|/dS)Nw) compression allowZip64rr )r r rrrzutf-8) rr rrrrrr)0rrCr rEr _get_packagerrrrG activesheetrPrHhiddenindexactiverkrmr_prepare_sst_string_data _prepare_vml_prepare_defined_names_prepare_drawings_add_chart_data_prepare_tables_prepare_metadata _add_workbook _set_tmpdirr%_set_in_memoryr-_create_package enumerater r compress_typewritestrgetvalueencodetimemktimerutimewriteremover rangerr)r{ xlsx_filerpackagersheet xml_filesfile_id file_data os_filename xml_filename is_binaryzipinfo timestampirrrrsr                 zWorkbook._store_workbookcCs|r|}n|}t|j}||t|t}id|d|d|jd|jd|jd|j d|j d|j d |j d |j d |jd |jd |jd|jd|jd|jd|j}|||j|||j|<|S)NrrrjrGr,r%r&r'r(r)r*r+rzr.r/r2r0)rrrP_check_sheetnamerrrjrGr,r%r&r'r(r)r*r+rzr.r/r2r0 _initializerrS)r{rrrr init_datarrrrsX         zWorkbook._add_sheetcCstd}|r|jd7_n|jd7_|dus|dkr1|r)|jt|j}n|jt|j}t|dkr=td|| |rHtd|| dsR| drXtd|| D]}| |j krmtd |q\|S) Nz [\[\]:*?/\\]r rrz.Excel worksheet name '%s' must be <= 31 chars.z4Invalid Excel character '[]:*?/\' in sheetname '%s'.'z7Sheet name cannot start or end with an apostrophe "%s".z5Sheetname '%s', with case ignored, is already in use.)rrrOrNrMstrrLrrsearchrendswithrlowerrr)r{r is_chartsheet invalid_charrrrrr sF    zWorkbook._check_sheetnamecCs,|||||dSN)_prepare_formats_prepare_fonts_prepare_num_formats_prepare_borders_prepare_fillsrrrrrCs  z#Workbook._prepare_format_propertiescCsg}g}|jD]}|jdur|||jdur||qdgt||_dgt||_|D] }|j}||j|<q0|D] }|j}||j|<q=dSr*)rTr=r dxf_indexrrUrW)r{rUrWrr dxf_formatrrrr+Us"       zWorkbook._prepare_formatscCs6t|j}|d=|jdur|d=|D]}|qdS)Nr )listrTr+ _get_xf_index)r{rTrrrr_set_default_xf_indicesqs   z Workbook._set_default_xf_indicescCsi}d}|jD]!}|}||vr|||_d|_q|||<||_d|_|d7}q||_|jD]}|js@|js@|js@|j s@|j rCd|_ q/dSNrr ) rU _get_font_key font_indexhas_fontrZrW font_colorbolditalic underlinefont_strikeout has_dxf_font)r{fontsrrkeyrrrr,s*    zWorkbook._prepare_fontscCsi}d}d}|j|jD]D}|j}t|ts$t|}|dkr d}||_q |dkr,d|_q |dkr4d|_q ||vr>|||_q |||<||_|d7}|jrP|d7}q ||_dS)Nrr 0General) rUrWr@rr$rnum_format_indexr=r[)r{ num_formatsrr[rr@rrrr-s4   zWorkbook._prepare_num_formatscCsi}d}|jD]!}|}||vr|||_d|_q|||<||_d|_|d7}q||_td}|jD]}|}||rBd|_ q4dS)Nrr z[^0:]) rU_get_border_key border_index has_borderrprrrWr%has_dxf_border)r{bordersrrr@rHrrrr.s&      zWorkbook._prepare_borderscCs.i}d}d|d<d|d<|jD]}|js|js|jr%d|_|j|_|j|_q|jD]h}|jdkrD|jdkrD|jdkrD|j}|j|_||_|jdkr]|jdkr]|jdkr]|j|_d|_d|_|jdkrr|jdkrr|jdkrrd|_d|_|}||vr|||_ d|_ q)|||<||_ d|_ |d7}q)||_ dS)Nrrz0:0:0r z17:0:0) rWpatternbg_colorfg_color has_dxf_fill dxf_bg_color dxf_fg_colorrU _get_fill_key fill_indexhas_fillrq)r{fillsrrtmpr@rrrr/sD        zWorkbook._prepare_fillscCs|j}|D]O}|jrd}|j}|d|j||g|jr-d}|j}|d|j||g|js3|jrVd}d}|jrF|jrF|jd|j}n|j|j}|d|j||gq||}||_| ||_ dS)Nr _xlnm._FilterDatabaserz_xlnm.Print_Arear,z_xlnm.Print_Titles) r\rautofilter_arearrprint_area_rangerepeat_col_rangerepeat_row_range_sort_defined_names_extract_named_rangesr])r{r\rr sheet_rangerrrr#s>    zWorkbook._prepare_defined_namescCsn|D] }|\}}}}|dd}|d}||d|q|jtdd|D]}|q.|S)N_xlnm.rr#z::r@)replacer'rrsortoperator itemgetterpop)r{names name_list defined_name_rLrrrr\Ls  zWorkbook._sort_defined_namescCsd}d}d}d}i}i}i}|D]}t|j} t|j} t|j} t|j} t|j} |j}d}| s@| s@| s@| s@| s@|s@q| sF| sF| rL|d7}d}|jr|jrXd}|j}n|j}d}| ||\}}}}}}}||vrs||}n|d7}|}|||<|j |||g| ||t | D]G}|j|d}|j|d}| ||\}}}}}}}||vr||}n|d7}|}|||<|j |||g| |||||||||| qt | D] }|d7}||||qt | D]}|||qt | D]N}|j|d}|j|d}|j|d}| ||\}}}}}}}||vr)||}n|d7}|}|||<|j |||g|||||||||| qt | D]O}|j|d}|j|d}|j|d}| ||\}}}}}}}||vr|||}n|d7}|}|||<|j |||g|||||||||| qN|r|j}|j |q|jddD]}|jdkr|j|qt|jd d d |_||_dS) NrFr TrrrrcSrr*)id)rrrrsz,Workbook._prepare_drawings..ra)rrrQroshapes header_images footer_imagesbackground_imagebackground_bytes_get_image_propertiesr_prepare_backgroundr_prepare_image_prepare_chart_prepare_shape_prepare_header_imagedrawingrRrkrsortedrr)r{ chart_ref_id image_ref_idref_id drawing_id image_idsheader_image_idsbackground_idsr chart_count image_count shape_countheader_image_countfooter_image_counthas_background has_drawingrC image_data image_typerrrx_dpiy_dpidigestrpositionrxrrrrrgs                              zWorkbook._prepare_drawingscCs,d}d}d}d}|st|d}|}n|}t|} tj|} t d|ddd} t d|ddd} t d |ddd} t d |ddd}t d |d d d}t d |ddd}d}d}d}d}| |krd|j d<| |\}}}}}nl| dkrd|j d<| |\}}}}}nX| |krd|j d<| |\}}}nF|dkrd|j d<||\}}}}}n2|dkr||krd|j d<||\}}}}}n||krd|j d<||\}}}}}ntd||r|std||s||dkrd}|dkr d}|||| ||| fS)Nrrrb3sr r`>Hr2sIr`rsIHDR spHYsbr g F%u?sIENDTrrr)r{roffset data_length end_markerrrrrlengthmarker x_density y_densityunitsrrrr>s4   zWorkbook._process_pngcCsd}t|}d}d}d}d}d}|s||krtd||d|dd} td||d|dd} | d@dkrd| d krd| d krd| d krdtd||d |d d}td||d |dd}| dkrtd||d|dd} td||d|dd} td||d|dd} | dkr| }| }| dkr| d}| d}|dkrd}|dkrd}| dkrd}q|| d}|s||ksd||||fS)NrFrrrr`iiiii ir rrr gRQ@iTrr)r{rrrrrrrrrrrrrrrrrfsH    (zWorkbook._process_jpgcCsBd}d}td|ddd}td|ddd}d||||fS)Nrrrdrrrrdecimalr fractionsrstructrwarningsrzipfilerr r r rr rr chartsheetr sharedstringsrformatrrrutilityr chart_arear chart_barr chart_columnrchart_doughnutr chart_liner chart_pier chart_radarr chart_scatterr chart_stockr exceptionsrrr r!r"r# XMLwriterr$objectrFrrrrsb                            >