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 _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 = str(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 parse_tables(self, element):
for t in TablePartList.from_tree(element).tablePart:
rel = self.ws._rels[t.id]
self.tables.append(rel.Target)
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())
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)
if ws.page_breaks:
xf.write(ws.page_breaks.to_tree())
tables = TablePartList()
for table in ws._tables:
row = ws[table.ref][0]
for cell, col in zip(row, table.tableColumns):
if cell.value:
col.name = str(cell.value)
rel = Relationship(type=table._rel_type, Target="")
ws._rels.append(rel)
table._rel_id = rel.Id
tables.append(Related(id=rel.Id))
if tables:
xf.write(tables.to_tree())
xml = out.getvalue()
out.close()
return xml
def parse_tables(self, element):
for t in TablePartList.from_tree(element).tablePart:
rel = self.ws._rels[t.id]
self.tables.append(rel.Target)
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())
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)
if ws.page_breaks:
xf.write(ws.page_breaks.to_tree())
tables = TablePartList()
for table in ws._tables:
row = ws[table.ref][0]
for cell, col in zip(row, table.tableColumns):
if cell.value:
col.name = str(cell.value)
rel = Relationship(type=table._rel_type, Target="")
ws._rels.append(rel)
table._rel_id = rel.Id
tables.append(Related(id=rel.Id))
if tables:
xf.write(tables.to_tree())
xml = out.getvalue()
out.close()
return xml