def get_named_range(self, range_string):
"""
Returns a 2D array of cells, with optional row and column offsets.
:param range_string: `named range` name
:type range_string: string
:rtype: tuples of tuples of :class:`openpyxl.cell.Cell
"""
named_range = self._parent.get_named_range(range_string)
if named_range is None:
msg = '%s is not a valid range name' % range_string
raise NamedRangeException(msg)
if not isinstance(named_range, NamedRange):
msg = '%s refers to a value, not a range' % range_string
raise NamedRangeException(msg)
result = []
for destination in named_range.destinations:
worksheet, cells_range = destination
if worksheet is not self:
msg = 'Range %s is not defined on worksheet %s' % \
(cells_range, self.title)
raise NamedRangeException(msg)
for row in self.iter_rows(cells_range):
result.extend(row)
return tuple(result)
def split_named_range(range_string):
"""Separate a named range into its component parts"""
for range_string in SPLIT_NAMED_RANGE_RE.split(range_string)[1::2]: # Skip first and from there every second item
match = NAMED_RANGE_RE.match(range_string)
if match is None:
raise NamedRangeException('Invalid named range string: "%s"' % range_string)
else:
match = match.groupdict()
sheet_name = match['quoted'] or match['notquoted']
xlrange = match['range']
sheet_name = sheet_name.replace("''", "'") # Unescape '
yield sheet_name, xlrange
def range(self, range_string, row=0, column=0):
"""Returns a 2D array of cells, with optional row and column offsets.
:param range_string: cell range string or `named range` name
:type range_string: string
:param row: number of rows to offset
:type row: int
:param column: number of columns to offset
:type column: int
:rtype: tuples of tuples of :class:`openpyxl.cell.Cell`
"""
if ':' in range_string:
# R1C1 range
result = []
min_range, max_range = range_string.split(':')
min_col, min_row = coordinate_from_string(min_range)
max_col, max_row = coordinate_from_string(max_range)
if column:
min_col = get_column_letter(
column_index_from_string(min_col) + column)
max_col = get_column_letter(
column_index_from_string(max_col) + column)
min_col = column_index_from_string(min_col)
max_col = column_index_from_string(max_col)
cache_cols = {}
for col in xrange(min_col, max_col + 1):
cache_cols[col] = get_column_letter(col)
rows = xrange(min_row + row, max_row + row + 1)
cols = xrange(min_col, max_col + 1)
for row in rows:
new_row = []
for col in cols:
new_row.append(self.cell('%s%s' % (cache_cols[col], row)))
result.append(tuple(new_row))
return tuple(result)
else:
try:
return self.cell(coordinate=range_string,
row=row,
column=column)
except CellCoordinatesException:
pass
# named range
named_range = self._parent.get_named_range(range_string)
if named_range is None:
msg = '%s is not a valid range name' % range_string
raise NamedRangeException(msg)
if isinstance(named_range, NamedRangeContainingValue):
msg = '%s refers to a value, not a range' % range_string
raise NamedRangeException(msg)
result = []
for destination in named_range.destinations:
worksheet, cells_range = destination
if worksheet is not self:
msg = 'Range %s is not defined on worksheet %s' % \
(cells_range, self.title)
raise NamedRangeException(msg)
content = self.range(cells_range)
if isinstance(content, tuple):
for cells in content:
result.extend(cells)
else:
result.append(content)
if len(result) == 1:
return result[0]
else:
return tuple(result)