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_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:
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_worksheet(worksheet, string_table, style_table):
"""Write a worksheet to an xml file."""
xml_file = StringIO()
doc = XMLGenerator(xml_file, '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)
})
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_hyperlinks(doc, worksheet)
if worksheet._charts:
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_worksheet(worksheet, string_table, style_table):
"""Write a worksheet to an xml file."""
xml_file = StringIO()
doc = XMLGenerator(xml_file, '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)})
end_tag(doc, 'sheetPr')
tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
start_tag(doc, 'sheetViews')
start_tag(doc, 'sheetView', {'workbookViewId': '0'})
tag(doc, 'selection', {'activeCell': worksheet.active_cell,
'sqref': worksheet.selected_cell})
end_tag(doc, 'sheetView')
end_tag(doc, 'sheetViews')
tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
write_worksheet_cols(doc, worksheet)
write_worksheet_data(doc, worksheet, string_table, style_table)
write_worksheet_hyperlinks(doc, worksheet)
if worksheet._charts:
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_worksheet(worksheet, string_table, style_table):
"""Write a worksheet to an xml file."""
xml_file = StringIO()
doc = XMLGenerator(xml_file, "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)},
)
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_hyperlinks(doc, worksheet)
if worksheet._charts:
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_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 = 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 __init__(self, parent_workbook):
Worksheet.__init__(self, parent_workbook)
self._max_col = 0
self._max_row = 0
self._parent = parent_workbook
self._fileobj_header = NamedTemporaryFile(mode='r+', prefix='openpyxl.', suffix='.header', delete=False)
self._fileobj_content = NamedTemporaryFile(mode='r+', prefix='openpyxl.', suffix='.content', delete=False)
self._fileobj = NamedTemporaryFile(mode='w', prefix='openpyxl.', delete=False)
self.doc = XMLGenerator(self._fileobj_content, 'utf-8')
self.header = XMLGenerator(self._fileobj_header, 'utf-8')
self.title = 'Sheet'
self._shared_date = SharedDate()
self._string_builder = self._parent.strings_table_builder
def _get_content_generator(self):
""" XXX: this is ugly, but it allows to resume writing the file
even after the handle is closed"""
# when I'll recreate the XMLGenerator, it will start writing at the
# begining of the file, erasing previously entered rows, so we have
# to move to the end of the file before adding new tags
handle = get_temporary_file(filename=self._fileobj_content_name)
handle.seek(0, 2)
doc = XMLGenerator(out=handle)
return doc
def write_worksheet(worksheet, string_table, style_table):
"""Write a worksheet to an xml file."""
xml_file = StringIO()
doc = XMLGenerator(xml_file, '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)})
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_hyperlinks(doc, worksheet)
margins = worksheet.page_margins.margins
if margins:
tag(doc, 'pageMargins', margins)
if worksheet.paper_size is not None: #size and orientation set in common setter, so expect both or neither
tag(doc, 'pageSetup', {'paperSize':'%s' % worksheet.paper_size, 'orientation':'%s' % worksheet.orientation})
if worksheet._charts:
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_string_table(string_table):
"""Write the string table xml."""
temp_buffer = StringIO()
doc = XMLGenerator(temp_buffer, '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.iteritems(),
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(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',
{'xml:space': 'preserve',
'xmlns': SHEET_MAIN_NS,
'xmlns:r': REL_NS})
if vba_root is not None:
codename = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS).get('codeName', worksheet.title)
start_tag(doc, 'sheetPr', {"codeName": codename})
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.auto_filter:
tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
write_worksheet_mergecells(doc, worksheet)
write_worksheet_datavalidations(doc, worksheet)
write_worksheet_hyperlinks(doc, worksheet)
for range_string, rules in worksheet.conditional_formatting.cf_rules.iteritems():
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')
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 the sheet has an xml_source field then the workbook must have
# been loaded with keep-vba true and we need to extract any control
# elements.
if vba_root is not None:
for t in ('{%s}legacyDrawing' % SHEET_MAIN_NS,
'{%s}controls' % SHEET_MAIN_NS):
for elem in vba_root.findall(t):
xml_file.write(re.sub(r' xmlns[^ >]*', '', tostring(elem).decode("utf-8")))
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')
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.auto_filter:
tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
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 write_worksheet(worksheet, string_table, style_table):
"""Write a worksheet to an xml file."""
if worksheet.xml_source:
vba_root = fromstring(worksheet.xml_source)
register_namespace("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships")
register_namespace("", "http://schemas.openxmlformats.org/spreadsheetml/2006/main")
else:
vba_root = None
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'})
if vba_root is not None:
codename = vba_root.find('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetPr').get('codeName', worksheet.title)
start_tag(doc, 'sheetPr', {"codeName": codename})
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)
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'})
# if the sheet has an xml_source field then the workbook must have
# been loaded with keep-vba true and we need to extract any control
# elements.
if vba_root is not None:
for t in ('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}legacyDrawing',
'{http://schemas.openxmlformats.org/spreadsheetml/2006/main}controls'):
for elem in vba_root.findall(t):
xml_file.write(re.sub(r' xmlns[^ >]*', '', tostring(elem).decode("utf-8")))
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')
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)
register_namespace(
"r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships"
)
register_namespace(
"", "http://schemas.openxmlformats.org/spreadsheetml/2006/main")
else:
vba_root = None
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'
})
if vba_root is not None:
codename = vba_root.find(
'{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetPr'
).get('codeName', worksheet.title)
start_tag(doc, 'sheetPr', {"codeName": codename})
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)
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'})
# if the sheet has an xml_source field then the workbook must have
# been loaded with keep-vba true and we need to extract any control
# elements.
if vba_root is not None:
for t in (
'{http://schemas.openxmlformats.org/spreadsheetml/2006/main}legacyDrawing',
'{http://schemas.openxmlformats.org/spreadsheetml/2006/main}controls'
):
for elem in vba_root.findall(t):
xml_file.write(
re.sub(r' xmlns[^ >]*', '',
tostring(elem).decode("utf-8")))
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')
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.auto_filter:
tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
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
