def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = write_sheetPr(self.sheet_properties)
xf.write(pr)
views = Element("sheetViews")
views.append(self.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(self))
cols = write_cols(self)
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
try:
while True:
r = (yield)
xf.write(r)
except GeneratorExit:
pass
af = write_autofilter(self)
if af is not None:
xf.write(af)
if self._comments:
comments = Element("legacyDrawing", {"{%s}id" % REL_NS: "commentsvml"})
xf.write(comments)
def _writer(doc):
with xmlfile(doc) as xf:
with xf.element('sheetData'):
try:
while True:
body = (yield)
xf.write(body)
except GeneratorExit:
pass
def _writer(doc):
with xmlfile(doc) as xf:
with xf.element('sheetData'):
try:
while True:
body = (yield)
xf.write(body)
except GeneratorExit:
pass
def test_write_formula(worksheet, write_rows):
ws = worksheet
ws.cell('F1').value = 10
ws.cell('F2').value = 32
ws.cell('F3').value = '=F1+F2'
ws.cell('A4').value = '=A1+A2+A3'
ws.formula_attributes['A4'] = {'t': 'shared', 'ref': 'A4:C4', 'si': '0'}
ws.cell('B4').value = '=1'
ws.formula_attributes['B4'] = {'t': 'shared', 'si': '0'}
ws.cell('C4').value = '=1'
ws.formula_attributes['C4'] = {'t': 'shared', 'si': '0'}
out = BytesIO()
with xmlfile(out) as xf:
write_rows(xf, ws)
xml = out.getvalue()
expected = """
<sheetData>
<row r="1" spans="1:6">
<c r="F1" t="n">
<v>10</v>
</c>
</row>
<row r="2" spans="1:6">
<c r="F2" t="n">
<v>32</v>
</c>
</row>
<row r="3" spans="1:6">
<c r="F3">
<f>F1+F2</f>
<v></v>
</c>
</row>
<row r="4" spans="1:6">
<c r="A4">
<f ref="A4:C4" si="0" t="shared">A1+A2+A3</f>
<v></v>
</c>
<c r="B4">
<f si="0" t="shared"></f>
<v></v>
</c>
<c r="C4">
<f si="0" t="shared"></f>
<v></v>
</c>
</row>
</sheetData>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_formula(worksheet, write_rows):
ws = worksheet
ws.cell('F1').value = 10
ws.cell('F2').value = 32
ws.cell('F3').value = '=F1+F2'
ws.cell('A4').value = '=A1+A2+A3'
ws.formula_attributes['A4'] = {'t': 'shared', 'ref': 'A4:C4', 'si': '0'}
ws.cell('B4').value = '=1'
ws.formula_attributes['B4'] = {'t': 'shared', 'si': '0'}
ws.cell('C4').value = '=1'
ws.formula_attributes['C4'] = {'t': 'shared', 'si': '0'}
out = BytesIO()
with xmlfile(out) as xf:
write_rows(xf, ws)
xml = out.getvalue()
expected = """
<sheetData>
<row r="1" spans="1:6">
<c r="F1" t="n">
<v>10</v>
</c>
</row>
<row r="2" spans="1:6">
<c r="F2" t="n">
<v>32</v>
</c>
</row>
<row r="3" spans="1:6">
<c r="F3">
<f>F1+F2</f>
<v></v>
</c>
</row>
<row r="4" spans="1:6">
<c r="A4">
<f ref="A4:C4" si="0" t="shared">A1+A2+A3</f>
<v></v>
</c>
<c r="B4">
<f si="0" t="shared"></f>
<v></v>
</c>
<c r="C4">
<f si="0" t="shared"></f>
<v></v>
</c>
</row>
</sheetData>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_formula(worksheet, write_rows):
ws = worksheet
ws.cell("F1").value = 10
ws.cell("F2").value = 32
ws.cell("F3").value = "=F1+F2"
ws.cell("A4").value = "=A1+A2+A3"
ws.formula_attributes["A4"] = {"t": "shared", "ref": "A4:C4", "si": "0"}
ws.cell("B4").value = "=1"
ws.formula_attributes["B4"] = {"t": "shared", "si": "0"}
ws.cell("C4").value = "=1"
ws.formula_attributes["C4"] = {"t": "shared", "si": "0"}
out = BytesIO()
with xmlfile(out) as xf:
write_rows(xf, ws)
xml = out.getvalue()
expected = """
<sheetData>
<row r="1" spans="1:6">
<c r="F1" t="n">
<v>10</v>
</c>
</row>
<row r="2" spans="1:6">
<c r="F2" t="n">
<v>32</v>
</c>
</row>
<row r="3" spans="1:6">
<c r="F3">
<f>F1+F2</f>
<v></v>
</c>
</row>
<row r="4" spans="1:6">
<c r="A4">
<f ref="A4:C4" si="0" t="shared">A1+A2+A3</f>
<v></v>
</c>
<c r="B4">
<f si="0" t="shared"></f>
<v></v>
</c>
<c r="C4">
<f si="0" t="shared"></f>
<v></v>
</c>
</row>
</sheetData>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_comment(worksheet, write_cell_implementation):
write_cell = write_cell_implementation
from openpyxl.comments import Comment
ws = worksheet
cell = ws['A1']
cell.comment = Comment("test comment", "test author")
out = BytesIO()
with xmlfile(out) as xf:
write_cell(xf, ws, cell, False)
assert len(ws._comments) == 1
def test_write_hyperlink(worksheet, write_cell_implementation):
write_cell = write_cell_implementation
ws = worksheet
cell = ws['A1']
cell.value = "test"
cell.hyperlink = "http://www.test.com"
out = BytesIO()
with xmlfile(out) as xf:
write_cell(xf, ws, cell, cell.has_style)
assert len(worksheet._hyperlinks) == 1
def _writer(doc):
with xmlfile(doc) as xf:
with xf.element('sheetData'):
try:
while True:
row = (yield)
with xf.element("row"):
for cell in row:
with xf.element("v"):
xf.write(str(cell))
except GeneratorExit:
pass
def test_write_cell(worksheet, write_cell_implementation, value, expected):
write_cell = write_cell_implementation
ws = worksheet
cell = ws['A1']
cell.value = value
out = BytesIO()
with xmlfile(out) as xf:
write_cell(xf, ws, cell, cell.has_style)
xml = out.getvalue()
diff = compare_xml(xml, expected)
assert diff is None, diff
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = self.sheet_properties.to_tree()
xf.write(pr)
xf.write(self.views.to_tree())
cols = self.column_dimensions.to_tree()
self.sheet_format.outlineLevelCol = self.column_dimensions.max_outline
xf.write(self.sheet_format.to_tree())
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
try:
while True:
r = (yield)
xf.write(r)
except GeneratorExit:
pass
if self.protection.sheet:
xf.write(worksheet.protection.to_tree())
if self.auto_filter.ref:
xf.write(self.auto_filter.to_tree())
if self.sort_state.ref:
xf.write(self.sort_state.to_tree())
if self.data_validations.count:
xf.write(self.data_validations.to_tree())
drawing = write_drawing(self)
if drawing is not None:
xf.write(drawing)
if self._comments:
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
def get_stream(self):
with xmlfile(self.out) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
try:
while True:
el = (yield)
if el is True:
yield xf
elif el is None: # et_xmlfile chokes
continue
else:
xf.write(el)
except GeneratorExit:
pass
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = self.sheet_properties.to_tree()
xf.write(pr)
xf.write(self.views.to_tree())
cols = self.column_dimensions.to_tree()
self.sheet_format.outlineLevelCol = self.column_dimensions.max_outline
xf.write(self.sheet_format.to_tree())
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
try:
while True:
r = (yield)
xf.write(r)
except GeneratorExit:
pass
if self.protection.sheet:
xf.write(worksheet.protection.to_tree())
if self.auto_filter.ref:
xf.write(self.auto_filter.to_tree())
if self.sort_state.ref:
xf.write(self.sort_state.to_tree())
if self.data_validations.count:
xf.write(self.data_validations.to_tree())
drawing = write_drawing(self)
if drawing is not None:
xf.write(drawing)
if self._comments:
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = self.sheet_properties.to_tree()
xf.write(pr)
views = Element('sheetViews')
views.append(self.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(self))
cols = write_cols(self)
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
try:
while True:
r = (yield)
xf.write(r)
except GeneratorExit:
pass
if self.protection.sheet:
xf.write(worksheet.protection.to_tree())
af = write_autofilter(self)
if af is not None:
xf.write(af)
dv = write_datavalidation(self)
if dv is not None:
xf.write(dv)
drawing = write_drawing(self)
if drawing is not None:
xf.write(drawing)
if self._comments:
legacyDrawing = Related(id="commentsvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = self.sheet_properties.to_tree()
xf.write(pr)
views = Element('sheetViews')
views.append(self.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(self))
cols = write_cols(self)
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
try:
while True:
r = (yield)
xf.write(r)
except GeneratorExit:
pass
if self.protection.sheet:
xf.write(worksheet.protection.to_tree())
af = write_autofilter(self)
if af is not None:
xf.write(af)
dv = write_datavalidation(self)
if dv is not None:
xf.write(dv)
drawing = write_drawing(self)
if drawing is not None:
xf.write(drawing)
if self._comments:
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
def write_string_table(string_table):
"""Write the string table xml."""
out = BytesIO()
with xmlfile(out) as xf:
with xf.element("sst", xmlns=SHEET_MAIN_NS, uniqueCount="%d" % len(string_table)):
for key in string_table:
el = Element('si')
text = SubElement(el, 't')
text.text = key
if key.strip() != key:
text.set(PRESERVE_SPACE, 'preserve')
xf.write(el)
return out.getvalue()
def write_string_table(string_table):
"""Write the string table xml."""
out = BytesIO()
with xmlfile(out) as xf:
with xf.element("sst", xmlns=SHEET_MAIN_NS, uniqueCount="%d" % len(string_table)):
for key in string_table:
el = Element('si')
if key.strip() != key:
el.set(PRESERVE_SPACE, 'preserve')
text = SubElement(el, 't')
text.text = key
xf.write(el)
return out.getvalue()
def test_write_formula(worksheet, write_rows):
ws = worksheet
ws['F1'] = 10
ws['F2'] = 32
ws['F3'] = '=F1+F2'
ws['A4'] = '=A1+A2+A3'
ws['B4'] = "=SUM(A10:A14*B10:B14)"
ws.formula_attributes['B4'] = {'t': 'array', 'ref': 'B4:B8'}
out = BytesIO()
with xmlfile(out) as xf:
write_rows(xf, ws)
xml = out.getvalue()
expected = """
<sheetData>
<row r="1" spans="1:6">
<c r="F1" t="n">
<v>10</v>
</c>
</row>
<row r="2" spans="1:6">
<c r="F2" t="n">
<v>32</v>
</c>
</row>
<row r="3" spans="1:6">
<c r="F3">
<f>F1+F2</f>
<v></v>
</c>
</row>
<row r="4" spans="1:6">
<c r="A4">
<f>A1+A2+A3</f>
<v></v>
</c>
<c r="B4">
<f ref="B4:B8" t="array">SUM(A10:A14*B10:B14)</f>
<v></v>
</c>
</row>
</sheetData>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_date(worksheet, write_cell_implementation, value, expected,
iso_dates):
from openpyxl.cell import Cell
write_cell = write_cell_implementation
ws = worksheet
cell = ws['A1']
cell.value = value
cell.parent.parent.iso_dates = iso_dates
out = BytesIO()
with xmlfile(out) as xf:
write_cell(xf, ws, cell, cell.has_style)
xml = out.getvalue()
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_whitespace(worksheet, write_cell_implementation):
write_cell = write_cell_implementation
ws = worksheet
cell = ws['A1']
cell.value = " whitespace "
out = BytesIO()
with xmlfile(out) as xf:
write_cell(xf, ws, cell)
expected = """
<c t="inlineStr" r="A1">
<is>
<t xml:space="preserve"> whitespace </t>
</is>
</c>"""
xml = out.getvalue()
diff = compare_xml(xml, expected)
assert diff is None, diff
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = write_sheetPr(self.sheet_properties)
xf.write(pr)
views = Element('sheetViews')
views.append(self.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(self))
cols = write_cols(self)
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
try:
while True:
r = (yield)
xf.write(r)
except GeneratorExit:
pass
if self.protection.sheet:
prot = Element('sheetProtection', dict(self.protection))
xf.write(prot)
af = write_autofilter(self)
if af is not None:
xf.write(af)
dv = write_datavalidation(self)
if dv is not None:
xf.write(dv)
if self._comments:
comments = Element('legacyDrawing',
{'{%s}id' % REL_NS: 'commentsvml'})
xf.write(comments)
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = write_sheetPr(self.sheet_properties)
xf.write(pr)
views = Element('sheetViews')
views.append(self.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(self))
cols = write_cols(self)
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
try:
while True:
r = (yield)
xf.write(r)
except GeneratorExit:
pass
if self.protection.sheet:
prot = Element('sheetProtection', dict(self.protection))
xf.write(prot)
af = write_autofilter(self)
if af is not None:
xf.write(af)
dv = write_datavalidation(self)
if dv is not None:
xf.write(dv)
if self._comments:
comments = Element('legacyDrawing', {'{%s}id' % REL_NS: 'commentsvml'})
xf.write(comments)
def test_write_height(worksheet, write_rows):
ws = worksheet
ws.cell("F1").value = 10
ws.row_dimensions[ws.cell("F1").row].height = 30
ws.row_dimensions[ws.cell("F2").row].height = 30
ws._garbage_collect()
out = BytesIO()
with xmlfile(out) as xf:
write_rows(xf, ws)
xml = out.getvalue()
expected = """
<sheetData>
<row customHeight="1" ht="30" r="1" spans="1:6">
<c r="F1" t="n">
<v>10</v>
</c>
</row>
<row customHeight="1" ht="30" r="2" spans="1:6"></row>
</sheetData>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_height(worksheet, write_rows):
ws = worksheet
ws.cell('F1').value = 10
ws.row_dimensions[ws.cell('F1').row].height = 30
ws.row_dimensions[ws.cell('F2').row].height = 30
ws._garbage_collect()
out = BytesIO()
with xmlfile(out) as xf:
write_rows(xf, ws)
xml = out.getvalue()
expected = """
<sheetData>
<row customHeight="1" ht="30" r="1" spans="1:6">
<c r="F1" t="n">
<v>10</v>
</c>
</row>
<row customHeight="1" ht="30" r="2" spans="1:6"></row>
</sheetData>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_height(worksheet, write_rows):
from openpyxl.worksheet.dimensions import RowDimension
ws = worksheet
ws['F1'] = 10
ws.row_dimensions[1] = RowDimension(ws, height=30)
ws.row_dimensions[2] = RowDimension(ws, height=30)
out = BytesIO()
with xmlfile(out) as xf:
write_rows(xf, ws)
xml = out.getvalue()
expected = """
<sheetData>
<row customHeight="1" ht="30" r="1" spans="1:6">
<c r="F1" t="n">
<v>10</v>
</c>
</row>
<row customHeight="1" ht="30" r="2" spans="1:6"></row>
</sheetData>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = self.sheet_properties.to_tree()
xf.write(pr)
xf.write(self.views.to_tree())
cols = self.column_dimensions.to_tree()
self.sheet_format.outlineLevelCol = self.column_dimensions.max_outline
xf.write(self.sheet_format.to_tree())
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
cell = WriteOnlyCell(self)
try:
while True:
row = (yield)
row_idx = self._max_row
attrs = {'r': '%d' % row_idx}
if row_idx in self.row_dimensions:
dim = self.row_dimensions[row_idx]
attrs.update(dict(dim))
with xf.element("row", attrs):
for col_idx, value in enumerate(row, 1):
if value is None:
continue
try:
cell.value = value
except ValueError:
if isinstance(value, Cell):
cell = value
else:
raise ValueError
cell.col_idx = col_idx
cell.row = row_idx
styled = cell.has_style
write_cell(xf, self, cell, styled)
if styled: # styled cell or datetime
cell = WriteOnlyCell(self)
except GeneratorExit:
pass
if self.protection.sheet:
xf.write(self.protection.to_tree())
if self.auto_filter.ref:
xf.write(self.auto_filter.to_tree())
if self.sort_state.ref:
xf.write(self.sort_state.to_tree())
if self.conditional_formatting:
cfs = write_conditional_formatting(self)
for cf in cfs:
xf.write(cf)
if self.data_validations.count:
xf.write(self.data_validations.to_tree())
drawing = write_drawing(self)
if drawing is not None:
xf.write(drawing)
if self._comments:
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
def write_worksheet(worksheet, shared_strings):
"""Write a worksheet to an xml file."""
if LXML is True:
from .lxml_worksheet import write_cell, write_rows
else:
from .etree_worksheet import write_cell, write_rows
out = BytesIO()
with xmlfile(out) as xf:
with xf.element('worksheet', xmlns=SHEET_MAIN_NS):
props = write_properties(worksheet)
xf.write(props)
dim = Element('dimension',
{'ref': '%s' % worksheet.calculate_dimension()})
xf.write(dim)
views = Element('sheetViews')
views.append(worksheet.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(worksheet))
cols = write_cols(worksheet)
if cols is not None:
xf.write(cols)
write_rows(xf, worksheet)
if worksheet.protection.sheet:
prot = Element('sheetProtection', dict(worksheet.protection))
xf.write(prot)
af = write_autofilter(worksheet)
if af is not None:
xf.write(af)
merge = write_mergecells(worksheet)
if merge is not None:
xf.write(merge)
cfs = write_conditional_formatting(worksheet)
for cf in cfs:
xf.write(cf)
dv = write_datavalidation(worksheet)
if dv is not None:
xf.write(dv)
hyper = write_hyperlinks(worksheet)
if hyper is not None:
xf.write(hyper)
options = worksheet.print_options
if len(dict(options)) > 0:
new_element = options.write_xml_element()
xf.write(new_element)
del new_element
margins = Element('pageMargins', dict(worksheet.page_margins))
xf.write(margins)
del margins
setup = worksheet.page_setup
if len(dict(setup)) > 0:
new_element = setup.write_xml_element()
xf.write(new_element)
del new_element
hf = write_header_footer(worksheet)
if hf is not None:
xf.write(hf)
if worksheet._charts or worksheet._images:
drawing = Element('drawing', {'{%s}id' % REL_NS: 'rId1'})
xf.write(drawing)
del drawing
# If vba is being preserved then add a legacyDrawing element so
# that any controls can be drawn.
if worksheet.vba_controls is not None:
xml = Element("{%s}legacyDrawing" % SHEET_MAIN_NS,
{"{%s}id" % REL_NS: worksheet.vba_controls})
xf.write(xml)
pb = write_pagebreaks(worksheet)
if pb is not None:
xf.write(pb)
# add a legacyDrawing so that excel can draw comments
if worksheet._comment_count > 0:
comments = Element('legacyDrawing',
{'{%s}id' % REL_NS: 'commentsvml'})
xf.write(comments)
xml = out.getvalue()
out.close()
return xml
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = self.sheet_properties.to_tree()
xf.write(pr)
xf.write(self.views.to_tree())
cols = self.column_dimensions.to_tree()
self.sheet_format.outlineLevelCol = self.column_dimensions.max_outline
xf.write(self.sheet_format.to_tree())
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
cell = WriteOnlyCell(self)
try:
while True:
row = (yield)
row_idx = self._max_row
attrs = {'r': '%d' % row_idx}
if row_idx in self.row_dimensions:
dim = self.row_dimensions[row_idx]
attrs.update(dict(dim))
with xf.element("row", attrs):
for col_idx, value in enumerate(row, 1):
if value is None:
continue
try:
cell.value = value
except ValueError:
if isinstance(value, Cell):
cell = value
else:
raise ValueError
cell.col_idx = col_idx
cell.row = row_idx
styled = cell.has_style
write_cell(xf, self, cell, styled)
if styled: # styled cell or datetime
cell = WriteOnlyCell(self)
except GeneratorExit:
pass
if self.protection.sheet:
xf.write(self.protection.to_tree())
if self.auto_filter.ref:
xf.write(self.auto_filter.to_tree())
if self.sort_state.ref:
xf.write(self.sort_state.to_tree())
if self.data_validations.count:
xf.write(self.data_validations.to_tree())
drawing = write_drawing(self)
if drawing is not None:
xf.write(drawing)
if self._comments:
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
def write_worksheet(worksheet):
"""Write a worksheet to an xml file."""
ws = worksheet
ws._rels = RelationshipList()
ws._hyperlinks = []
out = BytesIO()
with xmlfile(out) as xf:
with xf.element('worksheet', xmlns=SHEET_MAIN_NS):
props = ws.sheet_properties.to_tree()
xf.write(props)
dim = SheetDimension(ref=ws.calculate_dimension())
xf.write(dim.to_tree())
xf.write(ws.views.to_tree())
cols = ws.column_dimensions.to_tree()
ws.sheet_format.outlineLevelCol = ws.column_dimensions.max_outline
xf.write(ws.sheet_format.to_tree())
if cols is not None:
xf.write(cols)
# write data
write_rows(xf, ws)
if ws.protection.sheet:
xf.write(ws.protection.to_tree())
if ws.auto_filter:
xf.write(ws.auto_filter.to_tree())
if ws.sort_state:
xf.write(ws.sort_state.to_tree())
merge = write_mergecells(ws)
if merge is not None:
xf.write(merge)
cfs = write_conditional_formatting(ws)
for cf in cfs:
xf.write(cf)
if ws.data_validations:
xf.write(ws.data_validations.to_tree())
hyper = write_hyperlinks(ws)
if hyper:
xf.write(hyper.to_tree())
options = ws.print_options
if dict(options):
new_element = options.to_tree()
xf.write(new_element)
margins = ws.page_margins.to_tree()
xf.write(margins)
setup = ws.page_setup
if dict(setup):
new_element = setup.to_tree()
xf.write(new_element)
if bool(ws.HeaderFooter):
xf.write(ws.HeaderFooter.to_tree())
if ws.page_breaks:
xf.write(ws.page_breaks.to_tree())
drawing = write_drawing(ws)
if drawing is not None:
xf.write(drawing)
# if there is an existing vml file associated with this sheet or if there
# are any comments we need to add a legacyDrawing relation to the vml file.
if (ws.legacy_drawing is not None or ws._comments):
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
tables = _add_table_headers(ws)
if tables:
xf.write(tables.to_tree())
xml = out.getvalue()
out.close()
return xml
def write_worksheet(worksheet, shared_strings):
"""Write a worksheet to an xml file."""
ws = worksheet
ws._rels = []
ws._hyperlinks = []
if LXML is True:
from .lxml_worksheet import write_cell, write_rows
else:
from .etree_worksheet import write_cell, write_rows
out = BytesIO()
with xmlfile(out) as xf:
with xf.element('worksheet', xmlns=SHEET_MAIN_NS):
props = worksheet.sheet_properties.to_tree()
xf.write(props)
dim = Element('dimension', {'ref': '%s' % worksheet.calculate_dimension()})
xf.write(dim)
views = Element('sheetViews')
views.append(worksheet.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(worksheet))
cols = write_cols(worksheet)
if cols is not None:
xf.write(cols)
write_rows(xf, worksheet)
if worksheet.protection.sheet:
xf.write(worksheet.protection.to_tree())
af = write_autofilter(worksheet)
if af is not None:
xf.write(af)
merge = write_mergecells(worksheet)
if merge is not None:
xf.write(merge)
cfs = write_conditional_formatting(worksheet)
for cf in cfs:
xf.write(cf)
dv = write_datavalidation(worksheet)
if dv is not None:
xf.write(dv)
hyper = write_hyperlinks(worksheet)
if hyper is not None:
xf.write(hyper)
options = worksheet.print_options
if dict(options):
new_element = options.to_tree()
xf.write(new_element)
margins = worksheet.page_margins.to_tree()
xf.write(margins)
setup = worksheet.page_setup
if dict(setup):
new_element = setup.to_tree()
xf.write(new_element)
hf = write_header_footer(worksheet)
if hf is not None:
xf.write(hf)
drawing = write_drawing(worksheet)
if drawing is not None:
xf.write(drawing)
# if there is an existing vml file associated with this sheet or if there
# are any comments we need to add a legacyDrawing relation to the vml file.
if worksheet.legacy_drawing is not None or worksheet._comment_count > 0:
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
if len(worksheet.page_breaks):
xf.write(worksheet.page_breaks.to_tree())
xml = out.getvalue()
out.close()
return xml
def write_worksheet(worksheet, shared_strings):
"""Write a worksheet to an xml file."""
if LXML is True:
from .lxml_worksheet import write_cell, write_rows
else:
from .etree_worksheet import write_cell, write_rows
out = BytesIO()
with xmlfile(out) as xf:
with xf.element('worksheet', xmlns=SHEET_MAIN_NS):
props = write_properties(worksheet)
xf.write(props)
dim = Element('dimension', {'ref': '%s' % worksheet.calculate_dimension()})
xf.write(dim)
views = Element('sheetViews')
views.append(worksheet.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(worksheet))
cols = write_cols(worksheet)
if cols is not None:
xf.write(cols)
write_rows(xf, worksheet)
if worksheet.protection.sheet:
prot = Element('sheetProtection', dict(worksheet.protection))
xf.write(prot)
af = write_autofilter(worksheet)
if af is not None:
xf.write(af)
merge = write_mergecells(worksheet)
if merge is not None:
xf.write(merge)
cfs = write_conditional_formatting(worksheet)
for cf in cfs:
xf.write(cf)
dv = write_datavalidation(worksheet)
if dv is not None:
xf.write(dv)
hyper = write_hyperlinks(worksheet)
if hyper is not None:
xf.write(hyper)
options = worksheet.print_options
if len(dict(options)) > 0:
new_element = options.write_xml_element()
xf.write(new_element)
del new_element
margins = Element('pageMargins', dict(worksheet.page_margins))
xf.write(margins)
del margins
setup = worksheet.page_setup
if len(dict(setup)) > 0:
new_element = setup.write_xml_element()
xf.write(new_element)
del new_element
hf = write_header_footer(worksheet)
if hf is not None:
xf.write(hf)
if worksheet._charts or worksheet._images:
drawing = Element('drawing', {'{%s}id' % REL_NS: 'rId1'})
xf.write(drawing)
del drawing
pb = write_pagebreaks(worksheet)
if pb is not None:
xf.write(pb)
# If vba is being preserved then add a legacyDrawing element so
# that any controls can be drawn.
if worksheet.vba_controls is not None:
xml = Element("{%s}legacyDrawing" % SHEET_MAIN_NS,
{"{%s}id" % REL_NS : worksheet.vba_controls})
xf.write(xml)
elif worksheet._comment_count > 0:
# add a legacyDrawing so that excel can draw comments
# If a legacyDrawing element has already been added then
# we have to hope it already contains the vml for
# comments because we cannot add another.
comments = Element('{%s}legacyDrawing' % SHEET_MAIN_NS,
{'{%s}id' % REL_NS: 'commentsvml'})
xf.write(comments)
xml = out.getvalue()
out.close()
return xml
def write_worksheet(worksheet, shared_strings):
"""Write a worksheet to an xml file."""
ws = worksheet
ws._rels = []
ws._hyperlinks = []
if LXML is True:
from .lxml_worksheet import write_cell, write_rows
else:
from .etree_worksheet import write_cell, write_rows
out = BytesIO()
with xmlfile(out) as xf:
with xf.element('worksheet', xmlns=SHEET_MAIN_NS):
props = worksheet.sheet_properties.to_tree()
xf.write(props)
dim = Element('dimension',
{'ref': '%s' % worksheet.calculate_dimension()})
xf.write(dim)
views = Element('sheetViews')
views.append(worksheet.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(worksheet))
cols = write_cols(worksheet)
if cols is not None:
xf.write(cols)
write_rows(xf, worksheet)
if worksheet.protection.sheet:
xf.write(worksheet.protection.to_tree())
af = write_autofilter(worksheet)
if af is not None:
xf.write(af)
merge = write_mergecells(worksheet)
if merge is not None:
xf.write(merge)
cfs = write_conditional_formatting(worksheet)
for cf in cfs:
xf.write(cf)
dv = write_datavalidation(worksheet)
if dv is not None:
xf.write(dv)
hyper = write_hyperlinks(worksheet)
if hyper is not None:
xf.write(hyper)
options = worksheet.print_options
if dict(options):
new_element = options.to_tree()
xf.write(new_element)
margins = worksheet.page_margins.to_tree()
xf.write(margins)
setup = worksheet.page_setup
if dict(setup):
new_element = setup.to_tree()
xf.write(new_element)
hf = write_header_footer(worksheet)
if hf is not None:
xf.write(hf)
drawing = write_drawing(worksheet)
if drawing is not None:
xf.write(drawing)
# if there is an existing vml file associated with this sheet or if there
# are any comments we need to add a legacyDrawing relation to the vml file.
if worksheet.legacy_drawing is not None or worksheet._comment_count > 0:
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
if len(worksheet.page_breaks):
xf.write(worksheet.page_breaks.to_tree())
xml = out.getvalue()
out.close()
return xml
def write_worksheet(worksheet, shared_strings):
"""Write a worksheet to an xml file."""
worksheet._rels = []
if LXML is True:
from .lxml_worksheet import write_cell, write_rows
else:
from .etree_worksheet import write_cell, write_rows
out = BytesIO()
with xmlfile(out) as xf:
with xf.element('worksheet', xmlns=SHEET_MAIN_NS):
props = worksheet.sheet_properties.to_tree()
xf.write(props)
dim = Element('dimension', {'ref': '%s' % worksheet.calculate_dimension()})
xf.write(dim)
views = Element('sheetViews')
views.append(worksheet.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(worksheet))
cols = write_cols(worksheet)
if cols is not None:
xf.write(cols)
write_rows(xf, worksheet)
if worksheet.protection.sheet:
xf.write(worksheet.protection.to_tree())
if worksheet.auto_filter:
xf.write(worksheet.auto_filter.to_tree())
if worksheet.sort_state:
xf.write(worksheet.sort_state.to_tree())
merge = write_mergecells(worksheet)
if merge is not None:
xf.write(merge)
cfs = write_conditional_formatting(worksheet)
for cf in cfs:
xf.write(cf)
if worksheet.data_validations:
xf.write(worksheet.data_validations.to_tree())
hyper = write_hyperlinks(worksheet)
if hyper is not None:
xf.write(hyper)
options = worksheet.print_options
if dict(options):
new_element = options.to_tree()
xf.write(new_element)
margins = worksheet.page_margins.to_tree()
xf.write(margins)
setup = worksheet.page_setup
if dict(setup):
new_element = setup.to_tree()
xf.write(new_element)
hf = write_header_footer(worksheet)
if hf is not None:
xf.write(hf)
drawing = write_drawing(worksheet)
if drawing is not None:
xf.write(drawing)
# If vba is being preserved then add a legacyDrawing element so
# that any controls can be drawn.
if worksheet.vba_controls is not None:
legacyDrawing = Related(id=worksheet.vba_controls)
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
elif worksheet._comment_count > 0:
# add a legacyDrawing so that excel can draw comments
# If a legacyDrawing element has already been added then
# we have to hope it already contains the vml for
# comments because we cannot add another.
legacyDrawing = Related(id="commentsvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
if len(worksheet.page_breaks):
xf.write(worksheet.page_breaks.to_tree())
xml = out.getvalue()
out.close()
return xml
def write_worksheet(worksheet, shared_strings):
"""Write a worksheet to an xml file."""
worksheet._rels = []
if LXML is True:
from .lxml_worksheet import write_cell, write_rows
else:
from .etree_worksheet import write_cell, write_rows
out = BytesIO()
with xmlfile(out) as xf:
with xf.element('worksheet', xmlns=SHEET_MAIN_NS):
props = worksheet.sheet_properties.to_tree()
xf.write(props)
dim = Element('dimension',
{'ref': '%s' % worksheet.calculate_dimension()})
xf.write(dim)
views = Element('sheetViews')
views.append(worksheet.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(worksheet))
cols = write_cols(worksheet)
if cols is not None:
xf.write(cols)
write_rows(xf, worksheet)
if worksheet.protection.sheet:
xf.write(worksheet.protection.to_tree())
af = write_autofilter(worksheet)
if af is not None:
xf.write(af)
merge = write_mergecells(worksheet)
if merge is not None:
xf.write(merge)
cfs = write_conditional_formatting(worksheet)
for cf in cfs:
xf.write(cf)
dv = write_datavalidation(worksheet)
if dv is not None:
xf.write(dv)
hyper = write_hyperlinks(worksheet)
if hyper is not None:
xf.write(hyper)
options = worksheet.print_options
if dict(options):
new_element = options.to_tree()
xf.write(new_element)
margins = worksheet.page_margins.to_tree()
xf.write(margins)
setup = worksheet.page_setup
if dict(setup):
new_element = setup.to_tree()
xf.write(new_element)
hf = write_header_footer(worksheet)
if hf is not None:
xf.write(hf)
drawing = write_drawing(worksheet)
if drawing is not None:
xf.write(drawing)
# If vba is being preserved then add a legacyDrawing element so
# that any controls can be drawn.
if worksheet.vba_controls is not None:
legacyDrawing = Related(id=worksheet.vba_controls)
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
elif worksheet._comment_count > 0:
# add a legacyDrawing so that excel can draw comments
# If a legacyDrawing element has already been added then
# we have to hope it already contains the vml for
# comments because we cannot add another.
legacyDrawing = Related(id="commentsvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
if len(worksheet.page_breaks):
xf.write(worksheet.page_breaks.to_tree())
xml = out.getvalue()
out.close()
return xml
def write_worksheet(worksheet):
"""Write a worksheet to an xml file."""
ws = worksheet
ws._rels = RelationshipList()
ws._hyperlinks = []
out = BytesIO()
with xmlfile(out) as xf:
with xf.element('worksheet', xmlns=SHEET_MAIN_NS):
props = ws.sheet_properties.to_tree()
xf.write(props)
dim = SheetDimension(ref=ws.calculate_dimension())
xf.write(dim.to_tree())
xf.write(ws.views.to_tree())
cols = ws.column_dimensions.to_tree()
ws.sheet_format.outlineLevelCol = ws.column_dimensions.max_outline
xf.write(ws.sheet_format.to_tree())
if cols is not None:
xf.write(cols)
# write data
write_rows(xf, ws)
if ws.protection:
xf.write(ws.protection.to_tree())
if ws.auto_filter:
xf.write(ws.auto_filter.to_tree())
if ws.sort_state:
xf.write(ws.sort_state.to_tree())
merge = write_mergecells(ws)
if merge is not None:
xf.write(merge)
cfs = write_conditional_formatting(ws)
for cf in cfs:
xf.write(cf)
if ws.data_validations:
xf.write(ws.data_validations.to_tree())
hyper = write_hyperlinks(ws)
if hyper:
xf.write(hyper.to_tree())
options = ws.print_options
if dict(options):
new_element = options.to_tree()
xf.write(new_element)
margins = ws.page_margins.to_tree()
xf.write(margins)
setup = ws.page_setup
if dict(setup):
new_element = setup.to_tree()
xf.write(new_element)
if bool(ws.HeaderFooter):
xf.write(ws.HeaderFooter.to_tree())
if ws.page_breaks:
xf.write(ws.page_breaks.to_tree())
drawing = write_drawing(ws)
if drawing is not None:
xf.write(drawing)
# if there is an existing vml file associated with this sheet or if there
# are any comments we need to add a legacyDrawing relation to the vml file.
if (ws.legacy_drawing is not None or ws._comments):
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
tables = _add_table_headers(ws)
if tables:
xf.write(tables.to_tree())
xml = out.getvalue()
out.close()
return xml
