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_workbook(workbook):
"""Write the core workbook xml."""
wb = workbook
wb.rels = RelationshipList()
root = WorkbookPackage()
props = WorkbookProperties() # needs a mapping to the workbook for preservation
if wb.code_name is not None:
props.codeName = wb.code_name
if wb.excel_base_date == CALENDAR_MAC_1904:
props.date1904 = True
root.workbookPr = props
# workbook protection
root.workbookProtection = wb.security
# book views
active = get_active_sheet(wb)
if wb.views:
wb.views[0].activeTab = active
root.bookViews = wb.views
# worksheets
for idx, sheet in enumerate(wb._sheets, 1):
sheet_node = ChildSheet(name=sheet.title, sheetId=idx, id="rId{0}".format(idx))
rel = Relationship(type=sheet._rel_type, Target=sheet.path)
wb.rels.append(rel)
if not sheet.sheet_state == 'visible':
if len(wb._sheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.state = sheet.sheet_state
root.sheets.append(sheet_node)
# external references
for link in wb._external_links:
# need to match a counter with a workbook's relations
rId = len(wb.rels) + 1
rel = Relationship(type=link._rel_type, Target=link.path)
wb.rels.append(rel)
ext = ExternalReference(id=rel.id)
root.externalReferences.append(ext)
# Defined names
defined_names = copy(wb.defined_names) # don't add special defns to workbook itself.
# Defined names -> autoFilter
for idx, sheet in enumerate(wb.worksheets):
auto_filter = sheet.auto_filter.ref
if auto_filter:
name = DefinedName(name='_FilterDatabase', localSheetId=idx, hidden=True)
name.value = u"{0}!{1}".format(quote_sheetname(sheet.title),
absolute_coordinate(auto_filter)
)
defined_names.append(name)
# print titles
if sheet.print_titles:
name = DefinedName(name="Print_Titles", localSheetId=idx)
name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_titles.split(",")])
defined_names.append(name)
# print areas
if sheet.print_area:
name = DefinedName(name="Print_Area", localSheetId=idx)
name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_area])
defined_names.append(name)
root.definedNames = defined_names
# pivots
pivot_caches = set()
for pivot in wb._pivots:
if pivot.cache not in pivot_caches:
pivot_caches.add(pivot.cache)
c = PivotCache(cacheId=pivot.cacheId)
root.pivotCaches.append(c)
rel = Relationship(Type=pivot.cache.rel_type, Target=pivot.cache.path)
wb.rels.append(rel)
c.id = rel.id
wb._pivots = [] # reset
root.calcPr = wb.calculation
return tostring(root.to_tree())
def read_worksheets(self):
for sheet, rel in self.parser.find_sheets():
if rel.target not in self.valid_files:
continue
if "chartsheet" in rel.Type:
self.read_chartsheet(sheet, rel)
continue
rels_path = get_rels_path(rel.target)
rels = RelationshipList()
if rels_path in self.valid_files:
rels = get_dependents(self.archive, rels_path)
if self.read_only:
ws = ReadOnlyWorksheet(self.wb, sheet.name, rel.target,
self.shared_strings)
self.wb._sheets.append(ws)
continue
else:
fh = self.archive.open(rel.target)
ws = self.wb.create_sheet(sheet.name)
ws._rels = rels
ws_parser = WorksheetReader(ws, fh, self.shared_strings,
self.data_only)
ws_parser.bind_all()
# assign any comments to cells
for r in rels.find(COMMENTS_NS):
src = self.archive.read(r.target)
comment_sheet = CommentSheet.from_tree(fromstring(src))
for ref, comment in comment_sheet.comments:
ws[ref].comment = comment
# preserve link to VML file if VBA
if self.wb.vba_archive and ws.legacy_drawing:
ws.legacy_drawing = rels[ws.legacy_drawing].target
for t in ws_parser.tables:
src = self.archive.read(t)
xml = fromstring(src)
table = Table.from_tree(xml)
ws.add_table(table)
drawings = rels.find(SpreadsheetDrawing._rel_type)
for rel in drawings:
charts, images = find_images(self.archive, rel.target)
for c in charts:
ws.add_chart(c, c.anchor)
for im in images:
ws.add_image(im, im.anchor)
pivot_rel = rels.find(TableDefinition.rel_type)
for r in pivot_rel:
pivot_path = r.Target
src = self.archive.read(pivot_path)
tree = fromstring(src)
pivot = TableDefinition.from_tree(tree)
pivot.cache = self.parser.pivot_caches[pivot.cacheId]
ws.add_pivot(pivot)
ws.sheet_state = sheet.state
def _write_rels(self):
rels = RelationshipList()
rels.Relationship = self._rels
return rels.to_tree()
def read_worksheets(self):
comment_warning = """Cell '{0}':{1} is part of a merged range but has a comment which will be removed because merged cells cannot contain any data."""
for sheet, rel in self.parser.find_sheets():
if rel.target not in self.valid_files:
continue
if "chartsheet" in rel.Type:
self.read_chartsheet(sheet, rel)
continue
rels_path = get_rels_path(rel.target)
rels = RelationshipList()
if rels_path in self.valid_files:
rels = get_dependents(self.archive, rels_path)
if self.read_only:
ws = ReadOnlyWorksheet(self.wb, sheet.name, rel.target,
self.shared_strings)
ws.sheet_state = sheet.state
self.wb._sheets.append(ws)
continue
else:
fh = self.archive.open(rel.target)
ws = self.wb.create_sheet(sheet.name)
ws._rels = rels
ws_parser = WorksheetReader(ws, fh, self.shared_strings,
self.data_only)
ws_parser.bind_all()
# assign any comments to cells
for r in rels.find(COMMENTS_NS):
src = self.archive.read(r.target)
comment_sheet = CommentSheet.from_tree(fromstring(src))
for ref, comment in comment_sheet.comments:
try:
ws[ref].comment = comment
except AttributeError:
c = ws[ref]
if isinstance(c, MergedCell):
warnings.warn(
comment_warning.format(ws.title, c.coordinate))
continue
# preserve link to VML file if VBA
if self.wb.vba_archive and ws.legacy_drawing:
ws.legacy_drawing = rels[ws.legacy_drawing].target
else:
ws.legacy_drawing = None
for t in ws_parser.tables:
src = self.archive.read(t)
xml = fromstring(src)
table = Table.from_tree(xml)
ws.add_table(table)
drawings = rels.find(SpreadsheetDrawing._rel_type)
for rel in drawings:
charts, images = find_images(self.archive, rel.target)
for c in charts:
ws.add_chart(c, c.anchor)
for im in images:
ws.add_image(im, im.anchor)
pivot_rel = rels.find(TableDefinition.rel_type)
for r in pivot_rel:
pivot_path = r.Target
src = self.archive.read(pivot_path)
tree = fromstring(src)
pivot = TableDefinition.from_tree(tree)
pivot.cache = self.parser.pivot_caches[pivot.cacheId]
ws.add_pivot(pivot)
ws.sheet_state = sheet.state
