def unmerge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Remove merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string:
if start_row is None or start_column is None or end_row is None or end_column is None:
msg = "You have to provide a value either for "\
"'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise InsufficientCoordinatesException(msg)
else:
range_string = '%s%s:%s%s' % (get_column_letter(start_column + 1), start_row + 1, get_column_letter(end_column + 1), end_row + 1)
elif len(range_string.split(':')) != 2:
msg = "Range must be a cell range (e.g. A1:E1)"
raise InsufficientCoordinatesException(msg)
else:
range_string = range_string.replace('$', '')
if range_string in self._merged_cells:
self._merged_cells.remove(range_string)
min_col, min_row = coordinate_from_string(range_string.split(':')[0])
max_col, max_row = coordinate_from_string(range_string.split(':')[1])
min_col = column_index_from_string(min_col)
max_col = column_index_from_string(max_col)
# Mark cell as unmerged
for col in xrange(min_col, max_col + 1):
for row in xrange(min_row, max_row + 1):
if not (row == min_row and col == min_col):
self._get_cell('%s%s' % (get_column_letter(col), row)).merged = False
else:
msg = 'Cell range %s not known as merged.' % range_string
raise InsufficientCoordinatesException(msg)
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Set merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string:
if start_row is None or start_column is None or end_row is None or end_column is None:
msg = "You have to provide a value either for "\
"'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise InsufficientCoordinatesException(msg)
else:
range_string = '%s%s:%s%s' % (get_column_letter(start_column + 1), start_row + 1, get_column_letter(end_column + 1), end_row + 1)
elif len(range_string.split(':')) != 2:
msg = "Range must be a cell range (e.g. A1:E1)"
raise InsufficientCoordinatesException(msg)
else:
range_string = range_string.replace('$', '')
# Make sure top_left cell exists - is this necessary?
min_col, min_row = coordinate_from_string(range_string.split(':')[0])
max_col, max_row = coordinate_from_string(range_string.split(':')[1])
min_col = column_index_from_string(min_col)
max_col = column_index_from_string(max_col)
# Blank out the rest of the cells in the range
for col in xrange(min_col, max_col + 1):
for row in xrange(min_row, max_row + 1):
if not (row == min_row and col == min_col):
# PHPExcel adds cell and specifically blanks it out if it doesn't exist
self._get_cell('%s%s' % (get_column_letter(col), row)).value = None
self._get_cell('%s%s' % (get_column_letter(col), row)).merged = True
if range_string not in self._merged_cells:
self._merged_cells.append(range_string)
def write_worksheet_sheetviews(doc, worksheet):
start_tag(doc, 'sheetViews')
start_tag(doc, 'sheetView', {'workbookViewId': '0'})
selectionAttrs = {}
topLeftCell = worksheet.freeze_panes
if topLeftCell:
colName, row = coordinate_from_string(topLeftCell)
column = column_index_from_string(colName)
pane = 'topRight'
paneAttrs = {}
if column > 1:
paneAttrs['xSplit'] = str(column - 1)
if row > 1:
paneAttrs['ySplit'] = str(row - 1)
pane = 'bottomLeft'
if column > 1:
pane = 'bottomRight'
paneAttrs.update(dict(topLeftCell=topLeftCell,
activePane=pane,
state='frozen'))
tag(doc, 'pane', paneAttrs)
selectionAttrs['pane'] = pane
if row > 1 and column > 1:
tag(doc, 'selection', {'pane': 'topRight'})
tag(doc, 'selection', {'pane': 'bottomLeft'})
selectionAttrs.update({'activeCell': worksheet.active_cell,
'sqref': worksheet.selected_cell})
tag(doc, 'selection', selectionAttrs)
end_tag(doc, 'sheetView')
end_tag(doc, 'sheetViews')
def read_dimension(xml_source):
source = _get_xml_iter(xml_source)
it = iterparse(source)
smax_col = None
smax_row = None
smin_col = None
smin_row = None
for event, element in it:
if element.tag == '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}dimension':
ref = element.get('ref')
if ':' in ref:
min_range, max_range = ref.split(':')
else:
min_range = max_range = ref
min_col, min_row = coordinate_from_string(min_range)
max_col, max_row = coordinate_from_string(max_range)
return min_col, min_row, max_col, max_row
if element.tag == '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}c':
# Supposedly the dimension is mandatory, but in practice it can be
# left off sometimes, if so, observe the max/min extants and return
# those instead.
col, row = coordinate_from_string(element.get('r'))
if smin_row is None:
#initialize the observed max/min values
smin_col = smax_col = col
smin_row = smax_row = row
else:
#Keep track of the seen max and min (fallback if there's no dimension)
smin_col = min(smin_col, col)
smin_row = min(smin_row, row)
smax_col = max(smax_col, col)
smax_row = max(smax_row, row)
else:
element.clear()
return smin_col, smin_row, smax_col, smax_row
def get_range_boundaries(range_string, row=0, column=0):
if ':' in range_string:
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)
min_col = column_index_from_string(min_col) + column
max_col = column_index_from_string(max_col) + column
min_row += row
max_row += row
else:
min_col, min_row = coordinate_from_string(range_string)
min_col = column_index_from_string(min_col)
max_col = min_col + 1
max_row = min_row
return (min_col, min_row, max_col, max_row)
def get_range_boundaries(range_string, row=0, column=0):
if ":" in range_string:
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)
min_col = column_index_from_string(min_col) + column
max_col = column_index_from_string(max_col) + column
min_row += row
max_row += row
else:
min_col, min_row = coordinate_from_string(range_string)
min_col = column_index_from_string(min_col)
max_col = min_col + 1
max_row = min_row
return (min_col, min_row, max_col, max_row)
def _get_cell(self, coordinate):
if not coordinate in self._cells:
column, row = coordinate_from_string(coordinate)
new_cell = spreadsheet.openpyxl.cell.Cell(self, column, row)
self._cells[coordinate] = new_cell
if column not in self.column_dimensions:
self.column_dimensions[column] = ColumnDimension(column)
if row not in self.row_dimensions:
self.row_dimensions[row] = RowDimension(row)
return self._cells[coordinate]
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)