U o^h@sldZddlmZmZmZddlmZddlmZddl m Z ddl m Z m Z mZmZmZddlmZmZddlmZdd lmZdd lmZdd lmZmZdd lmZd dlm Z d dl!m"Z"m#Z#m$Z$d dl%m&Z&m'Z'm(Z(m)Z)d dl*m+Z+d dl,m-Z-d dl.m/Z/m0Z0m1Z1d dl2m3Z3m4Z4d dl5m6Z6d dl7m8Z8d dl9m:Z:m;Z;d dlddZ?dS)z.Worksheet is the 2nd-level container in Excel.)isliceproductchain) itemgetter) isgenerator) deprecated)column_index_from_stringget_column_letterrange_boundariescoordinate_to_tupleabsolute_coordinate)Cell MergedCell)ConditionalFormattingList)RelationshipList)_WorkbookChild) COL_RANGE_RE ROW_RANGE_RE) Translator)DataValidationList)PrintPageSetup PageMargins PrintOptions)ColumnDimension RowDimensionDimensionHolderSheetFormatProperties)SheetProtection) AutoFilter)Pane Selection SheetViewList)MultiCellRange CellRange)MergedCellRange)WorksheetProperties)RowBreakColBreak) ScenarioListc@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#ed+d,Z$e$j%dd-d,Z$dd.d/Z&d0d1Z'd2d3Z(d4d5Z)d6d7Z*d8d9Z+d:d;Z,edd?Z.ed@dAZ/edBdCZ0dDdEZ1edFdGZ2ddIdJZ3ddKdLZ4edMdNZ5edOdPZ6ddQdRZ7ddSdTZ8edUdVZ9dWdXZ:dYdZZ;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+xmlrrvisiblehiddenZ veryHidden12345678910Z11ZportraitZ landscapeNcCst||||dSN)r__init___setup)selfparenttitler?v/private/var/folders/sd/whlwsn6x1_qgglc0mjv25_695qk2gl/T/pip-install-4zq3fp6i/openpyxl/openpyxl/worksheet/worksheet.pyr:^szWorksheet.__init__cCst||jd|_t||jd|_t|_t|_i|_ g|_ g|_ t |_ d|_g|_t|_g|_g|_t|_g|_d|_t|d|_t|_d|_d|_d|_t|_ t!|_"t#|_$d|_%t&|_'d|_(i|_)d|_*t+|_,d|_-t.|_/t0|_1t2|_3dS)N)r+default_factoryr-)r+r)4r_add_rowZrow_dimensions _add_columnZcolumn_dimensionsr' row_breaksr( col_breaks_cells_charts_imagesrZ_relsZ_drawingZ _commentsr# merged_cells_tables_pivotsrdata_validationsZ _hyperlinksZ sheet_stater page_setuprZ print_options _print_rows _print_cols _print_arearZ page_marginsr"viewsr protection _current_rowrZ auto_filter paper_sizeZformula_attributes orientationrZconditional_formattingZlegacy_drawingr&sheet_propertiesrZ sheet_formatr)Z scenariosr<r?r?r@r;bsL zWorksheet._setupcCs |jjdSNr)rQZ sheetViewrWr?r?r@ sheet_viewszWorksheet.sheet_viewcCs|jjdjSrX)rY selectionsqrefrWr?r?r@ selected_cellszWorksheet.selected_cellcCs|jjdjSrX)rYrZ activeCellrWr?r?r@ active_cellszWorksheet.active_cellcCs |j|jfSr9)rDrErWr?r?r@ page_breaksszWorksheet.page_breakscCs|jjSr9)rYZ showGridLinesrWr?r?r@show_gridlinesszWorksheet.show_gridlinescCs |jjjSr9)rV outlinePrZ summaryBelowrWr?r?r@show_summary_belowszWorksheet.show_summary_belowcCs |jjjSr9)rVraZ summaryRightrWr?r?r@show_summary_rightszWorksheet.show_summary_rightcCs|jjdk r|jjjSdSr9)rYpane topLeftCellrWr?r?r@ freeze_paness zWorksheet.freeze_panescCst|tr|j}|dkrd}|s,d|j_dSt|\}}|j}t|ddd|_d|jd_|dkrn|d|j_|dkr|d|j_ d|j_ d|jd_|dkrd|jd_d|j_ |dkr|dkrt |j}| dt dddd | dt dddd ||_dS) NA1ZtopRightfrozen)re activePanestaterrZ bottomLeftZ bottomRight)rdr]r[) isinstancer coordinaterYrdr r rZZxSplitZySplitrilistinsertr!)r<rerowcolumnviewselr?r?r@rfs8        cCs6|dks|dkrtd|||}|dk r2||_|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_cellvalue)r<rorprucellr?r?r@rvs  zWorksheet.cellcCs4||f}||jkr*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. rorp)rFr _add_cell)r<rorprlrvr?r?r@rts   zWorksheet._get_cellcCs,|j}|j}t||j|_||j||f<dS)z: Internal method for adding cell objects. N)col_idxromaxrSrF)r<rvrpror?r?r@rxszWorksheet._add_cellcCst|tr8t|j|jgs(td|d|j|j}t|trJt|}t |\}}}}t ||||gsxtd||st | ||}||kr|d}|S|st |j |||j|d}||kr|d}|Sd|kr|||St |j ||||dS)alConvenience access by Excel style coordinates The key can be a single cell coordinate 'A1', a range of cells 'A1:D25', individual rows or columns 'A', 4 or ranges of rows or columns 'A:D', 4:10. Single cells will always be created if they do not exist. Returns either a single cell or a tuple of rows or columns. z&{0} is not a valid coordinate or rangez{0}:{1}r)min_colmin_rowmax_colmax_row:)r|r{r~r})rksliceallstartstop IndexErrorformatintstrr anytuple iter_cols iter_rows max_columnrt)r<keyr{r|r}r~colsrowsr?r?r@ __getitem__ s8     zWorksheet.__getitem__cCs|||_dSr9ru)r<rrur?r?r@ __setitem__4szWorksheet.__setitem__cCs|Sr9rrWr?r?r@__iter__8szWorksheet.__iter__cCs*t|\}}||f|jkr&|j||f=dSr9)r rF)r<rrorpr?r?r@ __delitem__<s zWorksheet.__delitem__cCs*d}|jr&tdd|jD}t|}|S)zMThe minimium row index containing data (1-based) :type: int rcss|]}|dVqdSrNr?.0cr?r?r@ Jsz$Worksheet.min_row..rFsetmin)r<r|rr?r?r@r|Bs zWorksheet.min_rowcCs*d}|jr&tdd|jD}t|}|S)zLThe maximum row index containing data (1-based) :type: int rcss|]}|dVqdSrr?rr?r?r@rWsz$Worksheet.max_row..rFrrz)r<r~rr?r?r@r~Os zWorksheet.max_rowcCs*d}|jr&tdd|jD}t|}|S)zOThe minimum column index containing data (1-based) :type: int rcss|]}|dVqdSrNr?rr?r?r@rdsz'Worksheet.min_column..r)r<r{rr?r?r@ min_column\s zWorksheet.min_columncCs*d}|jr&tdd|jD}t|}|S)zOThe maximum column index containing data (1-based) :type: int rcss|]}|dVqdSrr?rr?r?r@rqsz'Worksheet.max_column..r)r<r}rr?r?r@ris zWorksheet.max_columnc Csz|jrXt}t}|jD]\}}||||qt|}t|}t|}t|}ndSt||dt||S)zoReturn the minimum bounding range for all cells containing data (ex. 'A1:M24') :rtype: string zA1:A1r)rFraddrzrr ) r<rrrocolr~r}r{r|r?r?r@calculate_dimensionvs   zWorksheet.calculate_dimensioncCs|S)z1Returns the result of :func:`calculate_dimension`)rrWr?r?r@ dimensionsszWorksheet.dimensionsFcCsT|jdkrt||||gsdS|p$d}|p,d}|p6|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 rr?r)rSrrr~ _cells_by_row)r<r|r~r{r} values_onlyr?r?r@rs  zWorksheet.iter_rowsc#sZt||dD]Ffddt||dD}|rJtdd|DVqt|VqdS)Nrc3s|]}j|dVqdSrwNrv)rrpror<r?r@rsz*Worksheet._cells_by_row..css|] }|jVqdSr9rrrvr?r?r@rsrangerr<r{r|r}r~rcellsr?rr@rs zWorksheet._cells_by_rowcCs|S)zfProduces all cells in the worksheet, by row (see :func:`iter_rows`) :type: generator rrWr?r?r@rszWorksheet.rowsccs|jddD] }|Vq dS)zTProduces all cell values in the worksheet, by row :type: generator T)rNr)r<ror?r?r@valuesszWorksheet.valuescCsT|jdkrt||||gsdS|p$d}|p,d}|p6|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 rr?r)rSrrr~ _cells_by_col)r<r{r}r|r~rr?r?r@rs  zWorksheet.iter_colsc#sZt||dD]Ffddt||dD}|rJtdd|DVqt|VqdS)z% Get cells by column rc3s|]}j|dVqdSrr)rrorpr<r?r@rsz*Worksheet._cells_by_col..css|] }|jVqdSr9rrr?r?r@rsNrrr?rr@rs  zWorksheet._cells_by_colcCs|S)zGProduces all cells in the worksheet, by column (see :func:`iter_cols`))rrWr?r?r@columnsszWorksheet.columnscCs||j_||j_dS)zSet printer settings N)rMZ paperSizerU)r<rTrUr?r?r@set_printer_settings szWorksheet.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)rLappend)r<Zdata_validationr?r?r@add_data_validationszWorksheet.add_data_validationcCs|dk r||_|j|dS)zd Add a chart to the sheet Optionally provide a cell for the top-left anchor N)anchorrGr)r<Zchartrr?r?r@ add_chartszWorksheet.add_chartcCs|dk r||_|j|dS)zf Add an image to the sheet. Optionally provide a cell for the top-left anchor N)rrHr)r<imgrr?r?r@ add_image%szWorksheet.add_imagecCs|j|dSr9)rJr)r<tabler?r?r@ add_table/szWorksheet.add_tablecCs|j|dSr9)rKr)r<Zpivotr?r?r@ add_pivot3szWorksheet.add_pivotcCs,t|||||d}|j|||dS)z@ Set merge on a cell range. Range is a cell range (e.g. A1:E1)  range_stringr{r|r}r~N)r$rIr_clean_merge_range)r<r start_row start_columnend_row end_columncrr?r?r@ merge_cells7s  zWorksheet.merge_cellscCsPt||j}t|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) r%coordr from_iterablernextrrFr)r<rZmcrrrorr?r?r@r?s    zWorksheet._clean_merge_rangezUse ws.merged_cells.rangescCs|jjddS)zReturn a copy of cell rangesN)rIrangesrWr?r?r@merged_cell_rangesNszWorksheet.merged_cell_rangesc Cslt|||||d}|j|jkr.td|j|j|t|j}t ||D]\}} |j || f=qRdS)zC Remove merge on a cell range. Range is a cell range (e.g. A1:E1) rzCell range {0} is not mergedN) r$rrIrsrremoverrrrrF) r<rrrrrrrrorr?r?r@ unmerge_cellsUs    zWorksheet.unmerge_cellscCs|jd}t|tttfs"t|rt|dD]`\}}t|trn|}|jrZ|j|krZt d||_||_ ||_ nt||||d}||j ||f<q,nXt|t r|D]8\}}t|trt|}t||||d}||j ||f<qn ||||_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)rorpruN)rSrkrmrrr enumerater r=rsrprorFdictitemsrr _invalid_row)r<iterableZrow_idxrycontentrvr?r?r@rfs,     zWorksheet.appendroc Cs|dk}d}d}|dkr.|j|d}|}d} n|j|d}|}d} t|}t|jt| |dD]6\} } |rv| |krvq^n|r| |krq^|| | ||q^dS)zB Move either rows or columns around by the offset rro)r|)r{r)rreverseN)rrrmsortedrFr _move_cell) r<r|r{offset row_or_colr row_offset col_offsetrrrorpr?r?r@ _move_cellss"    zWorksheet._move_cellscCs|j||dd|j|_dS)z4 Insert row or rows before row==idx ror|rrN)rr~rSr<idxamountr?r?r@ insert_rowsszWorksheet.insert_rowscCs|j||dddS)z: Insert column or columns before col==idx rpr{rrN)rrr?r?r@ insert_colsszWorksheet.insert_colscCst|||j}|j||| dd|j}|jd}|D].}t||D]}||f|jkrF|j||f=qFq8|j|_|js|d|_dS)z2 Delete row or rows from row==idx rorrrN)_gutterr~rrrrrFrS)r<rr remainderr{r}rorr?r?r@ delete_rowss zWorksheet.delete_rowscCslt|||j}|j||| dd|j}|jd}|D].}t||D]}||f|jkrF|j||f=qFq8dS)z8 Delete column or columns from col==idx rprrN)rrrr|r~rrF)r<rrrr|r~rror?r?r@ delete_colss zWorksheet.delete_colsc Cst|trt|}t|ts$td|s0|s0dS|dk}|dk}|rTt|j|d}nt|j|d}t|D]\}} | || |||ql|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)Z row_shiftZ col_shift) rkrr$rsrrrrrrshift) r< cell_rangerr translateZdownrightrrorr?r?r@ move_ranges  zWorksheet.move_rangec Csz|||}|j|}|j|}||j||f<|j|j|jf=||_||_|rv|jdkrvt|j|j} | j||d|_dS)zn Move a cell from one place to another. Delete at old index Rebase coordinate f)Z row_deltaZ col_deltaN) rtrorprFZ data_typerrurlZtranslate_formula) r<rorprrrrvZnew_rowZnew_coltr?r?r@rs   zWorksheet._move_cellcCstdt|dS)NzQValue must be a list, tuple, range or generator, or a dict. Supplied value is {0}) TypeErrorrtype)r<rr?r?r@rszWorksheet._invalid_rowcCst|S)z(Dimension factory for column information)rrWr?r?r@rCszWorksheet._add_columncCst|S)z%Dimension factory for row information)rrWr?r?r@rB#szWorksheet._add_rowcCs|jr |jSdS)z7Rows to be printed at the top of every page (ex: '1:3')N)rNrWr?r?r@print_title_rows)szWorksheet.print_title_rowscCs$|dk rt|std||_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)rmatchrsrN)r<rr?r?r@r0s cCs|jr |jSdS)z@Columns to be printed at the left side of every page (ex: 'A:C')N)rOrWr?r?r@print_title_cols<szWorksheet.print_title_colscCs$|dk rt|std||_dS)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)rrrsrO)r<rr?r?r@rCs cCs.|jr|jrd|j|jgS|jp(|jSdS)N,)rrjoinrWr?r?r@ print_titlesOs 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. )rPrWr?r?r@ print_areaWszWorksheet.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 )rvr?r?r@ hsz(Worksheet.print_area..N)rkrrP)r<rur?r?r@r`s )N)N)N)NNNNF)F)NNNNF)F)N)N)NNNNN)NNNNN)NNrro)r)r)r)r)rrF)F)T__name__ __module__ __qualname____doc__Z _rel_type_pathZ mime_typeZ BREAK_NONEZ BREAK_ROWZ BREAK_COLUMNZSHEETSTATE_VISIBLEZSHEETSTATE_HIDDENZSHEETSTATE_VERYHIDDENZPAPERSIZE_LETTERZPAPERSIZE_LETTER_SMALLZPAPERSIZE_TABLOIDZPAPERSIZE_LEDGERZPAPERSIZE_LEGALZPAPERSIZE_STATEMENTZPAPERSIZE_EXECUTIVEZ PAPERSIZE_A3Z PAPERSIZE_A4ZPAPERSIZE_A4_SMALLZ PAPERSIZE_A5ZORIENTATION_PORTRAITZORIENTATION_LANDSCAPEr:r;propertyrYr\r^r_r`rbrcrfsetterrvrtrxrrrrr|r~rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrCrBrrrrr?r?r?r@r*9s (         #   )      &   &      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)rrzr)rrZmax_valZgutterr?r?r@rks&rN)@r itertoolsrrroperatorrinspectrZopenpyxl.compatrZopenpyxl.utilsrr r r r Z openpyxl.cellr rZopenpyxl.formatting.formattingrZopenpyxl.packaging.relationshiprZopenpyxl.workbook.childrZopenpyxl.workbook.defined_namerrZopenpyxl.formula.translaterZdatavalidationrpagerrrrrrrrrRrfiltersrrQr r!r"rr#r$merger%Z propertiesr&Z pagebreakr'r(Zscenarior)r*rr?r?r?r@s<             8