U [eLP@sdZddlZddlmZddlmZddlmZddlm Z ddl m Z ddl mZdd lmZGd d d eZGd d d eZddZdS)z pyexcel.sheet ~~~~~~~~~~~~~~~~~~~~~ Building on top of matrix, adding named columns and rows support :copyright: (c) 2014-2022 by Onni Software Ltd. :license: New BSD License, see LICENSE for more details N) defaultdict)_compact) constants) OrderedDict)Row)Column)Matrixc@sreZdZdZdejddddddfddZdejddddddfddZd d Zd d Z d dZ ddZ ddZ ddZ dJddZdKddZeddZejddZeddZejddZd d!Zd"d#Zd$d%Zd&d'Zd(d)Zd*d+Zd,d-Zd.d/Zd0d1Zd2d3Zd4d5Zd6d7ZdLd8d9Z dMd:d;Z!dNdd?Z#d@dAZ$edBdCZ%dDdEZ&dFdGZ'dHdIZ(dS)OSheetaLTwo dimensional data container for filtering, formatting and iteration :class:`~pyexcel.Sheet` is a container for a two dimensional array, where individual cell can be any Python types. Other than numbers, value of these types: string, date, time and boolean can be mixed in the array. This differs from Numpy's matrix where each cell are of the same number type. In order to prepare two dimensional data for your computation, formatting functions help convert array cells to required types. Formatting can be applied not only to the whole sheet but also to selected rows or columns. Custom conversion function can be passed to these formatting functions. For example, to remove extra spaces surrounding the content of a cell, a custom function is required. Filtering functions are used to reduce the information contained in the array. :ivar name: sheet name. use to change sheet name :ivar row: access data row by row :ivar column: access data column by column Example:: >>> import pyexcel as p >>> content = {'A': [[1]]} >>> b = p.get_book(bookdict=content) >>> b A: +---+ | 1 | +---+ >>> b[0].name 'A' >>> b[0].name = 'B' >>> b B: +---+ | 1 | +---+ NFc Cs6g|_g|_d|_d|_|j||||||||ddS)aConstructor :param sheet: two dimensional array :param name: this becomes the sheet name. :param name_columns_by_row: use a row to name all columns :param name_rows_by_column: use a column to name all rows :param colnames: use an external list of strings to name the columns :param rownames: use an external list of strings to name the rows r )sheetnamename_columns_by_rowname_rows_by_columncolnamesrownamestranspose_beforetranspose_afterN)_Sheet__column_names_Sheet__row_names_Sheet__row_index_Sheet__column_indexinit selfr r r rrrrrrO/var/www/html/services/stratfitenv/lib/python3.8/site-packages/pyexcel/sheet.py__init__@szSheet.__init__c Cs|dkr g}t||||_g|_g|_|r6|t||_t||_ |dkrl|r`t t j | |n |rv||_|dkr|rt t j ||n |r||_|r|dS)acustom initialization functions examples:: >>> import pyexcel as pe >>> data = [[1, 2, 3], [4, 5, 6], [7, 8, 9]] >>> sheet = pe.Sheet(data) >>> sheet.row[1] [4, 5, 6] >>> sheet.row[0:3] [[1, 2, 3], [4, 5, 6], [7, 8, 9]] >>> sheet.row += [11, 12, 13] >>> sheet.row[3] [11, 12, 13] >>> sheet.row[0:4] = [0, 0, 0] # set all to zero >>> sheet.row[3] [0, 0, 0] >>> sheet.row[0] = ['a', 'b', 'c'] # set one row >>> sheet.row[0] ['a', 'b', 'c'] >>> del sheet.row[0] # delete first row >>> sheet.row[0] # now, second row becomes the first [0, 0, 0] >>> del sheet.row[0:] >>> sheet.row[0] # nothing left Traceback (most recent call last): ... IndexError Nr )rrr rr transposeNamedRowrow NamedColumncolumnNotImplementedErrorrZMESSAGE_NOT_IMPLEMENTED_02r rrrrrrcs0)       z Sheet.initcCs tt||j|jd}|S)N)r r)r copydeepcopyZget_internal_arrayrr)rZ new_sheetrrrclones  z Sheet.clonecCs|SN)r%)rmemorrr __deepcopy__szSheet.__deepcopy__cCs |j|j|_|_t|dSr&)rrrrrrrrrs zSheet.transposecCs"||_t|||_|j|=dS)zUse the elements of a specified row to represent individual columns The specified row will be deleted from the data :param row_index: the index of the row that has the column names N)rmake_names_uniquerow_atrr)rZ row_indexrrrr szSheet.name_columns_by_rowcCs"||_t|||_|j|=dS)zUse the elements of a specified column to represent individual rows The specified column will be deleted from the data :param column_index: the index of the column that has the row names N)rr* column_atrr!)r column_indexrrrrszSheet.name_rows_by_columncCsddlm}tt}t|tr@|D]}||||q&nlt|j dkrX| d|j |}| D]>}t|||dkr||||j ||||ql||S)aGroup rows with similiar column into a two dimensional array. Example:: >>> import pyexcel as p >>> sample_data = [ ... ["22/09/2017", "morning"], ... ["22/09/2017", "afternoon"], ... ["23/09/2017", "morning"], ... ["23/09/2017", "afternoon"] ... ] >>> sheet = p.Sheet(sample_data) >>> sheet.group_rows_by_column(0) 22/09/2017: +------------+-----------+ | 22/09/2017 | morning | +------------+-----------+ | 22/09/2017 | afternoon | +------------+-----------+ 23/09/2017: +------------+-----------+ | 23/09/2017 | morning | +------------+-----------+ | 23/09/2017 | afternoon | +------------+-----------+ :returns: an instance of a Book r)Book) pyexcelr.rlist isinstanceintto_arrayappendlenrr indexrows)rZcolumn_index_or_namer.groupsrr-rrrgroup_rows_by_columns      zSheet.group_rows_by_columncCs,t|jd|}t|jdkr(|j|_|S)z) Preview top most 5 rows Nr)r rr5rr)rlinesr rrrtopsz Sheet.topcCs\t|d||f}t|jdkr6|jd|}||_t|jdkrX|jd|}||_|S)z) Preview top corner: 5x5 )rrrN)r regionr5rrrr)rr7columnsr=rZ columnnamesrrrtop_leftszSheet.top_leftcCs|jS)zReturn column names if anyrr)rrrrszSheet.colnamescCst||_dS)zSet column namesN)r*rrvaluerrrrscCs|jS)zReturn row names if anyrr)rrrrszSheet.rownamescCst||_dS)z Set row namesN)r*rrArrrr!scCs,|}tt|r|j|}||}|S)zGet a column by its name)compact is_stringtyperr6r,)rr r6 column_arrayrrrnamed_column_at&s   zSheet.named_column_atcCs.|}tt|r|j|}|||dS)z Take the first row as column names Given name to identify the column index, set the column to the given array except the column name. N)rDrErFrr6Z set_column_at)rr rGr6rrrset_named_column_at.s zSheet.set_named_column_atcsDttjdkr@fddtdtjD}|_dS)zaDelete one or more columns :param list column_indices: a list of column indices rcsg|]}|krj|qSrr@.0icolumn_indicesrrr Asz(Sheet.delete_columns..N)rdelete_columnsr5rrange)rrN new_seriesrrMrrP:s   zSheet.delete_columnscsDttjdkr@fddtdtjD}|_dS)zXDelete one or more rows :param list row_indices: a list of row indices rcsg|]}|krj|qSrrCrJ row_indicesrrrrOOsz%Sheet.delete_rows..N)r delete_rowsr5rrQ)rrTrRrrSrrUHs   zSheet.delete_rowscCs\t|tr2t|jdkr$|j|||gn&|j|}|j|t||gdS)zWorks only after you named columns by a row Given name to identify the column index, set the column to the given array except the column name. :param str name: a column name rN) r1r2r5rpoprPrr6rrr r6rrrdelete_named_column_atVs    zSheet.delete_named_column_atcCs|}|j|}||}|S)zGet a row by its name )rr6r+)rr r6 row_arrayrrr named_row_atfs  zSheet.named_row_atcCs.|}tt|r|j|}|||dS)z Take the first column as row names Given name to identify the row index, set the row to the given array except the row name. N)rDrErFrr6Z set_row_at)rr rYr6rrrset_named_row_atms zSheet.set_named_row_atcCs\t|tr2t|jdkr$|j|||gn&|j|}|j|t||gdS)zTake the first column as row names Given name to identify the row index, set the row to the given array except the row name. rN)r1r2r5rrVrUr6rrWrrrdelete_named_row_atys    zSheet.delete_named_row_atcCstg}t|tjrJ|}|D]}|j||||qt||n&t|jdkrdt t j n t||dS)zeTake ordereddict to extend named rows :param ordereddist/list rows: a list of rows. rN) r1rDrkeysrr4r extend_rowsr5 TypeErrorr*MESSAGE_DATA_ERROR_ORDEREDDICT_IS_EXPECTED)rr7 incoming_datar]krrrr^s  zSheet.extend_rowscCs8t|jdkr(||j}|j|7_t||dS)z+Put rows on the right most side of the datarN)r5rrVrrrextend_columns_with_rows)rr7headersrrrrcs zSheet.extend_columns_with_rowscCstg}t|tjrJ|}|D]}|j||||qt||n&t|jdkrdt t j n t||dS)zmTake ordereddict to extend named columns :param ordereddist/list columns: a list of columns rN) r1rDrr]rr4rextend_columnsr5r_rr`)rr>rar]rbrrrres  zSheet.extend_columnscCsg}|t|7}t|jdkrFddt|j|D}tjsFt|}t|jdkrt|jdkrz|dt j g|jn|d|j|S)z Returns an array after filteringrcSsg|]}|dg|dqS)rr)rKrBrrrrOsz"Sheet.to_array..) r0r7r5rziprDPY2rinsertrZ DEFAULT_NA)rretrrrr3szSheet.to_arrayccst|jdkrD|r|}n|j}|D]}tt||}|Vq&nNt|jdkr|r\|}n|j}|D]}tt||}|Vqjn tt j dS)z Make an array of dictionaries It takes the first row as keys and the rest of the rows as values. Then zips keys and row values per each row. This is particularly helpful for database operations. rN) r5rr7rDrrgrr> ValueErrorrZMESSAGE_DATA_ERROR_NO_SERIES)rZcustom_headersrdrthe_dictr!rrr to_recordss     zSheet.to_recordscCsrddlm}|}t}|rB|D]}||kr&||||<q&n|D]}||||<qF||d}t||jddS)a Rearrange the sheet. :ivar new_ordered_columns: new columns :ivar exclusion: to exlucde named column or not. defaults to False Example:: >>> sheet = Sheet( ... [["A", "B", "C"], [1, 2, 3], [11, 22, 33], [111, 222, 333]], ... name_columns_by_row=0) >>> sheet.project(["B", "A", "C"]) pyexcel sheet: +-----+-----+-----+ | B | A | C | +=====+=====+=====+ | 2 | 1 | 3 | +-----+-----+-----+ | 22 | 11 | 33 | +-----+-----+-----+ | 222 | 111 | 333 | +-----+-----+-----+ >>> sheet.project(["B", "C"]) pyexcel sheet: +-----+-----+ | B | C | +=====+=====+ | 2 | 3 | +-----+-----+ | 22 | 33 | +-----+-----+ | 222 | 333 | +-----+-----+ >>> sheet.project(["B", "C"], exclusion=True) pyexcel sheet: +-----+ | A | +=====+ | 1 | +-----+ | 11 | +-----+ | 111 | +-----+ r) get_array)adict)r r )r/rnto_dictrr]r r )rZnew_ordered_columnsZ exclusionrnrlZnew_dictr!arrayrrrprojects/   z Sheet.projectcCslt}t|jdkr8|dkr8|D]}||q&n0t|jdkr`|D]}||qNntd|S)zReturns a dictionaryrFzNot implemented) rDrr5r named_columnsupdater named_rowsr")rrrlr!rrrrp s  z Sheet.to_dictc csD|jD]8}z||j|iVWqtk r<|giVYqXqdS)ziterate rows using row namesN)rr IndexError)rZrow_namerrrru-s  zSheet.named_rowsc csD|jD]8}z||j|iVWqtk r<|giVYqXqdS)ziterate rows using column namesN)rr!rv)rZ column_namerrrrs5s  zSheet.named_columnscCs|jdd}t|S)z6 Plain representation without headers F)Z write_title)Z get_texttable_RepresentedString)rcontentrrrrx=s z Sheet.contentcCsvt|trft|dtr*|j|d}n|d}t|dtrR|j|d}n|d}|||St||SdSNrrf) r1tuplestrrr6r cell_valuer __getitem__)rasetrr!rrrr}Gs  zSheet.__getitem__cCs|t|trjt|dtr*|j|d}n|d}t|dtrR|j|d}n|d}||||nt|||dSry) r1rzr{rr6rr|r __setitem__)rr~crr!rrrrVs zSheet.__setitem__cCs|Sr&)Znumber_of_rowsr)rrr__len__esz Sheet.__len__)r:)r:r:)N)F)F))__name__ __module__ __qualname____doc__rZ DEFAULT_NAMErrr%r(rr rr9r<r?propertyrsetterrrHrIrPrUrXrZr[r\r^rcrer3rmrrrprursrxr}rrrrrrr sr, % D  .         >  r c@s(eZdZdZddZddZddZdS) rwzpresent in textcCs ||_dSr&text)rrrrrrlsz_RepresentedString.__init__cCs|jSr&rr)rrr__repr__osz_RepresentedString.__repr__cCs|jSr&rr)rrr__str__rsz_RepresentedString.__str__N)rrrrrrrrrrrrwisrwcCsvi}g}|D]d}tt|s&t|}|}||kr^||d||<|d|||fq d||<||q |S)z3Append the number of occurences to duplicated namesrfz%s-%dr)rDrErFr{stripr4)alist duplicatesZ new_namesitemrrrr*vs r*)rr# collectionsrr/rrDrZpyexcel._compactrZpyexcel.internal.sheets.rowrrZpyexcel.internal.sheets.columnrr Zpyexcel.internal.sheets.matrixrr objectrwr*rrrrs        X