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_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(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 test_tables(self, PrimedWorksheetReader): reader = PrimedWorksheetReader reader.bind_cells() ws = reader.ws r = Relationship(type="table", Id="rId1", Target="../tables/table1.xml") rels = RelationshipList() rels.append(r) ws._rels = rels reader.bind_tables() assert reader.tables == ["../tables/table1.xml"]
def test_external_hyperlinks(self, PrimedWorksheetReader): reader = PrimedWorksheetReader reader.bind_cells() ws = reader.ws r = Relationship(type="hyperlink", Id="rId1", Target="../") rels = RelationshipList() rels.append(r) ws._rels = rels reader.bind_hyperlinks() assert ws['A1'].hyperlink.target == "../"
def test_internal_hyperlinks(self, PrimedWorksheetReader): reader = PrimedWorksheetReader reader.bind_cells() ws = reader.ws r = Relationship(type="hyperlink", Id="rId1", Target="../") rels = RelationshipList() rels.append(r) ws._rels = rels reader.bind_hyperlinks() assert ws['B4'].hyperlink.location == "'STP nn000TL-10, PKG 2.52'!A1"
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_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_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 test_merged_hyperlinks(self, PrimedWorksheetReader): reader = PrimedWorksheetReader reader.bind_cells() ws = reader.ws r = Relationship(type="hyperlink", Id="rId1", Target="../") rels = RelationshipList() rels.append(r) ws._rels = rels reader.bind_merged_cells() reader.bind_hyperlinks() assert ws.merged_cells == "G18:H18 G23:H24 A18:B18" assert ws['A18'].hyperlink.display == 'http://test.com' assert ws['B18'].hyperlink is None
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_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 openpyxl.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_merged_hyperlinks(self, PrimedWorksheetReader): reader = PrimedWorksheetReader reader.bind_cells() ws = reader.ws r = Relationship(type="hyperlink", Id="rId1", Target="../") rels = RelationshipList() rels.append(r) ws._rels = rels reader.bind_merged_cells() reader.bind_hyperlinks() assert ws.merged_cells == "G18:H18 G23:H24 A18:B18" assert ws['A18'].hyperlink.display == 'http://test.com' assert ws['B18'].hyperlink is None # Link referencing H24 should be placed on G23 because H24 is a merged cell # and G23 is the top-left cell in the merged range assert ws["G23"].hyperlink.tooltip == "openpyxl" assert ws["H24"].hyperlink is None
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_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 WorksheetWriter: def __init__(self, ws, out=None): self.ws = ws self.ws._hyperlinks = [] self.ws._comments = [] if out is None: out = create_temporary_file() self.out = out self._rels = RelationshipList() self.xf = self.get_stream() next(self.xf) # start generator def write_properties(self): props = self.ws.sheet_properties self.xf.send(props.to_tree()) def write_dimensions(self): """ Write worksheet size if known """ ref = getattr(self.ws, 'calculate_dimension', None) if ref: dim = SheetDimension(ref()) self.xf.send(dim.to_tree()) def write_format(self): self.ws.sheet_format.outlineLevelCol = self.ws.column_dimensions.max_outline fmt = self.ws.sheet_format self.xf.send(fmt.to_tree()) def write_views(self): views = self.ws.views self.xf.send(views.to_tree()) def write_cols(self): cols = self.ws.column_dimensions self.xf.send(cols.to_tree()) def write_top(self): """ Write all elements up to rows: properties dimensions views format cols """ self.write_properties() self.write_dimensions() self.write_views() self.write_format() self.write_cols() def rows(self): """Return all rows, and any cells that they contain""" # order cells by row rows = defaultdict(list) for (row, col), cell in sorted(self.ws._cells.items()): rows[row].append(cell) # add empty rows if styling has been applied for row in self.ws.row_dimensions.keys() - rows.keys(): rows[row] = [] return sorted(rows.items()) def write_rows(self): xf = self.xf.send(True) with xf.element("sheetData"): for row_idx, row in self.rows(): self.write_row(xf, row, row_idx) self.xf.send(None) # return control to generator def write_row(self, xf, row, row_idx): attrs = {'r': f"{row_idx}"} dims = self.ws.row_dimensions attrs.update(dims.get(row_idx, {})) with xf.element("row", attrs): for cell in row: if cell._comment is not None: comment = CommentRecord.from_cell(cell) self.ws._comments.append(comment) if (cell._value is None and not cell.has_style and not cell._comment): continue write_cell(xf, self.ws, cell, cell.has_style) def write_protection(self): prot = self.ws.protection if prot: self.xf.send(prot.to_tree()) def write_scenarios(self): scenarios = self.ws.scenarios if scenarios: self.xf.send(scenarios.to_tree()) def write_filter(self): flt = self.ws.auto_filter if flt: self.xf.send(flt.to_tree()) def write_sort(self): """ As per discusion with the OOXML Working Group global sort state is not required. openpyxl never reads it from existing files """ pass def write_merged_cells(self): merged = self.ws.merged_cells if merged: cells = [MergeCell(str(ref)) for ref in self.ws.merged_cells] self.xf.send(MergeCells(mergeCell=cells).to_tree()) def write_formatting(self): df = DifferentialStyle() wb = self.ws.parent for cf in self.ws.conditional_formatting: for rule in cf.rules: if rule.dxf and rule.dxf != df: rule.dxfId = wb._differential_styles.add(rule.dxf) self.xf.send(cf.to_tree()) def write_validations(self): dv = self.ws.data_validations if dv: self.xf.send(dv.to_tree()) def write_hyperlinks(self): links = HyperlinkList() for link in self.ws._hyperlinks: if link.target: rel = Relationship(type="hyperlink", TargetMode="External", Target=link.target) self._rels.append(rel) link.id = rel.id links.hyperlink.append(link) if links: self.xf.send(links.to_tree()) def write_print(self): print_options = self.ws.print_options if print_options: self.xf.send(print_options.to_tree()) def write_margins(self): margins = self.ws.page_margins if margins: self.xf.send(margins.to_tree()) def write_page(self): setup = self.ws.page_setup if setup: self.xf.send(setup.to_tree()) def write_header(self): hf = self.ws.HeaderFooter if hf: self.xf.send(hf.to_tree()) def write_breaks(self): brks = (self.ws.row_breaks, self.ws.col_breaks) for brk in brks: if brk: self.xf.send(brk.to_tree()) def write_drawings(self): if self.ws._charts or self.ws._images: rel = Relationship(type="drawing", Target="") self._rels.append(rel) drawing = Related() drawing.id = rel.id self.xf.send(drawing.to_tree("drawing")) def write_legacy(self): """ Comments & VBA controls use VML and require an additional element that is no longer in the specification. """ if (self.ws.legacy_drawing is not None or self.ws._comments): legacy = Related(id="anysvml") self.xf.send(legacy.to_tree("legacyDrawing")) def write_tables(self): tables = TablePartList() for table in self.ws._tables.values(): if not table.tableColumns: table._initialise_columns() if table.headerRowCount: try: row = self.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) except TypeError: warn( "Column headings are missing, file may not be readable" ) rel = Relationship(Type=table._rel_type, Target="") self._rels.append(rel) table._rel_id = rel.Id tables.append(Related(id=rel.Id)) if tables: self.xf.send(tables.to_tree()) def get_stream(self): with xmlfile(self.out) as xf: with xf.element("worksheet", xmlns=SHEET_MAIN_NS): try: while True: el = (yield) if el is True: yield xf elif el is None: # et_xmlfile chokes continue else: xf.write(el) except GeneratorExit: pass def write_tail(self): """ Write all elements after the rows calc properties protection protected ranges # scenarios filters sorts # always ignored data consolidation # custom views # merged cells phonetic properties # conditional formatting data validation hyperlinks print options page margins page setup header row breaks col breaks custom properties # cell watches # ignored errors # smart tags # drawing drawingHF # background # OLE objects # controls # web publishing # tables """ self.write_protection() self.write_scenarios() self.write_filter() self.write_merged_cells() self.write_formatting() self.write_validations() self.write_hyperlinks() self.write_print() self.write_margins() self.write_page() self.write_header() self.write_breaks() self.write_drawings() self.write_legacy() self.write_tables() def write(self): """ High level """ self.write_top() self.write_rows() self.write_tail() self.close() def close(self): """ Close the context manager """ if self.xf: self.xf.close() def read(self): """ Close the context manager and return serialised XML """ self.close() if isinstance(self.out, BytesIO): return self.out.getvalue() with open(self.out, "rb") as src: out = src.read() return out def cleanup(self): """ Remove tempfile """ os.remove(self.out) ALL_TEMP_FILES.remove(self.out)
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())
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())