def test_conditional_formatting_customRule(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
worksheet.conditional_formatting.add(
'C1:C10', {
'type': 'expression',
'formula': ['ISBLANK(C1)'],
'stopIfTrue': '1',
'dxf': {}
})
worksheet.conditional_formatting.setDxfStyles(self.workbook)
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(
xml, """
<conditionalFormatting sqref="C1:C10">
<cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1">
<formula>ISBLANK(C1)</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def test_conditional_formatting_update(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.update(rules)
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(xml, """
<conditionalFormatting sqref="A1:A4">
<cfRule type="colorScale" priority="1">
<colorScale>
<cfvo type="min" />
<cfvo type="max" />
<color rgb="FFFF7128" />
<color rgb="FFFFEF9C" />
</colorScale>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
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 test_conditional_font(self):
"""Test to verify font style written correctly."""
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
# Create cf rule
redFill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
whiteFont = Font(color=Color("FFFFFFFF"))
worksheet.conditional_formatting.add('A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False,
font=whiteFont, fill=redFill))
worksheet.conditional_formatting.setDxfStyles(self.workbook)
# First, verify conditional formatting xml
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(xml, """
<conditionalFormatting sqref="A1:A3">
<cfRule dxfId="0" operator="equal" priority="1" type="cellIs">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
# Second, verify conditional formatting dxf styles
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
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 sax_writer(ws=None):
from openpyxl.writer.worksheet import write_worksheet_data
if ws is None:
ws = make_worksheet()
out = BytesIO()
doc = XMLGenerator(out)
write_worksheet_data(doc, ws, [], [])
def _get_content_generator(self):
""" XXX: this is ugly, but it allows to resume writing the file
even after the handle is closed"""
# restart XMLGenerator at the end of the file to prevent it being overwritten
handle = self.get_temporary_file(filename=self._fileobj_content_name)
handle.seek(0, 2)
return XMLGenerator(out=handle)
def test_conditional_formatting_update(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.update(rules)
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(
xml, """
<conditionalFormatting sqref="A1:A4">
<cfRule type="colorScale" priority="1">
<colorScale>
<cfvo type="min" />
<cfvo type="max" />
<color rgb="FFFF7128" />
<color rgb="FFFFEF9C" />
</colorScale>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
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 = self.get_temporary_file(filename=self._fileobj_content_name)
handle.seek(0, 2)
return XMLGenerator(out=handle)
def test_formula_rule(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
worksheet.conditional_formatting.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], stopIfTrue=True))
worksheet.conditional_formatting.setDxfStyles(self.workbook)
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(xml, """
<conditionalFormatting sqref="C1:C10">
<cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1">
<formula>ISBLANK(C1)</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
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_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_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 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()
def root(doc):
return XMLGenerator(doc)
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 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_conditional_font(self):
"""Test to verify font style written correctly."""
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
# Create cf rule
redFill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
whiteFont = Font(color=Color("FFFFFFFF"))
worksheet.conditional_formatting.add(
'A1:A3',
CellIsRule(operator='equal',
formula=['"Fail"'],
stopIfTrue=False,
font=whiteFont,
fill=redFill))
worksheet.conditional_formatting.setDxfStyles(self.workbook)
# First, verify conditional formatting xml
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(
xml, """
<conditionalFormatting sqref="A1:A3">
<cfRule dxfId="0" operator="equal" priority="1" type="cellIs">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
# Second, verify conditional formatting dxf styles
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
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()
def write_header(self):
"""Dummy method to preserve API compatibility with DumpWorksheet"""
return XMLGenerator(BytesIO())
def doc(out):
doc = XMLGenerator(out)
return doc
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, 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
