^Q\6m@sdddlmZddlmZmZddlmZddlZddlm 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;m<Z<ddl=m>Z>m?Z?ddl@mAZAmBZBmCZCmDZDddlEmFZFmGZGddlHmIZIddlJmKZKedddZLGdd d e*ZMdS)!)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'x |D]}dd|DVqWdS)zReturn cell values row-by-rowcss|]}|jVqdS)N)value).0cr8Q/var/www/dbchiro/venv/lib/python3.4/site-packages/openpyxl/worksheet/worksheet.py Qszflatten..Nr8)resultsrowr8r8r9flattenLs r=c @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%d2d3d4d5Z(dd6d7Z)d8d9Z*d:d;Z+d<d=Z,d>d?Z-d@dAZ.edBdCZ/edDdEZ0edFdGZ1edHdIZ2dJdKZ3edLdMZ4ddddddddNdOZ5dPdQZ6edRdSZ7edTdUZ8dddddVdWZ9dXdYZ:edZd[Z;e%d\d]d^Z<e%d_d`daZ=dbdcZ>dddeZ?ddfdgZ@ddhdiZAdjdkZBdldmZCddddddndoZDee%dpdqdrZEddddddsdtZFdudvZGddddwdxdyZHddzd{ZIdd|d}ZJdd~dZKdddZLddZMddZNddZOddZPeddZQeQj$ddZQeddZReRj$ddZReddZSeddZTeTj$ddZTdS) 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!visibleZhiddenZ veryHidden123456789Z10Z11ZportraitZ landscapeNcCs!tj||||jdS)N)r__init___setup)selfparenttitler8r8r9rKyszWorksheet.__init__cCs{td|j|_td|d|j|_t|_i|_g|_ g|_ t |_ d|_ g|_t|_g|_g|_t|_g|_d|_td||_t|_d|_d|_d|_t|_t |_!t"|_#d|_$t%|_&t'|_(d|_)i|_*d|_+t,|_-d|_.t/|_0t1|_2dS)Nindexr?default_factoryrAr)3r _add_rowZrow_dimensionsr( _add_columnZcolumn_dimensionsr4Z page_breaks_cells_charts_imagesrZ_rels_drawingZ _commentsr1 merged_cells_tables_pivotsr"data_validationsZ _hyperlinksZ sheet_stater# page_setupr%Z print_options _print_rows _print_cols _print_arear$Z page_marginsr0viewsr* protection _current_rowr+Z auto_filterr,Z sort_state paper_sizeZformula_attributes orientationrZconditional_formattingZlegacy_drawingr3sheet_propertiesr)Z sheet_format)rMr8r8r9rL}sD                              zWorksheet._setupcCs|jjdS)Nr)r`Z sheetView)rMr8r8r9 sheet_viewszWorksheet.sheet_viewcCs |jjjS)N)rf selectionsqref)rMr8r8r9 selected_cellszWorksheet.selected_cellcCs |jjjS)N)rfrg activeCell)rMr8r8r9 active_cellszWorksheet.active_cellcCs |jjS)N)rfZ showGridLines)rMr8r8r9show_gridlinesszWorksheet.show_gridlinescCs |jjjS)N)re outlinePrZ summaryBelow)rMr8r8r9show_summary_belowszWorksheet.show_summary_belowcCs |jjjS)N)rermZ summaryRight)rMr8r8r9show_summary_rightszWorksheet.show_summary_rightc csGx@d D]8}t|j|}|dk r|t|fVqqWdS) NcodeName!enableFormatConditionsCalculation filterMode publishedsyncHorizontalsyncRef syncVerticaltransitionEvaluationtransitionEntry) rprqrrrsrtrurvrwrx)getattrrer )rMattrr5r8r8r9vba_codes   zWorksheet.vba_codec CsCx<|jD].\}}|d kr t|j||q q WdS) Nrprqrrrsrtrurvrwrx) zcodeNamez!enableFormatConditionsCalculationz filterModez publishedzsyncHorizontalzsyncRefz syncVerticalztransitionEvaluationztransitionEntry)itemssetattrre)rMr5kvr8r8r9r{s  zUse the ws.values propertycCs |jjS)z8Return an unordered list of the cells in this worksheet.)rTvalues)rMr8r8r9get_cell_collectionszWorksheet.get_cell_collectioncCs#|jjdk r|jjjSdS)N)rfpane topLeftCell)rMr8r8r9 freeze_panesszWorksheet.freeze_panesc Cst|tr|j}n|dkr0d}n|sFd|j_dSt|\}}|j}td|dddd|_d|jd_|dkr|d|j_n|dkr|d|j_ d |j_ d |jd_|dkrd |jd_d |j_ qn|dkr|dkrt |j}|j dt d dd dd d|j dt d d d dd d||_ndS)NA1r activePaneZtopRightstatefrozenrr!Z bottomLeftZ bottomRightrrjrh) isinstancer coordinaterfrrr.rgZxSplitZySplitrlistinsertr/)rMrr<columnZviewselr8r8r9rs6           %%z)Set print titles rows or columns directlyrowscCsE|jj|}|dkr4dt||_n d||_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)rN get_indexrprint_title_colsprint_title_rows)rMnZ rows_or_colsZscoper8r8r9add_print_titles zWorksheet.add_print_titlecCsU|dks|dkr'tdn|j||}|dk rQ||_n|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_cellr5)rMr<rr5cellr8r8r9rs   zWorksheet.cellcCsN||f}||jkrCt|d|d|}|j|n|j|S)z Internal method for getting a cell from a worksheet. Will create a new cell if one doesn't already exist. r<col_idx)rTr _add_cell)rMr<rrrr8r8r9r0s  zWorksheet._get_cellcCs>|j}|j}t||j|_||j||fd}|jr:tdd|jD}t|}n|S)zMThe minimium row index containing data (1-based) :type: int r!css|]}|dVqdS)rNr8)r6r7r8r8r9r:sz$Worksheet.min_row..)rTsetmin)rMrrr8r8r9rws  zWorksheet.min_rowcCs>d}|jr:tdd|jD}t|}n|S)zLThe maximum row index containing data (1-based) :type: int r!css|]}|dVqdS)rNr8)r6r7r8r8r9r:sz$Worksheet.max_row..)rTrr)rMrrr8r8r9rs  zWorksheet.max_rowcCs>d}|jr:tdd|jD}t|}n|S)zOThe minimum column index containing data (1-based) :type: int r!css|]}|dVqdS)r!Nr8)r6r7r8r8r9r:sz'Worksheet.min_column..)rTrr)rMrrr8r8r9 min_columns  zWorksheet.min_columncCs>d}|jr:tdd|jD}t|}n|S)zOThe maximum column index containing data (1-based) :type: int r!css|]}|dVqdS)r!Nr8)r6r7r8r8r9r:sz'Worksheet.max_column..)rTrr)rMrrr8r8r9rs  zWorksheet.max_columnc Cs|jrt}t}x1|jD]&\}}|j||j|q%Wt|}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)rTraddrrr) rMrrr<colrrrrr8r8r9calculate_dimensions        zWorksheet.calculate_dimensioncCs |jS)z1Returns the result of :func:`calculate_dimension`)r)rMr8r8r9 dimensionsszWorksheet.dimensionscCs|dk r7tdt|j\}}}}n|jdkrct||||g rcfS|pld}|pxd}|p|j}|p|j}|dk r||7}n|dk r||7}n|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 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!)rrupperrbrrr _cells_by_row)rM range_stringrrrr row_offsetZ column_offsetr8r8r9rs""  !(       zWorksheet.iter_rowsc#sPxIt||dD]4tfddt||dDVqWdS)Nr!c3s'|]}jdd|VqdS)r<rN)r)r6r)r<rMr8r9r:sz*Worksheet._cells_by_row..)r r)rMrrrrr8)r<rMr9rszWorksheet._cells_by_rowcCs |jS)zfProduces all cells in the worksheet, by row (see :func:`iter_rows`) :type: generator )r)rMr8r8r9rszWorksheet.rowsccs3x,|jD]}tdd|DVq WdS)zTProduces all cell values in the worksheet, by row :type: generator css|]}|jVqdS)N)r5)r6r7r8r8r9r:sz#Worksheet.values..N)rr)rMr<r8r8r9rszWorksheet.valuescCsx|jdkr,t||||g r,fS|p5d}|pAd}|pP|j}|p_|j}|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!)rbrrr _cells_by_col)rMrrrrr8r8r9rs(  zWorksheet.iter_colsc#sPxIt||dD]4tfddt||dDVqWdS)z% Get cells by column r!c3s'|]}jd|dVqdS)r<rN)r)r6r<)rrMr8r9r:Dsz*Worksheet._cells_by_col..N)r r)rMrrrrr8)rrMr9r?szWorksheet._cells_by_colcCs |jS)zGProduces all cells in the worksheet, by column (see :func:`iter_cols`))r)rMr8r8r9columnsHszWorksheet.columnszg Use ws.iter_rows() or ws.iter_cols() depending whether you want rows or columns returned. cCs|j||||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 )r)rMrrrrr8r8r9get_squared_rangeNszWorksheet.get_squared_rangez=Ranges are workbook objects. Use wb.defined_names[range_name]c Cs|jj|}|jrR|j|jj|krRdj|}t|n|jdkrdj|}t|ng}x|jD]z\}}|j|}||krt dn||}t |t r|fg}nx|D]} |j | qWqWt |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)rNZ defined_namesZ localSheetIdrrKeyErrortype NameErrorZ destinationsrrrextendr) rMZ range_nameZdefnmsgresultrOZ cells_rangewsrr<r8r8r9get_named_rangehs$ $    zWorksheet.get_named_rangecCsS||j_||j|jfkrCtd|j|jfn||j_dS)zSet printer settings zValues should be %s or %sN)r\Z paperSizeORIENTATION_PORTRAITORIENTATION_LANDSCAPErrd)rMrcrdr8r8r9set_printer_settingss zWorksheet.set_printer_settingscCs|jj|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)r[append)rMZdata_validationr8r8r9add_data_validationszWorksheet.add_data_validationcCs,|dk r||_n|jj|dS)zd Add a chart to the sheet Optionally provide a cell for the top-left anchor N)anchorrUr)rMZchartrr8r8r9 add_charts  zWorksheet.add_chartcCs,|dk r||_n|jj|dS)zf Add an image to the sheet. Optionally provide a cell for the top-left anchor N)rrVr)rMZimgrr8r8r9 add_images  zWorksheet.add_imagecCs|jj|dS)N)rYr)rMtabler8r8r9 add_tableszWorksheet.add_tablecCs|jj|dS)N)rZr)rMZpivotr8r8r9 add_pivotszWorksheet.add_pivotc Cstd|d|d|d|d|}|jj|j|j\}}} } t|| d} t|| d} t| | } x6t| ddD]"}||jkr|j|=qqWdS)Nrrrrrr!) r2rXrcoordZboundsr rrrT)rMr start_row start_columnend_row end_columncrrrrrrrcellsr7r8r8r9 merge_cellsszWorksheet.merge_cellszUse ws.merged_cells.rangescCs|jjddS)zReturn a copy of cell rangesN)rXranges)rMr8r8r9merged_cell_rangesszWorksheet.merged_cell_rangesc Cshtd|d|d|d|d|}|j|jkrTtdj|jn|jj|dS)zC Remove merge on a cell range. Range is a cell range (e.g. A1:E1) rrrrrzCell range {0} is not mergedN)r2rrXrrremove)rMrrrrrrr8r8r9 unmerge_cellss zWorksheet.unmerge_cellsc Csw|jd}t|tttfs1t|rx6t|dD]\}}t|tr|}|jr|j|krt dn||_||_ ||_ nt|d|d|d|}||j ||fs z(Worksheet.print_area..N)rr r_)rMr5r8r8r9rs )U__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_A5rrrKrLpropertyrfrirkrlrnror{setterr rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrSrRrrrrrr8r8r8r9r>Ts  &  #  )      7   % #     0       r>)N __future__r itertoolsrroperatorrreinspectrwarningsrZopenpyxl.compatrr r r r Zopenpyxl.utilsr rrrrrrZopenpyxl.utils.cellrZ openpyxl.cellrZopenpyxl.utils.exceptionsrrZopenpyxl.utils.unitsrrrZopenpyxl.formatting.formattingrZopenpyxl.packaging.relationshiprZopenpyxl.workbook.childrZopenpyxl.workbook.defined_namerrZopenpyxl.utils.bound_dictionaryr Zdatavalidationr"Zpager#r$r%rr&r'r(r)rar*filtersr+r,r`r-r.r/r0Z cell_ranger1r2Z propertiesr3Z pagebreakr4r=r>r8r8r8r9s6 ( 4 ""