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 __init__(
self,
write_only=False,
iso_dates=False,
):
self._sheets = []
self._pivots = []
self._active_sheet_index = 0
self.defined_names = DefinedNameList()
self._external_links = []
self.properties = DocumentProperties()
self.security = DocumentSecurity()
self.__write_only = write_only
self.shared_strings = IndexedList()
self._setup_styles()
self.loaded_theme = None
self.vba_archive = None
self.is_template = False
self._differential_styles = DifferentialStyleList()
self.code_name = None
self.excel_base_date = CALENDAR_WINDOWS_1900
self.encoding = "utf-8"
self.iso_dates = iso_dates
if not self.write_only:
self._sheets.append(Worksheet(self))
self.rels = RelationshipList()
self.calculation = CalcProperties()
self.views = [BookView()]
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_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 test_tables(WorkSheetParser):
src = """
<sheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<tableParts count="1">
<tablePart r:id="rId1"/>
</tableParts>
</sheet>
"""
parser = WorkSheetParser
r = Relationship(type="table", Id="rId1", Target="../tables/table1.xml")
rels = RelationshipList()
rels.append(r)
parser.ws._rels = rels
parser.source = src
parser.parse()
assert parser.tables == ["../tables/table1.xml"]
def test_external_hyperlinks(WorkSheetParser):
src = """
<sheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<hyperlink xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
display="http://test.com" r:id="rId1" ref="A1"/>
</sheet>
"""
from openpyxl25.packaging.relationship import Relationship, RelationshipList
r = Relationship(type="hyperlink", Id="rId1", Target="../")
rels = RelationshipList()
rels.append(r)
parser = WorkSheetParser
parser.source = src
parser.ws._rels = rels
parser.parse()
assert parser.ws['A1'].hyperlink.target == "../"
def test_sequence(Relationship):
from openpyxl25.packaging.relationship import RelationshipList
rels = RelationshipList()
rels.append(
Relationship(type="drawing",
Target="drawings.xml",
TargetMode="external",
Id=""))
rels.append(
Relationship(type="chart",
Target="chart1.xml",
TargetMode="",
Id="chart"))
xml = tostring(rels.to_tree())
expected = """
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Target="drawings.xml" TargetMode="external" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"/>
<Relationship Id="chart" Target="chart1.xml" TargetMode="" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart"/>
</Relationships>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_read():
from openpyxl25.packaging.relationship import RelationshipList
xml = """
<Relationships>
<Relationship Id="rId3"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme"
Target="theme/theme1.xml"/>
<Relationship Id="rId2"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"
Target="worksheets/sheet1.xml"/>
<Relationship Id="rId1"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/chartsheet"
Target="chartsheets/sheet1.xml"/>
<Relationship Id="rId5"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"
Target="sharedStrings.xml"/>
<Relationship Id="rId4"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"
Target="styles.xml"/>
</Relationships>
"""
node = fromstring(xml)
rels = RelationshipList.from_tree(node)
assert len(rels) == 5
def _setup(self):
self.row_dimensions = BoundDictionary("index", self._add_row)
self.column_dimensions = DimensionHolder(
worksheet=self, default_factory=self._add_column)
self.page_breaks = PageBreak()
self._cells = {}
self._charts = []
self._images = []
self._rels = RelationshipList()
self._drawing = None
self._comments = []
self.merged_cells = MultiCellRange()
self._tables = []
self._pivots = []
self.data_validations = DataValidationList()
self._hyperlinks = []
self.sheet_state = 'visible'
self.page_setup = PrintPageSetup(worksheet=self)
self.print_options = PrintOptions()
self._print_rows = None
self._print_cols = None
self._print_area = None
self.page_margins = PageMargins()
self.views = SheetViewList()
self.protection = SheetProtection()
self._current_row = 0
self.auto_filter = AutoFilter()
self.sort_state = SortState()
self.paper_size = None
self.formula_attributes = {}
self.orientation = None
self.conditional_formatting = ConditionalFormattingList()
self.legacy_drawing = None
self.sheet_properties = WorksheetProperties()
self.sheet_format = SheetFormatProperties()
def _write_rels(self):
rels = RelationshipList()
rels.Relationship = self._rels
return rels.to_tree()
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_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)
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 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())