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), start_row, get_column_letter(end_column), end_row) 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('$', '') if range_string not in self._merged_cells: self._merged_cells.append(range_string) cells = cells_from_range(range_string) # only the top-left cell is preserved for c in islice(chain.from_iterable(cells), 1, None): if c in self._cells: del self._cells[c]
def write_rows(xf, worksheet, string_table, style_table=None): """Write worksheet data to xml.""" # Ensure a blank cell exists if it has a style for styleCoord in iterkeys(worksheet._styles): if isinstance(styleCoord, str) and COORD_RE.search(styleCoord): worksheet.cell(styleCoord) # create rows of cells cells_by_row = {} for cell in itervalues(worksheet._cells): cells_by_row.setdefault(cell.row, []).append(cell) with xf.element("sheetData"): for row_idx in sorted(cells_by_row): # row meta data row_dimension = worksheet.row_dimensions[row_idx] row_dimension.style = worksheet._styles.get(row_idx) attrs = { 'r': '%d' % row_idx, 'spans': '1:%d' % worksheet.max_column } attrs.update(dict(row_dimension)) with xf.element("row", attrs): row_cells = cells_by_row[row_idx] for cell in sorted(row_cells, key=row_sort): write_cell(xf, worksheet, cell, string_table)
def get_rows_to_write(worksheet): """Return all rows, and any cells that they contain""" # Ensure a blank cell exists if it has a style for styleCoord in iterkeys(worksheet._styles): if isinstance(styleCoord, str) and COORD_RE.search(styleCoord): worksheet.cell(styleCoord) # create rows of cells cells_by_row = {} for cell in itervalues(worksheet._cells): cells_by_row.setdefault(cell.row, []).append(cell) # make sure rows that only have a height set are returned for row_idx in worksheet.row_dimensions: if row_idx not in cells_by_row: cells_by_row[row_idx] = [] return cells_by_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 ":" 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 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 write_worksheet_data(doc, worksheet, string_table, style_table): """Write worksheet data to xml.""" start_tag(doc, 'sheetData') max_column = worksheet.get_highest_column() style_id_by_hash = style_table cells_by_row = {} for styleCoord in iterkeys(worksheet._styles): # Ensure a blank cell exists if it has a style if isinstance(styleCoord, str) and COORD_RE.search(styleCoord): worksheet.cell(styleCoord) for cell in worksheet.get_cell_collection(): cells_by_row.setdefault(cell.row, []).append(cell) for row_idx in sorted(cells_by_row): row_dimension = worksheet.row_dimensions[row_idx] attrs = {'r': '%d' % row_idx, 'spans': '1:%d' % max_column} if not row_dimension.visible: attrs['hidden'] = '1' if row_dimension.height > 0: attrs['ht'] = str(row_dimension.height) attrs['customHeight'] = '1' if row_idx in worksheet._styles: attrs['s'] = str(style_table[hash(worksheet.get_style(row_idx))]) attrs['customFormat'] = '1' start_tag(doc, 'row', attrs) row_cells = cells_by_row[row_idx] sorted_cells = sorted(row_cells, key=row_sort) for cell in sorted_cells: value = cell._value coordinate = cell.coordinate attributes = {'r': coordinate} if cell.data_type != cell.TYPE_FORMULA: attributes['t'] = cell.data_type if coordinate in worksheet._styles: attributes['s'] = '%d' % style_id_by_hash[ hash(worksheet._styles[coordinate])] if value in ('', None): tag(doc, 'c', attributes) else: start_tag(doc, 'c', attributes) if cell.data_type == cell.TYPE_STRING: tag(doc, 'v', body='%s' % string_table[value]) elif cell.data_type == cell.TYPE_FORMULA: if coordinate in worksheet.formula_attributes: attr = worksheet.formula_attributes[coordinate] if 't' in attr and attr['t'] == 'shared' and 'ref' not in attr: # Don't write body for shared formula tag(doc, 'f', attr=attr) else: tag(doc, 'f', attr=attr, body='%s' % value[1:]) else: tag(doc, 'f', body='%s' % value[1:]) tag(doc, 'v') elif cell.data_type == cell.TYPE_NUMERIC: if isinstance(value, (long, decimal.Decimal)): func = str else: func = repr tag(doc, 'v', body=func(value)) elif cell.data_type == cell.TYPE_BOOL: tag(doc, 'v', body='%d' % value) else: tag(doc, 'v', body='%s' % value) end_tag(doc, 'c') end_tag(doc, 'row') end_tag(doc, 'sheetData')
def write_worksheet_data(doc, worksheet, string_table, style_table): """Write worksheet data to xml.""" start_tag(doc, 'sheetData') max_column = worksheet.get_highest_column() style_id_by_hash = style_table cells_by_row = {} for styleCoord in iterkeys(worksheet._styles): # Ensure a blank cell exists if it has a style if isinstance(styleCoord, str) and COORD_RE.search(styleCoord): worksheet.cell(styleCoord) for cell in worksheet.get_cell_collection(): cells_by_row.setdefault(cell.row, []).append(cell) for row_idx in sorted(cells_by_row): row_dimension = worksheet.row_dimensions[row_idx] attrs = {'r': '%d' % row_idx, 'spans': '1:%d' % max_column} if not row_dimension.visible: attrs['hidden'] = '1' if row_dimension.height > 0: attrs['ht'] = str(row_dimension.height) attrs['customHeight'] = '1' if row_idx in worksheet._styles: attrs['s'] = str(style_table[hash(worksheet.get_style(row_idx))]) attrs['customFormat'] = '1' start_tag(doc, 'row', attrs) row_cells = cells_by_row[row_idx] sorted_cells = sorted(row_cells, key=row_sort) for cell in sorted_cells: value = cell._value coordinate = cell.coordinate attributes = {'r': coordinate} if cell.data_type != cell.TYPE_FORMULA: attributes['t'] = cell.data_type if coordinate in worksheet._styles: attributes['s'] = '%d' % style_id_by_hash[hash( worksheet._styles[coordinate])] if value in ('', None): tag(doc, 'c', attributes) else: start_tag(doc, 'c', attributes) if cell.data_type == cell.TYPE_STRING: tag(doc, 'v', body='%s' % string_table[value]) elif cell.data_type == cell.TYPE_FORMULA: if coordinate in worksheet.formula_attributes: attr = worksheet.formula_attributes[coordinate] if 't' in attr and attr[ 't'] == 'shared' and 'ref' not in attr: # Don't write body for shared formula tag(doc, 'f', attr=attr) else: tag(doc, 'f', attr=attr, body='%s' % value[1:]) else: tag(doc, 'f', body='%s' % value[1:]) tag(doc, 'v') elif cell.data_type == cell.TYPE_NUMERIC: if isinstance(value, (long, decimal.Decimal)): func = str else: func = repr tag(doc, 'v', body=func(value)) elif cell.data_type == cell.TYPE_BOOL: tag(doc, 'v', body='%d' % value) else: tag(doc, 'v', body='%s' % value) end_tag(doc, 'c') end_tag(doc, 'row') end_tag(doc, 'sheetData')