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 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_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.get_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 = self._shared_date.datetime_to_julian(cell)
attributes['s'] = STYLES[dtype]['style']
elif cell and cell[0] == '=':
dtype = 'formula'
else:
dtype = 'string'
cell = self._string_builder.add(cell)
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 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 = self._shared_date.datetime_to_julian(cell)
attributes['s'] = STYLES[dtype]['style']
elif cell and cell[0] == '=':
dtype = 'formula'
else:
dtype = 'string'
cell = self._string_builder.add(cell)
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_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, 'utf-8')
start_tag(
doc, 'worksheet', {
'xml:space':
'preserve',
'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_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
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'
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.get_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:
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(worksheet, string_table, style_table):
"""Write a worksheet to an xml file."""
xml_file = StringIO()
doc = XMLGenerator(out=xml_file, encoding='utf-8')
start_tag(doc, 'worksheet',
{'xml:space': 'preserve',
'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': '%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)
write_worksheet_data(doc, worksheet, string_table, style_table)
if worksheet.auto_filter:
tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
write_worksheet_mergecells(doc, worksheet)
write_worksheet_datavalidations(doc, worksheet)
write_worksheet_hyperlinks(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'})
end_tag(doc, 'worksheet')
doc.endDocument()
xml_string = xml_file.getvalue()
xml_file.close()
return xml_string
def write_header(self):
fobj = self.get_temporary_file(filename=self._fileobj_header_name)
doc = XMLGenerator(fobj, 'utf-8')
start_tag(doc, 'worksheet',
{'xml:space': 'preserve',
'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 _close_content(self):
doc = self._get_content_generator()
end_tag(doc, 'sheetData')
end_tag(doc, 'worksheet')
def _close_content(self):
doc = self._get_content_generator()
end_tag(doc, 'sheetData')
end_tag(doc, 'worksheet')
