def test_conditional_formatting_setRules(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
rules = {
'A1:A4': [{
'type': 'colorScale',
'priority': '13',
'colorScale': {
'cfvo': [{
'type': 'min'
}, {
'type': 'max'
}],
'color': [Color('FFFF7128'),
Color('FFFFEF9C')]
}
}]
}
worksheet.conditional_formatting.setRules(rules)
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
expected = '<conditionalFormatting sqref="A1:A4"><cfRule type="colorScale" priority="1"><colorScale><cfvo type="min"></cfvo><cfvo type="max"></cfvo><color rgb="FFFF7128"></color><color rgb="FFFFEF9C"></color></colorScale></cfRule></conditionalFormatting>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_conditional_formatting_addCustomRule(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
dxfId = worksheet.conditional_formatting.addDxfStyle(
self.workbook, None, None, None)
worksheet.conditional_formatting.addCustomRule(
'C1:C10', {
'type': 'expression',
'dxfId': dxfId,
'formula': ['ISBLANK(C1)'],
'stopIfTrue': '1'
})
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
assert dxfId == 0
expected = '<conditionalFormatting sqref="C1:C10"><cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1"><formula>ISBLANK(C1)</formula></cfRule></conditionalFormatting>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def _get_xml_iter(xml_source):
if not hasattr(xml_source, 'name'):
if isinstance(xml_source, str):
return StringIO(xml_source)
else:
return StringIO(str(xml_source, 'utf-8'))
else:
xml_source.seek(0)
return xml_source
def _write_worksheets(self, archive, shared_string_table, style_writer):
drawing_id = 1
chart_id = 1
image_id = 1
shape_id = 1
for i, sheet in enumerate(self.workbook.worksheets):
archive.writestr(
PACKAGE_WORKSHEETS + '/sheet%d.xml' % (i + 1),
write_worksheet(sheet, shared_string_table,
style_writer.get_style_by_hash()))
if sheet._charts or sheet._images or sheet.relationships:
archive.writestr(
PACKAGE_WORKSHEETS + '/_rels/sheet%d.xml.rels' % (i + 1),
write_worksheet_rels(sheet, drawing_id))
if sheet._charts or sheet._images:
dw = DrawingWriter(sheet)
archive.writestr(
PACKAGE_DRAWINGS + '/drawing%d.xml' % drawing_id,
dw.write())
archive.writestr(
PACKAGE_DRAWINGS +
'/_rels/drawing%d.xml.rels' % drawing_id,
dw.write_rels(chart_id, image_id))
drawing_id += 1
for chart in sheet._charts:
cw = ChartWriter(chart)
archive.writestr(
PACKAGE_CHARTS + '/chart%d.xml' % chart_id, cw.write())
if chart._shapes:
archive.writestr(
PACKAGE_CHARTS +
'/_rels/chart%d.xml.rels' % chart_id,
cw.write_rels(drawing_id))
sw = ShapeWriter(chart._shapes)
archive.writestr(
PACKAGE_DRAWINGS + '/drawing%d.xml' % drawing_id,
sw.write(shape_id))
shape_id += len(chart._shapes)
drawing_id += 1
chart_id += 1
for img in sheet._images:
buf = StringIO()
img.image.save(buf, format='PNG')
archive.writestr(
PACKAGE_IMAGES + '/image%d.png' % image_id,
buf.getvalue())
image_id += 1
def test_repair_central_directory():
from openpyxl.reader.excel import repair_central_directory, CENTRAL_DIRECTORY_SIGNATURE
data_a = "foobarbaz" + CENTRAL_DIRECTORY_SIGNATURE
data_b = "bazbarfoo1234567890123456890"
# The repair_central_directory looks for a magic set of bytes
# (CENTRAL_DIRECTORY_SIGNATURE) and strips off everything 18 bytes past the sequence
f = repair_central_directory(StringIO(data_a + data_b), True)
eq_(f.read(), data_a + data_b[:18])
f = repair_central_directory(StringIO(data_b), True)
eq_(f.read(), data_b)
def test_conditional_formatting_addCustomRule(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
dxfId = worksheet.conditional_formatting.addDxfStyle(self.workbook, None, None, None)
worksheet.conditional_formatting.addCustomRule('C1:C10', {'type': 'expression', 'dxfId': dxfId, 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1'})
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
assert dxfId == 0
expected = '<conditionalFormatting sqref="C1:C10"><cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1"><formula>ISBLANK(C1)</formula></cfRule></conditionalFormatting>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_conditional_formatting_setRules(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
rules = {'A1:A4': [{'type': 'colorScale', 'priority': '13',
'colorScale': {'cfvo': [{'type': 'min'}, {'type': 'max'}], 'color':
[Color('FFFF7128'), Color('FFFFEF9C')]}}]}
worksheet.conditional_formatting.setRules(rules)
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
expected = '<conditionalFormatting sqref="A1:A4"><cfRule type="colorScale" priority="1"><colorScale><cfvo type="min"></cfvo><cfvo type="max"></cfvo><color rgb="FFFF7128"></color><color rgb="FFFFEF9C"></color></colorScale></cfRule></conditionalFormatting>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def _write_worksheets(self, archive, shared_string_table, style_writer):
drawing_id = 1
chart_id = 1
image_id = 1
shape_id = 1
for i, sheet in enumerate(self.workbook.worksheets):
archive.writestr(
PACKAGE_WORKSHEETS + "/sheet%d.xml" % (i + 1),
write_worksheet(sheet, shared_string_table, style_writer.get_style_by_hash()),
)
if sheet._charts or sheet._images or sheet.relationships:
archive.writestr(
PACKAGE_WORKSHEETS + "/_rels/sheet%d.xml.rels" % (i + 1), write_worksheet_rels(sheet, drawing_id)
)
if sheet._charts or sheet._images:
dw = DrawingWriter(sheet)
archive.writestr(PACKAGE_DRAWINGS + "/drawing%d.xml" % drawing_id, dw.write())
archive.writestr(
PACKAGE_DRAWINGS + "/_rels/drawing%d.xml.rels" % drawing_id, dw.write_rels(chart_id, image_id)
)
drawing_id += 1
for chart in sheet._charts:
cw = ChartWriter(chart)
archive.writestr(PACKAGE_CHARTS + "/chart%d.xml" % chart_id, cw.write())
if chart._shapes:
archive.writestr(
PACKAGE_CHARTS + "/_rels/chart%d.xml.rels" % chart_id, cw.write_rels(drawing_id)
)
sw = ShapeWriter(chart._shapes)
archive.writestr(PACKAGE_DRAWINGS + "/drawing%d.xml" % drawing_id, sw.write(shape_id))
shape_id += len(chart._shapes)
drawing_id += 1
chart_id += 1
for img in sheet._images:
buf = StringIO()
img.image.save(buf, format="PNG")
archive.writestr(PACKAGE_IMAGES + "/image%d.png" % image_id, buf.getvalue())
image_id += 1
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_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 repair_central_directory(zipFile, is_file_instance):
''' trims trailing data from the central directory
code taken from http://stackoverflow.com/a/7457686/570216, courtesy of Uri Cohen
'''
f = zipFile if is_file_instance else open(zipFile, 'r+b')
data = f.read()
if hasattr(data, "decode"):
data = data.decode("utf-8")
pos = data.find(
CENTRAL_DIRECTORY_SIGNATURE) # End of central directory signature
if (pos > 0):
sio = StringIO(data)
sio.seek(pos + 22) # size of 'ZIP end of central directory record'
sio.truncate()
sio.seek(0)
return sio
f.seek(0)
return f
def repair_central_directory(zipFile, is_file_instance):
''' trims trailing data from the central directory
code taken from http://stackoverflow.com/a/7457686/570216, courtesy of Uri Cohen
'''
f = zipFile if is_file_instance else open(zipFile, 'r+b')
data = f.read()
if hasattr(data, "decode"):
data = data.decode("utf-8")
pos = data.find(CENTRAL_DIRECTORY_SIGNATURE) # End of central directory signature
if (pos > 0):
sio = StringIO(data)
sio.seek(pos + 22) # size of 'ZIP end of central directory record'
sio.truncate()
sio.seek(0)
return sio
f.seek(0)
return f
def assert_equals_file_content(reference_file, fixture, filetype='xml'):
if os.path.isfile(fixture):
fixture_file = open(fixture)
try:
fixture_content = fixture_file.read()
finally:
fixture_file.close()
else:
fixture_content = fixture
expected_file = open(reference_file)
try:
expected_content = expected_file.read()
finally:
expected_file.close()
if filetype == 'xml':
fixture_content = fromstring(fixture_content)
pretty_indent(fixture_content)
temp = BytesIO()
ElementTree(fixture_content).write(temp)
fixture_content = temp.getvalue()
expected_content = fromstring(expected_content)
pretty_indent(expected_content)
temp = BytesIO()
ElementTree(expected_content).write(temp)
expected_content = temp.getvalue()
fixture_lines = unicode(fixture_content).split('\n')
expected_lines = unicode(expected_content).split('\n')
differences = list(difflib.unified_diff(expected_lines, fixture_lines))
if differences:
temp = StringIO()
pprint(differences, stream=temp)
assert False, 'Differences found : %s' % temp.getvalue()
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
