def read_dimension(xml_source):
source = _get_xml_iter(xml_source)
it = iterparse(source)
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
else:
element.clear()
return None
def read_dimension(xml_source):
min_row = min_col = max_row = max_col = None
source = _get_xml_iter(xml_source)
it = iterparse(source)
for event, el in it:
if el.tag == '{%s}dimension' % SHEET_MAIN_NS:
dim = el.get("ref")
if ':' in dim:
start, stop = dim.split(':')
else:
start = stop = dim
min_col, min_row = coordinate_from_string(start)
max_col, max_row = coordinate_from_string(stop)
return min_col, min_row, max_col, max_row
if el.tag == '{%s}row' % SHEET_MAIN_NS:
row = el.get("r")
if min_row is None:
min_row = int(row)
span = el.get("spans")
start, stop = span.split(":")
if min_col is None:
min_col = int(start)
max_col = int(stop)
else:
min_col = min(min_col, int(start))
max_col = max(max_col, int(stop))
max_row = int(row)
warn("Unsized worksheet")
return get_column_letter(min_col), min_row, get_column_letter(max_col), max_row
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 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 read_dimension(xml_source):
source = _get_xml_iter(xml_source)
it = iterparse(source)
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
else:
element.clear()
return None
def coord2rowcol(coord):
"""
Transform a spreadsheet style coordinate into a 0, 0 based index.
"""
col, row_idx = coordinate_from_string(coord)
col_idx = column_index_from_string(col)
return row_idx - 1, col_idx - 1
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). More work to come.
"""
keyfunc = lambda x: x[0]
# Get the raw coordinates for each cell given
raw_coords = [coordinate_from_string(cell) for cell in cells]
# Group up as {column: [list of rows]}
grouped_coords = OrderedDict((k, [c[1] for c in g]) for k, g in
groupby(sorted(raw_coords, key=keyfunc), keyfunc))
ranges = list(input_ranges)
# For each column, find contiguous ranges of rows
for column in grouped_coords:
rows = sorted(grouped_coords[column])
grouped_rows = [[r[1] for r in list(g)] for k, g in
groupby(enumerate(rows),
lambda x: x[0] - x[1])]
for rows in grouped_rows:
if len(rows) == 0:
pass
elif len(rows) == 1:
ranges.append("%s%d" % (column, rows[0]))
else:
ranges.append("%s%d:%s%d" % (column, rows[0], column, rows[-1]))
return " ".join(ranges)
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 _get_row(self, element, min_col=1, max_col=None):
"""Return cells from a particular row"""
col_counter = min_col
for cell in safe_iterator(element, CELL_TAG):
coord = cell.get('r')
column_str, row = coordinate_from_string(coord)
column = column_index_from_string(column_str)
if max_col is not None and column > max_col:
break
if min_col <= column:
for gap in range(col_counter, column):
# pad row with missing cells
yield ReadOnlyCell(self, row, None, None)
data_type = cell.get('t', 'n')
style_id = int(cell.get('s', 0))
formula = cell.findtext(FORMULA_TAG)
value = cell.find(VALUE_TAG)
if value is not None:
value = value.text
if formula is not None:
if not self.parent.data_only:
data_type = Cell.TYPE_FORMULA
value = "=%s" % formula
yield ReadOnlyCell(self, row, column_str, value, data_type,
style_id)
col_counter = column + 1
if max_col is not None:
while col_counter <= max_col:
yield ReadOnlyCell(self, row, None, None)
col_counter += 1
def get_cells(self, min_row, min_col, max_row, max_col):
p = iterparse(self.xml_source, tag=[ROW_TAG], remove_blank_text=True)
for _event, element in p:
if element.tag == ROW_TAG:
row = int(element.get("r"))
if max_row is not None and row > max_row:
break
if min_row <= row:
for cell in safe_iterator(element, CELL_TAG):
coord = cell.get('r')
column_str, row = coordinate_from_string(coord)
column = column_index_from_string(column_str)
if max_col is not None and column > max_col:
break
if min_col <= column:
data_type = cell.get('t', 'n')
style_id = cell.get('s')
formula = cell.findtext(FORMULA_TAG)
value = cell.findtext(VALUE_TAG)
if formula is not None and not self.parent.data_only:
data_type = Cell.TYPE_FORMULA
value = "=%s" % formula
yield ReadOnlyCell(row, column_str, value, data_type,
style_id)
if element.tag in (CELL_TAG, VALUE_TAG, FORMULA_TAG):
# sub-elements of rows should be skipped
continue
element.clear()
def get_cells(self, min_row, min_col, max_row, max_col):
p = iterparse(self.xml_source, tag=[ROW_TAG], remove_blank_text=True)
for _event, element in p:
if element.tag == ROW_TAG:
row = int(element.get("r"))
if max_row is not None and row > max_row:
break
if min_row <= row:
for cell in safe_iterator(element, CELL_TAG):
coord = cell.get('r')
column_str, row = coordinate_from_string(coord)
column = column_index_from_string(column_str)
if max_col is not None and column > max_col:
break
if min_col <= column:
data_type = cell.get('t', 'n')
style_id = cell.get('s')
formula = cell.findtext(FORMULA_TAG)
value = cell.findtext(VALUE_TAG)
if formula is not None and not self.parent.data_only:
data_type = Cell.TYPE_FORMULA
value = "=%s" % formula
yield ReadOnlyCell(self, row, column_str,
value, data_type, style_id)
if element.tag in (CELL_TAG, VALUE_TAG, FORMULA_TAG):
# sub-elements of rows should be skipped
continue
element.clear()
def _get_row(self, element, min_col=1, max_col=None):
"""Return cells from a particular row"""
col_counter = min_col
for cell in safe_iterator(element, CELL_TAG):
coord = cell.get('r')
column_str, row = coordinate_from_string(coord)
column = column_index_from_string(column_str)
if max_col is not None and column > max_col:
break
if min_col <= column:
for gap in range(col_counter, column):
# pad row with missing cells
yield ReadOnlyCell(self, row, None, None)
data_type = cell.get('t', 'n')
style_id = int(cell.get('s', 0))
formula = cell.findtext(FORMULA_TAG)
value = cell.find(VALUE_TAG)
if value is not None:
value = value.text
if formula is not None:
if not self.parent.data_only:
data_type = Cell.TYPE_FORMULA
value = "=%s" % formula
yield ReadOnlyCell(self, row, column_str,
value, data_type, style_id)
col_counter = column + 1
if max_col is not None:
while col_counter <= max_col:
yield ReadOnlyCell(self, row, None, None)
col_counter += 1
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). More work to come.
"""
keyfunc = lambda x: x[0]
# Get the raw coordinates for each cell given
raw_coords = [coordinate_from_string(cell) for cell in cells]
# Group up as {column: [list of rows]}
grouped_coords = OrderedDict((k, [c[1] for c in g]) for k, g in
groupby(sorted(raw_coords, key=keyfunc), keyfunc))
ranges = list(input_ranges)
# For each column, find contiguous ranges of rows
for column in grouped_coords:
rows = sorted(grouped_coords[column])
grouped_rows = [[r[1] for r in list(g)] for k, g in
groupby(enumerate(rows),
lambda x: x[0] - x[1])]
for rows in grouped_rows:
if len(rows) == 0:
pass
elif len(rows) == 1:
ranges.append("%s%d" % (column, rows[0]))
else:
ranges.append("%s%d:%s%d" % (column, rows[0], column, rows[-1]))
return " ".join(ranges)
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 write_sheetviews(worksheet):
views = Element('sheetViews')
sheetviewAttrs = {'workbookViewId': '0'}
if not worksheet.show_gridlines:
sheetviewAttrs['showGridLines'] = '0'
view = SubElement(views, 'sheetView', sheetviewAttrs)
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'))
view.append(Element('pane', paneAttrs))
selectionAttrs['pane'] = pane
if row > 1 and column > 1:
SubElement(view, 'selection', {'pane': 'topRight'})
SubElement(view, 'selection', {'pane': 'bottomLeft'})
selectionAttrs.update({'activeCell': worksheet.active_cell,
'sqref': worksheet.selected_cell})
SubElement(view, 'selection', selectionAttrs)
return views
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 get_range_boundaries(range_string, row_offset=0, column_offset=1):
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)
max_col = column_index_from_string(max_col) + 1
else:
min_col, min_row = coordinate_from_string(range_string)
min_col = column_index_from_string(min_col)
max_col = min_col + column_offset
max_row = min_row + row_offset
return (min_col, min_row, max_col, max_row)
def get_range_boundaries(range_string, row_offset=0, column_offset=1):
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)
max_col = column_index_from_string(max_col) + 1
else:
min_col, min_row = coordinate_from_string(range_string)
min_col = column_index_from_string(min_col)
max_col = min_col + column_offset
max_row = min_row + row_offset
return (min_col, min_row, max_col, max_row)
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 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 read_dimension(xml_source):
min_row = min_col = max_row = max_col = None
source = _get_xml_iter(xml_source)
it = iterparse(source)
for event, el in it:
if el.tag == '{%s}dimension' % SHEET_MAIN_NS:
dim = el.get("ref")
if ':' in dim:
start, stop = dim.split(':')
else:
start = stop = dim
min_col, min_row = coordinate_from_string(start)
max_col, max_row = coordinate_from_string(stop)
return min_col, min_row, max_col, max_row
elif el.tag == '{%s}row' % SHEET_MAIN_NS:
row = el.get("r")
if min_row is None:
min_row = int(row)
span = el.get("spans", "")
if ":" in span:
start, stop = span.split(":")
if min_col is None:
min_col = int(start)
max_col = int(stop)
else:
min_col = min(min_col, int(start))
max_col = max(max_col, int(stop))
elif el.tag == '{%s}c' % SHEET_MAIN_NS:
coord = el.get('r')
column_str, row = coordinate_from_string(coord)
column = column_index_from_string(column_str)
if min_col is None:
min_col = column
else:
min_col = min(column, min_col)
if max_col is None:
max_col = column
else:
max_col = max(column, max_col)
el.clear()
max_row = int(row)
warn("Unsized worksheet")
return get_column_letter(min_col), min_row, get_column_letter(max_col), max_row
def read_dimension(xml_source):
min_row = min_col = max_row = max_col = None
source = _get_xml_iter(xml_source)
it = iterparse(source)
for event, el in it:
if el.tag == "{%s}dimension" % SHEET_MAIN_NS:
dim = el.get("ref")
if ":" in dim:
start, stop = dim.split(":")
else:
start = stop = dim
min_col, min_row = coordinate_from_string(start)
max_col, max_row = coordinate_from_string(stop)
return min_col, min_row, max_col, max_row
elif el.tag == "{%s}row" % SHEET_MAIN_NS:
row = el.get("r")
if min_row is None:
min_row = int(row)
span = el.get("spans", "")
if ":" in span:
start, stop = span.split(":")
if min_col is None:
min_col = int(start)
max_col = int(stop)
else:
min_col = min(min_col, int(start))
max_col = max(max_col, int(stop))
elif el.tag == "{%s}c" % SHEET_MAIN_NS:
coord = el.get("r")
column_str, row = coordinate_from_string(coord)
column = column_index_from_string(column_str)
if min_col is None:
min_col = column
else:
min_col = min(column, min_col)
if max_col is None:
max_col = column
else:
max_col = max(column, max_col)
el.clear()
max_row = int(row)
warn("Unsized worksheet")
return get_column_letter(min_col), min_row, get_column_letter(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 style_range(ws, cell_range, style=None):
"""
:param ws: Excel worksheet instance
:param range: An excel range to style (e.g. A1:F20)
:param style: An openpyxl Style object
"""
start_cell, end_cell = cell_range.split(':')
start_coord = ce.coordinate_from_string(start_cell)
start_row = start_coord[1]
start_col = ce.column_index_from_string(start_coord[0])
end_coord = ce.coordinate_from_string(end_cell)
end_row = end_coord[1]
end_col = ce.column_index_from_string(end_coord[0])
for row in range(start_row, end_row + 1):
for col_idx in range(start_col, end_col + 1):
col = ce.get_column_letter(col_idx)
ws.cell('%s%s' % (col, row)).style = style
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 read_dimension(source):
min_row = min_col = max_row = max_col = None
DIMENSION_TAG = '{%s}dimension' % SHEET_MAIN_NS
DATA_TAG = '{%s}sheetData' % SHEET_MAIN_NS
it = iterparse(source, tag=[DIMENSION_TAG, DATA_TAG])
for _event, element in it:
if element.tag == DIMENSION_TAG:
dim = element.get("ref")
if ':' in dim:
start, stop = dim.split(':')
else:
start = stop = dim
min_col, min_row = coordinate_from_string(start)
max_col, max_row = coordinate_from_string(stop)
return min_col, min_row, max_col, max_row
elif element.tag == DATA_TAG:
# Dimensions missing
break
element.clear()
def read_dimension(source):
min_row = min_col = max_row = max_col = None
DIMENSION_TAG = '{%s}dimension' % SHEET_MAIN_NS
DATA_TAG = '{%s}sheetData' % SHEET_MAIN_NS
it = iterparse(source, tag=[DIMENSION_TAG, DATA_TAG])
for _event, element in it:
if element.tag == DIMENSION_TAG:
dim = element.get("ref")
if ':' in dim:
start, stop = dim.split(':')
else:
start = stop = dim
min_col, min_row = coordinate_from_string(start)
max_col, max_row = coordinate_from_string(stop)
return min_col, min_row, max_col, max_row
elif element.tag == DATA_TAG:
# Dimensions missing
break
element.clear()
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 ":" not in range_string:
if COORD_RE.match(range_string):
return # Single cell
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 range(min_col, max_col + 1):
for row in range(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 _get_cell(self, coordinate):
if not coordinate in self._cells:
column, row = coordinate_from_string(coordinate)
new_cell = 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 get_square_indices(sheet, location):
left, right = location.upper().split(':')
left_column_letter, left_row = coordinate_from_string(left)
left_column = column_index_from_string(left_column_letter)
if right:
right_column_letter, right_row = coordinate_from_string(right)
right_column = column_index_from_string(right_column_letter)
right_defined = True
else:
right_row = sheet.get_highest_row()
right_column = sheet.get_highest_column()
right_defined = False
if left_column > right_column:
raise BadRequest(detail="Left column should be less than right")
if left_row <= 0 or right_row <= 0:
raise BadRequest(detail="Row index should be greater than 0")
if left_row > right_row:
raise BadRequest(
detail="Left row index should be less or equal than right row")
return left_column, left_row, right_column, right_row, right_defined
def _get_cell(self, coordinate):
if not coordinate in self._cells:
column, row = coordinate_from_string(coordinate)
new_cell = openpyxl.cell.Cell(self, column, row)
self._cells[coordinate] = new_cell
if column not in self.column_dimensions:
self.column_dimensions[column] = ColumnDimension(column, worksheet=self)
if row not in self.row_dimensions:
self.row_dimensions[row] = RowDimension(row, worksheet=self)
return self._cells[coordinate]
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 iterateOverCoordinates():
processing_range = 'E3:CJ11'
print(processing_range)
upperleft = (processing_range.partition(':')[0])
lowerright = (processing_range.partition(':')[2])
print(upperleft, lowerright)
xy = coordinate_from_string(upperleft)
print (xy)
startcol = column_index_from_string(xy[0])
print('startcol', startcol)
xy = coordinate_from_string(lowerright)
print (xy)
endcol = column_index_from_string(xy[0])
print('endcol', endcol)
maxcol = endcol+1
print('maxcol', maxcol)
icol = 5
print(icol)
column = get_column_letter(icol)
print(column)
def write_worksheet_sheetviews(doc, worksheet):
from openpyxl.writer.worksheet import start_tag, end_tag, tag
from openpyxl.cell import coordinate_from_string
from openpyxl.worksheet import column_index_from_string
viewattrs = {'workbookViewId': '0'}
#その他ビューパラメータを取得
sheetview = worksheet.sheet_view
if sheetview:
if hasattr(sheetview, 'zoomScale'):
viewattrs['zoomScale'] = sheetview.zoomScale
if hasattr(sheetview, 'zoomScaleNormal'):
viewattrs['zoomScaleNormal'] = sheetview.zoomScaleNormal
start_tag(doc, 'sheetViews')
start_tag(doc, 'sheetView', viewattrs)
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 _write_comment_shape(self, comment, idx):
# get zero-indexed coordinates of the comment
col, row = coordinate_from_string(comment._parent.coordinate)
row -= 1
column = column_index_from_string(col) - 1
style = ("position:absolute; margin-left:59.25pt;"
"margin-top:1.5pt;width:%(width)s;height:%(height)s;"
"z-index:1;visibility:hidden") % {
'height': comment._height,
'width': comment._width
}
attrs = {
"id": "_x0000_s%04d" % idx,
"type": "#_x0000_t202",
"style": style,
"fillcolor": "#ffffe1",
"{%s}insetmode" % officens: "auto"
}
shape = Element("{%s}shape" % vmlns, attrs)
SubElement(shape, "{%s}fill" % vmlns, {"color2": "#ffffe1"})
SubElement(shape, "{%s}shadow" % vmlns, {
"color": "black",
"obscured": "t"
})
SubElement(shape, "{%s}path" % vmlns,
{"{%s}connecttype" % officens: "none"})
textbox = SubElement(shape, "{%s}textbox" % vmlns,
{"style": "mso-direction-alt:auto"})
SubElement(textbox, "div", {"style": "text-align:left"})
client_data = SubElement(shape, "{%s}ClientData" % excelns,
{"ObjectType": "Note"})
SubElement(client_data, "{%s}MoveWithCells" % excelns)
SubElement(client_data, "{%s}SizeWithCells" % excelns)
SubElement(client_data, "{%s}AutoFill" % excelns).text = "False"
SubElement(client_data, "{%s}Row" % excelns).text = "%d" % row
SubElement(client_data, "{%s}Column" % excelns).text = "%d" % column
return shape
def _write_comment_shape(self, comment, idx):
# get zero-indexed coordinates of the comment
col, row = coordinate_from_string(comment._parent.coordinate)
row -= 1
column = column_index_from_string(col) - 1
style = ("position:absolute; margin-left:59.25pt;"
"margin-top:1.5pt;width:%(width)s;height:%(height)s;"
"z-index:1;visibility:hidden") % {'height': comment._height,
'width': comment._width}
attrs = {
"id": "_x0000_s%04d" % idx ,
"type": "#_x0000_t202",
"style": style,
"fillcolor": "#ffffe1",
"{%s}insetmode" % officens: "auto"
}
shape = Element("{%s}shape" % vmlns, attrs)
SubElement(shape, "{%s}fill" % vmlns,
{"color2": "#ffffe1"})
SubElement(shape, "{%s}shadow" % vmlns,
{"color": "black", "obscured": "t"})
SubElement(shape, "{%s}path" % vmlns,
{"{%s}connecttype" % officens: "none"})
textbox = SubElement(shape, "{%s}textbox" % vmlns,
{"style": "mso-direction-alt:auto"})
SubElement(textbox, "div", {"style": "text-align:left"})
client_data = SubElement(shape, "{%s}ClientData" % excelns,
{"ObjectType": "Note"})
SubElement(client_data, "{%s}MoveWithCells" % excelns)
SubElement(client_data, "{%s}SizeWithCells" % excelns)
SubElement(client_data, "{%s}AutoFill" % excelns).text = "False"
SubElement(client_data, "{%s}Row" % excelns).text = "%d" % row
SubElement(client_data, "{%s}Column" % excelns).text = "%d" % column
return shape
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)
def test_zero_row():
with pytest.raises(CellCoordinatesException):
coordinate_from_string('AQ0')
def test_roundtrip(self, value):
wb = Workbook()
ws = wb.get_active_sheet()
assert ws.point_pos(
*ws.cell(value).anchor) == coordinate_from_string(value)
def test_roundtrip(self, value):
wb = Workbook()
ws = wb.get_active_sheet()
assert ws.point_pos(*ws.cell(value).anchor) == coordinate_from_string(value)
import requests
from datetime import datetime, date, time
wb = load_workbook('biorepositories_from_website.xlsx')
ws = wb.get_active_sheet()
wb1 = Workbook()
ws1 = wb1.active
ws1.title = "GRbio repos"
START_ROW = 2
END_ROW = ws.get_highest_row()
GR_URL_COL = 'A'
GR_INSTIT_NAME_COL = 'B'
GR_REPO_NAME_COL = 'C'
GR_STATUS_COL = 'H'
#class Entry(self, name):
# self.name = name
#institutionName
#repoName
#URL
#desc
#contact
for row in ws.range('%s%s:%s%s'%(GR_REPO_NAME_COL, START_ROW, GR_REPO_NAME_COL, END_ROW)):
for cell in row:
coordinate = coordinate_from_string(cell.get_coordinate())
if cell.value == None or cell.value == "Herbarium" or cell.value == "Herbario":
cell.value = ws['%s%s'%(GR_INSTIT_NAME_COL, coordinate[1])].value
wb.save("biorepositories_from_website.xlsx")
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)
def test_invalid_coordinate():
with pytest.raises(CellCoordinatesException):
coordinate_from_string('AAA')
def get_mins_maxs_from_range(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)
return (min_col, min_row, max_col, max_row)
def test_roundtrip(self):
wb = Workbook()
ws = wb.get_active_sheet()
for address in ('A1', 'D52', 'X11'):
eq_(ws.point_pos(*ws.cell(address).anchor),
coordinate_from_string(address))
def test_coordinates():
column, row = coordinate_from_string('ZF46')
assert "ZF" == column
assert 46 == row
def test_invalid_coordinate():
coordinate_from_string('AAA')
def test_zero_row():
coordinate_from_string('AQ0')
def test_coordinates():
column, row = coordinate_from_string('ZF46')
assert "ZF" == column
assert 46 == row
def test_invalid_coordinate():
with pytest.raises(CellCoordinatesException):
coordinate_from_string('AAA')
def test_zero_row():
with pytest.raises(CellCoordinatesException):
coordinate_from_string('AQ0')
def get_row_col_from_string(string):
xy = coordinate_from_string(string) # returns ('A',4)
col = column_index_from_string(xy[0]) # returns 1
row = xy[1]
return ([row, col])
def duplicate_cell_dimensions(cell, new_cell, worksheet):
worksheet.row_dimensions[coordinate_from_string(new_cell.address)[1]] = \
worksheet.row_dimensions[coordinate_from_string(cell.address)[1]]
worksheet.column_dimensions[coordinate_from_string(new_cell.address)[0]] = \
worksheet.column_dimensions[coordinate_from_string(cell.address)[0]]
def test_coordinates():
column, row = coordinate_from_string('ZF46')
eq_("ZF", column)
eq_(46, row)
def test_coordinates():
column, row = coordinate_from_string('ZF46')
eq_("ZF", column)
eq_(46, row)
def test_zero_row():
coordinate_from_string('AQ0')
def test_invalid_coordinate():
coordinate_from_string('AAA')