o 2Zq@sddlmZ ddlmZmZddlmZddlZddlm Z ddl m Z ddl m Z mZmZmZmZddlmZmZmZmZmZmZmZdd lmZdd lmZdd lmZm Z dd l!m"Z"m#Z#m$Z$dd l%m&Z&ddl'm(Z(ddl)m*Z*ddl+m,Z,m-Z-ddl.m/Z/ddl0m1Z1ddl2m3Z3m4Z4m5Z5ddl6m7Z7m8Z8m9Z9m:Z:ddl;mZ>m?Z?ddl@mAZAmBZBmCZCmDZDddlEmFZFmGZGddlHmIZIddlJmKZKedddZLGdd d e*ZMd!d"ZNdS)#)absolute_import)isliceproduct) attrgetterN) isgenerator)warn)unicoderange basestring deprecated safe_string)coordinate_from_stringcolumn_index_from_stringget_column_letterrange_boundariesrows_from_rangecoordinate_to_tupleabsolute_coordinate)COORD_RE)Cell)SheetTitleExceptionNamedRangeException)points_to_pixelsDEFAULT_COLUMN_WIDTHDEFAULT_ROW_HEIGHT)ConditionalFormattingList)RelationshipList)_WorkbookChild) COL_RANGE_RE ROW_RANGE_RE)BoundDictionary)DataValidationList)PrintPageSetup PageMargins PrintOptions)ColumnDimension RowDimensionDimensionHolderSheetFormatProperties)SheetProtection) AutoFilter SortState) SheetViewPane Selection SheetViewList)MultiCellRange CellRange)WorksheetProperties) PageBreakz!Use the worksheet.values propertyccs |D] }dd|DVqdS)zReturn cell values row-by-rowcs|]}|jVqdSNvalue.0cr<N/var/www/html/env/lib/python3.10/site-packages/openpyxl/worksheet/worksheet.py Qzflatten..Nr<)resultsrowr<r<r=flattenLsrBc@seZdZdZdZdZdZdZdZdZ dZ d Z d Z d Z d Zd ZdZdZdZdZdZdZdZdZdZdZdddZddZeddZedd Zed!d"Zed#d$Z ed%d&Z!ed'd(Z"ed)d*Z#e#j$d+d*Z# e%d,d-d.Z&ed/d0Z'e'j$dd1d0Z'e%d2dd4d5Z(dd6d7Z)d8d9Z*d:d;Z+dd?Z-d@dAZ.dBdCZ/edDdEZ0edFdGZ1edHdIZ2edJdKZ3dLdMZ4edNdOZ5  ddPdQZ6dRdSZ7edTdUZ8edVdWZ9ddXdYZ:dZd[Z;ed\d]ZdddeZ?dfdgZ@ddhdiZAddjdkZBdldmZCdndoZDddpdqZEee%drdsdtZFddudvZGdwdxZHddzd{ZIdd|d}ZJdd~dZKdddZLdddZMddZNddZOddZPddZQeddZReRj$ddZReddZSeSj$ddZSeddZTeddZUeUj$ddZUdS) 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+xmlrr!visiblehidden veryHidden1234567891011portrait landscapeNcCst||||dSr6)r__init___setup)selfparenttitler<r<r=rVys zWorksheet.__init__cCstd|j|_t||jd|_t|_i|_g|_ g|_ t |_ d|_ g|_t|_g|_g|_t|_g|_d|_t|d|_t|_d|_d|_d|_t|_t |_!t"|_#d|_$t%|_&t'|_(d|_)i|_*d|_+t,|_-d|_.t/|_0t1|_2dS)Nindex)rDdefault_factoryrF)rDr)3r _add_rowrow_dimensionsr( _add_columncolumn_dimensionsr4 page_breaks_cells_charts_imagesr_rels_drawing _commentsr1 merged_cells_tables_pivotsr"data_validations _hyperlinks sheet_stater# page_setupr% print_options _print_rows _print_cols _print_arear$ page_marginsr0viewsr* protection _current_rowr+ auto_filterr, sort_state paper_sizeformula_attributes orientationrconditional_formattinglegacy_drawingr3sheet_propertiesr) sheet_formatrXr<r<r=rW}sF  zWorksheet._setupcCs |jjdS)Nr)rt sheetViewrr<r<r= sheet_views zWorksheet.sheet_viewcC |jjjSr6)r selectionsqrefrr<r<r= selected_cell zWorksheet.selected_cellcCrr6)rr activeCellrr<r<r= active_cellrzWorksheet.active_cellcCs|jjSr6)r showGridLinesrr<r<r=show_gridlinesszWorksheet.show_gridlinescCrr6)r~ outlinePr summaryBelowrr<r<r=show_summary_belowrzWorksheet.show_summary_belowcCrr6)r~r summaryRightrr<r<r=show_summary_rightrzWorksheet.show_summary_rightccs2dD]}t|j|}|dur|t|fVqdSN) codeName!enableFormatConditionsCalculation filterMode publishedsyncHorizontalsyncRef syncVerticaltransitionEvaluationtransitionEntry)getattrr~r )rXattrr8r<r<r=vba_codes zWorksheet.vba_codecCs,|D]\}}|dvrt|j||qdSr)itemssetattrr~)rXr8kvr<r<r=rs zUse the ws.values propertycCs |jS)z8Return an unordered list of the cells in this worksheet.)rbvaluesrr<r<r=get_cell_collections zWorksheet.get_cell_collectioncCs|jjdur |jjjSdSr6)rpane topLeftCellrr<r<r= 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)rrr) isinstancer coordinaterrrr.rxSplitySplitrlistinsertr/)rXrrAcolumnviewselr<r<r=rs:         z)Set print titles rows or columns directlyrowscCs4|j|}|dkrdt||_dSd||_dS)z Print Titles are rows or columns that are repeated on each printed sheet. This adds n rows or columns at the top or left of the sheet colszA:%sz1:%dN)rY get_indexrprint_title_colsprint_title_rows)rXn rows_or_colsscoper<r<r=add_print_titles zWorksheet.add_print_titlecCs6|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 r!z'Row or column values must be at least 1N) ValueError _get_cellr8)rXrArr8cellr<r<r=rs  zWorksheet.cellcCs4||f}||jvrt|||d}|||j|S)z Internal method for getting a cell from a worksheet. Will create a new cell if one doesn't already exist. )rAcol_idx)rbr _add_cell)rXrArrrr<r<r=r0s    zWorksheet._get_cellcCs,|j}|j}t||j|_||j||f<dS)z: Internal method for adding cell objects. N)rrAmaxrvrb)rXrrrAr<r<r=r<szWorksheet._add_cellcCst|trt|j|jgstd|d|j|j}t|tr%t|}t |\}}}}t ||||gsz$Worksheet.min_row..rbsetmin)rXrrr<r<r=r} zWorksheet.min_rowcCr)zLThe maximum row index containing data (1-based) :type: int r!csrrr<r9r<r<r=r>rz$Worksheet.max_row..rbrr)rXrrr<r<r=rrzWorksheet.max_rowcCr)zOThe minimum column index containing data (1-based) :type: int r!csrr!Nr<r9r<r<r=r>rz'Worksheet.min_column..r)rXrrr<r<r= min_columnrzWorksheet.min_columncCr)zOThe maximum column index containing data (1-based) :type: int r!csrrr<r9r<r<r=r>rz'Worksheet.max_column..r)rXrrr<r<r=rrzWorksheet.max_columnc Cst|jr,t}t}|jD]\}}||||q t|}t|}t|}t|}ndSdt||t||fS)zoReturn the minimum bounding range for all cells containing data (ex. 'A1:M24') :rtype: string zA1:A1z %s%d:%s%d)rbraddrrr) rXrrrAcolrrrrr<r<r=calculate_dimensions   zWorksheet.calculate_dimensioncCr)z1Returns the result of :func:`calculate_dimension`)rrr<r<r= dimensionszWorksheet.dimensionscCs|durtdt|\}}}}|jdkr!t||||gs!dS|p$d}|p(d}|p-|j}|p2|j}|dur;||7}|durC||7}|||||||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 range_string: range string (e.g. 'A1:B2') *deprecated* :type range_string: string :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: smallest row index (1-based index) :type max_row: int :param row_offset: added to min_row and max_row (e.g. 4) :type row_offset: int :param column_offset: added to min_col and max_col (e.g. 3) :type column_offset: int :rtype: generator NzGUsing a range string with iter_rows is deprecated. Use ws[range_string]rr<r!)rrupperrvrrr _cells_by_row)rX range_stringrrrr row_offset column_offsetr<r<r=rs$"   zWorksheet.iter_rowsc#s>t||dD]tfddt||dDVqdS)Nr!c3s|] }j|dVqdS)rArNr)r:rrArXr<r=r> z*Worksheet._cells_by_row..r rrXrrrrr<rr=rs   zWorksheet._cells_by_rowcCr)zfProduces all cells in the worksheet, by row (see :func:`iter_rows`) :type: generator rrr<r<r=r szWorksheet.rowsccs(|D] }tdd|DVqdS)zTProduces all cell values in the worksheet, by row :type: generator csr5r6r7r9r<r<r=r>r?z#Worksheet.values..N)rr)rXrAr<r<r=rs zWorksheet.valuescCsR|jdkrt||||gsdS|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: smallest row index (1-based index) :type max_row: int :rtype: generator rr<r!)rvrrr _cells_by_col)rXrrrrr<r<r=r s  zWorksheet.iter_colsc#s>t||dD]tfddt||dDVqdS)z% Get cells by column r!c3s|] }j|dVqdSrr)r:rArrXr<r=r>Jrz*Worksheet._cells_by_col..Nrrr<rr=rEs   zWorksheet._cells_by_colcCr)zGProduces all cells in the worksheet, by column (see :func:`iter_cols`))rrr<r<r=columnsNrzWorksheet.columnszg Use ws.iter_rows() or ws.iter_cols() depending whether you want rows or columns returned. cCs|||||S)aReturns a 2D array of cells. Will create any cells within the boundaries that do not already exist :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: smallest row index (1-based index) :type max_row: int :rtype: generator )rrr<r<r=get_squared_rangeTszWorksheet.get_squared_rangez=Ranges are workbook objects. Use wb.defined_names[range_name]c Cs|jj|}|jr|j|j|krd|}t||jdkr)d|}t|g}|jD](\}}|j|}||kr?t d||}t |t rL|fg}|D]} | | qNq.t |S)z Returns a 2D array of cells, with optional row and column offsets. :param range_name: `named range` name :type range_name: string :rtype: tuple[tuple[openpyxl.cell.cell.Cell]] z#{0} not available in this worksheetRANGEz"{0} refers to a value, not a rangez+Range includes cells from another worksheet)rY defined_names localSheetIdrrKeyErrortype NameError destinationsrrrextendr) rX range_namedefnmsgresultrZ cells_rangewsrrAr<r<r=get_named_rangens&       zWorksheet.get_named_rangecCs8||j_||j|jfvrtd|j|jf||j_dS)zSet printer settings zValues should be %s or %sN)rn paperSizeORIENTATION_PORTRAITORIENTATION_LANDSCAPErr{)rXryr{r<r<r=set_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)rkappend)rXdata_validationr<r<r=add_data_validationszWorksheet.add_data_validationcC|dur||_|j|dS)zd Add a chart to the sheet Optionally provide a cell for the top-left anchor N)anchorrcr)rXchartrr<r<r= add_chartzWorksheet.add_chartcCr)zf Add an image to the sheet. Optionally provide a cell for the top-left anchor N)rrdr)rXimgrr<r<r= add_imagerzWorksheet.add_imagecC|j|dSr6)rir)rXtabler<r<r= add_tablezWorksheet.add_tablecCrr6)rjr)rXpivotr<r<r= add_pivotrzWorksheet.add_pivotcCs~t|||||d} |j|j|j\}}} } t|| d} t|| d} t| | } t| ddD] }||jvr<|j|=q1dS)Nrrrrrr!) r2rhrcoordboundsr rrrb)rXr start_row start_columnend_row end_columncrrrrrrrcellsr;r<r<r= merge_cellss  zWorksheet.merge_cellszUse ws.merged_cells.rangescCs|jjddS)zReturn a copy of cell rangesN)rhrangesrr<r<r=merged_cell_rangesszWorksheet.merged_cell_rangescCs>t|||||d}|j|jvrtd|j|j|dS)zC Remove merge on a cell range. Range is a cell range (e.g. A1:E1) r!zCell range {0} is not mergedN)r2r"rhrrremove)rXrr$r%r&r'r(r<r<r= unmerge_cellss  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 r!z,Cells cannot be copied from other worksheets)rArr8N)rvrrrr r enumeraterrYrrrArbdictrr r _invalid_row)rXiterablerow_idxrcontentrr<r<r=rs0      zWorksheet.appendrAc Cs|dk}|j|d}|dkr|j|d}t|}t|jt||d}|D]0}|r0|j|kr0q&|r8|j|kr8q&|j|j|jf=t ||}t ||||||j|j|jf<q&dS)zB Move either rows or columns around by the offset r)rr)r)rreverseN) rrrsortedrbrrrArrr) rXrroffset row_or_colr5r)rvalr<r<r= _move_cellss    zWorksheet._move_cellscC|j||dddS)z4 Insert row or rows before row==idx rArr7r8Nr:rXidxamountr<r<r= insert_rows-zWorksheet.insert_rowscCr;)z: Insert column or columns before col==idx rrr7r8Nr=r>r<r<r= insert_cols4rBzWorksheet.insert_colscCsdt|||j}|j||| dd|D]}t|j|jdD]}||f|jvr.|j||f=qqdS)z2 Delete row or rows from row==idx rAr<r!N)_gutterrr:r rrrb)rXr?r@ remainderrArr<r<r= delete_rows; zWorksheet.delete_rowscCsdt|||j}|j||| dd|D]}t|j|jdD]}||f|jvr.|j||f=qqdS)z8 Delete column or columns from col==idx rrCr!N)rErr:r rrrb)rXr?r@rFrrAr<r<r= delete_colsJrHzWorksheet.delete_colscCstdt|)NzQValue must be a list, tuple, range or generator, or a dict. Supplied value is {0}) TypeErrorrr)rXr2r<r<r=r1YszWorksheet._invalid_rowcCt|S)z(Dimension factory for column information)r&rr<r<r=r__rzWorksheet._add_columncCrK)z%Dimension factory for row information)r'rr<r<r=r]drzWorksheet._add_rowcCs<ddlm}ddlm}||_|j|j_|j|j_||S)Nr)SpreadsheetDrawing)write_worksheet) $openpyxl.drawing.spreadsheet_drawingrLopenpyxl.writer.worksheetrMrfrcchartsrdimages)rXrLrMr<r<r=_writejs    zWorksheet._writecC|jr|jSdS)z7Rows to be printed at the top of every page (ex: '1:3')N)rprr<r<r=rszWorksheet.print_title_rowscC$|dur t|s td||_dS)zV Set rows to be printed on the top of every page format `1:3` Nz%Print title rows must be the form 1:3)rmatchrrp)rXrr<r<r=rz  cCrS)z@Columns to be printed at the left side of every page (ex: 'A:C')N)rqrr<r<r=rrTzWorksheet.print_title_colscCrU)zX Set cols to be printed on the left of every page format ``A:C` Nz%Print title cols must be the form C:D)rrVrrq)rXrr<r<r=rrWcCs*|jr|jrd|j|jgS|jp|jS)N,)rrjoinrr<r<r= print_titless  zWorksheet.print_titlescCs|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. )rrrr<r<r= print_areaszWorksheet.print_areacCs$t|tr|g}dd|D|_dS)zD Range of cells in the form A1:D4 or list of ranges cSsg|]}t|qSr<)r)r:rr<r<r= sz(Worksheet.print_area..N)rr rr)rXr8r<r<r=r[s r6)r)NNNNNrr)NNNN)NNNNN)NNrrA)r!)V__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_A5rrrVrWpropertyrrrrrrrsetterr rrrrrrrrrrrrrrrrrrrrrrrrr rrrrrr r*r,r.rr:rArDrGrIr1r_r]rRrrrZr[r<r<r<r=rCTs &           #    )       8   %   "       0          rCcCs&tt|d||t|||}|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!)r rr)r?r7max_valgutterr<r<r=rEs"rE)O __future__r itertoolsrroperatorrreinspectrwarningsropenpyxl.compatrr r r r openpyxl.utilsr rrrrrropenpyxl.utils.cellr openpyxl.cellropenpyxl.utils.exceptionsrropenpyxl.utils.unitsrrropenpyxl.formatting.formattingropenpyxl.packaging.relationshipropenpyxl.workbook.childropenpyxl.workbook.defined_namerropenpyxl.utils.bound_dictionaryr datavalidationr"pager#r$r%rr&r'r(r)rur*filtersr+r,rtr-r.r/r0 cell_ranger1r2 propertiesr3 pagebreakr4rBrCrEr<r<r<r=sJ    $           g