Table of Contents
- Rules
- Addition
- Example
- Adding Rows using integers
- Adding Rows using RowObj
- Adding RangeObj instance to RowObj instance
- Adding Columns using col string
- Adding Columns using ColObj
- Adding RangeObj instance to ColObj instance
- Adding Columns and rows using CellObj
- Add CellObj instance to RowObj instance
- Add RowObj instance to CellObj instance
- Example
- Subtraction
- Combining
- Checking if value in Range
- Iterating over Range
Working with the :py:class:`ooodev.utils.data_type.range_obj.RangeObj` class.
- Can add and subtract
int
- Can add and subtract
str
wherestr
is treated as column name - Can add and subtract
RowObj
- Can add and subtract
ColObj
- Can add and subtract
CellObj
- Adding or subtracting results in a new
RangeObj
- Adding/subtracting a number to a
RangeObj
is treated differently then adding/subtracting aRangeObj
to a number. - Adding/subtracting a string (column) to a
RangeObj
is treated differently then adding/subtracting aRangeObj
to a string. - Adding/subtracting
RowObj
,ColObj
, orCellObj
toRangeObj
is treated differently then adding/subtractingRangeObj
toRowObj
,ColObj
, orCellObj
. RangeObj
can be combined using the/
operator.
Adding positive number to RangeObj
results in rows being append to end or range.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="A", col_end="C", row_start=1, row_end=3, sheet_idx=0)
>>> str(rng1)
'A1:C3'
>>> rng1.row_count
3
>>> rng2 = rng1 + 5
>>> str(rng2)
'A1:C8'
>>> repr(rng2)
"RangeObj(col_start='A', col_end='C', row_start=1, row_end=8, sheet_idx=0)"
>>> rng2.row_count
8
Start Range A1:C3
Result A1:C8
Adding RangeObj
to a positive number result is new rows being added at the start of the range.
Starting with A10:C15
we end up with A5:C15
.
Note that in this example the number comes before rng1
.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="A", col_end="C", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'A10:C15'
>>> rng1.row_count
6
>>> rng2 = 5 + rng1
>>> str(rng2)
'A5:C15'
>>> repr(rng2)
"RangeObj(col_start='A', col_end='C', row_start=5, row_end=15, sheet_idx=0)"
>>> rng2.row_count
11
Start Range A10:C15
Result A5:C15
Adding a negative number.
Adding a negative number to RangeObj
results in rows being removed from the end of the range.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="A", col_end="C", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'A10:C15'
>>> rng1.row_count
6
>>> rng2 = rng1 + -5
>>> str(rng2)
'A10:C10'
>>> repr(rng2)
"RangeObj(col_start='A', col_end='C', row_start=10, row_end=10, sheet_idx=0)"
>>> rng2.row_count
1
Start Range A10:C15
Result A10:C10
Adding a RangeObj
to a negative number results in rows being added to the end of the range.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="A", col_end="C", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'A10:C15'
>>> rng1.row_count
6
>>> rng2 = -5 - rng1
>>> str(rng2)
'A5:C15'
>>> repr(rng2)
"RangeObj(col_start='A', col_end='C', row_start=5, row_end=15, sheet_idx=0)"
>>> rng2.row_count
11
Start Range A10:C15
Result A5:C15
RowObj
instances can also be used to add rows to a RangeObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>> from ooodev.utils.data_type.row_obj import RowObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.row_count
6
>>> rng2 = rng1 + RowObj.from_int(2)
>>> str(rng2)
'F10:H17'
>>> repr(rng2)
"RangeObj(col_start='F', col_end='H', row_start=10, row_end=17, sheet_idx=0)"
>>> rng2.row_count
8
Start Range F10:H15
Result F10:H17
RangeObj
instances can also be used to add rows to a RowObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>> from ooodev.utils.data_type.row_obj import RowObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.row_count
6
>>> rng2 = RowObj.from_int(2) + rng1
>>> str(rng2)
'F8:H15'
>>> repr(rng2)
"RangeObj(col_start='F', col_end='H', row_start=8, row_end=15, sheet_idx=0)"
>>> rng2.row_count
8
Start Range F10:H15
Result F8:H15
Adding columns is accomplished by adding a column letter such as C
to add three columns.
Adding column to RangeObj
results in columns being added to the right of the range.
Add 3 columns to the right of RangeObj
.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.col_count
3
>>> rng2 = rng1 + "C"
>>> str(rng2)
'F10:K15'
>>> repr(rng2)
"RangeObj(col_start='F', col_end='K', row_start=10, row_end=15, sheet_idx=0)"
>>> rng2.col_count
6
Start Range F10:H15
Result F10:K15
Adding column to RangeObj
results in columns being added to the right of the range.
Add 3 columns to the left of RangeObj
.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.col_count
3
>>> rng2 = "C" + rng1
>>> str(rng2)
'C10:H15'
>>> repr(rng2)
"RangeObj(col_start='C', col_end='H', row_start=10, row_end=15, sheet_idx=0)"
>>> rng2.col_count
6
Start Range F10:H15
Result C10:H15
ColObj
instances can also be used to add rows to a RangeObj
instance.
Adding ColObj
instance to RangeObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>> from ooodev.utils.data_type.col_obj import ColObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.col_count
3
>>> rng2 = rng1 + ColObj.from_int(2)
>>> str(rng2)
'F10:J15'
>>> repr(rng2)
"RangeObj(col_start='F', col_end='J', row_start=10, row_end=15, sheet_idx=0)"
>>> rng2.col_count
5
Start Range F10:H15
Result F10:J15
RangeObj
instances can also be used to add rows to a ColObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>> from ooodev.utils.data_type.col_obj import ColObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.col_count
3
>>> rng2 = rng1 + ColObj.from_int(2)
>>> str(rng2)
'F10:J15'
>>> repr(rng2)
"RangeObj(col_start='F', col_end='J', row_start=10, row_end=15, sheet_idx=0)"
>>> rng2.col_count
5
Start Range F10:H15
Result F10:J15
CellObj
instances can also be used to add rows to a RowObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>> from ooodev.utils.data_type.cell_obj import CellObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.row_count
6
>>> rng1.col_count
3
>>> rng2 = rng1 + CellObj.from_idx(1, 1)
>>> str(rng2)
'F10:J17'
>>> repr(rng2)
"RangeObj(col_start='F', col_end='J', row_start=10, row_end=17, sheet_idx=0)"
>>> rng2.row_count
8
>>> rng2.col_count
5
Start Range F10:H15
Result F10:J17
RowObj
instances can also be used to add rows to a CellObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>> from ooodev.utils.data_type.cell_obj import CellObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.row_count
6
>>> rng1.col_count
3
>>> rng2 = CellObj.from_idx(1, 1) + rng1
>>> str(rng2)
'D8:H15'
>>> repr(rng2)
"RangeObj(col_start='D', col_end='H', row_start=8, row_end=15, sheet_idx=0)"
>>> rng2.row_count
8
>>> rng2.col_count
5
Start Range F10:H15
Result D8:H15
Subtracting positive number from RangeObj
results in rows being removed from end of range.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="A", col_end="C", row_start=10, row_end=20, sheet_idx=0)
>>> str(rng1)
'A10:C20'
>>> rng1.row_count
11
>>> rng2 = rng1 - 5
>>> str(rng2)
'A10:C15'
>>> repr(rng2)
"RangeObj(col_start='A', col_end='C', row_start=10, row_end=15, sheet_idx=0)"
>>> rng2.row_count
6
Start Range A10:C20
Result A10:C15
Subtracting RangeObj
from a positive number results in rows being remove from start of range.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="A", col_end="C", row_start=10, row_end=20, sheet_idx=0)
>>> str(rng1)
'A10:C20'
>>> rng1.row_count
11
>>> rng2 = 5 - rng1
>>> str(rng2)
'A15:C20'
>>> repr(rng2)
"RangeObj(col_start='A', col_end='C', row_start=15, row_end=20, sheet_idx=0)"
>>> rng2.row_count
6
Start Range A10:C20
Result A15:C20
Subtracting negative number from RangeObj
results in rows being added to end of range.
The same as adding a positive number.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="A", col_end="C", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'A10:C15'
>>> rng1.row_count
6
>>> rng2 = rng1 - -5
>>> str(rng2)
'A10:C20'
>>> repr(rng2)
"RangeObj(col_start='A', col_end='C', row_start=10, row_end=20, sheet_idx=0)"
>>> rng2.row_count
11
Start Range A10:C15
Result A10:C20
Subtracting RangeObj
from negative number results in rows being subtracted from start of range.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="A", col_end="C", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'A10:C15'
>>> rng1.row_count
6
>>> rng2 = -3 - rng1
>>> str(rng2)
'A7:C15'
>>> repr(rng2)
"RangeObj(col_start='A', col_end='C', row_start=7, row_end=15, sheet_idx=0)"
>>> rng2.row_count
9
Start Range A10:C15
Result A7:C15
RowObj
instances can also be used to subtract rows from a RangeObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>> from ooodev.utils.data_type.row_obj import RowObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.row_count
6
>>> rng2 = rng1 - RowObj.from_int(2)
>>> str(rng2)
'F10:H13'
>>> repr(rng2)
"RangeObj(col_start='F', col_end='H', row_start=10, row_end=13, sheet_idx=0)"
>>> rng2.row_count
4
Start Range F10:H15
Result F10:H13
Subtracting RangeObj
instance from RowObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>> from ooodev.utils.data_type.row_obj import RowObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.row_count
6
>>> rng2 = RowObj.from_int(2) - rng1
>>> str(rng2)
'F12:H15'
>>> repr(rng2)
"RangeObj(col_start='F', col_end='H', row_start=12, row_end=15, sheet_idx=0)"
>>> rng2.row_count
4
Start Range F10:H15
Result F12:H15
Subtract column string from RangeObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.col_count
3
>>> rng2 = rng1 - "B"
>>> str(rng2)
'F10:F15'
>>> repr(rng2)
"RangeObj(col_start='F', col_end='F', row_start=10, row_end=15, sheet_idx=0)"
>>> rng2.col_count
1
Start Range F10:H15
Result F10:F15
Subtract RangeObj
instance from column string.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.col_count
3
>>> rng2 = "B" - rng1
>>> str(rng2)
'H10:H15'
>>> repr(rng2)
"RangeObj(col_start='H', col_end='H', row_start=10, row_end=15, sheet_idx=0)"
>>> rng2.col_count
1
Start Range F10:H15
Result H10:H15
Subtract ColObj
instance from RangeObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>> from ooodev.utils.data_type.col_obj import ColObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.col_count
3
>>> rng2 = rng1 - ColObj.from_int(2)
>>> str(rng2)
'F10:F15'
>>> repr(rng2)
"RangeObj(col_start='F', col_end='F', row_start=10, row_end=15, sheet_idx=0)"
>>> rng2.col_count
1
Start Range F10:H15
Result F10:F15
Subtracting RangeObj
instance from ColObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>> from ooodev.utils.data_type.col_obj import ColObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.col_count
3
>>> rng2 = ColObj.from_int(2) - rng1
>>> str(rng2)
'H10:H15'
>>> repr(rng2)
"RangeObj(col_start='H', col_end='H', row_start=10, row_end=15, sheet_idx=0)"
>>> rng2.col_count
1
Start Range F10:H15
Result H10:H15
Subtracting CellObj
instance from RangeObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>> from ooodev.utils.data_type.cell_obj import CellObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.row_count
6
>>> rng1.col_count
3
>>> rng2 = rng1 - CellObj.from_idx(1, 1)
>>> str(rng2)
'F10:F13'
>>> repr(rng2)
"RangeObj(col_start='F', col_end='F', row_start=10, row_end=13, sheet_idx=0)"
>>> rng2.row_count
4
>>> rng2.col_count
1
Start Range H10:H15
Result F10:F13
Subtracting RangeObj
instance from CellObj
instance.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>> from ooodev.utils.data_type.cell_obj import CellObj
>>>
>>> rng1 = RangeObj(col_start="F", col_end="H", row_start=10, row_end=15, sheet_idx=0)
>>> str(rng1)
'F10:H15'
>>> rng1.row_count
6
>>> rng1.col_count
3
>>> rng2 = CellObj.from_idx(1, 1) - rng1
>>> str(rng2)
'H12:H15'
>>> repr(rng2)
"RangeObj(col_start='H', col_end='H', row_start=12, row_end=15, sheet_idx=0)"
>>> rng2.row_count
4
>>> rng2.col_count
1
Start Range H10:H15
Result H12:H15
Combine (merging) of RangeObj
is done using the /
operator (similar to Path
).
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="C", col_end="F", row_start=3, row_end=6, sheet_idx=0)
>>> str(rng1)
'C3:F6'
>>> rng2 = RangeObj(col_start="C", col_end="F", row_start=1, row_end=2, sheet_idx=0)
>>> str(rng2)
'C1:F2'
>>> rng3 = rng1 / rng2
>>> str(rng3)
'C1:F6'
First Range C3:F6
Second Range C1:F2
Combined: C1:F6
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="A", col_end="B", row_start=2, row_end=4, sheet_idx=0)
>>> str(rng1)
'A2:B4'
>>> rng2 = RangeObj(col_start="C", col_end="F", row_start=6, row_end=8, sheet_idx=0)
>>> str(rng2)
'C6:F8'
>>> rng3 = rng1 / rng2
>>> str(rng3)
'A2:F8'
First Range A2:B4
Second Range C6:F8
Combined: A2:F8
RangeObj
can be combined with String ranges and vice versa.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="A", col_end="B", row_start=2, row_end=4, sheet_idx=0)
>>> str(rng1)
'A2:B4'
>>> rng2 = rng1 / 'C6:F8'
>>> str(rng2)
'A2:F8'
rng2 = 'C6:F8' / rng1
is also valid.
First Range A2:B4
Second Range C6:F8
Combined: A2:F8
Multiple ranges can be combined.
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng1 = RangeObj(col_start="A", col_end="B", row_start=2, row_end=4, sheet_idx=0)
>>> rng2 = RangeObj(col_start="C", col_end="F", row_start=6, row_end=8, sheet_idx=0)
>>> rng3 = RangeObj(col_start="J", col_end="L", row_start=7, row_end=14, sheet_idx=0)
>>> rng4 = rng1 / rng2 / rng3 / "K12:O22"
>>> str(rng4)
'A2:O22'
To check if a cell is in a range, use the in
operator.
The in
operator checks if a cell is in a range and can check the same values as the
:py:meth:`~ooodev.utils.data_type.range_obj.contains` method.
Acceptable values are:
CellObj
CellAddress
CellValues
str
in the format"A1"
Example:
>>> from ooodev.utils.data_type.range_obj import RangeObj
>>>
>>> rng = RangeObj.from_range("AA2:AB7")
>>> print("AA3" in rng)
True
To iterate over a range, use the for
loop.
The iteration is done in a column-major order, meaning that the cells are iterated over by column, then by row.
# each cell is an instance of CellObj
>>> rng = RangeObj.from_range("A1:C4")
>>> for cell in rng:
>>> print(cell)
A1
B1
C1
A2
B2
C2
A3
B3
C3
A4
B4
C4