o gk@sdZddlmZddlmZddlmZddlmZddl m Z ddl m Z m Z mZmZddlmZmZdd lmZdd lmZdd lmZdd lmZdd lmZddlmZddlm Z m!Z!m"Z"ddl#m$Z$m%Z%m&Z&m'Z'ddl(m)Z)ddl*m+Z+ddl,m-Z-m.Z.m/Z/ddl0m1Z1m2Z2ddl3m4Z4ddl5m6Z6ddl7m8Z8m9Z9ddl:m;Z;ddlm?Z?ddl@mAZAmBZBmCZCmDZDGdddeZEdd ZFd!S)"z.Worksheet is the 2nd-level container in Excel.)chain) itemgetter) isgenerator)warn) deprecated)column_index_from_stringget_column_letterrange_boundariescoordinate_to_tuple)Cell MergedCell)ConditionalFormattingList)RelationshipList)_WorkbookChild)DefinedNameDict) Translator)DataValidationList)PrintPageSetup PageMargins PrintOptions)ColumnDimension RowDimensionDimensionHolderSheetFormatProperties)SheetProtection) AutoFilter)Pane Selection SheetViewList)MultiCellRange CellRange)MergedCellRange)WorksheetProperties)RowBreakColBreak) ScenarioList) TableList) ArrayFormula) PrintTitlesColRangeRowRange PrintAreac@seZdZdZdZdZdZdZdZdZ dZ d Z d Z d Z d Zd ZdZdZdZdZdZdZdZdZdZdZdddZddZeddZedd Zed!d"Zed#d$Z ed%d&Z!ed'd(Z"e"j#dd)d(Z"dd*d+Z$d,d-Z%d.d/Z&d0d1Z'd2d3Z(d4d5Z)d6d7Z*ed8d9Z+ed:d;Z,edd?Z.d@dAZ/edBdCZ0ddEdFZ1ddGdHZ2edIdJZ3edKdLZ4ddMdNZ5ddOdPZ6edQdRZ7edSdTZ8dUdVZ9dWdXZ:ddYdZZ;dd[d\ZdadbZ?ddcddZ@dedfZAeeBdgdhdiZCddjdkZDdldmZEddodpZFddqdrZGddsdtZHddudvZIddwdxZJddydzZKdd{d|ZLd}d~ZMddZNddZOeddZPePj#ddZPeddZQeQj#ddZQeddZReddZSeSj#ddZSdS) WorksheetzRepresents a worksheet. Do not create worksheets yourself, use :func:`openpyxl.workbook.Workbook.create_sheet` instead worksheetz/xl/worksheets/sheet{0}.xmlzIapplication/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xmlrrvisiblehidden veryHidden1234567891011portrait landscapeNcCst||||dSN)r__init___setup)selfparenttitlerFV/var/www/html/api-tag/env/lib/python3.10/site-packages/openpyxl/worksheet/worksheet.pyrAis zWorksheet.__init__cCst||jd|_t||jd|_t|_t|_i|_ g|_ g|_ t |_ d|_g|_t|_t|_g|_t|_g|_d|_t|d|_t|_d|_d|_t|_ t!|_"t#|_$t%|_&t'|_(d|_)t*|_+t,|_-d|_.t/|_0t1|_2t3|_4dS)N)r.default_factoryr0)r.r)5r_add_rowrow_dimensions _add_columncolumn_dimensionsr$ row_breaksr% col_breaks_cells_charts_imagesr_rels_drawing _commentsr merged_cellsr'_tables_pivotsrdata_validations _hyperlinks sheet_stater page_setupr print_options _print_rows _print_colsr, _print_arear page_marginsrviewsr protectionr defined_names _current_rowr auto_filterr conditional_formattinglegacy_drawingr#sheet_propertiesr sheet_formatr& scenariosrCrFrFrGrBmsH  zWorksheet._setupcC|jjSr@)raactiverkrFrFrG sheet_viewzWorksheet.sheet_viewcC|jjdjSNr)rn selectionsqrefrkrFrFrG selected_cellzWorksheet.selected_cellcCrprq)rnrr activeCellrkrFrFrG active_cellruzWorksheet.active_cellcCs<i}|jD]}|jdkrt|jtr|jj||j<q|S)zHReturns a dictionary of cells with array formulae and the cells in arrayf)rOvalues data_type isinstancevaluer(ref coordinate)rCresultcrFrFrGarray_formulaes  zWorksheet.array_formulaecCrlr@)rn showGridLinesrkrFrFrGshow_gridlinesrozWorksheet.show_gridlinescCs|jjdur |jjjSdSr@)rnpane topLeftCellrkrFrFrG freeze_paness  zWorksheet.freeze_panescCs t|tr|j}|dkrd}|sd|j_dSt|\}}|j}t|ddd|_d|jd_|dkr7|d|j_|dkrY|d|j_ d|j_ d|jd_|dkrYd|jd_d|j_ |dkr|dkrt |j}| dt dddd | dt dddd ||_dSdSdS) NA1topRightfrozen)r activePanestaterr bottomLeft bottomRight)rrvrs)r{r r~rnrr rrrxSplitySplitrlistinsertr)rCrrowcolumnviewselrFrFrGrs:         cCs6|dks|dkr td|||}|dur||_|S)a Returns a cell object based on the given coordinates. Usage: cell(row=15, column=1, value=5) Calling `cell` creates cells in memory when they are first accessed. :param row: row index of the cell (e.g. 4) :type row: int :param column: column index of the cell (e.g. 3) :type column: int :param value: value of the cell (e.g. 5) :type value: numeric or time or string or bool or none :rtype: openpyxl.cell.cell.Cell rz'Row or column values must be at least 1N) ValueError _get_cellr|)rCrrr|cellrFrFrGrs  zWorksheet.cellcCsXd|kr dksntd|||f}||jvr't|||d}|||j|S)z Internal method for getting a cell from a worksheet. Will create a new cell if one doesn't already exist. rizCRow numbers must be between 1 and 1048576. Row number supplied was rr)rrOr _add_cell)rCrrr~rrFrFrGrs   zWorksheet._get_cellcCs,|j}|j}t||j|_||j||f<dS)z: Internal method for adding cell objects. N)col_idxrmaxrdrO)rCrrrrFrFrGr szWorksheet._add_cellcCst|trt|j|jgstd|d|j|j}t|tr%t|}t |\}}}}t ||||gsjz'Worksheet.min_column..r)rCrrFrFrG min_columnbzWorksheet.min_columncCr)zOThe maximum column index containing data (1-based) :type: int rcsrrrFrrFrFrGrvrz'Worksheet.max_column..r)rCrrFrFrGrnrzWorksheet.max_columnc Csz|jr,t}t}|jD]\}}||||q t|}t|}t|}t|}ndSt||dt||S)zoReturn the minimum bounding range for all cells containing data (ex. 'A1:M24') :rtype: string zA1:A1r)rOsetaddrrr) rCrrrcolrrrrrFrFrGcalculate_dimensionzs   zWorksheet.calculate_dimensioncCr)z1Returns the result of :func:`calculate_dimension`)rrkrFrFrG dimensionszWorksheet.dimensionsFcCsX|jdkrt||||gstdS|pd}|pd}|p|j}|p"|j}||||||S)a Produces cells from the worksheet, by row. Specify the iteration range using indices of rows and columns. If no indices are specified the range starts at A1. If no cells are in the worksheet an empty tuple will be returned. :param min_col: smallest column index (1-based index) :type min_col: int :param min_row: smallest row index (1-based index) :type min_row: int :param max_col: largest column index (1-based index) :type max_col: int :param max_row: largest row index (1-based index) :type max_row: int :param values_only: whether only cell values should be returned :type values_only: bool :rtype: generator rrFr)rdriterrr _cells_by_row)rCrrrr values_onlyrFrFrGrs  zWorksheet.iter_rowsc#s\t||dD]#fddt||dD}|r&tdd|DVqt|VqdS)Nrc3s|] }j|dVqdSrNr)rrrrCrFrGrsz*Worksheet._cells_by_row..cs|]}|jVqdSr@rrrrFrFrGrrangerrCrrrrrcellsrFrrGrs zWorksheet._cells_by_rowcCr)zfProduces all cells in the worksheet, by row (see :func:`iter_rows`) :type: generator rrkrFrFrGrszWorksheet.rowsccs|jddD]}|VqdS)zTProduces all cell values in the worksheet, by row :type: generator T)rNr)rCrrFrFrGryszWorksheet.valuescCsX|jdkrt||||gstdS|pd}|pd}|p|j}|p"|j}||||||S)a Produces cells from the worksheet, by column. Specify the iteration range using indices of rows and columns. If no indices are specified the range starts at A1. If no cells are in the worksheet an empty tuple will be returned. :param min_col: smallest column index (1-based index) :type min_col: int :param min_row: smallest row index (1-based index) :type min_row: int :param max_col: largest column index (1-based index) :type max_col: int :param max_row: largest row index (1-based index) :type max_row: int :param values_only: whether only cell values should be returned :type values_only: bool :rtype: generator rrFr)rdrrrr _cells_by_col)rCrrrrrrFrFrGrs  zWorksheet.iter_colsc#s\t||dD]#fddt||dD}|r&tdd|DVqt|VqdS)z% Get cells by column rc3s|] }j|dVqdSrr)rrrrCrFrGrsz*Worksheet._cells_by_col..csrr@rrrFrFrGrrNrrrFrrGrs   zWorksheet._cells_by_colcCr)zGProduces all cells in the worksheet, by column (see :func:`iter_cols`))rrkrFrFrGcolumns rzWorksheet.columnscCsdd|jDS)zK Return a list of column ranges where more than one column cSs$g|]}|jr|j|jkr|jqSrF)rrr)rcdrFrFrG s$z+Worksheet.column_groups..)rLryrkrFrFrG column_groupsszWorksheet.column_groupscCs||j_||j_dS)zSet printer settings N)r[ paperSize orientation)rC paper_sizerrFrFrGset_printer_settingss zWorksheet.set_printer_settingscCs|j|dS)z Add a data-validation object to the sheet. The data-validation object defines the type of data-validation to be applied and the cell or range of cells it should apply to. N)rXappend)rCdata_validationrFrFrGadd_data_validation szWorksheet.add_data_validationcC|dur||_|j|dS)zd Add a chart to the sheet Optionally provide a cell for the top-left anchor N)anchorrPr)rCchartrrFrFrG add_chart(zWorksheet.add_chartcCr)zf Add an image to the sheet. Optionally provide a cell for the top-left anchor N)rrQr)rCimgrrFrFrG add_image2rzWorksheet.add_imagecCs@|j|jrtd|jt|dstd|j|dS)zr Check for duplicate name in definedNames and other worksheet tables before adding table. z"Table with name {0} already existsrz6In write-only mode you must add table columns manuallyN) rD_duplicate_namenamerrhasattrrrVr)rCtablerFrFrG add_table<s  zWorksheet.add_tablecCs|jSr@)rVrkrFrFrGtablesIszWorksheet.tablescCs|j|dSr@)rWr)rCpivotrFrFrG add_pivotNszWorksheet.add_pivotcCsD|durt|||||d}|j}t||}|j|||dS)z@ Set merge on a cell range. Range is a cell range (e.g. A1:E1) N range_stringrrrr)r!coordr"rUr_clean_merge_range)rCr start_row start_columnend_row end_columncrmcrrFrFrG merge_cellsRs  zWorksheet.merge_cellscCs>|j}t||D]\}}t||||j||f<q |dS)z Remove all but the top left-cell from a range of merged cells and recreate the lost border information. Borders are then applied N)rnextr rOr)rCrrrrrFrFrGr]s   zWorksheet._clean_merge_rangezUse ws.merged_cells.rangescCs|jjddS)zReturn a copy of cell rangesN)rUrangesrkrFrFrGmerged_cell_rangesjszWorksheet.merged_cell_rangesc Csft|||||d}|j|jvrtd|j|j||j}t||D] \}} |j|| f=q&dS)zC Remove merge on a cell range. Range is a cell range (e.g. A1:E1) rzCell range {0} is not mergedN) r!rrUrrremoverrrO) rCrrrrrrrrrrFrFrG unmerge_cellsqs   zWorksheet.unmerge_cellscCs|jd}t|tttfst|rHt|dD]0\}}t|tr7|}|jr-|j|kr-t d||_||_ ||_ nt||||d}||j ||f<qn,t|t ro|D]\}}t|tr^t|}t||||d}||j ||f<qQn||||_dS)aAppends a group of values at the bottom of the current sheet. * If it's a list: all values are added in order, starting from the first column * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters) :param iterable: list, range or generator, or dict containing values to append :type iterable: list|tuple|range|generator or dict Usage: * append(['This is A1', 'This is B1', 'This is C1']) * **or** append({'A' : 'This is A1', 'C' : 'This is C1'}) * **or** append({1 : 'This is A1', 3 : 'This is C1'}) :raise: TypeError when iterable is neither a list/tuple nor a dict rz,Cells cannot be copied from other worksheets)rrr|N)rdr{rrrr enumerater rDrrrrOdictitemsrr _invalid_row)rCiterablerow_idxrcontentrrFrFrGrs0      zWorksheet.appendrc Cs|dk}d}d}|dkr|j|d}|}d} n |j|d}|}d} t|}t|jt| |dD]\} } |r:| |kr:q/|rA| |krAq/|| | ||q/dS)zB Move either rows or columns around by the offset rr)r)rr)rreverseN)rrrsortedrOr _move_cell) rCrroffset row_or_colr  row_offset col_offsetrrrrrFrFrG _move_cellss$    zWorksheet._move_cellscCs|j||dd|j|_dS)z4 Insert row or rows before row==idx rrrrN)rrrdrCidxamountrFrFrG insert_rowss zWorksheet.insert_rowscCs|j||dddS)z: Insert column or columns before col==idx rrrrN)rrrFrFrG insert_colsszWorksheet.insert_colscCst|||j}|j||| dd|j}|jd}|D]}t||D]}||f|jvr2|j||f=q#q|j|_|js@d|_dSdS)z2 Delete row or rows from row==idx rrrrN)_gutterrrrrrrOrd)rCrr remainderrrrrrFrFrG delete_rowss   zWorksheet.delete_rowscCslt|||j}|j||| dd|j}|jd}|D]}t||D]}||f|jvr2|j||f=q#qdS)z8 Delete column or columns from col==idx rrrN)rrrrrrrO)rCrrrrrrrrFrFrG delete_colss  zWorksheet.delete_colsc Cst|tr t|}t|tstd|s|sdS|dk}|dk}|r*t|j|d}nt|j|d}t|D] \}} | || |||q6|j ||ddS)a Move a cell range by the number of rows and/or columns: down if rows > 0 and up if rows < 0 right if cols > 0 and left if cols < 0 Existing cells will be overwritten. Formulae and references will not be updated. z#Only CellRange objects can be movedNr)r ) row_shift col_shift) r{rr!rrrrr from_iterablershift) rC cell_rangerr translatedownrightrrrrFrFrG move_ranges  zWorksheet.move_rangec Cs|||}|j|}|j|}||j||f<|j|j|jf=||_||_|r=|jdkr?t|j|j} | j||d|_dSdSdS)zn Move a cell from one place to another. Delete at old index Rebase coordinate rx) row_delta col_deltaN) rrrrOrzrr|r~translate_formula) rCrrrrr%rnew_rownew_coltrFrFrGr!s   zWorksheet._move_cellcCstdt|)NzQValue must be a list, tuple, range or generator, or a dict. Supplied value is {0}) TypeErrorrtype)rCr rFrFrGr 3szWorksheet._invalid_rowcCt|S)z(Dimension factory for column information)rrkrFrFrGrK9rzWorksheet._add_columncCr1)z%Dimension factory for row information)rrkrFrFrGrI>rzWorksheet._add_rowcC|jrt|jSdS)z7Rows to be printed at the top of every page (ex: '1:3')N)r]rrkrFrFrGprint_title_rowsD zWorksheet.print_title_rowscC|dur t||_dSdS)zV Set rows to be printed on the top of every page format `1:3` N)r+r])rCrrFrFrGr3KcCr2)z@Columns to be printed at the left side of every page (ex: 'A:C')N)r^rrkrFrFrGprint_title_colsUr4zWorksheet.print_title_colscCr5)zX Set cols to be printed on the left of every page format ``A:C` N)r*r^)rCrrFrFrGr7\r6cCst|j|j|jd}t|S)N)rrrE)r)r^r]rEr)rCtitlesrFrFrG print_titlesfszWorksheet.print_titlescCs|j|j_t|jS)z The print area for the worksheet, or None if not set. To set, supply a range like 'A1:D4' or a list of ranges. )rEr_rrkrFrFrG print_areals  zWorksheet.print_areacCsN|st|_dSt|trt||_dSt|dr%td||_dSdS)z Range of cells in the form A1:D4 or list of ranges. Print area can be cleared by passing `None` or an empty list r,N)r,r_r{r from_stringrjoin)rCr|rFrFrGr:vs   r@)NNNNF)F)NNNNN)NNrr)r)rrF)T__name__ __module__ __qualname____doc__ _rel_type_path mime_type BREAK_NONE BREAK_ROW BREAK_COLUMNSHEETSTATE_VISIBLESHEETSTATE_HIDDENSHEETSTATE_VERYHIDDENPAPERSIZE_LETTERPAPERSIZE_LETTER_SMALLPAPERSIZE_TABLOIDPAPERSIZE_LEDGERPAPERSIZE_LEGALPAPERSIZE_STATEMENTPAPERSIZE_EXECUTIVE PAPERSIZE_A3 PAPERSIZE_A4PAPERSIZE_A4_SMALL PAPERSIZE_A5ORIENTATION_PORTRAITORIENTATION_LANDSCAPErArBpropertyrnrtrwrrrsetterrrrrrrrrrrrrrrrrryrrrrrrrrrrrrrrrrrrrrrrr(rr rKrIr3r7r9r:rFrFrFrGr-Ds &        # )        '   &            0             r-cCs*tt|d||t|||d}|S)z When deleting rows and columns are deleted we rely on overwriting. This may not be the case for a large offset on small set of cells: range(cells_to_delete) > range(cell_to_be_moved) r)rrr)rrmax_valgutterrFrFrGrs&rN)GrA itertoolsroperatorrinspectrwarningsropenpyxl.compatropenpyxl.utilsrrr r openpyxl.cellr r openpyxl.formatting.formattingr openpyxl.packaging.relationshipropenpyxl.workbook.childropenpyxl.workbook.defined_nameropenpyxl.formula.translaterdatavalidationrpagerrrrrrrrrbrfiltersrrarrrr$r r!merger" propertiesr# pagebreakr$r%scenarior&rr'formular(print_settingsr)r*r+r,r-rrFrFrFrGsF                   F