
    3fiSI                       d Z ddlZddlZddlZddlmZ ddlmZ ddl	m
Z
mZmZmZ  ej                  e      ZdZdZd	Z e       Z G d
 de      Z G d de
      Z G d d      Z G d de
      Z G d de
      Z G d de
      Z G d de
      Z G d de
      Z G d de
      Z G d de
      Z  G d de
      Z! G d  d!e
      Z"y)"z
2019-04-15
Note: Support for workbooks stored in OneDrive Consumer platform is still not available.
At this time, only the files stored in business platform is supported by Excel REST APIs.
    N)quote   )File)ApiComponent
TrackerSetto_snake_casecol_index_to_labeli  i,  zAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheetc                       e Zd Zy)FunctionExceptionN)__name__
__module____qualname__     H/var/www/auto_recruiter/arenv/lib/python3.12/site-packages/O365/excel.pyr   r      s    r   r   c                        e Zd ZdZddddZdddd fd	
Zd
 Zd Zd Zd Z	d Z
d Zd Zd Zd Zd Zd Zd Z xZS )WorkbookSessionzu
    See https://docs.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0#sessions-and-persistence
    z/createSessionz/refreshSessionz/closeSession)create_sessionrefresh_sessionclose_sessionNT)parentconpersistc                   |r|rt        d      |r|j                  n|| _        |j                  dd      xs |rt        |dd      nd}t        |   |r|j                  n|j                  d      |       || _        |rt        j                  t              nt        j                  t              | _        d| _        t        j                  j!                         | _        y)zCreate a workbook session object.

        :param parent: parent for this operation
        :param Connection con: connection to use if no parent specified
        :param Bool persist: Whether or not to persist the session changes
        z*Need a parent or a connection but not bothmain_resourceNprotocolr   r   )seconds)
ValueErrorr   popgetattrsuper__init__r   getr   dt	timedelta%PERSISTENT_SESSION_INACTIVITY_MAX_AGE)NON_PERSISTENT_SESSION_INACTIVITY_MAX_AGEinactivity_limit
session_iddatetimenowlast_activity)selfr   r   r   kwargsr   	__class__s         r   r#   zWorkbookSession.__init__-   s     cIJJ!'6::S 

?D9 
6<GFOT2$ 	 	(.V__FJJz4J' 	 	
 
  LL!FG&OP 	 [[__.r   c                 "    | j                         S N__repr__r.   s    r   __str__zWorkbookSession.__str__P       }}r   c                 @    dj                  | j                  xs d      S )NzWorkbook Session: {}Not set)formatr*   r5   s    r   r4   zWorkbookSession.__repr__S   s    %,,T__-I	JJr   c                     | j                   d uS r2   )r*   r5   s    r   __bool__zWorkbookSession.__bool__V   s    d**r   c                    | j                  | j                  j                  d            }| j                  j	                  |d| j
                  i      }|st        d      |j                         }|j                  d      | _        y)zRequest a new session idr   persistChangesdataz2Could not create session as requested by the user.idT)		build_url
_endpointsr$   r   postr   RuntimeErrorjsonr*   r.   urlresponser@   s       r   r   zWorkbookSession.create_sessionY   sn     nnT__001ABC88==,<dll+K=LSTT}}((4.r   c                     | j                   r^| j                  | j                  j                  d            }| j                  j                  |d| j                   i      }t        |      S y)zRefresh the current session idr   workbook-session-idheadersFr*   rB   rC   r$   r   rD   boolr.   rH   rI   s      r   r   zWorkbookSession.refresh_sessione   s^     ??..!4!45F!GHCxx}}3T__E % H >!r   c                     | j                   r^| j                  | j                  j                  d            }| j                  j                  |d| j                   i      }t        |      S y)zClose the current sessionr   rK   rL   FrN   rP   s      r   r   zWorkbookSession.close_sessionp   s]     ??..!4!4_!EFCxx}}3T__E % H >!r   c                 ~   | j                   t        j                  j                         }| j                  | j
                  z   |k  rF| j                  r/| j                          t        j                  j                         }nt        d      || _        |j                  d      }|i x|d<   }| j                   |d<   yy)ziIf session is in use, prepares the request headers and
        checks if the session is expired.
        NzA non Persistent Session is expired. For consistency reasons this exception is raised. Please try again with manual refresh of the session rM   rK   )
r*   r%   r+   r,   r-   r)   r   r   rE   r$   )r.   r/   actualrM   s       r   prepare_requestzWorkbookSession.prepare_request{   s     ??&[[__&F""T%:%::fD<<'')[[__.F 'O 
 "(Djj+G.00y!G-1__G)*+ 'r   c                 \    | j                  |        | j                  j                  |i |S r2   )rT   r   r$   r.   argsr/   s      r   r$   zWorkbookSession.get   *    V$txx||T,V,,r   c                 \    | j                  |        | j                  j                  |i |S r2   )rT   r   rD   rV   s      r   rD   zWorkbookSession.post   s*    V$txx}}d-f--r   c                 \    | j                  |        | j                  j                  |i |S r2   )rT   r   putrV   s      r   r[   zWorkbookSession.put   rX   r   c                 \    | j                  |        | j                  j                  |i |S r2   )rT   r   patchrV   s      r   r]   zWorkbookSession.patch   s*    V$txx~~t.v..r   c                 \    | j                  |        | j                  j                  |i |S r2   )rT   r   deleterV   s      r   r_   zWorkbookSession.delete   s*    V$txx///r   )r   r   r   __doc__rC   r#   r6   r4   r<   r   r   r   rT   r$   rD   r[   r]   r_   __classcell__r0   s   @r   r   r   "   sf    
 +,(J "&4 !/FK+
		=6-.-/0r   r   c                   \   e Zd ZdZd Zd ZddZed        Zej                  d        Zed        Z
e
j                  d	        Z
ed
        Zej                  d        Zed        Zej                  d        Zed        Zej                  d        Zed        Zej                  d        Zy)RangeFormatFontz A font format applied to a rangec                     || _         t        |j                        | _        d| _        d| _        d| _        d| _        d| _        d| _	        d| _
        y )NcasingF#000000Calibri
   None)r   r   _cc_track_changes_loaded_bold_color_italic_name_size
_underline)r.   r   s     r   r#   zRangeFormatFont.__init__   sK    (

;


 r   c                    | j                   j                  | j                   j                  j                  d            }| j                   j                  j                  |      }|sy|j                         }|j                  dd      | _        |j                  dd      | _        |j                  dd      | _        |j                  dd      | _	        |j                  d	d
      | _
        |j                  dd      | _        d| _        y)z!Loads the data into this instancer:   Fboldcolorrh   italicnameri   sizerj   	underlinerk   T)r   rB   rC   r$   sessionrF   ro   rp   rq   rr   rs   rt   rn   rG   s       r   
_load_datazRangeFormatFont._load_data   s    kk##DKK$:$:$>$>x$HI;;&&**3/}}XXfe,
hhw	2xx%0XXfi0
XXfb)
((;7r   Nc                 ^   | j                   j                  } |d      | j                   |d      | j                   |d      | j                   |d      | j
                   |d      | j                   |d      | j                  i}|r&t        |j                               D ]
  }||vs||=  |S )Returns a dict to communicate with the server

        :param restrict_keys: a set of keys to restrict the returned data to
        :rtype: dict
        rv   rw   rx   ry   rz   r{   )
r   rl   ro   rp   rq   rr   rs   rt   listkeysr.   restrict_keysccr@   keys        r   to_api_datazRangeFormatFont.to_api_data   s     [[__vJ

wKxL$,,vJ

vJ

{OT__
 DIIK( "m+S	" r   c                 R    | j                   s| j                          | j                  S r2   )rn   r}   ro   r5   s    r   rv   zRangeFormatFont.bold   s    ||OOzzr   c                 H    || _         | j                  j                  d       y )Nrv   )ro   rm   addr.   values     r   rv   zRangeFormatFont.bold       
'r   c                 R    | j                   s| j                          | j                   S )z}The color of the range format font

        :getter: get the color
        :setter: set the color
        :type: str
        )rp   r}   r5   s    r   rw   zRangeFormatFont.color   s     {{OO{{r   c                 H    || _         | j                  j                  d       y )Nrw   )rp   rm   r   r   s     r   rw   zRangeFormatFont.color   s    (r   c                 R    | j                   s| j                          | j                  S )z}Is range format font in italics

        :getter: get the italic
        :setter: set the italic
        :type: bool
        )rn   r}   rq   r5   s    r   rx   zRangeFormatFont.italic   s     ||OO||r   c                 H    || _         | j                  j                  d       y )Nrx   )rq   rm   r   r   s     r   rx   zRangeFormatFont.italic  s    )r   c                 R    | j                   s| j                          | j                  S )zzThe name of the range format font

        :getter: get the name
        :setter: set the name
        :type: str
        )rn   r}   rr   r5   s    r   ry   zRangeFormatFont.name       ||OOzzr   c                 H    || _         | j                  j                  d       y )Nry   )rr   rm   r   r   s     r   ry   zRangeFormatFont.name  r   r   c                 R    | j                   s| j                          | j                  S )zzThe size of the range format font

        :getter: get the size
        :setter: set the size
        :type: int
        )rn   r}   rs   r5   s    r   rz   zRangeFormatFont.size!  r   r   c                 H    || _         | j                  j                  d       y )Nrz   )rs   rm   r   r   s     r   rz   zRangeFormatFont.size-  r   r   c                 R    | j                   s| j                          | j                  S )zIs range format font underlined

        :getter: get the underline
        :setter: set the underline
        :type: bool
        )rn   r}   rt   r5   s    r   r{   zRangeFormatFont.underline2  s     ||OOr   c                 H    || _         | j                  j                  d       y )Nr{   )rt   rm   r   r   s     r   r{   zRangeFormatFont.underline>      ,r   r2   )r   r   r   r`   r#   r}   r   propertyrv   setterrw   rx   ry   rz   r{   r   r   r   rd   rd      s   *!$,  
 
[[( ( 	 	 \\) ) 	 	 ]]* * 	 	 
[[( ( 	 	 
[[( ( 	 	 - -r   rd   c                       e Zd ZdZdddddddZd fd		Zd
 Zd Zed        Z	e	j                  d        Z	ed        Zej                  d        Zed        Zej                  d        Zed        Zej                  d        Zed        Zej                  d        Zd dZd Zed        Zed        Zej                  d        Zd Zd Zd Zd!dZ xZS )"RangeFormatzA format applied to a rangez/bordersz/fontz/fillz/fill/clearz/autofitColumnsz/autofitRows)bordersfontfill
clear_fillauto_fit_columnsauto_fit_rowsc                    |r|rt        d      || _        |r|j                  n|| _        |j                  dd       xs |rt	        |dd       nd }dj                  |      }t        |   |r|j                  n|j                  d      |       t        | j                        | _        d| _        |j                  | j                  i       }|j                  dd	      | _        |j                  d
d      | _        |j                  dd      | _        |j                  dd      | _        |j                  dd       | _        t)        |       | _        t,        | _        y )N'Need a parent or a session but not bothr   z	{}/formatr   r   rf   FcolumnWidth   horizontalAlignmentGeneral	rowHeight   verticalAlignmentBottomwrapText)r   ranger|   r    r!   r:   r"   r#   r   r$   r   rl   rm   _track_background_color_cloud_data_key_column_width_horizontal_alignment_row_height_vertical_alignment
_wrap_textrd   _fontUnsetSentinel_background_color)r.   r   r|   r/   r   
cloud_datar0   s         r   r#   zRangeFormat.__init__P  s6   gFGG 
)/v~~W 

?D9 
6<GFOT2$ 	
 $**=9(.V__FJJz4J' 	 	

 )9',$ZZ 4 4b9
'^^M2>%/^^4I9%U"%>>+r:#->>2Ex#P $..T:$T*
!.r   c                 "    | j                         S r2   r3   r5   s    r   r6   zRangeFormat.__str__t  r7   r   c                 p    dj                  | j                  r| j                  j                        S d      S )NzFormat for range address: {}Unkknown)r:   r   addressr5   s    r   r4   zRangeFormat.__repr__w  s5    -44"&**DJJ
 	
2<
 	
r   c                     | j                   S )zThe width of all columns within the range

        :getter: get the column_width
        :setter: set the column_width
        :type: float
        )r   r5   s    r   column_widthzRangeFormat.column_width|  s     !!!r   c                 H    || _         | j                  j                  d       y )Nr   )r   rm   r   r   s     r   r   zRangeFormat.column_width  s    "/r   c                     | j                   S )a   The horizontal alignment for the specified object.
        Possible values are: General, Left, Center, Right, Fill, Justify,
        CenterAcrossSelection, Distributed.

        :getter: get the vertical_alignment
        :setter: set the vertical_alignment
        :type: string
        )r   r5   s    r   horizontal_alignmentz RangeFormat.horizontal_alignment  s     )))r   c                 H    || _         | j                  j                  d       y )Nr   )r   rm   r   r   s     r   r   z RangeFormat.horizontal_alignment  s    %*" 67r   c                     | j                   S )zThe height of all rows in the range.

        :getter: get the row_height
        :setter: set the row_height
        :type: float
        )r   r5   s    r   
row_heightzRangeFormat.row_height       r   c                 H    || _         | j                  j                  d       y )Nr   )r   rm   r   r   s     r   r   zRangeFormat.row_height       -r   c                     | j                   S )zThe vertical alignment for the specified object.
        Possible values are: Top, Center, Bottom, Justify, Distributed.

        :getter: get the vertical_alignment
        :setter: set the vertical_alignment
        :type: string
        )r   r5   s    r   vertical_alignmentzRangeFormat.vertical_alignment  s     '''r   c                 H    || _         | j                  j                  d       y )Nr   )r   rm   r   r   s     r   r   zRangeFormat.vertical_alignment  s    #(  45r   c                     | j                   S )zIndicates whether Excel wraps the text in the object

        :getter: get the wrap_text
        :setter: set the wrap_text
        :type: bool
        )r   r5   s    r   	wrap_textzRangeFormat.wrap_text  s     r   c                 H    || _         | j                  j                  d       y )Nr   )r   rm   r   r   s     r   r   zRangeFormat.wrap_text  r   r   c                 &   | j                   } |d      | j                   |d      | j                   |d      | j                   |d      | j                   |d      | j
                  i}|r&t        |j                               D ]
  }||vs||=  |S )r   r   r   r   r   r   )rl   r   r   r   r   r   r   r   r   s        r   r   zRangeFormat.to_api_data  s     XX~ 2 2%&(B(B|d..#$d&>&>{OT__
 DIIK( "m+S	" r   c                    | j                   rg| j                  | j                         }|rI| j                  j                  | j	                  d      |      }|sy| j                   j                          | j                  j                   r| j                  j                  | j                  j                         }|rl| j                  j                  | j	                  | j                  j                  d            |      }|sy| j                  j                   j                          | j                  r| j                  F| j	                  | j                  j                  d            }| j                  j                  |      }nUd| j                  i}| j	                  | j                  j                  d            }| j                  j                  ||      }|syd| _	        y	)
zUpdates this range formatr    r?   Fr   r   rw   r   T)rm   r   r|   r]   rB   clearr   rC   r$   r   r   rD   )r.   r@   rI   rH   s       r   updatezRangeFormat.update  s   ##$2E2E#FD<<--dnnR.@t-L ##))+::$$::))

8Q8Q)RD<<--NN4??#6#6v#>?d .    

))//1''%%-nnT__%8%8%FG<<,,S1!7!78nnT__%8%8%@A<<--c-=+0D(r   c                     | j                   S )zReturns the font object defined on the overall range selected

        :getter: get the font
        :setter: set the font
        :type: RangeFormatFont
        )r   r5   s    r   r   zRangeFormat.font  s     zzr   c                 ^    | j                   t        u r| j                          | j                   S )zThe background color of the range

        :getter: get the background_color
        :setter: set the background_color
        :type: UnsentSentinel
        )r   r   _load_background_colorr5   s    r   background_colorzRangeFormat.background_color	  s*     !!]2'')%%%r   c                      || _         d| _        y )NT)r   r   r   s     r   r   zRangeFormat.background_color  s    !&'+$r   c                     | j                  | j                  j                  d            }| j                  j                  |      }|sy|j	                         }|j                  dd      | _        y)z(Loads the data related to the fill colorr   Nrw   )rB   rC   r$   r|   rF   r   rG   s       r   r   z"RangeFormat._load_background_color  sW    nnT__0089<<##C(}}!%'4!8r   c                     | j                  | j                  j                  d            }t        | j                  j                  |            S )zChanges the width of the columns of the current range
        to achieve the best fit, based on the current data in the columns
        r   rB   rC   r$   rO   r|   rD   r.   rH   s     r   r   zRangeFormat.auto_fit_columns#  s<     nnT__001CDEDLL%%c*++r   c                     | j                  | j                  j                  d            }t        | j                  j                  |            S )zChanges the width of the rows of the current range
        to achieve the best fit, based on the current data in the rows
        r   r   r   s     r   r   zRangeFormat.auto_fit_rows*  s;     nnT__00ABDLL%%c*++r   c                      y)zSets the border of this rangeNr   )r.   
side_styles     r   set_borderszRangeFormat.set_borders1  s    r   NNr2   )r   )r   r   r   r`   rC   r#   r6   r4   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   ra   rb   s   @r   r   r   D  sx   % #-'J"/H

 " " 0 0 	* 	*   8 !8     . . ( ( 6 6   - -*@   	& 	& , ,9,,r   r   c                       e Zd ZdZi dddddddd	d
dddddddddddddddddddddd d!d"d#d$d%d&d'd(d)ZeZdX fd*	Zd+ Zd, Z	d- Z
ed.        Zej                  d/        Zed0        Zej                  d1        Zed2        Zej                  d3        Zed4        Zej                  d5        Zed6        Zej                  d7        Zed8        Zej                  d9        Zed:        Zej                  d;        ZdYd<Zd=d>d?Zd@ ZdA ZdB ZdZdCZdZdDZdE ZdF ZdG ZdH ZdI ZdJ Z dK Z!dZdLZ"dZdMZ#d[dNZ$d\dOZ%d]dPZ&dQ Z'd^dRZ(dS Z)dT Z*dU Z+dV Z,dW Z- xZ.S )_RangezAn Excel Rangeget_cellz/cell(row={},column={})
get_columnz/column(column={})get_bounding_rectz/boundingRectcolumns_afterz/columnsAfter(count={})columns_beforez/columnsBefore(count={})entire_columnz/entireColumnintersectionz/intersection	last_cellz	/lastCelllast_columnz/lastColumnlast_rowz/lastRowoffset_rangez/offsetRangeget_rowz/row
rows_abovez/rowsAbove(count={})
rows_belowz/rowsBelow(count={})get_used_range/usedRange(valuesOnly={})clear_rangez/cleardelete_range/deletez/insertz/mergez/unmergez,/resizedRange(deltaRows={}, deltaColumns={})z/format)insert_rangemerge_rangeunmerge_rangeget_resized_range
get_formatc                    |r|rt        d      |r|j                  n|| _        |j                  | j                  i       }|j                  dd       | _        |j                  dd       xs |rt        |dd       nd }t        |t              r|j                  d      d   }t        |t        t        f      rWd| j                  v r#| j                  j                  d      d   | _        dj                  |t        | j                              }nd	j                  |      }t        | 9  |r|j                  n|j                  d
      |       t!        | j"                        | _        |j                  dd      | _        |j                  dd      | _        |j                  dd      | _        |j                  dd      | _        |j                  dd      | _        |j                  dd      | _        |j                  dd      | _        |j                  dd      | _        |j                  dd      | _        |j                  dg g      | _        |j                  dg g      | _        |j                  dg g      | _        |j                  dd      | _        |j                  dg g      | _         |j                  dg g      | _!        |j                  dg g      | _"        |j                  dg g      | _#        y )Nr   r   r   /ranger   !r   z{}/range(address='{}')z{}/ranger   r   rf   r   addressLocalcolumnCountrowCount	cellCountcolumnHiddenFcolumnIndex	rowHiddenrowIndexformulasformulasLocalformulasR1C1hiddennumberFormattext
valueTypesvalues)$r   r|   r$   r   	object_idr    r!   
isinstancer   split	WorkSheetr:   r   r"   r#   r   r   rl   rm   r   address_localcolumn_count	row_count
cell_count_column_hiddencolumn_index_row_hidden	row_index	_formulas_formulas_local_formulas_r1_c1r  _number_formatr  value_types_valuesr.   r   r|   r/   r   r   r0   s         r   r#   zRange.__init__S  s   gFGG)/v~~WZZ 4 4b9
 $	48 

?D9 
6<GFOT2$ 	
 fe$)//9!<Mfy%01dnn$!%!5!5c!:1!=4;;uT^^4M '--m<M(.V__FJJz4J' 	 	

 )9
 "~~i4 (^^NB?&NN=!<#
A6$..a8(nn^UC 'NN=!<%>>+u= $
A6#
RD9)~~otD)~~nrdC nnXu5(nn^bTBNN6B40	 &>>,=!~~h5r   c                 "    | j                         S r2   r3   r5   s    r   r6   zRange.__str__  r7   r   c                 8    dj                  | j                        S )NzRange address: {})r:   r   r5   s    r   r4   zRange.__repr__  s    "))$,,77r   c                 4    | j                   |j                   k(  S r2   r  r.   others     r   __eq__zRange.__eq__      ~~00r   c                     | j                   S )zIndicates whether all columns of the current range are hidden.

        :getter: get the column_hidden
        :setter: set the column_hidden
        :type: bool
        )r  r5   s    r   column_hiddenzRange.column_hidden       """r   c                 H    || _         | j                  j                  d       y )Nr,  )r  rm   r   r   s     r   r,  zRange.column_hidden      #0r   c                     | j                   S )zIndicates whether all rows of the current range are hidden.

        :getter: get the row_hidden
        :setter: set the row_hidden
        :type: bool
        )r  r5   s    r   
row_hiddenzRange.row_hidden  r   r   c                 H    || _         | j                  j                  d       y )Nr1  )r  rm   r   r   s     r   r1  zRange.row_hidden  r   r   c                     | j                   S )zRepresents the formula in A1-style notation.

        :getter: get the formulas
        :setter: set the formulas
        :type: any
        )r  r5   s    r   r  zRange.formulas  s     ~~r   c                 H    || _         | j                  j                  d       y )Nr  )r  rm   r   r   s     r   r  zRange.formulas  s    
+r   c                     | j                   S )a@  Represents the formula in A1-style notation, in the user's language
        and number-formatting locale. For example, the English "=SUM(A1, 1.5)"
        formula would become "=SUMME(A1; 1,5)" in German.

        :getter: get the formulas_local
        :setter: set the formulas_local
        :type: list[list]
        )r  r5   s    r   formulas_localzRange.formulas_local  s     ###r   c                 H    || _         | j                  j                  d       y )Nr6  )r  rm   r   r   s     r   r6  zRange.formulas_local      $ 01r   c                     | j                   S )zRepresents the formula in R1C1-style notation.

        :getter: get the formulas_r1_c1
        :setter: set the formulas_r1_c1
        :type: list[list]
        )r  r5   s    r   formulas_r1_c1zRange.formulas_r1_c1  s     ###r   c                 H    || _         | j                  j                  d       y )Nr:  )r  rm   r   r   s     r   r:  zRange.formulas_r1_c1  r8  r   c                     | j                   S )zRepresents Excel's number format code for the given cell.

        :getter: get the number_format
        :setter: set the number_fromat
        :type: list[list]
        )r  r5   s    r   number_formatzRange.number_format  r-  r   c                 H    || _         | j                  j                  d       y )Nr=  )r  rm   r   r   s     r   r=  zRange.number_format  r/  r   c                     | j                   S )a(  Represents the raw values of the specified range.
        The data returned can be of type string, number, or a Boolean.
        Cell that contains an error returns the error string.

        :getter: get the number_format
        :setter: set the number_fromat
        :type: list[list]
        )r!  r5   s    r   r  zRange.values   s     ||r   c                 p    t        |t              s|gg}|| _        | j                  j	                  d       y )Nr  )r  r   r!  rm   r   r   s     r   r  zRange.values  s0    %&WIE)r   c                 n   | j                   } |d      | j                   |d      | j                   |d      | j                   |d      | j                   |d      | j
                   |d      | j                   |d      | j                  i}|r&t        |j                               D ]
  }||vs||=  |S )r   r,  r1  r  r6  r:  r=  r  )
rl   r  r  r  r  r  r  r!  r   r   r   s        r   r   zRange.to_api_data  s     XX!4!4|d..zNDNN $"6"6 $"6"6!4!4xL$,,
 DIIK( "m+S	" r   GETmethodc                   |r8| j                   | j                  j                  |      j                  |       }n*| j                  | j                  j                  |            }|sd}|dk(  r| j                  j                  ||      }n"|dk(  r| j                  j                  ||      }sy | j                  dd| i| j                  |j                         iS )z!Helper that returns another rangeNrB  paramsPOSTr?   r   r   )	rB   rC   r$   r:   r|   rD   r0   r   rF   )r.   endpointrD  rW   r/   rH   rI   s          r   
_get_rangezRange._get_range*  s    ..!E!4!4X!>!E!Et!LMC..!4!4X!>?CFU?||''F';Hv||((6(:Ht~~UTUd.B.BHMMO-TUUr   c                 (    | j                  d||      S )z
        Gets the range object containing the single cell based on row and column numbers.
        :param int row: the row number
        :param int column: the column number
        :return: a Range instance
        r   rJ  )r.   rowcolumns      r   r   zRange.get_cell:  s     z377r   c                 &    | j                  d|      S )z
        Returns a column whitin the range
        :param int index: the index of the column. zero indexed
        :return: a Range
        r   rL  r.   indexs     r   r   zRange.get_columnC  s     |U33r   c                 (    | j                  d|      S )z
        Gets the smallest range object that encompasses the given ranges.
        For example, the GetBoundingRect of "B2:C5" and "D10:E15" is "B2:E16".
        :param str address: another address to retrieve it's bounding rect
        r   anotherRangerL  r.   r   s     r   r   zRange.get_bounding_rectK  s     2IIr   c                 *    | j                  d|d      S )z
        Gets a certain number of columns to the right of the given range.
        :param int columns: Optional. The number of columns to include in the resulting range.
        r   rH  rC  rL  r.   columnss     r   get_columns_afterzRange.get_columns_afterS  s    
 GGr   c                 *    | j                  d|d      S )z
        Gets a certain number of columns to the left  of the given range.
        :param int columns: Optional. The number of columns to include in the resulting range.
        r   rH  rC  rL  rW  s     r   get_columns_beforezRange.get_columns_beforeZ  s    
 /HHr   c                 $    | j                  d      S )z<Gets a Range that represents the entire column of the range.r   rL  r5   s    r   get_entire_columnzRange.get_entire_columna  s    //r   c                 *    | j                  d|       y)z
        Gets the Range that represents the rectangular intersection of the given ranges.

        :param address: the address range you want ot intersect with.
        :return: Range
        r   rS  NrL  rU  s     r   get_intersectionzRange.get_intersectione  s     	W=r   c                 $    | j                  d      S )z$Gets the last cell within the range.r   rL  r5   s    r   get_last_cellzRange.get_last_celln  s    {++r   c                 $    | j                  d      S )z&Gets the last column within the range.r   rL  r5   s    r   get_last_columnzRange.get_last_columnr  s    }--r   c                 $    | j                  d      S )z#Gets the last row within the range.r   rL  r5   s    r   get_last_rowzRange.get_last_rowv  s    z**r   c                 *    | j                  d||      S )a0  Gets an object which represents a range that's offset from the specified range.
            The dimension of the returned range will match this range.
            If the resulting range is forced outside the bounds of the worksheet grid,
            an exception will be thrown.

        :param int row_offset: The number of rows (positive, negative, or 0)
         by which the range is to be offset.
        :param int column_offset: he number of columns (positive, negative, or 0)
         by which the range is to be offset.
        :return: Range
        r   )	rowOffsetcolumnOffsetrL  )r.   
row_offsetcolumn_offsets      r   get_offset_rangezRange.get_offset_rangez  s!     j}  
 	
r   c                 *    | j                  dd|      S )z
        Gets a row contained in the range.
        :param int index: Row number of the range to be retrieved.
        :return: Range
        r   rH  )rD  rM  rL  rP  s     r   r   zRange.get_row  s     yUCCr   c                 *    | j                  d|d      S )z
        Gets a certain number of rows above a given range.

        :param int rows: Optional. The number of rows to include in the resulting range.
        :return: Range
        r   rH  rC  rL  r.   rowss     r   get_rows_abovezRange.get_rows_above       |T&AAr   c                 *    | j                  d|d      S )z
        Gets a certain number of rows below a given range.

        :param int rows: Optional. The number of rows to include in the resulting range.
        :return: Range
        r   rH  rC  rL  rn  s     r   get_rows_belowzRange.get_rows_below  rq  r   c                 T    | j                  dt        |      j                               S )z
        Returns the used range of the given range object.

        :param bool only_values: Optional. Defaults to True.
          Considers only cells with values as used cells (ignores formatting).
        :return: Range
        r   )rJ  strlower)r.   only_valuess     r   r   zRange.get_used_range  s$     /[1A1G1G1IJJr   c                     | j                  | j                  j                  d            }t        | j                  j                  |d|j                         i            S )z
        Clear range values, format, fill, border, etc.

        :param str apply_to: Optional. Determines the type of clear action.
          The possible values are: all, formats, contents.
        r   applyTor?   rB   rC   r$   rO   r|   rD   
capitalize)r.   apply_torH   s      r   r   zRange.clear  sN     nnT__00?@DLL%%cH<O<O<Q0R%STTr   c                     | j                  | j                  j                  d            }t        | j                  j                  |d|j                         i            S )z
        Deletes the cells associated with the range.

        :param str shift: Optional. Specifies which way to shift the cells.
          The possible values are: up, left.
        r   shiftr?   rz  )r.   r~  rH   s      r   r_   zRange.delete  sN     nnT__00@ADLL%%c%:J:J:L0M%NOOr   c                 F    | j                  dd|j                               S )a5  
        Inserts a cell or a range of cells into the worksheet in place of this range,
        and shifts the other cells to make space.

        :param str shift: Specifies which way to shift the cells. The possible values are: down, right.
        :return: new Range instance at the now blank space
        r   rH  )rD  r~  )rJ  r{  )r.   r~  s     r   r   zRange.insert_range  s"     ~fEDTDTDVWWr   c                     | j                  | j                  j                  d            }t        | j                  j                  |d|i            S )z
        Merge the range cells into one region in the worksheet.

        :param bool across: Optional. Set True to merge cells in each row of the
         specified range as separate merged cells.
        r   acrossr?   r   )r.   r  rH   s      r   mergezRange.merge  sE     nnT__00?@DLL%%c60B%CDDr   c                     | j                  | j                  j                  d            }t        | j                  j                  |            S )z,Unmerge the range cells into separate cells.r   r   r   s     r   unmergezRange.unmerge  9    nnT__00ABDLL%%c*++r   c                 ,    | j                  d||d      S )a  
        Gets a range object similar to the current range object,
        but with its bottom-right corner expanded (or contracted)
        by some number of rows and columns.

        :param int rows: The number of rows by which to expand the
          bottom-right corner, relative to the current range.
        :param int columns: The number of columns by which to expand the
          bottom-right corner, relative to the current range.
        :return: Range
        r   rB  rC  rL  )r.   ro  rX  s      r   r   zRange.get_resized_range  s     2D'%PPr   c                 t   | j                   sy| j                  | j                         }| j                  j                  | j	                  d      |      }|sy|j                         }| j                   D ]'  }t        | t        |      |j                  |             ) | j                   j                          y)zUpdate this rangeTr   r   r?   F)
rm   r   r|   r]   rB   rF   setattrr   r$   r   )r.   r@   rI   fields       r   r   zRange.update  s     ""d.A.AB<<%%dnnR&8t%D}}(( 	AED-.@	A!!#r   c                 j   | j                  d      }| j                         j                  d      j                  d      }| j                  j                  ||j                               }|sy|j                         }|j                  d      }|yt        dd| j                  i| j                  |iS )zReturns this range worksheetr   r   	worksheetrF  Nr|   r   )
rB   qselectexpandr|   r$   	as_paramsrF   r  r   r.   rH   r  rI   r@   wss         r   get_worksheetzRange.get_worksheet  s    nnR FFHOOI&--k:<<##C#>}}XXk":LL$2F2F1KLLr   c                     | j                  | j                  j                  d            }| j                  j                  |      }|sy | j                  dd| i| j
                  |j                         iS )z<Returns a RangeFormat instance with the format of this ranger   Nr   r   )rB   rC   r$   r|   range_format_constructorr   rF   rP   s      r   r   zRange.get_format  so    nnT__00>?<<##C(,t,, 

 00(--/B
 	
r   r   r2   )r   T)all)up)F)/r   r   r   r`   rC   r   r  r#   r6   r4   r)  r   r,  r   r1  r  r6  r:  r=  r  r   rJ  r   r   r   rY  r[  r]  r_  ra  rc  re  rk  r   rp  rs  r   r   r_   r   r  r  r   r   r  r   ra   rb   s   @r   r   r   6  s   -* 	_ 	2	
 	4 	 	 	[ 	} 	J 	 	6 	, 	, 	5  	x!" 		#$ "#K-J0  +F6P81 # # 1 1     . .   __, , 	$ 	$ 2 2 $ $ 2 2 # # 1 1 	 	 ]]* *. 27 V 84JHI0>,.+
"DBB	KUPXE,
Q&M
r   r   c                   V     e Zd ZdZddiZeZd fd	Zd Zd Z	d Z
d	 Zddd
dZ xZS )
NamedRangez7Represents a defined name for a range of cells or value	get_ranger   Nc                    |r|rt        d      |r|j                  n|| _        |j                  | j                  i       }|j                  dd       | _        |j                  dd       xs |rt        |dd       nd }dj                  || j                        }t        | %  |r|j                  n|j                  d      |       |j                  dd       | _        |j                  dd      | _        |j                  d	d      | _        |j                  d
d      | _        |j                  dd      | _        |j                  dd      | _        y )Nr   ry   r   z{}/names/{}r   r   commentr   scopetyper   visibleT)r   r|   r$   r   r  r    r!   r:   r"   r#   r   ry   r  r  	data_typer   r  r"  s         r   r#   zNamedRange.__init__   s,   gFGG)/v~~WZZ 4 4b9
 $5 

?D9 
6<GFOT2$ 	 &,,]DNNK(.V__FJJz4J' 	 	
 NN640	!~~i4  ^^GR0
 $3  ^^GR0
!~~i6r   c                 "    | j                         S r2   r3   r5   s    r   r6   zNamedRange.__str__G  r7   r   c                 N    dj                  | j                  | j                        S )NzNamed Range: {} ({}))r:   ry   r   r5   s    r   r4   zNamedRange.__repr__J  s    %,,TYY

CCr   c                 4    | j                   |j                   k(  S r2   r&  r'  s     r   r)  zNamedRange.__eq__M  r*  r   c                     | j                  | j                  j                  d            }| j                  j                  |      }|sy | j                  dd| i| j
                  |j                         iS )z5Returns the Range instance this named range refers tor  Nr   r   rB   rC   r$   r|   range_constructorr   rF   rP   s      r   r  zNamedRange.get_rangeP  o    nnT__00=><<##C(%t%% 

 00(--/B
 	
r   )r  r  c                P   ||t        d      i }|||d<   |||d<   |sdn|}| j                  j                  | j                  d      |      }|sy|j	                         }|j                  d| j                        | _        |j                  d| j                        | _        y)	z
        Updates this named range
        :param bool visible: Specifies whether the object is visible or not
        :param str comment: Represents the comment associated with this name
        :return: Success or Failure
        Nz)Provide "visible" or "comment" to update.r  r  r   r?   FT)r   r|   r]   rB   rF   r$   r  r  )r.   r  r  r@   rI   s        r   r   zNamedRange.updateZ  s     ?wHII%DO%DOtT<<%%dnnR&8t%D}}xx	4<<8xx	4<<8r   r   )r   r   r   r`   rC   r   r  r#   r6   r4   r)  r  r   ra   rb   s   @r   r  r    sH    A 	XJ %7ND1
 !%d  r   r  c                   V     e Zd ZdZdddZeZd fd	Zd Zd Z	d Z
d	 Zd
 Zd Z xZS )TableRowzAn Excel Table Rowr   r   )r  r_   c                    |r|rt        d      || _        |r|j                  n|| _        |j                  | j                  i       }|j                  dd       | _        |j                  dd       xs |rt        |dd       nd }dj                  || j
                        }t        | )  |r|j                  n|j                  d      |       |j                  dd      | _        |j                  dg g      | _        y )	Nr   rQ  r   z{}/rows/itemAt(index={})r   r   r   r  )r   tabler|   r$   r   r  r    r!   r:   r"   r#   r   rQ  r  r"  s         r   r#   zTableRow.__init__|  s    gFGG 
)/v~~WZZ 4 4b9
 $6 

?D9 
6<GFOT2$ 	
 399-X(.V__FJJz4J' 	 	
  ^^GQ/

 !nnXt4r   c                 "    | j                         S r2   r3   r5   s    r   r6   zTableRow.__str__  r7   r   c                 8    dj                  | j                        S )NzRow number: {})r:   rQ  r5   s    r   r4   zTableRow.__repr__  s    &&tzz22r   c                 4    | j                   |j                   k(  S r2   r&  r'  s     r   r)  zTableRow.__eq__  r*  r   c                     | j                  | j                  j                  d            }| j                  j                  |      }|sy | j                  dd| i| j
                  |j                         iS )z4Gets the range object associated with the entire rowr  Nr   r   r  rP   s      r   r  zTableRow.get_range  r  r   c                     | j                   j                  | j                  d      d|i      }|sy|j                         }|j	                  d| j
                        | _        y)zUpdates this rowr   r  r?   FTr|   r]   rB   rF   r$   r  r.   r  rI   r@   s       r   r   zTableRow.update  sS    <<%%dnnR&8&?Q%R}}hhx5r   c                     | j                  | j                  j                  d            }t        | j                  j                  |            S )zDeletes this rowr_   r   r   s     r   r_   zTableRow.delete  9    nnT__00:;DLL%%c*++r   r   )r   r   r   r`   rC   r   r  r#   r6   r4   r)  r  r   r_   ra   rb   s   @r   r  r  s  sA     J "5H31
,r   r  c                        e Zd ZdZdddddddd	ZeZd fd
	Zd Zd Z	d Z
d Zd Zd Zd Zd Zd Zd Zd Zd Zd Z xZS )TableColumnzAn Excel Table Columnr   /dataBodyRange/headerRowRange/totalRowRanger   z/filter/clearz/filter/apply)r_   data_body_rangeheader_row_rangetotal_row_rangeentire_rangeclear_filterapply_filterc                 >   |r|rt        d      || _        |r|j                  n|| _        |j                  | j                  i       }|j                  dd       | _        |j                  dd       xs |rt        |dd       nd }dj                  |t        | j
                              }t        | -  |r|j                  n|j                  d      |       |j                  dd      | _        |j                  d	d
      | _        |j                  dg g      | _        y )Nr   rA   r   z{}/columns('{}')r   r   ry   r   rQ  r   r  )r   r  r|   r$   r   r  r    r!   r:   r   r"   r#   r   ry   rQ  r  r"  s         r   r#   zTableColumn.__init__  s   gFGG 
)/v~~WZZ 4 4b9
 $d3 

?D9 
6<GFOT2$ 	
 +11-t~~AVW(.V__FJJz4J' 	 	
 NN62.	  ^^GQ/
 !nnXt4r   c                 "    | j                         S r2   r3   r5   s    r   r6   zTableColumn.__str__  r7   r   c                 8    dj                  | j                        S )NzTable Column: {}r:   ry   r5   s    r   r4   zTableColumn.__repr__  s    !((33r   c                 4    | j                   |j                   k(  S r2   r&  r'  s     r   r)  zTableColumn.__eq__  r*  r   c                     | j                  | j                  j                  d            }t        | j                  j                  |            S )zDeletes this table Columnr_   r   r   s     r   r_   zTableColumn.delete  r  r   c                     | j                   j                  | j                  d      d|i      }|sy|j                         }|j	                  dd      | _        y)zM
        Updates this column
        :param values: values to update
        r   r  r?   FTr  r  s       r   r   zTableColumn.update  sQ    
 <<%%dnnR&8&?Q%R}}hhx,r   c                     | j                  | j                  j                  |            }| j                  j                  |      }|sy | j                  dd| i| j
                  |j                         iS z*Returns a Range based on the endpoint nameNr   r   r  )r.   endpoint_namerH   rI   s       r   rJ  zTableColumn._get_range  sq     nnT__00?@<<##C(%t%% 

 00(--/B
 	
r   c                 $    | j                  d      S )zAGets the range object associated with the data body of the columnr  rL  r5   s    r   get_data_body_rangezTableColumn.get_data_body_range      011r   c                 $    | j                  d      S )zBGets the range object associated with the header row of the columnr  rL  r5   s    r   get_header_row_rangez TableColumn.get_header_row_range      122r   c                 $    | j                  d      S )zBGets the range object associated with the totals row of the columnr  rL  r5   s    r   get_total_row_rangezTableColumn.get_total_row_range#  r  r   c                 $    | j                  d      S )z7Gets the range object associated with the entire columnr  rL  r5   s    r   r  zTableColumn.get_range'      ~..r   c                     | j                  | j                  j                  d            }t        | j                  j                  |            S )z(Clears the filter applied to this columnr  r   r   s     r   r  zTableColumn.clear_filter+  s9    nnT__00@ADLL%%c*++r   c                     | j                  | j                  j                  d            }t        | j                  j                  |d|i            S )aE  
        Apply the given filter criteria on the given column.

        :param str criteria: the criteria to apply

            Example:

            .. code-block:: json

                {
                    "color": "string",
                    "criterion1": "string",
                    "criterion2": "string",
                    "dynamicCriteria": "string",
                    "filterOn": "string",
                    "icon": {"@odata.type": "microsoft.graph.workbookIcon"},
                    "values": {"@odata.type": "microsoft.graph.Json"}
                }

        r  criteriar?   r   )r.   r  rH   s      r   r  zTableColumn.apply_filter0  sE    * nnT__00@ADLL%%cX0F%GHHr   c                    | j                         j                  d      j                  d      }| j                  j	                  | j                  d      |j                               }|sy|j                         }|j	                  dd      S )z(Returns the filter applie to this columnry   filterr   rF  Nr  )r  r  r  r|   r$   rB   r  rF   )r.   r  rI   r@   s       r   
get_filterzTableColumn.get_filterH  sk    FFHOOF#**84<<##DNN2$6q{{}#M}}xx
D))r   r   )r   r   r   r`   rC   r   r  r#   r6   r4   r)  r_   r   rJ  r  r  r  r  r  r  r  ra   rb   s   @r   r  r    st     +-+ ''J #5J41,
	
232/,
I0*r   r  c                   X    e Zd ZdZi dddddddd	d
dddddddddddddddddddddd d!d"d#d$d%iZeZeZe	Z
dD fd'	Zd( Zd) Zd* Zd&d&d+d,Zd- Zd. Zd/ Zd0d&d1d2Zd&d&d+d3Zd4 Zd5 Zd6 ZdDd7Zd&d&d&d&d8d9Zd: Zd; Zd< Zd= Zd> Zd? Zd@ Z dA Z!dB Z"dC Z# xZ$S )ETablezAn Excel Tableget_columnsz/columnsr   z/columns/{id}delete_columnz/columns/{id}/deleteget_column_indexz/columns/itemAt
add_columnz/columns/addget_rowsz/rowsr   z
/rows/{id}
delete_rowz/rows/$/itemAt(index={id})get_row_indexz/rows/itemAtadd_rowsz	/rows/addr_   /r  r  r  r  r  r  r  r   convert_to_rangez/convertToRangeclear_filtersz/clearFiltersreapply_filtersz/reapplyFiltersNc                 ~   |r|rt        d      || _        |r|j                  n|| _        |j                  | j                  i       }|j                  dd       | _        |j                  dd       xs |rt        |dd       nd }dj                  |t        | j
                              }t        | -  |r|j                  n|j                  d      |       |j                  dd       | _        |j                  dd	      | _        |j                  d
d	      | _        |j                  dd       | _        |j                  dd      | _        |j                  dd      | _        |j                  dd      | _        |j                  dd      | _        |j                  dd      | _        |j                  dd      | _        y )Nr   rA   r   z{}/tables('{}')r   r   ry   showHeadersT
showTotalsstylehighlightFirstColumnFhighlightLastColumnshowBandedColumnsshowBandedRowsshowFilterButtonlegacyId)r   r   r|   r$   r   r  r    r!   r:   r   r"   r#   r   ry   show_headersshow_totalsr  highlight_first_columnhighlight_last_columnshow_banded_columnsshow_banded_rowsshow_filter_button	legacy_idr"  s         r   r#   zTable.__init__m  s   gFGG )/v~~WZZ 4 4b9
 $d3 

?D9 
6<GFOT2$ 	
 *00dnn@UV(.V__FJJz4J' 	 	
 NN640	&NN=$?%>>,=^^GT2
&0nn5KU&S#%/^^4I5%Q" $.>>2Eu#M  */? G #-..1CU"K#
E:r   c                 "    | j                         S r2   r3   r5   s    r   r6   zTable.__str__  r7   r   c                 8    dj                  | j                        S )Nz	Table: {}r  r5   s    r   r4   zTable.__repr__  s    !!$)),,r   c                 4    | j                   |j                   k(  S r2   r&  r'  s     r   r)  zTable.__eq__  r*  r   )topskipc                .     j                   j                  j                  d            }i }|||d<   |||d<   |sdn|} j                  j                  ||      }|st	        d      S |j                         } fd|j                  dg       D        S )	z
        Return the columns of this table
        :param int top: specify n columns to retrieve
        :param int skip: specify n columns to skip
        r  N$top$skiprF  r   c              3   ^   K   | ]$  } j                   dd ij                  |i & ywr   Nr   )column_constructorr   ).0rN  r.   s     r   	<genexpr>z$Table.get_columns.<locals>.<genexpr>  s9      
 $D##R4RD4H4H&3QR
   *-r   rB   rC   r$   r|   iterrF   r.   r  r  rH   rG  rI   r@   s   `      r   r  zTable.get_columns  s     nnT__00?@? F6N"F7O#<<##C#78O}}
((7B/
 	
r   c                 $   | j                  | j                  j                  d      j                  t	        |                  }| j
                  j                  |      }|sy|j                         } | j                  dd| i| j                  |iS )z
        Gets a column from this table by id or name
        :param id_or_name: the id or name of the column
        :return: WorkBookTableColumn
        r   rA   Nr   r   )	rB   rC   r$   r:   r   r|   rF   r  r   )r.   
id_or_namerH   rI   r@   s        r   r   zTable.get_column  s     nnOO-44j8I4J
 <<##C(}}&t&&SdSt7K7KT6RSSr   c                     |y| j                  | j                  j                  d            }| j                  j	                  |d|i      }|sy | j
                  dd| i| j                  |j                         iS )z
        Returns a table column by it's index
        :param int index: the zero-indexed position of the column in the table
        Nr  rQ  r?   r   r   )rB   rC   r$   r|   rD   r  r   rF   r.   rQ  rH   rI   s       r   get_column_at_indexzTable.get_column_at_index  s    
 =nnT__001CDE<<$$S/?$@&t&& 

 00(--/B
 	
r   c                     | j                  | j                  j                  d      j                  t	        |                  }t        | j                  j                  |            S )z
        Deletes a Column by its id or name
        :param id_or_name: the id or name of the column
        :return bool: Success or Failure
        r  r  )rB   rC   r$   r:   r   rO   r|   rD   )r.   r  rH   s      r   r  zTable.delete_column  sS     nnOO0775;L7M
 DLL%%c*++r   r   )rQ  r  c                   |y||d}|||d<   | j                  | j                  j                  d            }| j                  j	                  ||      }|sy|j                         } | j                  dd| i| j                  |iS )a  
        Adds a column to the table
        :param str name: the name of the column
        :param int index: the index at which the column should be added. Defaults to 0.
        :param list values: a two dimension array of values to add to the column
        N)ry   rQ  r  r  r?   r   r   )rB   rC   r$   r|   rD   rF   r  r   )r.   ry   rQ  r  rG  rH   rI   r@   s           r   r  zTable.add_column  s     </%F8nnT__00>?<<$$Sv$6}}&t&&SdSt7K7KT6RSSr   c                .     j                   j                  j                  d            }i }|||d<   |||d<   |sdn|} j                  j                  ||      }|st	        d      S |j                         } fd|j                  dg       D        S )	z
        Return the rows of this table
        :param int top: specify n rows to retrieve
        :param int skip: specify n rows to skip
        :rtype: TableRow
        r  Nr  r  rF  r   c              3   ^   K   | ]$  } j                   dd ij                  |i & ywr  )row_constructorr   )r  rM  r.   s     r   r  z!Table.get_rows.<locals>.<genexpr>   s9      
 !D  LL1E1Es0KL
r  r   r	  r  s   `      r   r  zTable.get_rows
  s     nnT__00<=? F6N"F7O#<<##C#78O}}
xx,
 	
r   c                    | j                  | j                  j                  d      j                  |            }| j                  j                  |      }|sy | j
                  dd| i| j                  |j                         iS )z"Returns a Row instance at an indexr   r  Nr   r   rB   rC   r$   r:   r|   r  r   rF   r  s       r   r   zTable.get_row%  s}    nnT__00;BBeBLM<<##C(#t## 

 00(--/B
 	
r   c                    |y| j                  | j                  j                  d            }dj                  ||      }| j                  j                  |      }|sy | j
                  dd| i| j                  |j                         iS )z
        Returns a table row by it's index
        :param int index: the zero-indexed position of the row in the table
        Nr  z{}(index={})r   r   r  r  s       r   get_row_at_indexzTable.get_row_at_index/  s    
 =nnT__00AB##C/<<##C(#t## 

 00(--/B
 	
r   c                     | j                  | j                  j                  d      j                  |            }t	        | j
                  j                  |            S )z
        Deletes a Row by it's index
        :param int index: the index of the row. zero indexed
        :return bool: Success or Failure
        r  r  )rB   rC   r$   r:   rO   r|   r_   )r.   rQ  rH   s      r   r  zTable.delete_rowB  sI     nnT__00>EEEOPDLL'',--r   c                 N   i }||rt        |d   t              s|g}||d<   |||d<   |r|nd}| j                  | j                  j	                  d            }| j
                  j                  ||      }|sy | j                  dd| i| j                  |j                         iS )	a  
        Add rows to this table.

         Multiple rows can be added at once.
         This request might occasionally receive a 504 HTTP error.
         The appropriate response to this error is to repeat the request.

        :param list values: Optional. a 1 or 2 dimensional array of values to add
        :param int index: Optional. Specifies the relative position of the new row.
         If null, the addition happens at the end.
        :return:
        Nr   r  rQ  r  r?   r   r   )
r  r   rB   rC   r$   r|   rD   r  r   rF   )r.   r  rQ  rG  rH   rI   s         r   r  zTable.add_rowsK  s     jD9 %F8#F7O!tnnT__00<=<<$$Sv$6#t## 

 00(--/B
 	
r   )ry   r  r  r  c                   ||||t        d      i }|r||d<   |||d<   |||d<   |r||d<   | j                  j                  | j                  d      |      }|sy|j	                         }|j                  d| j                        | _        |j                  d| j                        | _        |j                  d| j                        | _        |j                  d| j                        | _	        y	)
a0  
        Updates this table
        :param str name: the name of the table
        :param bool show_headers: whether or not to show the headers
        :param bool show_totals: whether or not to show the totals
        :param str style: the style of the table
        :return: Success or Failure
        (Provide at least one parameter to updatery   r  r  r  r   r?   FT)
r   r|   r]   rB   rF   r$   ry   r  r  r  )r.   ry   r  r  r  r@   rI   s          r   r   zTable.updatek  s     L$#GHHDL#".D"!,D!DM<<%%dnnR&8t%D}}HHVTYY/	 HH]D4E4EF88L$2B2BCXXgtzz2
r   c                     | j                  | j                  j                  d            }t        | j                  j                  |            S )zDeletes this tabler_   )rB   rC   r$   rO   r|   r_   r   s     r   r_   zTable.delete  s9    nnT__00:;DLL'',--r   c                     | j                  | j                  j                  |            }| j                  j                  |      }|sy|j	                         } | j
                  dd| i| j                  |iS r  )rB   rC   r$   r|   rF   r  r   )r.   r  rH   rI   r@   s        r   rJ  zTable._get_range  sl     nnT__00?@<<##C(}}%t%%RTRd6J6JD5QRRr   c                 $    | j                  d      S )z@Gets the range object associated with the data body of the tabler  rL  r5   s    r   r  zTable.get_data_body_range  r  r   c                 $    | j                  d      S )zAGets the range object associated with the header row of the tabler  rL  r5   s    r   r  zTable.get_header_row_range  r  r   c                 $    | j                  d      S )zAGets the range object associated with the totals row of the tabler  rL  r5   s    r   r  zTable.get_total_row_range  r  r   c                 $    | j                  d      S )z6Gets the range object associated with the entire tabler  rL  r5   s    r   r  zTable.get_range  r  r   c                 $    | j                  d      S )zGConverts the table into a normal range of cells. All data is preserved.r  rL  r5   s    r   r  zTable.convert_to_range  r  r   c                     | j                  | j                  j                  d            }t        | j                  j                  |            S )z6Clears all the filters currently applied on the table.r  r   r   s     r   r  zTable.clear_filters  r  r   c                     | j                  | j                  j                  d            }t        | j                  j                  |            S )z1Reapplies all the filters currently on the table.r  r   r   s     r   r  zTable.reapply_filters  s:    nnT__001BCDDLL%%c*++r   c                 j   | j                  d      }| j                         j                  d      j                  d      }| j                  j                  ||j                               }|sy|j                         }|j                  d      }|yt        dd| j                  i| j                  |iS )zReturns this table worksheetr   ry   r  rF  Nr   r   )rB   r  r  r  r|   r$   r  rF   r  r   r   r  s         r   r  zTable.get_worksheet  s    nnR FFHOOF#**;7<<##C#>}}XXk":JJ0D0Db/IJJr   r   )%r   r   r   r`   rC   r  r  r  r  r   r  r#   r6   r4   r)  r  r   r  r  r  r  r   r  r  r  r   r_   rJ  r  r  r  r  r  r  r  r  ra   rb   s   @r   r  r  R  s   zo 	/ 	-	
 	n 	G 	< 	2 	 	K 	# 	+ 	- 	+ 	  	-!" 	#$ 	,%J( %O2;h-1 "&D 
4T$
$	, )*$ T. # 
6

&.
@ "$d $L.
S232/3,
,
Kr   r  c            
            e Zd ZdZdddddddd	d
d	ZeZeZe	Z
d fd	Zd Zd Zd Zd ZdddddZd Zd Zd Zd dZd!dZd Zd"dZd Zd Zd Zed        Z xZS )#r  zAn Excel WorkSheet/tables/tables/{id}r   z/tables/addr   z /cell(row={row},column={column})
/names/add/names/addFormulaLocal/names/{name})	
get_tables	get_tabler  	add_tabler   r   add_named_rangeadd_named_range_fget_named_rangeNc                 <   |r|rt        d      || _        |r|j                  n|| _        |j                  | j                  i       }|j                  dd       | _        |j                  dd       xs |rt        |dd       nd }dj                  |t        | j
                              }t        | -  |r|j                  n|j                  d      |       |j                  dd       | _        |j                  dd       | _        |j                  d	d       | _        y )
Nr   rA   r   z{}/worksheets('{}')r   r   ry   position
visibility)r   workbookr|   r$   r   r  r    r!   r:   r   r"   r#   r   ry   r6  r7  r"  s         r   r#   zWorkSheet.__init__  s   gFGG )/v~~WZZ 4 4b9
 $d3 

?D9 
6<GFOT2$ 	
 .4450
 	(.V__FJJz4J' 	 	
 NN640	"z48 %..t<r   c                 "    | j                         S r2   r3   r5   s    r   r6   zWorkSheet.__str__  r7   r   c                 8    dj                  | j                        S )NzWorksheet: {}r  r5   s    r   r4   zWorkSheet.__repr__  s    %%dii00r   c                 4    | j                   |j                   k(  S r2   r&  r'  s     r   r)  zWorkSheet.__eq__  r*  r   c                 h    t        | j                  j                  | j                  d                  S )zDeletes this worksheetr   )rO   r|   r_   rB   r5   s    r   r_   zWorkSheet.delete  s%    DLL''r(:;<<r   )ry   r6  r7  c                   |||t        d      i }|r||d<   |r||d<   |r||d<   | j                  j                  | j                  d      |      }|sy|j	                         }|j                  d| j                        | _        |j                  d| j                        | _        |j                  d| j                        | _        y)	z:Changes the name, position or visibility of this worksheetr  ry   r6  r7  r   r?   FT)	r   r|   r]   rB   rF   r$   ry   r6  r7  )r.   ry   r6  r7  r@   rI   s         r   r   zWorkSheet.update  s     <H,1CGHHDL'D!+D<<%%dnnR&8t%D}}HHVTYY/	T]];((<Ar   c           	      :   | j                  | j                  j                  d            }| j                  j                  |      }|sg S |j	                         }|j                  dg       D cg c]"  } | j
                  dd| i| j                  |i$ c}S c c}w )z-Returns a collection of this worksheet tablesr/  r   r   r   rB   rC   r$   r|   rF   table_constructorr   r.   rH   rI   r@   r  s        r   r/  zWorkSheet.get_tables*       nnT__00>?<<##C(I}} '2.
 #D""P$P43G3G2OP
 	
 
   .'Bc                    | j                  | j                  j                  d      j                  |            }| j                  j                  |      }|sy | j
                  dd| i| j                  |j                         iS z
        Retrieves a Table by id or name
        :param str id_or_name: The id or name of the column
        :return: a Table instance
        r0  r  Nr   r   rB   rC   r$   r:   r|   r@  r   rF   r.   r  rH   rI   s       r   r0  zWorkSheet.get_table:       nnT__00=DD
DST<<##C(%t%% 

 00(--/B
 	
r   c                    |y||d}| j                  | j                  j                  d            }| j                  j	                  ||      }|sy | j
                  dd| i| j                  |j                         iS )z
        Adds a table to this worksheet
        :param str address: a range address eg: 'A1:D4'
        :param bool has_headers: if the range address includes headers or not
        :return: a Table instance
        N)r   
hasHeadersr1  r?   r   r   )rB   rC   r$   r|   rD   r@  r   rF   )r.   r   has_headersrG  rH   rI   s         r   r1  zWorkSheet.add_tableH  s     ?$K@nnT__00=><<$$Sv$6%t%% 

 00(--/B
 	
r   c                 8   | j                  | j                  j                  d            }|#| j                  |      }dj	                  ||      }| j
                  j                  |      }|sy | j                  dd| i| j                  |j                         iS )z
        Returns a Range instance from whitin this worksheet
        :param str address: Optional, the range address you want
        :return: a Range instance
        r  Nz{}(address='{}')r   r   )	rB   rC   r$   remove_sheet_name_from_addressr:   r|   r  r   rF   )r.   r   rH   rI   s       r   r  zWorkSheet.get_rangeZ  s     nnT__00=>99'BG$++C9C<<##C(%t%% 

 00(--/B
 	
r   c                 :   | j                  | j                  j                  d      j                  t	        |      j                                     }| j                  j                  |      }|sy | j                  dd| i| j                  |j                         iS )a  Returns the smallest range that encompasses any cells that
        have a value or formatting assigned to them.

        :param bool only_values: Optional. Defaults to True.
         Considers only cells with values as used cells (ignores formatting).
        :return: Range
        r   Nr   r   )
rB   rC   r$   r:   ru  rv  r|   r  r   rF   )r.   rw  rH   rI   s       r   r   zWorkSheet.get_used_rangek  s     nnOO 0188[9I9O9O9QR
 <<##C(%t%% 

 00(--/B
 	
r   c                    | j                  | j                  j                  d      j                  ||            }| j                  j                  |      }|sy | j
                  dd| i| j                  |j                         iS )zQGets the range object containing the single cell based on row and column numbers.r   )rM  rN  Nr   r   )rB   rC   r$   r:   r|   r  r   rF   )r.   rM  rN  rH   rI   s        r   r   zWorkSheet.get_cell~  s    nnOO
+22s62J
 <<##C(%t%% 

 00(--/B
 	
r   c                 X   |r+| j                  | j                  j                  d            }n*| j                  | j                  j                  d            }|||d}| j                  j	                  ||      }|sy | j
                  dd| i| j                  |j                         iS a  
        Adds a new name to the collection of the given scope using the user's locale for the formula
        :param str name: the name of this range
        :param str reference: the reference for this range or formula
        :param str comment: a comment to describe this named range
        :param bool is_formula: True if the reference is a formula
        :return: NamedRange instance
        r3  r2  )ry   	referencer  r?   Nr   r   rB   rC   r$   r|   rD   named_range_constructorr   rF   r.   ry   rR  r  
is_formularH   rG  rI   s           r   r2  zWorkSheet.add_named_range       ..!4!45H!IJC..!4!45F!GHCY7K<<$$Sv$6+t++ 

 00(--/B
 	
r   c                 r   t        |t              r| j                  |      }t        |t              st	        d      t        |t
              st	        d      t        |      }|dkD  rt        |d         nd}|j                  |k7  s|j                  |k7  rt	        d      ||_	        |j                          y)a  
        Updates the cells at a given range in this worksheet. This is a convenience method since there is no
        direct endpoint API for tableless row updates.
        :param str|Range address: the address to resolve to a range which can be used for updating cells.
        :param list[list[str]] rows: list of rows to push to this range. If updating a single cell, pass a list
        containing a single row (list) containing a single cell worth of data.
        z.address was not an accepted type: str or Rangez.rows was not an accepted type: list[list[str]]r   r   zjrows and columns are not the same size as the range selected. This is required by the Microsoft Graph API.N)r  ru  r  r   r   r   lenr  r  r  r   )r.   r   ro  r  	col_counts        r   update_cellszWorkSheet.update_cells  s     gs#nnW-G'5)MNN$%MNN I	$-MCQLq		)W-A-AY-N  J  K  Kr   c                    t        |      }|dkD  rt        |d         nd}|dz
  }| j                         }|j                  dk(  r>t        |j                  d         dk(  r#|j                  d   d   dk(  rg |_        d|_        |j                  }t	        |      }dj                  |dz   |||z         }| j                  ||       y)a  
        Appends rows to the end of a worksheet. There is no direct Graph API to do this operation without a Table
        instance. Instead, this method identifies the last row in the worksheet and requests a range after that row
        and updates that range.

        Beware! If you open your workbook from sharepoint and delete all of the rows in one go and attempt to append
        new rows, you will get undefined behavior from the Microsoft Graph API. I don't know if I did not give enough
        time for the backend to synchronize from the moment of deletion on my browser and the moment I triggered my
        script, but this is something I have observed. Sometimes insertion fails and sometimes it inserts where the new
        row would have been if data had not been deleted from the browser side. Maybe it is an API cache issue. However,
        after the first row is inserted successfully, this undefined behavior goes away on repeat calls to my scripts.
        Documenting this behavior for future consumers of this API.

        :param list[list[str]] rows: list of rows to push to this range. If updating a single cell, pass a list
            containing a single row (list) containing a single cell worth of data.
        r   r   r   zA{}:{}{}N)rY  r   r  r  r	   r:   r[  )	r.   ro  r  rZ  	col_indexcurrent_rangetarget_indexcol_nameinsert_range_addresss	            r   append_rowszWorkSheet.append_rows  s    " I	$-MCQLq	M	 ++- ""a'C0D0DQ0G,HA,MR_RfRfghRijkRlprRr#%M &'M#$.. &i0)001A8\\eMef 	.5r   c                    | j                  | j                  j                  d      j                  |            }| j                  j                  |      }|sy | j
                  dd| i| j                  |j                         iS z$Retrieves a Named range by it's namer4  ry   Nr   r   rB   rC   r$   r:   r|   rT  r   rF   r.   ry   rH   rI   s       r   r4  zWorkSheet.get_named_range      nnT__001BCJJPTJUV<<##C(+t++ 

 00(--/B
 	
r   c                     t        j                  d      }|j                  |       }|rdj                  |j	                               S | S )z+Removes the sheet name from a given addressz&([a-zA-Z]+[0-9]+):.*?([a-zA-Z]+[0-9]+):)recompilesearchjoingroups)r   compiledresults      r   rM  z(WorkSheet.remove_sheet_name_from_address  s>     ::FG)88FMMO,,Nr   r   r2   r  r   F)r   r   r   r`   rC   r  r@  r   r  r  rT  r#   r6   r4   r)  r_   r   r/  r0  r1  r  r   r   r2  r[  rb  r4  staticmethodrM  ra   rb   s   @r   r  r    s      #"56'5*
J (#=J11= "DT 0
 

$
"
&


*6'6R
  r   r  c                   F     e Zd ZdddZ fdZd Zd Zd Zd Zd	 Z	 xZ
S )
WorkbookApplicationz/applicationz/application/calculate)get_detailspost_calculationc                     t        |t              st        d      || _        |j                  j
                  | _        t        |dd      }t        | !  |j                  |       y)z
        Create A WorkbookApplication representation

        :param workbook: A workbook object, of the workboook that you want to interact with
        z+workbook was not an accepted type: Workbookr   Nr   )
r  WorkBookr   r   r|   r   r!   r"   r#   r   )r.   r8  r   r0   s      r   r#   zWorkbookApplication.__init__  s]     (H-JKK ##''/4@("3"3=Qr   c                 "    | j                         S r2   r3   r5   s    r   r6   zWorkbookApplication.__str__  r7   r   c                 @    dj                  | j                  xs d      S )Nz$WorkbookApplication for Workbook: {}r9   )r:   workbook_idr5   s    r   r4   zWorkbookApplication.__repr__  s#    5<<)	
 	
r   c                 ,    t        | j                        S r2   )rO   r   r5   s    r   r<   zWorkbookApplication.__bool__  s    DKK  r   c                     | j                  | j                  j                  d            }| j                  j                  |      }|sy|j	                         S )zGets workbookApplicationrv  N)rB   rC   r$   r   rF   rP   s      r   rv  zWorkbookApplication.get_details  sC    nnT__00?@88<<$}}r   c                 d   |dvrt        d      | j                  | j                  j                  d            }d|i}ddi}| j                  j
                  j                  r#| j                  j
                  j                  |d<   | j                  j                  |||      }|sy	|j                  S )
z4Recalculate all currently opened workbooks in Excel.)RecalculateFullFullRebuildz?calculation type must be one of: Recalculate, Full, FullRebuildrw  calculationTypezContent-typezapplication/jsonrK   )rM   r@   F)
r   rB   rC   r$   r   r|   r*   r   rD   ok)r.   calculation_typerH   r@   rM   rI   s         r   run_calculationsz$WorkbookApplication.run_calculations#  s    #IIQ  nnT__001CDE!#34!#56;;))-1[[-@-@-K-KG)*88==gD=A{{r   )r   r   r   rC   r#   r6   r4   r<   rv  r  ra   rb   s   @r   ru  ru    s/    %4J
R"

!r   ru  c            
            e Zd Zddddddddd	d
	ZeZeZeZ	e
Zddd fd
Zd Zd Zd Zd Zd Zd Zd Zd ZddZd Zd Zd Zd ZddZ xZS )ry  z/worksheetsr*  r+  z/worksheets/{id}z/functions/{name}z/namesr.  r,  r-  )	get_worksheetsr/  r0  r  function	get_namesr4  r2  r3  T)use_sessionr   c                
   |#t        |t              r|j                  t        k7  rt	        d      dj                  |j                  |j                  j                  d      j                  |j                              }t        | -  |j                  |       |du r|nd}t        |||      | _        |r| j                  j                          |j                   | _        d	j                  |j                        | _	        y)
a	  Create a workbook representation

        :param File file_item: the Drive File you want to interact with
        :param Bool use_session: Whether or not to use a session to be more efficient
        :param Bool persist: Whether or not to persist this info
        Nz)This file is not a valid Excel xlsx file.z{}{}/workbookitemr  r   T)r   r   r   zWorkbook:{})r  r   	mime_typeEXCEL_XLSX_MIME_TYPEr   r:   r   rC   r$   r  r"   r#   r   r   r|   r   ry   )r.   	file_itemr  r   r   r0   s        r   r#   zWorkBook.__init__J  s     i.""&::HII (..##  $$V,33y7J7J3K

 	)"4"4MR(D0'd&g]
 LL'') NN	&--
r   c                 "    | j                         S r2   r3   r5   s    r   r6   zWorkBook.__str__p  r7   r   c                 8    dj                  | j                        S )NzWorkbook: {}r  r5   s    r   r4   zWorkBook.__repr__s  s    $$TYY//r   c                 4    | j                   |j                   k(  S r2   r&  r'  s     r   r)  zWorkBook.__eq__v  r*  r   c           	      :   | j                  | j                  j                  d            }| j                  j                  |      }|sg S |j	                         }|j                  dg       D cg c]"  } | j
                  dd| i| j                  |i$ c}S c c}w )z,Returns a collection of this workbook tablesr/  r   r   r   r?  rA  s        r   r/  zWorkBook.get_tablesy  rB  rC  c                    | j                  | j                  j                  d      j                  |            }| j                  j                  |      }|sy | j
                  dd| i| j                  |j                         iS rE  rF  rG  s       r   r0  zWorkBook.get_table  rH  r   c                 $    | j                  |       S r2   )application_constructorr5   s    r   get_workbookapplicationz WorkBook.get_workbookapplication  s    ++D11r   c           	      :   | j                  | j                  j                  d            }| j                  j                  |      }|sg S |j	                         }|j                  dg       D cg c]"  } | j
                  dd| i| j                  |i$ c}S c c}w )z0Returns a collection of this workbook worksheetsr  r   r   r   )rB   rC   r$   r|   rF   worksheet_constructorr   )r.   rH   rI   r@   r  s        r   r  zWorkBook.get_worksheets  s     nnT__001ABC<<##C(I}} hhw+
 'D&&QdQt7K7KR6PQ
 	
 
rC  c                     | j                  | j                  j                  d      j                  t	        |                  }| j
                  j                  |      }|sy | j                  dd| i| j                  |j                         iS )z'Gets a specific worksheet by id or namer  r  Nr   r   )	rB   rC   r$   r:   r   r|   r  r   rF   rG  s       r   r  zWorkBook.get_worksheet  s    nnOO0775;L7M
 <<##C()t)) 

 00(--/B
 	
r   c                    | j                  | j                  j                  d            }| j                  j	                  ||rd|ind      }|sy|j                         } | j                  dd| i| j                  |iS )zAdds a new worksheetr  ry   Nr?   r   r   )rB   rC   r$   r|   rD   rF   r  r   )r.   ry   rH   rI   r@   s        r   add_worksheetzWorkBook.add_worksheet  sy    nnT__001ABC<<$$S~4$P}})t))VV$:N:NPT9UVVr   c                     | j                  | j                  j                  d      j                  t	        |                  }t        | j                  j                  |            S )zDeletes a worksheet by it's idr  r  )rB   rC   r$   r:   r   rO   r|   r_   )r.   worksheet_idrH   s      r   delete_worksheetzWorkBook.delete_worksheet  sQ    nnOO0775;N7O
 DLL'',--r   c                 4   | j                  | j                  j                  d      j                  |            }| j                  j                  ||      }|sy|j                         }|j                  d      }||j                  d      S t        |      )zInvokes an Excel Functionr  re  r?   Nerrorr   )rB   rC   r$   r:   r|   rD   rF   r   )r.   function_namefunction_paramsrH   rI   r@   r  s          r   invoke_functionzWorkBook.invoke_function  s    nnT__00<CCCWX<<$$S$?}}!=88G$$#E**r   c           	      :   | j                  | j                  j                  d            }| j                  j                  |      }|sg S |j	                         }|j                  dg       D cg c]"  } | j
                  dd| i| j                  |i$ c}S c c}w )z2Returns the list of named ranges for this Workbookr  r   r   r   )rB   rC   r$   r|   rF   rT  r   )r.   rH   rI   r@   nrs        r   get_named_rangeszWorkBook.get_named_ranges  s     nnT__00=><<##C(I}} hhw+
 )D((SS9M9Mr8RS
 	
 
rC  c                    | j                  | j                  j                  d      j                  |            }| j                  j                  |      }|sy | j
                  dd| i| j                  |j                         iS rd  rf  rg  s       r   r4  zWorkBook.get_named_range  rh  r   c                 X   |r+| j                  | j                  j                  d            }n*| j                  | j                  j                  d            }|||d}| j                  j	                  ||      }|sy | j
                  dd| i| j                  |j                         iS rQ  rS  rU  s           r   r2  zWorkBook.add_named_range  rW  r   r2   rr  )r   r   r   rC   ru  r  r  r  r  r@  r  rT  r#   r6   r4   r)  r/  r0  r  r  r  r  r  r  r  r4  r2  ra   rb   s   @r   ry  ry  8  s    '#+'*'5
J 2%(15t $
L01
 
2
 

W.+


r   ry  )#r`   r+   r%   loggingrk  urllib.parser   driver   utilsr   r   r   r	   	getLoggerr   logr'   r(   r  objectr   	Exceptionr   r   rd   r   r   r  r  r  r  r  ru  ry  r   r   r   <module>r     s      	   N Ng!(. %,2 )G 
 		 	F0l F0RV- V-ro, od^
L ^
BY YxL,| L,^M*, M*`xKL xKvh hV	=, =@F
| F
r   