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')
