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 iter_rows(workbook_name, sheet_name, xml_source, shared_date, string_table, range_string='', row_offset=0, column_offset=0): archive = get_archive_file(workbook_name) source = xml_source if range_string: min_col, min_row, max_col, max_row = get_range_boundaries( range_string, row_offset, column_offset) else: min_col, min_row, max_col, max_row = read_dimension(xml_source=source) min_col = column_index_from_string(min_col) max_col = column_index_from_string(max_col) + 1 max_row += 6 style_table = read_style_table(archive.read(ARC_STYLE)) source.seek(0) p = iterparse(source) return get_squared_range(p, min_col, min_row, max_col, max_row, string_table, style_table, shared_date)
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 get_cells(p, min_row, min_col, max_row, max_col, _re_coordinate=RE_COORDINATE): for _event, element in p: if element.tag == '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}c': coord = element.get('r') column_str, row = _re_coordinate.match(coord).groups() row = int(row) column = column_index_from_string(column_str) if min_col <= column <= max_col and min_row <= row <= max_row: data_type = element.get('t', 'n') style_id = element.get('s') value = element.findtext( '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v' ) yield RawCell(row, column_str, coord, value, data_type, style_id, None) if element.tag == '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v': continue element.clear()
def append(self, list_or_dict): """Appends a group of values at the bottom of the current sheet. * If it's a list: all values are added in order, starting from the first column * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters) :param list_or_dict: list or dict containing values to append :type list_or_dict: list/tuple or dict Usage: * append(['This is A1', 'This is B1', 'This is C1']) * **or** append({'A' : 'This is A1', 'C' : 'This is C1'}) * **or** append({0 : 'This is A1', 2 : 'This is C1'}) :raise: TypeError when list_or_dict is neither a list/tuple nor a dict """ row_idx = len(self.row_dimensions) if isinstance(list_or_dict, (list, tuple)): for col_idx, content in enumerate(list_or_dict): self.cell(row=row_idx, column=col_idx).value = content elif isinstance(list_or_dict, dict): for col_idx, content in iteritems(list_or_dict): if isinstance(col_idx, basestring): col_idx = column_index_from_string(col_idx) - 1 self.cell(row=row_idx, column=col_idx).value = content else: raise TypeError('list_or_dict must be a list or a dict')
def write_worksheet_cols(doc, worksheet): """Write worksheet columns to xml.""" if worksheet.column_dimensions: start_tag(doc, 'cols') for column_string, columndimension in \ iteritems(worksheet.column_dimensions): col_index = column_index_from_string(column_string) col_def = {} col_def['collapsed'] = str(columndimension.style_index) col_def['min'] = str(col_index) col_def['max'] = str(col_index) if columndimension.width != \ worksheet.default_column_dimension.width: col_def['customWidth'] = 'true' if not columndimension.visible: col_def['hidden'] = 'true' if columndimension.outline_level > 0: col_def['outlineLevel'] = str(columndimension.outline_level) if columndimension.collapsed: col_def['collapsed'] = 'true' if columndimension.auto_size: col_def['bestFit'] = 'true' if columndimension.width > 0: col_def['width'] = str(columndimension.width) else: col_def['width'] = '9.10' tag(doc, 'col', col_def) end_tag(doc, 'cols')
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=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_highest_column(self): """Get the largest value for column currently stored. :rtype: int """ if self.column_dimensions: return max([column_index_from_string(column_index) for column_index in self.column_dimensions]) else: return 1
def iter_rows( workbook_name, sheet_name, xml_source, shared_date, string_table, range_string="", row_offset=0, column_offset=0 ): archive = get_archive_file(workbook_name) source = xml_source if range_string: min_col, min_row, max_col, max_row = get_range_boundaries(range_string, row_offset, column_offset) else: min_col, min_row, max_col, max_row = read_dimension(xml_source=source) min_col = column_index_from_string(min_col) max_col = column_index_from_string(max_col) + 1 max_row += 6 style_table = read_style_table(archive.read(ARC_STYLE)) source.seek(0) p = iterparse(source) return get_squared_range(p, min_col, min_row, max_col, max_row, string_table, style_table, shared_date)
def get_cells(p, min_row, min_col, max_row, max_col, _re_coordinate=RE_COORDINATE): for _event, element in p: if element.tag == "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}c": coord = element.get("r") column_str, row = _re_coordinate.match(coord).groups() row = int(row) column = column_index_from_string(column_str) if min_col <= column <= max_col and min_row <= row <= max_row: data_type = element.get("t", "n") style_id = element.get("s") value = element.findtext("{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v") yield RawCell(row, column_str, coord, value, data_type, style_id, None) if element.tag == "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v": continue element.clear()
def get_highest_column(self): return column_index_from_string(self._max_column)
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 row_sort(cell): """Translate column names for sorting.""" return column_index_from_string(cell.column)