def _write_chartsheets(self, archive):
from openpyxl.packaging.relationship import Relationship, RelationshipList
from openpyxl.worksheet.drawing import Drawing
for idx, sheet in enumerate(self.workbook.chartsheets, 1):
if sheet._charts:
drawing = SpreadsheetDrawing()
drawing.charts = sheet._charts
self.workbook._drawings.append(drawing)
drawing_id = len(self.workbook._drawings)
drawingpath = "{0}/drawing{1}.xml".format(
PACKAGE_DRAWINGS, drawing_id)
archive.writestr(drawingpath, tostring(drawing._write()))
archive.writestr(
"{0}/_rels/drawing{1}.xml.rels".format(
PACKAGE_DRAWINGS, drawing_id),
tostring(drawing._write_rels()))
rel = Relationship(type="drawing", target="/" + drawingpath)
rels = RelationshipList()
rels.append(rel)
tree = rels.to_tree()
sheet.drawing.id = "rId{0}".format(len(rels))
archive.writestr(
PACKAGE_CHARTSHEETS + '/_rels/sheet%d.xml.rels' % idx,
tostring(tree))
xml = tostring(sheet.to_tree())
archive.writestr(PACKAGE_CHARTSHEETS + '/sheet%d.xml' % idx, xml)
def write_rels(worksheet, comments_id=None):
"""Write relationships for the worksheet to xml."""
rels = RelationshipList(worksheet._rels)
# If there is an existing vml file that is preserved or extended then
# create its relation.
if worksheet.legacy_drawing is not None:
rel = Relationship("vmlDrawing",
id="anysvml",
target='/' + worksheet.legacy_drawing)
rels.append(rel)
# Comments
if worksheet._comment_count > 0:
rel = Relationship(type="comments",
id="comments",
target='/xl/comments%s.xml' % comments_id)
rels.append(rel)
if worksheet.legacy_drawing is None:
rel = Relationship(type="vmlDrawing",
id="anysvml",
target='/xl/drawings/commentsDrawing%s.vml' %
comments_id)
rels.append(rel)
return rels.to_tree()
def _write_chartsheets(self, archive):
from openpyxl.packaging.relationship import Relationship, RelationshipList
from openpyxl.worksheet.drawing import Drawing
for idx, sheet in enumerate(self.workbook.chartsheets, 1):
if sheet._charts:
drawing = SpreadsheetDrawing()
drawing.charts = sheet._charts
self.workbook._drawings.append(drawing)
drawing_id = len(self.workbook._drawings)
drawingpath = "{0}/drawing{1}.xml".format(PACKAGE_DRAWINGS, drawing_id)
archive.writestr(drawingpath, tostring(drawing._write()))
archive.writestr(
"{0}/_rels/drawing{1}.xml.rels".format(
PACKAGE_DRAWINGS, drawing_id),
tostring(drawing._write_rels())
)
rel = Relationship(type="drawing", target="/" + drawingpath)
rels = RelationshipList()
rels.append(rel)
tree = rels.to_tree()
sheet.drawing.id = "rId{0}".format(len(rels))
archive.writestr(PACKAGE_CHARTSHEETS +
'/_rels/sheet%d.xml.rels' % idx, tostring(tree)
)
xml = tostring(sheet.to_tree())
archive.writestr(PACKAGE_CHARTSHEETS + '/sheet%d.xml' % idx, xml)
def write_root_rels(workbook):
"""Write the relationships xml."""
rels = RelationshipList()
rel = Relationship(type="officeDocument", target=ARC_WORKBOOK, id="rId1")
rels.append(rel)
rel = Relationship("", target=ARC_CORE, id='rId2',)
rel.type = "%s/metadata/core-properties" % PKG_REL_NS
rels.append(rel)
rel = Relationship("extended-properties", target=ARC_APP, id='rId3')
rels.append(rel)
if workbook.vba_archive is not None:
relation_tag = '{%s}Relationship' % PKG_REL_NS
# See if there was a customUI relation and reuse its id
arc = fromstring(workbook.vba_archive.read(ARC_ROOT_RELS))
rel_tags = arc.findall(relation_tag)
rId = None
for rel in rel_tags:
if rel.get('Target') == ARC_CUSTOM_UI:
rId = rel.get('Id')
break
if rId is not None:
vba = Relationship("", target=ARC_CUSTOM_UI, id=rId)
vba.type = CUSTOMUI_NS
rels.append(vba)
return tostring(rels.to_tree())
def write_root_rels(self):
"""Write the package relationships"""
rels = RelationshipList()
rel = Relationship(type="officeDocument", Target=ARC_WORKBOOK)
rels.append(rel)
rel = Relationship(Type=f"{PKG_REL_NS}/metadata/core-properties",
Target=ARC_CORE)
rels.append(rel)
rel = Relationship(type="extended-properties", Target=ARC_APP)
rels.append(rel)
if len(self.wb.custom_doc_props) >= 1:
rel = Relationship(type="custom-properties", Target=ARC_CUSTOM)
rels.append(rel)
if self.wb.vba_archive is not None:
# See if there was a customUI relation and reuse it
xml = fromstring(self.wb.vba_archive.read(ARC_ROOT_RELS))
root_rels = RelationshipList.from_tree(xml)
for rel in root_rels.find(CUSTOMUI_NS):
rels.append(rel)
return tostring(rels.to_tree())
def write_workbook_rels(workbook):
"""Write the workbook relationships xml."""
rels = RelationshipList()
rId = 0
for idx, _ in enumerate(workbook.worksheets, 1):
rId += 1
rel = Relationship(type='worksheet',
target='worksheets/sheet%s.xml' % idx,
id='rId%d' % rId)
rels.append(rel)
for idx, _ in enumerate(workbook.chartsheets, 1):
rId += 1
rel = Relationship(type='chartsheet',
target='chartsheets/sheet%s.xml' % idx,
id='rId%d' % rId)
rels.append(rel)
rId += 1
strings = Relationship(type='sharedStrings',
target='sharedStrings.xml',
id='rId%d' % rId)
rels.append(strings)
rId += 1
styles = Relationship(type='styles', target='styles.xml', id='rId%d' % rId)
rels.append(styles)
rId += 1
theme = Relationship(type='theme',
target='theme/theme1.xml',
id='rId%d' % rId)
rels.append(theme)
if workbook.vba_archive:
rId += 1
vba = Relationship(type='vbaProject',
target='vbaProject.bin',
id='rId%d' % rId)
vba.type = 'http://schemas.microsoft.com/office/2006/relationships/vbaProject'
rels.append(vba)
external_links = workbook._external_links
if external_links:
for idx, link in enumerate(external_links, 1):
ext = Relationship(type='externalLink',
target='externalLinks/externalLink%d.xml' % idx,
id='rId%d' % (rId + idx))
rels.append(ext)
return tostring(rels.to_tree())
def _write_external_links(self):
# delegate to object
"""Write links to external workbooks"""
wb = self.workbook
for idx, link in enumerate(wb._external_links, 1):
link._id = idx
rels_path = get_rels_path(link.path[1:])
xml = link.to_tree()
self._archive.writestr(link.path[1:], tostring(xml))
rels = RelationshipList()
rels.append(link.file_link)
self._archive.writestr(rels_path, tostring(rels.to_tree()))
self.manifest.append(link)
def _write_external_links(self):
# delegate to object
"""Write links to external workbooks"""
wb = self.workbook
for idx, link in enumerate(wb._external_links, 1):
link._id = idx
rels_path = get_rels_path(link.path[1:])
xml = link.to_tree()
self._archive.writestr(link.path[1:], tostring(xml))
rels = RelationshipList()
rels.append(link.file_link)
self._archive.writestr(rels_path, tostring(rels.to_tree()))
self.manifest.append(link)
def _write_external_links(self, archive):
"""Write links to external workbooks"""
wb = self.workbook
for idx, link in enumerate(wb._external_links, 1):
link._path = "{0}{1}.xml".format(link._rel_type, idx)
arc_path = "{0}/{1}s/{2}".format(PACKAGE_XL, link._rel_type, link._path)
rels_path = get_rels_path(arc_path)
xml = link.to_tree()
archive.writestr(arc_path, tostring(xml))
rels = RelationshipList()
rels.append(link.file_link)
archive.writestr(rels_path, tostring(rels.to_tree()))
def _write_external_links(self, archive):
"""Write links to external workbooks"""
wb = self.workbook
for idx, link in enumerate(wb._external_links, 1):
link._path = "{0}{1}.xml".format(link._rel_type, idx)
arc_path = "{0}/{1}s/{2}".format(PACKAGE_XL, link._rel_type,
link._path)
rels_path = get_rels_path(arc_path)
xml = link.to_tree()
archive.writestr(arc_path, tostring(xml))
rels = RelationshipList()
rels.append(link.file_link)
archive.writestr(rels_path, tostring(rels.to_tree()))
def _write_rels(self, archive, manifest):
"""
Write the relevant child objects and add links
"""
if self.cache is None:
return
rels = RelationshipList()
r = Relationship(Type=self.cache.rel_type, Target=self.cache.path)
rels.append(r)
self.id = r.id
if self.cache.path[1:] not in archive.namelist():
self.cache._write(archive, manifest)
path = get_rels_path(self.path)
xml = tostring(rels.to_tree())
archive.writestr(path[1:], xml)
def _write_rels(self, archive, manifest):
"""
Write the relevant child objects and add links
"""
if self.records is None:
return
rels = RelationshipList()
r = Relationship(Type=self.records.rel_type, Target=self.records.path)
rels.append(r)
self.id = r.id
self.records._id = self._id
self.records._write(archive, manifest)
path = get_rels_path(self.path)
xml = tostring(rels.to_tree())
archive.writestr(path[1:], xml)
def write_workbook_rels(workbook):
"""Write the workbook relationships xml."""
rels = RelationshipList()
rId = 0
for idx, _ in enumerate(workbook.worksheets, 1):
rId += 1
rel = Relationship(type='worksheet', target='worksheets/sheet%s.xml' % idx, id='rId%d' % rId)
rels.append(rel)
for idx, _ in enumerate(workbook.chartsheets, 1):
rId += 1
rel = Relationship(type='chartsheet', target='chartsheets/sheet%s.xml' % idx, id='rId%d' % rId)
rels.append(rel)
rId += 1
strings = Relationship(type='sharedStrings', target='sharedStrings.xml', id='rId%d' % rId)
rels.append(strings)
rId += 1
styles = Relationship(type='styles', target='styles.xml', id='rId%d' % rId)
rels.append(styles)
rId += 1
theme = Relationship(type='theme', target='theme/theme1.xml', id='rId%d' % rId)
rels.append(theme)
if workbook.vba_archive:
rId += 1
vba = Relationship(type='vbaProject', target='vbaProject.bin', id='rId%d' % rId)
vba.type ='http://schemas.microsoft.com/office/2006/relationships/vbaProject'
rels.append(vba)
external_links = workbook._external_links
if external_links:
for idx, link in enumerate(external_links, 1):
ext = Relationship(type='externalLink',
target='externalLinks/externalLink%d.xml' % idx,
id='rId%d' % (rId +idx))
rels.append(ext)
return tostring(rels.to_tree())
def _write_chartsheets(self):
for idx, sheet in enumerate(self.workbook.chartsheets, 1):
sheet._id = idx
xml = tostring(sheet.to_tree())
self._archive.writestr(sheet.path[1:], xml)
self.manifest.append(sheet)
if sheet._drawing:
self._write_drawing(sheet._drawing)
rel = Relationship(type="drawing", Target=sheet._drawing.path)
rels = RelationshipList()
rels.append(rel)
tree = rels.to_tree()
rels_path = get_rels_path(sheet.path[1:])
self._archive.writestr(rels_path, tostring(tree))
def _write_chartsheets(self):
for idx, sheet in enumerate(self.workbook.chartsheets, 1):
sheet._id = idx
xml = tostring(sheet.to_tree())
self._archive.writestr(sheet.path[1:], xml)
self.manifest.append(sheet)
if sheet._drawing:
self._write_drawing(sheet._drawing)
rel = Relationship(type="drawing", Target=sheet._drawing.path)
rels = RelationshipList()
rels.append(rel)
tree = rels.to_tree()
rels_path = get_rels_path(sheet.path[1:])
self._archive.writestr(rels_path, tostring(tree))
def write_root_rels(workbook):
"""Write the relationships xml."""
rels = RelationshipList()
rel = Relationship(type="officeDocument", Target=ARC_WORKBOOK)
rels.append(rel)
rel = Relationship(Target=ARC_CORE, Type="%s/metadata/core-properties" % PKG_REL_NS)
rels.append(rel)
rel = Relationship(type="extended-properties", Target=ARC_APP)
rels.append(rel)
if workbook.vba_archive is not None:
# See if there was a customUI relation and reuse it
xml = fromstring(workbook.vba_archive.read(ARC_ROOT_RELS))
root_rels = RelationshipList.from_tree(xml)
for rel in root_rels.find(CUSTOMUI_NS):
rels.append(rel)
return tostring(rels.to_tree())
def write_root_rels(workbook):
"""Write the relationships xml."""
rels = RelationshipList()
rel = Relationship(type="officeDocument", Target=ARC_WORKBOOK)
rels.append(rel)
rel = Relationship(Target=ARC_CORE, Type="%s/metadata/core-properties" % PKG_REL_NS)
rels.append(rel)
rel = Relationship(type="extended-properties", Target=ARC_APP)
rels.append(rel)
if workbook.vba_archive is not None:
# See if there was a customUI relation and reuse it
xml = fromstring(workbook.vba_archive.read(ARC_ROOT_RELS))
root_rels = RelationshipList.from_tree(xml)
for rel in root_rels.find(CUSTOMUI_NS):
rels.append(rel)
return tostring(rels.to_tree())
def write_rels(worksheet, comments_id=None, vba_controls_id=None):
"""Write relationships for the worksheet to xml."""
rels = RelationshipList(worksheet._rels)
# VBA
if worksheet.vba_controls is not None:
rel = Relationship("vmlDrawing", id=worksheet.vba_controls,
target='/xl/drawings/vmlDrawing%s.vml' % vba_controls_id)
rels.append(rel)
# Comments
if worksheet._comment_count > 0:
rel = Relationship(type="comments", id="comments",
target='/xl/comments%s.xml' % comments_id)
rels.append(rel)
if worksheet.vba_controls is None:
rel = Relationship(type="vmlDrawing", id="commentsvml",
target='/xl/drawings/commentsDrawing%s.vml' % comments_id)
rels.append(rel)
return rels.to_tree()
def write_rels(worksheet, comments_id=None):
"""Write relationships for the worksheet to xml."""
rels = RelationshipList(worksheet._rels)
# If there is an existing vml file that is preserved or extended then
# create its relation.
if worksheet.legacy_drawing is not None:
rel = Relationship(type="vmlDrawing", Id="anysvml", Target='/' + worksheet.legacy_drawing)
rels.append(rel)
# Comments
if worksheet._comments:
rel = Relationship(type="comments", Id="comments",
Target='/xl/comments%s.xml' % comments_id)
rels.append(rel)
if worksheet.legacy_drawing is None:
rel = Relationship(type="vmlDrawing", Id="anysvml",
Target='/xl/drawings/commentsDrawing%s.vml' % comments_id)
rels.append(rel)
return rels.to_tree()
class WorkbookWriter:
def __init__(self, wb):
self.wb = wb
self.rels = RelationshipList()
self.package = WorkbookPackage()
self.package.workbookProtection = wb.security
self.package.calcPr = wb.calculation
def write_properties(self):
props = WorkbookProperties(
) # needs a mapping to the workbook for preservation
if self.wb.code_name is not None:
props.codeName = self.wb.code_name
if self.wb.excel_base_date == CALENDAR_MAC_1904:
props.date1904 = True
self.package.workbookPr = props
def write_worksheets(self):
for idx, sheet in enumerate(self.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)
self.rels.append(rel)
if not sheet.sheet_state == 'visible':
if len(self.wb._sheets) == 1:
raise ValueError(
"The only worksheet of a workbook cannot be hidden")
sheet_node.state = sheet.sheet_state
self.package.sheets.append(sheet_node)
def write_refs(self):
for link in self.wb._external_links:
# need to match a counter with a workbook's relations
rId = len(self.wb.rels) + 1
rel = Relationship(type=link._rel_type, Target=link.path)
self.rels.append(rel)
ext = ExternalReference(id=rel.id)
self.package.externalReferences.append(ext)
def write_names(self):
defined_names = copy(self.wb.defined_names)
# Defined names -> autoFilter
for idx, sheet in enumerate(self.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)
self.package.definedNames = defined_names
def write_pivots(self):
pivot_caches = set()
for pivot in self.wb._pivots:
if pivot.cache not in pivot_caches:
pivot_caches.add(pivot.cache)
c = PivotCache(cacheId=pivot.cacheId)
self.package.pivotCaches.append(c)
rel = Relationship(Type=pivot.cache.rel_type,
Target=pivot.cache.path)
self.rels.append(rel)
c.id = rel.id
#self.wb._pivots = [] # reset
def write_views(self):
active = get_active_sheet(self.wb)
if self.wb.views:
self.wb.views[0].activeTab = active
self.package.bookViews = self.wb.views
def write(self):
"""Write the core workbook xml."""
self.write_properties()
self.write_worksheets()
self.write_names()
self.write_pivots()
self.write_views()
self.write_refs()
return tostring(self.package.to_tree())
def write_rels(self):
"""Write the workbook relationships xml."""
styles = Relationship(type='styles', Target='styles.xml')
self.rels.append(styles)
theme = Relationship(type='theme', Target='theme/theme1.xml')
self.rels.append(theme)
if self.wb.vba_archive:
vba = Relationship(type='', Target='vbaProject.bin')
vba.Type = 'http://schemas.microsoft.com/office/2006/relationships/vbaProject'
self.rels.append(vba)
return tostring(self.rels.to_tree())
def write_root_rels(self):
"""Write the package relationships"""
rels = RelationshipList()
rel = Relationship(type="officeDocument", Target=ARC_WORKBOOK)
rels.append(rel)
rel = Relationship(Type="%s/metadata/core-properties" % PKG_REL_NS,
Target=ARC_CORE)
rels.append(rel)
rel = Relationship(type="extended-properties", Target=ARC_APP)
rels.append(rel)
if self.wb.vba_archive is not None:
# See if there was a customUI relation and reuse it
xml = fromstring(self.wb.vba_archive.read(ARC_ROOT_RELS))
root_rels = RelationshipList.from_tree(xml)
for rel in root_rels.find(CUSTOMUI_NS):
rels.append(rel)
return tostring(rels.to_tree())
def _write_rels(self):
rels = RelationshipList()
rels.Relationship = self._rels
return rels.to_tree()
def _write_rels(self):
rels = RelationshipList()
rels.Relationship = self._rels
return rels.to_tree()
