def write_drawing(worksheet):
"""
Add link to drawing if required
"""
if worksheet._charts or worksheet._images:
rel = Relationship(type="drawing", Target="")
worksheet._rels.append(rel)
drawing = Related()
drawing.id = rel.id
return drawing.to_tree("drawing")
def write_drawing(worksheet):
"""
Add link to drawing if required
"""
if worksheet._charts or worksheet._images:
rel = Relationship(type="drawing", target="")
worksheet._rels.append(rel)
drawing = Related()
drawing.id = "rId%s" % len(worksheet._rels)
return drawing.to_tree("drawing")
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)
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 _add_table_headers(ws):
"""
Check if tables have tableColumns and create them and autoFilter if necessary.
Column headers will be taken from the first row of the table.
"""
tables = TablePartList()
for table in ws._tables:
if not table.tableColumns:
table._initialise_columns()
if table.headerRowCount:
row = ws[table.ref][0]
for cell, col in zip(row, table.tableColumns):
if cell.data_type != "s":
warn(
"File may not be readable: column headings must be strings."
)
col.name = unicode(cell.value)
rel = Relationship(Type=table._rel_type, Target="")
ws._rels.append(rel)
table._rel_id = rel.Id
tables.append(Related(id=rel.Id))
return tables
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):
"""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
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, 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."""
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."""
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):
"""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_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)