def __str__(self):
fmt = u"{0}!${1}${2}:${3}${4}"
if (self.min_col == self.max_col and self.min_row == self.max_row):
fmt = u"{0}!${1}${2}"
return fmt.format(self.sheetname,
get_column_letter(self.min_col), self.min_row,
get_column_letter(self.max_col), self.max_row)
def calculate_dimension(self, force=False):
if not all([self.max_column, self.max_row]):
if force:
self._calculate_dimension()
else:
raise ValueError(
"Worksheet is unsized, use calculate_dimension(force=True)"
)
return '%s%d:%s%d' % (get_column_letter(self.min_column), self.min_row,
get_column_letter(self.max_column), self.max_row)
def coord(self):
"""
Excel style representation of the range
"""
fmt = "{min_col}{min_row}:{max_col}{max_row}"
if (self.min_col == self.max_col and self.min_row == self.max_row):
fmt = "{min_col}{min_row}"
return fmt.format(min_col=get_column_letter(self.min_col),
min_row=self.min_row,
max_col=get_column_letter(self.max_col),
max_row=self.max_row)
def collapse_cell_addresses(cells, input_ranges=()):
""" Collapse a collection of cell co-ordinates down into an optimal
range or collection of ranges.
E.g. Cells A1, A2, A3, B1, B2 and B3 should have the data-validation
object applied, attempt to collapse down to a single range, A1:B3.
Currently only collapsing contiguous vertical ranges (i.e. above
example results in A1:A3 B1:B3).
"""
ranges = list(input_ranges)
# convert cell into row, col tuple
raw_coords = (coordinate_to_tuple(cell) for cell in cells)
# group by column in order
grouped_coords = defaultdict(list)
for row, col in sorted(raw_coords, key=itemgetter(1)):
grouped_coords[col].append(row)
# create range string from first and last row in column
for col, cells in grouped_coords.items():
col = get_column_letter(col)
fmt = "{0}{1}:{2}{3}"
if len(cells) == 1:
fmt = "{0}{1}"
r = fmt.format(col, min(cells), col, max(cells))
ranges.append(r)
return " ".join(ranges)
def cols(self):
"""
Return all cells in range by row
"""
for col in range(self.min_col, self.max_col + 1):
yield tuple('%s%d' % (get_column_letter(col), row)
for row in range(self.min_row, self.max_row + 1))
def parse_column_dimensions(self, col):
attrs = dict(col.attrib)
column = get_column_letter(int(attrs['min']))
attrs['index'] = column
if 'style' in attrs:
attrs['style'] = self.styles[int(attrs['style'])]
dim = ColumnDimension(self.ws, **attrs)
self.ws.column_dimensions[column] = dim
def calculate_dimension(self):
"""Return the minimum bounding range for all cells containing data (ex. 'A1:M24')
:rtype: string
"""
if self._cells:
rows = set()
cols = set()
for row, col in self._cells:
rows.add(row)
cols.add(col)
max_row = max(rows)
max_col = max(cols)
min_col = min(cols)
min_row = min(rows)
else:
return "A1:A1"
return '%s%d:%s%d' % (get_column_letter(min_col), min_row,
get_column_letter(max_col), max_row)
def translate_col(col_str, cdelta):
"""
Translate a range col-snippet by the given number of columns
"""
if col_str.startswith('$'):
return col_str
else:
try:
return get_column_letter(
column_index_from_string(col_str) + cdelta)
except ValueError:
raise TranslatorError("Formula out of range")
def add_print_title(self, n, rows_or_cols='rows'):
""" Print Titles are rows or columns that are repeated on each printed sheet.
This adds n rows or columns at the top or left of the sheet
"""
scope = self.parent.get_index(self)
if rows_or_cols == 'cols':
self.print_title_cols = 'A:%s' % get_column_letter(n)
else:
self.print_title_rows = '1:%d' % n
def test_column_letter(value, expected):
assert get_column_letter(value) == expected
def test_column_letter_boundries(value):
with pytest.raises(ValueError):
get_column_letter(value)
def column(self):
"""The letter of this cell's column (ex. 'A')"""
return get_column_letter(self.col_idx)