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_worksheet_data(doc, 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)
start_tag(doc, '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))
start_tag(doc, 'row', attrs)
row_cells = cells_by_row[row_idx]
for cell in sorted(row_cells, key=row_sort):
write_cell(doc, worksheet, cell, string_table)
end_tag(doc, 'row')
end_tag(doc, 'sheetData')
def write_worksheet_cols(doc, worksheet, style_table):
"""Write worksheet columns to xml."""
if worksheet.column_dimensions:
start_tag(doc, 'cols')
for column_string, columndimension in \
sorted(iteritems(worksheet.column_dimensions)):
col_index = column_index_from_string(column_string)
col_def = {'min': str(col_index), 'max': str(col_index)}
if columndimension.width != -1:
col_def['customWidth'] = '1'
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 column_string in worksheet._styles:
col_def['style'] = str(style_table[hash(
worksheet.get_style(column_string))])
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_cols(doc, worksheet, style_table):
"""Write worksheet columns to xml."""
if worksheet.column_dimensions:
start_tag(doc, 'cols')
for column_string, columndimension in \
sorted(iteritems(worksheet.column_dimensions)):
col_index = column_index_from_string(column_string)
col_def = {'min': str(col_index), 'max': str(col_index)}
if columndimension.width != -1:
col_def['customWidth'] = '1'
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 column_string in worksheet._styles:
col_def['style'] = str(style_table[hash(worksheet.get_style(column_string))])
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_cell(doc, worksheet, cell, string_table):
coordinate = cell.coordinate
attributes = {'r': coordinate}
cell_style = worksheet._styles.get(coordinate)
if cell_style is not None:
attributes['s'] = '%d' % cell_style
if cell.data_type != cell.TYPE_FORMULA:
attributes['t'] = cell.data_type
value = cell.internal_value
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.index(value))
elif cell.data_type == cell.TYPE_FORMULA:
shared_formula = worksheet.formula_attributes.get(coordinate)
if shared_formula is not None:
attr = shared_formula
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=value[1:])
else:
tag(doc, 'f', body=value[1:])
tag(doc, 'v')
elif cell.data_type in (cell.TYPE_NUMERIC, cell.TYPE_BOOL):
tag(doc, 'v', body=safe_string(value))
else:
tag(doc, 'v', body=value)
end_tag(doc, 'c')
def write_cell(doc, worksheet, cell):
string_table = worksheet.parent.shared_strings
coordinate = cell.coordinate
attributes = {'r': coordinate}
if cell.has_style:
attributes['s'] = '%d' % cell._style
if cell.data_type != cell.TYPE_FORMULA:
attributes['t'] = cell.data_type
value = cell.internal_value
if value in ('', None):
tag(doc, 'c', attributes)
else:
start_tag(doc, 'c', attributes)
if cell.data_type == cell.TYPE_STRING:
idx = string_table.add(value)
tag(doc, 'v', body='%s' % idx)
elif cell.data_type == cell.TYPE_FORMULA:
shared_formula = worksheet.formula_attributes.get(coordinate)
if shared_formula is not None:
attr = shared_formula
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=value[1:])
else:
tag(doc, 'f', body=value[1:])
tag(doc, 'v')
elif cell.data_type in (cell.TYPE_NUMERIC, cell.TYPE_BOOL):
tag(doc, 'v', body=safe_string(value))
else:
tag(doc, 'v', body=value)
end_tag(doc, 'c')
def write_worksheet_cols(doc, worksheet, style_table=None):
"""Write worksheet columns to xml.
style_table is ignored but required
for compatibility with the dumped worksheet <cols> may never be empty -
spec says must contain at least one child
"""
cols = []
for label, dimension in iteritems(worksheet.column_dimensions):
col_def = dict(dimension)
style = worksheet._styles.get(label)
if col_def == {} and style is None:
continue
elif style is not None:
col_def['style'] = '%d' % style
idx = column_index_from_string(label)
cols.append((idx, col_def))
if cols == []:
return
start_tag(doc, 'cols')
for idx, col_def in sorted(cols):
v = "%d" % idx
col_def.update({'min':v, 'max':v})
tag(doc, 'col', col_def)
end_tag(doc, 'cols')
def write_worksheet_cols(doc, worksheet, style_table=None):
"""Write worksheet columns to xml.
style_table is ignored but required
for compatibility with the dumped worksheet <cols> may never be empty -
spec says must contain at least one child
"""
cols = []
for label, dimension in iteritems(worksheet.column_dimensions):
dimension.style = worksheet._styles.get(label)
col_def = dict(dimension)
if col_def == {}:
continue
idx = column_index_from_string(label)
cols.append((idx, col_def))
if cols == []:
return
start_tag(doc, 'cols')
for idx, col_def in sorted(cols):
v = "%d" % idx
cmin = col_def.get('min') or v
cmax = col_def.get('max') or v
col_def.update({'min': cmin, 'max': cmax})
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 write_header_footer(doc, worksheet):
if worksheet.header_footer.hasHeader() or worksheet.header_footer.hasFooter():
start_tag(doc, 'headerFooter')
if worksheet.header_footer.hasHeader():
tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader())
if worksheet.header_footer.hasFooter():
tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter())
end_tag(doc, 'headerFooter')
def write_worksheet_mergecells(doc, worksheet):
"""Write merged cells to xml."""
if len(worksheet._merged_cells) > 0:
start_tag(doc, 'mergeCells', {'count': str(len(worksheet._merged_cells))})
for range_string in worksheet._merged_cells:
attrs = {'ref': range_string}
tag(doc, 'mergeCell', attrs)
end_tag(doc, 'mergeCells')
def write_worksheet_mergecells(doc, worksheet):
"""Write merged cells to xml."""
if len(worksheet._merged_cells) > 0:
start_tag(doc, 'mergeCells', {'count': str(len(worksheet._merged_cells))})
for range_string in worksheet._merged_cells:
attrs = {'ref': range_string}
tag(doc, 'mergeCell', attrs)
end_tag(doc, 'mergeCells')
def write_pagebreaks(doc, worksheet):
breaks = worksheet.page_breaks
if breaks:
start_tag(doc, 'rowBreaks', {'count': str(len(breaks)),
'manualBreakCount': str(len(breaks))})
for b in breaks:
tag(doc, 'brk', {'id': str(b), 'man': 'true', 'max': '16383',
'min': '0'})
end_tag(doc, 'rowBreaks')
def standard():
archive = ZipFile("std.zip", "w", ZIP_DEFLATED)
out = BytesIO()
doc = XMLGenerator(out)
for i in range(1000000):
start_tag(doc, "test")
end_tag(doc, "test")
doc.endDocument()
archive.writestr("sheet.xml", out.getvalue(), ZIP_DEFLATED)
archive.close()
def standard():
archive = ZipFile("std.zip", "w", ZIP_DEFLATED)
out = BytesIO()
doc = XMLGenerator(out)
for i in range(1000000):
start_tag(doc, "test")
end_tag(doc, "test")
doc.endDocument()
archive.writestr("sheet.xml", out.getvalue(), ZIP_DEFLATED)
archive.close()
def buffered():
archive = EnhZipFile("buffered.zip", "w", ZIP_DEFLATED)
out = archive.start_entry(ZipInfo("sheet.xml"))
#out.write = partial(out.write, compress_type=ZIP_DEFLATED)
doc = XMLGenerator(out)
for i in range(1000000):
start_tag(doc, "test")
end_tag(doc, "test")
doc.endDocument()
out.close()
archive.close()
def buffered():
archive = EnhZipFile("buffered.zip", "w", ZIP_DEFLATED)
out = archive.start_entry(ZipInfo("sheet.xml"))
#out.write = partial(out.write, compress_type=ZIP_DEFLATED)
doc = XMLGenerator(out)
for i in range(1000000):
start_tag(doc, "test")
end_tag(doc, "test")
doc.endDocument()
out.close()
archive.close()
def write_worksheet_autofilter(doc, worksheet):
auto_filter = worksheet.auto_filter
if auto_filter.filter_columns or auto_filter.sort_conditions:
start_tag(doc, 'autoFilter', {'ref': auto_filter.ref})
for col_id, filter_column in sorted(
auto_filter.filter_columns.items()):
start_tag(doc, 'filterColumn', {'colId': str(col_id)})
if filter_column.blank:
start_tag(doc, 'filters', {'blank': '1'})
else:
start_tag(doc, 'filters')
for val in filter_column.vals:
tag(doc, 'filter', {'val': val})
end_tag(doc, 'filters')
end_tag(doc, 'filterColumn')
if auto_filter.sort_conditions:
start_tag(doc, 'sortState', {'ref': auto_filter.ref})
for sort_condition in auto_filter.sort_conditions:
sort_attr = {'ref': sort_condition.ref}
if sort_condition.descending:
sort_attr['descending'] = '1'
tag(doc, 'sortCondtion', sort_attr)
end_tag(doc, 'sortState')
end_tag(doc, 'autoFilter')
elif auto_filter.ref:
tag(doc, 'autoFilter', {'ref': auto_filter.ref})
def write_worksheet_autofilter(doc, worksheet):
auto_filter = worksheet.auto_filter
if auto_filter.filter_columns or auto_filter.sort_conditions:
start_tag(doc, 'autoFilter', {'ref': auto_filter.ref})
for col_id, filter_column in sorted(auto_filter.filter_columns.items()):
start_tag(doc, 'filterColumn', {'colId': str(col_id)})
if filter_column.blank:
start_tag(doc, 'filters', {'blank': '1'})
else:
start_tag(doc, 'filters')
for val in filter_column.vals:
tag(doc, 'filter', {'val': val})
end_tag(doc, 'filters')
end_tag(doc, 'filterColumn')
if auto_filter.sort_conditions:
start_tag(doc, 'sortState', {'ref': auto_filter.ref})
for sort_condition in auto_filter.sort_conditions:
sort_attr = {'ref': sort_condition.ref}
if sort_condition.descending:
sort_attr['descending'] = '1'
tag(doc, 'sortCondtion', sort_attr)
end_tag(doc, 'sortState')
end_tag(doc, 'autoFilter')
elif auto_filter.ref:
tag(doc, 'autoFilter', {'ref': auto_filter.ref})
def write_header(self):
fobj = self.get_temporary_file(filename=self._fileobj_header_name)
doc = XMLGenerator(fobj)
start_tag(doc, 'worksheet',
{
'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
start_tag(doc, 'sheetPr')
tag(doc, 'outlinePr',
{'summaryBelow': '1',
'summaryRight': '1'})
end_tag(doc, 'sheetPr')
tag(doc, 'dimension', {'ref': 'A1:%s' % (self.get_dimensions())})
start_tag(doc, 'sheetViews')
start_tag(doc, 'sheetView', {'workbookViewId': '0'})
tag(doc, 'selection', {'activeCell': 'A1',
'sqref': 'A1'})
end_tag(doc, 'sheetView')
end_tag(doc, 'sheetViews')
tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
# START csv2xlsx
start_tag(doc, 'cols')
for (col_lo, col_hi), width in self.col_widths.items():
tag(doc, 'col', dict(min=str(col_lo), max=str(col_hi), width=width, customWidth="1"))
end_tag(doc, 'cols')
# END csv2xlsx
start_tag(doc, 'sheetData')
def append(self, row):
"""
:param row: iterable containing values to append
:type row: iterable
"""
doc = self._get_content_generator()
self._max_row += 1
span = len(row)
self._max_col = max(self._max_col, span)
row_idx = self._max_row
attrs = {'r': '%d' % row_idx,
'spans': '1:%d' % span}
start_tag(doc, 'row', attrs)
for col_idx, cell in enumerate(row):
if cell is None:
continue
coordinate = '%s%d' % (get_column_letter(col_idx + 1), row_idx)
attributes = {'r': coordinate}
if isinstance(cell, bool):
dtype = 'boolean'
elif isinstance(cell, NUMERIC_TYPES):
dtype = 'numeric'
elif isinstance(cell, (datetime.datetime, datetime.date)):
dtype = 'datetime'
cell = to_excel(cell)
attributes['s'] = STYLES[dtype]['style']
elif cell and cell[0] == '=':
dtype = 'formula'
else:
dtype = 'string'
cell = self._string_builder.add(cell)
if dtype != 'formula':
attributes['t'] = STYLES[dtype]['type']
start_tag(doc, 'c', attributes)
if dtype == 'formula':
tag(doc, 'f', body='%s' % cell[1:])
tag(doc, 'v')
elif dtype == 'boolean':
tag(doc, 'v', body='%d' % cell)
else:
tag(doc, 'v', body='%s' % cell)
end_tag(doc, 'c')
end_tag(doc, 'row')
def write_worksheet_hyperlinks(doc, worksheet):
"""Write worksheet hyperlinks to xml."""
write_hyperlinks = False
for cell in worksheet.get_cell_collection():
if cell.hyperlink_rel_id is not None:
write_hyperlinks = True
break
if write_hyperlinks:
start_tag(doc, 'hyperlinks')
for cell in worksheet.get_cell_collection():
if cell.hyperlink_rel_id is not None:
attrs = {'display': cell.hyperlink,
'ref': cell.coordinate,
'r:id': cell.hyperlink_rel_id}
tag(doc, 'hyperlink', attrs)
end_tag(doc, 'hyperlinks')
def write_worksheet_hyperlinks(doc, worksheet):
"""Write worksheet hyperlinks to xml."""
write_hyperlinks = False
for cell in worksheet.get_cell_collection():
if cell.hyperlink_rel_id is not None:
write_hyperlinks = True
break
if write_hyperlinks:
start_tag(doc, 'hyperlinks')
for cell in worksheet.get_cell_collection():
if cell.hyperlink_rel_id is not None:
attrs = {'display': cell.hyperlink,
'ref': cell.coordinate,
'r:id': cell.hyperlink_rel_id}
tag(doc, 'hyperlink', attrs)
end_tag(doc, 'hyperlinks')
def write_worksheet_hyperlinks(doc, worksheet):
"""Write worksheet hyperlinks to xml."""
write_hyperlinks = False
for cell in worksheet.get_cell_collection():
if cell.hyperlink_rel_id is not None:
write_hyperlinks = True
break
if write_hyperlinks:
start_tag(doc, 'hyperlinks', {'xmlns:r':"http://schemas.openxmlformats.org/officeDocument/2006/relationships"})
for cell in worksheet.get_cell_collection():
if cell.hyperlink_rel_id is not None:
attrs = {'display': cell.hyperlink,
'ref': cell.coordinate,
'r:id': cell.hyperlink_rel_id}
tag(doc, 'hyperlink', attrs)
end_tag(doc, 'hyperlinks')
def append(self, row):
"""
:param row: iterable containing values to append
:type row: iterable
"""
doc = self._get_content_generator()
self._max_row += 1
span = len(row)
self._max_col = max(self._max_col, span)
row_idx = self._max_row
attrs = {"r": "%d" % row_idx, "spans": "1:%d" % span}
start_tag(doc, "row", attrs)
for col_idx, cell in enumerate(row):
if cell is None:
continue
coordinate = "%s%d" % (get_column_letter(col_idx + 1), row_idx)
attributes = {"r": coordinate}
if isinstance(cell, bool):
dtype = "boolean"
elif isinstance(cell, NUMERIC_TYPES):
dtype = "numeric"
elif isinstance(cell, (datetime.datetime, datetime.date)):
dtype = "datetime"
cell = to_excel(cell)
attributes["s"] = STYLES[dtype]["style"]
elif cell and cell[0] == "=":
dtype = "formula"
else:
dtype = "string"
cell = self._string_builder.add(cell)
if dtype != "formula":
attributes["t"] = STYLES[dtype]["type"]
start_tag(doc, "c", attributes)
if dtype == "formula":
tag(doc, "f", body="%s" % cell[1:])
tag(doc, "v")
elif dtype == "boolean":
tag(doc, "v", body="%d" % cell)
else:
tag(doc, "v", body="%s" % cell)
end_tag(doc, "c")
end_tag(doc, "row")
def write_worksheet_datavalidations(doc, worksheet):
""" Write data validation(s) to xml."""
# Filter out "empty" data-validation objects (i.e. with 0 cells)
required_dvs = [x for x in worksheet._data_validations
if len(x.cells) or len(x.ranges)]
count = len(required_dvs)
if count == 0:
return
start_tag(doc, 'dataValidations', {'count': str(count)})
for data_validation in required_dvs:
start_tag(doc, 'dataValidation', data_validation.generate_attributes_map())
if data_validation.formula1:
tag(doc, 'formula1', body=data_validation.formula1)
if data_validation.formula2:
tag(doc, 'formula2', body=data_validation.formula2)
end_tag(doc, 'dataValidation')
end_tag(doc, 'dataValidations')
def write_worksheet_datavalidations(doc, worksheet):
""" Write data validation(s) to xml."""
# Filter out "empty" data-validation objects (i.e. with 0 cells)
required_dvs = [x for x in worksheet._data_validations
if len(x.cells) or len(x.ranges)]
count = len(required_dvs)
if count == 0:
return
start_tag(doc, 'dataValidations', {'count': str(count)})
for data_validation in required_dvs:
start_tag(doc, 'dataValidation', data_validation.generate_attributes_map())
if data_validation.formula1:
tag(doc, 'formula1', body=data_validation.formula1)
if data_validation.formula2:
tag(doc, 'formula2', body=data_validation.formula2)
end_tag(doc, 'dataValidation')
end_tag(doc, 'dataValidations')
def write_string_table(string_table):
"""Write the string table xml."""
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
start_tag(doc, 'sst', {'xmlns':
'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'uniqueCount': '%d' % len(string_table)})
for key in string_table:
start_tag(doc, 'si')
if key.strip() != key:
attr = {'xml:space': 'preserve'}
else:
attr = {}
tag(doc, 't', attr, key)
end_tag(doc, 'si')
end_tag(doc, 'sst')
string_table_xml = temp_buffer.getvalue()
temp_buffer.close()
return string_table_xml
def write_string_table(string_table):
"""Write the string table xml."""
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
start_tag(doc, 'sst', {'xmlns':
'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'uniqueCount': '%d' % len(string_table)})
for key in string_table:
start_tag(doc, 'si')
if key.strip() != key:
attr = {'xml:space': 'preserve'}
else:
attr = {}
tag(doc, 't', attr, key)
end_tag(doc, 'si')
end_tag(doc, 'sst')
string_table_xml = temp_buffer.getvalue()
temp_buffer.close()
return string_table_xml
def append(self, row):
"""
:param row: iterable containing values to append
:type row: iterable
"""
if (not isinstance(row, (list, tuple, range))
and not isgenerator(row)):
self._invalid_row(row)
doc = self._get_content_generator()
cell = WriteOnlyCell(self) # singleton
self._max_row += 1
span = len(row)
self._max_col = max(self._max_col, span)
row_idx = self._max_row
attrs = {'r': '%d' % row_idx,
'spans': '1:%d' % span}
start_tag(doc, 'row', attrs)
for col_idx, value in enumerate(row, 1):
if value is None:
continue
dirty_cell = False
column = get_column_letter(col_idx)
if isinstance(value, Cell):
cell = value
dirty_cell = True # cell may have other properties than a value
else:
cell.value = value
cell.coordinate = '%s%d' % (column, row_idx)
if cell.comment is not None:
comment = cell.comment
comment._parent = CommentParentCell(cell)
self._comments.append(comment)
write_cell(doc, self, cell)
if dirty_cell:
cell = WriteOnlyCell(self)
end_tag(doc, 'row')
def append(self, row):
"""
:param row: iterable containing values to append
:type row: iterable
"""
if (not isinstance(row, (list, tuple, range))
and not isgenerator(row)):
self._invalid_row(row)
doc = self._get_content_generator()
cell = WriteOnlyCell(self) # singleton
self._max_row += 1
span = len(row)
self._max_col = max(self._max_col, span)
row_idx = self._max_row
attrs = {'r': '%d' % row_idx, 'spans': '1:%d' % span}
start_tag(doc, 'row', attrs)
for col_idx, value in enumerate(row, 1):
if value is None:
continue
dirty_cell = False
column = get_column_letter(col_idx)
if isinstance(value, Cell):
cell = value
dirty_cell = True # cell may have other properties than a value
else:
cell.value = value
cell.coordinate = '%s%d' % (column, row_idx)
if cell.comment is not None:
comment = cell.comment
comment._parent = CommentParentCell(cell)
self._comments.append(comment)
write_cell(doc, self, cell)
if dirty_cell:
cell = WriteOnlyCell(self)
end_tag(doc, 'row')
def write_worksheet_conditional_formatting(doc, worksheet):
"""Write conditional formatting to xml."""
for range_string, rules in iteritems(
worksheet.conditional_formatting.cf_rules):
if not len(rules):
# Skip if there are no rules. This is possible if a dataBar rule was read in and ignored.
continue
start_tag(doc, 'conditionalFormatting', {'sqref': range_string})
for rule in rules:
if rule['type'] == 'dataBar':
# Ignore - uses extLst tag which is currently unsupported.
continue
attr = {'type': rule['type']}
for rule_attr in ConditionalFormatting.rule_attributes:
if rule_attr in rule:
attr[rule_attr] = str(rule[rule_attr])
start_tag(doc, 'cfRule', attr)
if 'formula' in rule:
for f in rule['formula']:
tag(doc, 'formula', None, f)
if 'colorScale' in rule:
start_tag(doc, 'colorScale')
for cfvo in rule['colorScale']['cfvo']:
tag(doc, 'cfvo', cfvo)
for color in rule['colorScale']['color']:
if str(color.index).split(
':'
)[0] == 'theme': # strip prefix theme if marked as such
if str(color.index).split(':')[2]:
tag(
doc, 'color', {
'theme': str(color.index).split(':')[1],
'tint': str(color.index).split(':')[2]
})
else:
tag(doc, 'color',
{'theme': str(color.index).split(':')[1]})
else:
tag(doc, 'color', {'rgb': str(color.index)})
end_tag(doc, 'colorScale')
if 'iconSet' in rule:
iconAttr = {}
for icon_attr in ConditionalFormatting.icon_attributes:
if icon_attr in rule['iconSet']:
iconAttr[icon_attr] = rule['iconSet'][icon_attr]
start_tag(doc, 'iconSet', iconAttr)
for cfvo in rule['iconSet']['cfvo']:
tag(doc, 'cfvo', cfvo)
end_tag(doc, 'iconSet')
end_tag(doc, 'cfRule')
end_tag(doc, 'conditionalFormatting')
def write_worksheet_data(doc, worksheet):
"""Write worksheet data to xml."""
cells_by_row = get_rows_to_write(worksheet)
start_tag(doc, '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))
start_tag(doc, 'row', attrs)
row_cells = cells_by_row[row_idx]
for cell in sorted(row_cells, key=row_sort):
write_cell(doc, worksheet, cell)
end_tag(doc, 'row')
end_tag(doc, 'sheetData')
def write_string_table(string_table):
"""Write the string table xml."""
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
start_tag(doc, 'sst', {'xmlns':
'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'uniqueCount': '%d' % len(string_table)})
strings_to_write = sorted(string_table.items(),
key=lambda pair: pair[1])
for key in [pair[0] for pair in strings_to_write]:
start_tag(doc, 'si')
if key.strip() != key:
attr = {'xml:space': 'preserve'}
else:
attr = {}
tag(doc, 't', attr, key)
end_tag(doc, 'si')
end_tag(doc, 'sst')
string_table_xml = temp_buffer.getvalue()
temp_buffer.close()
return string_table_xml
def write_header(self):
fobj = self.get_temporary_file(filename=self._fileobj_header_name)
doc = XMLGenerator(fobj)
start_tag(doc, 'worksheet',
{
'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
start_tag(doc, 'sheetPr')
tag(doc, 'outlinePr',
{'summaryBelow': '1',
'summaryRight': '1'})
end_tag(doc, 'sheetPr')
tag(doc, 'dimension', {'ref': 'A1:%s' % (self.get_dimensions())})
start_tag(doc, 'sheetViews')
start_tag(doc, 'sheetView', {'workbookViewId': '0'})
tag(doc, 'selection', {'activeCell': 'A1',
'sqref': 'A1'})
end_tag(doc, 'sheetView')
end_tag(doc, 'sheetViews')
fmt = write_format(self)
fobj.write(tostring(fmt))
cols = write_cols(self)
if cols is not None:
fobj.write(tostring(cols))
return doc
def write_header(self):
fobj = self.get_temporary_file(filename=self._fileobj_header_name)
doc = XMLGenerator(fobj)
start_tag(
doc, 'worksheet', {
'xmlns':
'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'xmlns:r':
'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
})
start_tag(doc, 'sheetPr')
tag(doc, 'outlinePr', {'summaryBelow': '1', 'summaryRight': '1'})
end_tag(doc, 'sheetPr')
tag(doc, 'dimension', {'ref': 'A1:%s' % (self.get_dimensions())})
start_tag(doc, 'sheetViews')
start_tag(doc, 'sheetView', {'workbookViewId': '0'})
tag(doc, 'selection', {'activeCell': 'A1', 'sqref': 'A1'})
end_tag(doc, 'sheetView')
end_tag(doc, 'sheetViews')
fmt = write_format(self)
fobj.write(tostring(fmt))
cols = write_cols(self)
if cols is not None:
fobj.write(tostring(cols))
return doc
def write_string_table(string_table):
"""Write the string table xml."""
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
start_tag(
doc, 'sst', {
'xmlns':
'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'uniqueCount': '%d' % len(string_table)
})
strings_to_write = sorted(string_table.items(), key=lambda pair: pair[1])
for key in [pair[0] for pair in strings_to_write]:
start_tag(doc, 'si')
if key.strip() != key:
attr = {'xml:space': 'preserve'}
else:
attr = {}
tag(doc, 't', attr, key)
end_tag(doc, 'si')
end_tag(doc, 'sst')
string_table_xml = temp_buffer.getvalue()
temp_buffer.close()
return string_table_xml
def write_worksheet_conditional_formatting(doc, worksheet):
"""Write conditional formatting to xml."""
for range_string, rules in iteritems(worksheet.conditional_formatting.cf_rules):
if not len(rules):
# Skip if there are no rules. This is possible if a dataBar rule was read in and ignored.
continue
start_tag(doc, 'conditionalFormatting', {'sqref': range_string})
for rule in rules:
if rule['type'] == 'dataBar':
# Ignore - uses extLst tag which is currently unsupported.
continue
attr = {'type': rule['type']}
for rule_attr in ConditionalFormatting.rule_attributes:
if rule_attr in rule:
attr[rule_attr] = str(rule[rule_attr])
start_tag(doc, 'cfRule', attr)
if 'formula' in rule:
for f in rule['formula']:
tag(doc, 'formula', None, f)
if 'colorScale' in rule:
start_tag(doc, 'colorScale')
for cfvo in rule['colorScale']['cfvo']:
tag(doc, 'cfvo', cfvo)
for color in rule['colorScale']['color']:
if str(color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such
if str(color.index).split(':')[2]:
tag(doc, 'color', {'theme': str(color.index).split(':')[1],
'tint': str(color.index).split(':')[2]})
else:
tag(doc, 'color', {'theme': str(color.index).split(':')[1]})
else:
tag(doc, 'color', {'rgb': str(color.index)})
end_tag(doc, 'colorScale')
if 'iconSet' in rule:
iconAttr = {}
for icon_attr in ConditionalFormatting.icon_attributes:
if icon_attr in rule['iconSet']:
iconAttr[icon_attr] = rule['iconSet'][icon_attr]
start_tag(doc, 'iconSet', iconAttr)
for cfvo in rule['iconSet']['cfvo']:
tag(doc, 'cfvo', cfvo)
end_tag(doc, 'iconSet')
end_tag(doc, 'cfRule')
end_tag(doc, 'conditionalFormatting')
def write_header(self):
fobj = self.get_temporary_file(filename=self._fileobj_header_name)
doc = XMLGenerator(fobj, 'utf-8')
start_tag(doc, 'worksheet',
{
'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
start_tag(doc, 'sheetPr')
tag(doc, 'outlinePr',
{'summaryBelow': '1',
'summaryRight': '1'})
end_tag(doc, 'sheetPr')
tag(doc, 'dimension', {'ref': 'A1:%s' % (self.get_dimensions())})
start_tag(doc, 'sheetViews')
start_tag(doc, 'sheetView', {'workbookViewId': '0'})
tag(doc, 'selection', {'activeCell': 'A1',
'sqref': 'A1'})
end_tag(doc, 'sheetView')
end_tag(doc, 'sheetViews')
tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
start_tag(doc, 'sheetData')
def write_header(self):
fobj = self.get_temporary_file(filename=self._fileobj_header_name)
doc = XMLGenerator(fobj, "utf-8")
start_tag(
doc,
"worksheet",
{
"xmlns": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
"xmlns:r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
},
)
start_tag(doc, "sheetPr")
tag(doc, "outlinePr", {"summaryBelow": "1", "summaryRight": "1"})
end_tag(doc, "sheetPr")
tag(doc, "dimension", {"ref": "A1:%s" % (self.get_dimensions())})
start_tag(doc, "sheetViews")
start_tag(doc, "sheetView", {"workbookViewId": "0"})
tag(doc, "selection", {"activeCell": "A1", "sqref": "A1"})
end_tag(doc, "sheetView")
end_tag(doc, "sheetViews")
tag(doc, "sheetFormatPr", {"defaultRowHeight": "15"})
start_tag(doc, "sheetData")
def write_etree(doc, element):
start_tag(doc, element.tag, element)
for e in element.getchildren():
write_etree(doc, e)
end_tag(doc, element.tag)
def write_worksheet(worksheet, shared_strings):
"""Write a worksheet to an xml file."""
xml_file = BytesIO()
doc = XMLGenerator(out=xml_file)
start_tag(doc, 'worksheet',
{'xmlns': SHEET_MAIN_NS,
'xmlns:r': REL_NS})
props = write_properties(worksheet, worksheet.vba_code)
xml_file.write(tostring(props))
dim = Element('dimension', {'ref': '%s' % worksheet.calculate_dimension()})
xml_file.write(tostring(dim))
views = write_sheetviews(worksheet)
xml_file.write(tostring(views))
fmt = write_format(worksheet)
xml_file.write(tostring(fmt))
cols = write_cols(worksheet)
if cols is not None:
xml_file.write(tostring(cols))
write_worksheet_data(doc, worksheet)
if worksheet.protection.sheet:
prot = Element('sheetProtection', dict(worksheet.protection))
xml_file.write(tostring(prot))
af = write_autofilter(worksheet)
if af is not None:
xml_file.write(tostring(af))
merge = write_mergecells(worksheet)
if merge is not None:
xml_file.write(tostring(merge))
cfs = write_conditional_formatting(worksheet)
for cf in cfs:
xml_file.write(tostring(cf))
dvs = write_datavalidation(worksheet)
if dvs:
xml_file.write(tostring(dvs))
hyper = write_hyperlinks(worksheet)
if hyper is not None:
xml_file.write(tostring(hyper))
options = worksheet.page_setup.options
if options:
tag(doc, 'printOptions', options)
tag(doc, 'pageMargins', dict(worksheet.page_margins))
setup = worksheet.page_setup.setup
if setup:
tag(doc, 'pageSetup', setup)
hf = write_header_footer(worksheet)
if hf is not None:
xml_file.write(tostring(hf))
if worksheet._charts or worksheet._images:
tag(doc, 'drawing', {'r:id': 'rId1'})
if worksheet.vba_controls is not None:
xml = Element("{%s}legacyDrawing" % SHEET_MAIN_NS,
{"{%s}id" % REL_NS : worksheet.vba_controls})
xml_file.write(tostring(xml))
breaks = write_pagebreaks(worksheet)
if breaks is not None:
xml_file.write(tostring(breaks))
# add a legacyDrawing so that excel can draw comments
if worksheet._comment_count > 0:
tag(doc, 'legacyDrawing', {'r:id': 'commentsvml'})
end_tag(doc, 'worksheet')
doc.endDocument()
xml_string = xml_file.getvalue()
xml_file.close()
return xml_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 _close_content(self):
doc = self._get_content_generator()
end_tag(doc, 'sheetData')
if self._comments:
tag(doc, 'legacyDrawing', {'r:id': 'commentsvml'})
end_tag(doc, 'worksheet')
def write_worksheet(worksheet, shared_strings):
"""Write a worksheet to an xml file."""
if worksheet.xml_source:
vba_root = fromstring(worksheet.xml_source)
else:
vba_root = None
xml_file = BytesIO()
doc = XMLGenerator(out=xml_file)
start_tag(doc, 'worksheet',
{'xmlns': SHEET_MAIN_NS,
'xmlns:r': REL_NS})
vba_attrs = {}
if vba_root is not None:
el = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS)
if el is not None:
vba_attrs['codeName'] = el.get('codeName', worksheet.title)
start_tag(doc, 'sheetPr', vba_attrs)
tag(doc, 'outlinePr',
{'summaryBelow': '%d' % (worksheet.show_summary_below),
'summaryRight': '%d' % (worksheet.show_summary_right)})
if worksheet.page_setup.fitToPage:
tag(doc, 'pageSetUpPr', {'fitToPage': '1'})
end_tag(doc, 'sheetPr')
tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
write_worksheet_sheetviews(doc, worksheet)
write_worksheet_format(doc, worksheet)
write_worksheet_cols(doc, worksheet)
write_worksheet_data(doc, worksheet, shared_strings)
if worksheet.protection.sheet:
tag(doc, 'sheetProtection', dict(worksheet.protection))
write_worksheet_autofilter(doc, worksheet)
write_worksheet_mergecells(doc, worksheet)
write_worksheet_conditional_formatting(doc, worksheet)
write_worksheet_datavalidations(doc, worksheet)
write_worksheet_hyperlinks(doc, worksheet)
options = worksheet.page_setup.options
if options:
tag(doc, 'printOptions', options)
tag(doc, 'pageMargins', dict(worksheet.page_margins))
setup = worksheet.page_setup.setup
if setup:
tag(doc, 'pageSetup', setup)
write_header_footer(doc, worksheet)
if worksheet._charts or worksheet._images:
tag(doc, 'drawing', {'r:id': 'rId1'})
# If vba is being preserved then add a legacyDrawing element so
# that any controls can be drawn.
if vba_root is not None:
el = vba_root.find('{%s}legacyDrawing' % SHEET_MAIN_NS)
if el is not None:
rId = el.get('{%s}id' % REL_NS)
tag(doc, 'legacyDrawing', {'r:id': rId})
write_pagebreaks(doc, worksheet)
# add a legacyDrawing so that excel can draw comments
if worksheet._comment_count > 0:
tag(doc, 'legacyDrawing', {'r:id': 'commentsvml'})
end_tag(doc, 'worksheet')
doc.endDocument()
xml_string = xml_file.getvalue()
xml_file.close()
return xml_string
def write_worksheet(worksheet, string_table, style_table):
"""Write a worksheet to an xml file."""
if worksheet.xml_source:
vba_root = fromstring(worksheet.xml_source)
else:
vba_root = None
xml_file = StringIO()
doc = XMLGenerator(out=xml_file, encoding='utf-8')
start_tag(doc, 'worksheet',
{'xmlns': SHEET_MAIN_NS,
'xmlns:r': REL_NS})
if vba_root is not None:
el = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS)
if el is not None:
codename =el.get('codeName', worksheet.title)
start_tag(doc, 'sheetPr', {"codeName": codename})
else:
start_tag(doc, 'sheetPr')
else:
start_tag(doc, 'sheetPr')
tag(doc, 'outlinePr',
{'summaryBelow': '%d' % (worksheet.show_summary_below),
'summaryRight': '%d' % (worksheet.show_summary_right)})
if worksheet.page_setup.fitToPage:
tag(doc, 'pageSetUpPr', {'fitToPage':'1'})
end_tag(doc, 'sheetPr')
tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
write_worksheet_sheetviews(doc, worksheet)
tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
write_worksheet_cols(doc, worksheet, style_table)
write_worksheet_data(doc, worksheet, string_table, style_table)
if worksheet.protection.enabled:
tag(doc, 'sheetProtection',
{'objects': '1', 'scenarios': '1', 'sheet': '1'})
write_worksheet_autofilter(doc, worksheet)
write_worksheet_mergecells(doc, worksheet)
write_worksheet_datavalidations(doc, worksheet)
write_worksheet_hyperlinks(doc, worksheet)
write_worksheet_conditional_formatting(doc, worksheet)
options = worksheet.page_setup.options
if options:
tag(doc, 'printOptions', options)
margins = worksheet.page_margins.margins
if margins:
tag(doc, 'pageMargins', margins)
setup = worksheet.page_setup.setup
if setup:
tag(doc, 'pageSetup', setup)
if worksheet.header_footer.hasHeader() or worksheet.header_footer.hasFooter():
start_tag(doc, 'headerFooter')
if worksheet.header_footer.hasHeader():
tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader())
if worksheet.header_footer.hasFooter():
tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter())
end_tag(doc, 'headerFooter')
if worksheet._charts or worksheet._images:
tag(doc, 'drawing', {'r:id':'rId1'})
# If vba is being preserved then add a legacyDrawing element so
# that any controls can be drawn.
if vba_root is not None:
el = vba_root.find('{%s}legacyDrawing' % SHEET_MAIN_NS)
if el is not None:
rId = el.get('{%s}id' % REL_NS)
tag(doc, 'legacyDrawing', {'r:id': rId})
breaks = worksheet.page_breaks
if breaks:
start_tag(doc, 'rowBreaks', {'count': str(len(breaks)), 'manualBreakCount': str(len(breaks))})
for b in breaks:
tag(doc, 'brk', {'id': str(b), 'man': 'true', 'max': '16383', 'min': '0'})
end_tag(doc, 'rowBreaks')
# add a legacyDrawing so that excel can draw comments
if worksheet._comment_count > 0:
tag(doc, 'legacyDrawing', {'r:id':'commentsvml'})
end_tag(doc, 'worksheet')
doc.endDocument()
xml_string = xml_file.getvalue()
xml_file.close()
return xml_string
def test_end_tag(self, doc, root):
end_tag(root, "end")
assert doc.getvalue() == b"</end>"
def _close_content(self):
doc = self._get_content_generator()
end_tag(doc, "sheetData")
end_tag(doc, "worksheet")
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_etree(doc, element):
start_tag(doc, element.tag, element)
for e in element.getchildren():
write_etree(doc, e)
end_tag(doc, element.tag)
def write_worksheet(worksheet, string_table, style_table):
"""Write a worksheet to an xml file."""
if worksheet.xml_source:
vba_root = fromstring(worksheet.xml_source)
else:
vba_root = None
xml_file = StringIO()
doc = XMLGenerator(out=xml_file, encoding='utf-8')
start_tag(doc, 'worksheet', {'xmlns': SHEET_MAIN_NS, 'xmlns:r': REL_NS})
if vba_root is not None:
el = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS)
if el is not None:
codename = el.get('codeName', worksheet.title)
start_tag(doc, 'sheetPr', {"codeName": codename})
else:
start_tag(doc, 'sheetPr')
else:
start_tag(doc, 'sheetPr')
tag(
doc, 'outlinePr', {
'summaryBelow': '%d' % (worksheet.show_summary_below),
'summaryRight': '%d' % (worksheet.show_summary_right)
})
if worksheet.page_setup.fitToPage:
tag(doc, 'pageSetUpPr', {'fitToPage': '1'})
end_tag(doc, 'sheetPr')
tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
write_worksheet_sheetviews(doc, worksheet)
tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
write_worksheet_cols(doc, worksheet, style_table)
write_worksheet_data(doc, worksheet, string_table, style_table)
if worksheet.protection.enabled:
tag(doc, 'sheetProtection', {
'objects': '1',
'scenarios': '1',
'sheet': '1'
})
write_worksheet_autofilter(doc, worksheet)
write_worksheet_mergecells(doc, worksheet)
write_worksheet_datavalidations(doc, worksheet)
write_worksheet_hyperlinks(doc, worksheet)
write_worksheet_conditional_formatting(doc, worksheet)
options = worksheet.page_setup.options
if options:
tag(doc, 'printOptions', options)
margins = worksheet.page_margins.margins
if margins:
tag(doc, 'pageMargins', margins)
setup = worksheet.page_setup.setup
if setup:
tag(doc, 'pageSetup', setup)
if worksheet.header_footer.hasHeader(
) or worksheet.header_footer.hasFooter():
start_tag(doc, 'headerFooter')
if worksheet.header_footer.hasHeader():
tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader())
if worksheet.header_footer.hasFooter():
tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter())
end_tag(doc, 'headerFooter')
if worksheet._charts or worksheet._images:
tag(doc, 'drawing', {'r:id': 'rId1'})
# If vba is being preserved then add a legacyDrawing element so
# that any controls can be drawn.
if vba_root is not None:
el = vba_root.find('{%s}legacyDrawing' % SHEET_MAIN_NS)
if el is not None:
rId = el.get('{%s}id' % REL_NS)
tag(doc, 'legacyDrawing', {'r:id': rId})
breaks = worksheet.page_breaks
if breaks:
start_tag(doc, 'rowBreaks', {
'count': str(len(breaks)),
'manualBreakCount': str(len(breaks))
})
for b in breaks:
tag(doc, 'brk', {
'id': str(b),
'man': 'true',
'max': '16383',
'min': '0'
})
end_tag(doc, 'rowBreaks')
# add a legacyDrawing so that excel can draw comments
if worksheet._comment_count > 0:
tag(doc, 'legacyDrawing', {'r:id': 'commentsvml'})
end_tag(doc, 'worksheet')
doc.endDocument()
xml_string = xml_file.getvalue()
xml_file.close()
return xml_string
def test_end_tag(self, doc, root):
end_tag(root, "end")
assert doc.getvalue() == b"</end>"
def test_end_tag(self, doc, root):
""""""
end_tag(root, "blah")
assert doc.getvalue() == "<start></blah>"
def test_end_tag(self, doc, root):
end_tag(root, "end")
assert doc.getvalue() == "<start>just words</end>"
def buffered():
archive = EnhZipFile("buffered.zip", "w", ZIP_DEFLATED)
out = archive.start_entry(ZipInfo("sheet.xml"))
#out.write = partial(out.write, compress_type=ZIP_DEFLATED)
doc = XMLGenerator(out)
for i in range(1000000):
start_tag(doc, "test")
end_tag(doc, "test")
doc.endDocument()
out.close()
archive.close()
if __name__ == "__main__":
zf = EnhZipFile('t.zip', 'w')
import time
from openpyxl.xml.functions import XMLGenerator, start_tag, end_tag
w = zf.start_entry(ZipInfo('t.txt', time.localtime()[:6]))
doc = XMLGenerator(w)
start_tag(doc, "test")
end_tag(doc, "test")
doc.endDocument()
w.write(u"Line1\n")
w.write(u"Line2\n")
w.close()
zf.finish_entry()
w = zf.start_entry(ZipInfo('p.txt', time.localtime()[:6]))
w.write("Some text\n")
w.close()
zf.close()
