o b@sddlZddlZddlZddlZddlZddlmZddlmZddlm Z m Z m Z m Z ddl mZddlmZmZmZmZddlmZdd lmZdd lmZdd lmZdd lmZdd lmZddl m!Z!ddl"m#Z#ddl$m%Z%ddl&m'Z'ddl(m)Z)ddl*m+Z+ddl,m-Z-ddl.m/Z/ddl0m1Z1ddl2m3Z3ddl4m5Z5ddl4m6Z6ddl4m7Z7ddl4m8Z8ddl4m9Z9ddl4m:Z:Gdddej;ZdS)"N)warn)datetime)ZipFileZipInfo ZIP_DEFLATED LargeZipFileunpack) int_types num_types str_types force_unicode) 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_zip64rZSheetZChart>%Xautoig)xf_indexZ font_familyr=Z hyperlink num_format)Ksuperr&__init__filenamegetr'r(r)r*r+r,r-r.r/r0r1r2r3r4 allow_zip64 WorksheetMetaworksheet_metaselected fileclosedZ filehandleZ 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_rangesZ custom_colorsdoc_propertiescustom_propertiesrutcnowZ 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)selfrAoptions __class__R/var/www/secure340b-portal/env/lib/python3.10/site-packages/xlsxwriter/workbook.pyr@=s     zWorkbook.__init__cCs|S)z0Return self object to use with "with" statement.ryruryryrz __enter__szWorkbook.__enter__cCs |dS)z-Close workbook when exiting "with" statement.N)close)rutypevalue tracebackryryrz__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)runamerryryrz 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)rurrryryrzadd_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. ZArial )Z font_nameZ font_sizetheme) r3copyr0updaterrRrTrPappend)ru propertiesZformat_properties xf_formatryryrzrss   zWorkbook.add_formatcCs|d}|durtddS|dkrt|}nP|dkr!t|}nG|dkr*t|}n>|dkr3t|}n5|dkr             zWorkbook.add_chartFcCsD|stj|stdt|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)ospathexistsrrrgrerf)rure is_streamryryrzadd_vba_projects   zWorkbook.add_vba_projectc Cs|js>z|Wnty}zt|d}~wty(}ztdd}~wwd|_|jr:|D]}|q3dSdSt 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.) rG_store_workbookIOErrorr$rr%r. worksheetsZ _opt_closer)rue worksheetryryrzr}5s"     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)intrarb)ruwidthheightryryrzset_sizeTs   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)rrrc)rurcryryrz set_tab_ratioks  zWorkbook.set_tab_ratiocCs ||_dS)z Set the document properties such as Title, Author etc. Args: properties: Dictionary of document properties. Returns: Nothing. N)rZ)rurryryrzset_properties~ zWorkbook.set_propertiescCs|dus|durtddS|dur4t|trd}nt|tr"d}nt|tr*d}n t|tr2d}nd}|dkr=|d }|dkrOt|d krOtd t|t|d kr]td t||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()rbooldateZ number_intnumbertextz%Y-%m-%dT%H:%M:%SZr5zbLength 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 isinstancerrr r strftimelenrr[r)rurrZ property_typeryryrzset_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. manualFZauto_except_tables autoNoTableN)rmrnro)rumoderoryryrz set_calc_modes zWorkbook.set_calc_modecCsd}d}|dr|d}td}||}|r8|d}|d}||}|dur7tdt|dSnd}td |tj rHtd |rRtd t|dStd |rbtd t|dStd|sntd|rxtdt|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_indexrrUNICODErXr)rurZformula sheet_index sheetnameZ sheet_partsrryryrz define_namesJ           zWorkbook.define_namecC|jS)z Return a list of the worksheet objects in the workbook. Args: None. Returns: A list of worksheet objects. )rLr{ryryrzrs 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. )rOrBrurryryrzget_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. )rtr{ryryrzget_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)rCr{ryryrzr68rzWorkbook.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)rgrryryrz set_vba_nameEs  zWorkbook.set_vba_namecCr)z Set the Excel "Read-only recommended" option when saving a file. Args: None. Returns: Nothing. rN)rqr{ryryrzread_only_recommendedVrzWorkbook.read_only_recommendedcCsf|||||||||| | d| dS)Nworkbook) _prepare_format_propertiesZ_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_tagZ _xml_closer{ryryrz_assemble_xml_fileis  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)0rrArrCr _get_packagerrrrE activesheetrLrFhiddenindexactiverergr_prepare_sst_string_data _prepare_vml_prepare_defined_names_prepare_drawings_add_chart_data_prepare_tables_prepare_metadataZ _add_workbookZ _set_tmpdirr'Z_set_in_memoryr/Z_create_package enumeraterr compress_typewritestrgetvalueencodetimemktimerutimewriteremoverrangerr})ruZ xlsx_filerpackagersheetZ xml_filesZfile_idZ file_dataZ os_filenameZ xml_filenameZ is_binaryzipinfo timestampiryryrzrsr                 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)NrrrdrEr.r'r(r)r*r+r,r-rtr0r1r4r2)rrrL_check_sheetnamerrrdrEr.r'r(r)r*r+r,r-rtr0r1r4r2 _initializerrO)rurrrrZ init_dataryryrzrsX         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.)rrrKrJrIstrrHrr searchrendswithrlowerrr!)rurZ is_chartsheetZ invalid_charrryryrzrsF    zWorkbook._check_sheetnamecCs,|||||dSN)_prepare_formats_prepare_fonts_prepare_num_formats_prepare_borders_prepare_fillsr{ryryrzrGs  z#Workbook._prepare_format_propertiescCsg}g}|jD]}|jdur|||jdur||qdgt||_dgt||_|D] }|j}||j|<q0|D] }|j}||j|<q=dSr)rPr=rZ dxf_indexrrQrS)rurQrSrrZ dxf_formatryryrzrYs"       zWorkbook._prepare_formatscCs6t|j}|d=|jdur|d=|D]}|qdS)Nr )listrPr-Z _get_xf_index)rurPrryryrz_set_default_xf_indicesus   z Workbook._set_default_xf_indicescCsi}d}|jD]!}|}||vr|||_d|_q|||<||_d|_|d7}q||_|jD]}|js@|js@|js@|j s@|j rCd|_ q/dSNrr ) rQZ _get_font_keyZ font_indexZhas_fontrVrSZ font_colorbolditalic underlineZfont_strikeoutZ has_dxf_font)ruZfontsrrkeyryryrzrs*    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 0ZGeneral) rQrSr>rr rZnum_format_indexr=rW)ruZ num_formatsrrWrr>ryryrzrs4   zWorkbook._prepare_num_formatscCsi}d}|jD]!}|}||vr|||_d|_q|||<||_d|_|d7}q||_td}|jD]}|}||rBd|_ q4dS)Nrr z[^0:]) rQZ_get_border_keyZ border_index has_borderrjrrrSrZhas_dxf_border)ruZbordersrrrrryryrzrs&      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) rSpatternZbg_colorZfg_colorZ has_dxf_fillZ dxf_bg_colorZ dxf_fg_colorrQZ _get_fill_keyZ fill_indexZhas_fillrk)ruZfillsrrtmprryryrzr sJ     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) rXrZautofilter_arearrZprint_area_rangeZrepeat_col_rangeZrepeat_row_range_sort_defined_names_extract_named_rangesrY)rurXrr sheet_rangeryryrzr's>    zWorkbook._prepare_defined_namescCsn|D] }|\}}}}|dd}|d}||d|q|jtdd|D]}|q.|S)N_xlnm.rrz::r)replacerrrsortoperator itemgetterpop)runames name_list defined_name_rHryryrzrPs  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)rryryrzsz,Workbook._prepare_drawings..r)rrrMriZshapesZ header_imagesZ footer_imagesZbackground_imageZbackground_bytes_get_image_propertiesrZ_prepare_backgroundrZ_prepare_imageZ_prepare_chartZ_prepare_shapeZ_prepare_header_imagedrawingrNr'rsortedrl)ruZ chart_ref_idZ image_ref_idZref_idZ drawing_idZ image_idsZheader_image_idsZbackground_idsrZ chart_countZ image_countZ shape_countZheader_image_countZfooter_image_countZhas_backgroundZ has_drawingrA image_data image_typerrrx_dpiy_dpidigestrpositionr*rryryrzrks                              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)NrrrbZ3sr r>HrZ2sIr br g F%u?Tr7rr )rurJoffset data_length end_markerrrr.r/Z marker_ihdrZ marker_physZ marker_iendlengthmarker x_density y_densityunitsryryrzrCBs:   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)NrFrrr3riiiii irN rLrMr gRQ@iTr8rP)rurJrQrRrSrrr.r/rUrTrXrVrWryryrzrDosN    (zWorkbook._process_jpgcCsBd}d}td|ddd}td|ddd}d||||fS)Nrs`                         J